Chinese natural language generated SQL statement and cross-database query method

Abstract

The application relates to a Chinese natural language generation SQL sentence and cross-database query method, which comprises the following steps: pre-processing a natural language sentence, obtaining a phrase segment, and dividing the phrase segment into multiple alternative segments; if the alternative segment is surrounded by a symbol, the alternative segment is marked as a value; then, according to a preset dictionary, the synonyms of the remaining alternative segments are obtained; the alternative segments and the synonyms are matched with table annotations, field annotations and label metadata in a preset metadata database; according to the table annotations, the field annotations or the label metadata matched by the alternative segments, the database name is obtained, and the alternative segments and the synonyms are marked as table names, field names or values; then, a new string is generated; through encoding and decoding of the string, the SQL is generated according to the SQL syntax rules in combination with the value. The application can improve the accuracy of the SQL sentence when the SQL sentence is applied to in-depth query in the corresponding field, and can reduce the situation that the query result does not match the real intention of the user query.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of natural language processing, in particular to a Chinese natural language generation SQL statement and cross-database query method. BACKGROUND

[0002] With the development of big data technology, more and more enterprises realize the importance of data, and begin to transform from digitization to dataization. The query demand of enterprises for various data shows the characteristics of diversity, dispersion and one-off. Therefore, the data department often opens the data query function and provides it to the business department for self-service query and analysis of data. However, although the business intelligence of the data warehouse query tool does not require the operator to understand the complex structured query language (SQL) writing method, the interface is complex, the terms are numerous, and there is still a high learning cost. The query efficiency and flexibility are not high enough. Most importantly, for software-as-a-service products, it is necessary to precisely, flexibly and finely control the permissions of platform tenants. Therefore, providing an efficient, simple and secure user-database auxiliary interaction means will help to improve the efficiency of data analysts.

[0003] To this end, the industry has proposed a scheme named Text-to-SQL, that is, converting natural language into corresponding SQL statements for query, querying in the database and returning the corresponding results. Through Text-to-SQL as the interface of database query, the queryer can interact with the database only by natural language (text or voice) without understanding the writing method of SQL, which greatly reduces the technical threshold of data query and improves the efficiency.

[0004] However, the existing Text-to-SQL model mainly considers the generalization of the model when designing. At the same time, due to the vastness and profundity of the Chinese language, when the existing Text-to-SQL model needs to be applied to a certain field and needs to be in-depth queried, the correct recall rate of the query is not ideal, and there is a problem that the query result and the real intention of the user query cannot be correctly aligned. SUMMARY

[0005] Based on this, the purpose of the present application is to provide a Chinese natural language generation SQL statement and cross-database query method and device, which has improved the accuracy of Text-to-SQL when applied to the corresponding field for in-depth query. The query statement is analyzed in combination with the metadata database to reduce the situation that the query result does not match the real intention of the user query.

[0006] A Chinese natural language generation SQL statement method, comprising the following steps:

[0007] S101, preprocessing the natural language statement to obtain the phrase segment contained in the natural language statement;

[0008] S102, dividing the phrase segment into multiple candidate segments;

[0009] S103, judging whether the candidate segment is surrounded by symbols before the preprocessing, if the candidate segment is surrounded by symbols, marking the candidate segment as a value;

[0010] S104, if the candidate segment is not surrounded by symbols, obtaining a synonym of the candidate segment according to a preset dictionary;

[0011] S105, matching the candidate segment and the synonym with table annotations, field annotations and tag metadata in a preset metadata database, wherein the metadata database contains table annotations, field annotations and tag metadata of multiple databases;

[0012] S106, if the candidate segment and / or the synonym matches the table annotation, marking the candidate segment as a table name; if the candidate segment and / or the synonym matches the field annotation, marking the candidate segment as a field name; if the candidate segment and / or the synonym matches the tag metadata, marking the candidate segment as a value; and obtaining a database name of a corresponding database according to the table annotation, the field annotation or the tag metadata matched by the candidate segment and the synonym;

[0013] S107, forming a new string by combining the database name, the table name, the field name and the natural language sentence;

[0014] S108, encoding the string to generate a vector;

[0015] S109, decoding the vector, and generating a SQL statement according to a SQL syntax rule and in combination with the value.

[0016] A method for cross-database query of a SQL statement generated based on the above method for generating a SQL statement from a Chinese natural language, comprising the following steps:

[0017] S301, adjusting the SQL statement by using a scheduling unit;

[0018] S302, judging whether the SQL statement involves only one database according to the number of database names contained in the SQL statement, if the SQL statement involves only one database, scheduling an idle execution unit to perform database query, and returning the query result to the scheduling unit;

[0019] S303, if the SQL statement involves multiple databases, the scheduling unit is used to parse the SQL statement and compile an optimized execution plan, wherein the optimized execution plan is an execution plan obtained by analyzing the SQL statement in combination with the content of the metadata database;

