A structured query language task execution method, device and electronic device

Abstract

The embodiment of the present application provides a structured query language task execution method, device and electronic device, which relates to the field of data processing technology. The method includes: obtaining a target SQL statement for a target database; determining the physical target table involved in the target SQL statement as the first physical target table; in each first physical target table, determining the target table that does not exist in the target database as the second physical target table; for each second physical target table, determining the selection statement associated with the second physical target table in the target SQL statement, and generating the second physical target table in the target database according to the fields involved in the selection statement; based on the physical source data table and the physical target table existing in the target database, executing the SQL task indicated by the target SQL statement. Through the embodiment of the present application, when the physical target table changes, the computing engine can also accurately execute the task SQL.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a structured query language task execution method, device and electronic device. Background Art

[0002] With the explosive growth of data volume, the demand for efficient and accurate data query and analysis has also increased. In the field of big data processing and analysis, when executing SQL (Structured Query Language), due to the different formats of the source table and target table supported by the computing engine and the computer, it is usually necessary to first convert the data in the physical source table into the format supported by the computing engine, and convert the format supported by the computing engine into the format supported by the computer (hereinafter referred to as the standard format). Only then can the computing engine perform operations such as querying from the physical source table, and then obtain the target table in the format supported by the computer. However, each time the task SQL is executed, the format of the source table and the target table needs to be standardized, and the process is relatively cumbersome. In order to simplify the process of executing SQL tasks, the related technology is to permanently store the source table corresponding to the physical source table and the target table corresponding to the physical target table in the computing engine, so that there is no need to standardize the format each time the SQL task is executed.

[0003] However, if the physical source table and the physical target table are changed, since the source table and the target table have been permanently stored in the calculation engine, the data in the source table and the target table stored in the calculation engine are not the same as the data in the new physical source table and the new physical target table. The calculation engine cannot execute the SQL task based on the data in the new physical source table and the new physical target table, resulting in the inability to execute the SQL task. Summary of the invention

[0004] The purpose of the embodiments of the present application is to provide a structured query language task execution method, device and electronic device to improve the robustness of SQL task execution. The specific technical solution is as follows:

[0005] According to a first aspect of an embodiment of the present application, a structured query language task execution method is provided, the method comprising:

[0006] Obtaining a target structured query language statement for a target database;

[0007] Determine a physical target table involved in the target structured query language statement as a first physical target table;

[0008] In each of the first physical target tables, a target table that does not exist in the target database is determined as a second physical target table;

[0009] For each of the second physical target tables, determining a select statement associated with the second physical target table in the target structured query language statement, and generating the second physical target table in the target database according to the fields involved in the select statement;

[0010] Based on the physical source data table and the physical target table existing in the target database, the structured query language task indicated by the target structured query language statement is executed.

[0011] In a possible implementation, determining the physical target table involved in the target structured query language statement as the first physical target table includes:

[0012] For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into an SQLNode, wherein each node in the SQLNode is used to represent an element in the single structured query language statement;

[0013] Each of the SQLNodes is traversed respectively to determine each node for representing a physical target table, and the physical target table represented by each of the determined nodes is used as the first physical target table.

[0014] In a possible implementation, before traversing each of the SQLNodes respectively, the method further includes:

[0015] Determine a SQLNode including a create view node, wherein the create view node is a node used to represent a create view class;

[0016] At each of the determined SQLNodes, the create view node is converted into a syntax subtree, wherein each node in the syntax subtree is used to represent each insert class and select class that references the create view class represented by the create view node.

[0017] In a possible implementation, executing the structured query language task indicated by the target structured query language statement includes:

[0018] For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into a RelNode, wherein each node in the RelNode is used to represent a step in the structured query language subtask indicated by the single structured query language statement;

[0019] Determine the logical data source table and the logical target table involved in each of the RelNodes as the first logical data source table and the first logical target table;

[0020] According to the preset mapping relationship between the first field and the second field and the mapping relationship between the first field type and the second field type, the first field existing in the logical data source table and the logical target table is adjusted to the corresponding second field, and the existing first field type is adjusted to the corresponding second field type, so as to obtain a second logical data source table and a second logical target table, wherein the first field and the first field type are fields and field types not supported by the computing engine, and the second field and the second field type are fields and field types preset to be supported by the computing engine;

[0021] The calculation engine executes the structured query language task indicated by the target structured query language statement according to the second logical data source table and the second logical target table.

[0022] In a possible implementation, the method further includes:

[0023] For each of the first logical data source tables and each of the first logical target tables, obtaining metadata information related to the table in the target database as first metadata information;

[0024] According to a preset mapping rule between the first parameter and the second parameter, the first parameter existing in the first metadata information is adjusted to a corresponding second parameter to obtain second metadata information, wherein the first parameter is a parameter not supported by the computing engine, and the second parameter is a parameter supported by the computing engine;

[0025] The executing, by the computing engine, the structured query language task indicated by the target structured query language statement according to the second logical data source table and the second logical target table includes:

[0026] The computing engine executes the structured query language task indicated by the target structured query language statement according to the second logical data source table, the second logical target table and the second metadata information.

[0027] According to a second aspect of an embodiment of the present application, a structured query language task execution device is provided, the device comprising:

[0028] An acquisition module, used for acquiring a target structured query language statement for a target database;

