SQL (Structured Query Language) statement processing method and device, equipment and medium
By automatically generating and filling table attributes in FlinkSQL table building statements, the problem of manual specification of table attributes is solved, the development efficiency and ease of use is improved, and the threshold for use is lowered.
Patent Information
- Application Number
- CN202411195385.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-28
- Publication Date
- 2024-12-24
AI Technical Summary
In FlinkSQL, SQL statements used to build tables require manual specification of a large number of table attributes, resulting in low writing efficiency and high threshold. Especially when it involves underlying implementation details, there may be difficulties in understanding, which increases the difficulty of using it.
By obtaining the statement type of the initial SQL statement, determining the data source type, and generating missing target table attribute pairs based on the associated table attribute generation rules, automatically updating the SQL statements so that developers only need to fill in some core table attributes, and other attributes are the system Automatically generated.
It improves the development efficiency of SQL developers, reduces the technical and usage threshold, simplifies the table building process, and improves the ease of use of FlinkSQL.
Smart Images
Figure CN119179707A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of big data technology, and in particular to a method, device, equipment and medium for processing SQL statements. Background Art
[0002] In FlinkSQL (part of Flink, a high-performance distributed cluster data processing engine that provides a SQL (Structured Query Language) interface that allows developers to use standard SQL to write stream processing and batch processing applications), the SQL statement used to create a table supports specifying table attributes in key-value format through the WITH keyword. These table attributes are usually used to find and create underlying connectors.
[0003] In the related art, the SQL statements used to create tables are manually written by SQL developers, who need to specify a large number of table properties in the SQL statements (such as storage path, pre-merged fields, synchronization mode, table type, whether to allow repeated insertion of data, and whether to disable the index guidance function, asynchronous clustering function, metadata function, asynchronous compression function, etc.), resulting in low efficiency in writing SQL statements. Summary of the invention
[0004] The purpose of this application is to solve one of the above technical problems at least to a certain extent.
[0005] To this end, the present application proposes a method, apparatus, device and medium for processing SQL statements to improve the development efficiency of SQL developers (i.e., improve the efficiency of writing SQL statements) and lower the technical and usage thresholds.
[0006] The first embodiment of the present application proposes a method for processing an SQL statement, including:
[0007] Acquire a statement type of an initial SQL statement, and in response to the statement type being a table creation type, acquire a table attribute list in a specified keyword from the initial SQL statement;
[0008] Determine a data source type based on the table attribute pairs in the table attribute list; wherein the data source type is used to indicate the data storage and access mode of the data table to be created by the initial SQL statement; the table attribute pairs include a table attribute key and a corresponding table attribute value;
[0009] Generate a target table attribute pair missing in the table attribute list based on a table attribute generation rule associated with the data source type;
[0010] Based on the target table attribute pair, the initial SQL statement is updated to obtain a target SQL statement, so as to create the data table according to the target SQL statement.
[0011] The second aspect of the present application provides a SQL statement processing device, including:
[0012] An acquisition module, configured to acquire a statement type of an initial SQL statement, and in response to the statement type being a table creation type, acquire a table attribute list in a specified keyword from the initial SQL statement;
[0013] A determination module, configured to determine a data source type based on a table attribute pair in the table attribute list; wherein the data source type is used to indicate a data storage and access method of a data table to be created by the initial SQL statement; and the table attribute pair includes a table attribute key and a corresponding table attribute value;
[0014] A first generating module, configured to generate a target table attribute pair missing in the table attribute list based on a table attribute generating rule associated with the data source type;
[0015] An updating module is used to update the initial SQL statement based on the target table attribute pair to obtain a target SQL statement, so as to create the data table according to the target SQL statement.
[0016] The third aspect of the present application provides an electronic device, comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the program, the method for processing SQL statements as described in the first aspect is implemented.
[0017] The fourth aspect of the present application provides a non-temporary computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, the method for processing SQL statements as described in the first aspect is implemented.
[0018] The fifth aspect of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the SQL statement processing method described in the first aspect of the present application.
[0019] The technical solution provided by the embodiments of the present application brings at least the following beneficial effects:
[0020] When SQL developers write SQL statements for creating tables, they only need to fill in some core or required table attributes. Other business-related table attributes can be automatically generated and filled based on the table attribute generation rules associated with the data source type in the SQL statement, thereby improving the development efficiency of SQL developers (that is, improving the efficiency of writing SQL statements) and lowering technical and usage barriers.
[0021] Additional aspects and advantages of the present application will be given in part in the description below, and in part will become apparent from the description below, or will be learned through the practice of the present application. BRIEF DESCRIPTION OF THE DRAWINGS
[0022] The above and / or additional aspects and advantages of the present application will become apparent and easily understood from the following description of the embodiments in conjunction with the accompanying drawings, in which:
[0023] Figure 1 A flowchart of a method for processing an SQL statement provided in an embodiment of the present application;
[0024] Figure 2 A flowchart of another method for processing SQL statements provided in an embodiment of the present application;
[0025] Figure 3 A flowchart of another method for processing SQL statements provided in an embodiment of the present application;
[0026] Figure 4 A flowchart of another method for processing SQL statements provided in an embodiment of the present application;
[0027] Figure 5 A flowchart of another method for processing SQL statements provided in an embodiment of the present application;
[0028] Figure 6 A schematic diagram of the processing or optimization principle of the SQL statement provided in the embodiment of the present application;
[0029] Figure 7 It is a structural schematic diagram of a device for processing SQL statements according to an embodiment of the present application;
[0030] Figure 8 It is a schematic diagram of the structure of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION
[0031] Embodiments of the present application are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended to be used to explain the present application, and should not be construed as limiting the present application.
[0032] When using FlinkSQL to create a table, it is often necessary to specify a large number of table attributes in the SQL statement used to create the table based on factors such as the data source type (or connector type), business scenario, etc. Taking the use of FlinkSQL to create a Hudi (Hadoop Upserts Delete and Incremental, a streaming data lake platform) table as an example, after combining a specific business scenario, the SQL statement used to create the table (allowing the Hudi table to synchronize metadata and data with the Hive (Hadoop-based data warehouse tool) table) can specify multiple table attributes through the WITH clause. These table attributes are usually used to control the storage behavior of the data table, the interaction method with Hive, and the performance optimization of the data table. For example, table attributes include but are not limited to the following:
[0033] 1. connector: This table attribute is used to indicate the data source type or connector type. For example, when 'connector' = 'hudi', connector indicates that the connector type of the data table is Hudi, that is, Hudi is used as the underlying storage system.
[0034] 2. write.precombine.field: This table attribute is used to indicate the pre-merge field (e.g., load_time). In the Hudi table, this field is used to handle data merging during concurrent writes.
[0035] 3. hive_sync.table and hoodie.datasource.hive_sync.table: These two table properties point to the target table name for data synchronization (such as my_table_rt, where the data table to be created by the SQL statement can be called the source table, which is used for data write operations; my_table_rt synchronizes data with the source table and is used for data read operations to support efficient reading and writing of data in the source table).
[0036] 4. path: This table attribute is used to indicate the storage path.
[0037] 5. hive_sync.mode: used to indicate the synchronization mode (such as HMS (Hive Metastore), that is, using HMS to synchronize metadata).
[0038] 6. hive_sync.conf.dir: used to indicate the directory of synchronization configuration files.
[0039] 7. clustering.schedule.enabled: used to indicate the clustering scheduling function of the Hudi table is enabled, which helps to optimize the storage layout and data query performance of the data table.
[0040] 8. hoodie.datasource.write.keygenerator.class: used to indicate the class that generates the primary key when writing.
[0041] 9. clean.async.enabled: Used to indicate that asynchronous cleanup operations are enabled, which helps to clean up no longer needed data files in the background, thereby improving storage efficiency.
[0042] 10. table.type and type: used to indicate the table type of the Hudi table. For example, the table type can be COPY_ON_WRITE (copy on write, referred to as COW). This table type adopts the copy on write strategy when processing update and delete operations.
[0043] 11. index.bootstrap.enabled: used to indicate that the index bootstrap function is set to disabled. Index bootstrap is usually used to quickly build an index when a data table is created.
[0044] 12. hoodie.merge.allow.duplicate.on.inserts: used to indicate that duplicate records are allowed when data is inserted.
[0045] 13. write.operation: used to indicate that the write operation is insert, indicating that the created data table is mainly used to insert new data.
[0046] 14. hive_sync.enable: used to indicate that the synchronization function is enabled.
[0047] 15. clustering.async.enabled: used to indicate that the asynchronous clustering function is disabled.
[0048] 16. hoodie.metadata.enable: used to indicate that the metadata function of the Hudi table is disabled to reduce storage overhead.
[0049] 17.hoodie.datasource.write.hive_style_partitioning: Used to indicate that Hive-style partitioning is enabled, which helps better integration with Hive.
[0050] 18. index.type: used to indicate that the index type is set (such as FLINK_STATE).
[0051] 19. compaction.async.enabled: used to indicate that the asynchronous compaction function is disabled.
[0052] In summary, when creating a large number of data tables, manually specifying table attributes is cumbersome and inefficient. In addition, some table attributes involve underlying implementation details, which may be difficult to understand, raising the threshold for using FlinkSQL and reducing the ease of use of FlinkSQL.
[0053] In view of at least one of the above problems, the embodiments of the present application provide a method, device, equipment and medium for processing SQL statements. Before describing the embodiments of the present application in detail, for ease of understanding, the commonly used technical terms are first introduced:
[0054] Flink: A high-performance distributed cluster data processing engine that runs at any scale at amazing memory speeds, seamlessly supporting efficient and stateful computations on bounded and unbounded data streams. With its excellent throughput, extremely low processing latency, and outstanding performance, Flink is an ideal choice for processing real-time data streams and complex data processing.
[0055] FlinkSQL: Part of the Flink engine, it provides a SQL interface that allows developers to use standard SQL language to write stream processing and batch processing applications.
[0056] Apache Calcite: An open source framework for SQL parsing. FlinkSQL is developed based on Apache Calcite to implement parsing and optimization of FlinkSQL statements.
[0057] Hudi: Provides the ability to efficiently perform updates, deletes, and incremental reads on Hadoop-compatible data stores.
[0058] Hive: A data warehouse infrastructure built on Hadoop that allows users to query and manage large data sets stored in the Hadoop Distributed File System or other compatible storage systems in a familiar way.
[0059] Combine the following Figure 1 , the processing method of the SQL statement provided in this application is described in detail.
[0060] Figure 1 A flowchart of a method for processing SQL statements provided in an embodiment of the present application.
[0061] The SQL statement processing method provided in the embodiment of the present application can be executed by the SQL statement processing device provided in the embodiment of the present application. The SQL statement processing device in the present application can be applied to an electronic device to perform the SQL statement processing function. Alternatively, the SQL statement processing device can be configured in an application of the electronic device so that the application can perform the SQL statement processing function.
[0062] The electronic device may be any device with computing capability, and the device or the application in the device may execute the processing function of SQL statements. The device with computing capability may be, for example, a personal computer, a mobile terminal, a server, etc. The mobile terminal may be, for example, a vehicle-mounted device, a mobile phone, a tablet computer, a personal digital assistant, a wearable device, or other hardware device with various operating systems, touch screens, and / or display screens.
[0063] like Figure 1 As shown, the SQL statement processing method includes the following steps S101 to S104:
[0064] Step S101, obtaining the statement type of the initial SQL statement, and in response to the statement type being a table creation type, obtaining a table attribute list in a specified keyword from the initial SQL statement.
[0065] The statement types include, but are not limited to, query, update, table creation, etc.
[0066] The table attribute list includes at least one table attribute pair, wherein each table attribute pair includes a table attribute key (key) and a corresponding table attribute value (value).
[0067] In an embodiment of the present application, the initial SQL statement submitted by relevant personnel (such as SQL developers) can be obtained, and the initial SQL statement can be semantically parsed to obtain the statement type to which the initial SQL statement belongs. If the statement type is a table creation type, a table attribute list in a specified keyword (such as the WITH keyword) can be obtained from the initial SQL statement.
[0068] Step S102, determining a data source type based on the table attribute pairs in the table attribute list; wherein the data source type is used to indicate the data storage and access method of the data table to be created by the initial SQL statement.
[0069] In an embodiment of the present application, a data source type (or connector type) can be determined based on a table attribute pair in a table attribute list, wherein the data source type is used to indicate the data storage and access method of the data table to be created by the initial SQL statement.
[0070] As an example, the table attribute pair where the table attribute key connector is located can be determined from the table attribute list, and the data source type can be determined according to the table attribute value corresponding to the connector in the table attribute pair.
[0071] For example, the table attribute pair includes: 'connector' = 'hudi', then the data source type is Hudi, that is, the table type of the data table to be created by the initial SQL statement is a Hudi table.
[0072] Step S103: Generate target table attribute pairs missing from the table attribute list based on the table attribute generation rule associated with the data source type.
[0073] The target table attribute pair (eg, the table attribute pair related to the actual business) includes the missing table attribute key and the corresponding table attribute value in the table attribute list.
[0074] The table attribute generation rule is used to indicate the table attribute keys missing in the initial SQL statement and the generation rules of the corresponding table attribute values.
[0075] The table attribute generation rules are pre-configured and stored for the data source type. It should be noted that the configured table attribute generation rules may be different for different data source types.
[0076] In an embodiment of the present application, a table attribute generation rule associated with a data source type may be queried, and based on the table attribute generation rule, a target table attribute pair missing from the table attribute list may be generated.
[0077] Step S104: based on the target table attribute pair, the initial SQL statement is updated to obtain a target SQL statement, so as to create a data table according to the target SQL statement.
[0078] In the embodiment of the present application, the initial SQL statement can be updated or optimized based on the target table attribute pair to obtain a target SQL statement, so as to create a data table according to the target SQL statement.
[0079] The SQL statement processing method of the embodiment of the present application is that when an SQL developer writes an SQL statement for creating a table, he only needs to fill in some core or required table attributes. Other business-related table attributes can be automatically generated and filled based on the table attribute generation rules associated with the data source type in the SQL statement, thereby improving the development efficiency of SQL developers (i.e., improving the writing efficiency of SQL statements) and lowering the technical and usage thresholds.
[0080] This application embodiment provides another method for processing SQL statements. Figure 2 A flowchart of another method for processing SQL statements provided in an embodiment of the present application.
[0081] It should be noted that the SQL statement processing method can be executed alone, or it can be executed in combination with any embodiment in the present application or a possible implementation method in the embodiment, or it can be executed in combination with any technical solution in the relevant technology, and the embodiments of the present application are not limited to this.
[0082] like Figure 2 As shown, the method for processing the SQL statement may include the following steps S201 to S209:
[0083] Step S201, obtaining the statement type of the initial SQL statement, and in response to the statement type being a table creation type, obtaining a table attribute list in a specified keyword from the initial SQL statement.
[0084] Step S202: determine the data source type based on the table attribute pairs in the table attribute list.
[0085] The data source type is used to indicate the data storage and access method of the data table to be created by the initial SQL statement; the table attribute pair includes a table attribute key and a corresponding table attribute value.
[0086] The explanation of steps S201 to S202 can be found in the relevant description in any embodiment of the present application and will not be repeated here.
[0087] Step S203, determine whether the data source type has an associated required table attribute key, if so, execute step S204, if not, execute steps S205 to S207.
[0088] In an embodiment of the present application, required table attribute keys associated with different data source types can be pre-configured and stored based on business needs, wherein the number of required table attribute keys associated with any data source type can be 0 (i.e., there is no required table attribute key associated with the data source type), or can be 1 or more, and the embodiment of the present application does not impose any restrictions on this.
[0089] Therefore, in this application, based on the data source type in step S202, the stored data can be queried to determine whether the data source type has an associated required table attribute key. If so, step S204 is executed; if not, steps S205 to S207 are executed.
[0090] Step S204, determining whether the table attribute keys in each table attribute pair contain all the required table attribute keys, if so, executing steps S205 to S207, if not, executing steps S208 to S209.
[0091] In an embodiment of the present application, when there are required table attribute keys associated with the data source type, it can be further determined whether the table attribute keys in each table attribute pair contain all the required table attribute keys. If so, execute steps S205 to S207; if not, execute steps S208 to S209.
[0092] It should be noted that steps S205 to S207 and steps S208 to S209 are two parallel implementation methods. In actual application, only one needs to be executed.
[0093] Step S205: querying table attribute generation rules associated with the data source type.
[0094] In the embodiment of the present application, when all mandatory table attribute keys are included in each table attribute key, the table attribute generation rule associated with the data source type can be further queried.
[0095] Alternatively, when there is no required table attribute key associated with the data source type, the table attribute generation rule associated with the data source type may be further queried.
[0096] Step S206: Generate the target table attribute pairs missing in the table attribute list by using the table attribute generation rule.
[0097] Step S207: based on the target table attribute pair, the initial SQL statement is updated to obtain a target SQL statement, so as to create a data table according to the target SQL statement.
[0098] The explanation of step S206 to step S207 can be found in the relevant description in any embodiment of the present application and will not be repeated here.
[0099] Step S208: Generate prompt information based on the target required table attribute key.
[0100] The target mandatory attribute key refers to the mandatory table attribute key that is missing from the attribute keys of each table.
[0101] The prompt information is used to prompt at least one of the following:
[0102] The first item is that the target required table attribute key and the table attribute value corresponding to the target required attribute key are missing in the initial SQL statement.
[0103] The second item is to update the initial SQL statement to supplement the target mandatory table attribute key and the table attribute value corresponding to the target mandatory attribute key in the initial SQL statement.
[0104] In the embodiment of the present application, when the target required table attribute key is not included in the table attribute keys (the target required table attribute key is included in the required table attribute key associated with the data source type), prompt information can be generated based on the target required table attribute key.
[0105] Step S209: display and / or send prompt information.
[0106] In an embodiment of the present application, the prompt information can be displayed in a visual manner, and / or the prompt information can be sent to a designated device or a designated account, so that relevant personnel (such as SQL developers) can update or supplement the initial SQL statement in a timely manner based on the prompt information, thereby improving the effectiveness and timeliness of data table creation.
[0107] The SQL statement processing method of the embodiment of the present application automatically generates and fills in other business-related table attributes based on the table attribute generation rules associated with the data source type in the initial SQL statement only when there are no required table attributes missing in the initial SQL statement, so that the filled SQL statement can meet actual business needs and table creation needs.
[0108] This application embodiment provides another method for processing SQL statements. Figure 3 A flowchart of another method for processing SQL statements provided in an embodiment of the present application.
[0109] It should be noted that the SQL statement processing method can be executed alone, or it can be executed in combination with any embodiment in the present application or a possible implementation method in the embodiment, or it can be executed in combination with any technical solution in the relevant technology, and the embodiments of the present application are not limited to this.
[0110] like Figure 3 As shown, the method for processing the SQL statement may include the following steps S301 to S309:
[0111] Step S301, obtaining the statement type of the initial SQL statement, and in response to the statement type being a table creation type, obtaining a table attribute list in a specified keyword from the initial SQL statement.
[0112] Step S302: Determine the data source type based on the table attribute pairs in the table attribute list.
[0113] The data source type is used to indicate the data storage and access method of the data table to be created by the initial SQL statement; the table attribute pair includes a table attribute key and a corresponding table attribute value.
[0114] The explanation of steps S301 to S302 can be found in the relevant description in any embodiment of the present application and will not be repeated here.
[0115] Step S303: determine the business scenario to which the data table to be created by the initial SQL statement is applicable.
[0116] In an embodiment of the present application, the business scenario (or business type) to which the data table to be created by the initial SQL statement is applicable can be determined based on the configuration information, or the business scenario to which the data table to be created by the initial SQL statement is applicable can be manually specified by relevant personnel (such as SQL developers), and the embodiment of the present application does not impose any restrictions on this.
[0117] Step S304: query table creation rules associated with business scenarios and data source types.
[0118] It should be noted that for the same data source type, when the business scenarios are different, the table creation rules may be different. The table creation rules may be used to indicate whether there is an associated mandatory table attribute key for the data source type.
[0119] In an embodiment of the present application, table creation rules associated with different business scenarios and data source types can be pre-configured and stored based on business needs, so that in the present application, the table creation rules associated with the business scenarios and data source types in the above steps can be queried from the stored data.
[0120] Step S305, based on the table creation rules, determine whether there is a required table attribute key associated with the data source type, if so, execute step S306, if not, execute steps S307 to S308.
[0121] In an embodiment of the present application, a table creation rule may be used to indicate whether a data source type associated with the table creation rule has an associated required table attribute key. When it is determined based on the table creation rule that there is a required table attribute key associated with the data source type, step S306 may be executed. When it is determined based on the table creation rule that there is no required table attribute key associated with the data source type, steps S307 to S308 may be executed.
[0122] Step S306, determining whether the table attribute keys in each table attribute pair contain all the required table attribute keys, if so, executing steps S307 to S308, if not, executing step S309.
[0123] Step S307, querying the table attribute generation rule associated with the data source type, and using the table attribute generation rule to generate the target table attribute pair missing in the table attribute list.
[0124] The explanation of steps S306 to S307 can be found in the relevant description in any embodiment of the present application and will not be repeated here.
[0125] In any embodiment of the present application, when all required table attribute keys are included in each table attribute key, the table attribute generation rule associated with the data source type and the business scenario in step S303 can be further queried.
[0126] The table attribute generation rule may be used to indicate the table attribute key related to the business scenario (i.e., the table attribute key related to the actual business), and the generation rule of the table attribute value corresponding to the related table attribute key. It should be noted that for the same data source type, when the business scenario is different, the table attribute generation rule may be different.
[0127] That is to say, based on actual business needs, table attribute generation rules associated with different business scenarios and data source types can be pre-configured and stored, so that in this application, the table attribute generation rules associated with the business scenarios and data source types in the above steps can be queried from the stored data. In this way, personalized table attribute generation requirements and table creation requirements in different business scenarios can be met.
[0128] Step S308: based on the target table attribute pair, the initial SQL statement is updated to obtain a target SQL statement, so as to create a data table according to the target SQL statement.
[0129] Step S309: Generate prompt information based on the target required table attribute key, and display and / or send the prompt information.
[0130] The explanation of steps S308 to S309 can be found in the relevant description in any embodiment of the present application and will not be repeated here.
[0131] The SQL statement processing method of the embodiment of the present application determines whether there is an associated required table attribute key for the data source type based on the table creation rules associated with the business scenario and data source type applicable to the data table to be created by the initial SQL statement, which can improve the rationality and reliability of the judgment result.
[0132] This application embodiment provides another method for processing SQL statements. Figure 4 A flowchart of another method for processing SQL statements provided in an embodiment of the present application.
[0133] It should be noted that the SQL statement processing method can be executed alone, or it can be executed in combination with any embodiment in the present application or a possible implementation method in the embodiment, or it can be executed in combination with any technical solution in the relevant technology, and the embodiments of the present application are not limited to this.
[0134] like Figure 4 As shown, the method for processing the SQL statement may include the following steps S401 to S405:
[0135] Step S401, obtaining the statement type of the initial SQL statement, and in response to the statement type being a table creation type, obtaining a table attribute list in a specified keyword from the initial SQL statement.
[0136] Step S402: Determine the data source type based on the table attribute pairs in the table attribute list.
[0137] The data source type is used to indicate the data storage and access method of the data table to be created by the initial SQL statement; the table attribute pair includes a table attribute key and a corresponding table attribute value.
[0138] The explanation of steps S401 to S402 can be found in the relevant description in any embodiment of the present application and will not be repeated here.
[0139] Step S403: query the table attribute generation rules associated with the data source type, and extract key information from the initial SQL statement.
[0140] The explanation of the table attribute generation rule can refer to the relevant description in any of the above embodiments, which will not be repeated here.
[0141] In an embodiment of the present application, the initial SQL statement can be semantically parsed to extract key information from the initial SQL statement; wherein the key information includes but is not limited to: identification information of the data table to be created by the initial SQL statement (such as the table name), identification information of the database to be stored in the data table (such as the library name), and field information in the data table, etc.
[0142] Step S404: Generate a target table attribute pair missing in the table attribute list based on the key information using a table attribute generation rule.
[0143] In the embodiment of the present application, a table attribute generation rule associated with the data source type may be used to generate a target table attribute pair missing in the table attribute list based on the above key information.
[0144] For example, the table name of the data table is my_table, and the database name of the data table to be stored is db_name. Assuming that the missing table attribute key in the table attribute list includes path (storage path), the target table attribute pair may include:
[0145] 'path' = 'hdfs: / / / xxxx / lakehouse / db_name / my_table', where hdfs is the abbreviation of Hadoop Distributed File System.
[0146] For another example, assuming that the missing table attribute keys in the table attribute list include hoodie.datasource.hive_sync.table (a table for data synchronization with the data table to be created by the initial SQL statement), the target table attribute pair may include:
[0147] 'hoodie.datasource.hive_sync.table' = 'my_table_rt', where the data table to be created by the initial SQL statement can be called the source table, which is used for data write operations; my_table_rt synchronizes data with the source table and is used for data read operations to support efficient reading and writing of data in the source table.
[0148] Step S405: based on the target table attribute pair, the initial SQL statement is updated to obtain a target SQL statement, so as to create a data table according to the target SQL statement.
[0149] For the explanation of step S405, please refer to the relevant description in any embodiment of the present application, which will not be repeated here.
[0150] In any embodiment of the present application, the table attribute list can be filled based on the target table attribute pair to obtain an update list, and the table attribute list in the initial SQL statement can be replaced based on the update list using a regular expression to obtain a target SQL statement.
[0151] It should be noted that regular expressions provide powerful pattern matching capabilities and can flexibly match various complex string patterns. This allows accurate matching and replacement based on the specific format of the table attribute list when replacing the table attribute list in the SQL statement, rather than just simple string replacement, which can greatly improve the accuracy and flexibility of the replacement. In addition, using the replacement method of regular expressions, complex replacement logic can be completed in a single expression, avoiding the need to write a large number of conditional judgments or loop statements to check and replace characters or strings one by one, which not only reduces the amount of code, but also improves the readability and maintainability of the code.
[0152] The SQL statement processing method of the embodiment of the present application generates target table attribute pairs missing in the table attribute list based on key information in the initial SQL statement and table attribute generation rules associated with the data source type, which can improve the effectiveness and accuracy of generating the target table attribute pairs.
[0153] In order to clearly explain how to obtain the statement type of the initial SQL statement in any embodiment of the present application, the present application also proposes a method for processing SQL statements.
[0154] Figure 5A flowchart of another method for processing SQL statements provided in an embodiment of the present application.
[0155] like Figure 5 As shown, based on any of the above embodiments, the following steps S501 to S503 may be used to obtain the statement type of the initial SQL statement:
[0156] Step S501, lexical analysis is performed on the initial SQL statement to obtain a token sequence; wherein the token sequence includes a plurality of tokens obtained by lexical decomposition of the initial SQL statement.
[0157] Each token is an independent unit in the initial SQL statement, such as a keyword, an identifier, a literal, etc.
[0158] In the embodiment of the present application, a lexical analysis may be performed on the initial SQL statement, the initial SQL statement may be decomposed into a plurality of tokens, and a token sequence may be generated according to the plurality of tokens obtained by the decomposition.
[0159] Step S502, performing grammatical analysis on the tag sequence to combine multiple tags to obtain an abstract syntax tree; wherein the nodes in the abstract syntax tree are used to indicate tags, and the edges between the nodes are used to indicate the grammatical relationship between the tags.
[0160] In an embodiment of the present application, a grammatical analysis can be performed on multiple tags in a tag sequence, and the multiple tags can be combined into an abstract syntax tree according to the grammatical rules of SQL, wherein the abstract syntax tree is a tree structure, wherein the nodes are used to indicate different components (i.e., tags) in the initial SQL statement, and the edges between the nodes are used to represent the grammatical relationship between these components (i.e., tags).
[0161] Step S503: perform semantic analysis and statement type identification on the abstract syntax tree to obtain the statement type to which the initial SQL statement belongs.
[0162] In the embodiment of the present application, semantic analysis and statement type identification may be performed on the abstract syntax tree to obtain the statement type to which the initial SQL statement belongs.
[0163] As an example, the statement type to which the initial SQL statement belongs can be identified by checking the root node or a specific type of child node of the abstract syntax tree. For example, if the root node or a key child node of the abstract syntax tree represents a table creation operation (such as CREATE TABLE), then the statement type of the initial SQL statement is determined to be a table creation type.
[0164] The SQL statement processing method of the embodiment of the present application can improve the accuracy and reliability of statement type identification by performing semantic analysis on the initial SQL statement to identify the statement type to which the initial SQL statement belongs.
[0165] In any embodiment of the present application, the SQL statement for creating a table can be optimized. When the SQL developer writes the SQL statement for creating a table, he only needs to fill in some core or required table attributes. Other business-related table attributes are automatically filled in during the SQL statement submission and execution process, so as to improve the development efficiency of the SQL developer and lower the threshold for using FlinkSQL.
[0166] As an example, the initial SQL statement submitted by the user is semantically parsed to obtain the statement type. If the statement type is CREATE TABLE (create table) type, the table attribute list in the WITH keyword in the initial SQL statement is further obtained, and the data source type is determined according to the table attribute value corresponding to the connector in the table attribute list. Then, according to the data source type, it can be judged whether the required table attributes in the table attribute list are complete. If so, different business-related table attributes are generated and filled based on the specific data source type. After the table attribute list is filled, the table attributes in the initial SQL statement are replaced by regular expressions to obtain the target SQL statement, and finally the generated target SQL statement is submitted to the FlinkSQL engine for execution to create a data table. The processing or optimization principle of the SQL statement provided in this application can be as follows Figure 6 As shown, it mainly includes the following steps:
[0167] Step 1: The user submits the initial SQL statement (or original SQL statement) for creating a table.
[0168] Step 2: semantically parse the initial SQL statement to obtain the statement type.
[0169] Step 3: Determine whether the statement type is CREATE TABLE (create table) type. If so, execute step 5 and subsequent steps. If not, execute step 4.
[0170] Step 4: Submit the initial SQL statement to the FlinkSQL engine for execution.
[0171] Step 5, obtain the table attribute list in the WITH keyword in the initial SQL statement.
[0172] Step 6: Determine the data source type according to the table attribute value corresponding to the table attribute list connector, and based on the data source type, determine whether the required table attributes in the table attribute list are complete. If so, execute step 8 and subsequent steps; if not, execute step 7.
[0173] Step 7: throw out a clear error prompt (referred to as prompt information in this application).
[0174] Step 8: Fill the required business-related table attributes into the table attribute list according to the table attribute value corresponding to the connector and other information.
[0175] Step 9: Generate a new SQL statement for creating a table (referred to as a target SQL statement in this application) based on the updated table attribute list (referred to as an update list in this application).
[0176] Step 10: Submit the target SQL statement to the FlinkSQL engine for execution to create a data table.
[0177] Assuming that the library name in the initial SQL statement is db_name, the table name is my_table; the field list is id, load_time, dt, where id is the primary key and dt is the partition key, and the table attribute keys in the table attribute list in the WITH keyword include: connector, table.type, write.precombine.field, then according to the table attribute value of the connector (such as hudi), it can be determined that the table type of the data table to be created is a hudi table. At this time, according to the built-in hudi table creation rules (different business scenarios can be built-in according to actual needs), it can be determined whether the table attribute list contains all the required table attribute keys. If not, an error message indicating that the required table attribute key xx is missing is thrown. If so, the table attribute list is filled according to the built-in table attribute generation rules of the hudi table (different business scenarios can be built-in according to actual needs). For example, the storage path of the data table is generated according to the library name and table name. After the table attribute list is filled, the table attribute list in the initial SQL statement is replaced by a regular expression to generate a new target SQL statement, and finally the target SQL statement is submitted to the FlinkSQL engine for execution.
[0178] Compared with the prior art, the SQL statement processing method provided in this application has at least the following advantages:
[0179] 1) Simplify the SQL statements written by SQL developers for table creation and improve the development efficiency of SQL statements;
[0180] 2) Lower the threshold for using FlinkSQL and improve its usability;
[0181] 3) For SQL statements used to create tables, different table attributes are automatically filled in according to the different attribute values of the connector, simplifying FlinkSQL's table creation statements and improving usability.
[0182] Corresponding to the SQL statement processing methods provided in the above-mentioned embodiments, an embodiment of the present application further provides a SQL statement processing device. Since the SQL statement processing device provided in the embodiment of the present application corresponds to the SQL statement processing methods provided in the above-mentioned embodiments, the implementation method of the SQL statement processing method is also applicable to the SQL statement processing device provided in this embodiment, and will not be described in detail in this embodiment.
[0183] Figure 7 The figure is a schematic diagram of the structure of a device for processing SQL statements according to an embodiment of the present application.
[0184] like Figure 7 As shown, the SQL statement processing device 700 may include: an acquisition module 710 , a determination module 720 , a first generation module 730 , and an update module 740 .
[0185] The acquisition module 710 is used to acquire the statement type of the initial SQL statement, and in response to the statement type being a table creation type, acquire a table attribute list in a specified keyword from the initial SQL statement;
[0186] The determination module 720 is used to determine the data source type based on the table attribute pair in the table attribute list; wherein the data source type is used to indicate the data storage and access mode of the data table to be created by the initial SQL statement; the table attribute pair includes a table attribute key and a corresponding table attribute value;
[0187] A first generating module 730, configured to generate a target table attribute pair missing in the table attribute list based on a table attribute generating rule associated with a data source type;
[0188] The updating module 740 is used to update the initial SQL statement based on the target table attribute pair to obtain the target SQL statement, so as to create a data table according to the target SQL statement.
[0189] As a possible implementation method of an embodiment of the present application, the first generation module 730 is used to: when there is an associated mandatory table attribute key for the data source type, determine whether the table attribute keys in each table attribute pair contain all the mandatory table attribute keys; if all the mandatory table attribute keys are contained in each table attribute key, query the table attribute generation rule associated with the data source type; and use the table attribute generation rule to generate the target table attribute pair missing in the table attribute list.
[0190] As a possible implementation method of an embodiment of the present application, the first generation module 730 is used to: determine the business scenario applicable to the data table; query the table creation rules associated with the business scenario and the data source type; based on the table creation rules, determine whether there are required table attribute keys associated with the data source type; if so, determine whether the table attribute keys in each table attribute pair contain all the required table attribute keys.
[0191] As a possible implementation method of an embodiment of the present application, the first generation module 730 is used to: if all required table attribute keys are included in each table attribute key, query the table attribute generation rules associated with the data source type and the business scenario; wherein the table attribute generation rules are used to indicate the table attribute keys related to the business scenario, and the generation rules of the table attribute values corresponding to the related table attribute keys.
[0192] As a possible implementation of the embodiment of the present application, the SQL statement processing device 700 further includes:
[0193] A second generating module is used to generate prompt information based on the target required table attribute key if the target required table attribute key is not included in the table attribute keys;
[0194] A processing module is used to display and / or send prompt information; wherein the prompt information is used to prompt at least one of the following: the target required table attribute key and the corresponding table attribute value are missing in the initial SQL statement; the initial SQL statement is updated to supplement the target required table attribute key and the corresponding table attribute value.
[0195] As a possible implementation method of an embodiment of the present application, the first generation module 730 is used to: extract key information from an initial SQL statement; wherein the key information includes at least one of the following: identification information of the data table, identification information of the database to be stored in the data table, and field information in the data table; and generate a target table attribute pair based on the key information using a table attribute generation rule.
[0196] As a possible implementation method of the embodiment of the present application, the update module 740 is used to: fill the table attribute list based on the target table attribute pair to obtain an update list; use a regular expression to replace the table attribute list in the initial SQL statement based on the update list to obtain a target SQL statement.
[0197] As a possible implementation method of an embodiment of the present application, the acquisition module 710 is used to: perform lexical analysis on the initial SQL statement to obtain a tag sequence; wherein the tag sequence includes multiple tags obtained by lexical decomposition of the initial SQL statement; perform syntactic analysis on the tag sequence to combine multiple tags to obtain an abstract syntax tree; wherein the nodes in the abstract syntax tree are used to indicate tags, and the edges between the nodes are used to indicate the grammatical relationship between the tags; perform semantic analysis and statement type identification on the abstract syntax tree to obtain the statement type to which the initial SQL statement belongs.
[0198] In the SQL statement processing device in the embodiment of the present application, when an SQL developer writes an SQL statement for creating a table, he only needs to fill in some core or required table attributes. Other business-related table attributes can be automatically generated and filled based on the table attribute generation rules associated with the data source type in the SQL statement, thereby improving the development efficiency of SQL developers (i.e., improving the efficiency of writing SQL statements) and lowering technical and usage thresholds.
[0199] In order to implement the above embodiment, the present application also proposes an electronic device, Figure 8 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. The electronic device includes:
[0200] A memory 801 , a processor 802 , and a computer program stored in the memory 801 and executable on the processor 802 .
[0201] When the processor 802 executes the program, the SQL statement processing method provided in any of the above embodiments is implemented.
[0202] Furthermore, the electronic device further comprises:
[0203] The communication interface 803 is used for communication between the memory 801 and the processor 802 .
[0204] The memory 801 is used to store computer programs that can be executed on the processor 802 .
[0205] The memory 801 may include a high-speed RAM memory, and may also include a non-volatile memory (non-volatile memory), such as at least one disk memory.
[0206] The processor 802 is used to implement the SQL statement processing method described in any of the above embodiments when executing the program.
[0207] If the memory 801, the processor 802 and the communication interface 803 are implemented independently, the communication interface 803, the memory 801 and the processor 802 can be connected to each other through a bus and communicate with each other. The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component (PCI) bus or an Extended Industry Standard Architecture (EISA) bus. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 8 Only one thick line is used in the diagram, but this does not mean that there is only one bus or only one type of bus.
[0208] Optionally, in a specific implementation, if the memory 801, the processor 802 and the communication interface 803 are integrated on a chip, the memory 801, the processor 802 and the communication interface 803 can communicate with each other through an internal interface.
[0209] The processor 802 may be a central processing unit (CPU), or an application specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application.
[0210] In order to implement the above embodiments, the embodiments of the present application further provide a non-temporary computer-readable storage medium on which a computer program is stored. When the program is executed by a processor, a method for processing SQL statements provided in any of the above embodiments is implemented.
[0211] In order to implement the above embodiments, the embodiments of the present application further provide a computer program product, which, when an instruction processor in the computer program product is executed, implements the SQL statement processing method provided in any of the above embodiments.
[0212] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.
[0213] In addition, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In the description of this application, the meaning of "plurality" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined.
[0214] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, fragment or portion of code comprising one or more executable instructions for implementing the steps of a custom logical function or process, and the scope of the preferred embodiments of the present application includes alternative implementations in which functions may not be performed in the order shown or discussed, including performing functions in a substantially simultaneous manner or in the reverse order depending on the functions involved, which should be understood by technicians in the technical field to which the embodiments of the present application belong.
[0215] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute the instructions), or in combination with these instruction execution systems, devices or apparatuses. For the purpose of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in combination with these instruction execution systems, devices or apparatuses. More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium and then editing, interpreting or processing in other suitable ways if necessary, and then stored in a computer memory.
[0216] It should be understood that the various parts of the present application can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.
[0217] A person skilled in the art may understand that all or part of the steps in the method for implementing the above-mentioned embodiment may be completed by instructing related hardware through a program, and the program may be stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiment.
[0218] In addition, each functional unit in each embodiment of the present application may be integrated into a processing module, or each unit may exist physically separately, or two or more units may be integrated into one module. The above-mentioned integrated module may be implemented in the form of hardware or in the form of a software functional module. If the integrated module is implemented in the form of a software functional module and sold or used as an independent product, it may also be stored in a computer-readable storage medium.
[0219] The storage medium mentioned above may be a read-only memory, a magnetic disk or an optical disk, etc. Although the embodiments of the present application have been shown and described above, it can be understood that the above embodiments are exemplary and cannot be understood as limiting the present application. A person of ordinary skill in the art may change, modify, replace and modify the above embodiments within the scope of the present application.
Claims
1. A method for processing a structured query language SQL statement, characterized in that: include: Acquire a statement type of an initial SQL statement, and in response to the statement type being a table creation type, acquire a table attribute list in a specified keyword from the initial SQL statement; Determine a data source type based on the table attribute pairs in the table attribute list; wherein the data source type is used to indicate the data storage and access mode of the data table to be created by the initial SQL statement; the table attribute pairs include a table attribute key and a corresponding table attribute value; Generate a target table attribute pair missing in the table attribute list based on a table attribute generation rule associated with the data source type; Based on the target table attribute pair, the initial SQL statement is updated to obtain a target SQL statement, so as to create the data table according to the target SQL statement.
2. The method according to claim 1, characterized in that: The generating the target table attribute pair missing from the table attribute list based on the table attribute generation rule associated with the data source type includes: In the case where the data source type has associated mandatory table attribute keys, determining whether the table attribute keys in each of the table attribute pairs contain all the mandatory table attribute keys; If all the required table attribute keys are included in the table attribute keys, querying the table attribute generation rule associated with the data source type; The table attribute generation rule is adopted to generate target table attribute pairs missing in the table attribute list.
3. The method according to claim 2, characterized in that In the case where there are required table attribute keys associated with the data source type, judging whether the table attribute keys in each of the table attribute pairs contain all the required table attribute keys includes: Determine the business scenario to which the data table applies; Querying table creation rules associated with the business scenario and the data source type; Based on the table creation rule, determining whether there is a required table attribute key associated with the data source type; If so, it is determined whether the table attribute keys in each of the table attribute pairs contain all of the required table attribute keys.
4. The method according to claim 3, characterized in that If all the required table attribute keys are included in each of the table attribute keys, querying the table attribute generation rules associated with the data source type includes: If all the required table attribute keys are included in the table attribute keys, querying the table attribute generation rule associated with the data source type and the business scenario; The table attribute generation rule is used to indicate the table attribute key related to the business scenario, and the generation rule of the table attribute value corresponding to the related table attribute key.
5. The method according to claim 2, characterized in that: The method further comprises: If the target mandatory table attribute key is not included in the table attribute keys, generating prompt information based on the target mandatory table attribute key; Display and / or send the prompt information; wherein the prompt information is used to prompt at least one of the following: The target required table attribute key and the corresponding table attribute value are missing in the initial SQL statement; The initial SQL statement is updated to supplement the target required table attribute key and the corresponding table attribute value.
6. The method according to claim 1, characterized in that The generating the target table attribute pair missing from the table attribute list based on the table attribute generation rule associated with the data source type includes: Extracting key information from the initial SQL statement; wherein the key information includes at least one of the following: identification information of the data table, identification information of the database to which the data table is to be stored, and field information in the data table; The target table attribute pair is generated based on the key information using the table attribute generation rule.
7. The method according to any one of claims 1 to 6, characterized in that The updating of the initial SQL statement based on the target table attribute pair to obtain the target SQL statement includes: Based on the target table attribute pair, the table attribute list is filled to obtain an update list; The table attribute list in the initial SQL statement is replaced based on the update list using a regular expression to obtain the target SQL statement.
8. A SQL statement processing device, characterized in that: include: An acquisition module, configured to acquire a statement type of an initial SQL statement, and in response to the statement type being a table creation type, acquire a table attribute list in a specified keyword from the initial SQL statement; A determination module, configured to determine a data source type based on a table attribute pair in the table attribute list; wherein the data source type is used to indicate a data storage and access method of a data table to be created by the initial SQL statement; and the table attribute pair includes a table attribute key and a corresponding table attribute value; A first generating module, configured to generate a target table attribute pair missing in the table attribute list based on a table attribute generating rule associated with the data source type; An updating module is used to update the initial SQL statement based on the target table attribute pair to obtain a target SQL statement, so as to create the data table according to the target SQL statement.
9. An electronic device, characterized in that: include: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method for processing an SQL statement according to any one of claims 1 to 7 when executing the program.
10. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method for processing an SQL statement as described in any one of claims 1 to 7 is implemented.