Automatic query and data retrieval optimization through procedural generation of data tables from query patterns

Patent metadata

Field	Value
Publication number	US-2025348489-A1
Application number	US-202519238988-A
Country	US
Kind code	A1
Filing date	Jun 16, 2025
Priority date	Mar 19, 2024
Publication date	Nov 13, 2025
Grant date	—

Abstract

Latency, response times, and efficiency improvements for data querying are provided herein, particularly in the context of querying large database systems and data tables from disparate data sources. There are provided systems and methods for automatic query and data retrieval optimization through procedural generation of data tables from query patterns. A service provider may utilize different computing services for query processing and data retrieval for different applications and services used by internal and/or external users. Instead of querying large database systems and numerous data tables, pre-aggregated data tables may instead be used and searched by procedurally generating such tables based on precomputation rules and query patterns. Once patterns have been identified in queries, corresponding data may be aggregated from data sources in a pre-aggregated data table. Query optimization rules may then be used to have these data tables queried in place of their original sources.

First claim

1 . (canceled)

2 . A method comprising:
receiving a query comprising a first parameter and a second parameter usable to search a database system for first data, wherein the database system utilizes a plurality of query structures for a plurality of data storage formats; determining a first rule of a first plurality of rules that corresponds to the first parameter and the second parameter, wherein the first plurality of rules are usable to configure queries to search a plurality of data tables instead of the database system when two or more parameters are identified in the queries; revising the query for the plurality of data tables based on the first rule; querying the plurality of data tables but not the database system; determining the first data from the plurality of data tables based on the querying the plurality of data tables; and returning the first data responsive to the query without querying the database system.

3 . The method of claim 2 , wherein, prior to the receiving the query, the method further comprises:
generating the plurality of data tables using a second plurality of rules, query patterns for queries from one or more query logs, and data from the database system, wherein the second plurality of rules are associated with aggregating the data to the plurality of data tables when one or more of the query patterns meet or exceed a threshold.

4 . The method of claim 3 , wherein, prior to the generating the plurality of data tables, the method further comprises:
accessing the query patterns associated with the queries; determining that one or more of the query patterns meet or exceed the threshold associated with a corresponding one or more of the second plurality of rules; and aggregating the data from the database system, wherein the plurality of data tables are generated based on the aggregated data.

5 . The method of claim 4 , wherein, prior to the receiving the query, the method further comprises:
generating the first plurality of rules based on the one or more of the query patterns and the aggregated data.

6 . The method of claim 5 , wherein the second plurality of rules usable by an offline precomputation component that generates the plurality of data tables in an offline environment, and wherein the first plurality of rules are usable by an online query optimization component that configures the queries to search the plurality of data tables in an online environment.

7 . The method of claim 3 , wherein the generating the plurality of data tables is triggered based on a job executed by a log analysis system that matches the query patterns to the second plurality of rules.

8 . The method of claim 3 , further comprising:
associating, in a data catalog, each of the plurality of data tables generated with one or more sources of the data from the database system; and maintaining the data catalog having a plurality of associations between each of the plurality of data tables and the one or more sources, wherein the data catalog enables searching of each of the one or more sources using corresponding ones of the plurality of query structures and the plurality of data storage formats.

9 . The method of claim 2 , further comprising generating an execution plan to execute the query and search the plurality of data tables, wherein the execution plan is generated based on the first rule.

10 . A system comprising:
a non-transitory memory; and one or more hardware processors coupled to the non-transitory memory and configured to execute instructions to cause the system to:
receive a request for data;
identify a first parameter and a second parameter usable to search a plurality of databases, each having a corresponding request structure and a corresponding data format, for the data based on the request;
determine that the first parameter and the second parameter correspond to a first rule usable to redirect the request to search a data table instead of searching the plurality of databases, wherein the data table comprises pre-aggregated data from the plurality of databases based on requests for the pre-aggregated data meeting or exceeding a threshold, wherein each of the requests includes two or more parameters usable to search the pre-aggregated data;
determine the data table having the pre-aggregated data based on the first rule;
search the data table for the data; and
provide the data without searching the plurality of databases in response to the request.

11 . The system of claim 10 , wherein, prior to receiving the request, executing the instructions further causes the system to:
generate the data table using a second rule associated with one or more request patterns associated with the requests meeting or exceeding the threshold.

12 . The system of claim 11 , wherein, prior to generating the data table, executing the instructions further causes the system to:
aggregate data records corresponding to the pre-aggregated data from the plurality of databases based on the second rule, wherein the data table is further generated using the aggregated data records to store the pre-aggregated data having the aggregated data records.

13 . The system of claim 12 , wherein executing the instructions further causes the system to:
analyze the one or more request patterns for parameters associated with searching the plurality of databases; and responsive to analyzing the one or more request patterns, generate the first rule based on the first parameter and the second parameter identified for searching the plurality of databases for the data.

14 . The system of claim 11 , wherein an offline component generates the data table in an offline environment, and wherein an online component routes the request to search the data table in an online environment based on the first parameter and the second parameter.

15 . The system of claim 11 , wherein generating the data table is performed responsive to executing a job by a log analysis system that analyzes the one or more request patterns based on the second rule.

16 . The system of claim 11 , wherein executing the instructions further causes the system to:
associate the pre-aggregated data in the data table with one or more sources of the pre-aggregated data in the plurality of databases.

17 . The system of claim 10 , wherein searching the data table is based on an execution plan associated with searching at least one of columns or rows of the data table using the first parameter and the second parameter.

18 . A non-transitory machine-readable medium having stored thereon machine-readable instructions executable to cause a machine to perform operations comprising:
receiving a query for a data record stored by at least one of a plurality of databases, wherein the plurality of databases have a plurality of query languages usable to search for the data record in a corresponding one of a plurality of data formats; identifying at least two query data values of the query; determining, based on the at least two query data values, a first rule usable to search a data table comprising pre-aggregated data for the data record, wherein the first rule redirects the query from searching the plurality of databases to searching the data table; identifying the data table based on the first rule; searching the pre-aggregated data of the data table for the data record using the at least two query data values and the first rule; determining the data record based on the searching; and outputting a response to the query comprising the data record without searching the plu

Assignees

• Paypal Inc

Inventors

• Chen Chongchen
• Feng Si
• Huang Dawei

Classifications

• G06F16/24542 Primary

  Plan optimisation · CPC title