[0029] A first determining module, configured to determine a physical target table involved in the target structured query language statement as a first physical target table;

[0030] A second determination module is used to determine, in each of the first physical target tables, a target table that does not exist in the target database as a second physical target table;

[0031] a generating module, configured to determine, for each second physical target table, in the target structured query language statement, a select statement associated with the second physical target table, and generate the second physical target table in the target database according to the fields involved in the select statement;

[0032] The execution module is used to execute the structured query language task indicated by the target structured query language statement based on the physical source data table and the physical target table existing in the target database.

[0033] In a possible implementation manner, the first determining module is specifically configured to:

[0034] For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into an SQLNode, wherein each node in the SQLNode is used to represent an element in the single structured query language statement;

[0035] Each of the SQLNodes is traversed respectively to determine each node for representing a physical target table, and the physical target table represented by each determined node is used as the first physical target table.

[0036] In a possible implementation, the device further includes:

[0037] A third determination module, used for determining a SQLNode including a create view node before traversing each of the SQLNodes respectively, wherein the create view node is a node used to represent a create view class;

[0038] The node conversion module is used to convert the create view node into a syntax subtree at each determined SQLNode, wherein each node in the syntax subtree is used to represent each insertion class and selection class that references the create view class represented by the create view node.

[0039] According to a third aspect of the embodiments of the present application, an electronic device is provided, including:

[0040] Memory, used to store computer programs;

[0041] The processor is used to implement the structured query language task execution method described in any one of the first aspects when executing the program stored in the memory.

[0042] According to a fourth aspect of an embodiment of the present application, a computer-readable storage medium is provided, wherein a computer program is stored in the computer-readable storage medium. When the computer program is executed by a processor, the structured query language task execution method described in any one of the first aspects is implemented.

[0043] An embodiment of the present application also provides a computer program product including instructions, which, when executed on a computer, enables the computer to execute any of the structured query language task execution methods described above.

[0044] Beneficial effects of the embodiments of the present application:

[0045] A structured query language task execution method provided by an embodiment of the present application, obtains a target structured query language statement for a target database, and according to the physical target table involved in the target structured query language statement, as the first physical target table, in each first physical target table, determines a target table that does not exist in the target database as the second physical target table, and then for each second physical target table, determines the selection statement associated with the second physical target table in the target structured query language statement, and generates the second physical target table in the target database according to the fields involved in the selection statement; finally, based on the physical source data table and the physical target table existing in the target database, executes the structured query language task indicated by the target structured query language statement. Through the method provided by the embodiment of the present application, a new physical target table can be determined according to the target structured query language statement, so that when the user issues an SQL task, even if the physical target table changes, the new physical target table can be obtained based on the target structured query language statement, and the computing engine can accurately execute the SQL task according to the physical source data table and the new physical target table existing in the target database, without creating a new physical target table in advance, and the computing engine can also accurately execute the SQL task, thereby improving the robustness of the SQL task execution.

[0046] Of course, implementing any product or method of the present application does not necessarily require achieving all of the advantages described above at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application, and for ordinary technicians in this field, other embodiments can also be obtained based on these drawings.

[0048] Figure 1 A comparison chart of the target table / source table and the physical target table / physical source table;

[0049] Figure 2 A first flow chart of the structured query language task execution method provided in the embodiment of the present application;

[0050] Figure 3 A second flow chart of the structured query language task execution method provided in the embodiment of the present application;

[0051] Figure 4 A third flow chart of the structured query language task execution method provided in the embodiment of the present application;

[0052] Figure 5 A fourth flow chart of the structured query language task execution method provided in an embodiment of the present application;

[0053] Figure 6 A fifth flow chart of the structured query language task execution method provided in the embodiment of the present application;

[0054] Figure 7 A schematic diagram of the structure of the modules for implementing the structured query language task execution method provided in the embodiment of the present application;

[0055] Figure 8 A flowchart of the SQL unified parsing submodule provided in the embodiment of the present application;

[0056] Fig. 9 A flowchart of the task SQL check submodule provided in the embodiment of the present application;

[0057] Fig.10 A workflow diagram of the task SQL generation submodule provided in an embodiment of the present application;

[0058] Fig.11 A schematic diagram of the structure of a structured query language task execution device provided in an embodiment of the present application;

[0059] Fig.12 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0060] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field based on the present application belong to the scope of protection of the present application.

[0061] In order to more clearly explain the data query method provided by this application, the relevant terms involved in this article are explained below:

[0062] SQL: The full name is Structured Query Language, which is a standard programming language specifically used to manage and operate databases. It allows users to perform operations such as querying, updating, and managing data.

[0063] SQL execution plan: After receiving the SQL query statement, the database management system will generate a detailed execution step based on the content of the SQL query statement. This step is called the SQL execution plan. The SQL execution plan is the core of guiding the database management system to perform data query, update and management operations, and determines the efficiency and performance of the query.

[0064] Computing engine: A component or framework used to process and execute computing tasks, providing real-time or near real-time data computing capabilities, and often has certain metadata management capabilities.

[0065] Metadata: It is data that describes the attribute information of data and is used to support functions such as indicating storage location, historical data, resource search, and file records.

[0066] Physical target table: also known as physical sink (output component in data stream processing) table, refers to the specific data source table where data is output from the computing engine in the stream processing process.

