Data warehouse dependency relationship generation method and device, equipment and storage medium

Abstract

The invention provides a data warehouse dependency relationship generation method which can be applied to the technical field of big data. The data warehouse dependency relationship generation method comprises the steps that execution information of a target job is acquired, and the execution information comprises an execution server and an execution time period of the target job; in the execution time period, ports of the execution server are scanned, port information of the execution server is obtained, and the port information and the target jobs have a one-to-one correspondence mapping relation; in the execution time period, according to the port information and the IP address of the execution server, screening out a target structured query language script corresponding to the target job from a plurality of structured query language scripts of the data warehouse; and according to the target structured query language script, generating a dependency relationship of the target job. The invention further provides a data warehouse dependency relationship generation device and equipment and a storage medium.

Description

TECHNICAL FIELD

[0001] The present disclosure relates to the technical field of big data, in particular to a data warehouse dependency relationship generation method and device, equipment and storage medium. BACKGROUND

[0002] At present, the data quality check of financial institutions for the regulatory reporting scene mainly targets the reporting result table. By checking the result table, problems existing in the result table can be found. However, there is a serious problem at present, that is, due to the lack of data lineage, the processing link of the job cannot be traced. For the data in the result table that does not meet the reporting requirements, the source of the problem cannot be located. Therefore, it is urgent to obtain the table-level lineage and the field-level lineage based on the complex data processing relationship of the data warehouse, so as to perform data correlation analysis from the end table to the source table and from the end field to the source field.

[0003] The structured query language (SQL) script is the key information for building data lineage. Therefore, by screening the SQL script of the data processing job in the data warehouse execution log, the dependency relationship can be accurately constructed. However, in the prior art, only the execution server IP and the execution time period of the data processing job are relied on to screen the SQL script, which leads to low screening efficiency and wide screening range, and misjudgment is likely to occur, thereby affecting the construction of data lineage. SUMMARY

[0004] In view of the above problems, the present disclosure provides a data warehouse dependency relationship generation method and device, equipment, medium and program product for improving the generation efficiency and accuracy of the dependency relationship.

[0005] According to a first aspect of the present disclosure, a data warehouse dependency relationship generation method is provided, comprising: obtaining execution information of a target job, wherein the execution information comprises an execution server and an execution time period of the target job; scanning a port of the execution server in the execution time period to obtain port information of the execution server, wherein the port information and the target job have a one-to-one mapping relationship; in the execution time period, according to the port information and the IP address of the execution server, screening a target structured query language script corresponding to the target job from a plurality of structured query language scripts of the data warehouse; and generating a dependency relationship of the target job according to the target structured query language script.

[0006] According to an embodiment of the present disclosure, the scanning the port of the execution server in the execution time period to obtain the port information of the execution server comprises: determining a time interval of the port scanning according to a time granularity of executing the target job by the data warehouse; and scanning the port of the execution server every time interval in the execution time period to obtain the port information of the execution server.

[0007] According to an embodiment of the present disclosure, the screening the target structured query language script corresponding to the target job from the plurality of structured query language scripts of the data warehouse according to the port information and the IP address of the execution server in the execution time period comprises: screening, according to the execution server, a structured query language script consistent with the IP address of the execution server from a structured query language execution log of the data warehouse as a first candidate script; screening, according to the execution time period, a structured query language script with a time stamp located in an interval of the execution time period from the first candidate script as a second candidate script; and screening, according to the port information, a structured query language script consistent with the port information from the second candidate script to obtain the target structured query language script corresponding to the target job.

[0008] According to an embodiment of the present disclosure, the screening the target structured query language script corresponding to the target job from the plurality of structured query language scripts of the data warehouse according to the port information and the IP address of the execution server in the execution time period further comprises: detecting whether there is an invalid structured query language script in the second candidate script, wherein the invalid structured query language script comprises a heartbeat structured query language script and a ping structured query language script; and in response to the existence of the invalid structured query language script in the second candidate script, filtering out the invalid structured query language script to obtain the target structured query language script corresponding to the target job.

[0009] According to an embodiment of the present disclosure, the generating the dependency relationship of the target job according to the target structured query language script comprises: parsing the target structured query language script to extract a table-level association relationship and a field-level association relationship, wherein the table-level association relationship represents an association relationship between a source table and a terminal table in a data processing process of the target job, and the field-level association relationship represents an association relationship between a source field and a terminal field in the data processing process of the target job; and constructing the dependency relationship of the target job according to the association relationship.

[0010] According to an embodiment of the present disclosure, the method further comprises: storing the dependency relationship to a dependency relationship database; and in response to a user initiating a query request, displaying the dependency relationship corresponding to the query request to the user from the dependency relationship database.

[0011] According to an embodiment of the present disclosure, in response to the user initiating the query request, the corresponding dependency relationship is displayed for the user from the dependency relationship database, comprising: in response to the user initiating the query request, displaying the dependency relationship corresponding to the query request for the user from the dependency relationship database by using the configured visualization tool.

