A SQL statement generation method, device, equipment and storage medium

Abstract

The application discloses a SQL statement generation method and device, equipment and a storage medium. The method comprises the following steps: obtaining an original SQL statement; the original SQL statement comprises main table metadata; the original SQL statement is parsed to generate a first syntax tree; based on a mapping relationship between preset main table metadata and M groups of sub-table metadata, the main table metadata in the first syntax tree is replaced by the M groups of sub-table metadata respectively to obtain M second syntax trees; and the M second syntax trees are inversely parsed to generate M sub SQL statements. In this way, according to the preset unified mapping relationship, the original SQL statement issued by the central cluster can be converted to automatically generate the sub SQL statements matching the M edge clusters. The conversion process does not require manual intervention, the conversion standard is unified, the correctness of the sub SQL statements obtained by the edge clusters is ensured, and the central cluster is facilitated to count the data of each edge cluster.

Description

Technical Field

[0001] This application relates to big data technology, and in particular to a method, apparatus, device and storage medium for generating Structured Query Language (SQL) statements. Background Technology

[0002] With the continuous development of communication and information technologies, the volume of data is growing exponentially, and the aggregation of large amounts of data has become an urgent problem to be solved.

[0003] Hadoop, as a typical big data technology, is adopted by many companies. These companies often deploy multiple Hadoop clusters in different regions or data centers, forming a "one-center, multiple-edge" deployment model. In this model, the central cluster initiates task requests, and each edge cluster responds to the tasks. Therefore, how to break down tasks according to each edge cluster, forming sub-tasks suitable for that edge cluster, and how to effectively analyze and decompose SQL, a commonly used data processing language, have become urgent problems to solve. In existing technologies, the decomposition and generation of SQL are often handled manually, specifically including the following steps: the central cluster first describes the business logic and sends the description to the edge clusters in a certain format; the edge clusters output corresponding SQL statements based on the business description and summarize the data belonging to their own edge cluster; the edge clusters report the processed data back to the central cluster.

[0004] However, the above solution has the following drawbacks: the entire process, from generating the business logic description to the conversion between the business logic and SQL, requires a lot of manual intervention. Due to the high degree of manual intervention, different people have different understandings, which leads to inconsistent SQL output. Ultimately, this will make the result data of the central cluster difficult to use or even unusable. Summary of the Invention

[0005] To address the aforementioned technical problems, this application aims to provide a method and apparatus for generating SQL statements.

[0006] The technical solution of this application is implemented as follows:

[0007] Firstly, a method for generating SQL statements is provided, which includes:

[0008] Obtain the original SQL statement; wherein, the original SQL statement includes the main table metadata;

[0009] The original SQL statement is parsed to generate a first syntax tree;

[0010] Based on the preset mapping relationship between the main table metadata and the M sets of sub-table metadata, the main table metadata in the first syntax tree is replaced by the M sets of sub-table metadata respectively to obtain M second syntax trees; where M is a positive integer;

[0011] The M second syntax trees are deparsed to generate M sub-SQL statements.

[0012] In the above scheme, the metadata includes the table name and at least one corresponding column name; the mapping relationship includes the mapping relationship between the table name and column name of the main table and the table name and column name of the child table.

[0013] In the above scheme, the step of replacing the main table metadata in the first syntax tree using the M groups of sub-table metadata based on the preset mapping relationship between the main table metadata and the M groups of sub-table metadata includes: querying the first syntax tree according to the first key field identifying the table name to determine the table name of the main table identified by the first key field; querying the first syntax tree according to the second key field identifying the first column name to determine at least one first column name of the main table identified by the second key field; wherein the first column name includes the column name of the main table; finding the table name of the M groups of sub-tables corresponding to the table name of the main table, and the first column name of the M groups of sub-tables corresponding to the first column name of the main table from the mapping relationship; wherein the first column name includes the column name of the sub-table; and replacing the table name and the first column name of the main table in the first syntax tree with the table name and the first column name of the M groups of sub-tables respectively to obtain the M second syntax trees.

[0014] In the above scheme, if the original SQL statement includes at least two main tables, the step of replacing the main table metadata in the first syntax tree using the metadata of the M groups of sub-tables based on the preset mapping relationship between the main table metadata and the metadata of the M groups of sub-tables includes: querying the first syntax tree according to the first key field to determine the table names of the at least two main tables identified by the first key field; querying the first syntax tree according to the third key field that identifies the second column name to determine the second column name of the at least two main tables identified by the third key field; wherein the second column name includes the table name and column name of the main table; finding the table name of the M groups of sub-tables corresponding to the table name of each main table and the second column name of the M groups of sub-tables corresponding to the second column name of each main table from the mapping relationship; wherein the second column name includes the table name and column name of the sub-table; and replacing the table name and second column name of each main table in the first syntax tree with the table name and second column name of the M groups of sub-tables respectively to obtain the M second syntax trees.

[0015] In the above scheme, the metadata also includes the database name of the table; the mapping relationship also includes the mapping relationship between the database name of the main table and the database name of the child table.

