Non-check-based heterogeneous database data blood relationship analysis method
Through the non-verified heterogeneous database data blood relationship analysis method, the SQL script is parsed to generate blood relationship graphs, solving the problem of relying on data governance systems and complex SQL syntax trees in the existing technology, and achieving efficient and independent blood relationship analysis and multi-database compatibility sex.
Patent Information
- Application Number
- CN202411039779.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-31
- Publication Date
- 2024-11-08
AI Technical Summary
When analyzing the data of SQL scripts, the existing technology relies on the data governance system or database dialect, and cannot independently parse existing scripts. It is difficult to obtain blood relationships in complex SQL syntax trees, and it is difficult to develop, and different database dialects need to redevelop matching rules.
Using a non-check-based heterogeneous database data blood relationship analysis method, the format replacement, lexical analysis and atomic blood relationship analysis are performed by reading SQL scripts, and lexical analysis is performed by using python's sqlparse module and sqllineage module to generate a blood relationship diagram. Persist this to files and databases, supporting custom replacement rules to suit different databases.
It realizes efficient parsing of SQL blood ties without relying on data governance systems and SQL syntax trees, improves analysis efficiency and compatibility, reduces development costs, and can independently parse blood ties links of multiple databases, which are suitable for data traceability and Impact analysis.
Smart Images