[0012] A second aspect of the present disclosure provides a data warehouse dependency generation apparatus, comprising: an acquisition module configured to acquire execution information of a target job, wherein the execution information comprises an execution server and an execution time period of the target job; a scanning module configured to scan a port of the execution server in the execution time period to obtain port information of the execution server, wherein the port information and the target job have a one-to-one mapping relationship; a screening module configured to screen a target structured query language script corresponding to the target job from a plurality of structured query language scripts of a data warehouse according to the port information and an IP address of the execution server in the execution time period; and a generation module configured to generate a dependency relationship of the target job according to the target structured query language script.

[0013] A third aspect of the present disclosure provides an electronic device, comprising: one or more processors; a memory configured to store one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the method.

[0014] A fourth aspect of the present disclosure further provides a computer-readable storage medium having a computer program or instructions stored thereon, wherein the computer program or instructions are executed by a processor to implement the steps of the method.

[0015] A fifth aspect of the present disclosure further provides a computer program product comprising a computer program or instructions, wherein the computer program or instructions are executed by a processor to implement the steps of the method. BRIEF DESCRIPTION OF DRAWINGS

[0016] The above and other objects, features and advantages of the present disclosure will become more apparent from the following description of embodiments of the present disclosure, taken in conjunction with the accompanying drawings, in which:

[0017] Figure 1 An application scenario diagram of a data warehouse dependency generation method, apparatus, device and storage medium according to an embodiment of the present disclosure is schematically shown;

[0018] Figure 2 A flowchart of a data warehouse dependency generation method according to an embodiment of the present disclosure is schematically shown;

[0019] Figure 3 A principle diagram of a data warehouse dependency generation method according to an embodiment of the present disclosure is schematically shown;

[0020] Figure 4 A schematic diagram illustrating a principle of a structured query language script screening mechanism according to an embodiment of the present disclosure is shown;

[0021] Figure 5 A schematic block diagram of a data warehouse dependency generation apparatus according to an embodiment of the present disclosure is shown; and

[0022] Figure 6 A block diagram of an electronic device suitable for implementing a data warehouse dependency generation method according to an embodiment of the present disclosure is shown. DETAILED DESCRIPTION

[0023] Hereinafter, embodiments of the present disclosure will be described with reference to the accompanying drawings. It is to be understood, however, that the description is merely illustrative of the present disclosure and in no way limits the scope of the present disclosure. In the following detailed description of the embodiments of the present disclosure, numerous specific details are set forth in order to provide a thorough understanding of the present disclosure. However, it will be apparent to one skilled in the art that the present disclosure can be practiced without these specific details. In other instances, well-known structures and functions have been not described in detail in order not to unnecessarily obscure aspects of the present disclosure.

[0024] The terms used herein are merely used to describe specific embodiments and are not intended to limit the present disclosure. The terms "include" and "have" and the like used herein indicate the presence of the described features, steps, operations, and / or components but do not preclude the presence or addition of one or more other features, steps, operations, or components.

[0025] All terms used herein, including technical and scientific terms, have the same meanings as those generally understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having meanings consistent with the context of the present description, and should not be interpreted in an idealized or overly formal way.

[0026] In the case of using expressions similar to "at least one of A, B, and C, etc.", it is generally to be interpreted as including one or more of the same unless otherwise specified. For example, "a system having at least one of A, B, and C" should be interpreted as including a system having A alone, a system having B alone, a system having C alone, a system having A and B together, a system having A and C together, a system having B and C together, and / or a system having A, B, and C together, etc.

[0027] It should be noted that the data warehouse dependency generation method and apparatus of the present disclosure can be applied to the application of the financial field in the generation of data warehouse dependency, and can also be applied to the application of any field other than the financial field in the generation of data warehouse dependency. The application field of the data warehouse dependency generation method and apparatus of the present disclosure is not limited.

[0028] It should be noted that in the embodiments of the present disclosure, some software, components, models and the like in the prior art may be mentioned, which should be considered as exemplary, and the purpose is only to illustrate the feasibility of the implementation of the technical solutions of the present disclosure, but does not mean that the applicant has or will necessarily use the scheme.

