Parallel SQL automatic generation method

Abstract

The invention relates to a parallelized SQL automatic generation method, which comprises the following steps of: obtaining a relationship tree of associated information among tables according to morethan one data table, carrying out breadth-first traversal, and pre-analyzing structural information of each layer of tree structure in the relationship tree of the tables, wherein each input data source table corresponds to one virtual table; aiming at the structure information corresponding to each virtual table, generating an association index table capable of expressing an association relationship between every two single tables; establishing indexes for the association relationship fields on all the association index tables; broadcasting the association index table to different types of data warehouses to realize transmission of constraints and association relationships among single tables in different libraries, and the virtual table corresponding to each of the other sub-tables can be directly associated with the virtual table corresponding to the main table through the association index table. By decoupling the dependency relationship between the tables, the execution efficiencyis effectively improved, the self limitation of the data warehouse is broken through, and the method has the characteristics of simplicity and easiness in use while being compatible with mainstream type data warehouses.

Description

technical field [0001] The invention belongs to the technical field of data processing and data extraction, and in particular relates to a parallelized SQL automatic generation method. Background technique [0002] Data storage in the form of databases has become an indispensable method in many applications. Commonly used databases include Oracle, Mysql, GreenPlum, Hive, etc., which are the mainstream sources of data sets. The operations on common mainstream databases basically involve the structured SQL language. In the execution of data extraction, SQL has efficient performance because it is more suitable for the interaction at the bottom of the database, and it is also simple and easy to use. [0003] Data extraction is used as a pre-data set for data processing and subsequent modeling, and usually requires data personnel to manually write a large amount of SQL. Even though SQL has the characteristics of simplicity and ease of use, the process of writing SQL and correctin...

AI Technical Summary

Problems solved by technology

[0003]Data extraction is used as a pre-data set for data processing and subsequent modeling. It usually requires data personnel to manually write a large amount of SQL, even if the SQL already has a concise and easy However, the process of writing SQL and correcting grammatical errors still consumes a lot of time and energy, and repeated work is difficult to reuse. At the same time, it is necessary to deal with the subtle differences between SQL languages ​​of different databases to achieve the goal of grammatical compatibility.

[0004]The current existing SQL generation system can realize syntax generation and error correction, and can optimize execution efficiency from the perspective of syntax, and can In terms of performance, the execution speed of SQL is improved, but the traditional table-to-table aggregation still depends on the order of execution between tables, so it cannot achieve true parallelization, and it cannot break through the database's limitation on the number of columns in a single table. The upper limit is limited; in addition, the current conventional multi-table data aggregation method only supports multi-table aggregation of the same source database. If you want to aggregate the tables of multiple types of databases, you need to move the data. This form is for big data. Quantitative table operations are very time-consuming

Images