[0020] S304, according to the optimized execution plan, a directed acyclic graph is obtained, the execution unit is scheduled, the SQL statement query of each stage is executed, the SQL statement query results of each stage are merged according to the directed acyclic graph, a final query result is generated, and the final query result and query statistical data are returned to the scheduling unit, wherein the query statistical data is statistical data of data generated in the SQL statement query process;

[0021] S305, the query statistical data is stored to the scheduling unit, and the final query result is output.

[0022] A readable storage medium, characterized in that: the readable storage medium stores a computer program, and the computer program is executed by a computer to realize the above-mentioned method for generating a SQL statement from a Chinese natural language, and the above-mentioned method for cross-database query.

[0023] A computer device, comprising the above-mentioned readable storage medium and a processor, and the processor can execute the computer program stored in the readable storage medium.

[0024] Compared with the prior art, the embodiments provided by the present application have at least the following beneficial effects:

[0025] The present application introduces a metadata database with richer data content, provides more complete context information for natural language generated SQL statements, improves the accuracy of natural language generated SQL statements, and makes the query results obtained by natural language generated SQL statements more accurate when applicable to a specific field, reducing the situation that the query results do not match the real intention of the user query. In addition, the SQL query method across databases in the present application can perform joint query on any multiple databases.

[0026] In order to better understand and implement, the present application is described in detail below with reference to the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0027] Figure 1 The step flowchart of the method for generating a SQL statement from a Chinese natural language in the embodiments of the present application;

[0028] Figure 2 The step flowchart of the configuration of the metadata database in the embodiments of the present application;

[0029] Figure 3 A step flow chart of the SQL statement cross-database query method in the embodiments of the present application;

[0030] Figure 4 A step flow chart of the method for generating an optimized execution plan by parsing a SQL statement in the embodiments of the present application. DETAILED DESCRIPTION

[0031] Please refer to Figure 1 , Figure 1 A step flow chart of the method for generating a SQL statement from a Chinese natural language in the embodiments of the present application.

[0032] A method for generating a SQL statement from a Chinese natural language, comprising the following steps:

[0033] S101, preprocessing a natural language statement to obtain a phrase segment contained in the natural language statement;

[0034] S102, dividing the phrase segment into multiple alternative segments;

[0035] S103, judging whether the alternative segment is surrounded by a symbol before the preprocessing, and if the alternative segment is surrounded by a symbol, marking the alternative segment as a value;

[0036] S104, if the alternative segment is not surrounded by a symbol, obtaining a synonym of the alternative segment according to a preset dictionary;

[0037] S105, matching the alternative segment and the synonym with table annotations, field annotations and tag metadata in a preset metadata database, wherein the metadata database contains table annotations, field annotations and tag metadata of multiple databases;

[0038] S106, if the alternative segment and / or the synonym matches the table annotations, marking the alternative segment as a table name; if the alternative segment and / or the synonym matches the field annotations, marking the alternative segment as a field name; if the alternative segment and / or the synonym matches the tag metadata, marking the alternative segment as a value; and obtaining a database name of a corresponding database according to the table annotations, the field annotations or the tag metadata matched by the alternative segment and the synonym;

[0039] S107, forming a new string from the database name, the table name and the field name, and the natural language statement;

[0040] S108, encoding the string to generate a vector;

[0041] S109, decoding the vector, and generating a SQL statement according to a SQL syntax rule in combination with the value.

[0042] For step S101, the natural language sentence is preprocessed to obtain the phrase segment contained in the natural language sentence;

[0043] The natural language sentence is a natural language sentence input by a user for querying, and can be a question or other sentences. In this application, the natural language sentence is mainly Chinese. For example, the natural language sentence can be “In XX year, how much is the sales of XX product in X quarter?”.

[0044] The preprocessing is to remove punctuation marks in the natural language sentence, divide the natural language sentence into multiple segments according to the punctuation marks, and the phrase segment is a segment obtained after the natural language sentence is preprocessed. For example, the phrase segments obtained after preprocessing the natural language sentence “In XX year, how much is the sales of XX product in X quarter?” are “In XX year” and “XX product in X quarter of sales how much”.

[0045] For step S102, the phrase segment is divided into multiple alternative segments;

[0046] The phrase segment is divided into multiple alternative segments, which is to cut the phrase segment by a sliding window of different lengths from 1 to a certain value to divide the phrase segment into multiple segments, and the alternative segment is a segment obtained after the phrase segment is cut by the sliding window. For example, for the phrase segments “In XX year” and “XX product in X quarter of sales how much”, after the division operation, the alternative segments obtained can be “In, X, X, year··· more, less, in X, XX, X year··· how much, in XX, XX year··· how much···” and the like.

