Method for constructing data visual query based on DSL and SQL abstract syntax tree

Abstract

The invention provides a method for constructing a data visual query based on a DSL and SQL abstract syntax tree, which comprises the following steps: S1, constructing a human-computer interaction interface for a user to select a chart type and data corresponding to a chart, and generating a DSL corresponding to the chart; s2, defining a semantic layer, namely analyzing the DSL into a corresponding DSL grammar fragment; s3, abstract syntax tree node conversion; s4, constructing the abstract syntax tree nodes into an abstract syntax tree; s5, performing query optimization, namely performing query optimization on the abstract syntax tree by utilizing a query optimizer; according to the method, the data visualization query SQL is constructed on the basis of the DSL and the abstract syntax tree, data visualization query construction is achieved by customizing the DSL and the mode conversion rule of the DSL and SQL abstract syntax tree nodes, the method is achieved on the basis of the DSL and the abstract syntax tree, compared with a traditional method, the method is more flexible and efficient, the generated SQL has higher interpretability, and the method has the advantages of being high in practicability and the like. And the support to the database / OLAP engine is wider.

Description

Technical Field

[0001] The present invention relates to a data visualization query method, in particular to a method for constructing data visualization query based on DSL and SQL abstract syntax tree, belonging to the technical field of data processing. Background Art

[0002] Data visualization query refers to the process of presenting data using graphics, images and other visual elements so that users can understand and analyze data more intuitively and effectively. Data visualization query can help users discover patterns, trends and associations in data, so as to make more accurate decisions;

[0003] There are many tools and platforms for data visualization query, such as Tableau, Power BI, Google Charts, etc. These tools and platforms provide a variety of visualization charts and dashboards, such as bar charts, line charts, pie charts, maps, heat maps, etc., to meet different data analysis and display needs.

[0004] DSL is the abbreviation of "Digital Subscriber Line", which is a technology used to access the Internet through telephone. DSL provides high-speed Internet access through telephone lines, which separates voice signals and data signals, allowing users to use the phone while surfing the Internet.

[0005] SQL is the abbreviation of Structured Query Language, which is a standard programming language for managing and operating relational database management systems. SQL is used to perform various operations such as querying, updating, inserting, and deleting data in the database.

[0006] CTE is the abbreviation of "Common Table Expression", which is a clause of a database query. It is used to create a temporary namespace in a SQL query that is only visible in this query, so as to perform a series of query operations in this namespace without affecting the external namespace.

[0007] The traditional method based on SQL template generates SQL for data visualization query. It is based on a specific database / OLAP engine and can neither flexibly meet the needs of visual editing of data and visual analysis nor be compatible with non-standard database / OLAP engines. In addition, the SQL for visual query is lengthy and inefficient, SQL debugging is difficult, and the interpretability is insufficient. When the data does not meet expectations, it is impossible to quickly locate / debug the inaccurate fragments. Therefore, a method for building data visualization query based on DSL and SQL abstract syntax tree is proposed. Summary of the invention

[0008] In view of this, the present invention provides a method for constructing data visualization query based on DSL and SQL abstract syntax tree to solve or alleviate the technical problems existing in the prior art and at least provide a beneficial choice.

[0009] The technical solution of the embodiment of the present invention is implemented as follows: a method for constructing data visualization query based on DSL and SQL abstract syntax tree, comprising the following steps:

[0010] S1. Build a human-computer interaction interface - for users to select the chart type and the data corresponding to the chart, and generate the DSL corresponding to the chart;

[0011] S2, define the semantic layer - parse the DSL into corresponding DSL syntax fragments;

[0012] S3, abstract syntax tree node conversion;

[0013] S4, constructing the abstract syntax tree node into an abstract syntax tree;

[0014] S5, query optimization - use the query optimizer to optimize the abstract syntax tree;

[0015] S6, Abstract syntax tree conversion - The semantic layer converts the abstract syntax tree into SQL that can be interpreted and executed by the database / OLAP engine through the dialect translation mechanism and logic compiler;

[0016] S7. SQL submission and execution - submit the SQL to the corresponding database / OLAP engine for execution to obtain data for building visual analysis charts.

[0017] Further preferably, in said S1, the data corresponding to the chart includes corresponding dimensions, metrics, comparison fields and calculation methods;

