US-12619609-B1 - Automatic querying through selection of registered data sets

US12619609B1US 12619609 B1US12619609 B1US 12619609B1US-12619609-B1

Abstract

Methods and systems described herein are directed to creating customized queries on data sets via selection of search elements based on meta-data from data set registrations. In some implementations, an automatic query system can register database elements with associated meta-data by receiving programming data objects, corresponding to database elements, with meta-data and adding the programming objects as selectable values for a user interface (UI). Each data object can define one or more elements for a table and/or column(s) within a table in a database. The automatic query system can provide the UI to a user to receive table and column selections. The automatic query system can select a root table to join the selected tables into, and generate a join query for the root table by inserting search string snippets, corresponding to selected tables and columns, into a query template.

Inventors

Matthew James Martinez
Joseph James Albert Campbell
Quentin L. Holness
Ryan Matthew West
Rachel Michelle Ballew
Mitchell Wade Palermo

Assignees

UNITED SERVICES AUTOMOBILE ASSOCIATION (USAA)

Dates

Publication Date: 20260505
Application Date: 20250116

Claims (20)

1 . A method comprising: receiving, from a user interface, selections of two or more tables and one or more columns for a join query, wherein the user interface is based on programming data objects corresponding to one or more database elements; selecting a first table of the two or more tables as a root table for the join query based on the first table having a highest number of connections between each of the two or more tables; resolving code snippets, from the programming data objects corresponding to the two or more tables and the one or more columns, into search string snippets; executing the join query to: insert, into a root table slot in a query template, a search string snippet for the root table; insert, into a join table slot in the query template, one or more search string snippets for one or more tables of the two or more tables that are not the root table; and insert into a column select slot in the query template, the one or more search string snippets for the one or more columns; and displaying one or more results of the execution of the join query.
2 . The method of claim 1 , further comprising: adding the programming data objects as selectable elements for the user interface, wherein tables are added as selectable elements, and wherein the user interface is configured to show columns, as selectable elements, from corresponding selected tables.
3 . The method of claim 1 , further comprising: receiving, from the user interface, one or more value filter selections for the join query; and generating the one or more value filter selections into search string snippets.
4 . The method of claim 1 , wherein generating the join query further comprises: inserting, into a value filter selection slot in the query template, search string snippets for one or more value filter selections.
5 . The method of claim 1 , further comprising: determining column labels that the two or more tables have in common; and selecting the first table of the two or more tables as the root table based on the first table having a highest number of column labels in common between each of the two or more tables.
6 . The method of claim 1 , further comprising: filtering a list of selectable tables to present tables that can be joined with previously selected tables.
7 . The method of claim 1 , further comprising: removing one or more tables from a list of selectable tables when the one or more tables have no connections with previously selected tables.
8 . A system comprising: one or more processors; and one or more memories storing instructions that, when executed by the one or more processors, cause the system to perform a process comprising: receiving, from a user interface, selections of two or more tables and one or more columns for a join query, wherein the user interface is based on programming data objects corresponding to one or more database elements; selecting a first table of the two or more tables as a root table for the join query based on the first table having a highest number of connections between each of the two or more tables; resolving code snippets, from the programming data objects corresponding to the two or more tables and the one or more columns, into search string snippets; executing the join query to: insert, into a root table slot in a query template, a search string snippet for the root table; insert, into a join table slot in the query template, one or more search string snippets for one or more tables of the two or more tables that are not the root table; and insert into a column select slot in the query template, the one or more search string snippets for the one or more columns; and displaying one or more results of the execution of the join query.
9 . The system according to claim 8 , wherein the process further comprises: adding the programming data objects as selectable elements for the user interface, wherein tables are added as selectable elements, and wherein the user interface is configured to show columns, as selectable elements, from corresponding selected tables.
10 . The system according to claim 8 , wherein the process further comprises: receiving, from the user interface, one or more value filter selections for the join query; and generating the one or more value filter selections into search string snippets.
11 . The system according to claim 8 , wherein generating the join query further comprises: inserting, into a value filter selection slot in the query template, search string snippets for one or more value filter selections.
12 . The system according to claim 8 , wherein the process further comprises: determining column labels that the two or more tables have in common; and selecting the first table of the two or more tables as the root table based on the first table having a highest number of column labels in common between each of the two or more tables.
13 . The system according to claim 8 , wherein the process further comprises: filtering a list of selectable tables to present tables that can be joined with previously selected tables.
14 . The system according to claim 8 , wherein the process further comprises: removing one or more tables from a list of selectable tables when the one or more tables have no connections with previously selected tables.
15 . A non-transitory computer-readable medium storing instructions that, when executed by a computing system, cause the computing system to perform operations comprising: receiving, from a user interface, selections of two or more tables and one or more columns for a join query, wherein the user interface is based on programming data objects corresponding to one or more database elements; selecting a first table of the two or more tables as a root table for the join query based on the first table having a highest number of connections between each of the two or more tables; resolving code snippets, from the programming data objects corresponding to the two or more tables and the one or more columns, into search string snippets; executing the join query to: insert, into a root table slot in a query template, a search string snippet for the root table; insert, into a join table slot in the query template, one or more search string snippets for one or more tables of the two or more tables that are not the root table; and insert into a column select slot in the query template, the one or more search string snippets for the one or more columns; and displaying one or more results of the execution of the join query.
16 . The non-transitory computer-readable medium of claim 15 , wherein the operations further comprise: adding the programming data objects as selectable elements for the user interface, wherein tables are added as selectable elements, and wherein the user interface is configured to show columns, as selectable elements, from corresponding selected tables.
17 . The non-transitory computer-readable medium of claim 15 , wherein the operations further comprise: receiving, from the user interface, one or more value filter selections for the join query; and generating the one or more value filter selections into search string snippets.
18 . The non-transitory computer-readable medium of claim 15 , wherein generating the join query further comprises: inserting, into a value filter selection slot in the query template, search string snippets for one or more value filter selections.
19 . The non-transitory computer-readable medium of claim 15 , wherein the operations further comprise: determining column labels that the two or more tables have in common; and selecting the first table of the two or more tables as the root table based on the first table having a highest number of column labels in common between each of the two or more tables.
20 . The non-transitory computer-readable medium of claim 15 , wherein the operations further comprise: filtering a list of selectable tables to present tables that can be joined with previously selected tables.