[0067] Physical source table: also known as the physical source table, refers to the specific data source table for data input into the computing engine in stream processing.

[0068] Stream processing task SQL: SQL used to submit tasks, usually composed of three parts: source table, sink table, and business logic SQL. The sink table is a memory table mapped inside the computing engine by the physical sink table of the real data source. The source table is a memory table mapped inside the computing engine by the physical source table of the real data source. The sink table and source table consist of table name, field, field type, data source connection parameters, and computing engine high-level parameters.

[0069] SQLNode: Also known as SQL node, it is an abstract syntax tree node of SQL statement, representing various components of SQL statement, such as select, from, where, join, etc. SQLNode is the product of SQL parsing stage, which parses SQL statement into tree structure for subsequent processing.

[0070] RelNode: full name Relational Node, hereinafter referred to as the abstract node of the relational expression, represents the logical execution plan after query optimization. RelNode will introduce the metadata information of the corresponding source table and target table.

[0071] Business SQL: In stream computing, it refers to the SQL related to the data stream processing logic. The statements related to the task itself include create view, select, and insert.

[0072] In the related art, when executing a query analysis task, in order to simplify the query analysis process, the physical source table and the physical target table are mapped in the computing engine to obtain the source table and the target table, and the source table and the target table are persisted in the computing engine, such as Figure 1 The figure shows a comparison diagram between the target table / source table and the physical target table / physical source table. Assuming that when executing a query analysis task, it is necessary to query based on the data in physical source table 1 and physical source table 2 to obtain a physical target table containing the query analysis results, it is necessary to create source table 1 and source table 2 corresponding to physical source table 1 and physical source table 2 in computing engine 100 according to the data in the physical source table, create a target table corresponding to the physical target table in computing engine 100, and persist source table 1, source table 2 and target table in computing engine 100. When querying, computing engine 100 creates a view based on source table 1 and source table 2, queries to obtain the target table, and converts and synchronizes the data in the target table to the physical target table.

[0073] However, if the user's query and analysis requirements change, that is, the physical source table and the physical target table change, since the source table and the target table have been permanently stored in the calculation engine, the data in the source table and the target table stored in the calculation engine are not the same as the data in the new physical source table and the new physical target table. The calculation engine cannot create views and perform queries and analyses based on the data in the new physical source table and the new physical target table, resulting in the inability to execute the SQL task.

[0074] In order to improve the robustness of SQL task execution, the first aspect of the embodiment of the present application provides a structured query language task execution method, see Figure 2 , Figure 2 A first flow chart of a structured query language task execution method provided in an embodiment of the present application, the method comprising the following steps:

[0075] Step S10, obtaining a target SQL statement for a target database;

[0076] Step S20, determining a physical target table involved in the target SQL statement as a first physical target table;

[0077] Step S30, in each first physical target table, determine a target table that does not exist in the target database as a second physical target table;

[0078] Step S40, for each second physical target table, determining the select statement associated with the second physical target table in the target SQL statement, and generating the second physical target table in the target database according to the fields involved in the select statement;

[0079] Step S50: executing the SQL task indicated by the target structured query language statement based on the physical source data table and the physical target table existing in the target database.

[0080] Through the method provided in the embodiment of the present application, a new physical target table can be determined according to the target structured query language statement. In this way, when the user issues an SQL task, even if the physical target table has changed, a new physical target table can be obtained based on the target structured query language statement. The computing engine can accurately execute the SQL task based on the physical source data table existing in the target database and the new physical target table. There is no need to create a new physical target table in advance. The computing engine can also accurately execute the SQL task, thereby improving the robustness of the SQL task execution.

[0081] The above steps S10 to S50 are described below:

[0082] In step S10, the target SQL statement is a statement constructed based on the task that the user wants to perform on a certain database. According to the operation that the user wants to perform, the SQL statement may include: DDL (Data Definition Language) statements, such as statements for creating (CREATE), modifying (ALTER) and deleting (DROP) databases, tables, views, indexes and other objects; DML (Data Manipulation Language) statements, such as statements for inserting (INSERT), updating (UPDATE) and deleting (DELETE) data in the database; DQL (Data Query Language) statements, whose core statement is the selection (SELECT) statement, which is used to retrieve data from the database; DCL (Data Control Language) statements, which are used to control the access rights of database users.

[0083] Databases include, but are not limited to, one or more of MySQL (Relational Database Management System), Kafka (an open source stream processing platform), Oracle (an information management software), ElasticSearch (a search server), PostgreSQL (an object-relational database management system), and StarRocks (a database system based on massively parallel processing technology).

[0084] It is understandable that there are multiple tables containing original data in the target database, namely physical source tables, also called physical source data tables. SQL statements are actually statements constructed based on the tasks that users want to perform on the source data tables in the target database. For example, suppose a database stores a physical source table named "sales_orders" that stores detailed information of all sales orders, such as order number, product information, sales date, etc. Through the SQL statement "SELECT FROM sales_orders" to extract all the data in the physical source table.

[0085] In step S20, the physical target table involved in the SQL statement may be pre-stored in the database, or may be obtained when the SQL task indicated by the SQL statement is executed.