[0029] In the technical solutions of the present disclosure, the user information (including but not limited to user personal information, user image information, user device information such as location information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved are all information and data authorized by the user or authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of related data comply with relevant laws, regulations and standards, necessary measures are taken, do not violate public order and good customs, and provide corresponding operation portal for user to choose authorization or refusal.

[0030] In the scenario of using personal information for automated decision-making, the method, device and system provided by the embodiments of the present disclosure all provide corresponding operation portal for the user to choose to agree or refuse the automated decision-making result; if the user chooses to refuse, the expert decision-making process is entered. The expression "automated decision-making" here refers to the activity of automatically analyzing, evaluating the behavior habits, interests and hobbies or economic, health, credit status of individuals through computer programs, and making decisions. The expression "expert decision-making" here refers to the activity of making decisions by personnel who are engaged in a certain field of work, have special experience, knowledge and skills, and reach a certain professional level.

[0031] The embodiment of the present disclosure provides a data warehouse dependency relationship generation method, comprising: obtaining execution information of a target job, wherein the execution information comprises an execution server and an execution time period of the target job; scanning a port of the execution server in the execution time period to obtain port information of the execution server, wherein the port information and the target job have a one-to-one mapping relationship; in the execution time period, according to the port information and the IP address of the execution server, filtering a target structured query language script corresponding to the target job from a plurality of structured query language scripts of the data warehouse; and generating a dependency relationship of the target job according to the target structured query language script.

[0032] Figure 1 The application scenario diagram of the data warehouse dependency relationship generation method according to the embodiment of the present disclosure is schematically shown.

[0033] As Figure 1As shown, the application scenario 100 according to this embodiment can include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104, and a server 105. The network 104 is a medium for providing a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103, and the server 105. The network 104 can include various connection types, such as wired, wireless communication links, or fiber optic cables, and the like.

[0034] A user can use the first terminal device 101, the second terminal device 102, the third terminal device 103 to interact with the server 105 through the network 104 to receive or send messages, and the like. Various communication client applications can be installed on the first terminal device 101, the second terminal device 102, the third terminal device 103, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, and the like (only as examples).

[0035] The first terminal device 101, the second terminal device 102, the third terminal device 103 can be various electronic devices with a display screen and supporting web browsing, including but not limited to smartphones, tablet computers, laptop computers, desktop computers, and the like.

[0036] The server 105 can be a server providing various services, such as a background management server providing support for a website browsed by a user using the first terminal device 101, the second terminal device 102, the third terminal device 103 (only as an example). The background management server can analyze and process received user requests and the like, and feed back the processing results (such as web pages, information, or data, and the like obtained or generated according to user requests) to the terminal device.

[0037] It should be noted that the data warehouse dependency relationship generation method provided by the embodiments of the present disclosure can generally be executed by the server 105. Correspondingly, the data warehouse dependency relationship generation apparatus provided by the embodiments of the present disclosure can generally be arranged in the server 105. The data warehouse dependency relationship generation method provided by the embodiments of the present disclosure can also be executed by a server or a server cluster different from the server 105 and capable of communicating with the first terminal device 101, the second terminal device 102, the third terminal device 103, and / or the server 105. Correspondingly, the data warehouse dependency relationship generation apparatus provided by the embodiments of the present disclosure can also be arranged in a server or a server cluster different from the server 105 and capable of communicating with the first terminal device 101, the second terminal device 102, the third terminal device 103, and / or the server 105.

[0038] It should be understood that, Figure 1The number of terminal devices, networks and servers in the figure is only illustrative. According to the implementation needs, there can be any number of terminal devices, networks and servers.

[0039] The following will be based on Figure 1 The described scenario, by Figures 2-4 The data warehouse dependency generation method of the embodiment of the disclosure is described in detail.

[0040] Figure 2 The flowchart of the data warehouse dependency generation method according to the embodiment of the disclosure is schematically shown.

[0041] As Figure 2 The data warehouse dependency generation method of the embodiment includes operations S210-S240, which can be executed by a server.

[0042] In operation S210, the execution information of the target job is obtained, wherein the execution information includes the execution server and the execution time period of the target job.

[0043] In operation S220, the ports of the execution server are scanned within the execution time period to obtain port information of the execution server, wherein the port information and the target job have a one-to-one mapping relationship.

[0044] In operation S230, within the execution time period, according to the port information and the IP address of the execution server, the target structured query language script corresponding to the target job is filtered from the plurality of structured query language scripts of the data warehouse.

[0045] In operation S240, the dependency relationship of the target job is generated according to the target structured query language script.

[0046] Dependency relationship refers to the relationship between tables and the relationship between fields. Generally speaking, data processing is mainly completed through SQL scripts, so as long as the available SQL scripts are accurately found, the data lineage can be accurately constructed.

[0047] SQL scripts are usually stored in SQL execution logs of the data warehouse. However, in the prior art, only the execution server IP and the execution time period of the data processing job are relied on to filter the SQL scripts, resulting in low filtering efficiency, and the filtering range is too wide, which is easy to misjudge, and further affects the construction of data lineage. Therefore, the target of the embodiment of the disclosure is to accurately filter the target SQL script corresponding to the target job from the plurality of SQL scripts in the SQL execution log of the data warehouse to construct the dependency relationship, specifically:

[0048] First, the execution information of the target job is acquired. The execution information of the target job generally includes an execution server and an execution time period of the target job, the execution server indicating that the target job is currently executed on the server, each execution server having a specific execution server IP. The execution time period indicates the execution time range of the target job, and is generally represented by the interval between the start time and the end time.

[0049] Then, the port of the execution server is scanned in the execution time period to determine the port information of the current execution server. By scanning the port, the mapping relationship between the port information and the target job can be recorded.

[0050] When a data processing job is triggered to be executed, the program code thereof runs on the execution server, and the execution server generates and sends SQL queries, data insertion, update or deletion instructions to the data warehouse according to the logic of the target job. After receiving the SQL instructions from the execution server, the data warehouse uses its powerful computing capability to internally perform actual reading, calculation, conversion and writing of data.

[0051] In Transmission Control Protocol / Internet Protocol (TCP / IP) communication, when an application program (such as a process running on the execution server) needs to connect to another application program (such as a data warehouse), a "connection" is established, and the port scanning can record this "connection", that is, record the connection relationship between the local port (also known as the source port, which is the unique identifier of the target job on the execution server) of the execution server and the target port of the data warehouse, that is, the mapping relationship mentioned above.

[0052] At this time, by using the IP address of the execution server, the execution time period and the scanned port information, the SQL script corresponding to the target job can be filtered from the SQL execution log of the data warehouse, and then the SQL script is parsed to obtain the relationship between tables and the relationship between fields, and finally the dependency relationship of the target job is generated.

[0053] According to an embodiment of the present disclosure, the port information corresponding to the current target job is obtained by scanning the port of the execution server within the execution time period of the target job, so as to accurately screen the SQL script, and then the dependency relationship is constructed. Since the port scanning records the mapping relationship between the port information and the target job, according to the port information, the SQL script corresponding to the target job can be quickly and accurately locked, the accuracy and efficiency of screening the SQL script are greatly improved, the misjudgment problem in screening only by the execution server IP and the execution time period is avoided, and thus the overall efficiency and accuracy of data bloodline construction are improved.

[0054] In the embodiment of the present disclosure, a port scanning script can be pre-configured to automatically scan the port information.

[0055] For example, first, the necessary library can be installed to detect whether the port of the execution server is open, then the port scanning script is written by using a script language, and relevant configurations are added, for example, the IP address of the server and the port list to be scanned can be configured.

[0056] If multiple servers need to be scanned, parallel port scanning can be used to improve efficiency, and the scanning results can be output to a log file for subsequent analysis, or the scanning results can be sent to a specific monitoring system or database.

[0057] According to an embodiment of the present disclosure, by pre-configuring the port scanning script, the automatic port scanning process can be realized, and manual intervention is avoided. The automatic script can be triggered according to the predetermined rules in a timely manner or on demand, and the scanning task can be automatically executed, which greatly reduces the errors and omissions of human operation.

[0058] In the embodiment of the present disclosure, operation S220 scans the port of the execution server within the execution time period to obtain the port information of the execution server, for example, which can include:

[0059] According to the time granularity of executing the target job of the data warehouse, the time interval of port scanning is determined; within the execution time period, the port of the execution server is scanned every time interval to obtain the port information of the execution server.

[0060] In the embodiment of the present disclosure, the scanning time interval is set to perform the scanning task within the specified time period.

[0061] For example, the time interval of port scanning can be determined according to the time granularity of executing the target job of the data warehouse (such as second level, minute level, hour level, day level, week level or month level, etc.).

[0062] Among them, the second reflects the execution time only needs several seconds to tens of seconds, and is often used for simple queries, small table operations, and lightweight data extraction, transformation and loading (Extract-Transform-Load, ETL) tasks, etc.

[0063] The minute level reflects the execution time in several minutes to tens of minutes, and is often used for medium complexity ETL tasks, summary calculations, and partition-level data processing, etc.

[0064] The hour level reflects the execution time of several tens of minutes to several hours, and is often used for large-scale batch processing jobs, complex SQL analysis across multiple tables, and daily offline processing tasks of data warehouse, etc.

[0065] The day level reflects the job execution time of several hours to more than one day, and is often used for full data migration or reconstruction, large report generation, and offline training data preparation, etc.

[0066] Week / month level is relatively rare, but may occur in full calculation or historical archiving scenarios, reflecting that it takes several days or even longer to execute a job once, and is often used for monthly settlement, full library-level archiving and historical backtracking, etc.

[0067] In the embodiment of the present disclosure, considering that the data warehouse job execution time is at least the minute level, the time interval of the port scanning is set to 15 seconds.

[0068] According to the embodiment of the present disclosure, by further specifying the time interval of the port scanning, the accuracy and efficiency of the scanning are guaranteed, and the system resources are saved.

[0069] In the embodiment of the present disclosure, the operation S230 filters out the target structured query language script corresponding to the target job from the plurality of structured query language scripts of the data warehouse according to the port information and the IP address of the execution server within the execution time period, which may include, for example:

[0070] According to the IP address of the execution server, the structured query language script consistent with the IP address of the execution server is filtered out from the structured query language execution log of the data warehouse as a first candidate script; according to the execution time period, the structured query language script with the timestamp located in the execution time period interval is filtered out from the first candidate script as a second candidate script; according to the port information, the structured query language script consistent with the port information is filtered out from the second candidate script to obtain the target structured query language script corresponding to the target job.

[0071] In the embodiment of the present disclosure, the SQL script is accurately filtered by triple filtering of the execution server IP, the execution time period, and the port information.

[0072] For example, assuming that the target job is executed on an execution server at IP01, all the SQL scripts corresponding to the jobs executed on the execution server at IP01 are first screened as first candidate scripts.

[0073] However, the target job is not the only job executed on the same execution server, and other jobs are also executed on the same execution server. Therefore, further screening is needed according to the execution time period to exclude the interference of other irrelevant jobs.

[0074] For example, assuming that the execution time period of the target job is 01:00-03:00, the SQL scripts corresponding to the jobs in other execution time periods can be excluded to obtain second candidate scripts.

[0075] Similarly, in the execution time period 01:00-03:00, in addition to the target job, other irrelevant jobs still exist. Therefore, the only SQL script corresponding to the target job can be screened from the second candidate scripts through the port information.

[0076] According to the embodiment of the present disclosure, the SQL scripts are screened according to the triple filtering mechanism (filtering by execution server IP → filtering by timestamp → filtering by port information), which can realize fast and accurate positioning and lay an accurate data foundation for subsequent dependency construction.

[0077] In the embodiment of the present disclosure, operation S230 screens, according to the port information and the IP address of the execution server, a target structured query language script corresponding to the target job from a plurality of structured query language scripts of the data warehouse in the execution time period. For example, operation S230 can further include:

[0078] detecting whether there is an invalid structured query language script in the second candidate scripts, wherein the invalid structured query language script includes a heartbeat structured query language script and a live structured query language script; in response to the existence of the invalid structured query language script in the second candidate scripts, filtering out the invalid structured query language script; and screening, according to the port information, a target structured query language script corresponding to the target job based on the second candidate scripts after filtering out the invalid structured query language script.

[0079] In the embodiment of the present disclosure, the invalid SQL scripts irrelevant to the business logic are filtered out before screening by using the port information, to further exclude interference.

[0080] For example, for the heartbeat SQL (used for detecting whether the connection is normal) and the live SQL (used for detecting the availability of the database), these SQLs do not involve data read-write or inter-table dependency and do not belong to the valid scripts of the target job. Therefore, the filtering processing can be performed.

[0081] According to an embodiment of the present disclosure, some SQL scripts appear under the same port, but are irrelevant to the business logic, such as heartbeat SQL and ping SQL, and the like, and the invalid SQL scripts are removed, so that false dependencies or invalid nodes in the dependency relationship can be avoided.

[0082] In the embodiment of the present disclosure, the operation S240 generates the dependency relationship of the target job according to the target structured query language script, and the dependency relationship may, for example, include:

[0083] The target structured query language script is parsed to extract a table-level association relationship and a field-level association relationship, and the dependency relationship of the target job is constructed according to the association relationship.

[0084] In the embodiment of the present disclosure, the dependency relationship is generated by parsing the table-level association relationship and the field-level association relationship in the target structured query language script.

[0085] The table-level association relationship represents an association relationship between a source table and a terminal table in a data processing process of the target job, and the field-level association relationship represents an association relationship between a source field and a terminal field in the data processing process of the target job.

[0086] According to an embodiment of the present disclosure, after the accurate SQL scripts are screened out, the table-level association relationship and the field-level association relationship can be parsed to directly construct the dependency relationship, which is simple and fast.

[0087] In the embodiment of the present disclosure, the data warehouse dependency relationship generation method may, for example, further include:

[0088] The dependency relationship is stored in a dependency relationship database, and in response to a query request initiated by a user, the dependency relationship corresponding to the query request is displayed to the user from the dependency relationship database.

[0089] In the embodiment of the present disclosure, the generated dependency relationship is stored in a database in a timely manner, which is convenient for subsequent use.

[0090] For example, the generated dependency relationship can be specially stored in a corresponding database for storage. Once the dependency relationship is generated, if it is not stored, the dependency relationship needs to be parsed and reconstructed from the SQL execution log every time the query or analysis is performed, which consumes time and resources. After the dependency relationship is stored in the database, the blood relationship information can be reused, and the efficiency is greatly improved.

[0091] According to an embodiment of the present disclosure, the storage and subsequent use function of the dependency relationship is further expanded, so that the dependency relationship constructed can be stored, queried and managed for a long time.

[0092] In the embodiment of the present disclosure, in response to a query request initiated by a user, the corresponding dependency relationship is displayed to the user from the dependency relationship database, and the display may, for example, include:

[0093] In response to a user initiating a query request, a configured visualization tool is utilized to present the user with dependencies corresponding to the query request from the dependency database.

[0094] In embodiments of the present disclosure, a configured visualization tool is utilized to present the dependencies.

[0095] For example, a visualization tool can be pre-configured to help users understand data flow and dependencies.

[0096] According to embodiments of the present disclosure, the method is enhanced in practicality by further refining the requirements for visualization presentation, so that the generated dependencies can be directly converted into an operational visualization data flow diagram.

[0097] Figure 3 A schematic diagram of the principle of the data warehouse dependency generation method according to embodiments of the present disclosure is shown.

[0098] As shown in the figure, the principle of the data warehouse dependency generation method of this embodiment is as follows: Figure 3 Data processing jobs executed in the data warehouse are executed through a job scheduling platform. The job scheduling platform flexibly configures data processing jobs by configuring dependencies between jobs, time points for job execution, frequency of job execution, and failure retransmission strategies.

[0099] Data processing jobs (such as Job A, Job B, Job C, Job D, etc.) represent specific jobs executed through the job scheduling platform, which collectively complete the processing of data from the source to the destination. Each job is executed on a different server, and each server will have multiple unrelated jobs executed at the same time. For example, Job A is executed on server 1, but there will be other jobs executed on server 1 at the same time in addition to Job A. During job execution, specific SQL scripts are sent to the data warehouse for execution, and SQL is the key information for forming data lineage.

[0100] In embodiments of the present disclosure, the data warehouse can use a massively parallel processing data warehouse (MPP) for example. The massively parallel processing data warehouse is a computer architecture based on distributed computing, which achieves high-performance processing through the cooperative work of a large number of homogeneous processing units. Each node has independent storage and communicates through message passing. The core concept is to divide tasks into multiple nodes for parallel execution and then aggregate the results. It has modular expansion capability and supports system expansion by adding nodes.

[0101]

[0102] ​SQL execution logs can be collected through the execution logs of the MPP data warehouse, and by filtering and analyzing the execution logs of the MPP data warehouse, valid SQL scripts can be obtained, and by analyzing the SQL scripts, the relationships between tables and the relationships between fields can be obtained, and then the data dependency relationship can be generated. As shown in Figure 2 , the source table represented by table A and the final result table represented by table D are shown. When the result table reported to the regulatory agency has a data quality problem in field D4, it is expected to find the relationship between table D and table A, and the relationship between field D4 and field A1, A4 through data bloodline.

[0103] The key of the data warehouse dependency relationship generation method of the embodiments of the present disclosure is to find the mapping relationship between the job and the specific execution SQL, and to provide accurate data input for data bloodline construction. Therefore, the data warehouse dependency relationship generation method of the embodiments of the present disclosure focuses on the design of the SQL script filtering mechanism, as shown in Figure 4 .

[0104] Figure 4 The principle diagram of the structured query language script filtering mechanism according to the embodiments of the present disclosure is schematically shown.

[0105] As shown in Figure 4 , the principle of the structured query language script filtering mechanism of this embodiment is as follows:

[0106] First, the target job JobA is executed on the execution server at IP01, and the specific SQL script is sent to the MPP data warehouse for execution at port A to form SQLA, which is required for constructing data bloodline. At the same time, other irrelevant jobs are also executed on the IP01 execution server, such as other job JobOther1 and other job JobOther2. These jobs will also send SQL scripts to the MPP data warehouse for execution to form other SQL scripts, such as SQLOther1 and SQLOther2, which are invalid interference SQL scripts.

[0107] For SQLA, SQLOther1 and SQLOther2 generated in the above process, the structured query language script filtering mechanism of the embodiments of the present disclosure needs to accurately filter out SQLA from them.

[0108] The SQL script executed by the MPP data warehouse usually has the characteristics shown in Table 1 as follows:

[0109] Table 1

[0110]

[0111] For example, since the target job JobA is executed on the execution server with IP address IP01, other jobs such as JobOther1 and JobOther2 are also executed in the same execution time period, the SQL scripts filtered out from the SQL execution log of the data warehouse only have the execution server IP and the execution time period, thus, when filtered according to the execution server IP and the execution time period of the target job JobA (such as IP01+01:00-03:00), other invalid SQL scripts are also included, which finally leads to inaccurate blood relationship.

[0112] Therefore, the structured query language script filtering mechanism of the embodiment of the present disclosure considers that each specific job process has an execution port, the port information called by the target job JobA is obtained, and then the corresponding SQL script can be accurately found by using the execution server IP of the target job JobA + execution time + port information, and then the dependency relationship is generated.

[0113] Based on the above data warehouse dependency relationship generation method, the present disclosure further provides a data warehouse dependency relationship generation apparatus. The following will be described in detail Figure 5 with reference to the apparatus.

[0114] Figure 5 The structural block diagram of the data warehouse dependency relationship generation apparatus according to the embodiment of the present disclosure is schematically shown.

[0115] As Figure 5 shown, the data warehouse dependency relationship generation apparatus 500 of the embodiment includes an acquisition module 510, a scanning module 520, a filtering module 530, and a generation module 540.

[0116] The acquisition module 510 is configured to acquire execution information of a target job, wherein the execution information includes an execution server and an execution time period of the target job. In an embodiment, the acquisition module 510 can be configured to perform the operation S210 described in the foregoing, and details are not described herein again.

[0117] The scanning module 520 is configured to scan ports of the execution server in the execution time period to obtain port information of the execution server, wherein the port information and the target job have a one-to-one mapping relationship. In an embodiment, the scanning module 520 can be configured to perform the operation S220 described in the foregoing, and details are not described herein again.

[0118] The filtering module 530 is configured to filter out a target structured query language script corresponding to the target job from a plurality of structured query language scripts of a data warehouse according to the port information and an IP address of the execution server in the execution time period. In an embodiment, the filtering module 530 can be configured to perform the operation S230 described in the foregoing, and details are not described herein again.

[0119] The generating module 540 is configured to generate the dependency relationship of the target job according to the target structured query language script. In an embodiment, the generating module 540 can be configured to perform the operation S240 described above, and details are not repeated here.

[0120] According to an embodiment of the present disclosure, any one or more of the acquiring module 510, the scanning module 520, the screening module 530 and the generating module 540 can be combined in one module, or any one of them can be split into multiple modules. Alternatively, at least part of the function of one or more of these modules can be combined with at least part of the function of the other modules, and implemented in one module. According to an embodiment of the present disclosure, at least one of the acquiring module 510, the scanning module 520, the screening module 530 and the generating module 540 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on chip, a system on substrate, a system on package, an application specific integrated circuit (ASIC), or any other reasonable way of hardware or firmware that can be integrated or packaged with a circuit, or any one of software, hardware and firmware or any appropriate combination of any of them. Alternatively, at least one of the acquiring module 510, the scanning module 520, the screening module 530 and the generating module 540 can be at least partially implemented as a computer program module which can perform corresponding functions when it is run.

[0121] Figure 6 A block diagram of an electronic device suitable for implementing the method of generating a data warehouse dependency relationship according to an embodiment of the present disclosure is schematically shown.

[0122] As shown in Figure 6 The electronic device 600 according to an embodiment of the present disclosure includes a processor 601 which can perform various appropriate actions and processes according to programs stored in a read only memory (ROM) 602 or loaded from a storage portion 608 to a random access memory (RAM) 603. The processor 601 can include, for example, a general purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset, and / or a special purpose microprocessor (such as an application specific integrated circuit (ASIC)), and the like. The processor 601 can also include an on-board memory for cache use. The processor 601 can include a single processing unit or multiple processing units for performing different actions of the method processes according to an embodiment of the present disclosure.

[0123] In the RAM 603, various programs and data required for the operation of the electronic device 600 are stored. The processor 601, the ROM 602, and the RAM 603 are connected to each other via the bus 604. The processor 601 performs various operations of the method flow according to the embodiments of the present disclosure by executing the programs in the ROM 602 and / or the RAM 603. It should be noted that the programs can also be stored in one or more memories other than the ROM 602 and the RAM 603. The processor 601 can also perform various operations of the method flow according to the embodiments of the present disclosure by executing the programs stored in the one or more memories.

[0124] According to an embodiment of the present disclosure, the electronic device 600 can further include an input / output (I / O) interface 605, which is also connected to the bus 604. The electronic device 600 can further include one or more of the following components connected to the input / output (I / O) interface 605: an input part 606 including a keyboard, a mouse, and the like; an output part 607 including a cathode ray tube (CRT), a liquid crystal display (LCD), and the like, and a speaker, and the like; a storage part 608 including a hard disk, and the like; and a communication part 609 including a network interface card such as a LAN card, a modem, and the like. The communication part 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the input / output (I / O) interface 605 as necessary. A removable medium 611 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, and the like is mounted on the drive 610 as necessary, so that a computer program read therefrom is installed in the storage part 608 as necessary.

[0125] The present disclosure also provides a computer readable storage medium, which can be included in the device / apparatus / system described in the above embodiments; or can exist separately without being assembled into the device / apparatus / system. The above computer readable storage medium carries one or more programs, when the one or more programs are executed, the method according to the embodiments of the present disclosure is implemented.

[0126] According to an embodiment of the present disclosure, the computer readable storage medium can be a nonvolatile computer readable storage medium, for example, can include, but is not limited to, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any appropriate combination thereof. In this disclosure, the computer readable storage medium can be any tangible medium that contains or stores a program that can be used by or in connection with an instruction execution system, apparatus, or device. For example, according to an embodiment of the present disclosure, the computer readable storage medium can include one or more memories, such as the ROM 602 and / or the RAM 603 described above, and / or one or more memory chips other than the ROM 602 and the RAM 603.

[0127] Embodiments of the present disclosure also include a computer program product that includes a computer program containing program codes for executing the methods shown in the flowcharts. When the computer program product is run in a computer system, the program codes are used to make the computer system implement the methods provided by the embodiments of the present disclosure.

[0128] The above-described functions defined in the system / device / apparatus of the embodiments of the present disclosure are performed when the computer program is executed by the processor 601. According to an embodiment of the present disclosure, the system, device, module, unit, etc. described above can be implemented by computer program modules.

[0129] In one embodiment, the computer program can rely on a tangible storage medium such as an optical storage device, a magnetic storage device, etc. In another embodiment, the computer program can also be transmitted, distributed, and downloaded in the form of a signal on a network medium and installed and executed by the communication part 609, and / or installed from the detachable medium 611. The program codes contained in the computer program can be transmitted by any appropriate network medium, including but not limited to wireless, wired, etc., or any appropriate combination thereof.

[0130] In such an embodiment, the computer program can be downloaded and installed from the network by the communication part 609, and / or installed from the detachable medium 611. When the computer program is executed by the processor 601, the above-described functions defined in the system of the embodiments of the present disclosure are performed. According to an embodiment of the present disclosure, the system, device, apparatus, module, unit, etc. described above can be implemented by computer program modules.

[0131] According to embodiments of the present disclosure, program code of the computer programs provided by the embodiments of the present disclosure can be written in any combination of one or more programming languages, and specifically, these computer programs can be implemented using a high-level procedural and / or object-oriented programming language, and / or an assembly / machine language. The programming language includes, but is not limited to, a programming language such as Java, C++, Python, "C" language, or a similar programming language. The program code can be executed entirely on a user computing device, partially on a user device, partially on a remote computing device, or entirely on a remote computing device or server. In the case involving a remote computing device, the remote computing device can be connected to the user computing device through any kind of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, connected to the Internet through an Internet service provider).

