ETL script generation method, device and equipment and computer readable medium

Abstract

The invention discloses an ETL script generation method, device and equipment and a computer readable medium, and relates to the technical field of computers. A specific embodiment of the method comprises the following steps: analyzing a document according to document configuration information, and integrating data table level information and field level information in the document to generate a data source configuration file; after a worksheet in the document configuration information is traversed and worksheet configuration information is verified, a data merging operation is determined and a data list is established according to the worksheet so as to construct a script output template; analyzing the data source configuration file to position a field according to data table level information and field level information, determining a conversion data parameter based on a source type of the field, processing the field after the data parameter is converted by combining a logic processing operator corresponding to the source type, filling the script output template with a field processing result, and generating the ETL script. According to the embodiment, the expandability and adaptability of ETL script development can be improved.

Description

Technical Field

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

[0002] In today's data-driven era, ETL (Extract-Transform-Load) programs have become key tools for enterprises 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 difficult to meet the requirements of scalability in the face of growing data volume and data complexity. Summary of the Invention

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

[0005] To achieve the above object, according to one aspect of the embodiments of the present invention, there is provided a method for generating an ETL script, including: Parsing a document according to document configuration information, integrating 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 a data merging operation and establishing a data list according to the worksheet to construct a script output template; Parsing the data source configuration file to locate fields according to table-level information and field-level information, determining conversion data parameters based on the source type of the fields, processing the fields after the conversion data parameters with the logical processing operator corresponding to the source type, and filling the field processing results into the script output template to generate an ETL script.

[0006] The parsing the document according to document configuration information, integrating table-level information and field-level information in the document to generate a data source configuration file includes: Parsing a document according to a list of documents to be processed in the document configuration information, extracting table-level information of the documents to be processed, and storing the documents to be processed in the list of documents to be processed; Based on the table-level information of the documents to be processed, calling a document extraction function to extract field information from the documents to be processed in the list of documents to be processed; 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.

[0007] Parsing the document according to the list of documents to be processed in the document configuration information, extracting the 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 includes: Parsing the document according to the list of documents to be processed in the document configuration information, extracting the data table - level information of the documents to be processed. Based on the data table - level information of the documents to be processed, calling the document extraction function to extract data table information row - by - row and column - by - column from the documents to be processed in the list of documents to be processed. The data table information includes field values and the positions of the fields. Store the data table information in the analysis_data_list, and store the analysis_data_list and the documents to be processed in the list of documents to be processed.

[0008] Based on the data table - level information of the documents to be processed, calling the document extraction function to extract field information from the documents to be processed in the list of documents to be processed includes: Determine that there is a regular expression for fields in the document to be processed based on the data table - level information of the document to be processed, and call the document extraction function to extract field information from the documents to be processed in the list of documents to be processed according to the regular expression. Determine that there is no regular expression for fields in the document to be processed based on the data table - level information of the document to be processed, and call the document extraction function to directly extract field information from the documents to be processed in the list of documents to be processed.

[0009] After traversing the worksheet check worksheet configuration information in the document configuration information, determining data merging operations and establishing a data list according to the worksheet includes: Traverse the worksheets in the document configuration information to obtain worksheet configuration information, execute the import function to successfully import the data of the worksheet into the database, and then successfully verify the worksheet configuration information. Determine multiple data merging operations according to the worksheet, and establish and successfully verify a data list.

[0010] Determining multiple data merging operations according to the worksheet, establishing and successfully verifying a data list includes: Determine the SQL statements for multiple data merging operations according to the worksheet; Execute an SQL statement for merging data at the data table level and verify the result of data merging at the data table level. Obtain the result of data merging at the field level by executing an SQL statement for merging data at the field level. Construct the data list based on the result of data merging at the data table level and the result of data merging at the field level.

[0011] 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 fields, processing the fields with the converted data parameters in combination with the logical processing operator corresponding to the source type, and filling the field processing results into the script output template to generate an ETL script, including: 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 fields, where the conversion data parameters include a conversion type and a data length, and concatenating the fields according to the conversion type and the data length to obtain the fields with the converted data parameters; Processing the fields with the converted data parameters in combination with the logical processing operator corresponding to the source type, and mapping the field processing results to the script output template based on the header information to generate an ETL script.

