Templated heterogeneous database ETL method, system and device and storage medium

Abstract

The invention provides a templated heterogeneous database ETL method, system and device and a storage medium. The templated heterogeneous database ETL method comprises the following steps: performing ETL template parameter configuration according to heterogeneous database entity information; creating at least one ETL job according to the configured template parameters; and executing an ETL execution command corresponding to the at least one ETL job, and reading parameter information in a configuration file or a parameter library through the template parameters in the execution command so as to execute the at least one ETL job. According to the method and the device, a large number of ETL operation mechanisms can be quickly developed through simple table name instantiation, so that the ETL operation development efficiency is further improved, and the user experience is improved.

Description

Technical Field

[0001] The present disclosure relates to the field of asset management, and in particular to a templated heterogeneous database ETL method, system, device, and storage medium. Background Art

[0002] The asset management industry refers to the investors entrusting financial institutions to invest and manage their assets, including funds, bank wealth management, insurance, trust and other institutions. At present, the scale of the asset management industry continues to expand, and the current status of the mainstream system construction in the industry is a combination of finished outsourced software and self-developed systems, and there are various types of relational databases. In the process of building a company-level data lake, data warehouse or data middle platform, it is necessary to integrate data from different types of databases of various systems. Currently, ETL is widely used for cross-database transmission and integration of data.

[0003] ETL: It is the abbreviation of Extract-Transform-Load, which is used to describe the process of extracting (extract), transforming (transform), and loading (load) data from the source end to the destination end.

[0004] With the construction of data lakes and data warehouses and the development of corresponding application data analysis, there are certain requirements for data quality. It is necessary to check, clean and standardize the data in the ETL process. This type of processing covers a wide range and is relatively cumbersome in configuration. The accuracy and efficiency of development need to be further improved.

[0005] In addition, the current mainstream ETL tools need to configure the field mapping between the source database and the target database for each job during the ETL job development process. This is inefficient when faced with the development of a large number of cross-database ETL jobs with relatively simple synchronization logic in the asset management industry. Summary of the invention

[0006] The present disclosure is completed to solve the above-mentioned problems, and its purpose is to provide a templated heterogeneous database ETL method, system, device, and storage medium that can quickly develop a large number of ETL job mechanisms through simple table name instantiation to further improve the efficiency of ETL job development.

[0007] The present disclosure provides this summary of the invention to introduce concepts in a brief form, which will be described in detail in the detailed implementation section below. This summary of the invention is not intended to identify the key features or essential features of the technical solution claimed for protection, nor is it intended to be used to limit the scope of the technical solution claimed for protection.

[0008] In order to solve the above technical problems, the embodiment of the present disclosure provides a templated heterogeneous database ETL method, which adopts the following technical solution, including: Configure ETL template parameters based on heterogeneous database entity information; Create at least one ETL job according to the configured template parameters; Execute an ETL execution command corresponding to the at least one ETL job, and read parameter information in a configuration file or a parameter library through the template parameter in the execution command to execute the at least one ETL job; The template parameters include at least one of the acquisition parameters of the source data source of the ETL, the preprocessing parameters, and the data loading parameters of the target data source; The ETL execution command is used to perform an ETL operation including at least one of data collection, preprocessing, and data loading.

[0009] In order to solve the above technical problems, the embodiment of the present disclosure further provides a templated heterogeneous database ETL system, which adopts the following technical solutions, including: Template parameter configuration component, used to configure ETL template parameters based on heterogeneous database entity information; An ETL job creation component, used to create at least one ETL job according to the configured template parameters; The ETL job execution component is used to execute the ETL execution command corresponding to the at least one ETL job, and read the parameter information in the configuration file or parameter library through the template parameter in the execution command to execute the at least one ETL job.

[0010] In order to solve the above technical problems, the embodiment of the present disclosure further provides a computer device, which adopts the following technical solution, including: A memory and a processor, wherein the memory stores a computer program, and the processor implements the method described above when executing the computer program.

[0011] In order to solve the above technical problems, the embodiment of the present disclosure further provides a computer-readable storage medium, which adopts the following technical solution, including: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method described above is implemented.

[0012] According to the technical solution disclosed in the present invention, compared with the existing technology, the present invention provides a templated mechanism for quickly developing a large number of ETL jobs through simple table name instantiation. At the same time, it can be combined with artificial intelligence technology to perform matching recommendations and generation of corresponding template parameters through the big model of the asset management industry, making template configuration more convenient and accurate, further greatly improving the efficiency of ETL job development and improving user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] Figure 1 is an exemplary system architecture diagram in which the present disclosure may be applied; Figure 2 is a flow chart of one embodiment of a method according to the present disclosure; Figure 3 is a schematic diagram of one embodiment of a system according to the present disclosure; Figure 4 It is a structural diagram of an embodiment of a computer device according to the present disclosure.

[0014] The above and other features, advantages and aspects of the embodiments of the present disclosure will become more apparent with reference to the following detailed description in conjunction with the accompanying drawings. Throughout the accompanying drawings, the same or similar reference numerals represent the same or similar elements. It should be understood that the drawings are schematic and that components and elements are not necessarily drawn to scale. DETAILED DESCRIPTION

