Unified SQL methods, systems, devices and media

Abstract

This invention discloses a method, system, device, and medium for unified SQL, relating to the field of SQL query technology. It solves the technical problem of using different SQL for analysis and calculation in different computing architectures. The key point of its technical solution is that it uses unified SQL to shield users from the differences in underlying storage and computing, enabling users to submit and execute in multiple database systems by coding sequentially, thus achieving cross-platform migration of applications. It also translates unified SQL into SQL syntax for various different databases, supporting capability expansion and compatibility with multiple databases.

Description

Technical Field

[0001] This disclosure relates to the field of SQL query technology, and in particular to a method, system, device and medium for unified SQL. Background Technology

[0002] The trend in big data development suggests a future emphasis on "heavy association, light collection," meaning data will no longer be collected locally beforehand and then uniformly computed and analyzed through big data computing platforms (such as Hadoop and Spark). This technology was repeatedly mentioned at the 2018 Gantner CIA (Controller Area Network In Automation) Summit and the 2019 BDTC (Big Data Technology Conference). The future will see more use of a single query and analysis engine to perform joint analysis across multiple data sources, such as cross-Spark, Greenplum, and Hive queries, and intelligently push operators to the appropriate computing engine based on data characteristics.

[0003] Currently, big data application analysis systems still use SQL as the data query and analysis language. However, due to differences in underlying storage and computing architecture, computing models, and data formats, various big data open-source software have different requirements and syntax for SQL, such as Apache Spark SQL and Hive. The SQL requirements and syntax of large-scale MPP (Massively Parallel Processing) database software are also different, such as Greenplum and GBase.

[0004] Based on scenario considerations, application systems will adopt different component deployment strategies to address differences in storage and computing costs. In large-scale scenarios, Spark, Hive, and other big data platforms will be used as computing engines, while Greenplum, GBase, and other similar platforms will be used as computing engines in small-scale scenarios. When different versions of the application's business software and code are used, development efficiency is low and manpower consumption is high. Summary of the Invention

[0005] This disclosure provides a method, system, device, and medium for unified SQL. Its technical objective is to shield the differences between various data computing engines through a unified standard SQL syntax, enabling applications to use unified SQL to analyze and compute data on different computing architectures; at the same time, it achieves the goal of cross-platform application migration through the development of unified SQL syntax.

[0006] The above-mentioned technical objective of this disclosure is achieved through the following technical solution:

[0007] A unified SQL approach includes:

[0008] Define a unified SQL syntax standard;

[0009] The unified SQL is parsed into a syntax tree;

[0010] The syntax tree is translated according to different SQL syntaxes to obtain multiple syntax trees;

[0011] The various syntax trees are de-parsed to obtain SQL strings from different databases;

[0012] Submit SQL strings from different databases to the environment for execution and return the data results.

[0013] Furthermore, before translating the syntax tree, a validity check is performed on the syntax tree, and warnings are given for non-standard syntax and functions.

[0014] Furthermore, after the legality check is completed, it is determined whether the Form object of the syntax tree is a physical table or a logical table. If it is a physical table, the syntax tree is translated directly.

[0015] If it is a logical table, then determine whether the logical table is in view mode or physical table mode. If it is in view mode, then optimize the syntax tree according to the optimization rules, and then translate the optimized syntax tree.

[0016] If the logical table is a physical table schema, then an execution plan for the actual physical table is generated.

[0017] Furthermore, a query SQL based on the logical table is constructed. The query SQL queries the logical table according to the query conditions. The logical table determines whether it is in view mode or physical table mode based on the scenario.

[0018] Furthermore, the optimization rules include filter pushdown rules and field pruning rules.

[0019] A unified SQL system, comprising:

[0020] Define modules to define a unified SQL syntax standard;

[0021] The parsing module parses the unified SQL into a syntax tree;

[0022] The translation module translates the syntax tree according to different SQL syntaxes to obtain multiple syntax trees;

[0023] The de-parsing module performs de-parsing on the various syntax trees to obtain SQL strings from different databases;

[0024] The execution module submits SQL strings from different databases to the environment for execution and returns the data results.

[0025] Furthermore, the system also includes an inspection module. Before translating the syntax tree, the inspection module performs a validity check on the syntax tree and provides warnings for non-standard syntax and functions.

[0026] Furthermore, the system also includes:

[0027] The first judgment module determines whether the Form object of the syntax tree is a physical table or a logical table after the legality check is completed.