[0012] According to a second aspect of an embodiment of the present invention, there is provided an ETL script generation device, including: A data module, configured to parse a document 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; A configuration module, configured to traverse worksheets in the document configuration information, verify worksheet configuration information, and determine a data merging operation and establish a data list according to the worksheets to construct a script output template; A generation module, 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 fields, process the fields with the converted data parameters in combination with the logical processing operator corresponding to the source type, and fill the field processing results into the script output template to generate an ETL script.

[0013] According to a third aspect of an embodiment of the present invention, there is provided an ETL script generation method electronic device, including: One or more processors; A storage device, configured to store 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 as described above.

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

[0015] One embodiment of the above invention has the following advantages or beneficial effects: parsing a document according to document configuration information, integrating table-level information and field-level information in the document to generate a data source configuration file; traversing worksheets in the document configuration information and verifying worksheet configuration information, and then determining a data merging operation and establishing a data list according to the worksheet to construct a script output template; parsing the data source configuration file to locate fields according to table-level information and field-level information, determining conversion data parameters based on the source type of the fields, processing the fields after the conversion data parameters are processed by combining the logical processing operator corresponding to the source type, and filling the processing results of the fields into the script output template to generate an ETL script. Using logical processing operators can correspondingly process fields of different source types, thereby improving the scalability and adaptability of ETL script development.

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

[0017] The drawings are used to better understand the present invention and do not unduly limit the present invention. Among them: Figure 1 is a schematic diagram of the main process of the ETL script generation method according to an embodiment of the present invention; Figure 2 is a schematic diagram of the process of integrating table-level information and field-level information in a document according to an embodiment of the present invention; Figure 3 is a schematic diagram of the process of extracting table-level information of a document to be processed according to an embodiment of the present invention; Figure 4 is a schematic diagram of the process of calling a document extraction function to extract field information according to an embodiment of the present invention; Figure 5 is a schematic diagram of the process of traversing worksheets in document configuration information according to an embodiment of the present invention; Figure 6 is a schematic diagram of the process of merging multiple types of data according to an embodiment of the present invention; Figure 7 is a schematic diagram of the process of processing fields by a logical processing operator corresponding to a source type according to an embodiment of the present invention; Figure 8 is a schematic diagram of the main structure of the ETL script generation device according to an embodiment of the present invention; Figure 9 is an exemplary system architecture diagram to which an embodiment of the present invention can be applied; Figure 10 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 implementation manners

[0018] The following describes exemplary embodiments of the present invention with reference to the accompanying drawings. Various details of the embodiments of the present invention are included to facilitate understanding, and they should be considered merely exemplary. Therefore, those of ordinary skill in the art should recognize that various changes and modifications can 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.

[0019] Currently, the methods for generating ETL scripts can be divided into the following three categories: Using programming languages, such as Python or Java, to write ETL scripts. By writing customized ETL scripts, data extraction, transformation, and loading operations can be performed according to actual needs. The ETL programs generated by the above methods have low standardization and poor maintainability.

[0020] Utilizing ETL tool libraries. There are many ETL tool libraries available for selection, such as Pandas, OpenPyXL, and DBConvert, etc. The tool libraries provide rich functions and simple operation methods, and can realize automatic generation of ETL scripts. However, the above tool libraries limit custom parameters and have poor flexibility.

[0021] Using ETL frameworks, such as Airflow or Apache Nifi. The above frameworks provide a complete set of solutions, including functions such as data extraction, transformation, and loading. Using these frameworks can simplify the implementation process of ETL programs, and at the same time improve the quality and maintainability of the code. However, the above frameworks require additional investment.

[0022] To solve the technical problem of poor scalability of ETL scripts, the technical solutions in the following embodiments of the present invention can be adopted.

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

[0024] 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.The ETL process mainly includes three steps: Extract, Transform, and Load. In the extraction phase, the ETL tool extracts data from various data sources, such as relational databases and file systems. In the transformation phase, the tool cleans, processes, and integrates the extracted data to meet the user's requirements. Finally, in the loading phase, the ETL tool loads the processed data into the target data storage, such as a data warehouse or a data lake.

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

[0026] The document includes multiple data tables, and each data table includes multiple fields. To improve the accuracy of generating the ETL script, it is necessary to obtain the data table-level information and field-level information in the document to generate a data source configuration file. The data source configuration file takes into account not only the data tables but also the fields, thereby fully restoring the consistency between the data source configuration file and the document.

