A construction method of an ETL tool based on modular programmable expansion

Abstract

The present invention relates to the field of data processing, in particular to a method for constructing an ETL tool based on module programmable expansion. In view of the problems such as a large number of frequent queries, complex cross-conversions, and distributed real-time processing encountered in data processing in data engineering, it is divided into three types according to different problem-solving methods: query service-oriented, multi-dimensional data-oriented, and big data-oriented. The query service-oriented focuses on functions such as component assembly, source table parsing, query configuration, and result output. The multi-dimensional data-oriented focuses on rule design such as extraction, transformation, quality, loading, and configuration. The big data-oriented focuses on distributed deployment, real-time data processing, and storage.

Description

Technical Field

[0001] The present invention relates to the field of data processing, and particularly to a method for constructing an ETL tool under efficient data processing based on modular programmable expansion. Background Art

[0002] With the popular application of Internet of Things, cloud computing, and big data technologies, a large amount of data has been accumulated in production, including structured text, databases, real-time streams, etc., semi-structured emails, reports, web pages, etc., and unstructured images, videos, and audios. To achieve precise data management, fast data services, and intelligent decision-making, it is necessary to use ETL (Extract, Transform, Load) tools to logically or physically organically centralize data from different sources, with different formats and similar characteristics, so as to achieve data format standardization, access consistency, and storage centralization.

[0003] The booming development of the big data market has promoted the progress of domestic and foreign software and hardware technologies, and data integration and management tools such as Oracle DataIntegrator (ODI), Informatica, Oracle Goldengate, DataPipline, RestCloud, Kettle, and DataX have emerged. These tools have functions such as data disaster recovery backup, import and export, and synchronization processing, but they still have difficulties in basic functions such as processing flexible requirements, complex and changing transformation logics, and real-time data, and there are problems such as complex implementation processes, low data conversion rates, inability to perform cluster management, not supporting breakpoint resumption, and requiring a large amount of manual analysis. Therefore, it is necessary to develop new ETL tools. Summary of the Invention

[0004] In view of the above problems, the present invention provides a new method for constructing an ETL tool with modular programmable expansion under efficient data processing. In data engineering, for problems such as a large number of frequent queries, complex cross-transformations, and distributed real-time processing encountered in data processing, according to the different problems to be solved, it is divided into three types: query service-oriented, multi-dimensional data-oriented, and big data-oriented. The query service-oriented focuses on functions such as component assembly, source table parsing, query configuration, and result output. The multi-dimensional data-oriented focuses on rule design such as extraction, transformation, quality, loading, and configuration. The big data-oriented focuses on distributed deployment, real-time data processing, and storage.

[0005] The present invention provides the following technical solution: An ETL tool construction method based on modular programmable expansion, comprising the following steps:

[0006] S1. Modularly divide the ETL tool. Each component includes classes, functions, and variables, and is an independent program module that can be assembled, replaced, configured, programmed, and executed. It covers the functions of internal component parameters and provides a set of standardized interfaces externally;

[0007] S2. In the data query service, create a new project or module in PyCharm, select appropriate input data engines from the component library according to the type of the source database to read components, write components, and report generation components, connect to the database where the source data is located, and store the query results in a txt, Excel report, or data warehouse.

[0008] S3. For multi-dimensional data cross-conversion, extract the data of the Excel table with multiple headers into a relational database to achieve the relationship of unique correspondence between the data and multiple dimension combinations.

[0009] S4. For big data, manage the ETL distributed cluster through a B / S architecture web management system. The common configuration part is uniformly managed by ZooKeeper, and message forwarding is achieved through the Kafka cluster of the message middleware.

[0010] In step S1, deconstruct the ETL tool according to the multi-dimensional relationship table or relational database table, including data source management, data engine, read / write, common dimension, data dimension, source table parsing, target table parsing, update / delete, read configuration, ETL main execution module, exception handling, log management, and operation monitoring. Each module adopts a design pattern of separating logic and parameter configuration, realizes positioning and specific functions in the form of a configuration file, and encapsulates variables in a lightweight json format data file.

[0011] Data sources include relational databases, semi-structured data such as xlxs and csv format files, unstructured data such as doc and txt format files, and external APIs, and various data sources are detailedly recorded in the form of a complete data dictionary, such as the name, type, access method, and host name of the database; the data operation engine module includes methods for accessing data sources such as JDBC and HTTP; the read / write module is responsible for data reading and writing; the source data is analyzed from the dimension and data levels, where the dimension is divided into common and private dimensions, and the source table and target table parsing modules are implementation methods of parsing and storing codes designed according to the specific table structure. The update and delete modules are responsible for special operations on individual tables in the data project from another line. The ETL management includes exception handling, log management, and operation monitoring modules.