[0016] In the above scheme, the step of replacing the metadata of the main table in the first syntax tree with the metadata of the M groups of sub-tables based on the preset mapping relationship between the metadata of the main table and the metadata of the M groups of sub-tables further includes: querying the first syntax tree according to the first key field to determine the database name corresponding to each main table; finding the database name of the M groups of sub-tables corresponding to the database name of each main table from the mapping relationship; and replacing the database name of each main table in the first syntax tree with the database name of the M groups of sub-tables.

[0017] In the above scheme, after generating M sub-SQL statements, the method further includes: performing semantic checks on each sub-SQL statement using Hive's built-in SQL compilation function; and when the semantic checks are correct, sending the M sub-SQL statements to M edge clusters for sharding computation.

[0018] Secondly, an SQL statement generation apparatus is provided, the apparatus comprising:

[0019] The acquisition unit is used to acquire the original SQL statement; wherein the original SQL statement includes the main table metadata;

[0020] The parsing unit is used to parse the original SQL statement and generate a first syntax tree;

[0021] The replacement unit is used to replace the main table metadata in the first syntax tree based on the preset mapping relationship between the main table metadata and the M groups of sub-table metadata, and to obtain M second syntax trees; where M is a positive integer;

[0022] The de-parsing unit is used to de-parse the M second syntax trees and generate M sub-SQL statements.

[0023] Thirdly, an SQL statement generation device is provided, comprising: a processor and a memory configured to store a computer program capable of running on the processor, wherein the processor is configured to execute the steps of the aforementioned method when running the computer program.

[0024] Fourthly, a computer-readable storage medium is provided having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of the aforementioned method.

[0025] Using the above technical solution, the original SQL statements issued by the central cluster can be automatically converted according to the preset unified mapping relationship to generate sub-SQL statements that match M edge clusters. This conversion process does not require manual intervention and the conversion standard is unified, ensuring the correctness of the sub-SQL statements obtained by the edge clusters and facilitating the central cluster to collect data from each edge cluster. Attached Figure Description

[0026] Figure 1 This is a schematic diagram of the first process of the SQL statement generation method in the embodiments of this application;

[0027] Figure 2 This is a schematic diagram of the second process of the SQL statement generation method in the embodiments of this application;

[0028] Figure 3 This is a schematic diagram of the third process of the SQL statement generation method in the embodiments of this application;

[0029] Figure 4 This is a schematic diagram of the structure of the SQL statement generation device in the embodiments of this application;

[0030] Figure 5 This is a schematic diagram of the structure of the SQL statement generation device in the embodiments of this application. Detailed Implementation

[0031] In order to gain a more detailed understanding of the features and technical content of the embodiments of this application, the implementation of the embodiments of this application will be described in detail below with reference to the accompanying drawings. The accompanying drawings are for reference and illustration only and are not intended to limit the embodiments of this application.

[0032] Example 1

[0033] This application provides a method for generating SQL statements. Figure 1 This is a schematic diagram of the first process of the SQL statement generation method in the embodiments of this application, as shown below. Figure 1 As shown, the SQL statement generation method can specifically include:

[0034] Step 101: Obtain the original SQL statement; wherein, the original SQL statement includes the main table metadata;

[0035] It should be noted that the master table metadata describes the attributes of the master table. The master table metadata includes at least the table name and at least one corresponding column name.

[0036] In practical applications, the business description serves as the input to the central cluster, and the output is the original SQL statement corresponding to the business description. The SQL statement generation device obtains the original SQL statement sent by the central cluster.

[0037] For example, if the original SQL statement issued by the central cluster (which can be understood as the head office) includes two main tables, Table 1 is the metadata table for the two main tables. As shown in Table 1, one main table is named "employees" and has two column names (also referred to as "main column names"), namely "id" and "name". The other main table is named "orders" and has three column names, namely "id", "price", and "employee_id".

[0038] Table 1. Metadata of the two main tables

[0039]

[0040] Step 102: Parse the original SQL statement to generate a first syntax tree;

[0041] It should be noted that before implementing this step, the original SQL statement needs to be semantically checked. Once the semantic check is confirmed to be correct, the original SQL statement is parsed using Hive's built-in parsing tools to generate the first syntax tree.

[0042] In practical applications, when performing semantic checks on raw SQL statements, the SQL statement generation device is not bound to the central cluster Hive database and does not call the central cluster Hive database to perform actual SQL operations. Instead, it performs semantic checks by simulating a Hive database. Specifically, it simulates a Hive environment within a process, creates the corresponding database and tables, and then calls the internal Hive SQL compilation method to compile the user-submitted raw SQL statement. The semantic correctness is determined by the compilation result. If the semantic check is successful based on the compilation result, the raw SQL statement is parsed; if the semantic check is incorrect based on the compilation result, a message indicating a semantic error is displayed on the SQL statement generation device, and parsing is not performed.

[0043] Step 103: Based on the preset mapping relationship between the main table metadata and the M groups of sub-table metadata, replace the main table metadata in the first syntax tree with the M groups of sub-table metadata respectively to obtain M second syntax trees; where M is a positive integer;

[0044] It should be noted that subtable metadata describes the attributes of the subtable. Subtable metadata includes at least the subtable name (also referred to as "subtable name") and at least one corresponding column name (also referred to as "subcolumn name").