[0027] See Figure 2 , Figure 2 is a schematic flow diagram of integrating the data table-level information and field-level information in the document according to an embodiment of the present invention. Specifically, it includes the following steps: S201. 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.

[0028] The document to be processed is the document for generating the ETL script. The document can be parsed according to the list of documents to be processed in the document configuration information, and then the data table-level information of the document to be processed can be extracted. As an example, parse the document according to the list of documents to be processed, obtain multiple documents to be processed in sequence, and then obtain the data table-level information in the multiple documents to be processed.

[0029] In an embodiment of the present invention, parse the document according to the list of documents to be processed in the document configuration information, and obtain the number of documents to be processed. Furthermore, obtain the detailed configuration information of the document to be processed. As an example, the detailed configuration information of the document to be processed includes the data table-level information of the document to be processed.

[0030] To facilitate subsequent acquisition of the field level, store the document to be processed in the list of documents to be processed, so that the field-level information can be extracted based on the document to be processed in the list of documents to be processed.

[0031] See Figure 3 , Figure 3It is a schematic flowchart of extracting data table-level information of a document to be processed according to an embodiment of the present invention. Specifically, it includes the following steps: S301. Analyze 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. Based on the data table-level information of the document to be processed, call the document extraction function to extract the data table information row by row and column by column from the documents to be processed in the list of documents to be processed. The data table information includes field values and the positions of the fields.

[0032] In an embodiment of the present invention, the document extraction function is used to extract the data table information row by row and column by column, thereby improving the accuracy of the data table information. Specifically, analyze 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. Based on the data table-level information of the document to be processed, call the document extraction function to extract the data table information row by row and column by column from the documents to be processed in the list of documents to be processed. The data table information includes field values and the positions of the fields.

[0033] Assign the configuration information related to the row to be extracted in the document to be processed to the analysis_data_list, so as to extract the corresponding fields in the rows of the data table information in analysis_data_list subsequently.

[0034] Process the documents to be processed in the list of documents to be processed in sequence to complete various operations on the tabular data. Process each row of data in the document to be processed one by one. As an example, the valid field values in each row of data are stored in the field value temporary list, and the positions of the valid fields in each row of data are stored in the field position temporary list. This is convenient for subsequent operations such as data association based on the field positions.

[0035] When a certain condition (first_row_flg = True) is met, assign the field temporary list to the sub_col_num_list variable for subsequent data association analysis and other operations.

[0036] If first_row_flg is False, then extract the values corresponding to sub_col_num_list in the current row and store them in the analysis_data_list list.

[0037] S302. Store the data table information in the analysis_data_list, and store the analysis_data_list and the document to be processed in the list of documents to be processed.

[0038] Store the corresponding values into the analysis_data_list according to the sub_col_num_list and integrate the data. That is, store the data table information into the analysis_data_list.

[0039] Store the field column values into the list data_list. Store data_list into the analysis_data_list. Perform a column transformation operation on the data in the analysis_data_list to make its format more compliant with the storage requirements. The analysis_data_list includes the field values and the positions of the fields in the data table information. The analysis_data_list and the document to be processed can be stored in the list of documents to be processed for extracting field information.

[0040] In Figure 3 the embodiment, extract the data table information by rows and columns to improve the accuracy of the data table-level information.

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

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

[0043] See Figure 4 , Figure 4 is a schematic flowchart of extracting field information by calling the document extraction function according to the embodiment of the present invention. It specifically includes the following steps: S401. Based on the data table-level information of the document to be processed, determine that there is a regular expression for the fields in the document to be processed, and call the document extraction function to extract the field information from the document to be processed in the list of documents to be processed according to the regular expression.

[0044] In the embodiment of the present invention, use regular expressions to extract field information. Using regular expressions can extract information that conforms to a specific pattern from a large amount of data to accurately achieve data extraction and cleaning.

[0045] Based on the data table-level information of the document to be processed, determine that there is a regular expression for the fields in the document to be processed, and call the document extraction function to extract the field information from the document to be processed in the list of documents to be processed according to the regular expression.