[0012] In step S2, in the data query service, for the integration tool to have the functions of batch data query and exporting data in a suitable format in data engineering, a method for constructing an ETL tool for query service is designed for fast data processing. The construction steps include: S21, create a new project or module in PyCharm, select the required input data engine reading component, writing component, and report generation component from the component library according to the type of the source database, and access the database where the source data is located; S22, configure the source table parsing module, including multiple SQL query statement documents and logic code execution programs, or write new business processing logic according to requirements; S23, configure the output data engine module according to the target data format, and store the query results in a txt, excel report, or data warehouse; S24, combine the above processes, run to obtain a document, and complete this query service after manual adjustment.

[0013] In step S3, an ETL tool for multi-dimensional data is designed. Among them, the configuration file Config.json, multi-values can be realized through nesting. For the ETLmain algorithm for multi-dimensional data conversion, the input is to obtain four types of data, namely Public, Data, Dim, and Table, by reading the data in the configuration file according to the path: Public (year, city); Data (column_start, columns); Dim (row_start, row, row_code_num, sql_model_table); Table (data: excel_data, total number of rows: total_row, start row row_start). The process performs deletion or update operations by calling the delete.json file, judges whether row_start is less than total_rows, constructs two vectors, sql_model and value, of the multi-dimensional model, and combines them into an executable insert_sql_model statement to realize data conversion, and stores the multi-dimensional data in the form of two-dimensional data into the table in the relational database.

[0014] In step S4, first introduce the distributed coordination service framework ZooKeeper into the system architecture to complete the work of distributed cluster management and cluster resource scheduling, and then use the Django framework to implement simple cluster management functions, achieving the configuration of distributed system configuration files, viewing the deployment status of nodes, and monitoring the status of nodes. The ETL execution nodes are distributed on the sub-nodes of the cluster to execute specific tasks, extract data from the source database to the target database, and the meta-database stores the configuration information required by the ETL execution nodes.

[0015] The ETL accesses the Kafka cluster. The big data producer creates messages and sends them to the Kafka cluster. The consumer obtains the messages from the Kafka cluster and then performs business logic processing. There are two commonly used libraries for Python to access Kafka: kafka-python and pykafka.

[0016] Log management and API service management are set up. Log management records the running status of the system and each node, and the API service provides the REAEST service for the entire system to interface with third-party applications, and quickly implements the interface through wsgiref.

[0017] From the above description, it can be seen that this patent of this solution selects the Python language to design the ETL tool, and libraries such as Scikit-learn, PyTorch, TensorFlow, and MxNet can be used. It can convert the original data into structures such as tables, graphs, and trees, or formats such as vectors, matrices, and tensors accepted by deep learning and machine learning applications, realizing the management of data format, storage, extraction, conversion, and movement for different computing platforms and application environments, bridging the gap between data engineering and data analysis, solving data processing problems such as wide sources, heterogeneous formats, uncertain requirements, and complex business, and providing important support for high-quality support services and system-level application research. Brief Description of the Drawings

[0018] Figure 1 It is a schematic diagram of an ETL tool for handling different requirements.

[0019] Figure 2 It is a schematic diagram of the overall solution for processing three types of data in data engineering.

[0020] Figure 3 It is a flowchart for modular splitting of the functions of the ETL tool.

[0021] Figure 4 It is a flowchart for constructing an ETL tool for query services.

[0022] Figure 5 It is a schematic diagram of the method for converting a multi-dimensional table in the construction of an index system.

[0023] Figure 6 It is a flowchart of the ETLmain algorithm for multi-dimensional data conversion.

[0024] Figure 7 It is a schematic diagram of the deployment framework of a distributed ETL tool.

[0025] Figure 8 It is a schematic diagram of the ETL tool accessing the Kafka cluster. Detailed Implementation Manner

