Script data consanguinity detection method and device

Abstract

The invention belongs to the technical field of big data processing, and particularly discloses a script data consanguinity detection method and device.The method comprises the steps that SQL statements executed in scripts are intercepted through a database driving module, target SQL statements are determined and forwarded to an SQL consanguinity analysis module, and the target SQL statements are SQL statements of to-be-analyzed data consanguinity; analyzing the target SQL statement through an SQL blood relationship analysis module to obtain a blood relationship between the data; wherein the access of the script to the database is realized based on the database driving module, and the database connection address corresponding to the script comprises an address used for callback of the SQL blood relationship analysis module. According to the method and the device, the SQL executed in various scripts can be intercepted and then forwarded to the SQL blood relationship analysis component, so that the blood relationship of the data table is analyzed, and a complete blood relationship link of a big data governance process is efficiently constructed.

Description

Technical Field

[0001] The present application belongs to the field of big data processing technology, and more specifically, to a script data lineage detection method and device. Background Art

[0002] In the big data industry, data governance is an extremely important function, and the lineage link of data tables is a core capability in data governance. Through the data lineage link, we can clearly know what kind of data flow the data being governed has in the entire big data platform. For example, there is a table A, whose data comes from table B and part of table C. Through the data table lineage link, we can know which data in table A comes from table B and which comes from table C. For the current data lineage monitoring in the big data field, we can only write the complete SQL content, that is, static SQL, in the SQL script, and then use SQL parsing technology to parse the data lineage. However, in the actual governance process, many governance process links may be involved (for example, executing a shell script or python script to perform some data processing). The SQL script is only one process node among many data governance, which means that there are many other data governance links that cannot detect the lineage of data tables. How to efficiently build a complete lineage link for the big data governance process is a technical problem that needs to be solved in this field. Summary of the invention

[0003] In view of the shortcomings of the existing technology, the purpose of this application is to achieve efficient construction of a complete lineage chain of the big data governance process.

[0004] To achieve the above objectives, in a first aspect, the present application provides a script data lineage detection method, comprising: Through the database driver module, the SQL statement executed in the script is intercepted, the target SQL statement is determined, and the target SQL statement is forwarded to the SQL lineage analysis module. The target SQL statement is the SQL statement of the data lineage relationship to be analyzed; Through the SQL lineage analysis module, the target SQL statement is analyzed to obtain the lineage relationship between the data; Among them, the script's access to the database is implemented based on the database driver module, and the database connection address corresponding to the script includes the address for calling back the SQL lineage analysis module.

[0005] In a possible implementation, the SQL statement executed in the script is intercepted by a database driver module to determine the target SQL statement, including: Based on the historical signature data and the signature of the intercepted SQL statement, the candidate SQL statement is screened out, and the signature of the candidate SQL statement does not exist in the historical signature data; Based on the statement type set, the candidate SQL statements are screened to determine the target SQL statement. The statement types in the statement type set are statement types associated with the data lineage relationship, and the statement type corresponding to the target SQL statement belongs to the statement type set.

[0006] In a possible implementation, before selecting candidate SQL statements, the method further includes: signing the intercepted SQL statements.

[0007] In a possible implementation, the statement type set includes: an INSERT statement type and an UPDATE statement type.

[0008] In a possible implementation, the SQL lineage analysis module includes: a lexical analyzer and a parser; the lexical analyzer is used to convert SQL statements into a series of lexical units according to the lexical rules of SQL and determine the lexical unit sequence; the parser is used to convert the lexical unit sequence into an abstract syntax tree according to the grammatical rules of SQL; The SQL lineage analysis module is used to parse the target SQL statement and obtain the lineage relationship between data, including: The target SQL is parsed through the lexical analyzer and parser to obtain the blood relationship between the data.

[0009] In a possible implementation, before parsing the target SQL statement, the following steps are further included: Determine the rule file of the ANTLR tool, which is used to represent lexical rules and grammar rules; Generate lexical analyzer and parser through ANTLR tool.

[0010] In a possible implementation, the script is a SQL script, a Python script, a Java script, or a shell script.

[0011] In a second aspect, the present application provides a script data lineage detection device, comprising: The database driver module is used to intercept the SQL statements executed in the script, determine the target SQL statement, and forward the target SQL statement to the SQL lineage analysis module. The target SQL statement is the SQL statement of the data lineage relationship to be analyzed; SQL lineage analysis module, used to parse the target SQL statement and obtain the lineage relationship between data; Among them, the script's access to the database is implemented based on the database driver module, and the database connection address corresponding to the script includes the address for calling back the SQL lineage analysis module.