[0046] 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 for the fields in the document to be processed. The document extraction function is called to extract the data at the corresponding positions from the data_list and store it in the db_data_list. After performing a column conversion operation on the list db_data_list, it is written into the database to achieve the storage of field information.

[0047] S402. Based on the data table-level information of the document to be processed, it is determined that there is no regular expression for the fields in the document to be processed. 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.

[0048] Based on the data table-level information of the document to be processed, it is determined that there is no regular expression for the fields in the document to be processed. 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.

[0049] 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 for the fields in the document to be processed. The document extraction function is called to extract the data at the corresponding positions from the data_list and store it in the db_data_list. After performing a column conversion operation on the list db_data_list, it is written into the database to achieve the storage of field information.

[0050] In Figure 4 the embodiment, the data extraction and cleaning are accurately implemented by using regular expressions.

[0051] S203. Store the 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.

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

[0053] 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. As an example, loop through the data in the interface_info_lack table in sequence to insert the data in the interface_column_info_lack table into the interface_tbl_info table. Among them, the interface_tbl_info list includes the database name (dbname) and the data table name (Tblname).

[0054] In Figure 2 's embodiment, integrate the data table-level information and field-level information in the document to generate a data source configuration file.

[0055] S102. After traversing the worksheets in the document configuration information and verifying the configuration information of the worksheets, determine the data merging operation and establish a data list according to the worksheets to construct a script output template.

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

[0057] The worksheets in the document configuration information include configuration information. After traversing the worksheets in the document configuration information and verifying the configuration information of the worksheets, determine the data merging operation and establish a data list according to the worksheets to construct a script output template.

[0058] See Figure 5 , Figure 5 is a flowchart of traversing the worksheets in the document configuration information according to the embodiment of the present invention. Specifically, it includes the following steps: S501. Traverse the worksheets in the document configuration information to obtain the worksheet configuration information, and execute the import function to successfully import the data of the worksheet into the database, then the worksheet configuration information is successfully verified.

[0059] In the embodiment of the present invention, in order to ensure that the data is successfully imported into the database, it is necessary to verify the validity of the worksheet configuration information. The data of the worksheet can be imported using the worksheet configuration information, and if the data is successfully imported into the database, the worksheet configuration information is successfully verified.

[0060] As an example, traverse the worksheets in the document configuration information to obtain the worksheet configuration information, and execute the import function to successfully import the data of the worksheet into the database, then the worksheet configuration information is successfully verified.

[0061] Specifically, traverse all worksheets in the document configuration information to ensure that the data in each worksheet can be processed. Trigger data transmission using the import function and import the data of the worksheet into the database.

[0062] As an example, dynamically generate SQL statements for inserting into the database according to the data field structure. Execute the SQL statements to write the data of the worksheet into the database. According to the success or failure of the data writing operation, return a boolean value to indicate the operation result.

[0063] Verify whether the data is successfully imported into the database to ensure that the data transmission is completed and correctly stored. If the data is successfully imported into the database, then the worksheet configuration information is successfully verified, thereby ensuring the accuracy and integrity of the data migration.

[0064] S502. Determine multiple data merging operations according to the worksheet, and establish and successfully verify the data list.

[0065] In the embodiments of the present invention, the worksheet involves multiple types of data. As an example, the data includes one or more of the following: model data, task data, field-level data, and deployment data. Among them, the model data involves the definition of data structure and business logic. The task data is data related to process control and task scheduling. The field-level data is specific attribute information. The deployment data is data related to the configuration and operating environment of the system.

[0066] According to the worksheet, multiple data merging operations can be determined, and then the data list is established and verified to determine the effectiveness of the merging operation.

[0067] See Figure 6 , Figure 6 is a schematic flowchart of the process of merging multiple types of data according to an embodiment of the present invention. Specifically, it includes the following steps: S601. Determine the SQL statements for multiple data merging operations according to the worksheet.

[0068] Obtain the SQL statement for merging model data, the SQL statement for merging task data, the SQL statement for merging field-level data, the SQL statement for merging deployment data, and obtain the field model conversion data list according to the worksheet.

[0069] S602. Execute the SQL statements for the merging operation for the data table-level information and verify the data table-level information merging result, execute the SQL statements for the merging operation for the field-level information to obtain the field-level information merging result, and construct a data list with the data table-level information merging result and the field-level information merging result.

