Method and System for Data Lineage and Impact Analysis Based on Apache Calcite

Abstract

The present invention provides a method and system for data lineage and impact analysis based on Apache Calcite, including: obtaining metadata information according to the collected metadata, where the metadata information includes tables and fields; integrating Apache Calcite to perform lexical and syntactic analysis on the SQL strings of the metadata information and convert them into an Abstract Syntax Tree (AST); using the Abstract Syntax Tree (AST) to obtain the relationship graph between tables and fields; and performing table-level and field-level lineage analysis and impact analysis according to the relationship graph. The present invention parses SQL of various different types of databases such as Greenplum and GaussDB, and has high scalability for different keywords, functions and other special syntax of different databases, and can support parsing the special syntax of multiple databases.

Description

Technical Field

[0001] The present invention relates to the technical field of data analysis. Specifically, it relates to a method and system for data lineage analysis and impact analysis based on the dynamic data management framework Apache Calcite. Background Art

[0002] Data lineage analysis and impact analysis are core functions of data management and data governance tools. By establishing the lineage relationship between data, on the one hand, the source and processing logic of downstream data can be traced, and on the other hand, the impact scope when upstream data changes can be quickly analyzed, so as to be able to give early warnings of change impacts and carry out supporting transformations in a timely manner. Apache Calcite is a basic framework that provides standard SQL language, various query optimizations, and connects various data sources, and can access various data and implement SQL queries. However, Apache Calcite only supports parsing conventional SQL, and will report errors or the parsing results are incomplete for the SQL parsing of different types of databases. Summary of the Invention

[0003] Aiming at the deficiencies in the prior art, the purpose of the present invention is to provide a method and system for data lineage analysis and impact analysis based on Apache Calcite.

[0004] According to a method for data lineage and impact analysis based on Apache Calcite provided by the present invention, it includes:

[0005] Step S1: Obtain metadata information according to the collected metadata, where the metadata information includes tables and fields;

[0006] Step S2: Integrate Apache Calcite, perform lexical and syntactic analysis on the SQL string of the metadata information, and convert it into an Abstract Syntax Tree (AST);

[0007] Step S3: Use the Abstract Syntax Tree (AST) to obtain the relationship graph between tables and fields;

[0008] Step S4: According to the relationship graph, perform table-level and field-level lineage analysis and impact analysis.

[0009] Preferably, step 2 includes the following steps:

[0010] Step S2.1: Adapt and transform the special syntax in databases such as Greenplum and GaussDB, and parse the SQL statement into an Abstract Syntax Tree (AST);

[0011] Step S2.2: Use a custom parser to parse the node objects of the Abstract Syntax Tree (AST), obtain the field blood relationship dependencies, and write them into a specified data table. The parsing includes:

[0012] Parse the query and association nodes in the Abstract Syntax Tree (AST) to obtain the blood relationship dependencies and dependency details between fields and tables;

[0013] Recursively parse the physical table information to which the fields in the subquery belong to obtain the blood relationship dependencies;

[0014] Among them, different parts of the SQL statement are parsed and encapsulated into different node objects by Calcite, and the corresponding blood relationship dependencies are obtained by parsing the node object information.

[0015] Preferably, the custom parser includes a Calcite SQL parser; during the process of generating the Calcite SQL parser, adjust the config.fmpp file to support the required keywords. The config.fmpp file is a Calcite template configuration file that completes the relevant configurations of FreeMarker and JavaCC; adjust the parsing rules in the Parser.jj file in the templates folder to adapt to the required database parsing, and use custom parsing functions to meet the parsing of special syntax rules. The Parser.jj file is the core parsing file required by the JavaCC parser; fmpp automatically generates the parsing file Parser.jj according to the configuration file, template file, and additional template file, and generates the SQL parser after compilation.

[0016] Preferably, based on Apache Calcite, establish the relationship between technical metadata and business metadata at the metadata level, realize the penetration of relationships at the field level and table level, and perform blood relationship analysis and impact analysis at different granularities; call Calcite to parse SQL; generate the parsed Abstract Syntax Tree SqlNode; call different parsers to parse the dependency relationships according to the type of SqlNode.

[0017] A system for data blood relationship and impact analysis based on Apache Calcite provided by the present invention includes:

[0018] Module M1: Obtain metadata information according to the collected metadata, where the metadata information includes tables and fields;

[0019] Module M2: Integrate Apache Calcite, perform lexical and syntactic analysis on the SQL string of the metadata information, and convert it into an Abstract Syntax Tree (AST);

[0020] Module M3: Obtain the relationship graph between tables and fields using the Abstract Syntax Tree (AST);

[0021] Module M4: Perform lineage analysis and impact analysis at the table level and field level based on the relationship graph.

[0022] Preferably, step 2 includes the following steps:

[0023] Module M2.1: Adapt the special syntax in databases such as Greenplum and GaussDB, and parse SQL statements into Abstract Syntax Trees (ASTs);

[0024] Module M2.2: Use a custom parser to parse the node objects of the Abstract Syntax Tree (AST), obtain the field lineage dependencies, and write them into a specified data table. The parsing includes:

[0025] Parse the query and join nodes in the Abstract Syntax Tree (AST) to obtain the lineage dependencies and dependency details between fields and tables;

[0026] For the fields in subqueries, recursively parse the physical table information to which the fields belong to obtain the lineage dependencies;

[0027] Among them, different parts of the SQL statement are parsed and encapsulated into different node objects by Calcite, and the corresponding lineage dependencies are obtained by parsing the node object information.

[0028] Preferably, the custom parser includes a Calcite SQL parser; during the generation of the Calcite SQL parser, adjust the config.fmpp file to support the required keywords; where the config.fmpp file is the Calcite template configuration file, which completes the relevant configurations of FreeMarker and JavaCC; adjust the parsing rules in the Parser.jj file in the templates folder to adapt to the required database parsing, and use custom parsing functions to satisfy the parsing of special syntax rules; where the Parser.jj file is the core parsing file required by the JavaCC parser; fmpp automatically generates the parsing file Parser.jj based on the configuration file, template file, and additional template files, and generates the SQL parser after compilation.

[0029] Preferably, based on Apache Calcite, establish the relationship between technical metadata and business metadata at the metadata level, realize the relationship penetration at the field level and table level, and perform lineage analysis and impact analysis at different granularities; call Calcite to parse SQL; generate the parsed Abstract Syntax Tree SqlNode; call different parsers to parse the dependencies according to the type of SqlNode.

[0030] A computer-readable storage medium storing a computer program, wherein when the computer program is executed by a processor, the steps of the method for Apache Calcite data lineage and impact analysis are implemented.

[0031] An electronic device according to the present invention includes a memory, a processor, and a computer program stored on the memory and executable on the processor. When the computer program is executed by the processor, the steps of the method for Apache Calcite data lineage and impact analysis are implemented.

[0032] Compared with the prior art, the present invention has the following beneficial effects:

[0033] 1. Based on Apache Calcite, the present invention establishes the relationship between technical metadata and business metadata at the metadata level, realizes the relationship penetration at the field level, job level, and table level, and then better understands and uses data through lineage analysis and impact analysis at different granularities.

[0034] 2. The present invention parses the query and join nodes in the abstract tree to obtain the lineage dependency relationship and dependency details between fields and tables; in addition, for the fields in the subquery, recursively parses the physical table information to which the fields belong to obtain the dependency relationship. Among them, Calcite parses different parts of the SQL statement and encapsulates them into different node objects, and obtains the corresponding lineage dependency relationship by parsing the node object information.

[0035] 3. The present invention parses SQLs of various different types of databases such as Greenplum and GaussDB. Different databases have different keywords, functions and other special grammars, so that the present invention can support parsing the special grammars of multiple databases. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] By reading the following detailed description of non-limiting embodiments with reference to the accompanying drawings, other features, objects and advantages of the present invention will become more apparent:

[0037] Figure 1 It is a schematic diagram of the implementation process steps for parsing.

[0038] Figure 2 It is a schematic diagram of the principle for generating a parser.

[0039] Figure 3 It is a schematic diagram of the process steps for parsing an SQL statement. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0040] The present invention will be described in detail below in conjunction with specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but do not limit the present invention in any form. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present invention, several changes and improvements can still be made. These all belong to the protection scope of the present invention.