[0012] In a third aspect, the present application provides an electronic device comprising: at least one memory for storing programs; and at least one processor for executing the programs stored in the memory. When the program stored in the memory is executed, the processor is used to execute the method described in the first aspect or any possible implementation of the first aspect.

[0013] In a fourth aspect, the present application provides a computer-readable storage medium, which stores a computer program. When the computer program runs on a processor, the processor executes the method described in the first aspect or any possible implementation of the first aspect.

[0014] In general, the above technical solutions conceived by this application have the following beneficial effects compared with the prior art: The present application provides a method of dynamically intercepting and executing SQL, which uniformly intercepts the SQL executed in scripts such as SQL scripts, python scripts, java scripts, and shell scripts, and then forwards the intercepted SQL to the SQL lineage analysis module for lineage analysis. In this way, the data lineage in each link involved in data governance can be obtained, and the complete lineage link of the big data governance process can be efficiently constructed. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 This is one of the flow charts of the script data blood relationship detection method provided in the embodiment of the present application; Figure 2 This is the second flow chart of the script data blood relationship detection method provided in the embodiment of the present application; Figure 3 It is a schematic diagram of the structure of the script data blood relationship detection device provided in the embodiment of the present application; Figure 4 It is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0016] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0017] In the embodiments of the present application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the embodiments of the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0018] In the description of the embodiments of the present application, unless otherwise specified, "multiple" means two or more than two. For example, multiple processing units refer to two or more processing units, etc.; multiple elements refer to two or more elements, etc.

[0019] First, the technical terms involved in the embodiments of the present application are introduced.

[0020] (1) SQL: Structure Query Language. The standard query language for databases. It can be divided into data definition language (DDL), data manipulation language (DML) and data control language (DCL).

[0021] (2) Database driver: provides a way to interact with the database, acts as an interface between the application and the database, converts the application's statements into a language that the database understands, and converts the database's results into a language that the application understands. Such as JDBC driver, ODBC driver.

[0022] (3) SQL lineage analysis: refers to the process of analyzing the dependencies and data flows between tables by parsing SQL statements. Antlr4 is usually used to implement SQL lineage analysis.

[0023] (4) ANTLR (Another Tool for Language Recognition) is an open source parser generation tool developed based on Java. It can generate corresponding grammar analyzers based on grammar rule files. It is widely used in DSL construction, language lexical and grammatical analysis, and other fields. ANTLR4 is its fourth version. The rule file of ANTLR4 is the core part of defining the language grammar, usually with the extension .g4.

[0024] The embodiments of the present application are described below in conjunction with the drawings in the embodiments of the present application.

[0025] Figure 1 This is one of the flow charts of the script data blood relationship detection method provided in the embodiment of the present application, such as Figure 1 As shown, the method includes the following steps S101 and S102.

[0026] Step S101, intercepting the SQL statement executed in the script through the database driver module (or database driver), determining the target SQL statement, and forwarding the target SQL statement to the SQL lineage analysis module, where the target SQL statement is the SQL statement of the data lineage relationship to be analyzed; Step S102, parsing the target SQL statement through the SQL lineage parsing module (or SQL lineage parsing component) to obtain the lineage relationship between the data; Among them, the script's access to the database is implemented based on the database driver module, and the database connection address corresponding to the script includes the address for calling back the SQL lineage analysis module.

[0027] It can be understood that, in the process of creating an application, the database driver module is referenced, the application's script can access the database through the database driver module, and at the same time, the database connection address is configured in the application, and the address of the callback lineage resolution module is added to the URL (Uniform Resource Locator). Then, when the application's script executes SQL statements, the database driver module can be used to intercept the SQL statements executed in the script and determine the target SQL statement, which is the SQL statement for the data lineage relationship to be resolved. The target SQL statement is then forwarded to the SQL lineage resolution module, and the target SQL statement is parsed by the SQL lineage resolution module to obtain the lineage relationship between the data.

[0028] Therefore, the present application provides a method of dynamically intercepting and executing SQL, which uniformly intercepts the SQL executed in scripts such as SQL scripts, python scripts, java scripts, and shell scripts, and then forwards the intercepted SQL to the SQL lineage analysis module for lineage analysis. In this way, the data lineage in each link involved in data governance can be obtained, and the complete lineage link of the big data governance process can be efficiently constructed.

