SQL-based data lineage analysis method and apparatus
By using an SQL-based data lineage analysis method, an abstract syntax tree is generated through parsing, and table-level and field-level lineage relationships are extracted. This solves the problems of intrusive data collection and insufficient customization in existing technologies, and achieves efficient and accurate data lineage analysis, which is suitable for multi-data center and database sharding scenarios.
Patent Information
- Application Number
- CN202510637782.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-05-16
- Publication Date
- 2026-11-17
AI Technical Summary
Existing data lineage collection solutions suffer from intrusive collection methods that increase development and maintenance costs, insufficient customization capabilities, low efficiency in distributed data processing, and difficulty in meeting the needs of complex scenarios.
It adopts a SQL-based data lineage analysis method, which collects lineage-related information from multiple data sources, parses and generates an abstract syntax tree, extracts table-level and field-level lineage relationships, and stores them in a relational database. It supports string replacement for database sharding and table partitioning, as well as flexible handling of data center locations.
It avoids intrusive modifications to the original code, reduces system maintenance costs and risks, improves the accuracy and applicability of data lineage analysis, and can handle multi-data center and database sharding scenarios.
Smart Images

Figure CN123120052A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data analysis technology, and in particular to a data lineage analysis method and apparatus based on SQL. Background Technology
[0002] Data lineage collection and analysis are crucial components of data governance and data security. They clearly demonstrate the source, flow, and processing of data, helping users understand the relationships between data points, thereby improving data quality and reducing data risks. However, with the explosive growth of data volume and the diversification of data sources, traditional data lineage collection methods are no longer sufficient to meet practical needs.
[0003] Existing kinship data collection methods have the following main shortcomings:
[0004] Intrusive data collection methods: Many solutions use hooks to collect data lineage information at runtime. This method requires modification of the original code, increases development and maintenance costs, and may affect system performance and stability. Column-level support is also incomplete.
[0005] High cost and insufficient customization capabilities: Although some open-source metadata tools (such as Atlas or DataHub) can provide rich data source support and lineage display, they usually need to be purchased, which is costly and difficult to meet the customization needs of specific business scenarios, and cannot fully adapt to the actual situation.
[0006] Insufficient distributed data processing capabilities: For distributed data lineage collection solutions, the approach of deploying agents at multiple points and then aggregating the data for unified lineage analysis is often adopted. This approach is inefficient and difficult to meet the requirements when dealing with multi-center distributed data, multi-type multi-system tasks, and heterogeneous data sources.
[0007] Currently, there is a lack of effective data lineage collection solutions for handling the aforementioned complex scenarios. Therefore, there is an urgent need for a method that can overcome these shortcomings and efficiently and accurately collect data lineage relationships in complex data environments. Summary of the Invention
[0008] In view of this, the present invention provides a data lineage analysis method and apparatus based on SQL to solve at least one of the aforementioned problems.
[0009] To achieve the above objectives, the present invention adopts the following solution:
[0010] According to a first aspect of the present invention, embodiments of the present invention provide a data lineage analysis method based on SQL, the method comprising: collecting lineage-related information from multiple different data sources; parsing the SQL statements in the lineage-related information to generate an abstract syntax tree, and extracting table-level lineage relationships, field-level lineage relationships, and corresponding task information by analyzing the abstract syntax tree; storing the extracted table-level lineage relationships, field-level lineage relationships, and task information in the form of tables in a relational database, wherein the table-level lineage relationships and field-level lineage relationships include a current table and a sub-lineage relationship table.
[0011] As an embodiment of the present invention, before collecting bloodline-related information from multiple different data sources, the method further includes: identifying distinguishing fields for database sharding and table partitioning in multiple different data sources; and performing string replacement on the distinguishing fields according to preset rules so that the collected bloodline-related information belongs to the same logical table.
[0012] As an embodiment of the present invention, before collecting lineage-related information from multiple different data sources, the method further includes: identifying the data centers to which the multiple different data sources belong; determining whether it is necessary to distinguish the location of the data centers according to preset rules: if it is not necessary to distinguish the location of the data centers, then the data from the same logical table from different data centers is logically normalized to unify its representation, so that the collected lineage-related information only displays the logical derivation relationship between the data, without considering its physical storage location; if it is necessary to distinguish the location of the data centers, then the location information of the data center to which each data source belongs is retained in the lineage-related information, so that the collected lineage-related information can display the data derivation relationship across data centers.
[0013] As an embodiment of the present invention, the above method parses the SQL statements in the lineage-related information to generate an abstract syntax tree, and extracts table-level lineage relationships and field-level lineage relationships by analyzing the abstract syntax tree, including: parsing the Hive SQL statements in the lineage-related information and generating an abstract syntax tree; optimizing the abstract syntax tree to remove useless tokens; traversing the abstract syntax tree to extract the upstream table name, downstream table name, and complete field information; constructing table-level lineage relationships based on the upstream table name and downstream table name, and constructing field-level lineage relationships based on the complete field information.
[0014] As an embodiment of the present invention, the above method of traversing the abstract syntax tree to extract complete field information includes: when traversing the abstract syntax tree, storing the field information in the field graph with the table name plus the field name as the key and the field source tree structure as the value according to the delimiters and semantics in the abstract syntax tree; the step of constructing field-level lineage relationships based on the complete field information includes: constructing field-level lineage relationships according to the relationships between fields in the field graph.
[0015] As an embodiment of the present invention, the above method further includes: validating the collected bloodline-related information; and adding validity tags to the table-level bloodline relationships and field-level bloodline relationships stored in the relational database according to the validity verification results.
[0016] According to a second aspect of the present invention, embodiments of the present invention provide an SQL-based data lineage analysis apparatus, the apparatus comprising: an information acquisition unit for acquiring lineage-related information from multiple different data sources; a relationship extraction unit for parsing the SQL statements in the lineage-related information to generate an abstract syntax tree, and extracting table-level lineage relationships, field-level lineage relationships, and corresponding task information by analyzing the abstract syntax tree; and a storage unit for storing the extracted table-level lineage relationships, field-level lineage relationships, and task information in the form of tables in a relational database, wherein the table-level lineage relationships and field-level lineage relationships include a current table and a sub-lineage relationship table.
[0017] As an embodiment of the present invention, the above-mentioned device further includes: a distinguishing field identification unit, used to identify distinguishing fields in multiple different data sources that are sharded and partitioned; and a field replacement unit, used to replace the distinguishing fields with strings according to preset rules, so that the collected bloodline-related information belongs to the same logical table.
[0018] As an embodiment of the present invention, the above-mentioned device further includes: a data center identification unit, used to identify the data centers to which multiple different data sources belong; a differentiation judgment unit, used to determine whether it is necessary to differentiate the data center location according to preset rules; a normalization processing unit, used to perform logical normalization processing on the data of the same logical table from different data centers when it is not necessary to differentiate the data center location, to unify its representation form, so that the collected lineage-related information only displays the logical derivation relationship between the data, without considering its physical storage location; and a location information retention unit, used to retain the location information of the data center to which each data source belongs in the lineage-related information when it is necessary to differentiate the data center location, so that the collected lineage-related information can display the data derivation relationship across data centers.
[0019] As an embodiment of the present invention, the above-mentioned relationship extraction unit includes: a parsing module, used to parse the Hive SQL statements in the lineage-related information and generate an abstract syntax tree; an optimization module, used to optimize the abstract syntax tree to remove useless tokens; a traversal module, used to traverse the abstract syntax tree to extract the upstream table name, the downstream table name and complete field information; and a construction module, used to construct table-level lineage relationships based on the upstream table name and the downstream table name, and construct field-level lineage relationships based on the complete field information.
[0020] As an embodiment of the present invention, the traversal module traversing the abstract syntax tree to extract complete field information includes: when traversing the abstract syntax tree, storing the field information in the field graph with the table name plus the field name as the key and the field source tree structure as the value according to the delimiters and semantics in the abstract syntax tree; the construction module constructing field-level lineage relationships based on the complete field information includes: constructing field-level lineage relationships according to the relationships between fields in the field graph.
[0021] As an embodiment of the present invention, the above-mentioned device further includes: a verification unit for validating the collected bloodline-related information; and a tag adding unit for adding validity tags to the table-level bloodline relationships and field-level bloodline relationships stored in the relational database according to the validity verification results.
[0022] According to a third aspect of the present invention, an electronic device is also provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the above-described method.
[0023] According to a fourth aspect of the present invention, embodiments of the present invention also provide a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of the above-described method.
[0024] According to a fifth aspect of the present invention, embodiments of the present invention also provide a computer program product, including a computer program / instructions, which, when executed by a processor, implement the steps of the above-described method.
[0025] The SQL-based data lineage analysis method and apparatus proposed in this invention do not rely on hook plugins, avoiding intrusive modifications to existing code, reducing system maintenance costs and risks, and improving system stability. Furthermore, by performing string replacement normalization processing on the distinguishing fields of sharded databases and tables, this invention ensures that the data lineage information collected from sharded databases and tables is from the same logical table, improving the accuracy of data lineage analysis. Moreover, this invention can flexibly handle data from multiple data centers, selecting logical normalization processing or retaining physical location information according to user needs. This simplifies the display of data lineage relationships and meets the needs of scenarios sensitive to data location, enhancing the applicability of data lineage analysis. Attached Figure Description
[0026] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:
[0027] Figure 1 This is a flowchart illustrating a data lineage analysis method based on SQL provided in an embodiment of this application;
[0028] Figure 2 This is a flowchart illustrating the extraction of table-level lineage relationships and field-level lineage relationships provided in an embodiment of this application;
[0029] Figure 3 This is a schematic diagram of the phenotypic bloodline provided in the embodiments of this application;
[0030] Figure 4 This is a schematic diagram of field-level lineage relationships provided in an embodiment of this application;
[0031] Figure 5 This is a flowchart illustrating a data lineage analysis method based on SQL, provided in another embodiment of this application;
[0032] Figure 6 This is a flowchart illustrating the identification and differentiation fields provided in an embodiment of this application;
[0033] Figure 7 This is a flowchart illustrating a data lineage analysis method based on SQL, provided in another embodiment of this application;
[0034] Figure 8 This is a schematic diagram of the structure of a SQL-based data lineage analysis device provided in an embodiment of this application;
[0035] Figure 9This is a schematic diagram of the structure of a data lineage analysis device based on SQL provided in another embodiment of this application;
[0036] Figure 10 This is a schematic diagram of the structure of a data lineage analysis device based on SQL provided in another embodiment of this application;
[0037] Figure 11 This is a schematic diagram of the structure of the relationship extraction unit provided in an embodiment of this application;
[0038] Figure 12 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation
[0039] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. Here, the illustrative embodiments of the present invention and their descriptions are used to explain the present invention, but are not intended to limit the present invention.
[0040] The information collected in the technical solution of this application is information and data authorized by the user or fully authorized by all parties. The collection, storage, use, processing, transmission, provision, disclosure and application of the relevant data all comply with the relevant laws, regulations and standards of the relevant countries and regions, necessary confidentiality measures have been taken, and they do not violate public order and good morals. Corresponding operation portals are provided for users to choose to authorize or refuse.
[0041] The acquisition, transmission, storage, use, and processing of data in this application all comply with the relevant provisions of national laws and regulations.
[0042] The relevant technical terms used in this application are explained as follows:
[0043] The term "data lineage" refers to the complete record of the source, destination, and processing of data. It's like a family tree, tracing the "bloodline" of the data and recording its entire lifecycle from generation to final use.
[0044] The term "heterogeneous data source" refers to data that can be stored in row-oriented or column-oriented formats, in a data warehouse or data lake, and in an SQL database or a NoSQL database. Data from multiple different sources constitutes heterogeneous data.
[0045] like Figure 1 The diagram shown is a flowchart of a data lineage analysis method based on SQL provided in an embodiment of this application. The method includes the following steps:
[0046] Step S101: Collect bloodline-related information from multiple different data sources.
[0047] Because the data sources are diverse (relational databases, data warehouses, data lakes, NoSQL databases, files, etc.) and distributed across multiple different data sources, such as distributed heterogeneous data sources, this application needs to handle data from different types of databases (SQL and NoSQL) and different storage formats (row-based and column-based storage) during the data collection process. This application can use configuration files to adopt different data collection methods for different systems and tasks.
[0048] Step S102: Parse the SQL statements in the lineage-related information to generate an abstract syntax tree, and extract table-level lineage relationships, field-level lineage relationships, and corresponding task information by analyzing the abstract syntax tree.
[0049] In this step, the collected data lineage information is parsed and analyzed to extract table-level and field-level lineage relationships, as well as related task information.
[0050] Table-level lineage refers to the dependency relationship between different database tables. For example, if data from table A is used to generate table B, then there is a table-level lineage relationship between table A and table B. This relationship describes the flow of data between different tables, focusing on the associations between tables.
[0051] Field-level lineage refers to the dependency relationship between fields in different database tables. It is more granular than table-level lineage and focuses on the flow of data at the field level. For example, if field 'a' in table A is used to calculate field 'b' in table B, then there is a field-level lineage relationship between field 'a' and field 'b'.
[0052] Task information refers to task details related to data lineage. This information records the various processing steps the data undergoes throughout its lifecycle, such as: SQL statements used to generate tables or fields; shell scripts, Python scripts, and other script commands used for data processing; function or method calls used for data processing in the program; task start and end times; task success or failure status; and log information during task execution. Combining task information with table-level and field-level lineage allows for complete tracing of the data processing process, facilitating data source tracing, problem investigation, and data governance. Task information can be stored as edge attributes of lineage relationships; for example, on the edge A->B, detailed information about task T is stored to verify lineage.
[0053] In summary, table-level lineage focuses on the relationships between tables, field-level lineage focuses on the relationships between fields, and task information records all the details of the data processing process. These three together constitute complete data lineage information.
[0054] Preferred, such as Figure 2 As shown, this step involves parsing the SQL statements in the lineage-related information to generate an abstract syntax tree, and further extracting table-level and field-level lineage relationships by analyzing the abstract syntax tree. This can be further divided into the following sub-steps:
[0055] Step S1021: Parse the Hive SQL statements in the lineage-related information and generate an abstract syntax tree.
[0056] The core of this step is to transform Hive SQL statements into a tree-like structure: an Abstract Syntax Tree (AST). An AST is a way to represent the syntax structure of code in a tree structure, where each node represents a syntactic unit, such as keywords like SELECT, FROM, JOIN, and WHERE, as well as table names, field names, and expressions. Generating an AST requires a syntax parser, which breaks down the SQL statement into syntactic units according to Hive SQL's syntax rules and constructs them into a tree structure. This process is similar to the front-end stage of a compiler, converting code into a form that is easy for a computer to understand.
[0057] Step S1022: Optimize the abstract syntax tree to remove useless tokens.
[0058] The AST generated in step S1021 may contain information that is useless for ancestry extraction, such as comments, redundant spaces, and newlines. The goal of this step is to remove this useless information, simplify the AST structure, and improve the efficiency of subsequent processing. "Token" refers to a syntactic unit; useless tokens are those syntactic units that do not contribute to ancestry extraction. Optimization methods may include, but are not limited to:
[0059] Remove comments: Delete the comment sections from SQL statements.
[0060] Remove spaces and newlines: Remove redundant spaces and newlines to make the AST structure more compact.
[0061] Remove irrelevant keywords and expressions: Delete keywords and expressions that are not related to the relationships between tables and fields.
[0062] Step S1023: Traverse the abstract syntax tree to extract the upstream table name, downstream table name, and complete field information.
[0063] This step involves extracting key information based on the optimized AST (Abstract Syntax Tree). For example, by performing a depth-first or breadth-first AST, all table and field names in the SQL statement can be identified. Specifically: upstream table names refer to the table names that serve as the data source in the SQL statement, such as the table names in the FROM clause. Downstream table names refer to the table names that serve as the data target in the SQL statement, such as the table names in the INTO clause or the target table names in the INSERT statement. Complete field information refers to all field names involved in the SQL statement, including their source table names and how they are used in the SQL statement (e.g., whether they are involved in calculations, whether they have been renamed, etc.). This information is used to construct field-level lineage relationships.
[0064] Step S1024: Construct table-level lineage relationships based on the upstream and downstream table names, and construct field-level lineage relationships based on the complete field information.
[0065] Table-level lineage establishes dependencies between tables based on extracted upstream and downstream table names. This is typically represented as a graph, where nodes represent tables and edges represent data flow. See [link to relevant documentation] for details. Figure 3 The diagram illustrates this. Field-level lineage relationships are established based on the extracted complete field information, defining the dependencies between fields. This can also be represented as a graph, with nodes representing fields and edges representing data flow. This part requires considering the calculation logic of the fields. For example, if a field is the result of calculations from multiple fields, then it's necessary to establish the relationship between that field and all the fields involved in the calculation. See [link to relevant documentation] for details. Figure 4 A schematic diagram.
[0066] Preferably, step S1023, traversing the abstract syntax tree to extract complete field information, includes: when traversing the abstract syntax tree, storing the field information in the field graph with the table name plus the field name as the key and the field source tree structure as the value, according to the delimiters and semantics in the abstract syntax tree. Correspondingly, step S1024, constructing field-level lineage relationships based on the complete field information, includes: constructing field-level lineage relationships according to the relationships between fields in the field graph.
[0067] Step S103: Store the extracted table-level lineage relationships, field-level lineage relationships, and task information in a relational database in the form of tables. The table-level lineage relationships and field-level lineage relationships include the current table and the sub-lineage relationship table.
[0068] This step involves storing the information extracted in step S102 into a relational database (e.g., TiDB). The storage structure is designed as follows: table-level lineage relationships and field-level lineage relationships are stored in tables and linked via foreign keys. Each lineage relationship contains only the current table and its child lineage relationship tables, forming a tree structure. Furthermore, logical and physical relationships in this application can be stored separately, allowing users to choose to view either logical or physical relationships according to their needs. Simultaneously, task information is stored as an attribute of the lineage relationship and associated with both table-level and field-level lineage relationships.
[0069] Preferably, the method of this embodiment may further include: validating the collected bloodline information; and adding validity tags to the bloodline relationships stored in the relational database based on the validity verification results.
[0070] Validating the collected lineage information ensures its accuracy and completeness. Validation can be performed from multiple perspectives, such as completeness verification, consistency verification, accuracy verification, timeliness verification, and rule validation.
[0071] After validity validation, the validation results need to be stored in a relational database as tags. This facilitates subsequent querying and analysis and improves the transparency and traceability of data lineage information. Validity tags can take various forms: adding validity tags to the lineage table in the relational database, for example, by adding a `validity_status` field to store the validity tags. This allows users to easily query and filter valid data lineage information, improving the efficiency and accuracy of data analysis. At the same time, validity tags also provide important reference information for subsequent data governance and maintenance.
[0072] By adding these two steps, this application significantly improves the reliability and credibility of data lineage information, providing a more reliable foundation for subsequent data analysis and governance.
[0073] As described above, steps S101-S103 together complete a comprehensive data lineage collection and storage process. This process can handle multiple data sources, various task types, and complex data distributions, and ultimately stores the data lineage information in a structured form in a relational database, facilitating subsequent querying and analysis. This invention can be deployed independently via API access, thus avoiding invasive modifications to existing code, reducing system maintenance costs and risks, and improving system stability.
[0074] like Figure 5The diagram shown is a flowchart of a SQL-based data lineage analysis method according to another embodiment of this application. The method includes the following steps:
[0075] Step S501: Identify the distinguishing fields for database sharding and table partitioning in multiple different data sources.
[0076] The key to this step is accurately identifying which fields are used to differentiate between database shards and table partitions. Different database systems and sharding strategies may use different fields as the differentiating factors. Therefore, a universal method is needed to identify these fields. Generally, differentiating fields can be identified based on metadata information or SQL statement analysis. For example, if the database system provides metadata information, such as configuration information for sharding rules, the differentiating field information can be extracted directly from the metadata. Alternatively, the SQL code that creates the sharding statements can be analyzed to identify the fields used for sharding.
[0077] If metadata or SQL statements cannot be directly obtained, statistical analysis can be performed on the data to identify which field values are related to the data storage location. This requires certain algorithms and rules to determine, such as... Figure 6 As shown, this embodiment provides the following method to solve the identification of distinguishing fields in this case:
[0078] Step S601: Extract a portion of sample data from each data source (database or table). The sample size needs to be adjusted based on the data volume and computing resources to ensure that the sample can represent the overall data distribution. Data cleaning and format conversion can be performed on the sample data. Data cleaning handles missing and outlier values, while format conversion transforms the sample data into a unified format for easier subsequent processing.
[0079] Step S602: Select all fields as candidate fields from the cleaned sample dataset, and mark the corresponding storage location (e.g., database name, table name) for each record for subsequent analysis.
[0080] Step S603: Encode the values of the candidate fields using one-hot encoding to convert the categorical fields into numerical data.
[0081] Step S604: Use a clustering algorithm to cluster the encoded data, aiming to group similar records into the same cluster. Analyze the clustering results and observe the distribution of storage locations of records in each cluster. If the value of a certain field shows a significant difference in distribution across different clusters, then that field may be a discriminant field.
[0082] Step S605: Use the Apriori algorithm to mine frequent itemsets, find the association between field values and storage locations, and generate association rules from the frequent itemsets.
[0083] Step S606: Based on the results of cluster analysis and association rule mining, identify the distinguishing fields.
[0084] The above steps effectively identify fields associated with data storage locations, especially when metadata or SQL statements cannot be directly obtained. This method combines cluster analysis and association rule mining, improving the accuracy and efficiency of data lineage information collection during automated identification.
[0085] Step S502: Perform string replacement on the distinguishing field according to preset rules so that the collected bloodline-related information belongs to the same logical table.
[0086] After identifying the distinguishing fields, these fields need to be replaced with strings to ensure that subsequent data collection refers to data lineage information from the same logical table. The string replacement rules need to be pre-defined and adjusted according to actual conditions. In this embodiment, the replacement rules can include uniform replacement, pattern replacement, or custom replacement. Uniform replacement replaces all distinguishing field values with a uniform value, such as "ALL," which is suitable when all data in all sharded databases and tables belong to the same logical table. Pattern replacement replaces values based on the pattern of the distinguishing field values, for example, replacing "region=north" with "region=ALL," which is suitable when the distinguishing field values have a certain pattern. Custom replacement allows users to define their own replacement rules, which is suitable for special cases or situations requiring more flexible replacement rules.
[0087] The purpose of string replacement is to treat data from different physical tables as data from the same logical table, thereby avoiding duplication or omission when collecting data lineage information. For example, if a logical table is divided into multiple physical tables, each storing a portion of data and distinguished by a field called "region", the value of the "region" field can be replaced with a uniform value, such as "ALL". This way, when collecting data lineage information, the data from these physical tables can be treated as data from the same logical table.
[0088] Step S503: Parse the SQL statements in the lineage-related information to generate an abstract syntax tree, and extract table-level lineage relationships, field-level lineage relationships, and corresponding task information by analyzing the abstract syntax tree.
[0089] Step S504: Store the extracted table-level lineage relationships, field-level lineage relationships, and task information in a relational database in the form of tables. The table-level lineage relationships and field-level lineage relationships include the current table and the sub-lineage relationship table.
[0090] The above steps S503 and S504 are the same as Figure 1 The corresponding steps in the embodiments are similar and will not be repeated here.
[0091] As described above, the SQL-based data lineage analysis method proposed in this invention does not rely on hook plugins, avoids intrusive modifications to existing code, reduces system maintenance costs and risks, and improves system stability. Furthermore, by performing string replacement normalization processing on the distinguishing fields of sharded databases and tables, this invention ensures that the data lineage information collected from the sharded databases and tables is from the same logical table, thus improving the accuracy of data lineage analysis.
[0092] like Figure 7 The diagram shown is a flowchart of a SQL-based data lineage analysis method according to another embodiment of this application. The method includes the following steps:
[0093] Step S701: Identify the data centers to which multiple different data sources belong.
[0094] The first step is to determine the physical location of each data source, i.e., which data center it belongs to. This can be achieved through configuration management, IP address or domain name or database metadata.
[0095] Step S702: Determine whether it is necessary to distinguish the location of the data center according to the preset rules. If it is not necessary to distinguish the location of the data center, proceed to step S703; otherwise, proceed to step S704.
[0096] This step introduces a preset rule to determine whether data center location information should be retained in the data lineage information. The rule can be configured according to different needs; for example, if the analysis only focuses on data relationships within a single data center, then data center location is not required. If the analysis needs to consider data relationships across data centers, then data center location needs to be considered. If some data is sensitive to data center location, then data center location needs to be considered.
[0097] Step S703: Perform logical normalization on the data from the same logical table from different data centers to unify its representation, so that the collected lineage-related information only shows the logical derivation relationship between the data, without considering its physical storage location.
[0098] If data center location is not a factor, logical normalization is required for data from the same logical table across different data centers. This means unifying the representation of data from the same logical table across different data centers, hiding the differences in their physical storage locations. For example, ensuring that field names and data types remain consistent across logical tables in different data centers. If field values differ across logical tables in different data centers, conversion is necessary to unify them.
[0099] Step S704: Retain the location information of the data center to which each data source belongs in the kinship-related information, so that the collected kinship-related information can display the data derivation relationship across data centers.
[0100] If it's necessary to differentiate data center locations, the location information of the data center to which each data source belongs needs to be retained in the data lineage information. This facilitates subsequent analysis of data derivation relationships across data centers. For example, a `datacenter` field can be added to the data lineage table to record the data center to which the data source belongs.
[0101] Step S705: Parse the SQL statements in the lineage-related information to generate an abstract syntax tree, and extract table-level lineage relationships, field-level lineage relationships, and corresponding task information by analyzing the abstract syntax tree.
[0102] Step S706: Store the extracted table-level lineage relationships, field-level lineage relationships, and task information in a relational database in the form of tables. The table-level lineage relationships and field-level lineage relationships include the current table and the sub-lineage relationship table.
[0103] As described above, the SQL-based data lineage analysis method proposed in this invention does not rely on hook plugins, avoiding intrusive modifications to existing code, reducing system maintenance costs and risks, and improving system stability. Furthermore, this invention can flexibly handle data from multiple data centers, selecting logical normalization or retaining physical location information based on user needs. This simplifies the display of data lineage relationships and meets the needs of scenarios sensitive to data location, enhancing the applicability of data lineage analysis.
[0104] like Figure 8 The diagram shows a schematic representation of a SQL-based data lineage analysis device according to an embodiment of this application. The device includes an information acquisition unit 810, a relationship extraction unit 820, and a storage unit 830, which are connected sequentially.
[0105] The information acquisition unit 810 is used to collect bloodline-related information from multiple different data sources.
[0106] The relation extraction unit 820 is used to parse the SQL statements in the lineage-related information to generate an abstract syntax tree, and to extract table-level lineage relationships, field-level lineage relationships, and corresponding task information by analyzing the abstract syntax tree.
[0107] Storage unit 830 is used to store the extracted table-level lineage relationships, field-level lineage relationships, and task information in the form of tables in a relational database. The table-level lineage relationships and field-level lineage relationships include the current table and the sub-lineage relationship table.
[0108] Preferably, the above-mentioned Figure 9 As shown, the above-mentioned device also includes:
[0109] The distinguishing field identification unit 840 is used to identify the distinguishing fields of database sharding and table partitioning in multiple different data sources.
[0110] The field replacement unit 850 is used to perform string replacement on the distinguishing field according to preset rules, so that the collected bloodline-related information belongs to the same logical table.
[0111] Preferably, the above-mentioned Figure 10 As shown, the above-mentioned device also includes:
[0112] The data center identification unit 860 is used to identify the data centers to which multiple different data sources belong.
[0113] The differentiation judgment unit 870 is used to determine whether it is necessary to differentiate the location of the data center according to preset rules.
[0114] The normalization processing unit 880 is used to perform logical normalization processing on data from the same logical table from different data centers when there is no need to distinguish the location of the data center, so as to unify its representation form and make the collected lineage-related information only show the logical derivation relationship between the data, without considering its physical storage location.
[0115] The location information retention unit 890 is used to retain the location information of the data center to which each data source belongs in the lineage-related information when it is necessary to distinguish the location of the data center, so that the collected lineage-related information can display the data derivation relationship across data centers.
[0116] Preferred, such as Figure 11 As shown, the relationship extraction unit 820 includes:
[0117] The parsing module 821 is used to parse the Hive SQL statements in the bloodline-related information and generate an abstract syntax tree.
[0118] The optimization module 822 is used to optimize the abstract syntax tree to remove useless tokens.
[0119] Traversal module 823 is used to traverse the abstract syntax tree to extract the upstream table name, downstream table name, and complete field information.
[0120] The construction module 824 is used to construct table-level lineage relationships based on the upstream table name and the downstream table name, and to construct field-level lineage relationships based on the complete field information.
[0121] Preferably, the traversal module 823 traversing the abstract syntax tree to extract complete field information includes: when traversing the abstract syntax tree, storing the field information in the field graph with the table name plus the field name as the key and the field source tree structure as the value, according to the delimiters and semantics in the abstract syntax tree; the construction module constructing field-level lineage relationships based on the complete field information includes: constructing field-level lineage relationships according to the relationships between fields in the field graph.
[0122] Preferably, the above-mentioned device further includes: a verification unit for validating the collected bloodline-related information; and a tag adding unit for adding validity tags to the corresponding table-level bloodline relationships and field-level bloodline relationships stored in the relational database based on the validity verification results.
[0123] For detailed descriptions of the above-mentioned units and modules, please refer to the corresponding descriptions in the foregoing method embodiments, which will not be repeated here.
[0124] As described above, the SQL-based data lineage analysis device proposed in this invention does not rely on hook plugins, avoiding intrusive modifications to existing code, reducing system maintenance costs and risks, and improving system stability. Furthermore, by performing string replacement normalization processing on the distinguishing fields of sharded databases and tables, this invention ensures that the data lineage information collected from sharded databases and tables is from the same logical table, improving the accuracy of data lineage analysis. Moreover, this invention can flexibly handle data from multiple data centers, selecting logical normalization processing or retaining physical location information according to user needs. This simplifies the display of data lineage relationships and meets the needs of scenarios sensitive to data location, enhancing the applicability of data lineage analysis.
[0125] Figure 12 This is a schematic diagram of an electronic device provided in an embodiment of the present invention. Figure 12 The illustrated electronic device is a general-purpose data processing apparatus, comprising a general-purpose computer hardware structure, including at least a processor 801 and a memory 802. The processor 801 and memory 802 are connected via a bus 803. The memory 802 is adapted to store one or more instructions or programs executable by the processor 801. These instructions or programs are executed by the processor 801 to implement the steps in the SQL-based data lineage analysis method described above.
[0126] The processor 801 described above can be a standalone microprocessor or a collection of one or more microprocessors. Thus, the processor 801 executes commands stored in the memory 802, thereby performing the method flow of the embodiments of the present invention as described above to process data and control other devices. The bus 803 connects the aforementioned components together, and also connects these components to the display controller 804, the display device, and the input / output (I / O) device 805. The input / output (I / O) device 805 can be a mouse, keyboard, modem, network interface, touch input device, motion-sensing input device, printer, and other devices known in the art. Typically, the input / output (I / O) device 805 is connected to the system via an input / output (I / O) controller 806.
[0127] The memory 802 can store software components, such as an operating system, a communication module, an interaction module, and application programs. Each of the modules and application programs described above corresponds to a set of executable program instructions that perform one or more functions and the methods described in the embodiments of the invention.
[0128] This invention also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the SQL-based data lineage analysis method described above.
[0129] In summary, the SQL-based data lineage analysis method and apparatus proposed in this invention do not rely on hook plugins, avoiding intrusive modifications to existing code, reducing system maintenance costs and risks, and improving system stability. Furthermore, by performing string replacement normalization processing on the distinguishing fields of sharded databases and tables, this invention ensures that the data lineage information collected from sharded databases and tables is from the same logical table, improving the accuracy of data lineage analysis. Moreover, this invention can flexibly handle data from multiple data centers, selecting logical normalization processing or retaining physical location information according to user needs. This simplifies the display of data lineage relationships and meets the needs of scenarios sensitive to data location, enhancing the applicability of data lineage analysis.
[0130] Preferred embodiments of the invention have been described above with reference to the accompanying drawings. Many features and advantages of these embodiments are apparent from this detailed description, and therefore the claims are intended to cover all such features and advantages of these embodiments that fall within their true spirit and scope. Furthermore, since many modifications and alterations will readily occur to those skilled in the art, the embodiments of the invention are not intended to be limited to the precise structures and operations illustrated and described, but rather to encompass all suitable modifications and equivalents falling within their scope.
[0131] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.
[0132] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.
[0133] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.
[0134] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.
[0135] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above descriptions are merely specific embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.
Claims
1. A data lineage analysis method based on SQL, characterized in that, The method includes: Bloodline-related information was collected from multiple different data sources; The SQL statements in the bloodline-related information are parsed to generate an abstract syntax tree, and the table-level bloodline relationship, field-level bloodline relationship, and corresponding task information are extracted by analyzing the abstract syntax tree; The extracted table-level lineage relationships, field-level lineage relationships, and task information are stored in a relational database in the form of tables. The table-level lineage relationships and field-level lineage relationships include the current table and the sub-lineage relationship table.
2. The SQL-based data lineage analysis method as described in claim 1, characterized in that, Prior to collecting kinship-related information from multiple different data sources, the method further includes: Identify the distinguishing fields for database sharding and table partitioning in multiple different data sources; The distinguishing fields are replaced with strings according to preset rules so that the collected bloodline-related information belongs to the same logical table.
3. The SQL-based data lineage analysis method as described in claim 1, characterized in that, Prior to collecting kinship-related information from multiple different data sources, the method further includes: Identify the data centers to which multiple different data sources belong; Determine whether it is necessary to differentiate data center locations based on preset rules: If it is not necessary to distinguish the location of the data center, the data of the same logical table from different data centers is logically normalized to unify its representation, so that the collected lineage-related information only shows the logical derivation relationship between the data, without considering its physical storage location. If it is necessary to distinguish the location of the data center, the location information of the data center to which each data source belongs is retained in the lineage-related information, so that the collected lineage-related information can display the data derivation relationship across data centers.
4. The SQL-based data lineage analysis method as described in claim 1, characterized in that, The process of parsing the SQL statements in the lineage-related information to generate an abstract syntax tree, and extracting table-level and field-level lineage relationships by analyzing the abstract syntax tree, includes: The Hive SQL statements in the bloodline-related information are parsed and an abstract syntax tree is generated; The abstract syntax tree is optimized to remove useless tokens; Traverse the abstract syntax tree to extract the upstream table name, downstream table name, and complete field information; A table-level lineage relationship is constructed based on the upstream and downstream table names, and a field-level lineage relationship is constructed based on the complete field information.
5. The SQL-based data lineage analysis method as described in claim 4, characterized in that, Traversing the abstract syntax tree to extract complete field information includes: When traversing the abstract syntax tree, the field information is stored in the field graph with the table name plus the field name as the key and the field source tree structure as the value, according to the delimiters and semantics in the abstract syntax tree; The construction of field-level bloodline relationships based on the complete field information includes: Construct field-level lineage relationships based on the relationships between fields in the field graph.
6. The SQL-based data lineage analysis method as described in claim 1, characterized in that, The method further includes: The validity of the collected bloodline-related information was verified; Based on the validity validation results, add validity labels to the corresponding table-level lineage relationships and field-level lineage relationships stored in the relational database.
7. A data lineage analysis device based on SQL, characterized in that, The device includes: The information collection unit is used to collect bloodline-related information from multiple different data sources; The relation extraction unit is used to parse the SQL statements in the lineage-related information to generate an abstract syntax tree, and to extract table-level lineage relationships, field-level lineage relationships, and corresponding task information by analyzing the abstract syntax tree; A storage unit is used to store the extracted table-level lineage relationships, field-level lineage relationships, and task information in the form of tables in a relational database. The table-level lineage relationships and field-level lineage relationships include the current table and the sub-lineage relationship table.
8. An electronic device 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 computer program, it implements the steps of the method according to any one of claims 1 to 6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.
10. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method according to any one of claims 1 to 6.