[0086] However, if the physical target table is obtained when the SQL task indicated by the SQL statement is executed, the physical target table may not exist in the target database, and the physical target table needs to be created in the target database.

[0087] After determining the target table that does not exist in the target database as the second physical target table in step S30, an actual second physical target table is created in the target database in step S40 for each second physical target table.

[0088] In step S40, the select statement associated with the second physical target table refers to the select statement involving the content of the second physical target table. Since the basic syntax of the select statement is "SELECT [column_list] FROM [table_name]", where [column_list] is a list of columns to be selected, which can be one or more column names, and can also use wildcards to indicate selecting all columns, and [table_name] is the name of the physical source table from which to retrieve data.

[0089] The fields involved in the select statement refer to the "[column_list]" in the select statement. Since the select statement selects a list of columns to be selected from the physical source table as part of the content of the second physical target table, if the second physical target table does not exist in the target database, the structure of the second physical target table can be determined based on the content retrieved from the physical source table by the SELECT statement, that is, the second physical target table is generated in the target database.

[0090] Exemplarily, if the selection statements associated with the second physical target table are "SELECT order_id FROM orders1", "SELECT order_amount FROM orders2", "order_id" represents the order number, and "order_amount" represents the order amount, then the second physical target table includes the order number and the order amount.

[0091] In step S50, the SQL task is the task that the user wants to perform on the source data table existing in the target database. The SQL task includes data query task (SQL statement is SELECT statement), data insertion task (SQL statement is INSERT statement), data update task (SQL statement is UPDATE statement), data deletion task (SQL statement is DELETE statement), etc.

[0092] The target SQL statement may involve only one or more physical target tables. When the target SQL statement includes multiple SQL statements, the multiple SQL statements may involve more first physical target tables, and it takes time to determine the physical target tables involved in the SQL statement.

[0093] Based on this, in a possible implementation manner, Figure 3 The figure shows a second flow chart of the structured query language task execution method provided in the embodiment of the present application. The above step S20 specifically includes:

[0094] Step S201, for each single SQL statement in the target SQL statement, convert the single SQL statement into an SQLNode, where each node in the SQLNode is used to represent an element in the single SQL statement;

[0095] Step S203, traverse each SQLNode respectively, determine each node used to represent the physical target table, and use the physical target table represented by each determined node as the first physical target table.

[0096] The target SQL statement may contain only a single SQL statement or may contain multiple SQL statements. In the case where the target SQL statement is a compound statement composed of multiple SQL statements, the target SQL statement needs to be decomposed into multiple single SQL statements.

[0097] A single SQL statement refers to a statement that contains only one complete and independent SQL command executed at one time. A single SQL statement includes but is not limited to a single SELECT statement, a single INSERT statement, a single UPDATE statement, a single DELETE statement, and a single CREATE statement. For example, the SQL statement is: DELETE FROM students WHERE age <18, which means deleting all student records in the "students" table whose age is less than 18 years old. This SQL statement is a single SQL statement and a single DELETE statement, which is only used for data deletion tasks.

[0098] Each node in SQLNode is used to represent an element in a single SQL statement. Taking the aforementioned statement "DELETEFROM students WHERE age < 18" as an example, "DELETE", "FROM", "FROM", and "WHERE" respectively represent an element of the SQL statement.

[0099] By adopting the method of the embodiment of the present application, the SQL statement is converted into SQLNode. Since each node of SQLNode is used to represent an element in a single SQL statement, it is only necessary to traverse the nodes representing the physical target table in SQLNode to determine the physical target table involved in the SQL statement, without having to analyze each target SQL statement sentence by sentence.

[0100] The CREATVIEW statement is used to create a view in the database. This view is a virtual table based on the SQL query results. Since the view does not store actual data, but only generates data dynamically based on the SQL query, the syntax of the CREATVIEW statement must be correct to ensure that the view can be created correctly and return the query results.

[0101] In the related art, the CREAT VIEW statement is executed inside the computing engine, and the DML statement referencing the view can be verified only after the memory table is built.

[0102] The basic syntax of the statement for creating a view is: CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition. Among them, view_name is the name of the view that the user wants to create, SELECT column1, column2, ... specifies the columns that the user wants to include in the view, FROM table_name specifies the table that the view is queried based on, and WHERE condition is an optional filtering operation.

[0103] The field following the field "CREATE VIEW view_name AS" in the create view statement is actually a SELECT statement. When verifying whether the create view statement is correct, verifying the statement following the field "CREATE VIEW view_name AS" in the view statement is actually equivalent to verifying the create view statement. Since a view is essentially a virtual table based on SQL query results, a create view statement usually contains a SELECT statement.

[0104] The verification of the SELECT statement or the INSERT statement does not rely on the computing engine. Based on this, in a possible implementation, before executing the above step S203, the create view statement may be rewritten into a SELECT statement.

[0105] Specifically, Figure 4 The third flow chart of the structured query language task execution method provided in the embodiment of the present application is shown, and the method further includes:

[0106] Step S2021, determining the SQLNode containing the created view node;

[0107] The create view node is a node used to represent the create view class;

[0108] Step S2022, converting the created view node into a syntax subtree in each determined SQLNode;

[0109] Each node in the syntax subtree is used to represent each insert class and select class that references the create view class represented by the create view node, that is, the INSERT class and the SELECT class.

