CN118227656A - Query method and device based on data lake - Google Patents

Abstract

The present invention discloses a query method and device based on a data lake, including: sampling the target data set of the query according to the user input, obtaining the pattern information M and the data sample information, thereby constructing the query; decomposing the query into a number of subtasks, thereby constructing a processing graph; correcting the processing graph, using the shuffle technology and/or the collapse technology, and optimizing the corrected processing graph in combination with the cost model; generating and executing the code according to the optimized processing graph to output the user query result. The present invention does not require an intermediary mode, simplifies the query process, does not require data conversion and loading, simplifies the operation, and improves the query efficiency as a whole. In terms of query details, a query optimizer for LLM generated code is designed, which greatly improves the execution efficiency of the LLM generated code and the interpretability of the corresponding method, wherein the correction of the processing graph to assist LLM can improve the query accuracy, so that the accuracy of the entire natural language query task exceeds the traditional method.

Description

A query method and device based on data lake

Technical Field

The present invention relates to the field of data management technology, and in particular to a query method and device based on a data lake.

Background technique

Data lakes are designed as comprehensive storage centers that are designed to accommodate, manage, and protect large amounts of data, regardless of their structure. Whether the data is well-organized, semi-structured, or completely unstructured, the data lake is able to preserve this data in its original format. In addition, it excels at handling a wide range of data types, regardless of size. However, providing data lake services requires complex engineering efforts. Traditional data lakes usually take two different approaches. In both strategies, it is necessary to define a unified, intermediary schema and establish mapping relationships between each source schema and this central schema. In the query-driven approach, any query against the intermediary schema is converted into a corresponding query for each source dataset based on these mapping relationships. These queries are then processed separately by the source systems. The role of the data lake is to merge the results into a coherent output. In contrast, in the data-driven model, we transform all incoming data to be consistent with the intermediary schema and then load them into the host system within the data lake. Here, the host system is responsible for processing all queries, providing a centralized data processing solution.

There are three main challenges in implementing a data lake system:

1. Expert knowledge is required for schema definition. Developing an appropriate mediation schema requires extensive domain expertise and a comprehensive understanding of all source data. Achieving this insight may not always be feasible.

2. Complexity of schema mapping. Even with the help of semi-automatic tools, creating schema mapping is a complex and labor-intensive task. This process often results in significant processing overhead and requires a lot of manual intervention.

3. Data conversion dilemma. This challenge has two sides, depending on the chosen approach. In the data-driven approach, the process of data conversion and loading is resource-intensive and expensive. On the other hand, the query-driven approach introduces the complexity of query rewriting and result merging, making it challenging to ensure the correctness and traceability of query results.

Summary of the invention

The purpose of the present invention is to provide a query method and device based on a data lake to address the problems of difficult schema definition, complex schema mapping, and difficult data conversion in the current multimodal database field.

The object of the present invention is to achieve the following technical solution: a query method based on a data lake, comprising:

(1) Based on the user input, the target data set of the query is sampled to obtain the pattern information M and data sample information S, thereby constructing the query Q;

(2) Decompose the query Q into several subtasks to construct a processing graph G;

(3) Modify the processing graph G, use the shuffle technology and/or the collapse technology, and optimize the modified processing graph G in combination with the cost model;

(4) Generate code based on the optimized processing graph and execute it to output the user query results.

Furthermore, data sampling is performed from the target data set to obtain the most representative data samples. The core logic is data feature statistics and null value removal. In addition, the primary and foreign key relationships are followed to retrieve connectable tuples from the associated data, and the data association information is integrated and added to the pattern information M to finally form the query Q.

Furthermore, the query Q is decomposed into several subtasks, including:

Use the big model to decompose the query Q into several subtasks.

Furthermore, the large model is trained as follows:

The subtasks and corresponding operators are used as training sets to train the large model;

The trained large model is used to convert the subtasks into corresponding operators.

Furthermore, the modified processing graph G includes: formal correction, logical correction and global correction; the formal correction is to check the table name and column name referenced by the subtask description in the processing graph; the logical correction is to check the conditions in the subtask description; the global correction is to check the relationship between the subtasks.

The shuffle technology and/or collapse technology are used, and the modified processing graph G is optimized in combination with the cost model, including:

The shuffle technology is used, and the order of operators in the processing graph G is changed in combination with the cost model to optimize the query plan;

Collapse technology is used, and the cost model is combined to cyclically calculate the cost benefits of merging multiple operators, thereby optimizing the query plan;

The operator is transformed from the subtask.

Furthermore, the method further includes: repeatedly executing step (2) and step (3) to obtain multiple optimized processing graphs, and selecting the optimal processing graph to execute step (4).

Furthermore, the optimal processing graph is selected from a plurality of optimized processing graphs through a cost model.

Furthermore, the method further includes: performing code verification after the code is generated, and if the verification fails, re-executing step (4); if the verification still fails, re-executing steps (2) to (4).

Furthermore, the code verification includes:

Operator-level verification is performed based on the generated code and the corresponding optimized processing graph.

The present invention also provides a query device based on a data lake, comprising:

The preprocessing module is used to sample the target data set of the query according to the user input, obtain the pattern information M and data sample information S, and thus construct the query Q;

The processing graph construction module is used to decompose the query Q into several subtasks to construct the processing graph G;

A processing graph optimization module is used to modify the processing graph G, and optimize the modified processing graph G by using the shuffle technology and/or the collapse technology in combination with the cost model;

The code generation and execution module is used to generate and execute code according to the optimized processing graph to output user query results.

Compared with the prior art, the beneficial effects of the present invention are: compared with the existing data lake query method, no intermediary mode is required, the query process is simplified, data conversion and loading are not required, the operation is simplified, and the query efficiency is improved overall.

In terms of query details, a query optimizer was designed for LLM generated code, which greatly improved the execution efficiency of LLM generated code and the interpretability of the corresponding method. Among them, modifying the processing graph to assist LLM can greatly improve query accuracy, making the accuracy of the entire natural language query task exceed that of traditional methods.

BRIEF DESCRIPTION OF THE DRAWINGS

In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

FIG1 is a schematic diagram of a query method based on a data lake according to an embodiment of the present invention;

FIG2 is a diagram showing an example of query optimization provided by an embodiment of the present invention;

FIG. 3 is a functional module diagram of the present invention.

Detailed ways

The present invention is described in detail below in conjunction with the accompanying drawings. In the absence of conflict, the features of the following embodiments and implementations can be combined with each other.

A query method based on a data lake of the present invention, as shown in FIG1 , includes:

(1) Based on the user input, the target data set of the query is sampled to obtain the pattern information M and data sample information S, thereby constructing the query Q;

For example, when a user asks for a query on the target data set: to query the total number of teachers in a school, the relevant tables of the entire database will be scanned (based on simple filtering based on keyword similarity comparison) to obtain the column name, type, and primary and foreign key relationships of each column, collectively referred to as pattern information M, and data scanning and sampling will be performed to obtain representative statistical information (such as the rating level range 1-5), main high-frequency values, etc., collectively referred to as data sample information S.

(2) Decompose the query Q into several subtasks to construct a processing graph G;

Specifically, a large language model (LLM) is used to decompose the query Q into several subtasks _Ji to construct a processing graph G. The large model is also pre-trained as follows: the operator list of the operator database (including detailed usage) and query processing examples are used to train the LLM, and the trained LLM is used to convert the input query Q into a processing graph G composed of multiple subtasks.

The operation uses an operator. The description information of the operator is similar to the execution plan in the database. We decompose the entire query logic into several sub-operations. Each operator will describe the specific conditions of the operation. For example, the filter operator will contain the exact conditions for filtering data. A complete query task may consist of read (reading data sets), filter (filtering conditions), select (selecting target columns), write (writing results to files) and various other query operations.

In another embodiment, it also includes: constructing an offline operator database, which includes typical query pairs (Ji, Gi) derived from a series of typical natural language queries (i.e., user input), Gi represents a processing graph constructed based on subtask Ji, and the processing graph here comes from a plurality of pre-included query examples, which basically include examples of combinations of various operators. In addition, the database will also be updated according to the user input in step (1) and the new processing graph constructed in step (2).

(3) Modify the processing graph G, and use the shuffle technology and/or collapse technology in combination with the cost model to optimize the modified processing graph G;