[0015] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art to which the present disclosure belongs; the terms used in the specification of the application herein are only for the purpose of describing specific embodiments and are not intended to limit the present disclosure; the terms "including" and "having" and any variations thereof in the specification and claims of the present disclosure and the above-mentioned drawings are intended to cover non-exclusive inclusions. The terms "first", "second", etc. in the specification and claims of the present disclosure or the above-mentioned drawings are used to distinguish different objects, not to describe a specific order.

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

[0017] In order to enable those skilled in the art to better understand the solution of the present disclosure, the technical solution in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the accompanying drawings.

[0018] System Structure First, the structure of a system according to an embodiment of the present disclosure is described. Figure 1 As shown, the system structure 100 may include terminal devices 101, 102, 103, 104, a network 105 and a server 106. The network 105 is used to provide a medium for communication links between the terminal devices 101, 102, 103, 104 and the server 106.

[0019] In this embodiment, the templated heterogeneous database ETL method runs on the electronic device (eg Figure 1 The terminal devices 101, 102, 103 or 104 shown in the figure can transmit various information through the network 105. The network 105 can include various connection types, such as wired, wireless communication links or optical fiber cables, etc. It should be noted that the above-mentioned wireless connection methods can include but are not limited to 3G / 4G / 5G connections, Wi-Fi connections, Bluetooth connections, WiMAX connections, Zigbee connections, UWB connections, local area networks ("LAN"), wide area networks ("WAN"), Internet (e.g., the Internet) and peer-to-peer networks (e.g., adhoc peer-to-peer networks) and other network connection methods currently known or developed in the future. The network 105 can communicate using any currently known or future developed network protocol such as HTTP (Hyper Text Transfer Protocol), and can be interconnected with digital data communications (e.g., communication networks) of any form or medium.

[0020] For example, a user can use terminal devices 101, 102, 103, 104 to interact with server 106 through network 105 to receive or send messages, etc. Various client applications can be installed on terminal devices 101, 102, 103 or 104, such as video live broadcast and playback applications, web browser applications, shopping applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0021] The terminal devices 101, 102, 103 or 104 can be, for example, various electronic devices with a touch display and / or support for web browsing, including but not limited to smart phones, tablet computers, e-book readers, MP3 (Moving Picture Experts Compressed Standard Audio Layer 3) players, MP4 (Moving Picture Experts Compressed Standard Audio Layer 4) players, head-mounted display devices, laptop computers, digital broadcast receivers, PDAs (personal digital assistants), PMPs (portable multimedia players), vehicle-mounted terminals (such as vehicle-mounted navigation terminals), etc., as well as mobile terminals such as digital TVs, desktop computers, etc.

[0022] The server 106 may be, for example, a server that provides various services, such as a background server that provides support for pages displayed on the terminal device 101 , 102 , 103 or 104 or for transmitted data.

[0023] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0024] Here, the terminal device can, for example, independently or in cooperation with other electronic terminal devices, run various operating systems such as applications in the Android system to implement the embodiment methods of the present disclosure, and can also run applications in other operating systems such as iOS system, Windows system, Hongmeng system, etc. to implement the embodiment methods of the present disclosure.

[0025] Templated Heterogeneous Database ETL Method The templated heterogeneous database ETL method disclosed in the present invention mainly deals with the scenario of a large number of ETLs between various heterogeneous databases in the field of asset management data processing. In most cases, the scenario needs to be processed according to the standard ETL process, and at the same time, it is necessary to take into account the personalized ETL processing for special systems and special databases. It is characterized in that by constructing templates, corresponding templates are used to develop ETL jobs for different scenarios, which can improve the overall development and operation efficiency, and at the same time improve the job quality and data quality in the entire heterogeneous database ETL process. The template is, for example, a set of configuration content of multiple parameters generated by extraction, and the number and order of processes and the parameters of each process component can be controlled by template parameters. The present disclosure can also be combined with artificial intelligence technology to match and recommend and automatically generate corresponding template parameters through the large model of the asset management industry, further improving the accuracy and efficiency of development operations.

[0026] refer to Figure 2 , shows a flow chart of an embodiment of a templated heterogeneous database ETL method according to the present disclosure. The templated heterogeneous database ETL method comprises the following steps: S21, configure ETL template parameters according to the entity information of heterogeneous databases, organize and present the parameters used by various functions in the ETL process in a templated manner, support editing and generate parameters of single or multiple ETL jobs by importing. The template parameters include at least one of the acquisition parameters of the source data source of the ETL, the preprocessing parameters, and the data loading parameters of the target data source.

[0027] In one or more embodiments, the collection parameters of the source data source of the ETL may include at least one of the type parameters of the source data source and the connection parameters, such as the address or authentication information used for the connection, the ETL collection table list, and the collection condition parameters. Generally, the type parameter of the source data source is a string of characters, which indicates the database type corresponding to the data source of the ETL, such as mysql corresponding to the MySql database, and dm corresponding to a certain database; the connection parameters include the URL (Uniform Resource Locator) for connecting to the database, the user name and password, etc., which are all filled in as strings. The ETL collection table list is a range list for ETL between heterogeneous databases, which determines the range of tables that need to be synchronized for ETL data. The collection condition is the collection condition of the corresponding table, which is filled in with the filter condition in sql. The incremental fields and incremental conditions selected in the filter condition can be automatically generated and filled in through the corresponding recommendation of the data portrait.

