Method, device and equipment for generating blood relationship expression SQL (structured query language) and medium

Abstract

The invention discloses a blood relationship expression SQL generation method and device, equipment and a medium. The method comprises the steps that blood relationship storage information of a target data source is acquired; the target data source comprises first data, and the first data comprises at least one data field; configuration information is extracted and obtained based on the blood relationship storage information; wherein the configuration information represents blood relationship strategy configuration for processing the first data into the second data; the configuration information comprises a data storage target, a data filtering rule, a data conversion rule and a data flow direction; and obtaining an SQL of data conversion from the target data source to the data storage target according to the target data source and the configuration information. According to the embodiment of the invention, the processing flow of the log data can be simplified, the traceability of data consanguinity is improved, the requirement of manual intervention is reduced, the overall efficiency and accuracy of data processing are improved, and the method can be widely applied to the technical field of data processing.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device, equipment and medium for generating SQL for blood relationship expression. Background Art

[0002] With the development of information technology, enterprises are facing increasing demands for data management and analysis. Among the many types of data sources, log data is particularly important because it records key information such as system operation status and user behavior. Log data usually contains rich business information that needs to be effectively collected, parsed, and utilized. Among them, a common application scenario is to extract specific information from log data and store it in different downstream data tables or components for further processing and analysis.

[0003] Data lineage refers to the historical record of all the steps and transformations that data undergoes from generation to consumption. It is essential for tracing the source of data, understanding the reasons for data changes, and maintaining data quality. Especially when processing log data, data lineage can help data analysts and engineers better understand the flow path of data and the business logic behind it, thereby ensuring the accuracy and reliability of data processing.

[0004] However, existing technical means are often manually configured by engineers, specifically relying on manually writing SQL queries to define data extraction logic. This method is not only time-consuming and inefficient, but also prone to errors in manual configuration. Summary of the invention

[0005] The present invention proposes a method, device, equipment and medium for generating SQL of blood relationship expression, aiming to solve one of the technical problems in the related art at least to a certain extent. The embodiments of the present invention can efficiently and accurately generate SQL of blood relationship expression.

[0006] On the one hand, an embodiment of the present invention provides a method for generating SQL for lineage expression, comprising:

[0007] Acquire the lineage storage information of the target data source; the target data source includes first data, and the first data includes at least one data field;

[0008] Configuration information is obtained based on the extraction of bloodline storage information;

[0009] The configuration information represents the blood relationship strategy configuration for processing the first data into the second data; the configuration information includes data storage targets, data filtering rules, data conversion rules and data flow directions;

[0010] Get the SQL for data conversion from the target data source to the data storage target according to the target data source and configuration information.

[0011] Optionally, the first segment of the lineage storage information includes identification information of the target data source; and obtaining the configuration information based on the lineage storage information extraction includes the following steps:

[0012] Determining a data storage target based on the second segment of the lineage storage information;

[0013] Determine data filtering rules based on the third segment of the blood relationship storage information;

[0014] The fourth segment based on the lineage storage information determines the data conversion rules and data flow of the data field.

[0015] Optionally, determining a data storage target based on the second segment of the lineage storage information includes the following steps:

[0016] Extract the target database name and the target resource name from the second segment, and then determine the target database and the target resource;

[0017] The data storage target of the second data is determined according to the location of the target resource in the target database.

[0018] Optionally, determining a data filtering rule based on the third segment of the blood relationship storage information includes the following steps:

[0019] Extracting conditional operators and logical operators for the target data field from the third segment;

[0020] Based on the conditional operators and logical operators, conditional operations and logical operations for the target data field are determined, thereby determining data filtering rules.

[0021] Optionally, determining the data conversion rule and data flow direction of the data field based on the fourth segment of the lineage storage information includes the following steps:

[0022] Extracting from the fourth segment the source field of the data field in the target data source, the extraction method, and the target field in the data storage target after processing based on the extraction method; the extraction methods include value copying, non-null value copying, constant filling, and dictionary transcoding;

[0023] The data conversion rules corresponding to the data fields are determined based on the extraction method, and the data flow direction corresponding to the data fields is determined based on the source fields and target fields.

[0024] Optionally, obtaining SQL for data conversion from the target data source to the data storage target according to the target data source and the configuration information includes the following steps:

[0025] Import the target data source and configuration information into the preset generation template to obtain the SQL for data conversion from the target data source to the data storage target.

[0026] Optionally, the data flow includes a source field of a data field in a target data source and a target field in a data storage target after conversion based on a data conversion rule; importing the target data source and configuration information into a preset generation template to obtain SQL for data conversion from the target data source to the data storage target, including the following steps:

[0027] Importing the target field in the data storage target after the data storage target and the data field are converted based on the corresponding data conversion rules into the first template;

[0028] Importing the source fields and / or data conversion rules corresponding to each target field into the second template in sequence;