[0047] For step S103, it is judged whether the alternative segment is surrounded by a symbol before the preprocessing, and if the alternative segment is surrounded by a symbol, the alternative segment is marked as a value;

[0048] The symbol includes single quotation marks, double quotation marks, and book title marks. The “value” is the corresponding specific content in the field of the table of the metadata database. By judging whether the alternative segment is surrounded by a symbol before the preprocessing, the alternative segment with special meaning or proper noun is identified, and the alternative segment with special meaning or proper noun is directly marked as “value”.

[0049] For step S104, if the alternative segment is not surrounded by a symbol, the synonym of the alternative segment is obtained according to a preset dictionary;

[0050] The preset dictionary is a vocabulary table configured in advance for word segmentation, and includes proper nouns and synonyms. The synonym is a word representing different forms of a noun with the same meaning. For the alternative segment not surrounded by the symbol, a corresponding synonym of the alternative segment is found by querying the preset dictionary.

[0051] For step S105, the alternative segment and the synonym are matched with table annotations, field annotations and label metadata in a preset metadata database, wherein the metadata database contains table annotations, field annotations and label metadata of multiple databases.

[0052] The preset metadata database is a metadata database set in advance. The metadata database is a database storing metadata. Further, the metadata stored in the metadata database includes table annotations, field annotations and label metadata of multiple databases. The table annotation is an annotation of the name of a table of the database. The field annotation is an annotation of the name of a field in the table of the database. The label metadata is specific content in the field in the table of the database. The alternative segment and the synonym are matched with the table annotations, the field annotations and the label metadata in the metadata database. The matching includes complete matching and partial matching. The table name, the field name or the value corresponding to the alternative segment and the synonym are identified.

[0053] For step S106, if the alternative segment and / or the synonym match the table annotation, the alternative segment is marked as a table name. If the alternative segment and / or the synonym match the field annotation, the alternative segment is marked as a field name. If the alternative segment and / or the synonym match the label metadata, the alternative segment is marked as a value. According to the table annotation, the field annotation or the label metadata matched by the alternative segment and the synonym, a database name of a corresponding database is obtained.

[0054] The table name is the name of a table in the database. The field name is the name of a field in the table in the database. The database name is the name of a database. The corresponding data is the database where the table annotation, the field annotation or the label metadata is located.

[0055] If the alternative segment and / or the synonym match the table annotation, the alternative segment is marked as the table name corresponding to the table annotation, thereby associating the alternative segment and the table annotation.

[0056] If the alternative segment and / or the synonym match the field annotation, the alternative segment is marked as the field name corresponding to the field annotation, thereby associating the alternative segment and the field annotation.

[0057] If the alternative segment and / or the synonym match the label metadata, the alternative segment is marked as value, while for the value that can determine the unique field, the value is linked with the field associated with the label metadata and table.

[0058] Finally, according to the table annotation, field annotation or label metadata matched by the alternative segment and the synonym, the database name of the database where the table annotation, field annotation or label metadata is located is obtained, so as to obtain the database name of the database where the alternative segment is located.

[0059] For the alternative segment matched to the table annotation, field annotation or label metadata, the alternative segment intersected with the alternative segment in the step S102 to perform the sliding window cutting will be removed.

[0060] For step S107, the database name, table name and field name and natural language sentence form a new string;

[0061] In the application, the string is formed by the database name, table name and field name and natural language sentence.

[0062] In one embodiment, the forming process of the string is:

[0063] A sentence head is added in front of the natural language sentence;

[0064] A table identifier is added, and a complete table name is added, the complete table name including the database name and the table name;

[0065] A field identifier is added, and a field name is added.

[0066] The sentence head is an identifier used in programming to identify the beginning of a natural language sentence, the table identifier is an identifier used in programming to identify the beginning of a table, and the field identifier is an identifier used in programming to identify the beginning of a field.

[0067] In one embodiment, it is assumed that there is a natural language sentence Q.

[0068] The natural language sentence, database name, table name and field name are spliced into a string X according to the following rules:

[0069] Add sentence beginning token [CLS]; add natural language statement Q; add delimiter token [SEP]; add table token [T]; add full table name, where the full table name includes database name and table name, and the full table name is in the form of database name + period (.) + table name; add field token [C]; add the field name; add delimiter token [SEP].

[0070] For step S108, the string is encoded to generate a vector;

[0071] The encoding is the process of converting information from one form or format to another, thereby obtaining a vector by encoding the string.

[0072] In one embodiment, the encoding step is as follows:

[0073] The string X and the natural language statement Q are encoded using a language understanding model with fine-tuned parameters to obtain the hidden layer output H. X and H Q ;

[0074] The H X and H Q Input into a bidirectional long short-term memory network to obtain the encoding h. X and h Q ;