[0029] In a possible implementation, intercepting the SQL statement executed in the script by the database driver module and determining the target SQL statement include: Based on the historical signature data and the signature of the SQL statement, an alternative SQL statement is screened out, and the signature of the alternative SQL statement does not exist in the historical signature data; Based on the statement type set, the candidate SQL statements are screened to determine the target SQL statement. The statement types in the statement type set are statement types associated with the data lineage relationship, and the statement type corresponding to the target SQL statement belongs to the statement type set.

[0030] It is understandable that the same SQL statement corresponds to the same signature. By screening out alternative SQL statements based on historical signature data and the signature of the SQL statement, it is possible to avoid the SQL lineage parsing module from repeatedly parsing the same SQL, thus saving computing resources.

[0031] Among various SQL statement types, some statement types (such as INSERT statement type and UPDATE statement type) are statement types associated with data lineage. Parsing SQL statements under these statement types can effectively obtain the lineage relationship between data. However, for other statement types (such as DELETE statement type or SELECT statement type), they are statement types that are not associated with data lineage. Parsing SQL statements under these statement types cannot obtain the lineage relationship between data. Therefore, by pre-configuring a statement type set, it is possible to filter among the alternative SQL statements based on the statement type set to determine the target SQL statement. The statement type corresponding to the filtered target SQL statement belongs to the statement type set, which can ensure that the SQL statement received by the SQL lineage parsing module is a SQL statement under a statement type associated with data lineage, avoid the SQL lineage parsing module from parsing SQL statements under statement types that are not associated with data lineage, and improve parsing efficiency.

[0032] The INSERT statement, UPDATE statement, DELETE statement and SELECT statement are the four basic operation statements in SQL (Structured Query Language), which are used to insert, update, delete and query data in the database respectively.

[0033] In a possible implementation, before selecting candidate SQL statements, the method further includes: signing the intercepted SQL statements.

[0034] Exemplarily, a hash value corresponding to the SQL statement may be calculated using a hash algorithm, and the calculated hash value may be used as the signature of the SQL statement.

[0035] In a possible implementation, the statement type set includes: an INSERT statement type and an UPDATE statement type.

[0036] In a possible implementation, the SQL lineage parsing module includes: a lexical analyzer and a parser, wherein the lexical analyzer is used to convert the SQL statement into a series of lexical units according to the lexical rules of SQL and determine the sequence of lexical units; the parser is used to convert the sequence of lexical units into an abstract syntax tree (AST) according to the grammatical rules of SQL to represent the structure of the SQL statement; The above SQL lineage analysis module parses the target SQL statement to obtain the lineage relationship between data, including: The target SQL is parsed through the lexical analyzer and parser to obtain the blood relationship between the data.

[0037] It can be understood that the lexical analyzer can convert SQL statements into a series of lexical units, and the parser can convert the lexical unit sequence into an abstract syntax tree, and then traverse the AST and analyze each node in the AST, and analyze the dependencies and data flow between related tables for each node to obtain the blood relationship between the data.

[0038] In a possible implementation, before parsing the target SQL statement, the following steps are further included: Determine the rule file of the ANTLR tool, which is used to represent lexical rules and grammar rules; Generate lexical analyzer and parser through ANTLR tool.

[0039] In a possible implementation, the script is a SQL script, a Python script, a Java script, or a shell script.

[0040] The following is an illustrative description of the script data bloodline detection method provided in the embodiments of the present application with reference to several examples.

[0041] Figure 2 This is the second flow chart of the script data blood relationship detection method provided in the embodiment of the present application, such as Figure 2 As shown, this application has two stages. The first stage is to use the database driver provided by this application. When configuring the database link, configure the address of the callback SQL lineage resolution component, so that the driver will intercept the SQL and forward it to the SQL lineage resolution component. The second stage is the process of the SQL lineage component performing lineage resolution on SQL.

[0042] The detailed steps of the first stage include step S201 to step S204.

[0043] Step S201, expanding the url connection parameters of the database driver program (or database driver module).

[0044] Step S202, adding the address of the SQL lineage resolution component to the URL connection parameters.

