ETL script generation method, apparatus, device, and computer-readable medium

Abstract

The present invention discloses an ETL script generation method, apparatus, device, and computer-readable medium, relating to the field of computer technology. A specific embodiment of the method includes: parsing a document based on document configuration information, integrating data table-level information and field-level information in the document to generate a data source configuration file; traversing the worksheets in the document configuration information and verifying the worksheet configuration information, determining data merge operations and establishing data lists based on the worksheets to construct a script output template; parsing the data source configuration file to locate fields based on data table-level information and field-level information, determining conversion data parameters based on the source type of the field, processing the fields after the conversion data parameters using a logical processing operator corresponding to the source type, and populating the script output template with the field processing results to generate an ETL script. This embodiment can improve the scalability and adaptability of ETL script development.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to an ETL script generation method, apparatus, device and computer-readable medium. Background Art

[0002] In today's data-driven era, ETL (Extract-Transform-Load) programs have become a key tool for businesses and organizations to process and analyze large amounts of data.

[0003] In the process of implementing the present invention, the inventors found that there are at least the following problems in the prior art: manually writing and maintaining ETL scripts is not only time-consuming and error-prone, but also the scalability of ETL scripts is difficult to meet the needs in the face of ever-increasing data volume and data complexity. Summary of the Invention

[0004] In view of this, embodiments of the present invention provide an ETL script generation method, apparatus, device, and computer-readable medium, which can improve the scalability and adaptability of ETL scripts.

[0005] To achieve the above object, according to one aspect of an embodiment of the present invention, a method for generating an ETL script is provided, comprising:

[0006] Parse the document according to the document configuration information, integrate the data table-level information and field-level information in the document to generate a data source configuration file;

[0007] After traversing the worksheets in the document configuration information and verifying the worksheet configuration information, determining a data merging operation and establishing a data list according to the worksheets to construct a script output template;

[0008] The data source configuration file is parsed to locate the fields according to the data table-level information and field-level information, the conversion data parameters are determined based on the source type of the field, the fields after the conversion data parameters are processed in combination with the logical processing operator corresponding to the source type, and the field processing results are filled in the script output template to generate an ETL script.

[0009] The step of parsing the document according to the document configuration information and integrating the data table-level information and field-level information in the document to generate a data source configuration file includes:

[0010] Parse the document according to the list of documents to be processed in the document configuration information, extract the data table-level information of the document to be processed and store the document to be processed in the list of documents to be processed;

[0011] Based on the data table-level information of the document to be processed, calling the document extraction function to extract field information from the document to be processed in the list of documents to be processed;

[0012] The data table-level information and the field-level information are stored in the interface_column_info_lack table, and the interface_column_info_lack table is inserted into the interface_tbl_info table, so as to generate a data source configuration file according to the interface_tbl_info table.

[0013] The step of parsing the document according to the list of documents to be processed in the document configuration information, extracting data table-level information of the document to be processed, and storing the document to be processed in the list of documents to be processed includes:

[0014] Parsing documents according to the list of pending documents in the document configuration information, extracting data table-level information of the pending documents, and calling a document extraction function based on the data table-level information of the pending documents to be processed to extract data table information by row and column from the pending documents in the list of pending documents, wherein the data table information includes field values ​​and field positions;

[0015] The data table information is stored in analysis_data_list, and the analysis_data_list and the document to be processed are stored in a document list to be processed.

[0016] The step of calling a document extraction function based on the data table-level information of the document to be processed to extract field information from the document to be processed in the list of documents to be processed includes:

[0017] Determining, based on data table-level information of the document to be processed, whether a regular expression exists in a field of the document to be processed, and calling a document extraction function to extract field information of the document to be processed in the list of documents to be processed according to the regular expression;

[0018] Based on the data table-level information of the document to be processed, it is determined that no regular expression exists in the field of the document to be processed, and a document extraction function is called to directly extract field information from the document to be processed in the list of documents to be processed.

[0019] After traversing the worksheet in the document configuration information to verify the worksheet configuration information, determining a data merging operation and establishing a data list according to the worksheet includes:

[0020] Traversing the worksheets in the document configuration information to obtain worksheet configuration information, executing an import function to successfully import the data of the worksheet into a database, and successfully verifying the worksheet configuration information;

[0021] Determine various data merge operations based on the worksheet, and create and successfully verify a data list.

[0022] The step of determining multiple data merging operations based on the worksheet, establishing and successfully verifying a data list, includes:

[0023] Determine the SQL statements for multiple data merging operations based on the worksheet;

[0024] An SQL statement for executing a merge operation on the data table-level information and verifying the merge result of the data table-level information, an SQL statement for executing a merge operation on the field-level information, obtaining the merge result of the field-level information, and constructing the data list with the merge result of the data table-level information and the merge result of the field-level information.

[0025] The step of parsing the data source configuration file to locate fields according to data table-level information and field-level information, converting data parameters based on the source type of the field, processing the fields after the data parameters are converted in combination with the logical processing operators corresponding to the source type, and filling the script output template with the field processing results to generate an ETL script includes:

[0026] Parsing the data source configuration file to locate fields according to data table-level information and field-level information, determining conversion data parameters based on the source type of the field, the conversion data parameters including a conversion type and a data length, and concatenating the fields according to the conversion type and data length to obtain fields after the conversion data parameters are obtained;