[0028] In one or more embodiments, the preprocessing parameters include at least one of the mapping relationship parameters of the field and the percentage parameter of the data allowed to be rejected. Generally, the mapping relationship parameters of the field are filled in according to the corresponding data field storage index document, and a constant string can be written, and the corresponding conversion rules are supported, including type conversion, value processing, etc. The standard conversion rules of the code field in the type conversion in the mapping relationship parameters can be automatically generated and filled in through intelligent recommendation of the data standard. The percentage parameter of the data allowed to be rejected is, for example, an integer value from 0 to 100, representing the percentage of data allowed to be rejected. The data is checked and converted in the preprocessing, and the data that cannot be processed is output to the rejection file by rejection. In the case of a large amount of source data and a supplementary recording operation, a certain proportion of data is allowed to be rejected, rather than directly reporting an error, so that the entire ETL stops to process and troubleshoot the problem data. The default value is 0, that is, if any data has a problem in the preprocessing, the job error is stopped after the ETL preprocessing process is completed. After setting the corresponding value, the ratio of the rejected data to the number of items to be processed this time exceeds the set parameter value before an error is reported.

[0029] In one or more embodiments, the loading parameters of the target data source include at least one of the type parameter and connection parameter of the target data source, such as the address or authentication information used for the connection of the target data source, the ETL loading table list, and the parameter of whether to automatically create a table. Generally, the type parameter of the target data source is a string of characters, which indicates the type of database corresponding to the data source of the ETL, such as mysql corresponding to the MySql database, and dm corresponding to a certain database; the connection parameters include the URL (Uniform Resource Locator) for connecting to the database, the user name and the password, all of which are filled in as a string. The ETL loading table list is a list of the scope of ETL between heterogeneous databases, which determines the range of tables that need to be synchronized for ETL data. The parameter of whether to automatically create a table is filled in with a string of "yes" or "no". When it is filled in as "yes", if the table does not exist in the target database, the corresponding table will be automatically created according to its data structure.

[0030] In one or more embodiments, for example, when applied to the asset management industry, a self-developed parameter management system, such as a configuration file library, a database table, or a specialized parameter management tool, can be used to conveniently define, modify, and save different parameter combinations, while supporting parameter version control and permission management to ensure the accuracy and security of the parameters. For the asset management industry, key parameters are determined, such as data accuracy requirements for different asset categories (decimal digits of stock prices, accuracy of bond yields), data update frequency (daily, weekly, monthly), data validation rules (reasonable range of asset value, upper limit of transaction amount), etc.; parameters are classified and grouped according to asset type (stocks, bonds, funds, real estate, etc.), business process (portfolio management, risk management, performance evaluation), data source (exchange, third-party data provider), etc., for easy management and search; a centralized parameter database is established, using relational database or NoSQL database to store parameter information, setting a unique identifier for each parameter, and establishing indexes to improve retrieval efficiency. At the same time, version control of parameters is provided to record the modification history of parameters so that they can be rolled back to a specific version when necessary; access and modification permissions for parameters are set for different users or user groups. For example, only senior data administrators can modify core parameters, while ordinary data processing personnel can only read and use them. Strict identity authentication and authorization mechanisms are implemented to ensure the security and accuracy of parameters; a parameter monitoring mechanism is established to check in real time or regularly whether the parameter values ​​are within a reasonable range. If the parameters have abnormal values ​​or deviate from the preset range, early warning notifications are issued in a timely manner so that timely measures can be taken.

[0031] In one or more embodiments, before step S21, for example, the customization and extension of the template is also included, for example, for common data processing requirements of the asset management industry, basic ETL templates are designed, such as asset data extraction templates (obtaining stock, bond and other data from different data sources), data cleaning templates (processing missing values, outliers), data aggregation templates (calculating the value of the investment portfolio), etc.; according to different asset management businesses, such as public fund management, private fund management, insurance asset management, etc., specific templates are customized, for example, public funds may require stricter data disclosure templates, while private funds may pay more attention to performance evaluation templates under specific strategies; considering the regulations and compliance requirements of the asset management industry, relevant verification and processing logic are incorporated into the template, for example, transaction data is screened according to anti-money laundering regulations, or reports in a specific format are generated according to regulatory requirements; extension interfaces are reserved in the template to add new functions or processing logic when needed, for example, extensions can be achieved through plug-in mechanisms or function calls; version management is performed on the customized and extended templates, and the modification history and reasons for the changes of the templates are recorded. In new projects or businesses, the appropriate template version can be selected as needed.

[0032] S22, creating at least one ETL job according to the configured template parameters, which is used to subsequently generate data collection, preprocessing and data loading operations in the ETL according to the parameters.