[0029] Import the target data source into the third template; import the data filtering rules into the fourth template;

[0030] The SQL for data conversion from the target data source to the data storage target is obtained according to the first template, the second template, the third template and the fourth template.

[0031] On the other hand, an embodiment of the present invention provides a device for generating SQL for lineage expression, including:

[0032] The first module is used to obtain the blood relationship storage information of the target data source; the target data source includes first data, and the first data includes at least one data field;

[0033] The second module is used to extract and obtain configuration information based on blood relationship storage information;

[0034] The configuration information represents the blood relationship strategy configuration for processing the first data into the second data; the configuration information includes data storage targets, data filtering rules, data conversion rules and data flow directions;

[0035] The third module is used to obtain the SQL for data conversion from the target data source to the data storage target according to the target data source and configuration information.

[0036] On the other hand, an embodiment of the present invention provides an electronic device, including: a processor and a memory; the memory is used to store programs; the processor executes the program to implement the above-mentioned SQL generation method for blood relationship expression.

[0037] On the other hand, an embodiment of the present invention provides a computer storage medium, in which a program executable by a processor is stored. When the program executable by the processor is executed by the processor, it is used to implement the above-mentioned SQL generation method for blood relationship expression.

[0038] The embodiment of the present invention obtains the lineage storage information of the target data source; the target data source includes first data, and the first data includes at least one data field; configuration information is extracted based on the lineage storage information; wherein the configuration information represents the lineage strategy configuration for processing the first data into the second data; the configuration information includes data storage targets, data filtering rules, data conversion rules and data flow; and SQL for data conversion from the target data source to the data storage target is obtained according to the target data source and the configuration information. The embodiment of the present invention can simplify the processing flow of log data, improve the traceability of data lineage, and reduce the need for manual intervention, thereby improving the overall efficiency and accuracy of data processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The accompanying drawings are used to provide a further understanding of the technical solution of the present invention and constitute a part of the specification. Together with the embodiments of the present invention, they are used to explain the technical solution of the present invention and do not constitute a limitation on the technical solution of the present invention.

[0040] Figure 1 A schematic diagram of an implementation environment for generating SQL for expressing blood relationship provided by an embodiment of the present invention;

[0041] Figure 2 A flowchart of a method for generating SQL for blood relationship expression provided by an embodiment of the present invention;

[0042] Figure 3 A schematic diagram of the expansion process of step S200 provided in an embodiment of the present invention;

[0043] Figure 4 A schematic diagram of the expansion process of step S201 provided in an embodiment of the present invention;

[0044] Figure 5 A schematic diagram of the expansion process of step S202 provided in an embodiment of the present invention;

[0045] Figure 6 A schematic diagram of the expansion process of step S203 provided in an embodiment of the present invention;

[0046] Figure 7 A schematic diagram of an example of a data processing flow provided by an embodiment of the present invention;

[0047] Figure 8 A schematic diagram of an example of a program processing flow provided by an embodiment of the present invention;

[0048] Fig. 9 A schematic diagram of the structure of a SQL generation device for blood relationship expression provided by an embodiment of the present invention;

[0049] Fig.10 A schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0050] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention 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 invention and are not used to limit the present invention.

[0051] It should be noted that, although the functional modules are divided in the system schematic diagram and the logical order is shown in the flow chart, in some cases, the steps shown or described may be performed in a different order than the module division in the system or the order in the flow chart. The terms "first / S100", "second / S200", etc. in the specification, claims and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence.

[0052] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present invention. The appearance of the phrase in various places in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0053] To facilitate the understanding of the technical solution of the present invention, the technical feature terms that may appear in the embodiments of the present invention are first explained:

[0054] Data lineage: It is a subcategory of metadata management in data governance. It is the process of tracing the source of data, obtaining the data generation link, and discovering the relationship between data.

[0055] Data governance: refers to a set of policies, standards, roles, responsibilities and processes for managing enterprise data, aiming to ensure the quality, security, compliance and value of data. Data governance focuses on how to establish and maintain an effective data management system within the organization, which covers all stages of the data life cycle, including data creation, storage, use, sharing, archiving and destruction.

[0056] ETL: ETL is a key process used in data warehousing and big data processing. This process is mainly used to extract data from different data sources, clean, transform and integrate the data, and then load it into the target data warehouse or data lake for data analysis and reporting.

[0057] SQL: The full name is Structured Query Language, which is a standard programming language for managing relational databases. It is designed to process structured data and allows users to create, read, update and delete data in the database. SQL is developed and maintained by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), so it is widely used and highly compatible in different database management systems (DBMS).

[0058] Data granularity: Data granularity refers to the degree to which data is segmented or detailed in a data warehouse or database. It is an indicator of the level of data abstraction and is usually used to describe the degree of data refinement or aggregation. The higher the data granularity, the more detailed and specific the data is; conversely, the lower the data granularity, the more aggregated and abstract the data is.