[0027] The fields after the data parameters are converted are processed in combination with the logical processing operator corresponding to the source type, and the field processing results are mapped to the script output template based on the header information to generate an ETL script.

[0028] According to a second aspect of an embodiment of the present invention, there is provided an ETL script generating device, comprising:

[0029] The data module is used to parse the document according to the document configuration information and integrate the data table-level information and field-level information in the document to generate a data source configuration file;

[0030] A configuration module, configured to traverse the worksheets in the document configuration information and verify the worksheet configuration information, and then determine a data merging operation and create a data list according to the worksheets to construct a script output template;

[0031] A generation module is used to parse the data source configuration file to locate fields according to data table-level information and field-level information, determine conversion data parameters based on the source type of the field, process the fields after the conversion data parameters in combination with the logical processing operator corresponding to the source type, and fill the script output template with the field processing results to generate an ETL script.

[0032] According to a third aspect of an embodiment of the present invention, an electronic device for generating an ETL script is provided, comprising:

[0033] one or more processors;

[0034] a storage device for storing one or more programs,

[0035] When the one or more programs are executed by the one or more processors, the one or more processors implement the above-described method.

[0036] According to a fourth aspect of an embodiment of the present invention, a computer-readable medium is provided, on which a computer program is stored. When the program is executed by a processor, the method described above is implemented.

[0037] One embodiment of the above invention has the following advantages or beneficial effects: parsing a document according to document configuration information, integrating data table-level information and field-level information in the document to generate a data source configuration file; after traversing the worksheets in the document configuration information and verifying the worksheet configuration information, determining data merge operations and establishing a data list based on the worksheets to construct a script output template; parsing the data source configuration file to locate fields according to data table-level information and field-level information, determining conversion data parameters based on the source type of the field, processing the fields after the conversion data parameters using a logical processing operator corresponding to the source type, and filling the script output template with the field processing results to generate an ETL script. The use of logical processing operators can process fields of different source types accordingly, thereby improving the scalability and adaptability of ETL script development.

[0038] The further effects of the above-mentioned non-conventional optional manner will be described below in conjunction with specific embodiments. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The accompanying drawings are provided for a better understanding of the present invention and are not intended to limit the present invention.

[0040] Figure 1 1 is a schematic diagram of the main process of the ETL script generation method according to an embodiment of the present invention;

[0041] Figure 2 is a schematic diagram of a process for integrating data table-level information and field-level information in a document according to an embodiment of the present invention;

[0042] Figure 3 is a schematic diagram of a process for extracting data table-level information of a document to be processed according to an embodiment of the present invention;

[0043] Figure 4 2. It is a schematic diagram of a process of calling a document extraction function to extract field information according to an embodiment of the present invention;

[0044] Figure 5is a schematic diagram of a process of traversing a work table in document configuration information according to an embodiment of the present invention;

[0045] Figure 6 is a schematic diagram of a process for merging multiple data according to an embodiment of the present invention;

[0046] Figure 7 1 is a flow chart of processing fields based on logic processing operators corresponding to source types according to an embodiment of the present invention;

[0047] Figure 8 2 is a schematic diagram of the main structure of an ETL script generating device according to an embodiment of the present invention;

[0048] Figure 9 is an exemplary system architecture diagram in which embodiments of the present invention may be applied;

[0049] Figure 10 It is a schematic diagram of the structure of a computer system of a terminal device or a server suitable for implementing an embodiment of the present invention. DETAILED DESCRIPTION

[0050] The following description of exemplary embodiments of the present invention is made in conjunction with the accompanying drawings, in which various details of the embodiments of the present invention are included to facilitate understanding. These details should be considered as merely exemplary. Therefore, it should be appreciated by those skilled in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present invention. Similarly, for the sake of clarity and conciseness, descriptions of well-known functions and structures are omitted in the following description.

[0051] Currently, the methods for generating ETL scripts can be divided into the following three categories:

[0052] Use programming languages ​​like Python or Java to write ETL scripts. Customized ETL scripts can extract, transform, and load data based on actual needs. However, the ETL programs generated by this approach are less standardized and less maintainable.

[0053] Leverage ETL libraries. Numerous ETL libraries are available, such as Pandas, OpenPyXL, and DBConvert. These libraries offer rich functionality and simple operations, enabling automated ETL script generation. However, these libraries limit customizable parameters and offer limited flexibility.

[0054] Use an ETL framework, such as Airflow or Apache Nifi. These frameworks provide a complete solution, including data extraction, transformation, and loading. These frameworks can simplify the implementation of ETL programs while improving code quality and maintainability. However, these frameworks require additional investment.

[0055] In order to solve the technical problem of poor scalability of ETL scripts, the following technical solutions in the embodiments of the present invention may be adopted.

[0056] See also Figure 1 , Figure 1 This is a schematic diagram of the main process of the ETL script generation method according to an embodiment of the present invention. The document is parsed to obtain a data source configuration file, and the data source configuration file is filled in the script output template to generate an ETL script. Figure 1 As shown in 100, it specifically includes the following steps:

[0057] S101. Parse the document according to the document configuration information, and integrate the data table-level information and field-level information in the document to generate a data source configuration file.