[0018] The dimension is the X-axis, which represents classification, and the measure is the Y-axis, which represents aggregation.

[0019] Further preferably, in S2, after the user submits the DSL of the chart, the semantic layer parses the DSL into corresponding DSL segments.

[0020] Further preferably, in S3, the DSL fragment is converted into an abstract syntax tree node using a mapping rule;

[0021] The mapping rules are as follows: the dimension is used as the Group By clause and the Select Item according to the conversion method declared in the DSL; the measure is used as the Aggregation clause according to the aggregation method declared in the DSL.

[0022] Further preferably, in S4, the abstract syntax tree nodes are constructed into an abstract syntax tree according to algebraic relations.

[0023] Further preferably, in S5, query optimization includes but is not limited to projection pruning, predicate pruning, constant condition elimination and constant conversion;

[0024] Among them, in projection pruning, the query optimizer will prune redundant column projections under semantic circumstances;

[0025] Among them, in the predicate pruning, the restrictive predicates exist user_id>=1and user_id>=2, so the restrictive predicate can be pruned to user_id>=2;

[0026] Among them, in the constant condition elimination, the restrictive predicate exists user_id = user_id, and the restrictive predicate is always established. Therefore, this restrictive constant condition can be completely cut;

[0027] Among them, in the constant conversion, the restrictive predicates exist user_id=10and order_pay_total>=user_id. Because there is a restriction of user_id=10, order_pay_total>=user_id will be replaced by order_pay_total>=10.

[0028] Further preferably, in S6, after SQL is translated into the dialect, the general SQL abstract syntax tree is converted into a specific abstract syntax tree.

[0029] Further preferably, in S6, the logic compiler uses the characteristics of CTE to construct SQL, and the logic compiler runs on the abstract syntax tree, and converts the abstract syntax tree into interpretable and executable SQL according to the SQL syntax of different databases / OLAP engines.

[0030] Further preferably, in S7, the obtained SQL is submitted to the corresponding database / OLAP engine by utilizing the data visualization system and executed to obtain data for constructing a visual analysis chart.

[0031] The embodiment of the present invention has the following advantages due to the adoption of the above technical solution:

[0032] 1. The present invention constructs data visualization query SQL based on DSL and abstract syntax tree, and realizes data visualization query construction by customizing DSL and pattern conversion rules of DSL and SQL abstract syntax tree nodes. It is more flexible and efficient than traditional methods based on DSL and abstract syntax tree, and the generated SQL has stronger interpretability and wider support for database / OLAP engines.

[0033] 2. The logic translation compiler of the present invention uses CTE characteristics to construct SQL. The generated SQL conforms to the logical flow of data modeling and data analysis and has good explainability and checkability.

[0034] The above summary is for illustrative purposes only and is not intended to be limiting in any way. In addition to the illustrative aspects, embodiments and features described above, further aspects, embodiments and features of the present invention will be readily apparent by reference to the accompanying drawings and the following detailed description. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] 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. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0036] Figure 1 It is a structural diagram of the present invention;

[0037] Figure 2 This is a schematic diagram of a table example in Embodiment 2 of the present invention;

[0038] Figure 3 A diagram schematically showing a data visualization according to the present invention. DETAILED DESCRIPTION

[0039] In the following, only some exemplary embodiments are briefly described. As those skilled in the art will appreciate, the described embodiments may be modified in various ways without departing from the spirit or scope of the present invention. Therefore, the drawings and descriptions are considered to be exemplary and non-restrictive in nature.

[0040] It should be noted that the terms "first", "second", "symmetrical", "array", etc. are only used to distinguish between description and position description purposes, and cannot be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the definition of "first", "symmetrical", etc. can explicitly or implicitly include one or more of these features; similarly, when the number of certain features is not limited in the form of words such as "two" or "three", it should be noted that this feature also explicitly or implicitly includes one or more feature quantities.

[0041] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0042] Glossary:

[0043] Data visualization: Data visualization (DV) is a method of encoding data into graphics or visual objects, aiming to reveal patterns, trends, and insights in the data through visual communication. Data visualization converts data into a graphical or visual format, making it easier to understand and analyze. Common types of data visualization include bar charts, line charts, pie charts, scatter plots, and heat maps. Data visualization can help people quickly identify patterns, trends, and anomalies in data.