[0059] JSON: A lightweight data exchange format that is easy for humans to read and write, and easy for machines to parse and generate. JSON is based on a subset of JavaScript, but as an independent data format, it is not limited to JavaScript and is widely used for data transmission and storage in a variety of programming languages ​​and environments.

[0060] UDF: User Defined Function, or UDF for short. It is a custom code module used in database management systems or data analysis software that allows users to extend the functionality of built-in functions. UDF allows developers to write application-specific functions that process data and return results. This mechanism enhances the flexibility and efficiency of data processing.

[0061] It is understandable that the SQL generation method for blood relationship expression provided in the embodiment of the present invention can be applied to any computer device with data processing and computing capabilities, and this computer device can be various types of terminals or servers. When the computer device in the embodiment is a server, the server is an independent physical server, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms. Optionally, the terminal is a smart phone, tablet computer, laptop computer, desktop computer, etc., but is not limited to this.

[0062] like Figure 1 FIG. 1 is a schematic diagram of an implementation environment provided by an embodiment of the present invention. Figure 1 The implementation environment includes at least one terminal 102 and a server 101. The terminal 102 and the server 101 can be connected to a network wirelessly or wired to complete data transmission and exchange.

[0063] Server 101 can be an independent physical server, or a server cluster or distributed system composed of multiple physical servers. It can also be a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN (Content Delivery Network), as well as big data and artificial intelligence platforms.

[0064] In addition, the server 101 can also be a node server in the blockchain network. Blockchain is a new application model of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanism, and encryption algorithm.

[0065] The terminal 102 may be a smart phone, a tablet computer, a laptop computer, a desktop computer, a smart speaker, a smart watch, etc., but is not limited thereto. The terminal 102 and the server 101 may be directly or indirectly connected via wired or wireless communication, which is not limited in the embodiment of the present invention.

[0066] Based on the example Figure 1 In the implementation environment shown, an embodiment of the present invention provides a method for generating SQL for blood relationship expression. The following is explained using the example of the method for generating SQL for blood relationship expression applied in the terminal 102. It can be understood that the method for generating SQL for blood relationship expression can also be applied in the server 101.

[0067] Reference Figure 2 , Figure 2 The flowchart of the SQL generation method for lineage expression applied to a terminal (for example, the terminal may be a DPU) provided in an embodiment of the present invention, the execution subject of the SQL generation method for lineage expression may be any of the aforementioned computer devices (including a server or a terminal). Figure 2 , the method comprises the following steps:

[0068] S100, obtaining the blood relationship storage information of the target data source;

[0069] Wherein, the target data source includes first data, and the first data includes at least one data field;

[0070] S200, extracting and obtaining configuration information based on blood relationship storage information;

[0071] The configuration information represents the blood relationship strategy configuration for processing the first data into the second data; the configuration information includes data storage targets, data filtering rules, data conversion rules and data flow directions;

[0072] It should be noted that the first segment of the blood relationship storage information includes the identification information of the target data source; in some embodiments, such as Figure 3 As shown, obtaining configuration information based on lineage storage information extraction may include the following steps: S201, determining the data storage target based on the second segment of the lineage storage information; S202, determining the data filtering rules based on the third segment of the lineage storage information; S203, determining the data conversion rules and data flow direction of the data field based on the fourth segment of the lineage storage information.

[0073] Among them, in some embodiments, such as Figure 4 As shown, determining the data storage target based on the second segment of the bloodline storage information may include the following steps: S2011, extracting the target database name and the target resource name from the second segment, and then determining the target database and the target resource; S2012, determining the data storage target of the second data according to the location of the target resource under the target database.

[0074] For example, in some specific implementations, examples of the first segment and the second segment in the lineage storage segment are as follows:

[0075] "groupId":"r01",#Rule group id

[0076] "ruleId":"r01-01",#Rule id

[0077] "resType":"Kafka",#Data source type

[0078] "resName":"topic01",#Data source name

[0079] "databaseName":"testKafka",#Database name

[0080] "targetDataSet":"ds-01",#Target dataset number

[0081] "targetDbName":"db01",#Target database name

[0082] "targetResName":"table01",#Target resource name

[0083] "targetResType":"Hive",#Target resource type

[0084] Among them, in some embodiments, such as Figure 5 As shown, determining data filtering rules based on the third segment of bloodline storage information may include the following steps: S2021, extracting conditional operators and logical operators for the target data field from the third segment; S2022, determining conditional operations and logical operations for the target data field based on the conditional operators and logical operators, and then determining data filtering rules.

[0085] For example, in some specific implementations, an example of the third segment in the lineage storage segment is as follows:

[0086]

[0087]

[0088] Among them, there are several commonly used types of conditional operators as follows (as shown in Table 1). More advanced types can be customized and are also implemented using UDF in the subsequent implementation process.