[0058] ETL processes primarily involve three steps: extraction, transformation, and loading. During the extraction phase, ETL tools extract data from various data sources, such as relational databases and file systems. During the transformation phase, the tools cleanse, process, and integrate the extracted data to meet user needs. Finally, during the loading phase, the ETL tools load the processed data into the target data store, such as a data warehouse or data lake.

[0059] In an embodiment of the present invention, a document can be parsed based on document configuration information. As an example, the document configuration information includes a document template, document parsing parameters, a list of documents to be processed, and a worksheet. The document includes multiple fields. The document is configured according to the document template. As an example, the document is a Word document.

[0060] A document contains multiple tables, each containing multiple fields. To improve the accuracy of generated ETL scripts, it is necessary to obtain both table-level and field-level information from the document to generate a data source configuration file. The data source configuration file considers not only the tables but also the fields, ensuring full consistency between the data source configuration file and the document.

[0061] See also Figure 2 , Figure 2 This is a schematic diagram of a process for integrating table-level information and field-level information in a document according to an embodiment of the present invention. Specifically, the process includes the following steps:

[0062] S201 , parsing documents according to a list of documents to be processed in the document configuration information, extracting data table-level information of the documents to be processed, and storing the documents to be processed in the list of documents to be processed.

[0063] To-be-processed documents are the documents for which ETL scripts are generated. Documents can be parsed based on the list of to-be-processed documents in the document configuration information to extract table-level information about the to-be-processed documents. For example, parsing documents based on the list of to-be-processed documents sequentially retrieves multiple to-be-processed documents, and then extracting table-level information from these multiple to-be-processed documents.

[0064] In one embodiment of the present invention, the document is parsed based on the list of pending documents in the document configuration information to obtain the number of pending documents. Furthermore, detailed configuration information of the pending documents is obtained. As an example, the detailed configuration information of the pending documents includes data table-level information of the pending documents.

[0065] In order to facilitate subsequent acquisition of field levels, the to-be-processed documents are stored in a to-be-processed document list, so that field-level information can be extracted based on the to-be-processed documents in the to-be-processed document list.

[0066] See also Figure 3 , Figure 3 This is a flow chart of extracting table-level information of a document to be processed according to an embodiment of the present invention. Specifically, it includes the following steps:

[0067] S301. Parse the document according to the list of pending documents in the document configuration information, extract data table-level information of the pending documents, and based on the data table-level information of the pending documents, call the document extraction function to extract data table information by row and column from the pending documents in the list of pending documents. The data table information includes field values ​​and field positions.

[0068] In one embodiment of the present invention, a document extraction function is used to extract data table information by row and column, thereby improving the accuracy of the data table information. Specifically, a document is parsed based on the list of pending documents in the document configuration information to extract data table-level information for the pending documents. Based on the data table-level information for the pending documents, the document extraction function is called to extract data table information by row and column from the pending documents in the pending document list. The data table information includes field values ​​and field positions.

[0069] Assign the configuration information related to the rows to be extracted in the document to be processed to analysis_data_list, so that the corresponding fields of the rows in the data table information in analysis_data_list can be extracted later.

[0070] The documents in the pending document list are processed sequentially, completing various operations on the table data. Each row of data in the pending document is processed one by one. For example, the valid field values ​​in each row of data are stored in a temporary field value list, and the positions of the valid fields in each row of data are stored in a temporary field position list. This facilitates subsequent operations such as data association based on field positions.

[0071] When certain conditions are met (first_row_flg=True), the temporary list of fields is assigned to the sub_col_num_list variable for subsequent data association analysis and other operations.

[0072] If first_row_flg is False, extract the value corresponding to sub_col_num_list in the current row and store it in the analysis_data_list list.

[0073] S302: Store the data table information into analysis_data_list, and store the analysis_data_list and the document to be processed into a list of documents to be processed.

[0074] According to sub_col_num_list, the corresponding value is stored in analysis_data_list to integrate the data. In other words, the data table information is stored in analysis_data_list.

[0075] Store the field column values ​​in the list data_list. This data_list is then stored in the analysis_data_list. The data in the analysis_data_list is converted to a format that better suits the storage requirements. The analysis_data_list includes the field values ​​and positions from the table. The analysis_data_list and the pending documents can be stored in the pending documents list to extract the field information.

[0076] exist Figure 3 In the embodiment, data table information is extracted in rows and columns to improve the accuracy of data table level information.

[0077] S202: Based on the data table-level information of the document to be processed, call a document extraction function to extract field information from the document to be processed in the list of documents to be processed.

[0078] The pending documents list stores pending documents. Based on the data table-level information of the pending documents, the document extraction function can be called to extract field information from the pending documents in the pending documents list.

[0079] See also Figure 4 , Figure 4 This is a flow chart of calling a document extraction function to extract field information according to an embodiment of the present invention. Specifically, the following steps are included:

[0080] S401: Determine whether a field in a to-be-processed document has a regular expression based on data table-level information of the to-be-processed document, and call a document extraction function to extract field information from the to-be-processed document in a to-be-processed document list according to the regular expression.

[0081] In an embodiment of the present invention, regular expressions are used to extract field information. Regular expressions can be used to extract information that conforms to specific patterns from large amounts of data, thereby accurately achieving data extraction and cleaning.