The subtasks generated by LLM are unreliable, so they must be logically corrected to improve their accuracy. The corrections mainly include formal correction, logical correction and global correction. Formal correction checks the table names and column names referenced by the subtask description in the processing diagram and attempts to correct them. If the correction cannot be made, return to step 2 and try again. Logical correction checks the conditions in the subtask description, such as whether the join operation conditions comply with the foreign key relationship and whether the read operation description of different modal data is correct. Global correction checks the relationship between subtasks, such as whether the target table and column of the subsequent operation are read or generated in the previous task.

After the correction is completed, the processing graph is further logically optimized to improve efficiency. Specifically, the shuffle technology is used, and the order of operators in the processing graph G is changed in combination with the cost model to optimize the query plan; the collapse technology is used, and the cost model is combined to cyclically calculate the cost benefits of merging multiple operators to optimize the query plan; according to the specific problem, one of the shuffle technology and the collapse technology is used or both are used to optimize the processing graph G, thereby optimizing the query plan.

The implementation of the shuffle technology takes the combination of join and filter operators as an example, as shown in Figure 2. Logically, the processing graph generated by LLM will try to execute join first and then filter, but in most cases this operation order leads to low efficiency. The shuffle technology first obtains a possible better state after adjusting the order (such as the common execution of filter first and then join), and then calculates the total CPU computing amount based on information such as the amount of data, the number of data operations, the number of rows and columns operated, which is called the computing cost. The costs of the two are compared, and the latter is determined to have a lower cost, and the processing graph is converted to the latter.

The implementation of Collapse technology is based on the defined merging rules. The exact cost is calculated through the cost model before deciding to execute the merge. Specifically, Collapse technology considers subquery expansion and optimal code implementation. Subquery expansion means that the subquery implementation in the query task may lead to a large number of repeated calculations, and the query needs to be expanded into a non-subquery form, which is based on a large number of subquery predefined rules to obtain an equivalent form. The optimal code implementation aims to eventually generate more efficient code. For example, group operations and aggregate function operators such as max will be presented separately, but if a separate code is generated for each operator, the data will be duplicated in memory. The better choice is to merge the two operators and finally use one line of code to achieve the goal.

In another embodiment, the method further includes: repeatedly executing step (2) and step (3) to obtain multiple optimized processing graphs, and selecting the optimal processing graph to execute step (4). The optimal processing graph is selected from the multiple optimized processing graphs through a cost model.

(4) Generate code based on the optimized processing graph and execute it to output the user query results.