[0026] The following will clearly and completely describe the technical solutions in the specific embodiments of the present invention in conjunction with the accompanying drawings in the specific embodiments of the present invention. Obviously, the described specific embodiments are only one specific embodiment of the present invention, rather than all specific embodiments. Based on the specific embodiments of the present invention, all other specific embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0027] This solution provides a method for constructing an ETL tool based on module programmable expansion, which supports structured and unstructured data, as well as other extended data formats (such as video, audio, binary data); supports complex and intensive calculations and can run user-defined functions; can quickly process real-time and efficient ETL workflows and analyze large amounts of data; can effectively design and flexibly improve the ETL workflow according to business requirements, reducing the work of ETL developers. In view of the problems such as a large number of frequent queries, complex cross-conversions, and distributed real-time processing encountered in data processing in data engineering, three key focus contents of ETL tools are designed as shown in the appendix. Figure 1 The three types of ETL tool key focus contents are shown as follows.

[0028] In the appendix Figure 2 a holistic solution for processing three types of data in data engineering is described. It includes three levels: data source, data processing, and data application. Data sources include relational databases, unstructured data, and real-time stream data. Relational data and low-volume data can be processed by data integration tools and stored in the database, or after being processed by the Spark ETL cluster module and the Distributed Load Agent (DLA) in the Hadoop cluster, they can be stored in HDF or Object Storage Service (OSS). It can not only process multi-dimensional data in the data warehouse and data in cloud storage, but also execute complex aggregations and run machine learning algorithms, and can dynamically add new nodes and utilize all available resources on the cluster. However, when there are large, high-speed, diverse, and valuable big data streams generated, traditional ETL methods are no longer applicable. Figure 2 A method for processing high-speed big data by three components for large data producers is described in the appendix. Among them, the Distributed Stream Platform (DSP) can process thousands of events per second. The events are saved in the DSP queue, and then the stream pull application or the automatically triggered Lambda function stores the data in the S3 object storage or blob cloud storage.

[0029] This patent is based on a modular specification design method. The system is decomposed into different and independent modules for development. There is no inevitable connection between each module, and they do not affect each other. According to the user's needs, modules can be quickly selected for assembly to achieve functions, realizing flexible design. JSON format files are used to model the workflow and predefined tasks for system component and business process model description. The ETL tool is modularly segmented as Figure 3 shown. Based on the component model and component relationships, the proxy and mediator patterns are used to solve the component tight coupling method, and the ETL system functions are modularly decomposed, laying a foundation for extension and component reuse. Each component consists of classes, functions, and variables and is an independent and assemblable, replaceable, configurable, programmable, and executable program module. It has an internal component parameter override function and provides a set of standardized interfaces externally, meeting certain standards. The component container uniformly manages components, including loading, assembling, unloading, updating, and monitoring. When assembling functions, appropriate components can be selected from the component library according to different system functions, and then the connection between components can be realized through programming or configuration files to complete the rapid assembly of the system.

[0030] This solution targets multi-dimensional relational tables or relational database tables and deconstructs the ETL tool. Figure 3 The framework includes data source management, data engine, read / write, common dimension, data dimension, source table parsing, target table parsing, update / delete, read configuration, ETL main execution module, exception handling, log management, operation monitoring, etc. Each module adopts a logical and parameter configuration separation design pattern, realizes positioning and specific functions in the form of a configuration file, and encapsulates variables in a lightweight json format data file. Among them, the data source module includes relational databases, semi-structured data (such as xlxs, csv format files), unstructured data (such as doc, txt format files), and external APIs, and various data sources are detailedly recorded in the form of a complete data dictionary, such as the name, type, access method, host name, etc. of the database; the data operation engine module includes methods for accessing data sources such as JDBC and HTTP; the read and write modules are responsible for data reading and writing; the source data is analyzed from the dimension and data levels, where the dimension is further divided into common and private dimensions. The source table and target table parsing modules are implemented methods of parsing and storing code designed according to specific table structures. The update and delete modules are responsible for special operations on individual tables in the data project from another line. ETL management includes modules such as exception handling, log management, and operation monitoring.

[0031] ETL tool for query services. In data query services, there is a need to address the issue that in data engineering, integration tools should have the functions of batch data query and exporting data in a suitable format, but common tools are difficult to handle such requirements. According to the ETL tool processing flow, based on the data query flow chart and the modular component library, a construction method of an ETL tool for query services is designed, which can quickly process data. The construction process is as Figure 4 shown: First step, create a new project or module in PyCharm, select appropriate input data engine reading components, writing components, report generation components, etc. from the component library according to the type of the source database, and connect to the database where the source data is located; further, configure the source table parsing module, including multiple SQL query statement documents and logic code execution programs, or write new business processing logic according to requirements; still further, configure the output data engine module according to the target data format, and store the query results in a txt, Excel report or data warehouse; finally, combine the above processes through the main function, run to obtain the document, and complete this query service after manual adjustment.