[0045] In practical applications, before implementing this step, the table and column information of the central cluster and each edge cluster are associated. This involves setting up the mapping relationship between the table names and column names of the main table in the central cluster and the table names and column names of the child tables in the edge clusters. During the replacement operation, the table names and column names of the M groups of child tables are used to replace the table names and column names of the main table in the first syntax tree, resulting in M ​​second syntax trees. Here, establishing the mapping relationship provides the central cluster administrator with a unified view of the table relationships and facilitates the SQL statement generation device in performing the replacement operation.

[0046] For example, if there are three edge clusters (which can be understood as three subsidiaries under a head office), the head office needs to collect data corresponding to at least one column name in a table of each subsidiary. However, the table names and corresponding column names of the subsidiary sub-tables may differ from those of the head office's main table, making it impossible to collect data. Therefore, a mapping relationship is pre-set between the table names and corresponding column names of each subsidiary sub-table and the table names and corresponding column names of the head office's main table. Here, the mapping relationship between the main table name and the table names of each sub-table is shown in Table 2.

[0047] Table 2 shows the mapping relationship between the names of the main table and the names of each sub-table.

[0048] main table name Company 1 Sub-table Name Company 2 Sub-table Name Company 3 Sub-table Names employees e employees_zj guangdong_emp orders o orders_zj guangdong_ord

[0049] As shown in Table 2, the main table is named employees, the corresponding sub-table for company 1 is named e, the corresponding sub-table for company 2 is named employees_zj, and the corresponding sub-table for company 3 is named guangdong_emp. The main table is named orders, the corresponding sub-table for company 1 is named o, the corresponding sub-table for company 2 is named orders_zj, and the corresponding sub-table for company 3 is named guangdong_ord.

[0050] Table 3 shows the mapping relationship between at least one column name in the main table and at least one column name in each sub-table. For example, if the main table is named `employees`, and the main columns are `id` and `name`, the corresponding sub-columns for company 1 are `eid` and `ename`, for company 2 are `id_zj` and `name_zj`, and for company 3 are `id` and `name`. Similarly, if the main table is named `orders`, and the main columns are `id`, `price`, and `employee_id`, the corresponding sub-columns for company 1 are `oid`, `oprice`, and `oeid`, for company 2 are `id_zj`, `price_zj`, and `eid_zj`, and for company 3 are `id`, `price`, and `eid`.

[0051] Table 3 shows the mapping relationship between at least one column name in the main table and at least one column name in each sub-table.

[0052]

[0053] When performing a replacement operation on the metadata of the main table in the first syntax tree, the corresponding replacement is performed on Table 1 based on the sub-table name e, sub-column name eid, and ename of Company 1 in the mapping relationship, to obtain the metadata table of the sub-table of Company 1 in the second syntax tree (i.e., Table 3).

[0054] Table 3. Metadata table of Company 1 sub-table

[0055]

[0056] When performing a replacement operation on the metadata of the main table in the first syntax tree, Table 1 is replaced according to the sub-table name e, sub-column name eid, and ename of Company 2 in the mapping relationship, to obtain the metadata table of the sub-table of Company 2 in the second syntax tree (i.e., Table 4).

[0057] Table 4. Metadata table of Company 2 sub-table

[0058]

[0059]

[0060] When performing a replacement operation on the metadata of the main table in the first syntax tree, Table 1 is replaced according to the sub-table name e, sub-column name eid, and ename of Company 3 in the mapping relationship, to obtain the metadata table of the sub-table of Company 3 in the second syntax tree (i.e., Table 5).

[0061] Table 5. Metadata table of Company 3 sub-tables

[0062]

[0063] It should also be noted that the main table metadata includes the database name of the main table, and the child table metadata includes the database name of the child table. Therefore, the mapping relationship also includes the mapping relationship between the database names of the main table and the database names of the child tables. When performing a replacement operation, it is also necessary to use the database names of the M groups of child tables to replace the database name of the main table in the first syntax tree.

[0064] Here, compared to directly searching and replacing the table names, column names, and database names in the original SQL statement, searching and replacing the table names, column names, and database names in the original SQL statement through the first syntax tree is more accurate.

[0065] Step 104: Perform reverse parsing on the M second syntax trees to generate M sub-SQL statements.

[0066] Here, using a simulated Hive environment, the built-in de-parse tool of Hive is used to de-parse M second syntax trees and generate M sub-SQL statements.

[0067] In some embodiments, after generating M sub-SQL statements, the method further includes: performing semantic checks on each sub-SQL statement using Hive's built-in SQL compilation function; and when the semantic checks are correct, sending the M sub-SQL statements to M edge clusters for sharding computation.

[0068] In other words, before sending the M sub-SQL statements to the M edge clusters for sharding computation, each sub-SQL statement needs to be compiled using Hive's built-in SQL compilation method. The semantics are then checked based on the compilation results. If the semantics check is successful based on the compilation results, the M sub-SQL statements are sent to the M edge clusters for sharding computation. If the semantics check is incorrect based on the compilation results, the corresponding sub-SQL statement will be displayed on the SQL statement generation device, indicating a semantic error, and therefore cannot be sent to the M edge clusters for sharding computation.