[0070] The merging SQL statements for data information at the data table level are executed in a loop to ensure that the data at the data table level is integrated according to the preset logic. Among them, the above merging SQL statements include: the merging SQL statement for obtaining model data from the worksheet, the merging SQL statement for obtaining task data, the merging SQL statement for obtaining field-level data, and the merging SQL statement for obtaining deployment data.

[0071] Judge whether the table name of the sdata table of the merging result of data table-level information is in a normal state. If the table name of the sdata table of the merging result of data table-level information is in a normal state, it is verified that the merging result of data table-level information is successful; if the table name of the sdata table of the merging result of data table-level information is in an abnormal state, it is verified that the merging result of data table-level information fails, and corresponding exception handling is required.

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

[0073] The merging SQL statements for field-level data information are executed in a loop to ensure that the field-level data is integrated according to the preset logic.

[0074] Judge whether the distribution key of the ods layer of the current row field is y to determine whether subsequent data conversion and other operations are required. The field ods is used to integrate relevant data scattered in multiple source systems. Call the field conversion function to perform conversion processing on the field data so that it meets the requirements of the ods layer data model or storage.

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

[0076] Store the merging result of field-level information in the column_info table of the database to complete the storage of field-level data.

[0077] Then, build a data list with the table storing the merging result of data table-level information and the table storing the merging result of field-level information. Furthermore, build a script output template based on the data list. As an example, use the data list as the script output template.

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

[0079] S103. Parse the data source configuration file to locate fields according to the data table-level information and field-level information, determine the conversion data parameters based on the source type of the fields, process the fields after the conversion data parameters are processed by combining the logical processing operators corresponding to the source type, and fill the field processing results into the script output template to generate an ETL script.

[0080] In the embodiments of the present invention, the data source configuration file includes data table-level information and field-level information, and then determines the corresponding conversion data parameters by locating the fields. Different source types of the fields result in different logical processing operators. Process the fields after the conversion data parameters are processed by the logical processing operators corresponding to the source type, and fill the field processing results into the script output template to generate an ETL script.

[0081] See Figure 7 , Figure 7 is a schematic flow chart of processing fields based on the logical processing operators corresponding to the source type according to the embodiments of the present invention. Specifically, it includes the following steps: S701. Parse the data source configuration file to locate fields according to the data table-level information and field-level information, determine the conversion data parameters based on the source type of the fields. The conversion data parameters include the conversion type and the data length, and splice the fields according to the conversion type and the data length to obtain the fields after the conversion data parameters are processed.

[0082] Parse the data source configuration file to obtain the data table-level information and field-level information. The data table-level information and field-level information include the field positions, and locate the fields according to the field positions. For the fields, the field name refers to the field name in the source data table, and each field has a specific name to identify the data type or content it stores. The source type refers to the type of data in these fields, such as character type, numeric type, date type, etc.

[0083] The conversion data parameters can be determined based on the source type of the fields. The conversion data parameters include the conversion type and the data length. There is a corresponding relationship between the source type and the conversion data parameters. According to the source type of the fields, perform the corresponding data type conversion and length setting operations. Each type has a corresponding conversion rule to ensure the consistency and correctness of the data.

[0084] As an example, for each source type, there is a corresponding conversion type and data length. For example: when the source type is A, the conversion type1 = 3, and the data length1 = the source length.

[0085] For the types involving decimal places, according to whether the decimal places exist and the specific values of the decimal places, perform different conversion logics to ensure the accuracy and correct format of the data.

[0086] Fields can be concatenated according to the conversion type and data length to obtain the fields after the conversion data parameters. Integrate the conversion type and data length of the fields to form a complete data structure and generate the final file, that is, the fields after the conversion data parameters.

[0087] S702. Combine the logical processing operator corresponding to the source type to process the fields after the conversion data parameters, and map the field processing results to the script output template based on the header information to generate an ETL script.

[0088] In the embodiments of the present invention, set the logical processing operator based on the source type, and then combine the logical processing operator corresponding to the source type to process the fields after the conversion data parameters. Map the field processing results to the script output template based on the header information to generate an ETL script.

[0089] The logical processing operator can be used to flexibly process and operate on fields according to actual needs. As an example, the logical processing operator involves data cleaning, data integration, data transformation, data mining, and data analysis.

