Architecture design method of self-developed ETL tool
By designing the architecture of self-developed ETL tools, the universality of existing ETL tools in database operations, data synchronization, file import, message middleware writing and data aggregation processing is solved, and dynamic configuration and automated operations are realized, reducing the number of applications. Development costs and efficiency are improved.
Patent Information
- Application Number
- CN202410979194.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-22
- Publication Date
- 2024-12-06
AI Technical Summary
Existing ETL tools lack universal tools in database operations, data synchronization, file import, message middleware writing and data aggregation processing, resulting in high development costs and the inability to dynamically add database configuration information, which increases the burden of personnel development. .
Designed a self-developed ETL tool architecture, including database configuration module, database adaptation module, ETL task module, database connection module, data synchronization module, data comparison difference module, data import and export module, data repair module, message module, The mailbox configuration module and data aggregation processing module realizes database adaptation, task configuration, connection management, synchronization, comparison, import and export, repair, message writing and aggregation processing functions through microservices and middleware technologies.
It realizes automation of dynamic database configuration, cross-database data synchronization, file import and export, message middleware writing and data aggregation processing, reduces development costs, improves operational efficiency and universality of tools.
Smart Images

Figure CN119088862A_ABST
Abstract
Description
Technical Field
[0001] The present invention discloses an architectural design scheme for a self-developed ETL tool, involving middleware technology, microservices, database and other technical means. Background Art
[0002] The main links in the ETL process are data extraction, data conversion and processing, and data loading. In some business scenarios, it is necessary to synchronize, compare, compensate, convert, and count certain business data. Currently, there is no ETL tool on the market that integrates the above functions.
[0003] Data extraction is the process of extracting data from a data source. In practical applications, relational databases are often used as data sources. There are generally two ways to extract data from a database: full extraction and incremental extraction. In addition to relational databases, the data source for ETL processing may also be files, such as txt files, excel files, xml files, etc. The extraction of file data is generally a full extraction with certain format conventions. The data extracted from the data source may not fully meet the requirements of the target database, such as inconsistent data formats, data input errors, incomplete data, etc., so it is necessary to convert and process the extracted data. Data conversion and processing can be performed in the ETL engine, or it can be performed simultaneously during the data extraction process using the characteristics of the relational database. In the ETL engine, data conversion is generally implemented in a componentized manner. Commonly used data conversion components include field mapping, data filtering, etc. The relational database itself has provided powerful SQL and functions to support data processing, such as adding where conditions to SQL query statements for filtering, renaming field names in queries to map with the target table, substr functions, case condition judgments, etc. Loading the converted and processed data into the target database is usually the last step of the ETL process. The best way to load data depends on the type of operation being performed and how much data needs to be loaded. When the target database is a relational database, there are generally two ways to load: 1. Direct SQL statements for insert, update, and delete operations. 2. Use batch loading methods. The original intention of self-developed ETL tools: 1. Support for existing business data. 2. Achieve universality rather than specific system requirements. 3. It was originally designed as a general tool for data compensation, and then it was continuously expanded and iterated to enhance its functions. A relational database refers to a database that uses a relational model to organize data. It stores data in the form of rows and columns to facilitate user understanding. This series of rows and columns of a relational database is called a table, and a group of tables constitutes a database. Users retrieve data from the database through queries, and a query is an execution code used to limit certain areas in the database. The relational model can be simply understood as a two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and the relationships between them. NoSQL refers to a non-relational database. With the rise of web2.0 websites on the Internet, traditional relational databases have become unable to handle web2.0 websites, especially ultra-large-scale and highly concurrent SNS-type web2.0 purely dynamic websites, and many difficult-to-overcome problems have emerged. Non-relational databases have developed very rapidly due to their own characteristics. The emergence of NoSQL databases is to solve the challenges brought by large-scale data sets with multiple data types, especially the application of big data.The message middleware Kafka is a high-throughput distributed publish-subscribe messaging system that can handle all action stream data of consumers on the website. This action (web browsing, search and other user actions) is a key factor in many social functions on the modern network. This data is usually solved by processing logs and log aggregation due to throughput requirements. For log data and offline analysis systems like Hadoop, but with the limitation of real-time processing, this is a viable solution. The purpose of Kafka is to unify online and offline message processing through Hadoop's parallel loading mechanism, and also to provide real-time messages through clusters. The message middleware RabbitMQ is an open source message broker software that implements the Advanced Message Queuing Protocol (AMQP). The RabbitMQ server is written in Erlang, and the cluster and failover are built on the open telecommunications platform framework. All major programming languages have client libraries for communicating with the broker interface.
[0004] When using ETL tools, the following problems exist:
[0005] 1. When performing database operations, you cannot dynamically add database configuration information. Perform operations in a timely manner. Each time a new database is added, you need to add database connection information in the application.properties file of the microservice, and then republish the service to take effect.
[0006] Second, data synchronization of many businesses currently needs to be performed across different databases, such as synchronization from MYSQL database A to MYSQL database B, from MYSQL database A to Hbase database B, from MYSQL database A in the test environment to MYSQL database B in the development environment, etc. There is no ready-made universal method for these, unless code is developed for specific needs, which increases the cost of personnel development.
[0007] 3. Currently, many businesses need to import large quantities of files into the database, but this requires code development for specific needs, resulting in high development costs.
[0008] 4. Currently, some businesses need to write data from the database to the message middleware, mainly Kafka and RabbitMQ. There are no related tools to implement it directly, and it also needs to be implemented through code development according to specific requirements, which increases the development cost.
[0009] 5. Currently, some businesses perform certain aggregation processing on the data in the database and then insert it into the database. The aggregation processing here mainly includes the data's average, maximum, minimum, mode, standard deviation and other statistical characteristic values. There is no direct tool to implement this part, and code development needs to be carried out according to specific requirements, which increases the development cost. Summary of the invention
[0010] In order to solve one of the problems existing in the prior art, the present invention discloses an architecture design method of an ETL tool, which involves middleware technology, microservices, database and other technical means. Through a database configuration module, a database adaptation module, an ETL task module, a database connection module, a data synchronization module, a data comparison difference module, a data import and export module, a data repair module, a message module, a mailbox configuration module, a data aggregation processing module, and a task issuing and receiving module, the above-mentioned major problems can be well solved.
[0011] To solve the above problems, the technical solutions adopted by the present invention are as follows:
[0012] An architecture design method for a self-developed ETL tool, including: database configuration module, database adaptation module, ETL task module, database connection module, data synchronization module, data comparison and difference module, data import and export module, data repair module, message module, mailbox configuration module, data aggregation processing module, and task sending and receiving module;
[0013] 1. Database configuration module:
[0014] The database configuration module is mainly used to store the configuration of relational database-related connection information, such as the database connection string, user name, and password. This module can add, modify, delete, enable, disable, and view details of the database configuration information.
[0015] 2. Database Adaptation Module:
[0016] The database adapter module is mainly used to adapt to different types of relational databases, such as MYSQL database, ClickHouse database, etc. Here, type adaptation will be performed first, and then operations will be performed under the corresponding type of database. The implementation method is as follows:
[0017] First define the interface DataBaseInitialization, which defines 7 methods: String getDataBaseType(), void beforeProcess(Task task), void getListForBatchInsert(Task task,String sql,DataOperatorTool dataOperatorTool), List <Map<String,Object> >getListForSolveCompare(Task task), void extendMultiFunction(Tasktask,TaskBaseConfig taskBaseConfig), int batchInsertOrUpdate(Task task,List <Map<String,Object> >data,List<Object[]> params,String operateSql,booleanisInsert), int repairData(Task task,String updateSql)
[0018] The specific explanation is as follows:
[0019] String getDataBaseType() returns the type of the current database. For example, if it is a MYSQL database, it returns "mysql";
[0020] voidbeforeProcess(Task task) is the pre-processing method of the task, task represents the current task;
[0021] void getListForBatchInsert(Task task,String sql,DataOperatorTooldataOperatorTool) is a method used to implement data synchronization. Task represents the current task, sql represents the specific SQL statement for querying data, and dataOperatorTool represents specific operations such as data update and addition.
[0022] List <Map<String,Object> >getListForSolveCompare(Task task) is a method for data comparison, where task represents the current task;
[0023] void extendMultiFunction(Task task,TaskBaseConfig taskBaseConfig) is a method for extending functions such as data export, writing to message middleware, http transmission message service, and aggregation processing. Task represents the current task, and taskBaseConfig represents the corresponding configuration.
[0024] int batchInsertOrUpdate(Task task,List <Map<String,Object> >data,List<Object[]> params,String operateSql,boolean isInsert) is the method for inserting data into the database in batches. task represents the current task, operateSql represents the SQL statement of the operation, and isInsert represents whether to insert data into the database.
[0025] int repairData(Task task,String updateSql) is a method for data repair, task represents the current task, updateSql represents the repaired SQL statement;
[0026] After creating the DataBaseInitialization interface class, create a new transmission mode implementation class according to different types of relational databases to implement the DataBaseInitialization interface, and then complete the specific methods in the implementation class; for example, the implementation class of the MYSQL database is defined as MysqlDataBaseUtil, which specifically implements the above 7 methods, and adds the @Component annotation to the specific implementation class. In this way, when starting the service, the Spring Bean mechanism is used to automatically inject this Bean object during the service startup process;
[0027] Define the ToolsBeanUtils tool class and add the @Component annotation to make this class a Bean object. At the same time, let this class implement the ApplicationContextAware interface, and then implement its setApplicationContext(ApplicationContext applicationContext) method. This method performs some operations when the container is initialized. Here, using this technique, you can define a Map in the class<String,DataBaseInitialization> Object, used to adapt to different types of databases, Key represents the method database type name, Value represents the specific implementation class, this Map object is recorded as dataBaseBeans, and is used inside the initialization method through Map<String,DataBaseInitialization> beans = applicationContext.getBeansOfType(DataBaseInitialization.class); Get all classes that implement the DataBaseInitialization interface class in the current service, then traverse dataBaseBeans and insert relevant information into transBeans. In this way, when the container is initialized, the specific processing Bean objects of different types of databases will also be injected into the container;
[0028] In the ToolsBeanUtils class, define the getDataBaseUtil(String type) method. You only need to enter the database type name to get the current database implementation class, and then call the method in it to operate the data. This solves the database adaptation problem.
[0029] 3. ETL task module:
[0030] The ETL task module is mainly used to configure related ETL tasks, such as data synchronization, file import, data comparison, etc. Here, you need to establish a task model first;
[0031] First, define the Task class, which mainly includes task ID: taskId, task name: taskName, CRON expression: cron, pre-processing SQL statement: beforeSql, source database name: sourceDb, source database query SQL statement: sourceSql, source database query table: sourceTableName, target database name: targetDb, target database processing table name: targetTableName, business type: serviceType, comparison fields: compareField, update matching fields: updateWhereKey, whether to perform aggregation processing: isNeedAggregation, data comparison matching fields: whereKey;
[0032] In the background configuration interface, add the corresponding ETL task. After adding, you can modify the task or enable or disable the task. At this point, the ETL task module is finished.
[0033] 4. Database connection module:
[0034] The database connection module is mainly used to connect to the corresponding database, which is mainly accomplished by establishing the DataSourceMgr class;
[0035] The DataSourceMgr class has the following methods: DruidDataSource getDataSource(String dsId), JdbcTemplate getJdbcTemplate(String dsId), DataSourceTransactionManagergetTxManager(String dsId), which are explained as follows: DruidDataSource getDataSource(String dsId) is a method for obtaining the database connection pool. dsId represents the ID of the current database. The returned DruidDataSource is the database connection information. After obtaining the database connection information, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DruidDataSource> The dsMap is used as a cache. When actually called, the database connection information will be obtained from the cache first to avoid creating a database connection every time.
[0036] JdbcTemplate getJdbcTemplate(String dsId) is a method to obtain the database JdbcTemplate. dsId represents the ID of the current database. The returned JdbcTemplate is the database underlying processing template class. After obtaining the database underlying processing template, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,JdbcTemplate> The jdbcMap is used as a cache. When actually calling, the template information will be retrieved from the cache first.
[0037] DataSourceTransactionManager getTxManager(String dsId) is a method for obtaining database transaction processing. dsId represents the ID of the current database. The returned DataSourceTransactionManager is the underlying transaction management class of the database. After obtaining the underlying transaction management class, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DataSourceTransactionManager> txMap is used as a cache. When actually called, the transaction management class will be first retrieved from the cache, so that the database connection problem can be solved.
[0038] 5. Data synchronization module
[0039] The data synchronization module is mainly used to handle the functions related to business data synchronization. There are several ways to handle data synchronization here: direct insert compensation, direct insert and then update compensation, direct update compensation, and direct update and then insert compensation. Because some data needs to be directly inserted into the corresponding table of the target database, some need to be inserted first and then updated, some need to directly update the data in the corresponding database, and some need to update first and then insert the data. The implementation method is as follows:
[0040] First, execute the SQL statement to query the relevant data from the configured source database according to the configured sourceSql, and then process it according to the selected processing method;
[0041] The implementation of different processing methods is as follows:
[0042] Create an interface class DataOperatorTool, which has two methods: int getDataOperatorType() and int[]operator(Task task,List <Map<String,Object> >data,DataBaseInitialization dataBaseUtil), the specific explanation is as follows:
[0043] int getDataOperatorType() is used to obtain the identifier of the data processing method. For example, 0 represents direct insert compensation, 1 represents direct insert and then update compensation, 2 represents direct update compensation, and 3 represents direct update and then insert compensation.
[0044] int[]operator(Task task,List <Map<String,Object> >data,DataBaseInitialization dataBaseUtil) is the specific method for data processing, task represents the current task, data represents the data to be processed in the current query, and dataBaseUtil is the database operation processing tool class;
[0045] Create different DataOperatorTool interface implementation classes: InsertTargetUtil represents direct insert compensation, InsertUpdateRepairUtil represents direct insert and then update compensation, and UpdateRepairUtil;
[0046] Represents direct update compensation, and UpdateInsertRepairUtil represents direct update and then insert compensation; these implementation classes will be annotated with @Component, and using Spring's Bean mechanism, the Bean object will be automatically injected during the service startup process, so that data processing can be performed according to the specific selected operation mode, so that the data synchronization problem can be solved;
[0047] 6. Data comparison and difference module
[0048] The data comparison and difference module is mainly used to process business difference data. For example, if the data in table a of database A is synchronized to table b of database B, if a failure or other reasons occur, some data cannot be synchronized normally. At this time, it is necessary to compare the data of these two tables to find out the data that has not been synchronized successfully. At this time, the relevant functions of the data comparison and difference module are needed; this part of the functions includes: difference data compensation operation, difference data insertion into third-party tables, and difference data notification service; the specific implementation method is as follows:
[0049] Create the ImportWithCompareForDiffService class, which contains two methods: StringvalidTaskCondition(FlowMap r) and void solveData(Task task). This class also references the aforementioned ToolsBeanUtils tool class, which is explained as follows:
[0050] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0051] void solveData(Task task) is a specific method for data processing. Task represents the current task. In this method, the getOperateUtil method in ToolsBeanUtils will be called. Here, the corresponding operation mode will be selected according to the selected operation mode identifier, that is, the aforementioned DataOperatorTool interface implementation class. There are specific processing methods in the corresponding implementation class, which are all related basic database operation methods. At this point, data comparison differences can be solved;
[0052] 7. Data import and export module
[0053] The data import and export module is mainly used to process the import and export of data, such as importing data from an Excel file to table a of database A, and exporting data from table b of database B to an Excel file; the specific implementation method is as follows:
[0054] First, define the FileImportService class, which is a class specifically used to import files. This class defines three methods: String validTaskCondition(FlowMap r), validFiledValue(FileImportConfigfileImportConfig,Map<String,Object> object) and readExcelAndImportData(String filePath,Task task,DataBaseInitialization dataBaseUtil,FileImportConfigfileImportConfig), the specific explanations are as follows:
[0055] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0056] validFiledValue(FileImportConfig fileImportConfig,Map<String,Object> object) is to verify the specific imported file configuration. Here, the import module has related pages for file import configuration, so its legitimacy will be verified, such as whether the file exists on the server and whether the file format is correct.
[0057] Map<String,Object> object) and readExcelAndImportData(String filePath,Task task,DataBaseInitialization dataBaseUtil,FileImportConfigfileImportConfig) are used to process specific import files, mainly using JAVA's POI-related jar packages. It should be noted that the header of the import file needs to correspond to the field to be inserted into the table;
[0058] Define the ExportDataService class, which is a class specifically used to export files. This class defines three methods: getListForExport(Task task), export(Task task,List <Map<String,Object> >data,boolean flag,TaskBaseConfig taskBaseConfig) and sendEmailForCompressFile(Tasktask,String filePath), the specific explanations are as follows:
[0059] getListForExport(Task task) is a method to obtain the data list to be exported. Task represents the current task. It mainly queries the required data through the data source configured in the task and the related SQL statements.
[0060] export(Task task,List <Map<String,Object> >data,TaskBaseConfigtaskBaseConfig) is the specific method of exporting data. Task represents the current task, data represents the data obtained by the previous query, and taskBaseConfig represents the related configuration data for export, such as the maximum number of export rows for a single file, and whether the export format is xlsx file or csv file. This solves the import and export problem.
[0061] sendEmailForCompressFile(Task task,String filePath) represents the method of sending data by email. Task represents the current task, and filePath represents the export file generated in the previous step and stored on the server. It will be sent to the relevant export data users by email.
[0062] 8. Data repair module
[0063] The data repair module is mainly used to process and modify data. For example, if a data error occurs in table a of database A and needs to be modified, the relevant SQL statements can be directly executed through the repair module to achieve the purpose. The specific implementation method is as follows:
[0064] Create the RepairDataService class, which is a class dedicated to data repair. It has two methods: StringvalidTaskCondition(FlowMap r) and void solveData(Tasktask). The specific explanations are as follows:
[0065] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0066] void solveData(Task task) is a specific method for data repair. In this method, the database and corresponding table name to be modified, as well as the modification SQL to be executed, will be found through the task configuration information. Finally, the SQL statement will be executed to achieve the purpose of modifying the data, and the modification results will be sent to relevant personnel by email. At this point, the data repair problem is solved.
[0067] 8. Message Module
[0068] The message module is mainly used to write the data to be queried into the message middleware module. For example, if a data error occurs in table a of database A and needs to be modified, the relevant SQL statements can be directly executed through the function of the repair module to achieve the purpose. The specific implementation method is as follows:
[0069] Create a MessageService class, which is a class dedicated to handling message-related services. This class defines three methods: String validTaskCondition(FlowMap r), MessageConfig getMessageConfig(inttaskId), and solveMessage(Task task,List <Map<String,Object> >data,boolean flag,TaskBaseConfig taskBaseConfig), the specific explanation is as follows:
[0070] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0071] getListForMessage(Task task) is a method to obtain the data list to be exported. Task represents the current task. It mainly queries the required data through the data source configured in the task and the related SQL statements.
[0072] MessageConfig getMessageConfig(int taskId) is used to obtain the configuration of the corresponding message middleware according to the task ID. taskId represents the task ID, and MessageConfig represents the configuration of the message middleware.
[0073] solveMessage(Task task,List <Map<String,Object> >data,MessageConfigmessageConfig) is the specific method for processing messages, task represents the current task, data represents the queried business data, and messageConfigs is the configuration of the message middleware; within this method, it will be judged according to the message middleware category configured by the task. If Kafka is configured, then the relevant configuration of Kafka will be read, mainly the topic to be written to the message: topic; if rabbitMq is configured, then the relevant configuration of rabbitMq will be read, mainly the message name: messageName, routing KEY: routeKey, exchanger: exchange. Finally, the relevant business data is written into the message middleware, so the problem of writing data into the message middleware is solved;
[0074] 8. Mailbox Configuration Module
[0075] The mailbox configuration module is mainly used to notify relevant business personnel of the ETL task execution status. Of course, users can choose to send emails only when the task execution fails. You can choose in the interface configuration; the specific implementation method is as follows:
[0076] First, create the EtlMailSenderConfig class, which is a class for mailbox configuration. This class defines several member variables: the IP address of the sender's mailbox: host, the sender's mailbox port: port, the sender's mailbox username: username, the sender's mailbox password: password; there is also a method in this class: Message generateMessage(String title, List <string>sendToList), the specific explanation is as follows:
[0077] Message generateMessage(String title,List <string>sendToList) is the combined email sending template information, title represents the title of the email, sendToList represents the email address set for receiving emails, Message represents the email sending template information, and several member variables of the above email configuration will be written into Message. At the same time, the @Component annotation is added to the EtlMailSenderConfig class, so that when the service is started, the Spring Bean mechanism is used to automatically inject this Bean object during the service startup process;
[0078] Then create the EtlSendEmailService class, which is a class dedicated to sending emails. The EtlMailSenderConfig class mentioned above will be injected into this class. This class defines two methods: sendMail(int taskId, String title, String content, ByteArrayInputStreamin, String filename) and saveMailLog(inttaskId, long costTime, int status, String errorMessage, String attachment, String title, String content). The specific explanations are as follows:
[0079] sendMail(int taskId,String title,String content,ByteArrayInputStreamin,String filename,boolean isSuccess) is a specific method for sending emails. taskId represents the task ID, title represents the email subject, content represents the content of the email, in represents the attachment file stream to be transmitted, and filename represents the attachment file name. In this method, the sendMail(int taskId,String title,String content,ByteArrayInputStreamin,Stringfilename) method of the EtlMailSenderConfig class is called to assemble the relevant email message and then send it out.
[0080] saveMailLog(int taskId,long costTime,int status,String errorMessage,String attachment,String title,String content) is a method for recording the log of sending emails, which will be called inside the sendMail method. taskId represents the task ID, costTime represents the time taken to send the email, status represents whether the email is sent successfully or not, 0 represents failure, 1 represents success, errorMessage represents the reason for the failure of sending the email, attachment represents the file name of the attachment, title represents the subject of the email, and content represents the content of the email. The purpose of recording the log of sending emails is mainly to prevent the failure of sending emails. If the current task fails to send the email, a scheduled task retry will be performed. The specific retry method is as follows:
[0081] Create the FailEmailSendScheduler class, which is a class specifically used to detect failed email sending tasks and resend them. The EtlSendEmailService class is injected into this class, and it has a void queryFailEmail() method. The specific explanation is as follows:
[0082] void queryFailEmail() is a method for retrieving tasks that failed to send emails and resending them. This is a scheduled task that is executed every half an hour. The specific implementation is as follows:
[0083] First, the database will be queried for the task that failed to send the email, and then the relevant email data will be reassembled. Finally, the email will be sent through the sendMail(int taskId,String title,String content,ByteArrayInputStreamin,String filename,boolean isSuccess) method of the EtlSendEmailService class, thus solving the problem of email configuration.
[0084] 9. Data Aggregation Processing Module
[0085] The data aggregation processing module is mainly used to aggregate the data before inserting it into the database. The aggregation processing here mainly includes the average value, maximum value, minimum value, mode, standard deviation and other characteristic values with statistical properties. The specific implementation method is as follows:
[0086] Create an interface class AggregationOperator, which has two methods: int getType() and List <Map<String,Object> >transformData(Task task,List <Map<String,Object> >data,AggregationConfig aggregationConfig), the specific explanation is as follows:
[0087] int getType() is a method to obtain the type of aggregation processing, and returns the identifier of the aggregation processing type. There are three types: 0: simple column calculation, 1: group statistics, 2: simple statistics. The specific implementation method is as follows:
[0088] Create two implementation classes of the AggregationOperator interface class: SimpleColumnAggregationOperator and GroupStatisticAggregationOperator. The specific explanations are as follows:
[0089] The SimpleColumnAggregationOperator class is a simple column calculation, which is mainly used for the simple addition of several columns in a database table to form a new column. For example, in table a of database A, the data in columns 1, 2, and 3 need to be added together to form a new column, recorded as column n, and then inserted into the new table. This is a function required in some application scenarios.
[0090] The SimpleColumnAggregationOperator class is a simple column calculation, which is mainly used for the simple addition of several columns in a database table to form a new column. For example, in table a of database A, the data in columns 1, 2, and 3 need to be added to form a new column, recorded as column n, and then inserted into the new table. This is a function required in some application scenarios. Note that the format in the "Aggregation Calculation Field Rule Configuration" column of the interface is "column 1, column 2, column 3#new column name", the column before "#" is the column to be added, and the name of the new field formed by adding these columns is after "#".
[0091] The GroupStatisticAggregationOperator class is a grouping statistic, which is mainly used for data statistics, such as calculating the average value, maximum value, minimum value, mode, standard deviation and other characteristic values with statistical properties; the specific implementation method is as follows:
[0092] Create an interface class StatisticOperator, which is an interface class for processing group statistics methods. There are two methods in this class: String getType() and Object getCalculatorResult(Task task, List <Map<String,Object> >mapList,StatisticOperatorField statisticOperatorField), the specific explanation is as follows:
[0093] String getType() is used to obtain the type name of the grouping statistical processing method, such as "sum" for sum function, "ariavg" for arithmetic mean function, "max" for maximum value function, "min" for minimum value function, "geoavg" for geometric mean function, "sta" for standard deviation function, etc.
[0094] Object getCalculatorResult(Task task,List <Map<String,Object> >mapList,StatisticOperatorField statisticOperatorField), this is a method for performing specific statistical function calculations on data. task represents the current task, mapList represents the current queried business data to be processed, statisticOperatorField represents the field conversion model, and the StatisticOperatorField class has three member variables: original field name: originField, processing function identifier: operatorFlag, and new field name after processing: newField; in this method, the task will select the corresponding statistical function according to the configured processing identifier. The statistical function selection method is implemented as follows:
[0095] Different StatisticOperator interface implementation classes are established for different processing methods. For example, the sum function implementation class is called SumStatisticOperator, the maximum function is called MaxStatisticOperator, the minimum function implementation class is called MinStatisticOperator, the geometric mean function is called GeoStatisticOperator, and the arithmetic mean function is called AriAvgStatisticOperator. When processing specific statistical tasks, the corresponding function processing class will be selected according to the configured identifier, and then there are specific operations inside the class. At this point, the problem of data aggregation processing is solved.
[0096] 10. Task sending and receiving module
[0097] The task sending and receiving module is mainly used for sending and receiving tasks. Here, microservices are mainly used to implement the functions of this module. The sending module is implemented by the etlfschedulerserver service, and the receiving module is implemented by the dataextractfilter service. The specific implementation is as follows:
[0098] There are two interfaces in etlfschedulerserver: receiveRegister and receiveTaskResult, which are explained as follows:
[0099] receiveRegister is a method for registering heartbeats. Since microservices are deployed in a distributed manner, the task dispatching module is a core module and consistency must be ensured to avoid duplication or non-dispatching of tasks. REDIS middleware is used here to implement this. Before dispatching a task, the task to be executed at the current time point will be queried from the database, and then the value of the corresponding KEY in REDIS will be read to determine whether there is a task dispatched at the current time point. If it has been dispatched, no operation will be performed. If it has not been dispatched, the current service will be dispatched, and the dispatched service number set will be written into the value of the corresponding KEY in REDIS, so that REDIS contains the latest task dispatching information.
[0100] receiveTaskResult is a method for receiving task execution results. This interface is mainly used by the task receiving service to actively call this interface to notify the task issuing module of the task execution status. When the task issuing module receives the task execution status, it will modify the task status in the database on the one hand, and modify REDIS to update the corresponding KEY value on the other hand, and remove the completed task number.
[0101] There is an interface in dataextractfilter: receiveTask, which is explained as follows:
[0102] The input parameter of receiveTask interface is only taskIdList, which is a List <interger>Type, represents the task number collection under the task. After receiving the required task number, the interface starts to execute the related tasks. Here, each task is directly thrown into the thread pool for execution, and is distributed and executed in order according to the order of the collection. After each task is completed, the receiveTaskResult interface of the etlfschedulerserver service will be called to feed back the current task execution result to the task delivery module. After receiving the task execution result, the task delivery module updates the cache information in REDIS. At this point, the problem of task delivery and reception is solved.
[0103] Compared with the prior art, the implementation effects of the present invention are as follows:
[0104] The present invention discloses an architecture design scheme of a self-developed ETL tool, which involves middleware technology, microservices, database and other technical means. Through a database configuration module, a database adaptation module, an ETL task module, a database connection module, a data synchronization module, a data comparison difference module, a data import and export module, a data repair module, a message module, a mailbox configuration module, a data aggregation processing module, and a task issuing and receiving module, several major problems described in the background technology can be well solved. BRIEF DESCRIPTION OF THE DRAWINGS
[0105] Figure 1 Configure the module interface for the database;
[0106] Figure 2 It is the ETL task module interface;
[0107] Figure 3 An interface for selecting several processing methods for data synchronization;
[0108] Figure 4 Configure the interface for data import;
[0109] Figure 5 Configure data export interface;
[0110] Figure 6 Configure the interface for the message middleware;
[0111] Figure 7 Configure the interface for the mailbox;
[0112] Figure 8 Configure the interface for aggregate analysis;
[0113] Fig. 9 Design diagrams for data synchronization;
[0114] Fig.10 Design graphs for data comparison and difference;
[0115] Fig.11 Import the design for the file;
[0116] Fig.12 Export design drawings for files;
[0117] Fig.13 Design diagram for data repair module;
[0118] Fig.14 Design diagrams for message middleware modules;
[0119] Fig.15 This is the overall design diagram of the architecture. DETAILED DESCRIPTION
[0120] The content of the present invention will be described below with reference to specific embodiments.
[0121] like Figures 1 to 15 As shown: In order to solve one of the problems existing in the prior art, the present invention discloses an architecture design scheme of a self-developed ETL tool, which involves middleware technology, microservices, database and other technical means. Through database configuration module, database adaptation module, ETL task module, database connection module, data synchronization module, data comparison difference module, data import and export module, data repair module, message module, mailbox configuration module, data aggregation processing module, task sending and receiving module, the above-mentioned major problems can be well solved.
[0122] An architecture design method for a self-developed ETL tool, including: database configuration module, database adaptation module, ETL task module, database connection module, data synchronization module, data comparison and difference module, data import and export module, data repair module, message module, mailbox configuration module, data aggregation processing module, and task sending and receiving module;
[0123] 1. Database configuration module:
[0124] The database configuration module is mainly used to store the configuration of relational database-related connection information, such as the database connection string, user name, and password. This module can add, modify, delete, enable, disable, and view details of the database configuration information.
[0125] 2. Database Adaptation Module:
[0126] The database adapter module is mainly used to adapt to different types of relational databases, such as MYSQL database, ClickHouse database, etc. Here, type adaptation will be performed first, and then operations will be performed under the corresponding type of database. The implementation method is as follows:
[0127] First define the interface DataBaseInitialization, which defines 7 methods: String getDataBaseType(), void beforeProcess(Task task), void getListForBatchInsert(Task task,String sql,DataOperatorTool dataOperatorTool), List <Map<String,Object> >getListForSolveCompare(Task task), void extendMultiFunction(Tasktask,TaskBaseConfig taskBaseConfig), int batchInsertOrUpdate(Task task,List <Map<String,Object> >data,List<Object[]> params,String operateSql,booleanisInsert), int repairData(Task task,String updateSql)
[0128] The specific explanation is as follows:
[0129] String getDataBaseType() returns the type of the current database. For example, if it is a MYSQL database, it returns "mysql";
[0130] voidbeforeProcess(Task task) is the pre-processing method of the task, task represents the current task;
[0131] void getListForBatchInsert(Task task,String sql,DataOperatorTooldataOperatorTool) is a method used to implement data synchronization. Task represents the current task, sql represents the specific SQL statement for querying data, and dataOperatorTool represents specific operations such as data update and addition.
[0132] List <Map<String,Object> >getListForSolveCompare(Task task) is a method for data comparison, where task represents the current task;
[0133] void extendMultiFunction(Task task,TaskBaseConfig taskBaseConfig) is a method for extending functions such as data export, writing to message middleware, http transmission message service, and aggregation processing. Task represents the current task, and taskBaseConfig represents the corresponding configuration.
[0134] int batchInsertOrUpdate(Task task,List <Map<String,Object> >data,List<Object[]> params,String operateSql,boolean isInsert) is the method for inserting data into the database in batches. task represents the current task, operateSql represents the SQL statement of the operation, and isInsert represents whether to insert data into the database.
[0135] int repairData(Task task,String updateSql) is a method for data repair, task represents the current task, updateSql represents the repaired SQL statement;
[0136] After creating the DataBaseInitialization interface class, create a new transmission mode implementation class according to different types of relational databases to implement the DataBaseInitialization interface, and then complete the specific methods in the implementation class; for example, the implementation class of the MYSQL database is defined as MysqlDataBaseUtil, which specifically implements the above 7 methods, and adds the @Component annotation to the specific implementation class. In this way, when starting the service, the Spring Bean mechanism is used to automatically inject this Bean object during the service startup process;
[0137] Define the ToolsBeanUtils tool class and add the @Component annotation to make this class a Bean object. At the same time, let this class implement the ApplicationContextAware interface, and then implement its setApplicationContext(ApplicationContext applicationContext) method. This method performs some operations when the container is initialized. Here, using this technique, you can define a Map in the class<String,DataBaseInitialization> Object, used to adapt to different types of databases, Key represents the method database type name, Value represents the specific implementation class, this Map object is recorded as dataBaseBeans, and is used inside the initialization method through Map<String,DataBaseInitialization> beans = applicationContext.getBeansOfType(DataBaseInitialization.class); Get all classes that implement the DataBaseInitialization interface class in the current service, then traverse dataBaseBeans and insert relevant information into transBeans. In this way, when the container is initialized, the specific processing Bean objects of different types of databases will also be injected into the container;
[0138] In the ToolsBeanUtils class, define the getDataBaseUtil(String type) method. You only need to enter the database type name to get the current database implementation class, and then call the method in it to operate the data. This solves the database adaptation problem.
[0139] 3. ETL task module:
[0140] The ETL task module is mainly used to configure related ETL tasks, such as data synchronization, file import, data comparison, etc. Here, you need to establish a task model first;
[0141] First, define the Task class, which mainly includes task ID: taskId, task name: taskName, CRON expression: cron, pre-processing SQL statement: beforeSql, source database name: sourceDb, source database query SQL statement: sourceSql, source database query table: sourceTableName, target database name: targetDb, target database processing table name: targetTableName, business type: serviceType, comparison fields: compareField, update matching fields: updateWhereKey, whether to perform aggregation processing: isNeedAggregation, data comparison matching fields: whereKey;
[0142] In the background configuration interface, add the corresponding ETL task. After adding, you can modify the task or enable or disable the task. At this point, the ETL task module is finished.
[0143] 4. Database connection module:
[0144] The database connection module is mainly used to connect to the corresponding database, which is mainly accomplished by establishing the DataSourceMgr class;
[0145] The DataSourceMgr class has the following methods: DruidDataSource getDataSource(String dsId), JdbcTemplate getJdbcTemplate(String dsId), DataSourceTransactionManagergetTxManager(String dsId), which are explained as follows: DruidDataSource getDataSource(String dsId) is a method for obtaining the database connection pool. dsId represents the ID of the current database. The returned DruidDataSource is the database connection information. After obtaining the database connection information, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DruidDataSource> The dsMap is used as a cache. When actually called, the database connection information will be obtained from the cache first to avoid creating a database connection every time.
[0146] JdbcTemplate getJdbcTemplate(String dsId) is a method to obtain the database JdbcTemplate. dsId represents the ID of the current database. The returned JdbcTemplate is the database underlying processing template class. After obtaining the database underlying processing template, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,JdbcTemplate> The jdbcMap is used as a cache. When actually calling, the template information will be retrieved from the cache first.
[0147] DataSourceTransactionManager getTxManager(String dsId) is a method for obtaining database transaction processing. dsId represents the ID of the current database. The returned DataSourceTransactionManager is the underlying transaction management class of the database. After obtaining the underlying transaction management class, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DataSourceTransactionManager> txMap is used as a cache. When actually called, the transaction management class will be first retrieved from the cache, so that the database connection problem can be solved.
[0148] 5. Data synchronization module
[0149] The data synchronization module is mainly used to handle the functions related to business data synchronization. There are several ways to handle data synchronization here: direct insert compensation, direct insert and then update compensation, direct update compensation, and direct update and then insert compensation. Because some data needs to be directly inserted into the corresponding table of the target database, some need to be inserted first and then updated, some need to directly update the data in the corresponding database, and some need to update first and then insert the data. The implementation method is as follows:
[0150] First, execute the SQL statement to query the relevant data from the configured source database according to the configured sourceSql, and then process it according to the selected processing method;
[0151] The implementation of different processing methods is as follows:
[0152] Create an interface class DataOperatorTool, which has two methods: int getDataOperatorType() and int[]operator(Task task,List <Map<String,Object> >data,DataBaseInitialization dataBaseUtil), the specific explanation is as follows:
[0153] int getDataOperatorType() is used to obtain the identifier of the data processing method. For example, 0 represents direct insert compensation, 1 represents direct insert and then update compensation, 2 represents direct update compensation, and 3 represents direct update and then insert compensation.
[0154] int[]operator(Task task,List <Map<String,Object> >data,DataBaseInitialization dataBaseUtil) is the specific method for data processing, task represents the current task, data represents the data to be processed in the current query, and dataBaseUtil is the database operation processing tool class;
[0155] Create different DataOperatorTool interface implementation classes: InsertTargetUtil represents direct insert compensation, InsertUpdateRepairUtil represents direct insert and then update compensation, and UpdateRepairUtil;
[0156] Represents direct update compensation, and UpdateInsertRepairUtil represents direct update and then insert compensation; these implementation classes will be annotated with @Component, and using Spring's Bean mechanism, the Bean object will be automatically injected during the service startup process, so that data processing can be performed according to the specific selected operation mode, so that the data synchronization problem can be solved;
[0157] 6. Data comparison and difference module
[0158] The data comparison and difference module is mainly used to process business difference data. For example, if the data in table a of database A is synchronized to table b of database B, if a failure or other reasons occur, some data cannot be synchronized normally. At this time, it is necessary to compare the data of these two tables to find out the data that has not been synchronized successfully. At this time, the relevant functions of the data comparison and difference module are needed; this part of the functions includes: difference data compensation operation, difference data insertion into third-party tables, and difference data notification service; the specific implementation method is as follows:
[0159] Create the ImportWithCompareForDiffService class, which contains two methods: StringvalidTaskCondition(FlowMap r) and void solveData(Task task). This class also references the aforementioned ToolsBeanUtils tool class, which is explained as follows:
[0160] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0161] void solveData(Task task) is a specific method for data processing. Task represents the current task. In this method, the getOperateUtil method in ToolsBeanUtils will be called. Here, the corresponding operation mode will be selected according to the selected operation mode identifier, that is, the aforementioned DataOperatorTool interface implementation class. There are specific processing methods in the corresponding implementation class, which are all related basic database operation methods. At this point, data comparison differences can be solved;
[0162] 7. Data import and export module
[0163] The data import and export module is mainly used to process the import and export of data, such as importing data from an Excel file to table a of database A, and exporting data from table b of database B to an Excel file; the specific implementation method is as follows:
[0164] First, define the FileImportService class, which is a class specifically used to import files. This class defines three methods: String validTaskCondition(FlowMap r), validFiledValue(FileImportConfigfileImportConfig,Map<String,Object> object) and readExcelAndImportData(String filePath,Task task,DataBaseInitialization dataBaseUtil,FileImportConfigfileImportConfig), the specific explanations are as follows:
[0165] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0166] validFiledValue(FileImportConfig fileImportConfig,Map<String,Object> object) is to verify the specific imported file configuration. Here, the import module has related pages for file import configuration, so its legitimacy will be verified, such as whether the file exists on the server and whether the file format is correct.
[0167] Map<String,Object> object) and readExcelAndImportData(String filePath,Task task,DataBaseInitialization dataBaseUtil,FileImportConfigfileImportConfig) are used to process specific import files, mainly using JAVA's POI-related jar packages. It should be noted that the header of the import file needs to correspond to the field to be inserted into the table;
[0168] Define the ExportDataService class, which is a class specifically used to export files. This class defines three methods: getListForExport(Task task), export(Task task,List <Map<String,Object> >data,boolean flag,TaskBaseConfig taskBaseConfig) and sendEmailForCompressFile(Tasktask,String filePath), the specific explanations are as follows:
[0169] getListForExport(Task task) is a method to obtain the data list to be exported. Task represents the current task. It mainly queries the required data through the data source configured in the task and the related SQL statements.
[0170] export(Task task,List <Map<String,Object> >data,TaskBaseConfigtaskBaseConfig) is the specific method of exporting data. Task represents the current task, data represents the data obtained by the previous query, and taskBaseConfig represents the related configuration data for export, such as the maximum number of export rows for a single file, and whether the export format is xlsx file or csv file. This solves the import and export problem.
[0171] sendEmailForCompressFile(Task task,String filePath) represents the method of sending data by email. Task represents the current task, and filePath represents the export file generated in the previous step and stored on the server. It will be sent to the relevant export data users by email.
[0172] 8. Data repair module
[0173] The data repair module is mainly used to process and modify data. For example, if a data error occurs in table a of database A and needs to be modified, the relevant SQL statements can be directly executed through the repair module to achieve the purpose. The specific implementation method is as follows:
[0174] Create the RepairDataService class, which is a class dedicated to data repair. It has two methods: StringvalidTaskCondition(FlowMap r) and void solveData(Tasktask). The specific explanations are as follows:
[0175] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0176] void solveData(Task task) is a specific method for data repair. In this method, the database and corresponding table name to be modified, as well as the modification SQL to be executed, will be found through the task configuration information. Finally, the SQL statement will be executed to achieve the purpose of modifying the data, and the modification results will be sent to relevant personnel by email. At this point, the data repair problem is solved.
[0177] 8. Message Module
[0178] The message module is mainly used to write the data to be queried into the message middleware module. For example, if a data error occurs in table a of database A and needs to be modified, the relevant SQL statements can be directly executed through the function of the repair module to achieve the purpose. The specific implementation method is as follows:
[0179] Create a MessageService class, which is a class dedicated to handling message-related services. This class defines three methods: String validTaskCondition(FlowMap r), MessageConfig getMessageConfig(inttaskId), and solveMessage(Task task,List <Map<String,Object> >data,boolean flag,TaskBaseConfig taskBaseConfig), the specific explanation is as follows:
[0180] String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc.
[0181] getListForMessage(Task task) is a method to obtain the data list to be exported. Task represents the current task. It mainly queries the required data through the data source configured in the task and the related SQL statements.
[0182] MessageConfig getMessageConfig(int taskId) is used to obtain the configuration of the corresponding message middleware according to the task ID. taskId represents the task ID, and MessageConfig represents the configuration of the message middleware.
[0183] solveMessage(Task task,List <Map<String,Object> >data,MessageConfigmessageConfig) is the specific method for processing messages, task represents the current task, data represents the queried business data, and messageConfigs is the configuration of the message middleware; within this method, it will be judged according to the message middleware category configured by the task. If Kafka is configured, then the relevant configuration of Kafka will be read, mainly the topic to be written to the message: topic; if rabbitMq is configured, then the relevant configuration of rabbitMq will be read, mainly the message name: messageName, routing KEY: routeKey, exchanger: exchange. Finally, the relevant business data is written into the message middleware, so the problem of writing data into the message middleware is solved;
[0184] 8. Mailbox Configuration Module
[0185] The mailbox configuration module is mainly used to notify relevant business personnel of the ETL task execution status. Of course, users can choose to send emails only when the task execution fails. You can choose in the interface configuration; the specific implementation method is as follows:
[0186] First, create the EtlMailSenderConfig class, which is a class for mailbox configuration. This class defines several member variables: the IP address of the sender's mailbox: host, the sender's mailbox port: port, the sender's mailbox username: username, the sender's mailbox password: password; there is also a method in this class: Message generateMessage(String title, List <string>sendToList), the specific explanation is as follows:
[0187] Message generateMessage(String title,List <string>sendToList) is the combined email sending template information, title represents the title of the email, sendToList represents the email address set for receiving emails, Message represents the email sending template information, and several member variables of the above email configuration will be written into Message. At the same time, the @Component annotation is added to the EtlMailSenderConfig class, so that when the service is started, the Spring Bean mechanism is used to automatically inject this Bean object during the service startup process;
[0188] Then create the EtlSendEmailService class, which is a class dedicated to sending emails. The EtlMailSenderConfig class mentioned above will be injected into this class. This class defines two methods: sendMail(int taskId, String title, String content, ByteArrayInputStreamin, String filename) and saveMailLog(inttaskId, long costTime, int status, String errorMessage, String attachment, String title, String content). The specific explanations are as follows:
[0189] sendMail(int taskId,String title,String content,ByteArrayInputStreamin,String filename,boolean isSuccess) is a specific method for sending emails. taskId represents the task ID, title represents the email subject, content represents the content of the email, in represents the attachment file stream to be transmitted, and filename represents the attachment file name. In this method, the sendMail(int taskId,String title,String content,ByteArrayInputStreamin,Stringfilename) method of the EtlMailSenderConfig class is called to assemble the relevant email message and then send it out.
[0190] saveMailLog(int taskId,long costTime,int status,String errorMessage,String attachment,String title,String content) is a method for recording the log of sending emails, which will be called inside the sendMail method. taskId represents the task ID, costTime represents the time taken to send the email, status represents whether the email is sent successfully or not, 0 represents failure, 1 represents success, errorMessage represents the reason for the failure of sending the email, attachment represents the file name of the attachment, title represents the subject of the email, and content represents the content of the email. The purpose of recording the log of sending emails is mainly to prevent the failure of sending emails. If the current task fails to send the email, a scheduled task retry will be performed. The specific retry method is as follows:
[0191] Create the FailEmailSendScheduler class, which is a class specifically used to detect failed email sending tasks and resend them. The EtlSendEmailService class is injected into this class, and it has a void queryFailEmail() method. The specific explanation is as follows:
[0192] void queryFailEmail() is a method for retrieving tasks that failed to send emails and resending them. This is a scheduled task that is executed every half an hour. The specific implementation is as follows:
[0193] First, the database will be queried for the task that failed to send the email, and then the relevant email data will be reassembled. Finally, the email will be sent through the sendMail(int taskId,String title,String content,ByteArrayInputStreamin,String filename,boolean isSuccess) method of the EtlSendEmailService class, thus solving the problem of email configuration.
[0194] 9. Data Aggregation Processing Module
[0195] The data aggregation processing module is mainly used to aggregate the data before inserting it into the database. The aggregation processing here mainly includes the average value, maximum value, minimum value, mode, standard deviation and other characteristic values with statistical properties. The specific implementation method is as follows:
[0196] Create an interface class AggregationOperator, which has two methods: int getType() and List <Map<String,Object> >transformData(Task task,List <Map<String,Object> >data,AggregationConfig aggregationConfig), the specific explanation is as follows:
[0197] int getType() is a method to obtain the type of aggregation processing, and returns the identifier of the aggregation processing type. There are three types: 0: simple column calculation, 1: group statistics, 2: simple statistics. The specific implementation method is as follows:
[0198] Create two implementation classes of the AggregationOperator interface class: SimpleColumnAggregationOperator and GroupStatisticAggregationOperator. The specific explanations are as follows:
[0199] The SimpleColumnAggregationOperator class is a simple column calculation, which is mainly used for the simple addition of several columns in a database table to form a new column. For example, in table a of database A, the data in columns 1, 2, and 3 need to be added together to form a new column, recorded as column n, and then inserted into the new table. This is a function required in some application scenarios.
[0200] The SimpleColumnAggregationOperator class is a simple column calculation, which is mainly used for the simple addition of several columns in a database table to form a new column. For example, in table a of database A, the data in columns 1, 2, and 3 need to be added to form a new column, recorded as column n, and then inserted into the new table. This is a function required in some application scenarios. Note that the format in the "Aggregation Calculation Field Rule Configuration" column of the interface is "column 1, column 2, column 3#new column name", the column before "#" is the column to be added, and the name of the new field formed by adding these columns is after "#".
[0201] The GroupStatisticAggregationOperator class is a grouping statistic, which is mainly used for data statistics, such as calculating the average value, maximum value, minimum value, mode, standard deviation and other characteristic values with statistical properties; the specific implementation method is as follows:
[0202] Create an interface class StatisticOperator, which is an interface class for processing group statistics methods. There are two methods in this class: String getType() and Object getCalculatorResult(Task task, List <Map<String,Object> >mapList,StatisticOperatorField statisticOperatorField), the specific explanation is as follows:
[0203] String getType() is used to obtain the type name of the grouping statistical processing method, such as "sum" for sum function, "ariavg" for arithmetic mean function, "max" for maximum value function, "min" for minimum value function, "geoavg" for geometric mean function, "sta" for standard deviation function, etc.
[0204] Object getCalculatorResult(Task task,List <Map<String,Object> >mapList,StatisticOperatorField statisticOperatorField), this is a method for performing specific statistical function calculations on data. task represents the current task, mapList represents the current queried business data to be processed, statisticOperatorField represents the field conversion model, and the StatisticOperatorField class has three member variables: original field name: originField, processing function identifier: operatorFlag, and new field name after processing: newField; in this method, the task will select the corresponding statistical function according to the configured processing identifier. The statistical function selection method is implemented as follows:
[0205] Different StatisticOperator interface implementation classes are established for different processing methods. For example, the sum function implementation class is called SumStatisticOperator, the maximum function is called MaxStatisticOperator, the minimum function implementation class is called MinStatisticOperator, the geometric mean function is called GeoStatisticOperator, and the arithmetic mean function is called AriAvgStatisticOperator. When processing specific statistical tasks, the corresponding function processing class will be selected according to the configured identifier, and then there are specific operations inside the class. At this point, the problem of data aggregation processing is solved.
[0206] 10. Task sending and receiving module
[0207] The task sending and receiving module is mainly used for sending and receiving tasks. Here, microservices are mainly used to implement the functions of this module. The sending module is implemented by the etlfschedulerserver service, and the receiving module is implemented by the dataextractfilter service. The specific implementation is as follows:
[0208] There are two interfaces in etlfschedulerserver: receiveRegister and receiveTaskResult, which are explained as follows:
[0209] receiveRegister is a method for registering heartbeats. Since microservices are deployed in a distributed manner, the task dispatching module is a core module and consistency must be ensured to avoid duplication or non-dispatching of tasks. REDIS middleware is used here to implement this. Before dispatching a task, the task to be executed at the current time point will be queried from the database, and then the value of the corresponding KEY in REDIS will be read to determine whether there is a task dispatched at the current time point. If it has been dispatched, no operation will be performed. If it has not been dispatched, the current service will be dispatched, and the dispatched service number set will be written into the value of the corresponding KEY in REDIS, so that REDIS contains the latest task dispatching information.
[0210] receiveTaskResult is a method for receiving task execution results. This interface is mainly used by the task receiving service to actively call this interface to notify the task issuing module of the task execution status. When the task issuing module receives the task execution status, it will modify the task status in the database on the one hand, and modify REDIS to update the corresponding KEY value on the other hand, and remove the completed task number.
[0211] There is an interface in dataextractfilter: receiveTask, which is explained as follows:
[0212] The input parameter of receiveTask interface is only taskIdList, which is a List <interger>Type, represents the task number collection under the task. After receiving the required task number, the interface starts to execute the related tasks. Here, each task is directly thrown into the thread pool for execution, and is distributed and executed in order according to the order of the collection. After each task is completed, the receiveTaskResult interface of the etlfschedulerserver service will be called to feed back the current task execution result to the task delivery module. After receiving the task execution result, the task delivery module updates the cache information in REDIS. At this point, the problem of task delivery and reception is solved.
[0213] The above content is a detailed description of the present invention in combination with specific embodiments, and it cannot be determined that the specific implementation of the present invention is limited to these descriptions. For those skilled in the art of the present invention, without departing from the concept of the present invention, several simple deductions or substitutions can be made, which should be regarded as falling within the scope of protection of the present invention.< / interger> < / string> < / string> < / interger> < / string> < / string>
Claims
1. A method for designing the architecture of a self-developed ETL tool, characterized by: include: Database configuration module, database adaptation module, ETL task module, database connection module, data synchronization module, data comparison and difference module, data import and export module, data repair module, message module, mailbox configuration module, data aggregation processing module, task issuing and receiving module; 1. Database configuration module: The database configuration module is mainly used to store the configuration of relational database-related connection information, such as database connection string, user name, and password. This module can add, modify, delete, enable, disable, and view details of database configuration information.
2. Database Adaptation Module: The database adapter module is mainly used to adapt to different types of relational databases, such as MYSQL database, ClickHouse database, etc. Here, type adaptation will be performed first, and then operations will be performed under the corresponding type of database. The implementation method is as follows: First define the interface DataBaseInitialization, which defines 7 methods: String getDataBaseType(), void beforeProcess(Task task), void getListForBatchInsert(Task task,String sql,DataOperatorTool dataOperatorTool), List <Map<String,Object> >getListForSolveCompare(Task task), void extendMultiFunction(Tasktask,TaskBaseConfig taskBaseConfig), int batchInsertOrUpdate(Task task,List <Map<String,Object> >data,List<Object[]> params,String operateSql,booleanisInsert), int repairData(Task task,String updateSql) The specific explanation is as follows: String getDataBaseType() returns the type of the current database. For example, if it is a MYSQL database, it returns "mysql"; voidbeforeProcess(Task task) is the pre-processing method of the task, task represents the current task; void getListForBatchInsert(Task task,String sql,DataOperatorTooldataOperatorTool) is a method used to implement data synchronization. Task represents the current task, sql represents the specific SQL statement for querying data, and dataOperatorTool represents specific operations such as data update and addition. List <Map<String,Object> >getListForSolveCompare(Task task) is a method for data comparison, where task represents the current task; void extendMultiFunction(Task task,TaskBaseConfig taskBaseConfig) is a method for extending functions such as data export, writing to message middleware, http transmission message service, and aggregation processing. Task represents the current task, and taskBaseConfig represents the corresponding configuration. int batchInsertOrUpdate(Task task,List <Map<String,Object> >data,List<Object[]> params,String operateSql,boolean isInsert) is the method for inserting data into the database in batches. task represents the current task, operateSql represents the SQL statement of the operation, and isInsert represents whether to insert data into the database. int repairData(Task task,String updateSql) is a method for data repair, task represents the current task, updateSql represents the repaired SQL statement; After creating the DataBaseInitialization interface class, create a new transmission mode implementation class according to different types of relational databases to implement the DataBaseInitialization interface, and then complete the specific methods in the implementation class; for example, the implementation class of the MYSQL database is defined as MysqlDataBaseUtil, which specifically implements the above 7 methods, and adds the @Component annotation to the specific implementation class. In this way, when starting the service, the Spring Bean mechanism is used to automatically inject this Bean object during the service startup process; Define the ToolsBeanUtils tool class and add the @Component annotation to make this class a Bean object. At the same time, let this class implement the ApplicationContextAware interface, and then implement its setApplicationContext(ApplicationContext applicationContext) method. This method performs some operations when the container is initialized. Here, using this technique, you can define a Map in the class<String,DataBaseInitialization> Object, used to adapt to different types of databases, Key represents the method database type name, Value represents the specific implementation class, this Map object is recorded as dataBaseBeans, and is used inside the initialization method through Map<String,DataBaseInitialization> beans = applicationContext.getBeansOfType(DataBaseInitialization.class); Get all classes that implement the DataBaseInitialization interface class in the current service, then traverse dataBaseBeans and insert relevant information into transBeans. In this way, when the container is initialized, the specific processing Bean objects of different types of databases will also be injected into the container; In the ToolsBeanUtils class, define the getDataBaseUtil(String type) method. You only need to enter the database type name to get the current database implementation class, and then call the method in it to operate the data. This solves the database adaptation problem.
3. ETL task module: The ETL task module is mainly used to configure related ETL tasks, such as data synchronization, file import, data comparison, etc. Here, you need to establish a task model first; First, define the Task class, which mainly includes task ID: taskId, task name: taskName, CRON expression: cron, pre-processing SQL statement: beforeSql, source database name: sourceDb, source database query SQL statement: sourceSql, source database query table: sourceTableName, target database name: targetDb, target database processing table name: targetTableName, business type: serviceType, comparison fields: compareField, update matching fields: updateWhereKey, whether to perform aggregation processing: isNeedAggregation, data comparison matching fields: whereKey; In the background configuration interface, add the corresponding ETL task. After adding, you can modify the task or enable or disable the task. At this point, the ETL task module is finished.
4. Database connection module: The database connection module is mainly used to connect to the corresponding database, which is mainly accomplished by establishing the DataSourceMgr class; The DataSourceMgr class has the following methods: DruidDataSource getDataSource(String dsId), JdbcTemplate getJdbcTemplate(String dsId), DataSourceTransactionManagergetTxManager(String dsId), which are explained as follows: DruidDataSource getDataSource(String dsId) is a method for obtaining the database connection pool. dsId represents the ID of the current database. The returned DruidDataSource is the database connection information. After obtaining the database connection information, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DruidDataSource> The dsMap is used as a cache. When actually called, the database connection information will be obtained from the cache first to avoid creating a database connection every time. JdbcTemplate getJdbcTemplate(String dsId) is a method to obtain the database JdbcTemplate. dsId represents the ID of the current database. The returned JdbcTemplate is the database underlying processing template class. After obtaining the database underlying processing template, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,JdbcTemplate> The jdbcMap is used as a cache. When actually calling, the template information will be retrieved from the cache first. DataSourceTransactionManager getTxManager(String dsId) is a method for obtaining database transaction processing. dsId represents the ID of the current database. The returned DataSourceTransactionManager is the underlying transaction management class of the database. After obtaining the underlying transaction management class, the relevant information will be stored in the ConcurrentHashMap of the DataSourceMgr class.<String,DataSourceTransactionManager> txMap is used as a cache. When actually called, the transaction management class will be first retrieved from the cache, so that the database connection problem can be solved.
5. Data Synchronization Module The data synchronization module is mainly used to handle the functions related to business data synchronization. There are several ways to handle data synchronization here: direct insert compensation, direct insert and then update compensation, direct update compensation, direct update and then insert compensation. Because some data needs to be directly inserted into the corresponding table of the target database, some need to be inserted first and then updated, some need to directly update the data in the corresponding database, and some need to update first and then insert the data. The implementation method is as follows: First, execute the SQL statement to query the relevant data from the configured source database according to the configured sourceSql, and then process it according to the selected processing method; The implementation of different processing methods is as follows: Create an interface class DataOperatorTool, which has two methods: int getDataOperatorType() and int[]operator(Task task,List <Map<String,Object> >data,DataBaseInitializationdataBaseUtil), the specific explanation is as follows: int getDataOperatorType() is used to obtain the identifier of the data processing method. For example, 0 represents direct insert compensation, 1 represents direct insert and then update compensation, 2 represents direct update compensation, and 3 represents direct update and then insert compensation. int[]operator(Task task,List <Map<String,Object> >data,DataBaseInitialization dataBaseUtil) is the specific method for data processing, task represents the current task, data represents the data to be processed in the current query, and dataBaseUtil is the database operation processing tool class; Create different types of DataOperatorTool interface implementation classes: InsertTargetUtil represents direct insertion compensation, InsertUpdateRepairUtil represents direct insertion and then update compensation, and UpdateRepairUtil; Represents direct update compensation, and UpdateInsertRepairUtil represents direct update and then insert compensation; these implementation classes will be annotated with @Component, and using Spring's Bean mechanism, the Bean object will be automatically injected during the service startup process, so that data processing can be performed according to the specific selected operation mode, so that the data synchronization problem can be solved; 6. Data comparison and difference module The data comparison and difference module is mainly used to process business difference data. For example, if the data in table a of database A is synchronized to table b of database B, if a failure or other reasons occur, some data cannot be synchronized normally. At this time, it is necessary to compare the data of these two tables to find out the data that has not been synchronized successfully. At this time, the relevant functions of the data comparison and difference module are needed; this part of the functions includes: difference data compensation operation, difference data insertion into third-party tables, and difference data notification service; the specific implementation method is as follows: Create the ImportWithCompareForDiffService class, which contains two methods: StringvalidTaskCondition(FlowMap r) and void solveData(Task task). This class also references the aforementioned ToolsBeanUtils tool class, which is explained as follows: String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc. void solveData(Task task) is a specific method for data processing. Task represents the current task. In this method, the getOperateUtil method in ToolsBeanUtils will be called. Here, the corresponding operation mode will be selected according to the selected operation mode identifier, that is, the aforementioned DataOperatorTool interface implementation class. There are specific processing methods in the corresponding implementation class, which are all related basic database operation methods. At this point, data comparison differences can be solved; 7. Data import and export module The data import and export module is mainly used to process the import and export of data, such as importing data from an Excel file to table a of database A, and exporting data from table b of database B to an Excel file; the specific implementation method is as follows: First, define the FileImportService class, which is a class specifically used to import files. This class defines three methods: String validTaskCondition(FlowMap r), validFiledValue(FileImportConfigfileImportConfig,Map<String,Object> object) and readExcelAndImportData(String filePath,Task task,DataBaseInitialization dataBaseUtil,FileImportConfigfileImportConfig), the specific explanations are as follows: String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc. validFiledValue(FileImportConfig fileImportConfig,Map<String,Object> object) is to verify the specific imported file configuration. Here, the import module has related pages for file import configuration, so its legitimacy will be verified, such as whether the file exists on the server and whether the file format is correct. Map<String,Object> object) and readExcelAndImportData(String filePath,Tasktask,DataBaseInitialization dataBaseUtil,FileImportConfig fileImportConfig) are used to process specific import files, mainly using JAVA's POI-related jar packages. It should be noted that the header of the import file needs to correspond to the field to be inserted into the table; Define the ExportDataService class, which is a class specifically used to export files. This class defines three methods: getListForExport(Task task), export(Task task,List <Map<String,Object> >data,boolean flag,TaskBaseConfig taskBaseConfig) and sendEmailForCompressFile(Tasktask,String filePath), the specific explanations are as follows: getListForExport(Task task) is a method to obtain the data list to be exported. Task represents the current task. It mainly queries the required data through the data source configured in the task and the related SQL statements. export(Task task,List <Map<String,Object> >data,TaskBaseConfigtaskBaseConfig) is the specific method of exporting data. Task represents the current task, data represents the data obtained by the previous query, and taskBaseConfig represents the related configuration data for export, such as the maximum number of export rows for a single file, and whether the export format is xlsx file or csv file. This solves the import and export problem. sendEmailForCompressFile(Task task,String filePath) represents the method of sending data by email. Task represents the current task, and filePath represents the export file generated in the previous step and stored on the server. It will be sent to the relevant export data users by email.
8. Data repair module The data repair module is mainly used to process and modify data. For example, if a data error occurs in table a of database A and needs to be modified, the relevant SQL statements can be directly executed through the repair module to achieve the purpose. The specific implementation method is as follows: Create the RepairDataService class, which is a class dedicated to data repair. It has two methods: StringvalidTaskCondition(FlowMap r) and void solveData(Tasktask). The specific explanations are as follows: String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc. void solveData(Task task) is a specific method for data repair. In this method, the database and corresponding table name to be modified, as well as the modification SQL to be executed, will be found through the task configuration information. Finally, the SQL statement will be executed to achieve the purpose of modifying the data, and the modification results will be sent to relevant personnel by email. At this point, the data repair problem is solved.
8. Message Module The message module is mainly used to write the data to be queried into the message middleware module. For example, if a data error occurs in table a of database A and needs to be modified, the relevant SQL statements can be directly executed through the function of the repair module to achieve the purpose. The specific implementation method is as follows: Create a MessageService class, which is a class dedicated to handling message-related services. This class defines three methods: String validTaskCondition(FlowMap r), MessageConfig getMessageConfig(inttaskId), and solveMessage(Task task,List <Map<String,Object> >data,boolean flag,TaskBaseConfig taskBaseConfig), the specific explanation is as follows: String validTaskCondition(FlowMap r) is a method for checking task parameters. r is some task-related parameters. Before executing a specific task, it is necessary to check the validity of some necessary parameters, such as database configuration parameters, correctness and existence of table names, etc. getListForMessage(Task task) is a method to obtain the data list to be exported. Task represents the current task. It mainly queries the required data through the data source configured in the task and the related SQL statements. MessageConfig getMessageConfig(int taskId) is used to obtain the configuration of the corresponding message middleware according to the task ID. taskId represents the task ID, and MessageConfig represents the configuration of the message middleware. solveMessage(Task task,List <Map<String,Object> >data,MessageConfigmessageConfig) is the specific method for processing messages, task represents the current task, data represents the queried business data, and messageConfigs is the configuration of the message middleware; within this method, it will be judged according to the message middleware category configured by the task. If Kafka is configured, then the relevant configuration of Kafka will be read, mainly the topic to be written to the message: topic; if rabbitMq is configured, then the relevant configuration of rabbitMq will be read, mainly the message name: messageName, routing KEY: routeKey, exchanger: exchange. Finally, the relevant business data is written into the message middleware, so the problem of writing data into the message middleware is solved; 8. Mailbox Configuration Module The mailbox configuration module is mainly used to notify relevant business personnel of the ETL task execution status. Of course, users can choose to send emails only when the task execution fails. You can choose in the interface configuration; the specific implementation method is as follows: First, create the EtlMailSenderConfig class, which is a class for mailbox configuration. This class defines several member variables: the IP address of the sender's mailbox: host, the sender's mailbox port: port, the sender's mailbox username: username, the sender's mailbox password: password; there is also a method in this class: Message generateMessage(String title, List<String> <string> sendToList), the specific explanation is as follows:< / string> Message generateMessage(String title,List <string>sendToList) is the combined email sending template information, title represents the title of the email, sendToList represents the email address set for receiving emails, Message represents the email sending template information, and several member variables of the above email configuration will be written into Message. At the same time, the @Component annotation is added to the EtlMailSenderConfig class, so that when the service is started, the Spring Bean mechanism is used to automatically inject this Bean object during the service startup process;< / string> Then create the EtlSendEmailService class, which is a class dedicated to sending emails. The EtlMailSenderConfig class mentioned above will be injected into this class. This class defines two methods: sendMail(int taskId, String title, String content, ByteArrayInputStreamin, String filename) and saveMailLog(inttaskId, long costTime, int status, String errorMessage, String attachment, String title, String content). The specific explanations are as follows: sendMail(int taskId,String title,String content,ByteArrayInputStreamin,String filename,boolean isSuccess) is a specific method for sending emails. taskId represents the task ID, title represents the email subject, content represents the content of the email, in represents the attachment file stream to be transmitted, and filename represents the attachment file name. In this method, the sendMail(int taskId,Stringtitle,String content,ByteArrayInputStreamin,String filename) method of the EtlMailSenderConfig class is called to assemble the relevant email message and then send it out. saveMailLog(int taskId,long costTime,int status,String errorMessage,String attachment,String title,String content) is a method for recording the log of sending emails, which will be called inside the sendMail method. taskId represents the task ID, costTime represents the time taken to send the email, status represents whether the email is sent successfully or not, 0 represents failure, 1 represents success, errorMessage represents the reason for the failure of sending the email, attachment represents the file name of the attachment, title represents the subject of the email, and content represents the content of the email. The purpose of recording the log of sending emails is mainly to prevent the failure of sending emails. If the current task fails to send the email, a scheduled task retry will be performed. The specific retry method is as follows: Create the FailEmailSendScheduler class, which is a class specifically used to detect failed email sending tasks and resend them. The EtlSendEmailService class is injected into this class, and it has a void queryFailEmail() method. The specific explanation is as follows: void queryFailEmail() is a method for retrieving tasks that failed to send emails and resending them. This is a scheduled task that is executed every half an hour. The specific implementation is as follows: First, the database will be queried for the task that failed to send the email, and then the relevant email data will be reassembled. Finally, the email will be sent through the sendMail(int taskId,String title,String content,ByteArrayInputStreamin,String filename,boolean isSuccess) method of the EtlSendEmailService class, thus solving the problem of email configuration.
9. Data Aggregation Processing Module The data aggregation processing module is mainly used to aggregate the data before inserting it into the database. The aggregation processing here mainly includes the average value, maximum value, minimum value, mode, standard deviation and other characteristic values with statistical properties. The specific implementation method is as follows: Create an interface class AggregationOperator, which has two methods: int getType() and List <Map<String,Object> >transformData(Task task,List <Map<String,Object> >data,AggregationConfig aggregationConfig), the specific explanation is as follows: int getType() is a method to obtain the type of aggregation processing, and returns the identifier of the aggregation processing type. There are three types: 0: simple column calculation, 1: group statistics, 2: simple statistics. The specific implementation method is as follows: Create two implementation classes of the AggregationOperator interface class: SimpleColumnAggregationOperator and GroupStatisticAggregationOperator. The specific explanations are as follows: The SimpleColumnAggregationOperator class is a simple column calculation, which is mainly used for the simple addition of several columns in a database table to form a new column. For example, in table a of database A, the data in columns 1, 2, and 3 need to be added together to form a new column, recorded as column n, and then inserted into the new table. This is a function required in some application scenarios. The SimpleColumnAggregationOperator class is a simple column calculation, which is mainly used for the simple addition of several columns in a database table to form a new column. For example, in table a of database A, the data in columns 1, 2, and 3 need to be added to form a new column, recorded as column n, and then inserted into the new table. This is a function required in some application scenarios. Note that the format in the "Aggregation Calculation Field Rule Configuration" column of the interface is "column 1, column 2, column 3#new column name", the column before "#" is the column to be added, and the name of the new field formed by adding these columns is after "#". The GroupStatisticAggregationOperator class is a grouping statistic, which is mainly used for data statistics, such as calculating the average value, maximum value, minimum value, mode, standard deviation and other characteristic values with statistical properties; the specific implementation method is as follows: Create an interface class StatisticOperator, which is an interface class for processing group statistics methods. There are two methods in this class: String getType() and Object getCalculatorResult(Task task, List <Map<String,Object> >mapList,StatisticOperatorField statisticOperatorField), the specific explanation is as follows: String getType() is used to obtain the type name of the grouping statistical processing method, such as "sum" for sum function, "ariavg" for arithmetic mean function, "max" for maximum value function, "min" for minimum value function, "geoavg" for geometric mean function, "sta" for standard deviation function, etc. Object getCalculatorResult(Task task,List <Map<String,Object> >mapList,StatisticOperatorField statisticOperatorField), this is a method for performing specific statistical function calculations on data. task represents the current task, mapList represents the current queried business data to be processed, statisticOperatorField represents the field conversion model, and the StatisticOperatorField class has three member variables: original field name: originField, processing function identifier: operatorFlag, and new field name after processing: newField; in this method, the task will select the corresponding statistical function according to the configured processing identifier. The statistical function selection method is implemented as follows: Different StatisticOperator interface implementation classes are established for different processing methods. For example, the sum function implementation class is called SumStatisticOperator, the maximum function is called MaxStatisticOperator, the minimum function implementation class is called MinStatisticOperator, the geometric mean function is called GeoStatisticOperator, and the arithmetic mean function is called AriAvgStatisticOperator. When processing specific statistical tasks, the corresponding function processing class will be selected according to the configured identifier, and then there are specific operations inside the class. At this point, the problem of data aggregation processing is solved.
10. Task sending and receiving module The task sending and receiving module is mainly used for sending and receiving tasks. Here, microservices are mainly used to implement the functions of this module. The sending module is implemented by the etlfschedulerserver service, and the receiving module is implemented by the dataextractfilter service. The specific implementation is as follows: There are two interfaces in etlfschedulerserver: receiveRegister and receiveTaskResult, which are explained as follows: receiveRegister is a method for registering heartbeats. Since microservices are deployed in a distributed manner, the task dispatching module is a core module and consistency must be ensured to avoid duplication or non-dispatching of tasks. REDIS middleware is used here to implement this. Before dispatching a task, the task to be executed at the current time point will be queried from the database, and then the value of the corresponding KEY in REDIS will be read to determine whether there is a task dispatched at the current time point. If it has been dispatched, no operation will be performed. If it has not been dispatched, the current service will be dispatched, and the dispatched service number set will be written into the value of the corresponding KEY in REDIS, so that REDIS contains the latest task dispatching information. receiveTaskResult is a method for receiving task execution results. This interface is mainly used by the task receiving service to actively call this interface to notify the task issuing module of the task execution status. When the task issuing module receives the task execution status, it will modify the task status in the database on the one hand, and modify REDIS to update the corresponding KEY value on the other hand, and remove the completed task number. There is an interface in dataextractfilter: receiveTask, which is explained as follows: The input parameter of receiveTask interface is only taskIdList, which is a List <interger> Type, represents the task number collection under the task. After receiving the required task number, the interface starts to execute the related tasks. Here, each task is directly thrown into the thread pool for execution, and is distributed and executed in order according to the order of the collection. After each task is completed, the receiveTaskResult interface of the etlfschedulerserver service will be called to feed back the current task execution result to the task delivery module. After receiving the task execution result, the task delivery module updates the cache information in REDIS. At this point, the problem of task delivery and reception is solved.< / interger>