[0132] The flow diagrams and the block diagrams in the drawings are illustrations of possible architectures, functions, and operations of systems, methods, and computer program products according to various embodiments of the present disclosure. In this regard, each block in the flow diagrams or block diagrams can represent a module, a segment, or a portion of code, which comprises one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and / or flow diagrams, and combinations thereof, can be implemented by special purpose hardware-based systems that perform the specified functions or operations, or combinations of special purpose hardware and computer instructions.

[0133] Those skilled in the art can understand that the features described in various embodiments of the present disclosure can be combined and / or integrated in various combinations, even if such combinations are not explicitly described in the present disclosure. In particular, the features described in various embodiments of the present disclosure can be combined and / or integrated in various combinations without departing from the spirit and teachings of the present disclosure. All such combinations and / or integrations are within the scope of the present disclosure.

[0134] The embodiments of the present disclosure are described above. However, these embodiments are merely for illustrative purposes, and are not intended to limit the scope of the present disclosure. Although each embodiment is described above separately, this does not mean that the measures in each embodiment cannot be used advantageously in combination. Those skilled in the art can make various substitutions and modifications without departing from the scope of the present disclosure, and these substitutions and modifications should all fall within the scope of the present disclosure.

Claims

1. A method for generating data warehouse dependencies, characterized in that, The method includes: Obtain the execution information of the target job, wherein the execution information includes the execution server and execution time period of the target job; During the execution time period, the ports of the execution server are scanned to obtain the port information of the execution server, wherein the port information and the target job have a one-to-one mapping relationship; During the execution time period, based on the port information and the IP address of the execution server, a target structured query language script corresponding to the target job is selected from multiple structured query language scripts in the data warehouse. The dependencies of the target job are generated based on the target structured query language script.