[0089] Table 1

[0090]

[0091]

[0092] Specifically, the identification of data filtering rules, in the filter, identifies that the filtering rules of the ETL include two parts, the first is that the f001 field is not empty, and the logical operator is OR; the second is that the f002 field is not empty. Then the corresponding generated data filtering rule example can be: f001 IS NOT NULL OR f002 IS NOT NULL;

[0093] Among them, in some embodiments, such as Figure 6 As shown, determining the data conversion rules and data flow direction of the data field based on the fourth segment of the bloodline storage information may include the following steps: S2031, extracting the source field of the data field in the target data source, the extraction method, and the target field in the data storage target after processing based on the extraction method from the fourth segment; the extraction methods include value copying, non-empty value copying, constant filling, and dictionary transcoding; S2032, determining the data conversion rules corresponding to the data field based on the extraction method, and determining the data flow direction corresponding to the data field based on the source field and the target field.

[0094] Among them, it should be noted that the data flow direction is determined based on the target field. In some specific implementations, the data flow direction may not have a corresponding source field, and only a target field exists. For example, if a constant of a target field is directly filled, there is no need to use the data field of the source field as the data basis to implement the corresponding data conversion, and naturally there is no corresponding source field.

[0095] For example, in some specific implementations, an example of the fourth segment in the lineage storage segment is as follows:

[0096]

[0097]

[0098] There are several types of extraction methods (as shown in Table 2). If a more advanced type is required, it can be customized and implemented using UDF in the subsequent implementation process.

[0099] Table 2

[0100]

[0101] Specifically, the definition and identification of data conversion rules mainly involves reading the mapping rules of multiple fields under the extractFields attribute. According to the ETL process involving 4 fields shown in the example of the fourth segment, the specific implementation is as follows:

[0102] 1. Perform dictionary mapping on the f003 field in topic01 according to the preset map01 dictionary, and fill the mapped value into the f004 field in the target table table01.

[0103] 2. Perform a non-null value copy operation on the f005 and f006 fields in topic01, and copy the non-null values ​​in f005 and f006 to the f007 field in the target table table01.

[0104] 3. Perform a value copy operation on the f008 field in topic01, and copy the data in f008 to the f009 field in the target table table01.

[0105] 4. Fill the f010 field in the target table table01 with the preset constant "hello world".

[0106] S300: Obtain SQL for data conversion from the target data source to the data storage target according to the target data source and configuration information.

[0107] It should be noted that, in some embodiments, obtaining the SQL for data conversion from the target data source to the data storage target based on the target data source and configuration information may include the following steps: importing the target data source and configuration information into a preset generation template to obtain the SQL for data conversion from the target data source to the data storage target.

[0108] Among them, in some embodiments, the data flow includes the source field of the data field in the target data source and the target field in the data storage target after conversion based on the data conversion rules; importing the target data source and configuration information into a preset generation template to obtain the SQL for data conversion from the target data source to the data storage target may include the following steps: importing the data storage target and the target field in the data storage target after conversion based on its corresponding data conversion rules into the first template; importing the source field and / or data conversion rules corresponding to each target field into the second template in sequence; importing the target data source into the third template; importing the data filtering rules into the fourth template; and obtaining the SQL for data conversion from the target data source to the data storage target according to the first template, the second template, the third template and the fourth template.

[0109] For example, in some specific implementations, examples of SQL results generated based on the aforementioned segments of the lineage storage information may be as follows:

[0110] INSERT INTO db01.table01(f004,f007,f009,f010)#First template

[0111] SELECT#Second template

[0112] udf_dicmap('map01',f003),#corresponding to the target field f004

[0113] coalesce(f005,f006),#corresponding to target field f007

[0114] f008,#corresponding to the target field f009

[0115] 'hello world'#corresponding to the target field f010

[0116] FROM topic01#The third template

[0117] WHERE(f001 IS NOT NULL OR f002 IS NOT NULL)#Fourth template

[0118] It can then be executed on a variety of components that support the SQL protocol. It should be noted that user-defined functions must be mounted before execution.

[0119] In order to explain the principle of the technical solution of the present invention in detail, the overall process of the present invention is described below in combination with some specific embodiments. It is easy to understand that the following is an explanation of the technical principle of the present invention and cannot be regarded as a limitation of the present invention.

[0120] First of all, it should be noted that at present, in the process of processing log data, especially when extracting data from raw log files to downstream data tables or components, there are often the following challenges: 1. Lack of lineage information. Traditional data processing processes may not fully consider the storage and management of data lineage, making it difficult to track the source and flow path of the data. 2. Limitations of manual configuration. Existing solutions usually rely on manually written SQL queries or other scripts to define the data extraction logic, which is not only time-consuming but also prone to errors. 3. Lack of automated tools. There is a lack of mature automated tools on the market to support the lineage management of log data and the automatic generation of SQL queries, which increases the workload of data engineers.