Figure CN118916377A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database technology, and in particular to a method for analyzing data lineage in heterogeneous databases based on non-verification. Background Art
[0002] The increasing complexity of data processing logic has given rise to demands for data traceability, impact analysis, and data system optimization, all of which point to the technology of data lineage. For data processing systems that are mainly based on SQL scripts, the best way to obtain data lineage is to parse SQL statements to obtain lineage. Currently, the industry's existing SQL-based data lineage analysis solutions usually rely on data governance systems or data development systems, or perform SQL lineage analysis based on one or several database dialects. The SQL lineage analysis of data governance systems and development tools mainly includes solutions based on integrated systems, solutions based on syntax trees, and manual solutions.
[0003] Solutions based on integrated systems are represented by Alibaba's Data-Works and ByteDance's Data-Leap, both of which are solutions integrated into data governance systems or data development systems. When developers use the integrated system to write SQL, they can select input and output tables through a graphical interface, use the table metadata registered in the data governance system, and combine SQL syntax parsing to automatically complete the construction of SQL lineage. The advantage of this solution is that the developer does not feel the entire process, and the generated SQL lineage is accurate. The first disadvantage is that it relies on the construction of the data governance system and the data development system. It can parse the lineage of SQL developed based on the integrated system, but it is powerless for scripts independent of the integrated system, such as stock scripts that existed before the data governance system was built.
[0004] The syntax tree-based solution is represented by the earlier parsing solution of Bank of Communications, which uses Druid or other tools to parse SQL to obtain lineage by building a syntax tree. The advantage of this type of solution is that the SQL syntax structure obtained is accurate, which is conducive to the correct extraction of lineage. Secondly, it can run independently of the integrated system and can parse existing scripts. The disadvantage of this type of solution is that although it is simple to obtain the syntax tree, it is difficult to write traversal rules that can obtain lineage on the syntax tree. The syntax tree of complex SQL is often complex. In actual practice, there are many situations to deal with when obtaining lineage from the multi-layer structure of a complex syntax tree, which is difficult to develop. In addition, different database dialects are different, and matching lineage rules need to be redeveloped. Summary of the invention
[0005] The purpose of the present invention is to overcome the defects of the above-mentioned prior art and provide a non-verification-based heterogeneous database data lineage analysis method with high accuracy and high applicability.
[0006] The purpose of the present invention can be achieved by the following technical solutions:
[0007] A method for analyzing data lineage in a heterogeneous database based on non-verification, the method comprising:
[0008] Read SQL script;
[0009] Preprocessing: Replace the format of the SQL script and convert it into an SQL string for lexical analysis;
[0010] Lexical analysis: Use Python's sqlparse module to perform lexical analysis on the SQL string, split the script string into multiple statements, and generate the corresponding token list;
[0011] Atomic lineage analysis: Based on the atomic lineage analysis sqllineage module, the token list is scanned, and the source and target states are switched according to the scanned keywords, and the corresponding source and target records are matched to generate the lineage from the source to the target.
[0012] Preferably, the format replacement of the SQL script includes:
[0013] Script variable replacement: Based on the regular expression replacement logic, script variables are classified as variables with the same name under the constant table. The constant table defaults to a collection of constants and script variables.
[0014] Script constant replacement: convert script constants to default values under the constant table;
[0015] Non-lineage function replacement: Convert CREATE statements to INSERT statements.
[0016] Preferably, the script constants include empty strings, string constants and non-string constants.
[0017] Preferably, the step of replacing the format of the SQL script further includes:
[0018] MERGE statement replacement: replace the MERGE statement with one or two INSERT statements;
[0019] And head annotation information extraction.
[0020] Preferably, the atomic lineage parsing sqllineage module further includes: for an INSERT-type SQL statement, by sharing the source and target states, the COL information is passed to the target state to match the source field with the target field.
[0021] Preferably, the atomic lineage parsing sqllineage module further includes: splitting a UNION type operation into two independent logics to perform UNION parsing.
[0022] Preferably, the method also includes performing graph processing on the output of the atomic lineage analysis sqllineage module, including: converting the lineage edges generated by the atomic lineage analysis in each sub-query process into nodes and edges, connecting the edges of each sub-query of the same statement into a lineage graph of the SQL statement, and then merging multiple lineage graphs of multiple SQL statements in the entire SQL script into a script lineage graph.
[0023] Preferably, after generating the script lineage graph, the method further includes:
[0024] Persist the edge information in the script lineage graph to files and databases;
[0025] The pandas library is used in the persistence process to save the parsed data in Excel file format and persist it to the hard disk;
[0026] The persisted data is converted into a dat file after character segmentation, and then saved into the database through database import operation.
[0027] Preferably, the reading of the SQL script is specifically:
[0028] Use Python's paramiko module for sftp download;
[0029] Read the address of the data center script from the configuration file in INI format, use SFTP to connect to the ETL server corresponding to the address, and download the SQL script with the specified script development naming specification.
[0030] Preferably, the method further comprises: using the logging module of Python to record each step of the blood relationship analysis in a hierarchical manner.
[0031] Compared with the prior art, the present invention has the following beneficial effects:
[0032] 1) The SQL lineage parsing method for heterogeneous databases based on non-verification designed by the present invention can effectively solve the problem of SQL lineage acquisition on heterogeneous databases, and the obtained lineage link has high integrity. It can run independently of the data governance system and the data development system, and does not rely on the SQL syntax tree. It is only built for the single scenario of lineage, with higher parsing efficiency and no need to adapt each grammatical structure. It can greatly improve the efficiency of business personnel and technical personnel in data tracing, impact analysis, and data system optimization.
[0033] 2) Before the lexical analysis, the present invention performs format replacement on the SQL script and converts it into an SQL string for lexical analysis, which can replace some statements that cannot be recognized by the master control program and obtain more accurate lineage.
[0034] 3) When the present invention replaces the format of the SQL script, through MERGE replacement and header comment information extraction, different replacement rules are added through different databases. Each project team can customize the added rules according to its own SQL script specifications, realizing customizable SQL script preprocessing, and further improving the accuracy of subsequent lexical analysis and atomic lineage resolution.
[0035] 4) The present invention adopts Python's sqlparse module to perform lexical analysis on the SQL script string. The selected sqlparse module is a non-verification type lexical analyzer. During lexical analysis, it does not verify whether the statement is lexically rigorous, but simply matches keywords and brackets. The bracket matching adopts a non-strict method, so it can be compatible with different dialects of different databases to the greatest extent, that is, it does not rely on the specific database dialect implementation and can cover most database systems.
[0036] 5) The present invention performs a non-round-trip scanning of the token list based on the atomic lineage parsing sqllineage module, switches between the source and target states according to the scanned keywords, matches the corresponding source and target records to generate the lineage from the source to the target, and the efficiency and compatibility of the lineage parsing scenario are extremely high.
[0037] 6) The present invention improves the atomic lineage parsing sqllineage module, including: for SQL statements of the INSERT(COL..) type, by sharing the source and target states, the COL information is passed to the target state, so that the source field and the target field can be matched; for UNION type operations, there is a mismatch problem in sqllineage, and by splitting the UNION into two independent logics, the UNION can be correctly parsed.
[0038] 7) This method is also compatible with self-circulating lineage, that is, the source and target are the same table, multiple output scripts, multiple statement scripts, etc., and there are only a few restrictions on development specifications, and the development cost and learning cost are low, which is economical and practical. BRIEF DESCRIPTION OF THE DRAWINGS
[0039] Figure 1 is a flow chart of the method of the present invention;
[0040] Figure 2 This is a schematic diagram of the data lineage model;
[0041] Figure 3 This is a flow chart of the data lineage analysis solution. DETAILED DESCRIPTION
[0042] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.
[0043] Example
[0044] Glossary
[0045] Data lineage (hereafter referred to as lineage) refers to the relationship between the source, destination, and transformation process of a given data element. It describes the source, transmission path, and transformation operation of the data, as well as the flow of data in different systems, processes, and environments.
[0046] Data lineage model: a directed cyclic graph, where the source node and the target node of the data are the nodes of the directed cyclic graph. Each SQL statement that exists for reading and writing defines a data conversion process, and the source and target of the conversion process can be found. Therefore, each SQL statement that exists for reading and writing corresponds to an edge in the graph, with the starting point of the graph as the source node and the end point as the target node. The directed cyclic graph formed by such edges and nodes is used as the intersection data lineage model. Depending on whether the node in the graph is a table or a field, it is divided into table-level lineage and field-level lineage.
[0047] Table-level lineage: The table is the finest-grained lineage. The source and destination of the data are both tables, and the conversion process is a table-to-table relationship.
[0048] Field-level lineage: The field is the finest-grained lineage. The source and destination of the data are both specific columns (fields) in the table, and the conversion process is a column-to-column relationship.
[0049] Database dialect: Different databases implement SQL statements differently.
[0050] SELECT-FROM statement: A statement that can read data from a source table. The general form is SELECT...FROM tableA, where "..." represents a column (COL). TableA may contain only one database table, or it may associate multiple tables through the JOIN syntax. Its characteristic is that it only reads data from the table.
[0051] Data writing statements: statements that can write data, mainly SQL statements containing keywords such as INSERT, UPDATE, MERGE, etc., whose characteristic is that they can modify the data in the table.
[0052] Data source: used to describe the source of data. In this embodiment, for each SQL statement, the data source is defined as the source table or source field in the SELECT-FROM statement.
[0053] Source table: In SELECT-FROM, the set of tables after FROM is considered as set A, the set of tables between SELECT and FROM that reference the columns of the table is considered as set B, and the intersection of sets AB is the source table of the statement.
[0054] Source fields: The columns referenced between SELECT and FROM in SELECT-FROM are called source fields. Except for constants, source fields cannot exist independently from the source table. If there are source fields, there must be a source table.
[0055] Target table: The table modified by the data write statement.
[0056] Target field: The column that is modified by the data write statement.
[0057] Bloodline: represents the data conversion process. The starting point of the bloodline is the source table or source field, and the end point of the bloodline is the target table or target field. Whether the bloodline is the source table or source field (or the target table and target field) depends on whether we obtain table-level bloodline or field-level bloodline. Bloodline is formed by a single SQL statement with read and write. SQL statements without read and write or read-only will not form bloodline. At the same time, there is no write-only SQL statement. For the special case INSERT-VALUES, it is regarded as a constant read.
[0058] Lineage path: A directed sequence from one node to another along the edges in the lineage graph.
[0059] Source node: A special node with an in-degree of 0, that is, the node does not come from SQL data conversion, but generally comes from data files loaded from the business system, or from services or messages from the business system.
[0060] Bloodline link: refers specifically to a special bloodline path corresponding to a node A, the end point of the path is node A and the starting point is the source node.
[0061] Bloodline link tree: The data of node A may come from multiple source nodes, so node A has multiple bloodline links. All the bloodline links of node A will eventually converge on node A to form a tree structure. Therefore, all the bloodline links of node A are called the bloodline link tree of node A.
[0062] In order to more intuitively describe the data lineage model, this embodiment draws a schematic diagram of the data lineage model, such as Figure 2As shown in the figure. The solid nodes represent tables or fields, depending on whether you want to obtain table-level lineage or field-level lineage. If you want to obtain table-level lineage through the model, the nodes in the figure represent a table, and vice versa. Each edge represents a read or write SQL statement, such as an INSERT or UPDATE statement. The source node represents the most upstream table or field in the lineage link, which is generally the source of all tables or fields in a link. Correspondingly, the target node represents the most downstream table or field in the lineage link, which is generally the last link of all tables or fields in a link, that is, there is no downstream table or field that has any reference to it for the time being. Note: If there is a new reference, just add a downstream node and add an edge to the figure. The green edge is a bidirectional arrow, indicating that there is a mutual reference between the two tables or fields. The red edge is a single arrow, indicating that there is only a one-way reference between the two tables or fields. All nodes and edges together constitute the data lineage model, which is essentially a directed cyclic graph.
[0063] like Figure 1 As shown, this embodiment provides a method for analyzing data lineage in a heterogeneous database based on non-verification, the method comprising:
[0064] S1. Read the SQL script in the configuration file;
[0065] Read the address of the data center script from the ini-format configuration file, use sftp to connect to the ETL server corresponding to the address, and download the specified SQL script. When downloading SQL scripts, only script names that meet the data script development naming specifications are downloaded to shield scripts uploaded by mistake.
[0066] An example of a configuration file in ini format is as follows:
[0067] [TEST]
[0068] host=123.123.xx.xxx
[0069] port=22
[0070] username=lineageUser
[0071] password=123456
[0072] remoteDir = / data / ETL /
[0073] localDir=E:\SqlLineage\SQL
[0074] resultPath=E:\SqlLineage\TEST\lineage_result.xlsx
[0075] errorPath=E:\SqlLineage\TEST\lineage_error.xlsx
[0076] validPathPrefix=IGNORE
[0077] S2. Preprocessing: Replace the format of the SQL script and convert it into a relatively simple and blood-equivalent SQL string for lexical analysis.
[0078] Replacement rules, including
[0079] 1) Script variable replacement:
[0080] Script variables are replaced with specific values when the script is running, but lexical analysis cannot be performed directly. Preprocessing is required. During preprocessing, it is necessary to ensure that the corresponding script variables can be restored to their original meanings by users when displayed. The conversion rules are mainly based on the idea of regular expression replacement. The following table 1 is a description of the regular expression.
[0081] Table 1
[0082]
[0083] The common rules are as follows:
[0084] sql=re.sub(r"'\$\{(.*?)\}'",r"CONTANT_VALUE.\1",sql)
[0085] The unchanged script variables are classified as variables with the same name under the CONTANT_VALUE table. CONTANT_VALUE is the default constant table. Users default to this table as a collection of constants and script variables.
[0086] 2) Script constant replacement:
[0087] Script constants generally include empty strings, string constants, non-string constants, etc. Here they are converted to the default values under CONTANT_VALUE. The replacement rules are as follows:
[0088] sql=re.sub(r""",r"CONTANT_VALUE.EMPTY_STR",sql)
[0089] sql=re.sub(r"'(.*?)'",r"CONTANT_VALUE.STR_VALUE",sql)
[0090] sql=re.sub(r"\b(\d+\.?\d*)\b",r"CONTANT_VALUE.CONTANT_VALUE",sql)
[0091] sql=re.sub(r"\bcurrent_timestamp\b",
[0092] "CONTANT_VALUE.CUR_TIMESTAMP",sql,flags=re.IGNORECASE)
[0093] sql=re.sub(r"\bcurrent_date\b",r"CONTANT_VALUE.CUR_TIMESTAMP",sql,flags=re.IGNORECASE)
[0094] sql=re.sub(r"\bNULL\b",r"CONTANT_VALUE.NON_VALUE",sql,flags=re.IGNORECASE)
[0095] 3) Non-bloodline function replacement:
[0096] Some functions do not read lineage, such as Count. The value of count depends on the count result, but the count result itself does not belong to any row of data in the source table.
[0097] sql=re.sub(r"COUNT\(\*\)",r"CONTANT_VALUE.COUNT_VALUE",sql,flags=re.IGNORECASE)
[0098] 4) Replacement of CREATE AS:
[0099] Atomic lineage analysis only recognizes the INSERT-UPDATE and SELECT-FROM modes. CREATE statements must be converted into INSERT statements to be correctly analyzed.
[0100] sql=re.sub(r"CREATE[\t]*TABLE[\t]*(\S+)[\t]*AS[\t]*TABLE\s",r"INSERTINTO\1",sql,flags=re.IGNORECASE)
[0101] sql=
[0102] re.sub(r"CREATE[\t\r\n]*TABLE[\t]*(\S+)[\t]*AS[\t]*--(\S+)[\t\r\n]*\(",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0103] sql=re.sub(r"CREATE[\t\r\n]*TABLE[\t]*(\S+)[\t]*AS[\t\r\n]*\(",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0104] sql=re.sub(r"CREATE[\t\r\n]*TABLE[\t]*(\S+)[\t]*AS\s",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0105] sql=
[0106] re.sub(r"CREATE[\t\r\n]*TABLE[\t\r\n]*(\S+)[\t]*AS[\t\r\n]*\(",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0107] sql=
[0108] re.sub(r"CREATE[\t\r\n]*LOCAL[\t]*TEMPORARY[\t]*TABLE[\t\r\n]*(\S+)[\t\r\n]*ON[\t]*COMMIT[\t]*PRESERVE[\t]*ROWS[\t]*DISTRIBUTE[\t]*BY[\t]*HASH[\t]*\(\S+\)[\t]*AS\s",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0109] sql=
[0110] re.sub(r"CREATE[\t\r\n]*LOCAL[\t]*TEMPORARY[\t]*TABLE[\t\r\n]*(\S+)[\t\r\n]*AS\s",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0111] sql=
[0112] re.sub(r"CREATE[\t\r\n]*LOCAL[\t]*TEMPORARY[\t]*TABLE[\t\r\n]*(\S+)[\t\r\n]*AS[\t\r\n]",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0113] sql=
[0114] re.sub(r"CREATE[\t\r\n]*LOCAL[\t]*TEMPORARY[\t]*TABLE[\t\r\n]*(\S+)[\t\r\n]*ON[\t]*COMMIT[\t]*PRESERVE[\t]*ROWS[\t\r\n]*AS\s",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0115] sql=re.sub(r"CREATE[\t]*VIEW[\t]*(\S+)[\t]*AS\s",r"INSERT INTO\1",sql,flags=re.IGNORECASE)
[0116] The above statements are all different ways of writing CREATE TABLE (each sentence after 'sql=...' is a way of writing), which are converted into the form of INSERT INTO through regular expressions to facilitate the acquisition of data lineage.
[0117] Finally, based on the actual parsing process, if you find that the parsing cannot be done accurately, you can add other replacement rules to ensure bloodline consistency.
[0118] Custom rules: Add different replacement rules for different databases. Each project team can also add rules here according to their own SQL script specifications. Since it only involves string replacement, the development cost is low.
[0119] In this embodiment, the custom rules include:
[0120] 1) MERGE statement replacement: There are two matching situations for MERGE statements in SQL, one is when a match is found, written as WHENMATCHED; the other is when a match is not found, written as WHEN NOT MATCHED. Here, MERGE is replaced with an INSERT statement based on these two situations. When only one of them appears, it is replaced with one INSERT statement. If both situations appear in the SQL statement at the same time, they are replaced with two INSERT statements, one for MATCHED and one for NOT MATCHED. This type of replacement is all for better acquisition of data lineage information.
[0121] 2) Head annotation information extraction
[0122] Through the above replacement rules, some statements that cannot be recognized by the master control program are replaced, and it is expected to obtain more accurate bloodline.
[0123] S3. Lexical analysis
[0124] The sqlparse module of Python is used to perform lexical analysis on the SQL script string. The script string is divided into multiple statements, and each statement is tokenized to generate a token list for lineage analysis.
[0125] Since sqlparse is a non-verifying lexical analyzer, it does not verify whether the statement is lexically rigorous during lexical analysis, but simply matches keywords and brackets. Bracket matching is not strict, so it can be compatible with different dialects of different databases to the greatest extent. At the same time, in the subsequent lineage analysis process, we only care about the two types of keywords INSERT-UPDATE and SELECT-FROM that will generate lineage, and the lexical inaccuracy will not affect the analysis of lineage.
[0126] S4. Atomic lineage analysis
[0127] The atomic lineage parsing of this embodiment is mainly based on the sqllineage library and is localized based on the application scenario. The reason for selecting the sqllineage module is that sqllineage uses a non-syntax tree implementation, and its essence is a program that sequentially scans the token list without back and forth. During the scanning process, it switches between the source and target states according to the scanned keywords, and then matches the corresponding source and target records in a manner similar to bracket matching to generate the lineage from the source to the target. Such a non-syntax tree implementation makes the efficiency and compatibility of lineage parsing scenarios extremely high.
[0128] To address the problem that the existing sqllineage library cannot directly interact with the data center environment, this embodiment makes two improvements to the sqllineage library:
[0129] 1) For SQL statements of the type INSERT(COL..), field-level lineage cannot achieve matching between source and target fields. The solution of this method is to modify the source code of sqllineage, achieve sharing of source and target states, pass COL information to the target state, and thus achieve matching between source and target fields.
[0130] 2) For UNION operations, there is a mismatch problem in sqllineage. By modifying the logic of the source code, the idea is to split UNION into two independent logics so that UNION can be parsed correctly.
[0131] S5. Graph Processing
[0132] Based on the networkX library, for each subquery, the lineage edges generated by atomic lineage parsing are converted into nodes and edges and stored in the networkX graph, and this process is automatically completed by networkX. Then networkX connects the edges of each subquery of the same statement into the lineage graph of the SQL statement, and then merges multiple lineage graphs of multiple SQL statements in the entire script into a script lineage graph.
[0133] In addition, in this step, the intermediate table can be eliminated as needed, and the table with in-degree 0 is found as the source table of the script, and the table with out-degree 0 is found as the target table of the script, but this method is not suitable for self-circular dependent scripts.
[0134] S6, Bloodline Output
[0135] Persist edge information in networkX graphs to files and databases.
[0136] The programming language used in this embodiment is Python. The pandas library is used in the persistence process to save the parsed data in Excel file format and persist it to the hard disk. The persisted data is segmented by special characters (Note: the special characters can be set according to the development habits of the enterprise), converted into a dat file, and then saved into the database through the database import operation for subsequent display by the blood relationship front end.
[0137] After the above steps, the initial parsed data can now be obtained in the database.
[0138] Since the atomic lineage parsing library sqllineage cannot directly obtain a lineage link, it only parses the SQL script into isolated mappings from source to target, that is, part of a directed cyclic graph.
[0139] Therefore, recursive logic is also needed here to trace each fragment upward from the target node until the most upstream node (source node) is found and recorded as a complete link in the database table.
[0140] S7, bloodline front-end display
[0141] It provides users with functions such as lineage query and traceability, and its display form is a lineage graph, including table-level lineage graph and field-level lineage graph.
[0142] The above explains the main aspects of the data lineage analysis solution, namely the principles and implementation ideas of the analysis.
[0143] This embodiment also includes log processing, specifically: using the logging module of Python to record the output of each step in a hierarchical manner.
[0144] Figure 3 This is the data lineage analysis solution process of this embodiment.
[0145] The electronic device of the present invention includes a central processing unit (CPU), which can perform various appropriate actions and processes according to computer program instructions stored in a read-only memory (ROM) or loaded from a storage unit into a random access memory (RAM). In the RAM, various programs and data required for device operation can also be stored. The CPU, ROM and RAM are connected to each other via a bus. An input / output (I / O) interface is also connected to the bus.
[0146] Multiple components in the device are connected to the I / O interface, including: input units, such as keyboards, mice, etc.; output units, such as various types of displays, speakers, etc.; storage units, such as disks, optical disks, etc.; and communication units, such as network cards, modems, wireless communication transceivers, etc. The communication unit allows the device to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunication networks.
[0147] The processing unit performs the various methods and processes described above, such as methods S1 to S7. For example, in some embodiments, methods S1 to S7 may be implemented as a computer software program, which is tangibly contained in a machine-readable medium, such as a storage unit. In some embodiments, part or all of the computer program may be loaded and / or installed on the device via a ROM and / or a communication unit. When the computer program is loaded into the RAM and executed by the CPU, one or more steps of methods S1 to S7 described above may be performed. Alternatively, in other embodiments, the CPU may be configured to execute methods S1 to S7 in any other appropriate manner (e.g., by means of firmware).
[0148] The functions described above herein may be performed at least in part by one or more hardware logic components. For example, without limitation, exemplary types of hardware logic components that may be used include: field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chip systems (SOCs), load programmable logic devices (CPLDs), and the like.
[0149] The program code for implementing the method of the present invention can be written in any combination of one or more programming languages. These program codes can be provided to a processor or controller of a general-purpose computer, a special-purpose computer or other programmable data processing device, so that the program code, when executed by the processor or controller, enables the functions / operations specified in the flow chart and / or block diagram to be implemented. The program code can be executed entirely on the machine, partially on the machine, partially on the machine as a stand-alone software package and partially on a remote machine, or entirely on a remote machine or server.
[0150] In the context of the present invention, a machine-readable medium may be a tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, device, or equipment. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or equipment, or any suitable combination of the foregoing. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
[0151] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed by the present invention, and these modifications or replacements should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be based on the protection scope of the claims.
Claims
1. A method for analyzing data lineage in heterogeneous databases based on non-verification, characterized in that: The method includes: Read SQL script; Preprocessing: Replace the format of the SQL script and convert it into an SQL string for lexical analysis; Lexical analysis: Use Python's sqlparse module to perform lexical analysis on the SQL string, split the script string into multiple statements, and generate the corresponding token list; Atomic lineage analysis: Based on the atomic lineage analysis sqllineage module, the token list is scanned, and the source and target states are switched according to the scanned keywords, and the corresponding source and target records are matched to generate the lineage from the source to the target.
2. According to the non-verification-based heterogeneous database data lineage analysis method of claim 1, it is characterized in that: The format replacement of the SQL script includes: Script variable replacement: Based on the regular expression replacement logic, script variables are classified as variables with the same name under the constant table. The constant table defaults to a collection of constants and script variables. Script constant replacement: convert script constants to default values under the constant table; Non-lineage function replacement: Convert CREATE statements to INSERT statements.
3. According to the non-verification-based heterogeneous database data lineage analysis method of claim 2, it is characterized in that: The script constants include empty strings, string constants and non-string constants.
4. According to the non-verification-based heterogeneous database data lineage analysis method of claim 1, it is characterized in that: The format replacement of the SQL script also includes: MERGE statement replacement: replace the MERGE statement with one or two INSERT statements; And head annotation information extraction.
5. According to the non-verification-based heterogeneous database data lineage analysis method of claim 1, it is characterized in that: The atomic lineage analysis sqllineage module also includes: for an INSERT-type SQL statement, by sharing the source and target states, the COL information is passed to the target state to match the source field with the target field.
6. The method for analyzing data lineage in heterogeneous databases based on non-verification according to claim 1, characterized in that: The atomic lineage analysis sqllineage module also includes: splitting the UNION type operation into two independent logics to perform UNION analysis.
7. The method for analyzing data lineage in heterogeneous databases based on non-verification according to claim 1, characterized in that: The method also includes graph processing of the output of the atomic lineage analysis sqllineage module, including: converting the lineage edges generated by the atomic lineage analysis in each sub-query process into nodes and edges, connecting the edges of each sub-query of the same statement into a lineage graph of the SQL statement, and then merging multiple lineage graphs of multiple SQL statements in the entire SQL script into a script lineage graph.
8. The method for analyzing data lineage in heterogeneous databases based on non-verification according to claim 7, characterized in that: After generating the script lineage graph, it also includes: Persist the edge information in the script lineage graph to files and databases; The pandas library is used in the persistence process to save the parsed data in Excel file format and persist it to the hard disk; The persisted data is converted into a dat file after character segmentation, and then saved into the database through database import operation.
9. The method for analyzing data lineage in heterogeneous databases based on non-verification according to claim 1, characterized in that: The SQL script reading is specifically as follows: Use Python's paramiko module for sftp download; Read the address of the data center script from the configuration file in INI format, use SFTP to connect to the ETL server corresponding to the address, and download the SQL script with the specified script development naming specification.
10. The method for analyzing data lineage in heterogeneous databases based on non-verification according to claim 1, characterized in that: The method further comprises: using a logging module of Python to record each step of blood relationship analysis in a hierarchical manner.