[0069] Here, the execution entity for steps 101 to 104 can be the processor of the SQL statement generation device.

[0070] Using the above technical solution, the original SQL statements issued by the central cluster can be automatically converted according to the preset unified mapping relationship to generate sub-SQL statements that match M edge clusters. This conversion process does not require manual intervention and the conversion standard is unified, ensuring the correctness of the sub-SQL statements obtained by the edge clusters and facilitating the central cluster to collect data from each edge cluster.

[0071] Example 2

[0072] Based on the above embodiments, this application provides a specific SQL statement generation method for cases where the original SQL statement does not include a related table, i.e., it includes one table. Figure 2 This is a schematic diagram of the second process of the SQL statement generation method in the embodiments of this application.

[0073] like Figure 2 As shown, the specific steps include the following:

[0074] Step 201: Obtain the original SQL statement; wherein, the original SQL statement includes the main table metadata;

[0075] It should be noted that the master table metadata is information describing the attributes of the master table. For example, the master table metadata includes at least the table name of the master table and at least one corresponding first column name (i.e., the column name of the master table).

[0076] In practical applications, the business description serves as the input to the central cluster, and the output is the original SQL statement corresponding to the business description. The SQL statement generation device obtains the original SQL statement sent by the central cluster.

[0077] Step 202: Parse the original SQL statement to generate a first syntax tree;

[0078] It should be noted that before implementing this step, the original SQL statement needs to be semantically checked. Once the semantic check is confirmed to be correct, the original SQL statement is parsed using Hive's built-in parsing tools to generate the first syntax tree.

[0079] For example, if the original SQL statement is: select a, todate(b), c from t where d! = 0, the parsed first syntax tree is:

[0080]

[0081] Step 203: Query the first syntax tree based on the first key field that identifies the table name to determine the name of the main table identified by the first key field;

[0082] It should be noted that the first syntax tree includes at least the first key field that identifies the table name and the corresponding main table name. Therefore, the main table name can be determined by querying the first key field in the first syntax tree.

[0083] Here, based on the example above, TOK_TABNAME is the first key field, and t is the name of the main table. Specifically, when querying the first syntax tree and finding the TOK_TABNAME field, the next field is the name of the main table, t.

[0084] Step 204: Query the first syntax tree based on the second key field that identifies the first column name to determine at least one first column name of the main table identified by the second key field; wherein, the first column name includes the column name of the main table;

[0085] It should be noted that the first syntax tree also includes a second key field that identifies the column name and at least one first column name of the corresponding main table (i.e., the column name of the main table). Therefore, by querying the second key field in the first syntax tree, at least one first column name of the main table can be determined.

[0086] Here, based on the example above, TOK_TABLE_OR_COL is the second key field, and a, b, c, and d are the column names of the main table, respectively. Specifically, when querying the first syntax tree and finding the TOK_TABLE_OR_COL field, the next field is the first column name of the main table, which is a, b, c, and d.

[0087] Step 205: Based on the preset mapping relationship between the main table metadata and the M group sub-table metadata, find the table name of the M group sub-table corresponding to the table name of the main table, and the first column name of the M group sub-table corresponding to the first column name of the main table; wherein, the first column name includes the column name of the sub-table;

[0088] It should be noted that subtable metadata describes the attributes of the subtable. Subtable metadata includes at least the subtable name and at least one corresponding first column name (i.e., the subtable column name).

[0089] In practical applications, before implementing this step, it's necessary to establish a connection between the table and column information of the central cluster and each edge cluster. This involves setting up the mapping relationship between the table names and column names of the main table in the central cluster and the table names and column names of the child tables in the edge clusters. Before performing the replacement operation, it's necessary to find the table names of the M groups of child tables corresponding to the table name of the main table, and the first column name of the M groups of child tables corresponding to the first column name of the main table. Establishing this mapping relationship provides the central cluster administrator with a unified view of the table relationships and facilitates the SQL statement generation device in performing the replacement operation.

[0090] Based on the above example, if we look up the table name t1 of a set of sub-tables corresponding to the table name t of the main table, and the first column names a1, b1, c1, and d1 of a set of sub-tables corresponding to the first column names a, b, c, and d of the main table respectively, according to the mapping relationship.

[0091] Step 206: Replace the table name and first column name of the main table in the first syntax tree with the table name and first column name of the M sub-tables respectively to obtain the M second syntax trees;

[0092] Based on the above example, after replacing the table name and the first column name of the main table in the first syntax tree with the table name and the first column name of the sub-table respectively, the corresponding second syntax tree is:

[0093]

[0094]

[0095] Based on steps 203 to 206, in some embodiments, the main table metadata also includes the database name of the main table, the sub-table metadata also includes the database name of the sub-table, and the mapping relationship also includes the mapping relationship between the database names of the main table and the database names of the sub-tables; the first syntax tree is queried according to the first key field to determine the database name corresponding to each main table; the database names of the M groups of sub-tables corresponding to the database name of each main table are found from the mapping relationship; and the database names of each main table in the first syntax tree are replaced respectively using the database names of the M groups of sub-tables.