[0110] By adopting the method of the embodiment of the present application, in the SQLNode containing the create view node, the create view node is converted into a syntax subtree for representing the SELECT statement, thereby realizing the ability to parse and verify the create view statement without relying on the computing engine, thereby improving the flexibility and efficiency of SQL queries.

[0111] It is understandable that the data types in the physical source table and the physical target table stored in the target database are different from the data types that the computing engine can recognize. Therefore, before the computing engine executes the SQL task, the physical source table and the physical target table need to be converted into data types that the computing engine can recognize.

[0112] See, Figure 5 This is a fourth flow chart of the structured query language task execution method provided in the embodiment of the present application. The aforementioned step S50 of executing the structured query language task indicated by the target structured query language statement specifically includes:

[0113] Step S501, for each single SQL statement in the target SQL statement, convert the single SQL statement into a RelNode, each node in the RelNode is used to represent a step in the SQL subtask indicated by the single SQL statement;

[0114] You can use existing tools to convert a single SQL statement to a RelNode, such as Calcite (a data query engine that can convert any data query into a SQL-based query). Specifically, you can first use Calcite's SQL parser to convert a single SQL statement to an SQLNode. Since the structure of the physical target table has been determined based on the above, and the structure of the physical source table is known, Calcite can access the metadata of the physical source table and the physical target table for verification. After the verification is passed, use Calcite's SqlToRelConverter (a key component of Calcite) to convert the SQLNode to a logical RelNode.

[0115] Step S503, determining the logical data source table and the logical target table involved in each RelNode as the first logical data source table and the first logical target table;

[0116] Step S505: According to the preset mapping relationship between the first field and the second field and the mapping relationship between the first field type and the second field type, the first field existing in the logical data source table and the logical target table is adjusted to the corresponding second field, and the existing first field type is adjusted to the corresponding second field type, so as to obtain a second logical data source table and a second logical target table;

[0117] The first field and the first field type are fields and field types not supported by the computing engine, and the second field and the second field type are fields and field types supported by the preset computing engine;

[0118] Step S507: Execute the SQL task indicated by the target SQL statement according to the second logical data source table and the second logical target table through the computing engine.

[0119] By adopting the method of the embodiment of the present application, the field types in the logical data source table and the logical target table involved in RelNode are adjusted so that the field types in the logical data source table and the logical target table are field types that can be recognized by the computing engine, thereby avoiding the inability to execute SQL tasks normally due to the field types in the logical data source table and the logical target table involved in RelNode being types that the computing engine does not support and recognize.

[0120] It can be understood that the mapping relationship between the field types supported and recognized by the computing engine and the field types that the computing engine cannot recognize is pre-stored in the target database. After obtaining the logical data source table and the logical target table, the parameters existing in the metadata information involved in the table can be adjusted to parameters supported by the computing engine according to the mapping relationship pre-stored in the target database.

[0121] See also Figure 6 , Figure 6 A fifth flow chart of the structured query language task execution method provided in the embodiment of the present application, the method further includes the following steps:

[0122] Step S5041, for each first logical data source table and each first logical target table, obtaining metadata information related to the table in the target database as first metadata information;

[0123] Step S5042: According to a preset mapping rule between the first parameter and the second parameter, the first parameter existing in the first metadata information is adjusted to the corresponding second parameter to obtain the second metadata information;

[0124] The first parameter is a parameter not supported by the computing engine, and the second parameter is a parameter supported by the computing engine;

[0125] The aforementioned step S507 specifically includes:

[0126] Step S5071: Execute the structured query language task indicated by the target structured query language statement through the computing engine according to the second logical data source table, the second logical target table and the second metadata information.

[0127] By adopting the method of the embodiment of the present application, since there are preset mapping rules between the parameters supported by the computing engine and the parameters not supported by the computing engine, the logical data source table and the logical target table can be converted into data types that can be recognized by the computing engine. When the physical source table and the physical target table change, the SQL task can also be accurately executed.

[0128] Based on the above, it can be seen that the structured query language task execution method provided in the embodiment of the present application can be considered to be completed in five modules respectively. Figure 7 The structure diagram of the module for implementing the structured query language task execution method provided by the embodiment of the present application is shown, and the module includes a SQL unified parsing submodule 11, an SQL verification submodule 12, a task SQL generation submodule 13, a task submission submodule 14, and a data acquisition and storage submodule 15. Among them, the SQL unified parsing submodule 11, the SQL verification submodule 12, and the task SQL generation submodule 13 constitute a metadata management unit 1 external to the computing engine, and the metadata management unit 1 can provide the ability to perform SQL parsing and verification independently of the computing engine, and can manage metadata information of other data sources, so that users do not need to pay attention to data mapping between the data source and the computing engine, data source data parameter mapping, whether the physical target table exists, and other issues.

[0129] Depend on Figure 7 It can be seen that in the process of executing SQL tasks, the SQL unified parsing submodule 11, the SQL verification submodule 12, the task SQL generation submodule 13, and the task submission submodule 14 continuously obtain data from the data acquisition and storage submodule 15. In the embodiment of the present application, in the process of executing SQL tasks by the SQL unified parsing submodule 11, the SQL verification submodule 12, the task SQL generation submodule 13, and the task submission submodule 14, first, the SQL unified parsing submodule 11 performs SQL segmentation and SQL parsing to obtain SQLNode; then the SQL verification submodule 12 performs sink table verification and sink table generation based on SQLNode to obtain RelNode; then the task SQL generation submodule 13 performs field type mapping, metadata information mapping, and high-order parameter mapping, and converts the logical data source table and logical target table involved in RelNode into data supported by the computing engine; finally, the task submission submodule 14 performs task SQL proofreading, task parameter filling, and submits the SQL task and task parameters to the computing engine.