[0082] Based on the data table-level information of the document to be processed, it is determined whether a regular expression exists in the field of the document to be processed, and the document extraction function is called to extract the field information of the document to be processed in the list of documents to be processed according to the regular expression.

[0083] As an example, based on the data table-level information of the document to be processed, it is determined that there is a regular expression in the field of the document to be processed, and the document extraction function is called to extract the data at the corresponding position from the data_list and store it in the db_data_list. The list db_data_list is converted into columns and then written into the database to realize the storage of field information.

[0084] S402: Determine based on the data table-level information of the document to be processed that no regular expression exists in the field of the document to be processed, and call a document extraction function to directly extract field information from the document to be processed in the list of documents to be processed.

[0085] Based on the data table-level information of the document to be processed, it is determined that the field in the document to be processed does not contain a regular expression, and the document extraction function is called to directly extract the field information from the document to be processed in the list of documents to be processed.

[0086] As an example, based on the data table-level information of the document to be processed, it is determined that there is no regular expression in the field of the document to be processed. The document extraction function is called to extract the data at the corresponding position in data_list and store it in db_data_list. The list db_data_list is converted into columns and then written to the database to realize the storage of field information.

[0087] exist Figure 4 In the embodiment, regular expressions are used to accurately implement data extraction and cleaning.

[0088] S203: Store data table-level information and field-level information in the interface_column_info_lack table, and insert the interface_column_info_lack table into the interface_tbl_info table to generate a data source configuration file according to the interface_tbl_info list.

[0089] In one embodiment of the present invention, the interface_tbl_info table and the interface_column_info_lack table are read from the database. Table-level information and field-level information are stored in the interface_column_info_lack table. The interface_column_info_lack table is used to store table-level information and field-level information. The interface_tbl_info table is used to generate a data source configuration file.

[0090] Insert the interface_column_info_lack table into the interface_tbl_info table to generate a data source configuration file based on the interface_tbl_info list. As an example, loop through the data in the interface_info_lack table to insert the data from the interface_column_info_lack table into the interface_tbl_info table. The interface_tbl_info list includes the database name (dbname) and the table name (Tblname).

[0091] exist Figure 2 In an embodiment, the data table-level information and field-level information in the document are integrated to generate a data source configuration file.

[0092] S102: After traversing the worksheets in the document configuration information and verifying the configuration information of the worksheets, a data merging operation is determined and a data list is created according to the worksheets to construct a script output template.

[0093] In an embodiment of the present invention, in order to quickly generate an ETL script, a script output template may be constructed, and then the ETL script may be quickly generated based on the script output template.

[0094] The worksheet in the document configuration information includes configuration information. After traversing the worksheet in the document configuration information and verifying the configuration information of the worksheet, a data merging operation is determined and a data list is established according to the worksheet to construct a script output template.

[0095] See also Figure 5 , Figure 5 This is a flow chart of traversing the worksheet in the document configuration information according to an embodiment of the present invention. Specifically, it includes the following steps:

[0096] S501 , traverse the worksheets in the document configuration information to obtain worksheet configuration information, execute the import function to successfully import the worksheet data into the database, and successfully verify the worksheet configuration information.

[0097] In an embodiment of the present invention, in order to ensure that data is successfully imported into the database, the validity of the worksheet configuration information needs to be verified. The worksheet configuration information can be used to import the data of the worksheet. If the data is successfully imported into the database, the worksheet configuration information is successfully verified.

[0098] As an example, the worksheet configuration information in the document configuration information is traversed to obtain the worksheet configuration information, and the import function is executed to successfully import the data of the worksheet into the database, and the worksheet configuration information is successfully verified.

[0099] Specifically, all worksheets in the document configuration information are traversed to ensure that the data in each worksheet can be processed. The import function is used to trigger data transmission and import the worksheet data into the database.

[0100] As an example, dynamically generate an SQL statement for inserting data into a database based on the data field structure. Execute the SQL statement to write the worksheet data into the database. Depending on whether the data write operation succeeds or fails, a Boolean value is returned to indicate the result of the operation.

[0101] Verify that the data has been successfully imported into the database to ensure that the data has been transferred and stored correctly. If the data has been successfully imported into the database, the worksheet configuration information is successfully verified, thus ensuring the accuracy and completeness of the data migration.

[0102] S502: Determine multiple data merging operations based on the worksheet, and create and successfully verify a data list.

[0103] In embodiments of the present invention, a worksheet involves multiple types of data. For example, this data includes one or more of the following: model data, task data, field-level data, and deployment data. Model data involves the definition of data structures and business logic. Task data is data related to process control and task scheduling. Field-level data is specific attribute information. Deployment data is data related to system configuration and operating environment.

[0104] Based on the worksheet, you can determine various data merge operations, and then create and verify a data list to determine the validity of the merge operation.

[0105] See also Figure 6 , Figure 6 This is a schematic diagram of a process for merging multiple data according to an embodiment of the present invention. Specifically, it includes the following steps:

[0106] S601: Determine SQL statements for multiple data merging operations based on a worksheet.

[0107] Get model data merging SQL statements, task data merging SQL statements, field-level data merging SQL statements, deployment data merging SQL statements, and field model conversion data lists based on the worksheet.