[0096] In other words, when performing the replacement operation, it is also necessary to use the database names of the M group of sub-tables to replace the database names of the main table in the first syntax tree.

[0097] For example, if the main table metadata also includes the main table's database name and alias, then the parsed first syntax tree can be:

[0098]

[0099] Here, `test` is the database name of the main table, `t` is the table name of the main table, and `abc` is the alias of the main table. The database names and table names of the M sub-tables are used to replace the database name `test` and the table name `t` of the main table, respectively, while the alias of the main table remains unchanged.

[0100] Here, compared to directly searching and replacing the table names, column names, and database names in the original SQL statement, searching and replacing the table names, column names, and database names in the original SQL statement through the first syntax tree is more accurate.

[0101] Step 207: Perform reverse parsing on the M second syntax trees to generate M sub-SQL statements.

[0102] Based on the above example, after re-parsing the second syntax tree of the example in step 206, the corresponding sub-SQL statement is: select a1, todate(b1), c1 from t1 where d1! = 0.

[0103] In some embodiments, after generating M sub-SQL statements, the method further includes: performing semantic checks on each sub-SQL statement using Hive's built-in SQL compilation function; and when the semantic checks are correct, sending the M sub-SQL statements to M edge clusters for sharding computation.

[0104] Using the above technical solution, the original SQL statements issued by the central cluster can be automatically converted according to the preset unified mapping relationship to generate sub-SQL statements that match M edge clusters. This conversion process does not require manual intervention and the conversion standard is unified, ensuring the correctness of the sub-SQL statements obtained by the edge clusters and facilitating the central cluster to collect data from each edge cluster.

[0105] Example 3

[0106] Based on the above embodiments, this application provides another specific method for generating SQL statements that addresses situations where the original SQL statement includes related tables, i.e., at least two main tables, with column names in each main table having a certain relationship. Figure 3 This is a schematic diagram of the third process of the SQL statement generation method in the embodiments of this application.

[0107] like Figure 3 As shown, the specific steps include the following:

[0108] Step 301: Obtain the original SQL statement; wherein, the original SQL statement includes the main table metadata;

[0109] It should be noted that the main table metadata describes the attributes of the main table. For example, the main table metadata includes at least the table name of the main table and at least one corresponding second column name; wherein, when the column name of the main table is the same as the column name of the child table (i.e., there is a direct relationship), the second column name includes the table name of the main table and the column name; when the column name of the main table is different from the column name of the child table, the second column name includes either the column name or the table name of the main table and the column name.

[0110] In practical applications, the business description serves as the input to the central cluster, and the output is the original SQL statement corresponding to the business description. The SQL statement generation device obtains the original SQL statement sent by the central cluster.

[0111] Step 302: Parse the original SQL statement to generate a first syntax tree;

[0112] It should be noted that before implementing this step, the original SQL statement needs to be semantically checked. Once the semantic check is confirmed to be correct, the original SQL statement is parsed using Hive's built-in parsing tools to generate the first syntax tree.

[0113] For example, if the original SQL statement is: from t1 join t2 on t1.c1=t2.c1 or from t1, t2 where t1.c1=t2.c1, the parsed first syntax tree is:

[0114]

[0115] The TOK_JOIN field indicates that there is a relationship between tables t1 and t2.

[0116] Step 303: Query the first syntax tree based on the first key field to determine the table names of the at least two main tables identified by the first key field;

[0117] It should be noted that the first syntax tree includes at least two first key fields that identify the table name and at least two table names of the main tables. Therefore, by querying the first key fields in the first syntax tree, the table name of each main table can be determined.

[0118] Here, based on the example above, TOK_TABNAME is the first key field, and t1 and t2 are the table names of the main table, respectively. Specifically, when querying the first syntax tree and finding the TOK_TABNAME field, the next field is the table name of the main table, which is t1 and t2 respectively.

[0119] Step 304: Query the first syntax tree based on the third key field that identifies the second column name to determine the second column name of the at least two main tables identified by the third key field; wherein, the second column name includes the table name and column name of the main table;

[0120] It should be noted that the first syntax tree also includes a third key field that identifies the second column name and at least one second column name of the corresponding main table (i.e., including the table name and column name of the main table). Therefore, by querying the third key field in the first syntax tree, at least one second column name of the main table can be determined.

[0121] Here, based on the example above, TOK_TABLE_OR_COL is the third key field, and t1.c1 and t2.c1 are the column names of the main table, respectively. Specifically, when querying the first syntax tree and finding the TOK_TABLE_OR_COL field, the next field is the name of the main table, and the next field after that is the name of the column in the main table, which together are represented as t1.c1 and t2.c1, respectively.

[0122] The second column names t1.c1 and t2.c1 mentioned above both include c1 in t1 and t2. Using the second column name format can accurately distinguish between t1 and t2.

[0123] Step 305: Find the table names of the M groups of sub-tables corresponding to the table name of each main table, and the second column names of the M groups of sub-tables corresponding to the second column name of each main table from the mapping relationship; wherein, the second column name includes the table name and column name of the sub-table;

[0124] It should be noted that subtable metadata describes the attributes of the subtable. Subtable metadata includes at least the subtable name and at least one corresponding second column name (i.e., the subtable name and column name).