[0033] Among them, the ETL job, for example, includes a table specified in the ETL collection table list in the template, and multiple ETL processing operations created by the data collection, preprocessing and data loading parameters of the corresponding type source data source, wherein data collection and data loading are required items in the ETL processing operation, and the preprocessing operation determines whether to perform preprocessing according to whether the table in the parameter has the corresponding preprocessing related parameter configuration. For a table in the source data source specified by an ETL job, the ETL operation first collects the data to be processed from the source data source according to the data collection parameters, and then preprocesses the collected data according to the preprocessing parameters, and finally loads the collected data to be processed or the preprocessed data into the corresponding table of the target data source according to the data loading parameters. Here, the ETL collection table list in a template can be one table or multiple tables, and the corresponding number of ETL jobs are generated according to the corresponding number.

[0034] In one or more embodiments, the ETL job generated by the present disclosure may be composed of a general ETL program and its operating parameters. The ETL program is, for example, an executable script program, and its parameters are stored in the form of a configuration file or a parameter library. When the ETL program is running, it obtains the necessary parameters for operation by reading the configuration file or reading the parameter library through jdbc. When generating the ETL job, the parameters need to be generated as a configuration file, or loaded into the parameter library for storage, and the execution command of the ETL program is generated according to the type and parameters to complete the creation of the ETL job.

[0035] In one or more embodiments, in the data processing application scenario of the asset management industry, the present disclosure provides an innovative mechanism for quickly generating a large number of ETL jobs through simple table name instantiation to improve development efficiency: for common data tables in asset management, such as asset holding tables, transaction record tables, valuation tables, etc., a unified naming specification is formulated, and a mapping relationship between table names and data sources, data structures, and business logic is established. For example, the "stock_holdings" table represents stock holding data, and the "bond_transactions" table represents bond transaction data; a general ETL template is designed for common data processing operations in the asset management industry, such as data cleaning, format conversion, data aggregation, etc., and parameter positions related to table names are reserved in the template so that instantiation can be performed according to the actual table name; a table name parsing tool is developed, which can automatically identify the type of table (asset category, business type, etc.) and related features when the table name is entered, and obtain the corresponding data source, target library, data structure and other information from the mapping rules according to the parsing results; based on the information obtained from the parsing and the pre-designed template The board can batch generate configuration files for ETL jobs. For example, for a series of asset holding tables with different dates (such as "stock_holdings_202301" and "stock_holdings_202302"), the corresponding ETL job configuration can be quickly generated to realize data extraction, conversion and loading; according to factors such as the amount of data and data complexity in the table, the parameters of the ETL job, such as parallelism and buffer size, are automatically adjusted to improve processing efficiency. For important core data tables (such as real-time transaction data), more computing resources are preferentially allocated; for example, a visual interface can also be provided so that users can intuitively enter the table name and view the generated ETL job process and configuration, monitor the execution status and progress of the ETL job in real time, and promptly discover and handle abnormal situations; the mechanism of instantiating the table name to generate the ETL job can also be combined with the data governance framework of the asset management industry to ensure the accuracy, integrity and security of the data, so as to comply with the industry's data standards and compliance requirements and review and verify the generated ETL jobs.

[0036] S23, executing an ETL execution command corresponding to at least one ETL job, and reading parameter information in a configuration file or a parameter library through a template parameter in the execution command to execute at least one ETL job. The ETL execution command is used to perform at least one of data collection, preprocessing, and data loading.

[0037] At least one ETL job includes at least one table specified in the ETL collection table list, and the at least one table corresponds to at least one of the ETL operations created by data collection, preprocessing, and data loading parameters of the source data source.

[0038] In one or more embodiments, the ETL operation includes at least: The data to be processed is collected from the source data source according to the collection parameters. Artificial intelligence research can be used to generate source data portrait attributes, including but not limited to data volume, incremental identification, field type, length, whether there is garbled code, etc., and the collection plug-in is automatically selected for collection and preliminary cleaning work is performed.

[0039] The collected data is preprocessed according to the preprocessing parameters, and the collected data is checked and cleaned according to the set data standards and rules, including type conversion, code conversion, etc. The data standard content of the asset management industry big model can be used to perform corresponding standard type conversion and standard code mapping matching recommendations and automatic generation, and finally the collected data is converted into data that meets the data standards and ETL data requirements set by the industry or enterprise.

