Search

CN-116467341-B - Query acceleration method, system and device for extracting data based on SQL query template

CN116467341BCN 116467341 BCN116467341 BCN 116467341BCN-116467341-B

Abstract

The invention discloses a query acceleration method for extracting data based on an SQL query template, which comprises the following steps of extracting an initial SQL query mode of a query instance based on historical behavior data, generating a parameter dimension table based on dynamic parameters and parameter historical values, rewriting the initial SQL query mode and the parameter dimension table according to a preset model to generate a relation projection SQL, scheduling the relation projection SQL according to date parameters, scheduling an execution relation projection SQL to obtain an execution result, converting the execution result into all query instances corresponding to the query mode, storing the query instance and the corresponding result in an HBASE database in a one-to-one mapping manner, and inquiring whether the result corresponding to the query instance exists in the HBase database or not, if so, requesting the HBase database. According to the invention, SQL materialization rewrite is performed by utilizing the SQL query template and the historical parameter values, and the SQL instances possibly queried can be calculated in advance by scheduling according to the date, so that the extremely good query performance is realized.

Inventors

  • XU BAORONG
  • ZHANG LE
  • Shan Bingyang

Assignees

  • 浙江大应科技有限公司

Dates

Publication Date
20260505
Application Date
20230418

Claims (10)

  1. 1. The query acceleration method for extracting data based on the SQL query template is characterized by comprising the following steps of: Extracting an initial SQL query mode of a query instance based on historical behavior data, wherein the initial SQL query mode comprises dynamic parameters and parameter historical values; Generating a parameter dimension table based on the dynamic parameters and the parameter history values; Rewriting the initial SQL query mode and the parameter dimension table according to a preset model to generate a relational projection SQL; scheduling the relation projection SQL based on the dynamic parameters, scheduling the execution relation projection SQL to obtain an execution result, converting the execution result into all query examples corresponding to the query mode, and storing the query examples and the corresponding results in an HBASE database in a one-to-one mapping manner; and inquiring whether a result corresponding to the instance to be inquired exists in the HBase database, and if so, requesting the HBase database.
  2. 2. The query acceleration method of claim 1, wherein the initial SQL query pattern for extracting query instances based on historical behavioral data comprises the steps of: acquiring historical behavior data, wherein the historical behavior data comprises all data of a query instance; Acquiring all data of a query instance, wherein the all data comprise the query instance, and deducing a dynamic parameter and a historical combination value of the dynamic parameter based on the query instance; and carrying out cluster extraction on all the data according to a preset cluster model to obtain a corresponding initial SQL query mode, wherein the preset cluster model is based on dynamic parameter setting.
  3. 3. The query acceleration method of claim 1, wherein the generating a parameter dimension table based on the dynamic parameters and parameter history values comprises the steps of: extracting all data corresponding to the dynamic parameters and the parameter history values to obtain dynamic parameters, wherein the dynamic parameters comprise date dynamic parameters and non-date dynamic parameters, and generating a unique identifier based on each history value combination of the non-date dynamic parameters; And combining each historical value of the non-date dynamic parameter and a unique identifier as a field of a parameter dimension table.
  4. 4. The query acceleration method for extracting data based on an SQL query template according to claim 1, wherein the scheduling execution of the relational projection SQL to obtain an execution result, and converting the execution result into a result corresponding to the query instance, comprises the following steps: and acquiring the relational projection SQL, combining the data of the same identifier, and converting the data into a result corresponding to the corresponding query instance SQL.
  5. 5. The query acceleration method for extracting data based on an SQL query template according to claim 1, wherein the querying whether the result corresponding to the instance to be queried exists in an HBase database or not, if yes, requesting the HBase database, and comprising the following steps; and if the result exists, directly inquiring the HBase database, and if the result does not exist, directly issuing the result to the original database for data inquiry.
  6. 6. The query acceleration method of claim 1, wherein the query acceleration method is stored in an HBase database, and comprises the steps of: and storing the query instance SQL as a key and a data set with compose _uk of 01 as a value into a kv storage hbase in combination with the unique identifier.
  7. 7. The query acceleration method for extracting data based on SQL query templates according to claim 1, wherein the rewriting of the initial SQL query pattern and the parameter dimension table according to the preset model further comprises searching for the nearest public parent node SQL sub-query in which all non-date dynamic parameters are located if there are nested SQL sub-queries in the initial SQL query pattern; and rewriting according to a preset model based on the position and parameter dimension table of the SQL child query of the nearest public parent node.
  8. 8. The query acceleration system based on the SQL query template for extracting data is characterized by comprising a template extraction module, a parameter dimension-only table generation module, a rewrite generation module, a dispatch execution module and a query request module; The template extraction module is used for extracting an initial SQL query mode of a query instance based on historical behavior data, wherein the initial SQL query mode comprises dynamic parameters and parameter historical values; The parameter dimension-only table generation module is used for generating a parameter dimension table based on the dynamic parameters and the parameter history values; The rewrite generation module is used for rewriting the initial SQL query mode and the parameter dimension table according to a preset model to generate a relational projection SQL; The scheduling execution module is used for scheduling the relation projection SQL according to the date parameter, scheduling the execution relation projection SQL to obtain an execution result, converting the execution result into all query examples corresponding to the query mode, and storing the query examples and the corresponding results in the HBASE database in a one-to-one mapping manner; And the query request module is used for querying whether a result corresponding to the instance to be queried exists in the HBase database, and if so, requesting the HBase database.
  9. 9. A computer readable storage medium storing a computer program, which when executed by a processor implements the method of any one of claims 1 to 7.
  10. 10. A query acceleration device for extracting data based on a SQL query template, comprising a memory, a processor and a computer program stored in the memory and running on the processor, wherein the processor implements the method of any one of claims 1 to 7 when executing the computer program.