[0125] In practical applications, before implementing this step, the table and column information of the central cluster and each edge cluster are associated. This involves setting up the mapping relationship between the table names and column names of the main tables in the central cluster and the table names and column names of the sub-tables in the edge clusters. Before performing the replacement operation, it is necessary to find, from the mapping relationship, the table names of the M groups of sub-tables corresponding to the table names of each main table, and the second column names of the M groups of sub-tables corresponding to the second column names of each main table. Establishing this mapping relationship provides the central cluster administrator with a unified view of the table relationships and facilitates the SQL statement generation device in performing the replacement operation.

[0126] Based on the above example, if we look up the table names t3 and t4 of a set of sub-tables corresponding to the table names t1 and t2 of the main table, and the first column names t3.c2 and t3.c2 of a set of sub-tables corresponding to the second column names t1.c1 and t2.c1 of the main table, respectively.

[0127] Step 306: Replace the table name and second column name of each main table in the first syntax tree with the table name and second column name of the M sub-tables respectively to obtain the M second syntax trees;

[0128] Based on the above example, after replacing the table name and second column name of the main table in the first syntax tree with the table name and second column name of the sub-table respectively, the corresponding second syntax tree is:

[0129]

[0130] Based on steps 303 to 306, in some embodiments, the main table metadata also includes the database name of the main table, the sub-table metadata also includes the database name of the sub-table, and the mapping relationship also includes the mapping relationship between the database names of the main table and the database names of the sub-tables; the first syntax tree is queried according to the first key field to determine the database name corresponding to each main table; the database names of the M groups of sub-tables corresponding to the database name of each main table are found from the mapping relationship; and the database names of each main table in the first syntax tree are replaced respectively using the database names of the M groups of sub-tables.

[0131] In other words, when performing the replacement operation, it is also necessary to use the database names of the M group of sub-tables to replace the database names of each of the at least two main tables in the first syntax tree.

[0132] For example, if the main table metadata also includes the main table's database name and alias, then the parsed first syntax tree can be:

[0133]

[0134]

[0135] Here, `test` is the database name of the main table, `t` is the table name of the main table, and `abc` is the alias of the main table. The database names and table names of the M sub-tables are used to replace the database name `test` and the table name `t` of the main table, respectively, while the alias of the main table remains unchanged.

[0136] Here, compared to directly searching and replacing table names, column names, and database names in the original SQL statement, searching and replacing table names, column names, and database names in the original SQL statement through the first syntax tree is more accurate.

[0137] Step 307: Perform reverse parsing on the M second syntax trees to generate M sub-SQL statements.

[0138] Based on the above example, after re-parsing the second syntax tree of the example in step 306, the corresponding sub-SQL statement is: from t3 join t4 on t3.c2=t4.c2 or from t3, t4 where t3.c2=t4.c2.

[0139] In some embodiments, after generating M sub-SQL statements, the method further includes: performing semantic checks on each sub-SQL statement using Hive's built-in SQL compilation function; and when the semantic checks are correct, sending the M sub-SQL statements to M edge clusters for sharding computation.

[0140] Using the above technical solution, the original SQL statements issued by the central cluster can be automatically converted according to the preset unified mapping relationship to generate sub-SQL statements that match M edge clusters. This conversion process does not require manual intervention and the conversion standard is unified, ensuring the correctness of the sub-SQL statements obtained by the edge clusters and facilitating the central cluster to collect data from each edge cluster.

[0141] Example 4

[0142] To implement the method of the embodiments of this application, based on the same inventive concept, an SQL statement generation device is also provided in the embodiments of this application. Figure 4 This is a schematic diagram of the structure of the SQL statement generation device in the embodiments of this application.

[0143] like Figure 4 As shown, the SQL statement generation device specifically includes:

[0144] Acquisition unit 401 is used to acquire the original SQL statement; wherein, the original SQL statement includes the main table metadata;

[0145] Parsing unit 402 is used to parse the original SQL statement and generate a first syntax tree;

[0146] Replacement unit 403 is used to replace the main table metadata in the first syntax tree based on the preset mapping relationship between the main table metadata and the M groups of sub-table metadata, and to obtain M second syntax trees; where M is a positive integer;

[0147] The unparse unit 404 is used to unparse the M second syntax trees to generate M sub-SQL statements.

[0148] In some embodiments, the metadata includes the table name and at least one corresponding column name; the mapping relationship includes the mapping relationship between the table name and column name of the main table and the table name and column name of the child table.

[0149] In some embodiments, the apparatus includes: a replacement unit 403, specifically configured to: query the first syntax tree based on a first key field identifying a table name to determine the table name of the main table identified by the first key field; query the first syntax tree based on a second key field identifying a first column name to determine at least one first column name of the main table identified by the second key field; wherein the first column name includes the column name of the main table; search for the table names of M groups of sub-tables corresponding to the table name of the main table, and the first column names of the M groups of sub-tables corresponding to the first column name of the main table, from the mapping relationship; wherein the first column name includes the column name of the sub-table; and replace the table name and the first column name of the main table in the first syntax tree with the table names and the first column names of the M groups of sub-tables respectively to obtain the M second syntax trees.