[0040] In one or more embodiments, for example, machine learning algorithms and data analysis are used to provide parameter recommendation data for new ETL jobs based on historical ETL job configurations and executions, including: collecting historical ETL job data in the asset management industry, including data sources, data scales, conversion requirements, and corresponding parameter settings and final processing effects of different asset types (such as stocks, bonds, funds, etc.), cleaning and preprocessing these data, removing abnormal transaction data, missing values, etc., and standardizing the data format for subsequent analysis; for asset management data, extracting features related to asset categories, transaction frequencies, risk levels, market conditions, etc. For example, for stock data, features may include sector classification, market value size, and price-earnings ratio; for bonds, features may include credit ratings, expiration dates, etc.; considering using decision tree-based integrated models (such as random forests) or deep learning models (such as multi-layer perceptrons); using features in historical data as input, and taking the optimal ETL parameter combination and processing effect (such as data accuracy and processing speed) as goals, to perform model training; in new asset management When the data processing job is executed, real-time information including market dynamics, new asset transaction data, and regulatory changes is collected, and these new situations are regularly integrated into the training data to retrain or fine-tune the model to adapt to industry changes. When faced with new asset management data processing tasks (such as quarterly asset inventory), the characteristics of the current task are extracted, such as asset size, types of assets involved, and urgency of processing. The model predicts suitable ETL parameters based on these characteristics, such as the frequency of data extraction, accuracy requirements for conversion, and priority of loading, and recommends them to data processing personnel. Explanations are provided for the recommended parameters, such as explaining that due to the large current asset size and tight processing time, a high-concurrency data extraction method is recommended. Data processing personnel are allowed to provide feedback on the recommended results, such as whether they are adopted and the actual processing effect, so that the model can continue to learn and improve. The reserved test data set is used to evaluate the accuracy and effectiveness of the model's recommendations under different asset types and business scenarios. According to the evaluation results, the model structure, feature selection, or optimization parameters are adjusted to improve the applicability and practicality of intelligent recommendations in the asset management industry.

[0041] According to the data loading parameters, the collected data to be processed or the pre-processed data is loaded into the corresponding table of the target data source. First, the structure of the specified table in the target database is adjusted accordingly based on the actual data situation, and the optimal plug-in is selected according to the data volume. The data is actually loaded through the plug-in to complete the entire ETL process.

[0042] In one or more embodiments, the ETL operation of data collection specifically includes: First, for a specific database, select the preset optimal plug-in for data extraction and collection based on the data volume. For example, through parameters and use the general jdbc to connect to the corresponding database. After the connection is successful, obtain the information of the corresponding table to be collected, including the data structure of the table and the amount of data collected this time; select the corresponding plug-in to connect to the database based on the amount of data and data type of the table to be collected; generate the corresponding data collection query statement based on the parameters, plug-in information and data structure.

[0043] Secondly, data is automatically collected in parallel in multiple batches based on the data portrait information. For example, one or more sub-processes are generated for data collection based on the concurrency parameters and the data portrait information of the table. Different processes generate corresponding query conditions for uniform data segmentation based on the information in the collected data portrait.

[0044] Finally, according to the parameters, the sub-process will collect data in parallel or serially, and write the collected data into a file or pipeline file. The main process waits for the sub-process to complete, collects and summarizes the corresponding data collection information, including data volume, rejected data, etc., and generates corresponding structure files and control files as needed according to the parameters.

[0045] Here, for example, the data collection operation is controlled by collection parameters to collect the data to be processed from the source data source, such as controlling the collection of the entire table, the collection of specified fields, and the collection of custom queries through parameters; controlling the collection conditions including the entire table, incremental date, and custom incremental conditions through parameters; controlling the collection process through parameters to remove spaces, line breaks, and positioning characters from the data for fields of specified types; and controlling whether to perform quantitative statistics on the collected data through parameters.

[0046] In one or more embodiments, the pre-processing ETL operation specifically includes: First, the file generated by data acquisition is read according to the set parameters, and the integrity of the data file is checked according to its control file information, including file size, MD5 code, etc.

[0047] Secondly, based on the set parameters, the contents of the files or pipeline files generated by data collection are read, and technical verification of the data is performed according to the data structure or metadata and the configured verification parameters, such as data format, data boundary, data type, etc. The data scalability verification is performed based on the configured verification parameter information, such as checking whether the data primary key is null based on the acquired data structure or metadata, such as ID card validity check, etc.

[0048] Finally, based on the configured verification parameters, the data after the scalability verification is converted into a standardized encoding, and the converted data is checked for garbled characters. For example, the parameters are used to determine whether the data with problems in the verification should be output, and the location of the output indication file and data file. For data with problems during the verification process, alarms and warnings are issued according to the importance of the problems, and the data with problems is output to the problem indication file and problem data file according to the parameters.

[0049] In one or more embodiments, the method for data standardization encoding conversion in data preprocessing includes, for example: reading the content of a file or pipeline file after data verification according to set parameters, converting the data type, length and data format of the source to the target data based on the standard data type established in the data standard and the mapping parameters from different database types to the standard data type; converting the code data of the source to the target data based on the standard code established in the data standard and the mapping parameters from the code of each system or table to the standard code.

[0050] In one or more embodiments, the ETL operation of data loading specifically includes: For a specific database, select the preset optimal plug-in to load data based on the amount of data that needs to be loaded.

[0051] The loading type is selected based on the data loading parameters, such as pre-processing before loading and post-processing after loading. Pre-processing and post-processing based on parameters can support deletion, clearing and custom operations on data.

[0052] Load data into the corresponding table of the target data source according to the data loading parameters. For example, you can also set whether to automatically create a table based on the parameters, create a specified table based on metadata, etc. Read the content of the pre-processed file or pipeline file according to the set parameters, use the plug-in determined in the previous order to load and write data into the table of the target database, and count the corresponding data volume after completion.

[0053] It should be understood that, although the steps in the flowchart of the accompanying drawings are displayed in sequence as indicated by the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least a part of the steps in the flowchart of the accompanying drawings may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be executed in turn or alternately with other steps or at least a part of the sub-steps or stages of other steps.