[0041] Based on Apache Calcite, the present invention establishes the relationship between technical metadata and business metadata at the metadata level, realizes the relationship penetration at the field level, job level, and table level, and then better understands and uses data through lineage analysis and impact analysis at different granularities.

[0042] The present invention obtains the lineage dependency relationship and dependency details between fields and tables by parsing query and association nodes in the abstract tree; in addition, for the fields in subqueries, recursively parses the physical table information to which the fields belong, so as to obtain the dependency relationship. Among them, Calcite parses and encapsulates different parts of the SQL statement into different node objects, and obtains the corresponding lineage dependency relationship by parsing the node object information.

[0043] As a lineage analysis tool, the present invention has multiple advantages. By analyzing DDL and DML statements in SQL scripts, it can quickly and accurately identify the direct mapping relationships between fields (including dependency relationships and conversion operations between fields), associations between table fields, filtering conditions and other dependency relationships, making data processing more convenient and efficient. The present invention, as a lineage analysis tool, includes integrating the Apache Calcite SQL parsing framework to parse SQL statements into an abstract syntax tree and a custom parser for lineage relationship parsing.

[0044] A method based on Apache Calcite data lineage and impact analysis provided by the present invention includes:

[0045] Step 1: Collect metadata, and obtain metadata information such as tables and fields according to the metadata;

[0046] Step 2: Integrate Apache Calcite, perform lexical and syntactic analysis on the SQL strings of metadata information such as tables and fields, and convert them into an AST (Abstract Syntax Tree);

[0047] Step 3: Use the AST to obtain the relationship graph between tables and fields;

[0048] Step 4: Complete table-level and field-level lineage analysis and impact analysis through the generated relationship graph;

[0049] Step 5: Complete the lineage analysis and impact analysis at the table level, job level, and field level through the generated relationship graph.

[0050] The said Step 2 includes the following steps:

[0051] Step 2.1: Adapt and transform some specific grammars in databases such as Greenplum and GaussDB, and parse the SQL statements therein into abstract syntax trees;

[0052] Step 2.2: Use a custom parser to parse the abstract syntax tree node objects, obtain the field dependency relationship data, generate the parsing result and write it into the specified data source. The implementation process is as Figure 1 shown.

[0053] The said custom parser includes a Calcite SQL parser. During the process of generating the Calcite SQL parser, the relevant files required are all placed in the codegen folder, including the custom parsing files such as guassParserImpls.ftl, gpParserImpls.ftl, parserImpls.ftl, tcl.ftl in the includes folder; the config.fmpp file and the Parser.jj file in the templates folder. Files such as GuassParserImpls.ftl, gpParserImpls.ftl, parserImpls.ftl, tcl.ftl are additional syntax template files, and the Parser.jj file is the core parsing file required by the JavaCC parser (such as the main modification file when adding functions). The config.fmpp file is a CALCITE template configuration file to complete the relevant configurations of FreeMarker and JavaCC; fmpp automatically generates the ultimate parsing file Parser.jj in the fmpp / javacc folder according to the configuration file, template file, and additional template files, and generates the SQL parser javacc / * after compilation. The specific process of generating the parser is as Figure 2 shown. The present invention parses SQLs of various different types of databases such as GP and Gauss. Different databases have different keywords, functions and other special grammars, enabling the present invention to support the parsing of special grammars of multiple databases. The present invention makes corresponding adjustments to the source code and configuration of the Apache Calcite framework. For example, adjust config.fmpp to support more keywords, adjust and optimize the parsing rules in Parser.jj to adapt to the parsing of multiple databases, and use custom parsing functions to meet the parsing of special grammar rules.

[0054] The core process of using the lineage analysis tool to parse SQL statements is asFigure 3 As shown in Figure 3 , it includes: calling Calcite to parse SQL; generating the parsed abstract syntax tree SqlNode; and calling different parsers to parse the dependency relationships according to the type of SqlNode (create, update, etc.).

[0055] The present invention also provides a system for Apache Calcite data lineage and impact analysis. The system for Apache Calcite data lineage and impact analysis can be implemented by executing the process steps of the method for Apache Calcite data lineage and impact analysis. That is, those skilled in the art can understand the method for Apache Calcite data lineage and impact analysis as a preferred implementation manner of the system for Apache Calcite data lineage and impact analysis. Specifically, according to a system for Apache Calcite data lineage and impact analysis provided by the present invention, it includes:

[0056] Module M1: Obtain metadata information according to the collected metadata, where the metadata information includes tables and fields;

[0057] Module M2: Integrate Apache Calcite, perform lexical and syntactic analysis on the SQL string of the metadata information, and convert it into an abstract syntax tree AST;

[0058] Module M3: Use the abstract syntax tree AST to obtain the relationship graph between tables and fields;

[0059] Module M4: Perform table-level and field-level lineage analysis and impact analysis according to the relationship graph.

[0060] Step 2 includes the following steps:

[0061] Module M2.1: Adapt and transform the special syntax in databases such as Greenplum and GaussDB, and parse the SQL statement into an abstract syntax tree AST;

[0062] Module M2.2: Use a custom parser to parse the node objects of the abstract syntax tree AST, obtain the field lineage dependencies, and write them into a specified data table. The parsing includes: parsing the query and join nodes in the abstract syntax tree AST to obtain the lineage dependencies and dependency details between fields and tables; recursively parsing the physical table information to which the fields in the subquery belong to obtain the lineage dependencies; where Calcite parses and encapsulates different parts of the SQL statement into different node objects, and the corresponding lineage dependencies are obtained by parsing the node object information.

[0063] The custom parser includes a Calcite SQL parser; during the process of generating the Calcite SQL parser, the config.fmpp file is adjusted to support the required keywords; among them, the config.fmpp file is a Calcite template configuration file that completes the relevant configurations of FreeMarker and JavaCC; the parsing rules in the Parser.jj file in the templates folder are adjusted to adapt to the required database parsing, and custom parsing functions are used to satisfy the parsing of special syntax rules; among them, the Parser.jj file is the core parsing file required by the JavaCC parser; fmpp automatically generates the parsing file Parser.jj according to the configuration file, template file and additional template files, and generates the SQL parser after compilation.

[0064] Based on Apache Calcite, the relationship between technical metadata and business metadata is established at the metadata level, realizing the relationship penetration at the field level and table level, and performing lineage analysis and impact analysis at different granularities; calling Calcite to parse SQL; generating the parsed abstract syntax tree SqlNode; calling different parsers according to the type of SqlNode to parse the dependency relationship.

[0065] According to a computer-readable storage medium storing a computer program provided by the present invention, when the computer program is executed by a processor, the steps of the method for Apache Calcite data lineage and impact analysis are implemented.

[0066] According to an electronic device provided by the present invention, including a memory, a processor, and a computer program stored on the memory and executable on the processor, when the computer program is executed by the processor, the steps of the method for Apache Calcite data lineage and impact analysis are implemented.

[0067] Those skilled in the art know that in addition to implementing the systems, devices and their respective modules provided by the present invention in the form of pure computer-readable program code, the method steps can be logically programmed to make the systems, devices and their respective modules provided by the present invention in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers, and embedded microcontrollers to implement the same program. Therefore, the systems, devices and their respective modules provided by the present invention can be regarded as a kind of hardware component, and the modules included therein for implementing various programs can also be regarded as the structures within the hardware component; the modules for implementing various functions can also be regarded as both software programs for implementing the method and the structures within the hardware component.

[0068] The specific embodiments of the present invention have been described above. It should be understood that the present invention is not limited to the above specific embodiments, and those skilled in the art can make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. Without conflict, the embodiments of the present application and the features in the embodiments can be combined with each other arbitrarily.

Claims