[0075] Calculate the table encoding vector Wherein, D represents the database set, and D i It is the i-th database, where Δ is the total number of databases in the database set. It is the v-th table in the i-th database, where T is the total number of tables in the i-th database. It is a table The index in the string X, the It is h X The encoding vector of the table corresponding to the index in the table, the yes The augmented matrix consisting of the zero vector [0 0 0] and f(·) is a feedforward layer network function;

[0076] Calculate field encoding vector Among them, the Let Γ represent the j-th field in the v-th table of the i-th database, where Γ is the total number of fields in the v-th table of the i-th database. It is a field in the string X index, It is h XThe field encoding vector corresponding to the index, F t , F f , and F p are feature vectors obtained from the metadata database;

[0077] According to the table encoding vector and the field encoding vector, a table encoding vector augmented matrix is formed:

[0078]

[0079] The table encoding vector augmented matrix is calculated to form a database encoding vector:

[0080]

[0081] The data encoding vector is calculated to form a database collection encoding vector:

[0082]

[0083] The complete encoding vector h e is calculated: Q h D ].

[0084] For step S109, the vector is decoded, and according to the SQL syntax rules, the SQL statement is generated in combination with the value;

[0085] The decoding is a process of operating the vector generated by the encoding, and the result obtained by the decoding is used to generate the SQL statement according to the SQL syntax rules in combination with the value.

[0086] In one embodiment, decoding the vector to generate the SQL statement includes the following steps:

[0087] Obtain the xth time step of the decoder neural network, and the hidden layer state s x , where s1=h e , the hth (h∈[1, 2, …, H]) attention head;

[0088] Calculate the comparison function: Where the h y is the yth column vector in the encoding vector h e , the n is the number of columns in the encoding vector h e , the is a parameter matrix, and the T in the represents the transpose;

[0089] Calculate the weight index:

[0090] Computing the output of the x-th layer h-th attention head: where |Q| is the modulo of natural language sentence Q as a string, |D| is the modulo of database collection D;

[0091] Computing the attention head output at the x-th time step:

[0092] Computing the generator probability selected from the dictionary V: where W s , W y and b are learnable parameters;

[0093] Computing the probability distribution of selecting a word v from the dictionary V:

[0094] where v y represents the y-th word in the dictionary, y: v y = v represents the summation of P y (v) is the LSTM softmax probability distribution of all words in the dictionary; V

[0095] Selecting the word that maximizes P x (v):

[0096] Assembling SQL according to SQL syntax rules combined with the values.

[0097] In one embodiment, please refer to Figure 2 , Figure 2 The step flow chart for configuring the metadata database in the embodiments of the present application, the update of the metadata database includes the following steps:

[0098] S201, traversing all the preset databases;

[0099] S202, judging whether the database table structure of the database changes, if the database table structure changes, obtaining the table name, table annotation, field name and field annotation of the database through a crawler;

[0100] S203, collecting the content of the database to generate content samples, removing duplicates by column to form a set, and putting into a sample database;

[0101] S204, traversing the source code repository of the database, and judging whether the source code repository is published a new version;

[0102] ​​S205, if the source code repository releases a new version, the source code of the source code repository is obtained through a crawler, and the table name, table annotation, field name, field annotation, field type and field tag of the source code repository are obtained by using a syntax parser to parse the source code;

[0103] S206, the table name and field name of the database are associated with the table name and field name of the source code repository, the table annotation and field annotation of the database and the table annotation, field annotation, field type and field tag of the source code repository are cleaned up, and are saved to a metadata database.

[0104] For step S201, all preset databases are traversed;

[0105] The database is a repository for organizing, storing and managing data according to a data structure, and is a long-term storage in a computer, an organized, shareable and unified management of a large amount of data collection. The preset all databases are a plurality of databases set in advance, and in an embodiment, the preset all databases are a plurality of databases in the trade service field set in advance. By traversing all preset databases, the state of each database is detected.

[0106] For step S202, it is judged whether the database table structure of the database changes, and if the database table structure changes, the table name, table annotation, field name and field annotation of the database are obtained through a crawler;

[0107] The crawler is a web crawler, which refers to a program or script that automatically captures information according to certain rules. The database table structure is the table information in the database. If it is detected that the database table structure of the database changes, the table name, table annotation, field name and field annotation information of the database can be crawled from the database through the crawler.

[0108] For step S203, the content of the database is collected to generate a content sample, which is de-duplicated by column to form a set and is put into a sample database;

[0109] The content sample includes table data or part of the table data, all column data or part of the column data. The content sample of the database is collected through a crawler, and the content sample is de-duplicated by column to form a set, and finally falls into a sample database. The sample database is used for further matching of the alternative segments that are not matched to the table annotation, field annotation or tag metadata.

[0110] For step S204, the source code repository of the database is traversed, and it is judged whether the source code repository releases a new version;