Description

CROSS-REFERENCE TO RELATED APPLICATIONS This application is a continuation of U.S. patent application Ser. No. 17/877,784, filed on Jul. 29, 2022, now allowed, entitled “AUTOMATIC QUERYING THROUGH SELECTION OF REGISTERED DATA SETS,” which claims priority to U.S. Provisional Application No. 63/227,524, filed on Jul. 30, 2021, entitled “AUTOMATIC QUERYING THROUGH SELECTION OF REGISTERED DATA SETS,” both of which are hereby incorporated by reference in its entirety for all purposes. TECHNICAL FIELD The present disclosure is directed to creating customized queries on data sets via selection of search elements based on meta-data from data set registration. BACKGROUND When analysts build a search query, they have to identify data sources, such as which tables to search and which columns to use. However, these data sources are often cryptically formatted. Furthermore, background knowledge for them is often only available in unstructured institutional knowledge, which the analysis must exhaustively search such as by asking teammates or subject matter experts, looking through often outdated comments or company manuals, and applying inefficient trial and error techniques. However, such analysis is inefficient and inaccurate for locating data that is stored in various tables and under a variety of labels. BRIEF DESCRIPTION OF THE DRAWINGS FIG. 1 is a block diagram illustrating an overview of devices on which some implementations can operate. FIG. 2 is a block diagram illustrating an overview of an environment in which some implementations can operate. FIG. 3 is a block diagram illustrating components which, in some implementations, can be used in a system employing the disclosed technology. FIG. 4 is a flow diagram illustrating a process used in some implementations for registering database elements with meta-data. FIG. 5A is a flow diagram illustrating a process used in some implementations for receiving table, column, and value filter query selections for a query. FIG. 5B is a flow diagram illustrating a process used in some implementations for executing a join query based on query selections. FIG. 6A is a conceptual diagram illustrating an example of a UI for selecting tables of data for a query. FIG. 6B is a conceptual diagram illustrating an example of a UI for selecting columns of data for a query. FIG. 6C is a conceptual diagram illustrating an example of a UI for selecting value filters for a query. FIG. 6D is a conceptual diagram illustrating an example of query results. The techniques introduced here may be better understood by referring to the following Detailed Description in conjunction with the accompanying drawings, in which like reference numerals indicate identical or functionally similar elements. DETAILED DESCRIPTION Aspects of the present disclosure are directed to executing customized queries created from user selections, of query elements, that are based on meta-data from data set registrations. Currently, analysts often must rely on informal, unorganized, or cryptic institutional data sources (e.g., their knowledge, knowledge of teammates or subject matter experts, non-uniform and ambiguous data labels, manuals or comments, etc.), to build a search query of data located in multiple tables or databases. However, relying on such esoteric sources to build a search query is an inefficient and inaccurate process to locate data that is stored in various tables and under a variety of labels. Analysts unfamiliar with data labels or data storage processes of a company can waste time and resources building a search query and may require months of training to be able to effectively build such queries. Thus, an automatic query system is needed to retrieve data from a variety of tables in a database(s) and provide the results to the user. The automatic query system described herein can allow a user to filter and select tables, columns, and values of data in a database, using meta-data assigned to those database elements, and execute a join query without the user needing detailed knowledge of the underlying data tables. In some implementations, the automatic query system can register database elements with associated meta-data by receiving programming data objects, corresponding to database elements, with meta-data and adding the programming objects as selectable values for a user interface (UI). Each data object can define one or more elements for a table and/or column(s) within a table in a database. The corresponding meta-data can define 1) what is shown in the UI for that table/column selection and 2) how the tables/columns can be searched/filtered. The automatic query system can add the programming objects as selectable values for the UI by adding the tables as selectable elements for the UI and adding columns as selectable elements where the corresponding table has been selected. Additional details on registering database elements with meta-data are provided below in relation to FIG. 4. In some implemen