[0108] S602. Execute a SQL statement for a merge operation on the data table-level information and verify the merge result of the data table-level information. Execute a SQL statement for a merge operation on the field-level information to obtain the merge result of the field-level information. Construct a data list based on the merge result of the data table-level information and the merge result of the field-level information.

[0109] Merge SQL statements for table-level data are executed repeatedly to ensure that the table-level data is integrated according to the preset logic. These merge SQL statements include: merge SQL statements for obtaining model data from worksheets, merge SQL statements for obtaining task data, merge SQL statements for obtaining field-level data, and merge SQL statements for obtaining deployment data.

[0110] Determine whether the table name (sdata) of the table-level information merge result is in a normal state. If so, verification of the table-level information merge result is successful. If not, verification of the table-level information merge result fails, and appropriate exception handling is required.

[0111] In one embodiment of the present invention, the merged model data is stored in the database ods_model_info table, the merged task data is stored in the database ods_task_info table, and the merged deployment data is stored in the database deploy_info table to achieve final storage of the data.

[0112] The merge SQL statement for field-level data information is executed cyclically to ensure that the field-level data is integrated according to the preset logic.

[0113] Determine whether the ODS-layer distribution key for the current row's field is y to determine whether subsequent data conversion operations are required. The ODS field is used to consolidate related data scattered across multiple source systems. Call the field conversion function to convert the field data to conform to the ODS-layer data model or storage requirements.

[0114] Check whether the value of the current row field exists in the index list obtained previously to determine whether the field value is an index value and whether special processing is required.

[0115] The merged results of the field-level information are stored in the column_info table of the database to complete the storage of field-level data.

[0116] Next, a data list is constructed using a table that stores the merged results of table-level information and a table that stores the merged results of field-level information. A script output template is then constructed based on the data list. As an example, the data list is used as the script output template.

[0117] exist Figure 6 In the embodiment, data table-level information and field-level information are respectively stored in corresponding tables to increase the pertinence of information processing and thereby improve processing efficiency.

[0118] S103. Parse the data source configuration file to locate the fields according to the data table-level information and field-level information, determine the conversion data parameters based on the source type of the field, process the fields after the conversion data parameters in combination with the logical processing operator corresponding to the source type, and fill the script output template with the field processing results to generate the ETL script.

[0119] In an embodiment of the present invention, a data source configuration file includes both table-level and field-level information. The corresponding conversion data parameters are determined by locating the fields. Different logical processing operators are used for different field source types. The fields after the conversion data parameters are processed based on the logical processing operators corresponding to the source types. The field processing results are then populated into a script output template to generate an ETL script.

[0120] See also Figure 7 , Figure 7 This is a flow chart of processing fields based on logical processing operators corresponding to source types according to an embodiment of the present invention. Specifically, it includes the following steps:

[0121] S701. Parse the data source configuration file to locate the fields according to the data table-level information and field-level information, determine the conversion data parameters based on the source type of the field, the conversion data parameters include the conversion type and data length, and concatenate the fields according to the conversion type and data length to obtain the fields after the conversion data parameters are obtained.

[0122] Parse the data source configuration file to obtain table-level and field-level information. This information includes the field position, which is used to locate the field. For fields, the field name refers to the name of the field in the source data table. Each field has a unique name that identifies the data type or content it stores. The source type refers to the type of data in these fields, such as character, numeric, or date.

[0123] Conversion data parameters are determined based on the field's source type. These parameters include the conversion type and data length. Source types correspond to these parameters. Based on the field's source type, data type conversion and length setting operations are performed accordingly. Each type has corresponding conversion rules to ensure data consistency and accuracy.

[0124] As an example, each source type has a corresponding conversion type and data length. For example, when the source type is A, conversion type 1 = 3, and data length 1 = source length.

[0125] For data types involving decimal places, different conversion logic is performed based on whether decimal places exist and their specific values ​​to ensure the accuracy and format of the data are correct.

[0126] The fields after the data parameters are converted can be obtained by concatenating the fields according to the conversion type and data length. The conversion type and data length of the fields are integrated to form a complete data structure and generate the final file, that is, the fields after the data parameters are converted.

[0127] S702: Process the fields after converting the data parameters using the logic processing operator corresponding to the source type, and map the field processing results to the script output template based on the header information to generate an ETL script.

[0128] In an embodiment of the present invention, a logical processing operator is set based on the source type, and then the fields after the data parameters are converted are processed in combination with the logical processing operator corresponding to the source type. The field processing results are mapped to the script output template based on the header information to generate the ETL script.

[0129] Logical processing operators can be used to flexibly process and operate fields according to actual needs. As an example, logical processing operators involve data cleaning, data integration, data transformation, data mining, and data analysis.

[0130] exist Figure 7 In the embodiment, the logical processing operator corresponding to the source type is used to process the field, thereby improving the adaptability of the generated ETL script.