[0090] In Figure 7 the embodiments, use the logical processing operator corresponding to the source type to process the fields, improving the adaptability of generating the ETL script.

[0091] In the above embodiments of the present invention, 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; traverse the worksheets in the document configuration information and verify the worksheet configuration information, and then determine the data merging operation and establish a data list according to the worksheet to construct a script output template; 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 fields, combine the logical processing operator corresponding to the source type to process the fields after the conversion data parameters, and fill the field processing results into the script output template to generate an ETL script. Using the logical processing operator can correspondingly process fields of different source types, improving the scalability and adaptability of ETL script development.

[0092] See Figure 8 , Figure 8 is the main structural schematic diagram of the ETL script generation device according to the embodiments 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: A data module 801, configured to parse a document 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; A configuration module 802, which is used to traverse worksheets in the document configuration information, verify the worksheet configuration information, and then determine a data merging operation and create a data list based on the worksheet to construct a script output template. A generation module 803, which 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 fields, process the fields after the conversion data parameters are processed in combination with the logic processing operator corresponding to the source type, and fill the field processing results into the script output template to generate an ETL script.

[0093] In an embodiment of the present invention, the data module 801 is specifically configured to parse a document according to a list of documents to be processed in the document configuration information, extract data table-level information of the documents to be processed, and store the documents to be processed in the list of documents to be processed. Based on the data table-level information of the documents to be processed, call a document extraction function to extract field information from the documents to be processed in the list of documents to be processed. 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.

[0094] In an embodiment of the present invention, the data module 801 is specifically configured to parse a document according to a list of documents to be processed in the document configuration information, extract data table-level information of the documents to be processed, and based on the data table-level information of the documents to be processed, call a document extraction function to extract data table information from the documents to be processed in the list of documents to be processed by rows and columns respectively, where the data table information includes field values and the positions of the fields. Store the data table information in the analysis_data_list, and store the analysis_data_list and the documents to be processed in the list of documents to be processed.

[0095] In an embodiment of the present invention, the data module 801 is specifically configured to determine that there is a regular expression in the fields in the document to be processed based on the data table-level information of the document to be processed, and call 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. Based on the data table-level information of the document to be processed, determine that there is no regular expression in the fields in 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.

[0096] 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 worksheet into the database, and then successfully verify the worksheet configuration information; Determine various data merging operations according to the worksheet, and establish and successfully verify a data list.

[0097] In one embodiment of the present invention, the configuration module 802 is specifically configured to determine SQL statements for various data merging operations according to the worksheet; Execute the SQL statement for the data merging operation at the data table level information and verify the data merging result at the data table level information, execute the SQL statement for the data merging operation at the field level information to obtain the data merging result at the field level information, and construct the data list with the data merging result at the data table level information and the data merging result at the field level information.

[0098] 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 the data table level information and the field level information, determine conversion data parameters based on the source type of the fields, the conversion data parameters include a conversion type and a data length, and splice the fields according to the conversion type and the data length to obtain the fields after the conversion data parameters; Process the fields after the conversion data parameters in combination with the logic processing operator corresponding to the source type, and map the field processing result to the script output template based on the header information to generate an ETL script.

[0099] Figure 9 An exemplary system architecture 900 to which the ETL script generation method or the ETL script generation device according to the embodiments of the present invention can be applied is shown.

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

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

[0102] The terminal devices 901, 902, and 903 can be various electronic devices with a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and so on.

[0103] The server 905 can be a server that provides various services. For example, it can be a background management server (only for example) that supports shopping websites browsed by users using the terminal devices 901, 902, and 903. The background management server can analyze and process data such as product information query requests received, and feedback the processing results (such as target push information, product information - only for example) to the terminal devices.

[0104] It should be noted that the ETL script generation method provided by the embodiments of the present invention is generally executed by the server 905. Correspondingly, the ETL script generation device is generally set in the server 905.

[0105] It should be understood that Figure 9 the numbers of the terminal devices, networks, and servers in

[0106] are merely illustrative. According to the implementation requirements, there can be any number of terminal devices, networks, and servers. Figure 10 is a schematic structural diagram of a computer system 1000 of a terminal device suitable for implementing the embodiments of the present invention. Figure 10 The terminal device shown is merely an example and should not impose any limitation on the functions and usage scope of the embodiments of the present invention.

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