[0111] The source code repository refers to a data repository for storing uncompiled text files written in accordance with certain programming language specifications, a series of human-readable computer language instructions. By traversing all source code repositories, it is determined whether each source code repository releases a new version, i.e., whether the source code repository information changes.

[0112] For step S205, if the source code repository releases a new version, the source code of the source code repository is obtained through a crawler, and the table name, table comment, field name, field comment, field type and field tag of the source code repository are obtained by using a syntax parser to parse the source code;

[0113] The syntax parsing is achieved by an algorithm design to recognize and parse the source code. For the source code repository releasing a new version, the source code in the source code repository is first crawled through the crawler, and then the syntax parser is used to parse the source code to obtain the database table entity class in the source code, and the table name, table comment, field name, field comment, field type and field tag information in the source code repository are taken out.

[0114] For step S206, the table name and field name of the database are associated with the table name and field name of the source code repository, and the table comment and field comment of the database and the table comment, field comment, field type and field tag of the source code repository are cleaned and saved to the metadata database.

[0115] For the table name, table comment, field name and field comment information in the database and the table name, table comment, field name, field comment, field type and field tag information in the source code repository, the table name and field name in the database and the table name and field name in the source code repository are associated, and the table comment and field comment of the database and the table comment, field comment, field type and field tag of the source code repository are cleaned, i.e., more accurate and detailed table comment, field comment, field type and field tag information are obtained, and finally the obtained data is saved to the metadata database.

[0116] In one embodiment, after step S206 is completed, the following steps are further included:

[0117] The administrator is notified to audit the data saved to the metadata database, further improve the data saved to the metadata database, and finally save the formal metadata to the metadata database.

[0118] In one embodiment, after step S106 is executed, the following steps are further included:

[0119] If the alternative segment and / or the synonym do not match the table annotation, field annotation or label metadata, the alternative segment and / or the synonym are fuzzy matched in a sample database in the metadata database in combination with the table name to which the closest alternative segment to the alternative segment in the natural language sentence is matched, and if the alternative segment matches a field, the alternative segment is marked as a value;

[0120] In one embodiment, if the alternative segment and / or the synonym do not match the table annotation, field annotation or label metadata, the alternative segment and / or the synonym are fuzzy matched in a sample database in the metadata database in combination with the table name to which the closest alternative segment to the alternative segment in the natural language sentence is matched, and the fuzzy matching is to obtain all field data of the type string in the table according to the table name in descending order of cosine similarity, and if there is only one field with a similarity exceeding a preset threshold, the alternative segment matches the field, and the alternative segment is marked as a value.

[0121] In one embodiment, referring to Figure 3 , Figure 3 The SQL statement cross-database query method generated by the natural language SQL statement generation method described in the above embodiment includes the following steps:

[0122] S301, adjusting the SQL statement using a scheduling unit;

[0123] S302, determining whether the SQL statement involves only one database according to the number of database names contained in the SQL statement, and if the SQL statement involves only one database, scheduling an idle execution unit to perform database query and returning the query result to the scheduling unit;

[0124] S303, if the SQL statement involves multiple databases, using the scheduling unit to parse the SQL statement and compiling to generate an optimized execution plan, wherein the optimized execution plan is an execution plan obtained by analyzing the SQL statement in combination with the content of the metadata database;

[0125] S304, obtaining a directed acyclic graph according to the optimized execution plan, scheduling the execution unit to execute the SQL statement query of each stage, merging the SQL statement query results of each stage according to the directed acyclic graph to generate a final query result, and returning the final query result and query statistical data to the scheduling unit, wherein the query statistical data is statistical data of the data generated in the SQL statement query process.

[0126] S305, store the query statistics to the scheduling unit and output the final query result.

[0127] For step S301, use the scheduling unit to adjust the SQL statement;

[0128] The scheduling unit can parse the SQL statement and call other functions, and the SQL statement is generated by the method for generating a SQL statement from a Chinese natural language described in the above embodiments. After generating the SQL statement, when starting to execute the SQL statement query, first adjust the SQL statement through the scheduling unit.

[0129] The adjustment includes adding necessary filter conditions according to user permission configuration and table configuration, such as adding filter conditions for tenants and the like; detecting tables and fields in the SQL statement, and hiding sensitive information, such as: if it is detected that a field contains sensitive information, then mask or hide the field containing the sensitive information.

[0130] For step S302, according to the number of database names contained in the SQL statement, it is judged whether the SQL statement only involves one database, if the SQL statement only involves one database, then schedule an idle execution unit to perform database query, and return the query result to the scheduling unit;

[0131] The execution unit is used to execute SQL queries. Before performing the SQL statement query, first need to judge according to the number of database names in the SQL statement whether the SQL statement query is for one database or multiple databases. If there is only one database name in the SQL statement, it means that the SQL statement query is for one database, otherwise the SQL statement query involves multiple databases. For the SQL statement query involving only one database, the scheduling unit is used to schedule an execution unit to query the SQL statement, and when the query is completed, the execution unit outputs the query result to the scheduling unit.