[0032] ETL tool for multi-dimensional data. Aiming at the problem of cross-conversion of multi-dimensional data in the ETL process, based on the proposed model and method, this patent implements a set of tools for extracting Excel table data with multiple headers into a relational database, as specifically shown in the appendix Figure 5 shown. At the same time, a multi-level header processing method for constructing an index system is formed: Table 1 is a common data storage format in statistics. This kind of table with multi-level headers is convenient for daily data use, but not suitable for data storage, program operation and index management. Convert Table 1 to the format of Table 3, which not only solves the problem of two-dimensional storage of multi-dimensional multi-level headers, but also facilitates system-level operations and index system management. The data sample conversion process is shown in Tables 1, 2, and 3, that is, the data in the table needs to be converted into a two-dimensional form for storage in the data table. The specific steps are as follows: First, extract four dimensions from Table 1: the common dimensions DimYear and DimCity, and the other dimensions Type and the index dimension Index; secondly, store these four dimension tables into the database respectively. In this article, the structures and values of the four dimensions are stored together in Table 2; then, convert the values in Table 1 into the ids corresponding to the values of each dimension in Table 2. The most important feature is that the two columns of GDP (100 million yuan) and growth rate (%) also become the index dimension in Table 3; finally, store Table 3 into the database, and finally realize the unique corresponding relationship between the data and multiple dimensions. Based on the modular method and the conversion process description, design an ETL tool for multi-dimensional data. The configuration file Config.json is as shown in the following table, and multiple values can be achieved through nesting.

[0033]

[0034] The ETLmain algorithm for multi-dimensional data transformation is as attached Figure 5 as shown. The input is to obtain four types of data, namely Public, Data, Dim, and Table, from the configuration file according to the path: Public (year, city); Data (column_start, columns); Dim (row_start, row, row_code_num, sql_model_table); Table (data: excel_data, total number of rows: total_row, starting row row_start). The process performs deletion or update operations by calling the delete.json file, checks whether row_start is less than total_rows, constructs two vectors, the multi-dimensional model sql_model and value, combines them into an executable insert_sql_model statement to achieve data transformation, and stores the multi-dimensional data in the table in the relational database in the format of Table 3.

[0035] For the ETL tool for big data, in the case of a large number of task jobs, a large amount of data to be transformed, and real-time data, when running on the client of the local server, there will be phenomena such as excessive resource consumption, blocking of single-node system resources, and a sharp decline in data transformation performance. Moreover, it cannot be combined with big data technologies such as the HDFS file system and MapReduce distributed computing. Therefore, to address the above problems, this section designs a distributed ETL tool for big data. Through the B / S architecture web management system, it manages the ETL distributed cluster. The common configuration part is uniformly managed by ZooKeeper, and message forwarding can be achieved through the Kafka cluster of the message middleware. The specific implementation is as attached Figure 6 as shown, where Y represents yes and N represents no.

[0036] The design of this solution is based on the requirements analysis. First, a distributed coordination service framework ZooKeeper is introduced into the system architecture to complete the work of distributed cluster management and cluster resource scheduling. Then, the Django framework is used to implement simple cluster management functions, achieving the configuration of the distributed system configuration file, viewing the deployment status of nodes, and monitoring the status of nodes. The ETL execution nodes are distributed on the sub-nodes of the cluster to perform specific tasks, extracting data from the source database to the target database. The meta-database stores the configuration information required by the ETL execution nodes, as attached Figure 7 as shown.

[0037] Attached Figure 8It is that ETL accesses the Kafka cluster. The producer creates messages and sends them to the Kafka cluster. The consumer obtains messages from the Kafka cluster and then processes the corresponding business logic. There are two commonly used libraries for Python to access Kafka: kafka-python and pykafka. In this section, it is implemented through kafka-python to establish a consumer group. At the same time, the system has set up log management and API service management. The log management records the running status of the system and the running status of each node. The API service provides the REAEST service for the entire system, can interface with third-party applications, and quickly implement the interface through wsgiref.

[0038] The above are only the preferred specific embodiments of the present disclosure and are not used to limit the present disclosure. For those skilled in the art, the present disclosure can have various changes and modifications. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present disclosure shall be included within the protection scope of the present disclosure.

Claims