[0121] In order to overcome the above challenges, the present invention proposes a method for automatically generating a lineage expression based on log detail data and corresponding SQL. The method aims to simplify the processing flow of log data, improve the traceability of data lineage, and reduce the need for manual intervention, thereby improving the overall efficiency and accuracy of data processing.

[0122] The present invention provides a method for expressing detailed data lineage based on JSON format and automatically generating corresponding SQL to improve the storage and expression mechanism of data lineage. For log data, a very common scenario is that it is necessary to collect the different information contained in the data in the log into multiple different downstream data tables or components, and perform subsequent different processing according to business data with different meanings. The processing of log data diversion is a relatively conventional processing process. The present invention has developed a complete set of logical expressions based on the characteristics and processing procedures of log data.

[0123] The purpose of the present invention is to propose a method for realizing the lineage expression based on log detail data and the automatic generation of corresponding SQL, so as to solve the problems of missing lineage information, large limitations of manual configuration and lack of automated tools in the current log data processing. Specifically, the present invention aims to achieve the following objectives:

[0124] 1. Improve the traceability of data lineage: By performing detailed lineage analysis on log data, the present invention can accurately trace the flow path of data from the original log file to each downstream data table or component. This function will greatly facilitate data analysts and engineers to understand the source of data and the business logic behind it, thereby better supporting data analysis decisions.

[0125] 2. Simplify the data processing process: With the help of the automation tool provided by the present invention, the time and workload of manually configuring and writing SQL queries can be significantly reduced. Generating SQL query statements in an automated manner not only improves work efficiency, but also reduces the possibility of errors introduced by human factors.

[0126] 3. Enhanced data processing flexibility: The design of the present invention takes into account the diversity and complexity of log data structures, so it can flexibly adapt to different types of log data sources. Log data can be effectively parsed and distributed to corresponding downstream components after ETL according to predefined rules.

[0127] 4. Improve the accuracy of data processing: By automatically extracting key information from log data and accurately mapping it to downstream data tables or components, errors in data processing can be significantly reduced. In addition, through detailed lineage information records, the root cause of data quality problems can be quickly located and timely measures can be taken to correct them.

[0128] 5. Promote teamwork and knowledge sharing: This invention provides unified data lineage management and automation tools, so that data engineers, analysts and other stakeholders can work together on the same platform to maintain data accuracy and consistency. This not only helps to improve the work efficiency of the team, but also promotes knowledge sharing and experience accumulation within the organization.

[0129] 6. Support compliance and audit requirements: In many industries, such as finance, healthcare, etc., there are strict requirements for data compliance and auditing. The lineage tracking function provided by the present invention can help organizations better meet these requirements and ensure that data processing activities comply with relevant specifications.

[0130] like Figure 7 As shown, the technical solution of the present invention comprises the following steps:

[0131] 1. Identification and configuration of data sources: Identify the data source type (such as Kafka, database, etc.) and configure the name and related properties of the data source.

[0132] 2. Application of filtering rules: Preprocess the data and define filtering conditions to determine which data is further processed.

[0133] 3. Definition of data conversion rules: Define how data is converted, including field mapping, value copying, dictionary transcoding, and constant filling. According to the rules provided by conditions in the data conversion strategy, the corresponding fields of the original log data are checked. If they meet the extraction rules, different fields are processed according to extractFields to achieve field-level lineage tracking and management.

[0134] 4. Designation of data targets: define the storage location and format of the processed data.

[0135] 5. Generate SQL scripts: Automatically generate SQL query scripts according to the above rules to perform data conversion tasks.

[0136] like Figure 8 As shown, it is an architecture diagram of the lineage expression based on log detail data and the corresponding SQL automatic generation system, which intuitively shows the data processing flow from data source to target resource. The present invention will be further described in detail below in conjunction with specific implementation examples. The specific embodiments of the method are only used for explanation and description, not for limitation of scenarios and embodiments.

[0137] Among them, in some specific implementations, the lineage storage JSON sample fragment is as shown in code 1:

[0138]

[0139]

[0140]

[0141] The types of conditional operators are commonly used as shown in Table 1 above. More advanced types can be customized and are also implemented using UDF in the subsequent implementation process.

[0142] The types of extraction methods are as shown in Table 2 above. If a more advanced type is required, it can be customized and implemented using UDF in the subsequent implementation process. Specific embodiment:

[0144] Taking the configuration file in the above code 1 as an example, the program identifies and configures the data source and finds that there is a Kafka topic named topic01 as the data source. It wants to send the data in it to another Hive data table named table01 in the db01 library after some processing.

[0145] Next, we identify the data filtering rules. In the filter, we identify that the ETL filtering rules contain two parts. The first one is that the f001 field is not empty and the logical operator is OR. The second one is that the f002 field is not empty.