[0150] In some embodiments, the apparatus includes: a replacement unit 403, specifically configured to: if the original SQL statement includes at least two main tables, query the first syntax tree based on the first key field to determine the table names of the at least two main tables identified by the first key field; query the first syntax tree based on the third key field identifying the second column name to determine the second column name of the at least two main tables identified by the third key field; wherein the second column name includes the table name and column name of the main table; search from the mapping relationship for the table names of M groups of sub-tables corresponding to the table name of each main table, and the second column name of the M groups of sub-tables corresponding to the second column name of each main table; wherein the second column name includes the table name and column name of the sub-table; and replace the table name and second column name of each main table in the first syntax tree with the table names and second column names of the M groups of sub-tables respectively to obtain the M second syntax trees.

[0151] In some embodiments, the metadata also includes the database name of the table; the mapping relationship also includes the mapping relationship between the database name of the main table and the database name of the child table.

[0152] In some embodiments, the apparatus includes: a replacement unit 403, specifically configured to query the first syntax tree based on the first key field to determine the database name corresponding to each main table; search for the database names of M groups of sub-tables corresponding to the database name of each main table from the mapping relationship; and replace the database name of each main table in the first syntax tree with the database names of the M groups of sub-tables respectively.

[0153] In some embodiments, after generating M sub-SQL statements, each sub-SQL statement is semantically checked using Hive's built-in SQL compilation function; if the semantic check is correct, the M sub-SQL statements are sent to M edge clusters for sharding computation.

[0154] Using the above technical solution, the original SQL statements issued by the central cluster can be automatically converted according to the preset unified mapping relationship to generate sub-SQL statements that match M edge clusters. This conversion process does not require manual intervention and the conversion standard is unified, ensuring the correctness of the sub-SQL statements obtained by the edge clusters and facilitating the central cluster to collect data from each edge cluster.

[0155] This application also provides another SQL statement generation device. Figure 5 This is a schematic diagram of the structure of the SQL statement generation device in an embodiment of this application. Figure 5 As shown, the SQL statement generation device includes: a processor 501 and a memory 502 configured to store computer programs capable of running on the processor;

[0156] When the processor 501 is configured to run a computer program, it executes the method steps described in the foregoing embodiments.

[0157] Of course, in practical applications, such as Figure 5 As shown, the various components in this SQL statement generation device are coupled together via bus system 503. It can be understood that bus system 503 is used to implement communication between these components. In addition to a data bus, bus system 503 also includes a power bus, a control bus, and a status signal bus. However, for clarity, in... Figure 5 The general designated all buses as Bus System 503.

[0158] In practical applications, the aforementioned processor can be at least one of the following: Application-Specific Integrated Circuit (ASIC), Digital Signal Processing Device (DSPD), Programmable Logic Device (PLD), Field-Programmable Gate Array (FPGA), controller, microcontroller, and microprocessor. It is understood that, for different devices, the electronic devices used to implement the functions of the aforementioned processor can also be other types, and the embodiments of this application do not specifically limit this.

[0159] The aforementioned memory can be volatile memory, such as random-access memory (RAM); or non-volatile memory, such as read-only memory (ROM), flash memory, hard disk drive (HDD), or solid-state drive (SSD); or a combination of the above types of memory, and provides instructions and data to the processor.

[0160] In an exemplary embodiment, this application also provides a computer-readable storage medium for storing a computer program.

[0161] Optionally, the computer-readable storage medium can be applied to any of the methods in the embodiments of this application, and the computer program causes the computer to execute the corresponding processes implemented by the processor in the various methods of the embodiments of this application. For the sake of brevity, it will not be described in detail here.

[0162] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are merely illustrative. For example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods, such as: multiple units or components can be combined, or integrated into another system, or some features can be ignored or not executed. In addition, the coupling, direct coupling, or communication connection between the various components shown or discussed can be through some interfaces, and the indirect coupling or communication connection between devices or units can be electrical, mechanical, or other forms.

[0163] The units described above as separate components may or may not be physically separate. The components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the units may be selected to achieve the purpose of this embodiment according to actual needs.

[0164] Furthermore, in the various embodiments of the present invention, all functional units can be integrated into one processing module, or each unit can be a separate unit, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or in a combination of hardware and software functional units. Those skilled in the art will understand that all or part of the steps of the above method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, it performs the steps of the above method embodiments. The aforementioned storage medium includes various media capable of storing program code, such as mobile storage devices, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0165] The methods disclosed in the several method embodiments provided in this application can be arbitrarily combined without conflict to obtain new method embodiments.

[0166] The features disclosed in the several product embodiments provided in this application can be arbitrarily combined without conflict to obtain new product embodiments.

[0167] The features disclosed in the several method or device embodiments provided in this application can be arbitrarily combined without conflict to obtain new method or device embodiments.