1. A method based on Apache Calcite data lineage and impact analysis, characterized in that Including: Step S1: Obtain metadata information according to the collected metadata, where the metadata information includes tables and fields; Step S2: Integrate Apache Calcite, perform lexical and syntactic analysis on the SQL string of the metadata information, and convert it into an Abstract Syntax Tree (AST); Step S3: Use the Abstract Syntax Tree (AST) to obtain the relationship graph between tables and fields; Step S4: Perform lineage analysis and impact analysis at the table level and field level according to the relationship graph; Step 2 includes the following steps: Step S2.1: Adapt and transform the special syntax in Greenplum and GaussDB databases, and parse the SQL statement into an Abstract Syntax Tree (AST); Step S2.2: Use a custom parser to parse the node objects of the Abstract Syntax Tree (AST), obtain the field lineage dependency relationship, and write it into a specified data table. The parsing includes: Parse the query and join nodes in the Abstract Syntax Tree (AST) to obtain the lineage dependency relationship and dependency details between fields and tables; Recursively parse the physical table information to which the fields in the subquery belong to obtain the lineage dependency relationship; Among them, different parts of the SQL statement are parsed and encapsulated into different node objects by Apache Calcite, and the corresponding lineage dependency relationship is obtained by parsing the node object information; The custom parser includes a Calcite SQL parser; during the process of generating the Calcite SQL parser, adjust the config.fmpp file to support the required keywords. The config.fmpp file is a Calcite template configuration file that completes the relevant configurations of FreeMarker and JavaCC; adjust the parsing rules in the Parser.jj file in the templates folder to adapt to the required database parsing, and use custom parsing functions to meet the special syntax rule parsing. The Parser.jj file is the core parsing file required by the JavaCC parser; fmpp automatically generates the parsing file Parser.jj according to the configuration file, template file, and additional template file, and generates a SQL parser after compilation.

2. The method for Apache Calcite data lineage and impact analysis according to claim 1, characterized in that, Based on Apache Calcite, establish the relationship between technical metadata and business metadata at the metadata level, achieve relationship penetration at the field level and table level, and perform lineage analysis and impact analysis at different granularities; Call Calcite to parse SQL; Generate the parsed Abstract Syntax Tree SqlNode; call different parsers to parse the dependency relationship according to the type of SqlNode.

3. A system based on Apache Calcite data lineage and impact analysis, characterized in that, Including: Module M1: Obtain metadata information according to the collected metadata, where the metadata information includes tables and fields; Module M2: Integrate Apache Calcite, perform lexical and syntactic analysis on the SQL string of the metadata information, and convert it into an Abstract Syntax Tree (AST); Module M3: Use the Abstract Syntax Tree (AST) to obtain the relationship graph between tables and fields; Module M4: Perform lineage analysis and impact analysis at the table level and field level according to the relationship graph; Step 2 includes the following steps: Module M2.1: Adapt and transform the special syntax in Greenplum and GaussDB databases, and parse the SQL statement into an Abstract Syntax Tree (AST); Module M2.2: Use a custom parser to parse the node objects of the Abstract Syntax Tree (AST), obtain the field lineage dependencies, and write them into a specified data table. The parsing includes: Parse the query and join nodes in the Abstract Syntax Tree (AST) to obtain the lineage dependencies and dependency details between fields and tables; Recursively parse the physical table information to which the fields in the subquery belong to obtain the lineage dependencies; Among them, different parts of the SQL statement are parsed and encapsulated into different node objects by Calcite, and the corresponding lineage dependencies are obtained by parsing the node object information; The custom parser includes a Calcite SQL parser; during the generation of the Calcite SQL parser, adjust the config.fmpp file to support the required keywords. The config.fmpp file is a Calcite template configuration file that completes the relevant configurations of FreeMarker and JavaCC; adjust the parsing rules in the Parser.jj file in the templates folder to adapt to the required database parsing, and use custom parsing functions to satisfy the parsing of special syntax rules. The Parser.jj file is the core parsing file required by the JavaCC parser; fmpp automatically generates the parsing file Parser.jj according to the configuration file, template file, and additional template file, and generates the SQL parser after compilation.

4. The system based on Apache Calcite data lineage and impact analysis according to claim 3, wherein, Based on Apache Calcite, establish the relationship between technical metadata and business metadata at the metadata level, realize the relationship penetration at the field level and table level, and perform lineage analysis and impact analysis at different granularities; Call Calcite to parse SQL; Generate the parsed Abstract Syntax Tree SqlNode; call different parsers to parse the dependencies according to the type of SqlNode.

5. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method for Apache Calcite-based data lineage and impact analysis described in claim 1 or 2.

6. An electronic device, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, When the computer program is executed by a processor, it implements the steps of the method for Apache Calcite-based data lineage and impact analysis described in claim 1 or 2.

Images