Universal ETL method suitable for various destination table update modes

Abstract

The invention discloses a universal ETL method suitable for various destination table update modes. The method not only overcome the shortcomings of high cost, low speed, high fault rate and high implementation workload of ETL tool software, but also solves the problems that an existing database script is non-universal and cannot realize various table update modes. According to the method, four database objects, i.e., an ETL task table used for recording various ETL tasks in a data warehouse, an ETL log table used for recording logs generated in operation of an ETL storage process, a storage process module used for executing an ETL task in an operation, and an index and remark information storage process sub-module used for copying an index and a remark of a destination table to a shadow table during the executing process of the storage process module, are set, a sequence of the objects is adjusted, and necessary programs of database connection, operation and the like are set, so that the ETL process of extracting source table data, converting the data and finally storing the data to the target data warehouse is finished.

Description

technical field [0001] The invention relates to an ETL method for updating and storing a database, in particular to a general ETL method applicable to updating modes of various purpose tables. Background technique [0002] ETL is the abbreviation of Extract-Transform-Load, which is the process of data extraction, transformation and loading, and can also be understood as the process of data extraction, transformation and loading. ETL, as the core and soul of data warehouse and business intelligence, can follow unified rules Integrating and improving the value of data is responsible for completing the process of transforming data from data sources to target data warehouses, and is an important step in the implementation of data warehouses. [0003] The current ETL methods are divided into two categories: ETL tool software and database scripts. [0004] The so-called ETL tool software refers to installing a set of ETL tool software in addition to the database software used in ...

AI Technical Summary

Problems solved by technology

[0015] In order to overcome the above-mentioned problems, the present inventor has carried out intensive research, and has designed a kind of general ETL method that is applicable to multiple purpose list update modes, and this method has both avoided the high cost of ETL tool software, slow speed, many faults, The shortcomings of the implementation of heavy workload, and to overcome the existing database script is not universal, can not achieve a variety of table update problems, the core of this method is to set the ETL process of four database objects located in the target data warehouse, four The database objects are: ETL task table, ETL log table, stored procedure module, index and remark information stored procedure submodule, among which, ETL task table is used to record each ETL task in the data warehouse; ETL log table is used to record ETL storage The log generated when the process is running, the stored process module is used to execute the ETL task contained in a certain job information module, the index and remark information stored process sub-module is used to copy the index and remark of the target table to the shadow table, set the above four Database objects, adjust their order, and then set the necessary programs such as database connection and operation, so as to complete the ETL process of extracting source table data for conversion and finally storing the data to the target data warehouse, thereby completing the present invention

Images