[0130] The SQL unified parsing submodule 11, the SQL verification submodule 12, the task SQL generation submodule 13, the task submission submodule 14 and the data collection and storage submodule 15 are described below respectively:

[0131] (1) Data acquisition and storage submodule 15

[0132] This submodule is equivalent to the target database mentioned above, and is the core data support of the task generation submodule. The metadata information of the data source (i.e. Figure 7 Heterogeneous data source metadata in the data source table), mapping of field types of data source table information to computing engine data types, table name mapping, connection parameter mapping, and other key rules (i.e. Figure 7 The data source and computing engine mapping rules in the front-end interface) and the advanced parameters preset by the user through the front-end interface (i.e. Figure 7 ).

[0133] The information stored in the data collection and storage submodule 15 is continuously stored and used during the execution of the SQL task.

[0134] (2) SQL unified parsing submodule 11

[0135] The function of the SQL unified parsing submodule 11 is to parse one or more input target SQL statements into an abstract syntax tree SQLNode. For example, a CREATEVIEW statement will be converted into a SQLCREATEVIEW class, an INSERT statement will be converted into a SQLINSERT class, and a SELECT statement will be converted into a SQLSELECT class. In addition, the submodule has a SQL statement rewriting function, and can reconstruct CREATEVIEW statements, INSERT statements, and SELECT statements.

[0136] The workflow diagram of SQL unified parsing submodule 11 is as follows Figure 8First, the target SQL statement consisting of multiple SQL statements submitted by the user is obtained (corresponding to the aforementioned step S10) and finely divided into multiple single SQL statements, and each single SQL statement is parsed into SQLNode one by one by Calciteparser (corresponding to the aforementioned step S201), and then the SQLNodelist corresponding to the target SQL statement is obtained. Subsequently, traverse the SQLNodelist corresponding to the target SQL statement to see if there is a SQLNode containing a CREATVIEW statement. If not, directly store the SQLNodelist corresponding to the target SQL statement temporarily in the memory of the service program running the metadata management unit, and clean it up after the SQL task is completed; if it exists, the VIEW needs to be stored, and further check whether other SQLSELECT statements or SQLINSERT statements reference the VIEW. If there is a reference, reconstruct the CREATVIEW statement into a subquery statement (SQLSELECT statement or SQLINSERT statement), and merge it with the corresponding SQLSELECT statement or SQLINSERT statement (corresponding to the aforementioned steps S2021 to S2022), and finally obtain a SQLSELECT statement set or a SQLINSERT statement set; if there is no reference, directly store the SQLNodelist corresponding to the target SQL statement temporarily in the memory of the service program running the metadata management unit, and clean it up after the SQL task is completed.

[0137] (3) Task SQL verification submodule 13

[0138] See also Fig. 9 The following is a workflow diagram of the task SQL verification submodule. After receiving the SQLSELECT statement set or SQLINSERT statement set output by the SQL unified parsing submodule 11, the submodule uses the SqlVisitor visitor to traverse all physical target tables in the SQLNodelist (corresponding to the aforementioned step S20), and combines the data source metadata information of the data acquisition and storage submodule 15 to determine whether the physical target table actually has a data source (corresponding to the aforementioned step S30). If so, the interface is called to obtain the metadata information of all physical target tables and physical source tables from the data acquisition and storage submodule 15, and after SQL verification, the SQLNode is converted into a RelNode (corresponding to the aforementioned step S501).

[0139] If it does not exist, it is necessary to remove the fields related to the physical target table in the SQLINSERT statement, convert the SQLINSERT statement into a SQLSELECT statement, and convert the SQLSELECT statement into a RelNode in combination with the metadata information of the physical source table. Then obtain the execution plan from the RelNode, obtain the fields of the target table required by the user according to the execution plan, and map these field types to the data types of the corresponding data sources in combination with the preset mapping rules; finally, generate the table creation statement of the target table using the data source metadata information in the data acquisition and storage submodule 15. After the user determines to execute this SQL, the physical target table in the data acquisition and storage submodule 15 already exists, then call the interface to obtain the metadata information of all physical target tables and physical source tables from the data acquisition and storage submodule 15, and convert the SQLNode into a RelNode.

[0140] Finally, all RelNode collections are temporarily stored in memory and cleaned up after the SQL task is generated later.

[0141] (4) Task SQL generation submodule 13