Specifically, according to the optimized processing graph or the optimal processing graph, a running state s is generated, and the code is generated iteratively. The code generation process is iterative, starting from the graph vertex (operator) without a predecessor, and proceeding through the graph in sequence. This creates a running state s = [J', G', C'], where J' represents the task that has been completed by the existing subgraph G', and C' represents the current set of code snippets. Each new operator converted into code will update the state s to guide the subsequent code generation work. Finally, the code snippets are linked into a coherent output. In each step of generating code, we will generate corresponding customized prompts according to the corresponding operator type and select the best code implementation example.

In one embodiment, the method further includes: performing code verification after the code is generated, and if the verification fails, re-executing step (4); if the verification still fails, re-executing steps (2) to (4).

Code verification is performed, including: operator-level verification based on the generated code and the corresponding optimized processing graph. Specifically, we search for the corresponding implementation in the code in order based on each operator in the processing graph. This is step verification, and we pay special attention to whether the operator conditions have corresponding accurate implementations in the code, that is, logical verification. For example, the operator conditions of the filter will be converted into code language, and the two need to be distinguished. This distinction is relatively loose. When it cannot be distinguished under the preset rules, the next step will be allowed to be executed to determine whether a valid result can be obtained. While verifying the operator, the syntax of the code is verified, and finally the code is executed and the result is returned. Verify whether the result meets expectations based on user feedback.

Therefore, the present invention provides LLM with comprehensive metadata from the source data set, so that it uses predefined data processing operators with detailed semantic descriptions to answer queries, which also includes modifying and optimizing the processing graph and verifying the subsequently generated code. This allows users to submit queries in natural language to improve intuitiveness, completely bypasses the intermediary mode, simplifies the query process, does not require data conversion and loading, and simplifies operations. At the same time, the code generation process is simplified, improving performance and accuracy of results.

The present invention also provides a query device based on a data lake, as shown in FIG3 , comprising:

The preprocessing module is used to sample the target data set of the query according to the user input, obtain the pattern information M and data sample information S, and thus construct the query Q;

The processing graph construction module is used to decompose the query Q into several subtasks to construct the processing graph G;

A processing graph optimization module modifies the processing graph G, uses the shuffle technology and/or the collapse technology, and optimizes the modified processing graph G in combination with the cost model;

The code generation and execution module is used to generate and execute code according to the optimized processing graph to output user query results.

It should be noted that the device embodiment shown in this embodiment matches the content of the above method embodiment, and the content of the above method embodiment can be referred to, which will not be repeated here.

The above embodiments are only used to illustrate the design ideas and features of the present invention, and their purpose is to enable those skilled in the art to understand the content of the present invention and implement it accordingly. The protection scope of the present invention is not limited to the above embodiments. Therefore, any equivalent changes or modifications made based on the principles and design ideas disclosed by the present invention are within the protection scope of the present invention.

Claims (10)

1. A data lake-based query method, comprising:

(1) Sampling the target data set to be queried according to user input to obtain mode information M and data sample information S, so as to construct query Q;

(2) Decomposing the query Q into a plurality of subtasks, thereby constructing a processing diagram G;

(3) Modifying the processing diagram G, adopting a shuffle technology and/or a Collapse technology, and optimizing the modified processing diagram G by combining a cost model;

(4) And generating codes according to the optimized processing diagram and executing the codes to output a user query result.

2. The method of claim 1, wherein data sampling is performed from a target data set to obtain a most representative data sample, and wherein core logic is data feature statistics and null removal; in addition, connectable tuples are retrieved from the associated data following the primary foreign key relationship, the data association information is integrated, and added to the pattern information M, ultimately forming a query Q.

3. The method of claim 1, wherein decomposing the query Q into a number of sub-tasks comprises:

the query Q is broken down into several subtasks using a large model.

4. A method according to claim 3, wherein the large model is further trained as follows:

The subtasks and the corresponding operators are used as training sets for training the large model;

the trained large model is used to translate the subtasks into corresponding operators.

5. The method of claim 1, wherein the modifying the process map G comprises: form correction, logic correction, and global correction; the form is modified to be checked for table names and column names of subtask description references in the processing diagram; the logic is modified to check for conditions in the subtask description; the global correction is to check the relation among subtasks;

the optimization of the corrected processing graph G by adopting a shuffle technology and/or a Collapse technology and combining a cost model comprises the following steps:

Adopting a shuffle technology, and changing the sequence of operators in the processing diagram G by combining a cost model to optimize a query plan;

adopting a Collapse technology, and circularly calculating the cost benefit brought by combining a plurality of operators by combining a cost model, so as to optimize a query plan;

Wherein the operator is translated from the subtask.

6. The method as recited in claim 1, further comprising: and (3) repeatedly executing the step (2) and the step (3) to obtain a plurality of optimized processing graphs, and selecting the optimal processing graph to execute the step (4).

7. The method of claim 6, wherein the optimal process map is selected from a plurality of optimized process maps by a cost model.

8. The method as recited in claim 1, further comprising: after the code is generated, code verification is carried out, if verification fails, the step (4) is re-executed, and if the verification still fails, the steps (2) - (4) are re-executed.

9. The method of claim 8, wherein the performing code verification comprises:

and verifying the level of the operators according to the generated codes and the corresponding optimized processing diagram.

10. A data lake-based query device, comprising:

The preprocessing module is used for sampling the target data set to be inquired according to user input to obtain mode information M and data sample information S so as to construct inquiry Q;

The processing diagram construction module is used for decomposing the query Q into a plurality of subtasks so as to construct a processing diagram G;

the processing diagram optimizing module is used for correcting the processing diagram G, adopting a shuffle technology and/or a Collapse technology, and combining a cost model to optimize the corrected processing diagram G;

and the code generation and execution module is used for generating codes according to the optimized processing diagram and executing the codes so as to output a user query result.