Description

Query acceleration method, system and device for extracting data based on SQL query template Technical Field The invention relates to the technical field of big data query, in particular to a query acceleration method, a query acceleration system and a query acceleration device for extracting data based on an SQL query template. Background In the prior art, the data query acceleration technology often occurs in a report scene, because report queries generally need extremely response performance such as opening within 3s, and besides time consumption of page rendering and other processes, the real SQL query only leaves 1s. The current solutions for realizing the acceleration of the data query are three types, namely, through pre-calculation, optimization on a storage computing layer (such as selecting a column storage and an MPP computing engine) to accelerate, or direct return when the query is next time based on the hot spot query result. For reports with large data volume, the second scheme can not meet the extremely high performance requirement in calculation, and the third scheme can solve the performance problem of repeated inquiry, but can not solve the scene of inquiry date change. Under a relatively fixed query mode, pre-computation belongs to a better scheme, and the current pre-computation is generally realized by beating a cube, such as kylin, and pre-computing a certain aggregation column according to certain dimension columns, or constructing a materialized view. The prior pre-calculation has the defects that the extreme query performance of all reports cannot be ensured, the pre-calculation configuration such as kylin cube and materialized view needs professional data personnel support, frequent maintenance is needed, and even if such data expert exists, the extreme query performance of all reports cannot be ensured. For example, once a cube of kylin is different from an actual query, there is a secondary calculation (an actual scene report configurator and a person constructing the configuration cube of a data model are independent two departments) in the configured dimension column and the aggregated column, so that the performance cannot meet the requirement. The same problem exists with building materialized views, and not all data sources support materialized views. Disclosure of Invention Aiming at the defects in the prior art, the invention provides a query acceleration method, a query acceleration system and a query acceleration device for extracting data based on an SQL query template. In order to solve the technical problems, the invention is solved by the following technical scheme: a query acceleration method for extracting data based on SQL query templates comprises the following steps: Extracting an initial SQL query mode of a query instance based on historical behavior data, wherein the initial SQL query mode comprises dynamic parameters and parameter historical values; Generating a parameter dimension table based on the dynamic parameters and the parameter history values; Rewriting the initial SQL query mode and the parameter dimension table according to a preset model to generate a relational projection SQL; scheduling the relation projection SQL based on the dynamic parameters, scheduling the execution relation projection SQL to obtain an execution result, converting the execution result into all query examples corresponding to the query mode, and storing the query examples and the corresponding results in an HBASE database in a one-to-one mapping manner; and inquiring whether a result corresponding to the instance to be inquired exists in the HBase database, and if so, requesting the HBase database. As an implementation manner, the initial SQL query pattern for extracting query instances based on historical behavior data includes the following steps: acquiring historical behavior data, wherein the historical behavior data comprises all data of a query instance; Acquiring all data of a query instance, wherein the all data comprise the query instance, and deducing a dynamic parameter and a historical combination value of the dynamic parameter based on the query instance; and carrying out cluster extraction on all the data according to a preset cluster model to obtain a corresponding initial SQL query mode, wherein the preset cluster model is based on dynamic parameter setting. As an implementation manner, the generating a parameter dimension table based on the dynamic parameter and the parameter history value includes the following steps: extracting all data corresponding to the dynamic parameters and the parameter history values to obtain dynamic parameters, wherein the dynamic parameters comprise date dynamic parameters and non-date dynamic parameters, and generating a unique identifier based on each history value combination of the non-date dynamic parameters; And combining each historical value of the non-date dynamic parameter and a unique identifier as a field of a param