[0044] DSL: DSL (Domain-Specific Language) is a computer programming language or specification language designed specifically for a specific problem domain, task, or solution. Unlike general-purpose programming languages ​​(such as Java, C++, Python), DSL focuses on a specific domain and provides a limited but highly optimized set of functions.

[0045] Abstract Syntax Tree: Abstract Syntax Tree (AST) is a tree representation of the syntax structure of source code. It uses a tree-like method to represent the syntax structure of a programming language, and each node represents a structure in the source code. In compiler design, the abstract syntax tree is an intermediate representation of the source code. As a bridge between the source code and the target code, it plays an important role in the compilation process.

[0046] Interpretability: In data science, interpretability refers to the degree to which a model’s output and its decision-making process can be understood and explained by humans. Highly interpretable models allow researchers, developers, and business decision makers to understand how the model made a specific prediction or decision.

[0047] Embodiment 1

[0048] like Figure 1 As shown, an embodiment of the present invention provides a method for constructing a data visualization query based on DSL and SQL abstract syntax tree, comprising the following steps:

[0049] S1. Build a human-computer interaction interface - for users to select the chart type and the data corresponding to the chart, and generate the DSL corresponding to the chart;

[0050] S2, define the semantic layer - parse the DSL into corresponding DSL syntax fragments;

[0051] S3, abstract syntax tree node conversion;

[0052] S4, constructing the abstract syntax tree node into an abstract syntax tree;

[0053] S5, query optimization - use the query optimizer to optimize the abstract syntax tree;

[0054] S6, Abstract syntax tree conversion - The semantic layer converts the abstract syntax tree into SQL that can be interpreted and executed by the database / OLAP engine through the dialect translation mechanism and logic compiler;

[0055] S7. SQL submission and execution - submit the SQL to the corresponding database / OLAP engine for execution to obtain data for building visual analysis charts.

[0056] In one embodiment, in S1, the data corresponding to the chart includes corresponding dimensions, metrics, comparison fields and calculation methods;

[0057] The dimension is the X-axis, which represents classification, and the measure is the Y-axis, which represents aggregation.

[0058] like Figure 3 As shown, any data visualization chart can be classified into three types: dimension, measurement, and comparison, where the dimension is regarded as the X-axis (classification) and the measurement is regarded as the Y-axis (aggregation).

[0059] In one embodiment, in S2, after the user submits the DSL of the diagram, the semantic layer parses the DSL into corresponding DSL fragments.

[0060] In S3, mapping rules are used to convert DSL fragments into abstract syntax tree nodes;

[0061] The mapping rules are as follows: the dimension is used as the Group By clause and the Select Item according to the conversion method declared in the DSL; the measure is used as the Aggregation clause according to the aggregation method declared in the DSL.

[0062] In S4, abstract syntax tree nodes are constructed into an abstract syntax tree according to algebraic relations.

[0063] After the user submits the DSL of the diagram, the semantic layer first parses the DSL into corresponding DSL syntax fragments, then converts the DSL fragments into abstract syntax tree nodes according to the mapping rules, and then constructs the abstract syntax tree nodes into an abstract syntax tree according to algebraic relations.

[0064] In one embodiment, in S5, query optimization includes but is not limited to projection pruning, predicate pruning, constant condition elimination, and constant conversion;

[0065] Among them, in projection pruning, the query optimizer will prune redundant column projections under semantic circumstances;

[0066] Among them, in the predicate pruning, the restrictive predicates exist user_id>=1and user_id>=2, so the restrictive predicate can be pruned to user_id>=2;

[0067] Among them, in the constant condition elimination, the restrictive predicate exists user_id = user_id, and the restrictive predicate is always established. Therefore, this restrictive constant condition can be completely cut;

[0068] Among them, in the constant conversion, the restrictive predicates exist user_id=10and order_pay_total>=user_id. Because there is a restriction of user_id=10, order_pay_total>=user_id will be replaced by order_pay_total>=10.

[0069] In one embodiment, in S6, after SQL is translated through the dialect, the general SQL abstract syntax tree is converted into a specific abstract syntax tree.

[0070] The logical compiler uses the characteristics of CTE to construct SQL. The logical compiler runs on the abstract syntax tree and converts the abstract syntax tree into interpretable and executable SQL according to the SQL syntax of different databases / OLAP engines.