1. A method for constructing an ETL tool based on module programmable expansion, characterized in that It includes the following steps: S1. Modularly divide the ETL tool. Each component includes classes, functions, and variables, and is an independent program module that can be assembled, replaced, configured, programmed, and executed. It has the function of covering internal component parameters and provides a set of standardized interfaces externally; S2. In the data query service, create a new project or module in PyCharm. Select appropriate input data engine reading components, writing components, and report generation components from the component library according to the type of the source database, connect to the database where the source data is located, and store the query results in a txt, Excel report, or data warehouse; S3. For multi-dimensional data cross-conversion, extract the data in the Excel table with multiple headers to a relational database to achieve the unique corresponding relationship between the data and multiple dimensions; S4. For big data, manage the ETL distributed cluster through the B / S architecture web management system. The common configuration part is uniformly managed by ZooKeeper, and message forwarding is achieved through the Kafka cluster of the message middleware; In step S2, in the data query service, aiming at the function that the integration tool should have batch data query and export data in a suitable format in the data engineering, a construction method of the ETL tool for the query service is designed for fast data processing. The construction steps include: S21. Create a new project or module in PyCharm. Select the required input data engine reading components, writing components, and report generation components from the component library according to the type of the source database, and connect to the database where the source data is located; S22. Configure the source table parsing module, including multiple sql query statement documents and logic code execution programs, or write new business processing logic according to requirements; S23. Configure the output data engine module according to the target data format, and store the query results in a txt, Excel report, or data warehouse; S24. Combine the above processes, run to obtain the document, and complete this query service after manual adjustment; In step S3, design a multidimensional data ETL tool. There is a configuration file Config.json, and multi-values can be implemented through nesting. For the ETLmain algorithm for multidimensional data conversion, the input is to obtain four types of data, namely Public, Data, Dim, and Table, from the data in the configuration file read according to the path: Public (year, city); Data (column_start, columns); Dim (row_start, row, row_code_num, sql_model_table); Table (data: excel_data, total number of rows: total_row, starting row row_start). The process performs deletion or update operations by calling the delete.json file, determines whether row_start is less than total_rows, constructs two vectors, sql_model and value, for the multidimensional model, combines them into an executable insert_sql_model statement to achieve data conversion, and stores the multidimensional data in the form of two-dimensional data into a table in the relational database.

2. The method for constructing an ETL tool based on module programmable extension according to claim 1, characterized in that In step S1, deconstruct the ETL tool according to the multidimensional relationship table or the relational database table, including data source management, data engine, reading and writing, public dimension, data dimension, source table parsing, target table parsing, update and deletion, reading configuration, ETL main execution module, exception handling, log management, and operation monitoring. Each module adopts a design pattern of separating logic and parameter configuration, realizes positioning and specific functions in the form of a configuration file, and encapsulates variables in a lightweight json-format data file.

3. The method for constructing an ETL tool based on module programmable extension according to claim 2, characterized in that The data sources include relational databases, semi-structured data including xlxs and csv format files, unstructured data including doc and txt format files, and external APIs, and various data sources are detailedly recorded in the form of a complete data dictionary, including the name, type, access method, and host name of the database; the data operation engine module includes methods for accessing data sources such as JDBC and HTTP; the reading and writing module is responsible for data reading and writing; the source data is analyzed from the dimension and data levels, where the dimension is further divided into public and private dimensions. The source table and target table parsing modules are methods for implementing parsing and storage code designed according to specific table structures. The update and deletion modules are responsible for special operations on individual tables in the data engineering from another line. The ETL management includes exception handling, log management, and operation monitoring modules.

4. The method for constructing an ETL tool based on module programmable extension according to claim 1, characterized in that In step S4, first introduce the distributed coordination service framework ZooKeeper into the system architecture to complete the work of distributed cluster management and cluster resource scheduling, and then use the Django framework to implement simple cluster management functions, achieving the configuration of distributed system configuration files, the viewing of node deployment status, and the monitoring of node status. The ETL execution nodes are distributed on the child nodes of the cluster to execute specific tasks, extracting data from the source database to the target database, and the meta-database stores the configuration information required by the ETL execution nodes.

5. The method for constructing an ETL tool based on module programmable extension according to claim 4, wherein The ETL accesses the Kafka cluster. The big data producer creates messages and sends them to the Kafka cluster. The consumer obtains the messages from the Kafka cluster and then performs business logic processing. There are two commonly used libraries for Python to access Kafka: kafka-python and pykafka.

6. The method for constructing an ETL tool based on module programmable extension according to claim 5, wherein Log management and API service management are set up. Log management records the system running status and the running status of each node. The API service provides the REAEST service for the entire system, which is used to connect to third-party applications, and the interface is quickly implemented through wsgiref.

Images