[0054] Templated heterogeneous database ETL system In order to implement the technical solution in the embodiment of the present disclosure, an embodiment of the present disclosure provides a templated heterogeneous database ETL system. The templated heterogeneous database ETL system is used to implement the above-mentioned templated heterogeneous database ETL method. The system can be specifically applied to various electronic terminal devices, such as Figure 3 As shown, it includes: a template parameter configuration component 301, an ETL job creation component 302, and an ETL job execution component 303.

[0055] The template parameter configuration component 301 is used to configure ETL template parameters according to the entity information of the heterogeneous database. The specific implementation process of the above method can refer to the above templated heterogeneous database ETL method, which will not be repeated here.

[0056] The ETL job creation component 302 is used to create at least one ETL job according to the configured template parameters. The specific implementation process of the above method can refer to the above templated heterogeneous database ETL method, which will not be repeated here.

[0057] The ETL job execution component 303 is used to execute the ETL execution command corresponding to at least one ETL job, and read the parameter information in the configuration file or parameter library through the template parameter in the execution command to execute at least one ETL job. The specific implementation process of the above method can refer to the above templated heterogeneous database ETL method, which will not be repeated here.

[0058] In one or more embodiments, the ETL job execution component includes, for example, at least one of a data acquisition module, a data preprocessing module, and a data loading module, and may also include, for example, a batch generation parameter module.

[0059] When the templated heterogeneous database ETL system disclosed in the present invention executes the above method, for example, the parameters used by each function in the ETL process are first organized and presented in a templated manner through the template parameter configuration component 301, and the parameters of each step on the template are configured, and then the template file is batch created by importing the ETL job creation component 302 to generate parameters for a single or multiple ETL jobs; the data acquisition module in the ETL job execution component 303, for example, connects to the corresponding data source based on the parameters, organizes the corresponding query statements, and submits them to the data source for query and metadata query comparison operations, and collects data from the data source; the data preprocessing module processes the collected data based on the parameters, and through verification, cleaning, type conversion, etc., the collected original data meets the required standards and requirements; the data loading module loads the preprocessed data to the target data source based on the parameters, completes a series of operations such as insertion, replacement, and update, and compares the corresponding data volume. The parameter information in the entire ETL process includes at least the following parts: metadata information is a dynamic parameter, which is obtained from the data source in real time for comparison with the corresponding parameters; the mapping relationship parameter information between different data sources and data standards is the basic parameter for general data format conversion, which needs to be pre-made in advance according to the data standard; other functions and steps in the ETL process are controlled by configured parameters; the template-based heterogeneous data ETL method and system can support the rapid migration and conversion of data between heterogeneous data sources, and realize the full adaptation of data source data structure changes without manual intervention. At the same time, template parameters can be parsed and matched from the ETL tool through the tool, which can quickly realize the migration of ETL tools.

[0060] It should be understood that, although each box in the block diagram of the accompanying drawings may represent a module, a part of which contains one or more executable instructions for implementing a specified logical function, these modules are not necessarily executed in sequence. The modules and functional units in the device embodiments disclosed herein may be integrated into a processing module, or each unit may exist physically separately, or two or more modules or functional units may be integrated into one module. The above-mentioned integrated modules may be implemented in the form of hardware or in the form of software functional modules. If the integrated modules are implemented in the form of software functional modules and sold or used as independent products, they may also be stored in a computer-readable storage medium. The above-mentioned storage medium may be a read-only memory, a disk or an optical disk, etc.