[0131] In the above-described embodiment of the present invention, a document is parsed according to document configuration information, and the data table-level information and field-level information in the document are integrated to generate a data source configuration file. After traversing the worksheets in the document configuration information and verifying the worksheet configuration information, a data merge operation is determined and a data list is established based on the worksheet to construct a script output template. The data source configuration file is parsed to locate fields according to the data table-level information and field-level information, conversion data parameters are determined based on the source type of the field, and the fields after the conversion data parameters are processed using a logical processing operator corresponding to the source type. The field processing results are then populated into the script output template to generate an ETL script. The use of logical processing operators can process fields of different source types, thereby improving the scalability and adaptability of ETL script development.

[0132] See also Figure 8 , Figure 8Schematic diagram of the main structure of the ETL script generation device according to an embodiment of the present invention, the ETL script generation device can implement the ETL script generation method, such as Figure 8 As shown in 800, the ETL script generation device specifically includes:

[0133] The data module 801 is used to parse the document according to the document configuration information, and integrate the data table-level information and field-level information in the document to generate a data source configuration file;

[0134] Configuration module 802, configured to traverse the worksheets in the document configuration information and verify the worksheet configuration information, and then determine data merging operations and create data lists according to the worksheets to construct a script output template;

[0135] Generate module 803, which is used to parse the data source configuration file to locate the fields according to the data table-level information and field-level information, determine the conversion data parameters based on the source type of the field, process the fields after the conversion data parameters in combination with the logical processing operator corresponding to the source type, and fill the script output template with the field processing results to generate an ETL script.

[0136] In one embodiment of the present invention, the data module 801 is specifically configured to parse the document according to the list of documents to be processed in the document configuration information, extract the data table-level information of the document to be processed, and store the document to be processed in the list of documents to be processed;

[0137] Based on the data table-level information of the document to be processed, calling the document extraction function to extract field information from the document to be processed in the list of documents to be processed;

[0138] The data table-level information and the field-level information are stored in the interface_column_info_lack table, and the interface_column_info_lack table is inserted into the interface_tbl_info table, so as to generate a data source configuration file according to the interface_tbl_info table.

[0139] In one embodiment of the present invention, the data module 801 is specifically configured to parse documents according to the list of to-be-processed documents in the document configuration information, extract data table-level information of the to-be-processed documents, and based on the data table-level information of the to-be-processed documents, call a document extraction function to extract data table information by row and column from the to-be-processed documents in the list of to-be-processed documents, the data table information including field values ​​and field positions.

[0140] The data table information is stored in analysis_data_list, and the analysis_data_list and the document to be processed are stored in a document list to be processed.

[0141] In one embodiment of the present invention, the data module 801 is specifically configured to determine, based on data table-level information of the to-be-processed document, whether a regular expression exists in a field of the to-be-processed document, and call a document extraction function to extract field information from the to-be-processed document in the to-be-processed document list according to the regular expression;

[0142] Based on the data table-level information of the document to be processed, it is determined that no regular expression exists in the field of the document to be processed, and a document extraction function is called to directly extract field information from the document to be processed in the list of documents to be processed.

[0143] In one embodiment of the present invention, the configuration module 802 is specifically configured to traverse the worksheets in the document configuration information to obtain worksheet configuration information, execute an import function to successfully import the data of the worksheets into the database, and successfully verify the worksheet configuration information;

[0144] Determine various data merge operations based on the worksheet, and create and successfully verify a data list.

[0145] In one embodiment of the present invention, the configuration module 802 is specifically configured to determine SQL statements for multiple data merging operations based on the worksheet;

[0146] An SQL statement for executing a merge operation on the data table-level information and verifying the merge result of the data table-level information, an SQL statement for executing a merge operation on the field-level information, obtaining the merge result of the field-level information, and constructing the data list with the merge result of the data table-level information and the merge result of the field-level information.

[0147] In one embodiment of the present invention, the generation module 803 is specifically configured to parse the data source configuration file to locate fields according to data table-level information and field-level information, determine conversion data parameters based on the source type of the field, the conversion data parameters including a conversion type and a data length, and concatenate the fields according to the conversion type and data length to obtain fields after the conversion data parameters are obtained;

[0148] The fields after the data parameters are converted are processed in combination with the logical processing operator corresponding to the source type, and the field processing results are mapped to the script output template based on the header information to generate an ETL script.

[0149] Figure 9 An exemplary system architecture 900 is shown to which the ETL script generation method or ETL script generation apparatus according to an embodiment of the present invention can be applied.

[0150] like Figure 9As shown, system architecture 900 may include terminal devices 901, 902, 903, a network 904, and a server 905. Network 904 is used to provide a medium for communication links between terminal devices 901, 902, 903 and server 905. Network 904 may include various connection types, such as wired or wireless communication links or fiber optic cables.

[0151] Users can use terminal devices 901, 902, and 903 to interact with server 905 via network 904 to receive or send messages, etc. Terminal devices 901, 902, and 903 can be installed with various communication client applications, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. (only as examples).

[0152] The terminal devices 901 , 902 , and 903 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, and desktop computers.

[0153] Server 905 may be a server that provides various services, such as a backend management server (for example only) that supports shopping websites browsed by users using terminal devices 901, 902, and 903. The backend management server may analyze and process received data, such as product information query requests, and provide feedback (for example, targeted push information and product information) to the terminal device.

[0154] It should be noted that the ETL script generation method provided in the embodiment of the present invention is generally executed by the server 905 , and accordingly, the ETL script generation device is generally provided in the server 905 .