[0071] The system semantic layer converts the abstract syntax tree into SQL executable by a specific database / OLAP engine through the dialect translation mechanism and the logical compiler. The principle of dialect translation is the reverse process of converting DSL fragments to abstract syntax trees. After dialect translation, the general SQL abstract syntax tree will be converted into a specific abstract syntax tree. The logical compiler runs on a specific abstract syntax tree and converts the abstract syntax book into interpretable and executable SQL according to the SQL syntax of different databases / OLAP engines.

[0072] The dialect translation mechanism and logic compiler are designed to equivalently convert the abstract syntax tree into executable SQL;

[0073] Among them, equivalence refers to the different forms of the same function in different databases / OLAP engines. For example, if you want to format the date as yyyy-MM-dd format, you need to use date_format(${TimeColumn},'%Y-%m-%d') in MySql, and formatDateTime(${TimeColumn},'%Y-%m-%d"') in Clickhouse, then these two functions are called equivalent functions;

[0074] Among them, equivalence refers to the way different functions achieve the same effect in different databases / OLAP engines. For example, to add one week to a date, you can directly use addWeeks(${TimeColumn}, 1) in Clickhouse, while you need to use addDays(${TimeColumn}, 7) in Spark. These two function forms are called equivalent functions.

[0075] In one embodiment, in S7, the obtained SQL is submitted to the corresponding database / OLAP engine by utilizing the data visualization system and executed to obtain data for constructing a visualization analysis chart.

[0076] The data visualization system submits the obtained SQL to the corresponding database / OLAP engine for execution to obtain data for building visual analysis charts.

[0077] Embodiment 2

[0078] The embodiment of the present invention provides a method for constructing data visualization query based on DSL and SQL abstract syntax tree:

[0079] 1. First, the user selects the required chart type through the data visualization analysis and design system interface, and selects the corresponding dimensions, metrics, comparison fields and calculation methods of the chart, and the system generates the DSL corresponding to the chart.

[0080] Assume that you have a table t_order_sample structure such as Figure 2 As shown in the table, there are three columns: user_id, report_date, and order_pay_total. Each row of data in the table represents an order. Assuming that we need to analyze the business flow data by day, usually, we need to draw a visual display chart such as Figure 3 As shown, any data visualization chart can be classified into three types: dimension, measurement, and comparison, where the dimension is regarded as the X-axis (classification) and the measurement is regarded as the Y-axis (aggregation).

[0081] Figure 3 The chart DSL snippet shown is shown in the following example code snippet:

[0082] Chart DSL:

[0083]

[0084] 2. After the user submits the DSL of the diagram, the semantic layer parses the DSL into corresponding DSL syntax fragments, converts the DSL fragments into abstract syntax tree nodes according to the mapping rules, and then constructs the abstract syntax tree nodes into an abstract syntax tree according to algebraic relations.

[0085] The diagram's DSL will be converted to DSL syntax. An example code snippet is shown below:

[0086]

[0087]

[0088] The basic mapping rules are as follows:

[0089] The dimension is used as the Group By clause and the Select Item according to the conversion method declared in the DSL; the measure is used as the Aggregation clause according to the aggregation method declared in the DSL;

[0090] The above DSL will get the following AST according to the DSL mapping rules:

[0091]

[0092] 3. The system uses the query optimizer to optimize the abstract syntax tree, such as projection pruning, predicate pruning, constant condition elimination, constant conversion, etc.;

[0093] Among them, projection clipping:

[0094]

[0095]

[0096] As shown in the code blocks before and after projection clipping, in projection clipping, the query optimizer will clip redundant column projections under semantic circumstances (user_id=1 in the example as a restrictive condition actually no longer has dimensional meaning in the groupby clause, and can therefore be clipped as shown after according to the optimization rules).

[0097] Among them, predicate pruning:

[0098]

[0099]

[0100]

[0101] As shown in the code blocks in the projection clipping before and after, the restrictive predicates exist user_id>=1 and user_id>=2, so the restrictive predicate can be clipped to user_id>=2.

[0102] Among them, the constant condition is eliminated:

[0103]

[0104]

[0105] Constant condition elimination As shown in the code blocks in before and after, the restrictive predicate exists user_id = user_id, and the restrictive predicate is constant. Therefore, this restrictive constant condition can be completely trimmed.