[0146] After completion, define and identify data conversion rules, mainly read the mapping rules of multiple fields under the extractFields attribute. According to the ETL process involving 4 fields shown in Code 1:

[0147] 1. Perform dictionary mapping on the f003 field in topic01 according to the preset map01 dictionary, and fill the mapped value into the f004 field in the target table table01.

[0148] 2. Perform a non-null value copy operation on the f005 and f006 fields in topic01, and copy the non-null values ​​in f005 and f006 to the f007 field in the target table table01.

[0149] 3. Perform a value copy operation on the f008 field in topic01, and copy the data in f008 to the f009 field in the target table table01.

[0150] 4. Fill the f010 field in the target table table01 with the preset constant "hello world".

[0151] Finally, run the SQL generator, and the generated results are as follows:

[0152] INSERT INTO db01.table01(f004,f007,f009,f010)

[0153] SELECT

[0154] udf_dicmap('map01',f003),

[0155] coalesce(f005,f006),

[0156] f008,

[0157] 'hello world'

[0158] FROM topic01

[0159] WHERE(f001 IS NOT NULL OR f002 IS NOT NULL);

[0160] It can then be executed on a variety of components that support the SQL protocol. It should be noted that user-defined functions must be mounted before execution.

[0161] This invention mainly proposes an innovative solution to the problems of lineage analysis and automatic SQL generation in log data processing. The core technical points of this solution include:

[0162] 1. Automatically generate SQL query statements: Based on the data lineage map obtained through analysis, the present invention can automatically generate SQL query statements for data extraction, conversion and loading. These query statements cover the entire data flow path from the original log file to the downstream data table or component, greatly reducing the workload of developers.

[0163] 2. Flexible configuration and expansion capabilities: Considering the diversity and complexity of log data, the present invention provides highly flexible configuration options, allowing users to adjust the rules of lineage analysis and the templates for SQL generation according to actual needs. In addition, lineage storage also supports plug-in expansion, which is convenient for adding new data sources or expanding other functions in the future.

[0164] 3. Comprehensive data quality monitoring: In addition to the core data lineage analysis and SQL generation functions, the present invention can also be integrated with a data quality monitoring module. The configuration information based on data lineage can monitor abnormal situations in the data processing process in real time to ensure the accuracy and integrity of the data.

[0165] Compared with the prior art, the present invention has at least the following beneficial effects:

[0166] 1. Improve data processing efficiency: By performing blood relationship analysis and SQL generation in an automated manner, the present invention can significantly shorten the data processing cycle and improve overall work efficiency.

[0167] 2. Reduce the human error rate: Automatic SQL generation tools reduce the need for manual development, reduce the probability of data errors caused by human factors, and improve the accuracy of data processing.

[0168] 3. Enhanced data traceability: Detailed lineage information records facilitate data source tracing, help quickly locate problems, and improve troubleshooting efficiency.

[0169] 4. Promote team collaboration: Unified data lineage management and automation tools promote communication and collaboration across departments and enhance teamwork capabilities.

[0170] 5. Support compliance requirements: For industries that need to comply with strict compliance standards, the data lineage tracking function provided by the present invention helps to meet audit requirements and ensure that data processing activities comply with relevant regulations.

[0171] 6. Easy to maintain and expand later: Flexible configuration options facilitate the later maintenance and functional upgrade of the system, ensuring long-term stability and availability.

[0172] In summary, the present invention provides an implementation method for lineage expression based on log detail data and automatic generation of corresponding SQL, aiming to solve key problems in current log data processing through automated and intelligent means, improve the efficiency, accuracy and reliability of data processing, and thus promote the development of data-driven businesses.

[0173] By achieving the above objectives, the present invention is expected to become an important technological innovation in the field of log data processing, which can not only significantly improve the efficiency and accuracy of data processing, but also enhance the transparency and auditability of the data processing process, and provide more powerful data support for enterprises or organizations.

[0174] On the other hand, Fig. 9 As shown, the embodiment of the present invention provides a SQL generation device 900 for lineage expression, including:

[0175] The first module 901 is used to obtain the blood relationship storage information of the target data source; the target data source includes first data, and the first data includes at least one data field;

[0176] The second module 902 is used to extract and obtain configuration information based on the blood relationship storage information;

[0177] The configuration information represents the blood relationship strategy configuration for processing the first data into the second data; the configuration information includes data storage targets, data filtering rules, data conversion rules and data flow directions;

[0178] The third module 903 is used to obtain the SQL for data conversion from the target data source to the data storage target according to the target data source and the configuration information.

[0179] The contents of the method embodiments of the present invention are all applicable to the device embodiments. The functions specifically implemented by the device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0180] On the other hand, an embodiment of the present invention further provides an electronic device, the electronic device includes a memory and a processor, the memory stores a computer program, and the processor implements the above sensitive information method when executing the computer program. The electronic device can be any intelligent terminal including a tablet computer, a car computer, etc.