[0061] Templated heterogeneous database ETL equipment Reference below Figure 4 , which shows an electronic device (eg, Figure 1The terminal device in the embodiment of the present disclosure may be various terminal devices in the above system. Figure 4 The electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.

[0062] like Figure 4 As shown, the electronic device 400 may include a processing device (such as a central processing unit, a graphics processing unit, etc.) 401 for controlling the overall operation of the electronic device. The processing device may include one or more processors to execute instructions to complete all or part of the steps of the above method. In addition, the processing device 401 may also include one or more modules for processing and interacting with other devices.

[0063] The storage device 402 is used to store various types of data. The storage device 402 may include various types of computer-readable storage media or combinations thereof, such as electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or components, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present disclosure, a computer-readable storage medium may be any tangible medium that contains or stores a program that may be used by or in conjunction with an instruction execution system, device, or component.

[0064] The sensor device 403 is used to sense the specified measured information and convert it into a usable output signal according to a certain rule, and may include one or more sensors. For example, it may include an acceleration sensor, a gyroscope sensor, a magnetic sensor, a pressure sensor or a temperature sensor, etc., for detecting changes in the opening / closing state, relative positioning, acceleration / deceleration, temperature, humidity and light of the electronic device.

[0065] The processing device 401 , the storage device 402 , and the sensor device 403 are connected to each other via a bus 404 . An input / output (I / O) interface 405 is also connected to the bus 404 .

[0066] The multimedia device 406 may include input devices such as a touch screen, a touchpad, a keyboard, a mouse, a camera, a microphone, etc. for receiving input signals from a user. The various input devices may cooperate with the various sensors of the above-mentioned sensor device 403 to complete, for example, gesture operation input, image recognition input, distance detection input, etc. The multimedia device 406 may also include output devices such as a liquid crystal display (LCD), a speaker, a vibrator, etc.

[0067] The power supply device 407 is used to provide power to various devices in the electronic device, and may include a power management system, one or more power supplies, and components for distributing power to other devices.

[0068] The communication device 408 may allow the electronic device 400 to communicate with other devices wirelessly or by wire to exchange data.

[0069] The above-mentioned devices can also be connected to the I / O interface 405 to implement the application of the electronic device 400.

[0070] Although Figure 4 An electronic device having various devices is shown, but it should be understood that it is not required to implement or possess all the devices shown. More or fewer devices may be implemented or possessed instead.

[0071] In particular, according to an embodiment of the present disclosure, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present disclosure includes a computer program product, which includes a computer program carried on a non-transitory computer-readable medium, and the computer program contains program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication device, or installed from a storage device. When the computer program is executed by a processing device, the above-mentioned functions defined in the method of the embodiment of the present disclosure are executed.

[0072] In the context of the present disclosure, a machine-readable medium may be a tangible medium that can contain or store a program for use by or in connection with an instruction execution system, apparatus, or device.

[0073] It should be noted that the above-mentioned computer-readable medium of the present disclosure may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. In the present disclosure, the computer-readable signal medium may include a data signal propagated in the baseband or as part of a carrier wave, which carries a computer-readable program code. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. The computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate or transmit a program for use by or in combination with an instruction execution system, device or device. The program code contained on the computer-readable medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (radio frequency), etc., or any suitable combination of the above.

[0074] The computer-readable medium may be included in the electronic device, or may exist independently without being incorporated into the electronic device.

[0075] Computer program code for performing the operations of the present disclosure may be written in one or more programming languages ​​or a combination thereof, including, but not limited to, object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving a remote computer, the remote computer may be connected to the user's computer through any type of network, or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

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

[0077] The units involved in the embodiments described in the present disclosure may be implemented by software or hardware, wherein the name of a unit does not, in some cases, limit the unit itself.

[0078] The functions described above herein may be performed at least in part by one or more hardware logic components. For example, without limitation, exemplary types of hardware logic components that may be used include: field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), complex programmable logic devices (CPLDs), and the like.

[0079] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL method is provided, which adopts the following technical solution, including: Configure ETL template parameters based on heterogeneous database entity information; Create at least one ETL job according to the configured template parameters; Execute an ETL execution command corresponding to the at least one ETL job, and read parameter information in a configuration file or a parameter library through the template parameter in the execution command to execute the at least one ETL job; The template parameters include at least one of the acquisition parameters of the source data source of the ETL, the preprocessing parameters, and the data loading parameters of the target data source; The ETL execution command is used to perform an ETL operation including at least one of data collection, preprocessing, and data loading.

[0080] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL method is provided, which adopts the following technical solution, including: The acquisition parameters of the source data source of the ETL include at least one of the type parameter and connection parameter of the source data source, the ETL acquisition table list, and the acquisition condition parameter; The preprocessing parameters include at least one of a field mapping parameter and a percentage parameter of allowed rejected data; The target data source loading parameters include at least one of the target data source type parameter and connection parameter, the ETL loading table list, and the automatic table creation flag parameter.

[0081] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL method is provided, which adopts the following technical solution, including: The at least one ETL job includes at least one table specified in the ETL collection table list, and the at least one table corresponds to at least one of the ETL operations of data collection, preprocessing, and data loading parameter creation of the source data source; The ETL operation at least includes: Collecting the data to be processed from the source data source according to the collection parameters; Preprocessing the collected data according to the preprocessing parameters; The collected data to be processed or the pre-processed data are loaded into the corresponding table of the target data source according to the data loading parameters.

[0082] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL method is provided, which adopts the following technical solution, including: The ETL operations of data collection include: For a specific database, select a preset plug-in to collect data based on the amount of data; Automatically collect multiple batches of data in parallel according to the data portrait information; The data collection operation is controlled by the collection parameters to collect the data to be processed from the source data source.

[0083] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL method is provided, which adopts the following technical solution, including: The pre-processing ETL operations include: Perform technical verification of data based on the acquired data structure or metadata and configured verification parameters; Performing data scalability check based on the configured check parameter information; The data after the extensibility check is converted into code based on the configured check parameters.

[0084] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL method is provided, which adopts the following technical solution, including: The ETL operations for data loading include: For a specific database, select a preset plug-in to load data according to the amount of data that needs to be loaded; Selecting a loading type based on the data loading parameters; The data is loaded into a corresponding table of a target data source according to the data loading parameters.

[0085] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL system is provided, which is used to implement any of the above methods, and adopts the following technical solution, including: Template parameter configuration component, used to configure ETL template parameters based on heterogeneous database entity information; An ETL job creation component, used to create at least one ETL job according to the configured template parameters; The ETL job execution component is used to execute the ETL execution command corresponding to the at least one ETL job, and read the parameter information in the configuration file or parameter library through the template parameter in the execution command to execute the at least one ETL job.