[0142] See also Fig.10 The workflow diagram of the task SQL generation submodule. This submodule obtains the logical data source table and the logical target table involved in the RelNode according to the RelNode set output by the task SQL generation submodule 13 (corresponding to the aforementioned step S503), and then adjusts the fields and field types existing in the logical data source table and the logical target table to the fields and field types that the computing engine can recognize (corresponding to the aforementioned step S505) to ensure that the data source data can be correctly recognized by the computing engine. Subsequently, the metadata information involved in the table in the data acquisition and storage submodule 15 is obtained, such as the parameters in the SQL statement (corresponding to the aforementioned step S5041), and the data source metadata information is mapped based on the preset mapping rules (i.e. Fig.10 ), converts it into the writing style specified by the computing engine (corresponding to the aforementioned step S5042), and then generates the parameters required by the computing engine to connect to the corresponding data source, such as the with parameter in Flink, the parameters for connecting to the MySQL database, url (uniform resource locator), driver, etc. Finally, according to the preset mapping rules, the high-level parameters supplemented by the user obtained by the front end are converted into the writing style specified by the engine, and supplemented into the task SQL, generating the task SQL high-level parameters, and then obtaining the final complete task SQL.

[0143] Among them, high-level parameters are tuning parameters between the computing engine and the data source. These parameters will not affect the connection between the computing engine and the data source. High-level parameters usually use default values, but some users will make detailed adjustments to these parameters according to different task requirements. High-level parameters are divided into high-level parameters of the source table of the data source and high-level parameters of the target table. The high-level parameters of the source table of the data source include the scan partition number parameter (scan.partition.num), the scan partition column parameter (scan.partition.column), the cache strategy parameter (lookup.cache), and the high-level parameters of the target table include the insertion parallelism (sink.parallelism), the cache refresh interval (sink.buffer-flush.interval), and the maximum number of cache storage rows (sink.buffer-flush.max-rows). It is understandable that the high-level parameters of different data sources may be different.

[0144] (5) Task submission submodule 14

[0145] After the task SQL is generated, it will be returned to the front end for proofreading by the user, and the computing engine will also proofread the task SQL through the proofreading interface. After the proofreading is completed, each task SQL will set specific task parameters before submission. These task parameters are for users to perform different degrees of task tuning. After the user configures the task parameters through the front end interface, the submodule submits the task to the computing engine, which executes the task.

[0146] Corresponding to the first aspect, the second aspect of the embodiment of the present application provides a structured query language task execution device, such as Fig.11 The figure is a schematic diagram of the structure of a structured query language task execution device provided in an embodiment of the present application, the device comprising:

[0147] An acquisition module 1101 is used to acquire a target structured query language statement for a target database;

[0148] A first determining module 1102 is used to determine a physical target table involved in the target structured query language statement as a first physical target table;

[0149] A second determination module 1103 is used to determine, in each of the first physical target tables, a target table that does not exist in the target database as a second physical target table;

[0150] A generating module 1104 is used to determine, for each second physical target table, a select statement associated with the second physical target table in the target structured query language statement, and generate the second physical target table in the target database according to the fields involved in the select statement;

[0151] The execution module 1105 is used to execute the structured query language task indicated by the target structured query language statement based on the physical source data table and the physical target table existing in the target database.

[0152] Through the device provided in the embodiment of the present application, a new physical target table can be determined according to the target structured query language statement. In this way, when the user issues an SQL task, even if the physical target table has changed, a new physical target table can be obtained based on the target structured query language statement. The computing engine can accurately execute the SQL task based on the physical source data table existing in the target database and the new physical target table. There is no need to create a new physical target table in advance. The computing engine can also accurately execute the SQL task, thereby improving the robustness of the SQL task execution.

[0153] In a possible implementation manner, the first determining module 1102 is specifically configured to:

[0154] For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into an SQLNode, wherein each node in the SQLNode is used to represent an element in the single structured query language statement;

[0155] Each of the SQLNodes is traversed respectively to determine each node for representing a physical target table, and the physical target table represented by each of the determined nodes is used as the first physical target table.

[0156] In a possible implementation, the device further includes:

[0157] A third determination module is used to determine the SQLNode containing the create view node before traversing each of the SQLNodes respectively, wherein the create view node is a node used to represent the create view class;

[0158] The node conversion module is used to convert the create view node into a syntax subtree at each determined SQLNode, wherein each node in the syntax subtree is used to represent each insertion class and selection class that references the create view class represented by the create view node.

[0159] The present application also provides an electronic device, such as Fig.12 As shown, including:

[0160] Memory 1201, used for storing computer programs;

[0161] The processor 1202 is used to implement the following steps when executing the program stored in the memory 1201:

[0162] Get the target SQL statement for the target database;

[0163] Determine a physical target table involved in the target SQL statement as the first physical target table;

[0164] In each first physical target table, a target table that does not exist in the target database is determined as a second physical target table;

[0165] For each second physical target table, determining a select statement associated with the second physical target table in the target SQL statement, and generating the second physical target table in the target database according to the fields involved in the select statement;

[0166] Based on the physical source data table and the physical target table existing in the target database, the SQL task indicated by the target structured query language statement is executed.

[0167] The memory may include a random access memory (RAM) or a non-volatile memory (NVM), such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0168] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0169] In another embodiment provided by the present application, a computer-readable storage medium is provided, in which a computer program is stored. When the computer program is executed by a processor, the steps of any of the above structured query language task execution methods are implemented.

[0170] In another embodiment provided by the present application, a computer program product including instructions is also provided. When the computer program product is run on a computer, the computer executes any structured query language task execution method in the above embodiments.

[0171] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions may be transmitted from a website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a solid-state hard disk (SSD), etc.