[0045] Step S203, the extended driver intercepts the SQL executed in the script: Step S2031, signing the intercepted SQL; Step S2032: If a SQL with the same signature is detected in the system, the process is ignored, otherwise, the process goes to step S2033; S2033, classify the SQL type, if it is INSERT or UPDATE, proceed to step S2034, otherwise ignore the process; Step S2034, asynchronously forward the intercepted SQL to the lineage analysis component via HTTP.

[0046] Step S204, using the database driver provided by this application in the data governance script.

[0047] The detailed steps of the second stage include step S301 to step S305.

[0048] Step S301, define the g4 rule file for Antlr4 to parse SQL in the SQL lineage parsing component.

[0049] Step S302, generating a lexical analyzer and a parser based on the Antlr4 rule file.

[0050] Step S303: the SQL lineage component receives the sent SQL.

[0051] Step S304: Based on the lexical analyzer and parser in step S302, the received SQL is parsed using the monitoring mode to obtain the blood relationship between the data.

[0052] Step S305, storing the blood relationship parsed in step S304 into the blood relationship parsing table.

[0053] Through the database driver provided by this application, the address of the SQL lineage analysis component to be forwarded is configured while configuring the database link information. In this way, the SQL executed in various scripts can be intercepted and then forwarded to the SQL lineage analysis component, thereby parsing the lineage relationship of the data table.

[0054] The following is a specific implementation case, and the detailed implementation steps are as follows.

[0055] Step S401, create a new Java application and introduce the database driver jar of this application.

[0056] Step S402, start the SQL lineage analysis component of the present application.

[0057] Step S403, configure the database connection address in the Java application, and add the address of the callback blood relationship resolution component in the URL.

[0058] Step S404: execute such a SQL statement in the Java application: "insert into table_a select t1.col1,t2.col2 from table_b t1 innerjoin table_c t2 on t1.id = t2.id".

[0059] Step S405, the database driver will intercept the SQL just executed and send it to the blood relationship analysis component.

[0060] Step S406, the lineage analysis component performs SQL lineage analysis and obtains the following lineage relationship: the data of col1 in table_a comes from col1 in table_b, and the data of col2 in table_a comes from col2 in table_c. In this way, a complete lineage relationship in table_a is obtained.

[0061] Compared with the traditional method of lineage analysis based on static SQL in the field of big data governance, the advantage of this application is that it can intercept SQL in the driver and perform SQL lineage analysis. In this way, each governance SQL involved in the data governance process can be analyzed by lineage, thus forming a complete lineage link of the big data governance process, and more clearly and intuitively seeing the data flow in the entire platform.

[0062] The script data bloodline detection device provided in the present application is described below. The script data bloodline detection device described below and the script data bloodline detection method described above can be referenced to each other.

[0063] Figure 3 is a schematic diagram of the structure of the script data blood relationship detection device provided in the embodiment of the present application, such as Figure 3 As shown, the device includes: a database driving module 10 and an SQL lineage analysis module 20.

[0064] The database driver module 10 is used to intercept the SQL statements executed in the script, determine the target SQL statement, and forward the target SQL statement to the SQL lineage analysis module, where the target SQL statement is the SQL statement of the data lineage relationship to be analyzed; SQL lineage analysis module 20, used to parse the target SQL statement and obtain the lineage relationship between data; Among them, the script's access to the database is implemented based on the database driver module, and the database connection address corresponding to the script includes the address for calling back the SQL lineage analysis module.

[0065] It can be understood that the detailed functional implementation of each of the above-mentioned units / modules can be found in the introduction of the aforementioned method embodiment, and will not be repeated here.

[0066] It should be understood that the above-mentioned device is used to execute the method in the above-mentioned embodiment. The implementation principle and technical effect of the corresponding program module in the device are similar to those described in the above-mentioned method. The working process of the device can refer to the corresponding process in the above-mentioned method, which will not be repeated here.

[0067] Based on the method in the above embodiment, an embodiment of the present application provides an electronic device, Figure 4 is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application, such as Figure 4 As shown, the electronic device may include: a processor (Processor) 810, a communication interface (Communications Interface) 820, a memory (Memory) 830 and a communication bus 840, wherein the processor 810, the communication interface 820, and the memory 830 communicate with each other through the communication bus 840. The processor 810 can call the logic instructions in the memory 830 to execute the method in the above embodiment.

[0068] In addition, the logic instructions in the above-mentioned memory 830 can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product, which is stored in a storage medium and includes a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application.

[0069] Based on the method in the above embodiment, an embodiment of the present application provides a computer-readable storage medium, which stores a computer program. When the computer program runs on a processor, the processor executes the method in the above embodiment.