[0181] It can be understood that the contents of the above method embodiments are all applicable to the present device embodiments, the functions specifically implemented by the present device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0182] like Fig.10 As shown, Fig.10A specific example of the hardware structure of an electronic device 1000 according to an embodiment is shown. The electronic device 1000 includes:

[0183] The processor 1001 may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of the present invention.

[0184] The memory 1002 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 1002 can store an operating system and other application programs. When the technical solution provided in the embodiment of this specification is implemented by software or firmware, the relevant program code is stored in the memory 1002, and the processor 1001 calls and executes the network node population optimization method of the embodiment of the present invention;

[0185] Input / output interface 1003, used to implement information input and output;

[0186] The communication interface 1004 is used to realize the communication interaction between the device and other devices. The communication can be realized through a wired manner (such as USB, network cable, etc.) or a wireless manner (such as mobile network, WIFI, Bluetooth, etc.);

[0187] A bus 1005 , which transmits information between various components of the device (e.g., the processor 1001 , the memory 1002 , the input / output interface 1003 , and the communication interface 1004 );

[0188] The processor 1001 , the memory 1002 , the input / output interface 1003 and the communication interface 1004 are connected to each other in communication within the device via the bus 1005 .

[0189] The electronic device embodiments described above are merely illustrative, and the units described as separate components may or may not be physically separated, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0190] The contents of the method embodiments of the present invention are all applicable to the electronic device embodiments. The functions specifically implemented by the electronic device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0191] Another aspect of an embodiment of the present invention further provides a computer-readable storage medium, wherein the storage medium stores a program, and the program is executed by a processor to implement the above method.

[0192] It should be noted that the computer-readable medium shown in the embodiment of the present invention may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM), a flash memory, an optical fiber, a portable compact disk read-only memory (Compact Disc Read to Only Memory, CD to ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, device or device. In the present invention, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. Such propagated data signals may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer readable signal media may also be any computer readable medium other than computer readable storage media, which may send, propagate, or transmit programs for use by or in conjunction with an instruction execution system, apparatus, or device. The program code contained on the computer readable medium may be transmitted using any suitable medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0193] The contents of the method embodiments of the present invention are all applicable to the computer-readable storage medium embodiments. The functions specifically implemented by the computer-readable storage medium embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.

[0194] The embodiment of the present invention also discloses a computer program product or a computer program, which includes a computer instruction stored in a computer-readable storage medium. A processor of a computer device can read the computer instruction from the computer-readable storage medium, and the processor executes the computer instruction, so that the computer device executes the above method.

[0195] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0196] It should be noted that, although several modules of the device for action execution are mentioned in the above detailed description, this division is not mandatory. In fact, according to an embodiment of the present invention, the features and functions of two or more modules or units described above can be embodied in one module or unit. On the contrary, the features and functions of one module or unit described above can be further divided into being embodied by multiple modules or units.

[0197] Through the description of the above implementation, it is easy for those skilled in the art to understand that the example implementation described here can be implemented by software, or by software combined with necessary hardware. Therefore, the technical solution according to the implementation of the present invention can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (which can be a CD to ROM, a USB flash drive, a mobile hard disk, etc.) or on a network, including several instructions to enable a computing device (which can be a personal computer, a server, a touch terminal, or a network device, etc.) to execute the method according to the implementation of the present invention.

[0198] In some selectable embodiments, the function / operation mentioned in the block diagram may not occur in the order mentioned in the operation diagram. For example, depending on the function / operation involved, the two boxes shown in succession can actually be executed substantially simultaneously or the boxes can sometimes be executed in reverse order. In addition, the embodiment presented and described in the flow chart of the present invention is provided by way of example, for the purpose of providing a more comprehensive understanding of technology. The disclosed method is not limited to the operation and logic flow presented herein. Selectable embodiments are expected, wherein the order of various operations is changed and the sub-operation of a part for which is described as a larger operation is performed independently.

[0199] In addition, although the present invention is described in the context of functional modules, it should be understood that, unless otherwise specified, one or more of the functions and / or features can be integrated into a single physical device and / or software module, or one or more functions and / or features can be implemented in a separate physical device or software module. It is also understood that a detailed discussion of the actual implementation of each module is unnecessary for understanding the present invention. More specifically, in view of the properties, functions and internal relationships of the various functional modules in the device disclosed herein, the actual implementation of the module will be understood within the conventional skills of the engineer. Therefore, those skilled in the art can implement the present invention set forth in the claims without excessive experimentation using ordinary techniques. It is also understood that the specific concepts disclosed are merely illustrative and are not intended to limit the scope of the present invention, which is determined by the full scope of the appended claims and their equivalents.

[0200] If the function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, 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. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the methods of each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, etc. Various media that can store program codes.