[0132] For step S303, if the SQL statement involves multiple databases, the scheduling unit is used to parse the SQL statement and compile an optimized execution plan, wherein the optimized execution plan is an execution plan obtained by analyzing the SQL statement and processing in combination with the content of the metadata database;

[0133] The optimized execution plan is an execution plan obtained by analyzing the SQL statement and processing in combination with the content of the metadata database.

[0134] In one embodiment, please refer to Figure 4 , Figure 4 The step flow chart for generating the optimized execution plan by parsing the SQL statement in the embodiment of the present application, the compiling generates the optimized execution plan, comprising the following steps:

[0135] S401, performing lexical analysis on the SQL statement to generate a syntax tree;

[0136] S402, constructing a parsed execution plan according to the syntax tree;

[0137] S403, combining the parsed execution plan with the information in the metadata database to generate an analyzed execution plan;

[0138] S404, optimizing the analyzed execution plan to generate a suboptimal execution plan;

[0139] S405, according to the suboptimal execution plan, calculating the query cost according to the cost model, obtaining the execution plan with the minimum query cost, and generating the optimized execution plan;

[0140] S406, generating a directed acyclic graph of the SQL execution plan according to the database involved in the optimized execution plan.

[0141] For step S401, performing lexical analysis on the SQL statement to generate a syntax tree;

[0142] The lexical analysis is a process of converting a sequence of characters into a sequence of words in computer science. The syntax tree is a graphical representation of the structure of a sentence, which represents the derivation result of the sentence and is conducive to understanding the hierarchy of the sentence syntax structure. By performing the lexical analysis on the SQL, the syntax tree is generated.

[0143] For step S402, constructing a parsed execution plan according to the syntax tree;

[0144] The parsed execution plan includes database, table, field, value, aggregation operation, filtering, grouping and other information. The parsed execution plan is obtained according to the syntax tree.

[0145] For step S403, combining the parsed execution plan with the information in the metadata database to generate an analyzed execution plan;

[0146] The parsed execution plan is obtained according to the syntax tree, so the parsed execution plan does not include any database information. Therefore, by adding the information in the metadata database, such as the table name and field name, to the parsed execution plan, the analyzed execution plan is generated.

[0147] For step S404, the analyzed execution plan is optimized to generate a suboptimal execution plan;

[0148] By setting a set of optimization rules on the analyzed execution plan, the SQL is recursively heuristically optimized to finally generate a suboptimal execution plan.

[0149] In one embodiment, the optimization rules include the following steps:

[0150] Predicate pushdown, i.e., pushing filter conditions down to a place closer to the data source to reduce the amount of table scanning, for example, moving the filter operator executed after JOIN to before JOIN;

[0151] Field pruning, i.e., removing unnecessary query fields to reduce network transmission and thus speed up query efficiency;

[0152] Constant replacement, i.e., calculating constant expressions and performing type conversion before execution to reduce the calculation amount of the execution engine when dealing with large data;

[0153] Small table-based constant expansion, i.e., if the queried table involves a small table configured in the database management, the table is queried as a subquery according to the execution plan, and the result is expanded as a constant in the execution plan;

[0154] Custom rules, i.e., extending and loading into the system in the form of Java classes to implement personalized optimization in combination with business characteristics.

[0155] For step S405, according to the suboptimal execution plan, the query cost is calculated according to the cost model to obtain an execution plan with the minimum query cost to generate an optimized execution plan;

[0156] The cost model is a model established for cost estimation of the suboptimal execution plan. For the suboptimal execution plan, the cost of CPU operation and network transmission of each operator is calculated according to the cost model to further adjust and thus obtain an optimized execution plan.

[0157] In one embodiment, generating an optimized execution plan includes the following steps:

[0158] The cross-database tables before and after the JOIN statement are exchanged in turn to derive a set of equivalent execution plans from the suboptimal execution plan;

[0159] The tables participating in the associated query are taken out, and the cost of single-table query is estimated according to the historical query statistics, the number of records of the table collected by the database metadata crawler, the execution overhead of each execution unit, and the execution cost of functions with certain weights.

[0160] Traverse each execution plan, calculate the overall query cost of each execution plan;

[0161] Select the execution plan with the minimum query cost as the optimized execution plan.

[0162] For step S406, generate a directed acyclic graph of the SQL execution plan according to the database involved in the optimized execution plan.

[0163] The directed acyclic graph refers to a loop-free directed graph. The directed acyclic graph is generated according to the database involved in the optimized execution plan, splitting the query into multiple different stages and tasks according to the dependency relationship of each task.

[0164] For SQL statements involving multiple databases, the scheduling unit is used to parse the query before execution, and then the optimized execution plan is generated according to the steps described in the above embodiments.