[0028] The second judgment module determines whether the logical table is in view mode or physical table mode;

[0029] The optimization module optimizes the syntax tree according to optimization rules when the logical table is in view mode.

[0030] When creating a composite module, if the logical table is in physical table mode, an execution plan for the actual physical table is generated.

[0031] Furthermore, the second determination module includes:

[0032] The query unit constructs a query SQL based on the logical table, wherein the query SQL queries the logical table according to query conditions.

[0033] The judgment unit determines whether the logical table is in view mode or physical table mode based on the scenario.

[0034] A unified SQL implementation device includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the unified SQL method as described in any one of claims 1-4.

[0035] A computer medium storing a computer program that, when executed by a processor, implements the method of Unified SQL as described in any one of claims 1-4.

[0036] The beneficial effects of this disclosure are as follows: The unified SQL method, system, device and medium described in this disclosure shield users from the differences in underlying storage and computation by using unified SQL, enabling users to submit and execute in multiple database systems by coding sequentially, thus achieving cross-platform migration of applications; the unified SQL is translated into SQL syntax for multiple different databases, which can support capability expansion and is compatible with multiple databases. Attached Figure Description

[0037] Figure 1This is a flowchart of the method disclosed herein;

[0038] Figure 2 This is a schematic diagram of the system disclosed herein;

[0039] Figure 3 This is a flowchart of Example 1;

[0040] Figure 4 This is a flowchart of Example 2;

[0041] Figure 5 A flowchart for optimizing logical table queries;

[0042] Figure 6 This is a schematic diagram of Example 3. Detailed Implementation

[0043] The technical solution of this disclosure will now be described in detail with reference to the accompanying drawings. In the description of this disclosure, it should be understood that the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated, but are only used to distinguish different components.

[0044] Figure 1 The flowchart of the method disclosed herein is as follows: Figure 1 As shown, the first step is to define a unified SQL syntax standard, such as using SQL99 as a base, while ensuring compatibility with various SQL standard syntaxes, providing users with a unified SQL language including DDL (Data Definition Language), DQL (Data Query Language), and DML (Data Manipulation Language). Then, the unified SQL is parsed into a syntax tree, syntax parsing rules are defined, and the syntax parsing of user-written unified SQL is implemented, converting it into a syntax tree.

[0045] The syntax tree is translated according to different SQL syntaxes to obtain multiple syntax trees. For example, it can be translated according to Spark SQL syntax to construct a Spark SQL syntax tree, and simultaneously translated according to GreenPlum SQL syntax to construct a GreenPlum SQL syntax tree. This can be extended to translate according to the dialects of other database syntaxes. Then, the multiple syntax trees are reverse-parsed to obtain SQL strings for different databases. For example, they can be parsed into Spark SQL strings, GreenPlum SQL strings, and other database SQL strings. Finally, depending on the physical environment configuration, the corresponding SQL strings from the parsed SQL strings for different databases are submitted to the environment for execution, and the data results are returned.

[0046] Figure 2 This is a schematic diagram of the system disclosed herein, such as Figure 2 As shown, the functions of each module in this system are referenced. Figure 1 The methods described will not be elaborated further.

[0047] Implementation Benefit 1: Before translating the syntax tree, a validity check is performed on the syntax tree itself. The SQL syntax of the tree nodes is validated to ensure it conforms to standard syntax or functions. Warnings are issued for non-standard syntax and functions. Figure 3 As shown.

[0048] Example 2: After the validity check is completed, it is determined whether the Form object in the syntax tree is a physical table or a logical table. If it is a physical table, the syntax tree is directly translated. If it is a logical table, it is determined whether the logical table is in view mode or physical table mode. If the logical table is in view mode, the syntax tree is optimized according to the optimization rules, and the view is not actually executed, thus not introducing computational overhead. If the logical table is in physical table mode, an execution plan for the actual physical table is generated, such as... Figure 4 As shown.

[0049] Optimization rules include filter pushdown and field pruning. Filter pushdown refers to pushing the original query conditions into the filter conditions of the logical table when it is invoked, provided that the logical table contains the field information of the query conditions. Field pruning refers to reducing the number of fields defined in the original logical table to only the fields used in the query when it is invoked. This avoids calculating the full dataset and saves computing resources. If the logical table is a physical table schema, an execution plan for the actual physical table is generated to calculate the full dataset. In this scenario, the full dataset needs to be prepared in advance for the application.