[0108] The following components are connected to the I / O interface 1005: an input section 1006 including a keyboard, a mouse, etc.; an output section 1007 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc. as well as a speaker, etc.; a storage section 1008 including a hard disk, etc.; and a communication section 1009 including a network interface card such as a LAN card, a modem, etc. 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 required. A removable medium 1011, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 1010 as required so that a computer program read out therefrom is installed into the storage section 1008 as required.

[0109] Specifically, according to an embodiment disclosed by the present invention, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, an embodiment disclosed by the present invention includes a computer program product that includes a computer program carried on a computer-readable medium, and the computer program includes program codes for performing the methods shown in the flowcharts. In such an embodiment, the computer program can be downloaded and installed from a network via the communication section 1009, and / or installed from the removable medium 1011. When the computer program is executed by a central processing unit (CPU) 1001, the above-described functions defined in the system of the present invention are executed.

[0110] It should be noted that the computer-readable medium shown in the present invention can be a computer-readable signal medium, a computer-readable storage medium, or any combination of the two. A computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the above. More specific examples of a computer-readable storage medium can include, but are not limited to: an electrical connection having 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 or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium can be any tangible medium that contains or stores a program, and this program can be used by or in conjunction with an instruction execution system, apparatus, or device. In the present invention, a computer-readable signal medium can include a data signal propagated in a baseband or as part of a carrier wave, which carries computer-readable program code. Such a propagated data signal can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. A computer-readable signal medium can also be any computer-readable medium other than a computer-readable storage medium, and this computer-readable medium can send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, apparatus, or device. The program code contained on a computer-readable medium can be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0111] The flowcharts and block diagrams in the accompanying drawings illustrate the possible architectures, functions, and operations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram can represent a module, a program segment, or a part of code, and the above-mentioned module, program segment, or part of code contains one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions marked in the blocks can occur in a different order than that marked in the accompanying drawings. For example, two consecutive blocks shown can actually be executed substantially in parallel, and they can sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in the block diagram or flowchart, as well as the combination of blocks in the block diagram or flowchart, can be implemented by a dedicated hardware-based system for performing the specified functions or operations, or can be implemented by a combination of dedicated hardware and computer instructions.

[0112] The modules involved in the embodiments of the present invention can be implemented in software or in hardware. The described modules can also be provided in a processor. For example, it can be described as: a processor includes a data module, a configuration module, and a generation module. Among them, the names of these modules do not constitute a limitation to the module itself in some cases. For example, the data module can also be described as "used to parse a document according to document configuration information, integrate the data table-level information and field-level information in the document to generate a data source configuration file".

[0113] As another aspect, the present invention also provides a computer-readable medium, which can be included in the device described in the above embodiments; or can exist alone without being assembled into the device. The above computer-readable medium carries one or more programs. When the above one or more programs are executed by a device, the device includes: Parse a document according to document configuration information, integrate the data table-level information and field-level information in the document to generate a data source configuration file; Traverse the worksheets in the document configuration information and verify the worksheet configuration information, then determine a data merging operation and establish a data list according to the worksheet to construct a script output template; Parse the data source configuration file to locate fields according to the data table-level information and field-level information, determine conversion data parameters based on the source type of the fields, process the fields after the conversion data parameters are processed in combination with the logical processing operator corresponding to the source type, and fill the field processing results into the script output template to generate an ETL script.

[0114] According to the technical solution of the embodiments of the present invention, parse a document according to document configuration information, integrate the data table-level information and field-level information in the document to generate a data source configuration file; traverse the worksheets in the document configuration information and verify the worksheet configuration information, then determine a data merging operation and establish a data list according to the worksheet to construct a script output template; parse the data source configuration file to locate fields according to the data table-level information and field-level information, determine conversion data parameters based on the source type of the fields, process the fields after the conversion data parameters are processed in combination with the logical processing operator corresponding to the source type, and fill the field processing results into the script output template to generate an ETL script. Using logical processing operators can correspondingly process fields of different source types, thereby improving the scalability and adaptability of ETL script development.

[0115] The above specific embodiments do not constitute a limitation on the protection scope of the present invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can occur depending on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principle of the present invention shall be included within the protection scope of the present invention. It should be noted that in the technical solutions of the present disclosure, the acquisition, storage, and application of the user's personal information comply with the provisions of relevant laws and regulations and do not violate public order and good customs.