[0168] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for generating Structured Query Language (SQL) statements, characterized in that, The method includes: Obtain the original SQL statement; wherein, the original SQL statement includes the main table metadata; The original SQL statement is parsed to generate a first syntax tree; Based on the preset mapping relationship between the main table metadata and the M sets of sub-table metadata, the main table metadata in the first syntax tree is replaced by the M sets of sub-table metadata respectively to obtain M second syntax trees; where M is a positive integer; The M second syntax trees are deparsed to generate M sub-SQL statements; The method, based on a preset mapping relationship between main table metadata and M groups of sub-table metadata, uses the M groups of sub-table metadata to replace the main table metadata in the first syntax tree, including: querying the first syntax tree based on a first key field identifying the table name to determine the table name of the main table identified by the first key field; querying the first syntax tree based on a second key field identifying the first column name to determine at least one first column name of the main table identified by the second key field; wherein the first column name includes the column name of the main table; finding the table name of the M groups of sub-tables corresponding to the table name of the main table, and the first column name of the M groups of sub-tables corresponding to the first column name of the main table from the mapping relationship; wherein the first column name includes the column name of the sub-table; and replacing the table name and first column name of the main table in the first syntax tree with the table name and first column name of the M groups of sub-tables respectively to obtain the M second syntax trees; If the original SQL statement includes at least two main tables, the step of replacing the main table metadata in the first syntax tree using the metadata of the M groups of sub-tables based on the preset mapping relationship between the main table metadata and the metadata of the M groups of sub-tables includes: querying the first syntax tree according to the first key field to determine the table names of the at least two main tables identified by the first key field; querying the first syntax tree according to the third key field that identifies the second column name to determine the second column name of the at least two main tables identified by the third key field; wherein the second column name includes the table name and column name of the main table; finding the table name of the M groups of sub-tables corresponding to the table name of each main table and the second column name of the M groups of sub-tables corresponding to the second column name of each main table from the mapping relationship; wherein the second column name includes the table name and column name of the sub-table; and replacing the table name and second column name of each main table in the first syntax tree with the table name and second column name of the M groups of sub-tables respectively to obtain the M second syntax trees.

2. The method according to claim 1, characterized in that, The metadata includes the table name and at least one corresponding column name; The mapping relationship includes the mapping relationship between the table name and column name of the main table and the table name and column name of the child table.

3. The method according to claim 2, characterized in that, The metadata also includes the database name of the table; The mapping relationship also includes the mapping relationship between the database name of the main table and the database name of the child table.

4. The method according to claim 3, characterized in that, The method of replacing the main table metadata in the first syntax tree using the M groups of sub-table metadata based on the preset mapping relationship between the main table metadata and the M groups of sub-table metadata also includes: Based on the first key field, query the first syntax tree to determine the database name corresponding to each main table; Find the database name of the M groups of sub-tables corresponding to the database name of each main table from the mapping relationship; The database name of each main table in the first syntax tree is replaced using the database names of the M group of sub-tables.

5. The method according to claim 1, characterized in that, After generating M sub-SQL statements, the method further includes: The semantics of each sub-SQL statement are checked using Hive's built-in SQL compilation function; Once the semantic check is confirmed to be correct, the M sub-SQL statements are sent to the corresponding M edge clusters for sharding computation.

6. An SQL statement generation device, characterized in that, The device includes: The acquisition unit is used to acquire the original SQL statement; wherein the original SQL statement includes the main table metadata; The parsing unit is used to parse the original SQL statement and generate a first syntax tree; The replacement unit is used to replace the main table metadata in the first syntax tree based on the preset mapping relationship between the main table metadata and the M groups of sub-table metadata, and to obtain M second syntax trees; where M is a positive integer; The de-parsing unit is used to de-parse the M second syntax trees and generate M sub-SQL statements; The replacement unit is specifically configured to: query the first syntax tree based on a first key field identifying the table name to determine the table name of the main table identified by the first key field; query the first syntax tree based on a second key field identifying the first column name to determine at least one first column name of the main table identified by the second key field; wherein the first column name includes the column name of the main table; search the mapping relationship for the table names of the M groups of sub-tables corresponding to the table name of the main table, and the first column names of the M groups of sub-tables corresponding to the first column name of the main table; wherein the first column name includes the column name of the sub-table; and replace the table name and the first column name of the main table in the first syntax tree with the table names and the first column names of the M groups of sub-tables respectively to obtain the M second syntax trees; The replacement unit is further configured to: if the original SQL statement includes at least two main tables, query the first syntax tree based on the first key field to determine the table names of the at least two main tables identified by the first key field; query the first syntax tree based on the third key field identifying the second column name to determine the second column name of the at least two main tables identified by the third key field; wherein the second column name includes the table name and column name of the main table; search the mapping relationship for the table names of the M groups of sub-tables corresponding to the table name of each main table, and the second column name of the M groups of sub-tables corresponding to the second column name of each main table; wherein the second column name includes the table name and column name of the sub-table; and replace the table name and second column name of each main table in the first syntax tree with the table names and second column names of the M groups of sub-tables respectively to obtain the M second syntax trees.

7. An SQL statement generation device, characterized in that, The SQL statement generation device includes: a processor and a memory configured to store computer programs capable of running on the processor. Wherein, when the processor is configured to run the computer program, it performs the steps of the method according to any one of claims 1 to 5.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the computer program implements the steps of the method according to any one of claims 1 to 5.

Images