[0070] Based on the method in the above embodiment, an embodiment of the present application provides a computer program product. When the computer program product runs on a processor, the processor executes the method in the above embodiment.

[0071] It is understandable that the processor in the embodiment of the present application may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field programmable gate arrays (FPGA) or other programmable logic devices, transistor logic devices, hardware components or any combination thereof. The general-purpose processor may be a microprocessor or any conventional processor.

[0072] The method steps in the embodiments of the present application can be implemented by hardware or by a processor executing software instructions. The software instructions can be composed of corresponding software modules, and the software modules can be stored in random access memory (RAM), flash memory, read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), registers, hard disks, mobile hard disks, CD-ROMs, or any other form of storage medium known in the art. An exemplary storage medium is coupled to the processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in an ASIC.

[0073] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented by software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium or transmitted through the computer-readable storage medium. The computer instructions may be transmitted from a website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state drive (SSD)), etc.

[0074] It should be understood that the various numerical numbers involved in the embodiments of the present application are only used for the convenience of description and are not used to limit the scope of the embodiments of the present application.

[0075] It will be easily understood by those skilled in the art that the above description is only a preferred embodiment of the present application and is not intended to limit the present application. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present application shall be included in the scope of protection of the present application.

Claims

1. A script data lineage detection method, characterized in that: include: Through the database driver module, the SQL statement executed in the script is intercepted, the target SQL statement is determined, and the target SQL statement is forwarded to the SQL lineage analysis module. The target SQL statement is the SQL statement of the data lineage relationship to be analyzed; Through the SQL lineage analysis module, the target SQL statement is analyzed to obtain the lineage relationship between the data; Among them, the script's access to the database is implemented based on the database driver module, and the database connection address corresponding to the script includes the address for calling back the SQL lineage analysis module.

2. The script data lineage detection method according to claim 1, characterized in that: The method of intercepting the SQL statement executed in the script through the database driver module and determining the target SQL statement includes: Based on the historical signature data and the signature of the intercepted SQL statement, the candidate SQL statement is screened out, and the signature of the candidate SQL statement does not exist in the historical signature data; Based on the statement type set, the candidate SQL statements are screened to determine the target SQL statement. The statement types in the statement type set are statement types associated with the data lineage relationship, and the statement type corresponding to the target SQL statement belongs to the statement type set.

3. The script data lineage detection method according to claim 2, characterized in that: Before selecting the candidate SQL statements, the method further includes: signing the intercepted SQL statements.

4. The script data lineage detection method according to claim 2, characterized in that: The statement type set includes: INSERT statement type and UPDATE statement type.

5. The script data lineage detection method according to claim 1, characterized in that: The SQL lineage analysis module includes: a lexical analyzer and a parser; the lexical analyzer is used to convert SQL statements into a series of lexical units according to the lexical rules of SQL and determine the lexical unit sequence; the parser is used to convert the lexical unit sequence into an abstract syntax tree according to the grammatical rules of SQL; The target SQL statement is parsed through the SQL lineage parsing module to obtain the lineage relationship between the data, including: The target SQL is parsed through the lexical analyzer and parser to obtain the blood relationship between the data.

6. The script data lineage detection method according to claim 5, characterized in that: Before parsing the target SQL statement, it also includes: Determine the rule file of the ANTLR tool, which is used to represent lexical rules and grammar rules; Generate lexical analyzer and parser through ANTLR tool.

7. The script data lineage detection method according to any one of claims 1 to 6, characterized in that: The script is a SQL script, a Python script, a Java script or a shell script.

8. A script data bloodline detection device, characterized in that: include: The database driver module is used to intercept the SQL statements executed in the script, determine the target SQL statement, and forward the target SQL statement to the SQL lineage analysis module. The target SQL statement is the SQL statement of the data lineage relationship to be analyzed; SQL lineage analysis module, used to parse the target SQL statement and obtain the lineage relationship between data; Among them, the script's access to the database is implemented based on the database driver module, and the database connection address corresponding to the script includes the address for calling back the SQL lineage analysis module.

9. An electronic device, characterized in that: include: at least one memory for storing a computer program; At least one processor is used to execute the program stored in the memory. When the program stored in the memory is executed, the processor is used to execute the method according to any one of claims 1 to 7.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program runs on a processor, the processor is caused to execute the method according to any one of claims 1 to 7.

Images