2. The method according to claim 1, characterized in that, The step of scanning the ports of the execution server during the execution time period to obtain the port information of the execution server includes: The time interval for port scanning is determined based on the time granularity of the target job executed in the data warehouse. During the execution time period, the port of the execution server is scanned at regular intervals to obtain the port information of the execution server.

3. The method according to claim 1, characterized in that, The step of selecting the target structured query language script corresponding to the target job from multiple structured query language scripts in the data warehouse during the execution time period, based on the port information and the IP address of the execution server, includes: Based on the IP address of the execution server, select the structured query language scripts that match the IP address of the execution server from the structured query language execution logs of the data warehouse, and use them as the first candidate scripts; Based on the execution time period, structured query language scripts with timestamps located within the execution time period are selected from the first candidate scripts and used as the second candidate scripts; Based on the port information, a structured query language script that matches the port information is selected from the second candidate scripts to obtain the target structured query language script corresponding to the target job.

4. The method according to claim 3, characterized in that, The step of selecting the target structured query language script corresponding to the target job from multiple structured query language scripts in the data warehouse during the execution time period, based on the port information and the IP address of the execution server, further includes: Detect whether there is an invalid structured query language script in the second candidate script, wherein the invalid structured query language script includes a heartbeat structured query language script and a liveness detection structured query language script; In response to the presence of an invalid structured query language script in the second candidate script, the invalid structured query language script is filtered out. Based on the second candidate script after filtering out invalid structured query language scripts, the target structured query language script corresponding to the target job is selected according to the port information.