[0155] It should be understood that Figure 9 The number of terminal devices, networks and servers in the embodiment is merely illustrative. Any number of terminal devices, networks and servers may be provided as required.

[0156] Reference below Figure 10 , which shows a schematic structural diagram of a computer system 1000 of a terminal device suitable for implementing an embodiment of the present invention. Figure 10 The terminal device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0157] like Figure 10As shown, computer system 1000 includes a central processing unit (CPU) 1001, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage unit 1008 into a random access memory (RAM) 1003. Various programs and data required for the operation of system 1000 are also stored in RAM 1003. CPU 1001, ROM 1002, and RAM 1003 are connected to each other via a bus 1004. An input / output (I / O) interface 1005 is also connected to bus 1004.

[0158] The following components are connected to the I / O interface 1005: an input section 1006 including a keyboard, mouse, and the like; an output section 1007 including devices such as a cathode ray tube (CRT), a liquid crystal display (LCD), and speakers; a storage section 1008 including a hard disk; and a communication section 1009 including a network interface card such as a LAN card or a modem. The communication section 1009 performs communication processing via a network such as the Internet. A drive 1010 is also connected to the I / O interface 1005 as needed. Removable media 1011, such as a magnetic disk, an optical disk, a magneto-optical disk, or a semiconductor memory, is installed in the drive 1010 as needed, so that computer programs read from the removable media can be installed in the storage section 1008 as needed.

[0159] In particular, according to embodiments disclosed herein, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed herein include a computer program product comprising a computer program embodied on a computer-readable medium, the computer program containing program code for executing the methods illustrated in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via communication section 1009 and / or installed from removable media 1011. When executed by central processing unit (CPU) 1001, the computer program performs the aforementioned functions defined in the system of the present invention.

[0160] It should be noted that the computer-readable medium described in the present invention may be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. Computer-readable storage media may include, for example, but not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or components, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to, an electrical connection having one or more conductors, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present invention, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, device, or component. In the present invention, a computer-readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such a propagated data signal may take a variety of forms, including, but not limited to, electromagnetic signals, optical signals, or any suitable combination thereof. A computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium that can transmit, propagate, or transport a program for use by or in conjunction with an instruction execution system, apparatus, or device. Program code embodied on a computer-readable medium may be transmitted using any suitable medium, including but not limited to wireless, wireline, optical fiber cable, RF, or any suitable combination thereof.

[0161] The flowcharts and block diagrams in the accompanying drawings illustrate the possible implementation architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present invention. In this regard, each box in the flowchart or block diagram can represent a module, program segment, or a part of code, and the above-mentioned module, program segment, or a part of code contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in an order different from that 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 flowchart, and the combination of boxes in the block diagram or flowchart, can be implemented with a dedicated hardware-based system that performs the specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0162] The modules involved in the embodiments of the present invention may be implemented in software or in hardware. The modules described may also be provided in a processor. For example, they may be described as follows: a processor includes a data module, a configuration module, and a generation module. The names of these modules do not, in some cases, constitute limitations on the modules themselves. For example, the data module may also be described as "used to parse documents according to document configuration information, and integrate data table-level information and field-level information in the document to generate a data source configuration file."

[0163] As another aspect, the present invention further provides a computer-readable medium, which may be included in the device described in the above embodiments, or may exist independently without being incorporated into the device. The computer-readable medium carries one or more programs, and when the one or more programs are executed by the device, the device includes:

[0164] Parse the document according to the document configuration information, integrate the data table-level information and field-level information in the document to generate a data source configuration file;

[0165] After traversing the worksheets in the document configuration information and verifying the worksheet configuration information, determining a data merging operation and establishing a data list according to the worksheets to construct a script output template;

[0166] The data source configuration file is parsed to locate the fields according to the data table-level information and field-level information, the conversion data parameters are determined based on the source type of the field, the fields after the conversion data parameters are processed in combination with the logical processing operator corresponding to the source type, and the field processing results are filled in the script output template to generate an ETL script.

[0167] According to the technical solution of an embodiment of the present invention, a document is parsed based on document configuration information, and the data table-level information and field-level information in the document are integrated to generate a data source configuration file. After traversing the worksheets in the document configuration information and verifying the worksheet configuration information, a data merge operation is determined and a data list is established based on the worksheet to construct a script output template. The data source configuration file is parsed to locate fields according to the data table-level information and field-level information, and conversion data parameters are determined based on the source type of the field. The fields after the conversion data parameters are processed in combination with the logical processing operator corresponding to the source type, and the field processing results are filled into the script output template to generate an ETL script. The use of logical processing operators can process fields of different source types, thereby improving the scalability and adaptability of ETL script development.

[0168] The above specific implementation methods do not constitute a limitation on the scope of protection of the present invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations and substitutions may occur depending on design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included in the scope of protection of the present invention. It should be noted that in the technical solution disclosed herein, the acquisition, storage and application of user personal information involved are in compliance with the provisions of relevant laws and regulations and do not violate public order and good morals.

Claims