Claims

1. An ETL script generation method, characterized in that Including: Parsing the document according to the document configuration information, integrating the 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 a data merging operation and establishing a data list according to the worksheet to construct a script output template; Parsing the data source configuration file to locate fields according to the data table-level information and field-level information, determining conversion data parameters based on the source type of the fields, processing the fields after the conversion data parameters are processed by combining the logical processing operators corresponding to the source type, and filling the field processing results into the script output template to generate an ETL script.

2. The ETL script generation method according to claim 1, wherein The parsing the document according to the document configuration information, integrating the data table-level information and field-level information in the document to generate a data source configuration file includes: Parsing the document according to the list of documents to be processed in the document configuration information, extracting the 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; Based on the data table-level information of the documents to be processed, calling a document extraction function to extract field information from the documents to be processed in the list of documents to be processed; 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.

3. The ETL script generation method according to claim 2, wherein The parsing the document according to the list of documents to be processed in the document configuration information, extracting the 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 includes: Parsing the document according to the list of documents to be processed in the document configuration information, extracting the data table-level information of the documents to be processed, and based on the data table-level information of the documents to be processed, calling a document extraction function to extract data table information row by row and column by column from the documents to be processed in the list of documents to be processed, where the data table information includes field values and the positions of the fields; Storing the data table information in the analysis_data_list, and storing the analysis_data_list and the documents to be processed in the list of documents to be processed.

4. The ETL script generation method according to claim 2, wherein The calling a document extraction function to extract field information from the documents to be processed in the list of documents to be processed based on the data table-level information of the documents to be processed includes: Determining that there is a regular expression for the fields in the documents to be processed based on the data table-level information of the documents to be processed, and calling a document extraction function to extract field information from the documents to be processed in the list of documents to be processed according to the regular expression; Determining that there is no regular expression for the fields in the documents to be processed based on the data table-level information of the documents to be processed, and calling a document extraction function to directly extract field information from the documents to be processed in the list of documents to be processed.

5. The ETL script generation method according to claim 1, wherein 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 includes: Traverse the worksheet in the document configuration information to obtain the worksheet configuration information, execute the import function to successfully import the data of the worksheet into the database, and then successfully verify the worksheet configuration information; Determine multiple data merging operations based on the worksheet, and establish and successfully verify a data list.

6. The ETL script generation method according to claim 5, wherein The 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 according to the worksheet; Execute the SQL statements for the merging operation on the data table level information and verify the merging result of the data table level information. Execute the SQL statements for the merging operation on the field level information to obtain the merging result of the field level information. Construct the data list with the merging result of the data table level information and the merging result of the field level information.

7. The ETL script generation method according to claim 1, characterized in that, The parsing the data source configuration file to locate fields according to the data table level information and the field level information, converting data parameters based on the source type of the field, combining the logical processing operator corresponding to the source type to process the field after converting the data parameters, and filling the processed result of the field into the script output template to generate an ETL script includes: Parse the data source configuration file to locate fields according to the data table level information and the field level information, determine the data parameters for conversion based on the source type of the field. The data parameters for conversion include the conversion type and the data length, and splice the fields according to the conversion type and the data length to obtain the field after converting the data parameters; Combine the logical processing operator corresponding to the source type to process the field after converting the data parameters, and map the processed result of the field to the script output template based on the header information to generate an ETL script.

8. An ETL script generation device, characterized in that, Includes: A data module, used to parse a document according to the document configuration information, and integrate the data table level information and the field level information in the document to generate a data source configuration file; A configuration module, used to traverse the worksheets in the document configuration information, verify the worksheet configuration information, and then determine data merging operations and establish a data list based on the worksheet to construct a script output template; A generation module, used to parse the data source configuration file to locate fields according to the data table level information and the field level information, determine the data parameters for conversion based on the source type of the field, combine the logical processing operator corresponding to the source type to process the field after converting the data parameters, and fill the processed result of the field into the script output template to generate an ETL script.

9. An ETL script generation method electronic device, characterized in that, Includes: One or more processors; A storage device, used to store 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 described in any one of claims 1-7.

10. A computer-readable medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method described in any one of claims 1-7.

Images