5. The method according to claim 1, characterized in that, The step of generating the dependency relationship of the target job based on the target structured query language script includes: The target structured query language script is parsed to extract table-level relationships and field-level relationships. The table-level relationships represent the relationships between the source table and the end table during the data processing of the target job, and the field-level relationships represent the relationships between the source field and the end field during the data processing of the target job. Based on the aforementioned relationships, the dependencies of the target job are constructed.

6. The method according to claim 1, characterized in that, The method further includes: Store the dependencies in a dependency database; In response to a user's query request, the system displays the dependencies corresponding to the query request from the dependency database.

7. The method according to claim 6, characterized in that, The step of responding to a user's query request and displaying the corresponding dependency relationship to the user from the dependency relationship database includes: In response to a user's query request, the system uses a configured visualization tool to display the dependencies corresponding to the query request from the dependency database.

8. A data warehouse dependency generation device, characterized in that, The device includes: The acquisition module is used to acquire the execution information of the target job, wherein the execution information includes the execution server and execution time period of the target job; The scanning module is used to scan the ports of the execution server during the execution time period to obtain the port information of the execution server, wherein the port information and the target job have a one-to-one mapping relationship; The filtering module is used to filter out the target structured query language script corresponding to the target job from multiple structured query language scripts in the data warehouse within the execution time period, based on the port information and the IP address of the execution server. The generation module is used to generate the dependencies of the target job based on the target structured query language script.

9. An electronic device, comprising: One or more processors; Memory, used to store one or more computer programs. The characteristic feature is that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program or instructions stored thereon, characterized in that, When the computer program or instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 7.

11. A computer program product, comprising a computer program or instructions, characterized in that, When the computer program or instructions are executed by a processor, they implement the steps of the method according to any one of claims 1 to 7.

Images