[0106] Among them, the constant conversion:

[0107]

[0108]

[0109]

[0110] Constant conversion As shown in the code blocks in before and after, the restrictive predicate exists user_id=10andorder_pay_total>=user_id. Because there is a restriction of user_id=10, order_pay_total>=user_id will be replaced by order_pay_total>=10.

[0111] 4. The system semantic layer converts the abstract syntax tree into SQL executable by a specific database / OLAP engine through the dialect translation mechanism and the logical compiler. The principle of dialect translation is the reverse process of converting DSL fragments to abstract syntax trees. After dialect translation, the general SQL abstract syntax tree will be converted into a specific abstract syntax tree. The logical compiler runs on a specific abstract syntax tree and converts the abstract syntax tree into interpretable and executable SQL according to the SQL syntax of different databases / OLAP engines.

[0112] The dialect translation mechanism and logic compiler are designed to equivalently convert the abstract syntax tree into executable SQL.

[0113] Equivalence refers to the different forms of the same function in different databases / OLAP engines. For example, suppose we want to format the date as yyyy-MM-dd format, in MySql we need to use date_format(${TimeColumn},'%Y-%m-%d'), while in Clickhouse we need to use formatDateTime(${TimeColumn},%Y-%m-%d'), then these two functions are called equivalent functions.

[0114] Equivalence refers to the way different functions achieve the same effect in different databases / OLAP engines. For example, to add one week to a date, you can directly use addWeeks(${TimeColumn}, 1) in Clickhouse, while you need to use addDays(${TimeColumn}, 7) in Spark. We call these two function forms equivalent functions.

[0115] 5. The data visualization system submits the obtained SQL to the corresponding database / OLAP engine for execution to obtain data for building visual analysis charts.

[0116] The above are only specific embodiments of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art can easily think of various changes or substitutions within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.

Claims

1. A method for building data visualization query based on DSL and SQL abstract syntax tree, It is characterized in that The following steps are involved: S1. Build a human-computer interaction interface - for users to select the chart type and the data corresponding to the chart, and generate the DSL corresponding to the chart; S2, define the semantic layer - parse the DSL into corresponding DSL syntax fragments; S3, abstract syntax tree node conversion; S4, constructing the abstract syntax tree node into an abstract syntax tree; S5, query optimization - use the query optimizer to optimize the abstract syntax tree; S6, Abstract syntax tree conversion - The semantic layer converts the abstract syntax tree into SQL that can be interpreted and executed by the database / OLAP engine through the dialect translation mechanism and logic compiler; S7. SQL submission and execution - submit the SQL to the corresponding database / OLAP engine for execution to obtain data for building visual analysis charts.

2. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 1, Features: In said S1, the data corresponding to the chart includes the corresponding dimensions, measures, comparison fields and calculation methods; The dimension is the X-axis, which represents classification, and the measure is the Y-axis, which represents aggregation.

3. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 1, Features: In S2, after the user submits the DSL of the diagram, the semantic layer parses the DSL into corresponding DSL fragments.

4. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 3, Features: In S3, the DSL fragment is converted into an abstract syntax tree node using a mapping rule; The mapping rules are as follows: the dimension is used as the Group By clause and the SelectItem according to the conversion method declared in the DSL; the measure is used as the Aggregation clause according to the aggregation method declared in the DSL.

5. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 4, Features: In S4, abstract syntax tree nodes are constructed into an abstract syntax tree according to algebraic relations.

6. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 1, Features: In S5, query optimization includes but is not limited to projection pruning, predicate pruning, constant condition elimination and constant conversion.

7. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 1, Features: In S6, after SQL is translated into the dialect, the general SQL abstract syntax tree is converted into a specific abstract syntax tree.

8. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 7, Features: In S6, the logic compiler uses the characteristics of CTE to construct SQL. The logic compiler runs on the abstract syntax tree and converts the abstract syntax tree into interpretable and executable SQL according to the SQL syntax of different databases / OLAP engines.

9. The method for constructing data visualization query based on DSL and SQL abstract syntax tree according to claim 1, Features: In S7, the obtained SQL is submitted to the corresponding database / OLAP engine by utilizing the data visualization system and executed to obtain data for constructing a visualization analysis chart.

Images