[0050] The process of implementing query optimization using logical tables is as follows: Figure 5As shown, the specific steps are as follows: (1) Construct the schema of the logical table. The logical table is visible to the business. The logical table can be switched according to the differences in the underlying operating environment. It may be a view mode or a physical table mode. For example: create logic table fact_mr_only as select a as cellid,b as name from ods_mr; (2) Construct a query SQL based on the logical table. Only query some fields in the logical table. For example, there are 2 fields in the logical table, but only 1 field is found in the query statement. And add a certain filter condition to the query conditions, such as filtering out records where the cell ID is equal to 1: select cellid from fact_mr_only where cellid=1; (3) Determine whether the logical table is in view mode according to the scenario. If it is necessary to save computing resources and delay the calculation, then use view mode. If it is necessary to perform full calculation, then use physical table mode and prepare full data in advance; (4) If it is in view mode, then it needs to go through filtering pushdown, field pruning and other processing. The calculation will be introduced after the query SQL is executed. The expanded query statement is: select cellid from(select a as cellid from ods_mr where cellid=1); If it is a physical table mode, a table fact_mr_only with two fields cellid and name will be created first, and the values ​​of a and b fields in ods_mr table will be inserted into the fact_mr_only table; (5) Finally, the SQL statement is submitted to the computing engine and the execution result is returned.

[0051] Example 3: Figure 6 This is a schematic diagram of Embodiment 3, that is, a schematic diagram of the system of this disclosure. The specific implementation of each module is as described above and will not be repeated here.

[0052] The above are exemplary embodiments of this disclosure, and the scope of protection of this disclosure is defined by the claims and their equivalents.

Claims

1. A unified SQL method, characterized in that... ,include: Define a unified SQL syntax standard; The unified SQL is parsed into a syntax tree; The syntax tree is translated according to different SQL syntaxes to obtain multiple syntax trees; The various syntax trees are de-parsed to obtain SQL strings from different databases; Submit SQL strings from different databases to the environment for execution and return the data results; The method further includes: Before translating the syntax tree, a validity check is performed on the syntax tree, and warnings are given for non-standard syntax and functions; After the legality check is completed, it is determined whether the Form object of the syntax tree is a physical table or a logical table. If it is a physical table, the syntax tree is translated directly. If it is a logical table, then determine whether the logical table is in view mode or physical table mode. If it is in view mode, then optimize the syntax tree according to the optimization rules, and then translate the optimized syntax tree. If the logical table is a physical table schema, then an execution plan for the actual physical table is generated.

2. The unified SQL method as described in claim 1, characterized in that, Construct a query SQL based on the logical table. The query SQL queries the logical table according to the query conditions. Based on the scenario, determine whether the logical table is in view mode or physical table mode.

3. The unified SQL method as described in claim 2, characterized in that, The optimization rules include filter pushdown rules and field pruning rules.

4. A unified SQL system, characterized in that, include: Define modules to define a unified SQL syntax standard; The parsing module parses the unified SQL into a syntax tree; The translation module translates the syntax tree according to different SQL syntaxes to obtain multiple syntax trees; The de-parsing module performs de-parsing on the various syntax trees to obtain SQL strings from different databases; The execution module submits SQL strings from different databases to the environment for execution and returns the data results; The system also includes: Before translating the syntax tree, the inspection module performs a legality check on the syntax tree and issues warnings for non-standard syntax and functions. The first judgment module determines whether the Form object of the syntax tree is a physical table or a logical table after the legality check is completed. The second judgment module determines whether the logical table is in view mode or physical table mode; The optimization module optimizes the syntax tree according to optimization rules when the logical table is in view mode. When creating a composite module, if the logical table is in physical table mode, an execution plan for the actual physical table is generated.

5. The unified SQL system as described in claim 4, characterized in that, The second judgment module includes: The query unit constructs a query SQL based on the logical table, wherein the query SQL queries the logical table according to query conditions. The judgment unit determines whether the logical table is in view mode or physical table mode based on the scenario.

6. The unified SQL system as described in claim 5, characterized in that, The optimization rules include filter pushdown rules and field pruning rules.

7. A device for implementing Unified SQL, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the method of unified SQL as described in any one of claims 1-2.

8. A computer medium, characterized in that, The computer medium stores a computer program that, when executed by a processor, implements the method of Unified SQL as described in any one of claims 1-2.

Images