[0201] The logic and / or steps represented in the flowchart or otherwise described herein, for example, may be considered as an ordered list of executable instructions for implementing logical functions, and may be embodied in any computer-readable medium for use by an instruction execution device, apparatus or device (such as a computer-based device, a device including a processor, or other device that can fetch instructions from an instruction execution device, apparatus or device and execute the instructions), or in conjunction with such instruction execution device, apparatus or device. For purposes of this specification, "computer-readable medium" may be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution device, apparatus or device, or in conjunction with such instruction execution device, apparatus or device.

[0202] More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk case (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be a paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, deciphering or, if necessary, processing in another suitable manner, and then stored in a computer memory.

[0203] It should be understood that the various parts of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution device. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0204] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "examples", "specific examples", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0205] Although the embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the claims and their equivalents.

[0206] The above is a specific description of the preferred implementation of the present invention, but the present invention is not limited to the embodiments. Those skilled in the art can make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of the present invention.

Claims

1. A method for generating SQL for blood relationship expression, characterized in that: The following steps are involved: Acquire the lineage storage information of the target data source; the target data source includes first data, and the first data includes at least one data field; Extracting and obtaining configuration information based on the blood relationship storage information; Wherein, the configuration information represents the blood relationship strategy configuration for processing the first data into the second data; the configuration information includes data storage target, data filtering rules, data conversion rules and data flow direction; According to the target data source and the configuration information, an SQL for data conversion from the target data source to the data storage target is obtained.

2. The method for generating SQL for blood relationship expression according to claim 1, characterized in that: The first segment of the lineage storage information includes identification information of the target data source; the step of extracting and obtaining configuration information based on the lineage storage information includes the following steps: determining the data storage target based on the second segment of the lineage storage information; Determining the data filtering rule based on the third segment of the blood relationship storage information; The data conversion rule and the data flow direction of the data field are determined based on the fourth segment of the lineage storage information.

3. The SQL generation method for blood relationship expression according to claim 2, characterized in that: Determining the data storage target based on the second segment of the bloodline storage information comprises the following steps: Extract the target database name and the target resource name from the second segment, and then determine the target database and the target resource; The data storage target of the second data is determined according to the location of the target resource under the target database.

4. The SQL generation method for blood relationship expression according to claim 2, characterized in that: The step of determining the data filtering rule based on the third segment of the blood relationship storage information comprises the following steps: extracting conditional operators and logical operators for the target data field from the third segment; The conditional operation and the logical operation for the target data field are determined based on the conditional operator and the logical operator, thereby determining the data filtering rule.

5. The method for generating SQL for blood relationship expression according to claim 2, characterized in that: The step of determining the data conversion rule and the data flow direction of the data field based on the fourth segment of the blood relationship storage information comprises the following steps: Extracting from the fourth segment the source field of the data field in the target data source, the extraction method, and the target field in the data storage target after processing based on the extraction method; the extraction method includes value copying, non-null value copying, constant filling, and dictionary transcoding; The data conversion rule corresponding to the data field is determined based on the extraction method, and the data flow direction corresponding to the data field is determined based on the source field and the target field.

6. The method for generating SQL for blood relationship expression according to claim 1, characterized in that: The step of obtaining the SQL for data conversion from the target data source to the data storage target according to the target data source and the configuration information comprises the following steps: The target data source and the configuration information are imported into a preset generation template to obtain SQL for data conversion from the target data source to the data storage target.

7. The method for generating SQL for blood relationship expression according to claim 6, characterized in that: The data flow includes the source field of the data field in the target data source and the target field in the data storage target after conversion based on the data conversion rule; the target data source and the configuration information are imported into a preset generation template to obtain the SQL for data conversion from the target data source to the data storage target, including the following steps: Importing the data storage target and the data field into the first template after the data storage target and the data field are converted based on the corresponding data conversion rules; Importing the source field and / or the data conversion rule corresponding to each target field into the second template in sequence; Importing the target data source into the third template; importing the data filtering rule into the fourth template; The SQL for data conversion from the target data source to the data storage target is obtained by sorting out the first template, the second template, the third template and the fourth template.

8. A SQL generation device for blood relationship expression, characterized in that: include: The first module is used to obtain the blood relationship storage information of the target data source; The target data source includes first data, and the first data includes at least one data field; The second module is used to extract and obtain configuration information based on the blood relationship storage information; Wherein, the configuration information represents the blood relationship strategy configuration for processing the first data into the second data; the configuration information includes data storage target, data filtering rules, data conversion rules and data flow direction; The third module is used to obtain SQL for data conversion from the target data source to the data storage target according to the target data source and the configuration information.

9. An electronic device, characterized in that: including a processor and a memory; The memory is used to store programs; The processor executes the program to implement the method according to any one of claims 1 to 7.

10. A computer storage medium storing a program executable by a processor, characterized in that: The program executable by the processor is used to implement the method according to any one of claims 1 to 7 when executed by the processor.

Images