1. A method for generating an ETL script, characterized in that: include: Parse the document according to the list of documents to be processed in the document configuration information, extract the data table-level information of the document to be processed and store the document to be processed in the list of documents to be processed; Based on the data table-level information of the to-be-processed document, calling a document extraction function to extract field information from the to-be-processed document in the to-be-processed document list; storing the data table-level information and the field-level information in the interface_column_info_lack table, and inserting the interface_column_info_lack table into the interface_tbl_info table to generate a data source configuration file according to the interface_tbl_info table; After traversing the worksheets in the document configuration information and verifying the worksheet configuration information, determining a data merging operation and establishing a data list according to the worksheets to construct a script output template; Parsing the data source configuration file to locate fields according to data table-level information and field-level information, determining conversion data parameters based on the source type of the field, processing the fields after the conversion data parameters in combination with the logical processing operators corresponding to the source type, and filling the script output template with the field processing results to generate an ETL script; The step of parsing the document according to the list of documents to be processed in the document configuration information, extracting data table-level information of the document to be processed, and storing the document to be processed in the list of documents to be processed includes: Parsing documents according to the list of pending documents in the document configuration information, extracting data table-level information of the pending documents, and calling a document extraction function based on the data table-level information of the pending documents to be processed to extract data table information by row and column from the pending documents in the list of pending documents, wherein the data table information includes field values ​​and field positions; The data table information is stored in analysis_data_list, and the analysis_data_list and the document to be processed are stored in a list of documents to be processed; The step of calling a document extraction function based on the data table-level information of the document to be processed to extract field information from the document to be processed in the list of documents to be processed includes: Determining, based on data table-level information of the document to be processed, whether a regular expression exists in a field of the document to be processed, and calling a document extraction function to extract field information of the document to be processed in the list of documents to be processed according to the regular expression; Based on the data table-level information of the document to be processed, it is determined that no regular expression exists in the field of the document to be processed, and a document extraction function is called to directly extract field information from the document to be processed in the list of documents to be processed.

2. The ETL script generation method according to claim 1, characterized in that: After traversing the worksheet in the document configuration information to verify the worksheet configuration information, determining a data merging operation and establishing a data list according to the worksheet includes: Traversing the worksheets in the document configuration information to obtain worksheet configuration information, executing an import function to successfully import the data of the worksheet into a database, and successfully verifying the worksheet configuration information; Determine various data merge operations based on the worksheet, and create and successfully verify a data list.

3. The ETL script generation method according to claim 2, characterized in that: The step of determining multiple data merging operations based on the worksheet, establishing and successfully verifying a data list, includes: Determine the SQL statements for multiple data merging operations based on the worksheet; An SQL statement for executing a merge operation on the data table-level information and verifying the merge result of the data table-level information, an SQL statement for executing a merge operation on the field-level information, obtaining the merge result of the field-level information, and constructing the data list with the merge result of the data table-level information and the merge result of the field-level information.

4. The ETL script generation method according to claim 1, characterized in that: The step of parsing the data source configuration file to locate fields according to data table-level information and field-level information, converting data parameters based on the source type of the field, processing the fields after the data parameters are converted in combination with the logical processing operators corresponding to the source type, and filling the script output template with the field processing results to generate an ETL script includes: Parsing the data source configuration file to locate fields according to data table-level information and field-level information, determining conversion data parameters based on the source type of the field, the conversion data parameters including a conversion type and a data length, and concatenating the fields according to the conversion type and data length to obtain fields after the conversion data parameters are obtained; The fields after the data parameters are converted are processed in combination with the logical processing operator corresponding to the source type, and the field processing results are mapped to the script output template based on the header information to generate an ETL script.

5. An ETL script generation device, characterized in that: include: A data module is configured to parse documents according to the list of pending documents in the document configuration information, extract data table-level information of the pending documents, and store the pending documents in the list of pending documents; based on the data table-level information of the pending documents, call a document extraction function to extract field information from the pending documents in the list of pending documents; store the data table-level information and the field-level information in the interface_column_info_lack table, and insert the interface_column_info_lack table into the interface_tbl_info table to generate a data source configuration file according to the interface_tbl_info table; and, parsing the document according to the list of pending documents in the document configuration information, extracting data table-level information of the pending document; based on the data table-level information of the pending document, calling a document extraction function to extract data table information by row and column from the pending document in the list of pending documents, the data table information including field values ​​and field positions; storing the data table information in analysis_data_list, and storing the analysis_data_list and the pending document in the list of pending documents; and, based on the data table-level information of the document to be processed, determining whether a regular expression exists in a field of the document to be processed, and calling a document extraction function to extract field information from the document to be processed in the list of documents to be processed according to the regular expression; Determining based on data table-level information of the document to be processed that a regular expression does not exist in a field of the document to be processed, calling a document extraction function to directly extract field information from the document to be processed in the list of documents to be processed; A configuration module, configured to traverse the worksheets in the document configuration information and verify the worksheet configuration information, and then determine a data merging operation and create a data list according to the worksheets to construct a script output template; A generation module is used to parse the data source configuration file to locate fields according to data table-level information and field-level information, determine conversion data parameters based on the source type of the field, process the fields after the conversion data parameters in combination with the logical processing operator corresponding to the source type, and fill the script output template with the field processing results to generate an ETL script.

6. An electronic device for generating an ETL script, characterized in that: include: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the method according to any one of claims 1 to 4.

7. A computer-readable medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method according to any one of claims 1 to 4 is implemented.

Images