[0086] According to one or more embodiments of the present disclosure, a templated heterogeneous database ETL system is provided, which adopts the following technical solution: The ETL job execution component includes at least one of a data acquisition module, a data preprocessing module, a data loading module and a batch generation parameter module.

[0087] According to one or more embodiments of the present disclosure, a computer device is provided, which adopts the technical solution described below, including a memory and a processor, wherein a computer program is stored in the memory, and when the processor executes the computer program, any of the methods described above is implemented.

[0088] According to one or more embodiments of the present disclosure, a computer-readable storage medium is provided, which adopts the technical solution described below, and a computer program is stored on the computer-readable storage medium, and when the computer program is executed by a processor, any of the methods described above is implemented.

[0089] The above description is only a preferred embodiment of the present disclosure and an explanation of the technical principles used. Those skilled in the art should understand that the scope of disclosure involved in the present disclosure is not limited to the technical solutions formed by a specific combination of the above technical features, but should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above disclosed concept. For example, the above features are replaced with the technical features with similar functions disclosed in the present disclosure (but not limited to) by each other to form a technical solution.

[0090] In addition, although each operation is described in a specific order, this should not be understood as requiring these operations to be performed in the specific order shown or in a sequential order. Under certain circumstances, multitasking and parallel processing may be advantageous. Similarly, although some specific implementation details are included in the above discussion, these should not be interpreted as limiting the scope of the present disclosure. Some features described in the context of a separate embodiment can also be implemented in a single embodiment in combination. On the contrary, the various features described in the context of a single embodiment can also be implemented in multiple embodiments individually or in any suitable sub-combination mode.

[0091] Although the subject matter has been described in language specific to structural features and / or methodological logical actions, it should be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or actions described above. On the contrary, the specific features and actions described above are merely example forms of implementing the claims.

Claims

1. A templated heterogeneous database ETL method, characterized in that: include: Configure ETL template parameters based on heterogeneous database entity information; Create at least one ETL job according to the configured template parameters; Execute an ETL execution command corresponding to the at least one ETL job, and read parameter information in a configuration file or a parameter library through the template parameter in the execution command to execute the at least one ETL job; The template parameters include at least one of the acquisition parameters of the source data source of the ETL, the preprocessing parameters, and the data loading parameters of the target data source; The ETL execution command is used to perform an ETL operation including at least one of data collection, preprocessing, and data loading.

2. The templated heterogeneous database ETL method according to claim 1, characterized in that: The acquisition parameters of the source data source of the ETL include at least one of the type parameter and connection parameter of the source data source, the ETL acquisition table list, and the acquisition condition parameter; The preprocessing parameters include at least one of a field mapping parameter and a percentage parameter of allowed rejected data; The target data source loading parameters include at least one of the target data source type parameter and connection parameter, the ETL loading table list, and the automatic table creation flag parameter.

3. The templated heterogeneous database ETL method according to claim 2, characterized in that: The at least one ETL job includes at least one table specified in the ETL collection table list, and the at least one table corresponds to at least one of the ETL operations of data collection, preprocessing, and data loading parameter creation of the source data source; The ETL operation at least includes: Collecting the data to be processed from the source data source according to the collection parameters; Preprocessing the collected data according to the preprocessing parameters; The collected data to be processed or the pre-processed data are loaded into the corresponding table of the target data source according to the data loading parameters.

4. The templated heterogeneous database ETL method according to claim 1, characterized in that: The ETL operations of data collection include: For a specific database, select a preset plug-in to collect data based on the amount of data; Automatically collect multiple batches of data in parallel according to the data portrait information; The data collection operation is controlled by the collection parameters to collect the data to be processed from the source data source.

5. The templated heterogeneous database ETL method according to claim 1, characterized in that: The pre-processing ETL operations include: Perform technical verification of data based on the acquired data structure or metadata and configured verification parameters; Performing data scalability check based on the configured check parameter information; The data after the extensibility check is converted into code based on the configured check parameters.

6. The templated heterogeneous database ETL method according to claim 1, characterized in that: The ETL operations for data loading include: For a specific database, select a preset plug-in to load data according to the amount of data that needs to be loaded; Selecting a loading type based on the data loading parameters; The data is loaded into a corresponding table of a target data source according to the data loading parameters.

7. A templated heterogeneous database ETL system, used to implement the method according to any one of claims 1 to 6, characterized in that: include: Template parameter configuration component, used to configure ETL template parameters based on heterogeneous database entity information; An ETL job creation component, used to create at least one ETL job according to the configured template parameters; The ETL job execution component is used to execute the ETL execution command corresponding to the at least one ETL job, and read the parameter information in the configuration file or parameter library through the template parameter in the execution command to execute the at least one ETL job.

8. The templated heterogeneous database ETL system according to claim 7, characterized in that: The ETL job execution component includes at least one of a data acquisition module, a data preprocessing module, and a data loading module.

9. A computer device, comprising a memory and a processor, wherein the memory stores a computer program, and the processor implements the method according to any one of claims 1 to 6 when executing the computer program.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 6 is implemented.

Images