[0165] For step S304, according to the optimized execution plan, obtain the directed acyclic graph, schedule the execution unit, execute the SQL statement query of each stage, merge the SQL statement query results of each stage according to the directed acyclic graph, generate the final query result, and return the final query result and query statistical data to the scheduling unit, wherein the query statistical data is the statistical data of the data generated during the SQL statement query process;

[0166] The query statistical data is the query data obtained during the cross-database query of the SQL statement. According to the optimized execution plan, the scheduling unit schedules the execution unit to start executing the cross-database query of the SQL statement to obtain the query result. For the query result, according to the dependency relationship of each task in the directed acyclic graph, the query result is merged to obtain the final query result. The execution unit finally returns the final query result and the query statistical data to the scheduling unit.

[0167] For step S305, store the query statistical data to the scheduling unit and output the final query result;

[0168] The query statistical data stored in the scheduling unit can be used for cost calculation in the next cross-database query of the SQL statement. At the same time, the scheduling unit obtains the final query result returned by the execution unit, and then outputs the final query result.

[0169] In one embodiment, the cross-database query method of the SQL statement in Chinese natural language further comprises the following steps:

[0170] Configure the user's permission information, record the SQL and query result of each query.

[0171] By configuring the permission information of the user, data-level tenant control and field-level permission control can be provided. By recording the SQL and query result of each query, auditing can be performed.

[0172] The application further provides a Chinese natural language SQL statement generation and cross-database query device, comprising a readable storage medium, wherein the readable storage medium stores a computer program, and the computer program can implement the method for generating a Chinese natural language SQL statement and / or the method for cross-database query of the SQL statement in the above-described embodiments when executed by a computer.

[0173] The application further provides a computer device, comprising the readable storage medium and the processor in the above-described embodiments, and the processor can execute the computer program stored in the readable storage medium.

[0174] The above-described embodiments only express several implementation manners of the application, and the description is relatively specific and detailed, but the application scope should not be limited in this way. It should be noted that, for those skilled in the art, without departing from the concept of the application, several modifications and improvements can be made, and these all belong to the protection scope of the application.

Claims

1. A Chinese natural language generated SQL sentence method, characterized in that: The method comprises the following steps: S101, preprocessing a natural language sentence to obtain a phrase segment contained in the natural language sentence; S102, dividing the phrase segment into multiple candidate segments; S103, judging whether the candidate segment is surrounded by a symbol before the preprocessing, if the candidate segment is surrounded by the symbol, marking the candidate segment as a value, the value being a corresponding specific content in a field of a table of a metadata database; S104, if the candidate segment is not surrounded by the symbol, obtaining a synonym of the candidate segment according to a preset dictionary; S105, matching the candidate segment and the synonym with table annotations, field annotations and tag metadata in a preset metadata database, wherein the metadata database contains table annotations, field annotations and tag metadata of multiple databases; S106, if the candidate segment and / or the synonym matches the table annotation, marking the candidate segment as a table name; if the candidate segment and / or the synonym matches the field annotation, marking the candidate segment as a field name; if the candidate segment and / or the synonym matches the tag metadata, marking the candidate segment as a value; obtaining a database name of a corresponding database according to the table annotation, the field annotation or the tag metadata matched by the candidate segment and the synonym; S107, forming a new string with the database name, the table name and the field name and the natural language sentence; S108, encoding the string to generate a vector; The encoding comprises the following steps: encoding the string X and the natural language query Q using a language understanding model with fine-tuned parameters to obtain a hidden layer output H X and H Q ; The H X and H Q Input a bidirectional long short-term memory network to obtain an encoding h X and h Q ; Computing table encoding vectors v = 1, 2, …, T, D = {D i}, where D i i = 1, 2, …, Δ: where D represents a database set, D i is the ith database, Δ is the total number of databases in the database set, and is the vth table in the ith database, T is the total number of tables in the ith database, and is the table is the index in the string X, and is the h X is the encoding vector of the table corresponding to the index, and is and the augmented matrix consisting of the zero vector [0 0 0], and f(·) is a feedforward layer network function. Computing field encoding vectors where j = 1, 2, Γ: wherein the represents the jth field in the vth table in the ith database, Γ is the total number of fields in the vth table in the ith database, and is the index of the field in the string X, is the encoding vector of the field at the corresponding index in h X , F t , F f , and F p are feature vectors obtained from the metadata database; forming a table encoding vector augmented matrix according to the table encoding vector and the field encoding vector: calculating the table encoding vector augmented matrix to form a database encoding vector: calculating the data encoding vector to form a database collection encoding vector: Compute the complete code vector: h e = [h Q h D ]; S109, decoding the vector to generate an SQL statement according to an SQL syntax rule in combination with the value; The decoding comprises the following steps: obtaining an x-th time step of the decoder neural network, a hidden layer state s x wherein s1= h e , an h-th (h e [1, 2,..., H]) attention head; Computing the comparison function: wherein said h y is the yth column vector in said encoding vector h e , said n is the number of columns in said encoding vector h e , said is a parameter matrix, said T in said h denotes the transpose; Computing the weight index: Computing the output of the x-th layer h-th attention head: where |Q| is the modulo of the natural language sentence Q as a string, and |D| is the modulo of the database collection D. Computing the attention head output for the x-th time step: The generator probability is computed from the dictionary V: where W s , W y and b are learnable parameters; calculating a probability distribution of selecting a vocabulary v from the dictionary V: Wherein, the v y Let y represent the y-th word in the dictionary, where y: v y =v means that when v is satisfied y =v condition, for Summation is performed on P. V (v) is the LSTMsoftmax probability distribution of all words in the dictionary; The selection is such that P x (v) maximum vocabulary: assembling SQL according to the SQL syntax rule in combination with the value.