[0172] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0173] Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0174] The above description is only a preferred embodiment of the present application and is not intended to limit the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application are included in the protection scope of the present application.

Claims

1. A structured query language task execution method, characterized in that: The method comprises: Obtaining a target structured query language statement for a target database; Determine a physical target table involved in the target structured query language statement as a first physical target table; In each of the first physical target tables, a target table that does not exist in the target database is determined as a second physical target table; For each of the second physical target tables, determining a select statement associated with the second physical target table in the target structured query language statement, and generating the second physical target table in the target database according to the fields involved in the select statement; Based on the physical source data table and the physical target table existing in the target database, executing the structured query language task indicated by the target structured query language statement; The step of executing the structured query language task indicated by the target structured query language statement includes: For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into a RelNode, wherein each node in the RelNode is used to represent a step in the structured query language subtask indicated by the single structured query language statement; Determine the logical data source table and the logical target table involved in each of the RelNodes as the first logical data source table and the first logical target table; According to the preset mapping relationship between the first field and the second field and the mapping relationship between the first field type and the second field type, the first field existing in the logical data source table and the logical target table is adjusted to the corresponding second field, and the existing first field type is adjusted to the corresponding second field type, so as to obtain a second logical data source table and a second logical target table, wherein the first field and the first field type are fields and field types not supported by the computing engine, and the second field and the second field type are fields and field types preset to be supported by the computing engine; The calculation engine executes the structured query language task indicated by the target structured query language statement according to the second logical data source table and the second logical target table.

2. The method according to claim 1, characterized in that: The step of determining the physical target table involved in the target structured query language statement as the first physical target table includes: For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into an SQLNode, wherein each node in the SQLNode is used to represent an element in the single structured query language statement; Each of the SQLNodes is traversed respectively to determine each node for representing a physical target table, and the physical target table represented by each determined node is used as the first physical target table.

3. The method according to claim 2, characterized in that Before traversing each of the SQLNodes respectively, the method further includes: Determine a SQLNode containing a create view node, wherein the create view node is a node used to represent a create view class; At each of the determined SQLNodes, the create view node is converted into a syntax subtree, wherein each node in the syntax subtree is used to represent each insert class and select class that references the create view class represented by the create view node.

4. The method according to claim 1, characterized in that: The method further comprises: For each of the first logical data source tables and each of the first logical target tables, obtaining metadata information related to the table in the target database as first metadata information; According to a preset mapping rule between the first parameter and the second parameter, the first parameter existing in the first metadata information is adjusted to a corresponding second parameter to obtain second metadata information, wherein the first parameter is a parameter not supported by the computing engine, and the second parameter is a parameter supported by the computing engine; The executing, by the computing engine, the structured query language task indicated by the target structured query language statement according to the second logical data source table and the second logical target table includes: The computing engine executes the structured query language task indicated by the target structured query language statement according to the second logical data source table, the second logical target table and the second metadata information.

5. A structured query language task execution device, characterized in that: The device comprises: An acquisition module, used for acquiring a target structured query language statement for a target database; A first determining module, configured to determine a physical target table involved in the target structured query language statement as a first physical target table; A second determination module is used to determine, in each of the first physical target tables, a target table that does not exist in the target database as a second physical target table; a generating module, configured to determine, for each second physical target table, in the target structured query language statement, a select statement associated with the second physical target table, and generate the second physical target table in the target database according to the fields involved in the select statement; An execution module, configured to execute the structured query language task indicated by the target structured query language statement based on the physical source data table and the physical target table existing in the target database; The step of executing the structured query language task indicated by the target structured query language statement includes: For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into a RelNode, wherein each node in the RelNode is used to represent a step in the structured query language subtask indicated by the single structured query language statement; Determine the logical data source table and the logical target table involved in each of the RelNodes as the first logical data source table and the first logical target table; According to the preset mapping relationship between the first field and the second field and the mapping relationship between the first field type and the second field type, the first field existing in the logical data source table and the logical target table is adjusted to the corresponding second field, and the existing first field type is adjusted to the corresponding second field type, so as to obtain a second logical data source table and a second logical target table, wherein the first field and the first field type are fields and field types not supported by the computing engine, and the second field and the second field type are fields and field types preset to be supported by the computing engine; The calculation engine executes the structured query language task indicated by the target structured query language statement according to the second logical data source table and the second logical target table.

6. The device according to claim 5, characterized in that The first determining module is specifically configured to: For each single structured query language statement in the target structured query language statement, convert the single structured query language statement into an SQLNode, wherein each node in the SQLNode is used to represent an element in the single structured query language statement; Each of the SQLNodes is traversed respectively to determine each node for representing a physical target table, and the physical target table represented by each determined node is used as the first physical target table.

7. The device according to claim 6, characterized in that The device also includes: A third determination module, used for determining a SQLNode including a create view node before traversing each of the SQLNodes respectively, wherein the create view node is a node used to represent a create view class; The node conversion module is used to convert the create view node into a syntax subtree at each determined SQLNode, wherein each node in the syntax subtree is used to represent each insertion class and selection class that references the create view class represented by the create view node.

8. An electronic device, characterized in that: include: Memory, used to store computer programs; A processor, for implementing any of the methods described in claims 1-4 when executing a program stored in a memory.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 4 is implemented.

Images