2. The method for generating SQL sentence of Chinese natural language according to claim 1, characterized in that: The metadata database acquisition comprises the following steps: S201, traversing all preset databases; S202, judging whether a database table structure of the database changes, if the database table structure changes, obtaining a table name, table annotations, a field name and field annotations of the database through a crawler; S203, collecting contents of the database to generate content samples, removing duplicates by column to form a set and putting into a sample database; S204, traversing a source code repository of the database to judge whether the source code repository releases a new version; S205, if the source code repository releases a new version, obtaining source code of the source code repository through a crawler and simultaneously using a syntax parser to parse the source code to obtain a table name, table annotations, a field name, field annotations, a field type and a field tag of the source code repository; S206, join the table name and field name of the database with the table name and field name of the source code repository, and clean the table annotation and field annotation of the database and the table annotation, field annotation, field type and field label of the source code repository, and save to the metadata database.

3. The method for generating SQL sentence of Chinese natural language according to claim 1, characterized in that: Further comprising the following steps: If the alternative segment and / or the synonym do not match the table annotation, field annotation or label metadata, then in the sample database in the metadata database, the alternative segment and / or synonym are combined with the table name matched by the closest alternative segment in the natural language sentence to the alternative segment, and the alternative segment is fuzzily matched, and if the alternative segment matches a field, then the alternative segment is marked as a value.

4. The method for generating SQL sentence of Chinese natural language according to claim 1, characterized in that: The forming of a new string by the database name, table name and field name and the natural language sentence comprises the following steps: Adding a sentence head in front of the natural language sentence; Adding a table identifier, and adding a complete table name, which comprises the database name and the table name; Adding a field identifier, and adding a field name.

5. A method for cross-database query based on the SQL sentence generated by the method for generating SQL sentence of Chinese natural language according to any one of claims 1-4, characterized in that: Further comprising the following steps: S301, using a scheduling unit, adjusting the SQL statement; S302, according to the number of database names contained in the SQL statement, judging whether the SQL statement involves only one database, if the SQL statement involves only one database, then scheduling an idle execution unit to perform database query, and returning the query result to the scheduling unit; S303, if the SQL statement involves multiple databases, then using the scheduling unit to parse the SQL statement, and compiling to generate an optimized execution plan, wherein the optimized execution plan is an execution plan obtained by analyzing the SQL statement in combination with the content of the metadata database; S304, according to the optimized execution plan, obtaining a directed acyclic graph, scheduling the execution unit, executing the SQL statement query of each stage, according to the directed acyclic graph, merging the SQL statement query results of each stage, generating a final query result, returning the final query result and query statistical data to the scheduling unit, wherein the query statistical data is the statistical data of the data generated in the SQL statement query process; S305, storing the query statistical data to the scheduling unit, and outputting the final query result.

6. The method of claim 5, wherein: The compiling to generate an optimized execution plan comprises the following steps: S401, performing lexical analysis on the SQL statement to generate a syntax tree; S402, according to the syntax tree, constructing a parsed execution plan; S403, combining the parsed execution plan with the information in the metadata database to generate an analyzed execution plan; S404, optimizing the analyzed execution plan to generate a suboptimal execution plan; S405, according to the suboptimal execution plan, calculating the query cost according to a cost model, obtaining an execution plan with minimum query cost, and generating an optimized execution plan; S406, according to the database involved in the optimized execution plan, generating a directed acyclic graph of the SQL execution plan.

7. A readable storage medium characterized by: The readable storage medium stores a computer program, and the computer program is executed by a computer to realize the method for generating a Chinese natural language SQL sentence according to any one of claims 1-4 and / or the method for cross-database query of the SQL sentence according to any one of claims 5-6.

8. A computer device, comprising: The readable storage medium and the processor, wherein the processor can execute the computer program stored in the readable storage medium.

Images