Universal ETL method suitable for various destination table update modes

An update method and purpose technology, applied in special data processing applications, instruments, electrical and digital data processing, etc., can solve the problems of heavy implementation workload, many failures, inability to implement multiple table update methods, etc., and achieve low operating costs. Effect

Active Publication Date: 2016-01-06
北京宸瑞科技股份有限公司
View PDF3 Cites 10 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

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 submo

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Universal ETL method suitable for various destination table update modes
  • Universal ETL method suitable for various destination table update modes
  • Universal ETL method suitable for various destination table update modes

Examples

Experimental program
Comparison scheme
Effect test

specific example

[0205] There are two database servers S and D, S is the remote database, and D is the target data warehouse. There is a personnel table in S, which contains 3 fields: ID number, name, gender code, where the ID number is The primary key, the gender code stores 1 and 2, 1 for male and 2 for female. The number of data entries in the personnel table is about 10 million, and new data is added every day, and some old data is deleted or modified. Now it is necessary to import the personnel table of S into D and update it automatically every day, and convert the gender code into meaningful male and female. To achieve this task, the following steps can be implemented:

[0206] Step 1: Establish a database connection to access the remote database S in the target data warehouse D. The database connection only needs to be established once and can be used repeatedly in the future.

[0207] Step 2: Create an empty personnel table in D, which contains 4 fields: ID number, name, gender cod...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

PUM

No PUM Login to view more

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 ...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

Application Information

Patent Timeline
no application Login to view more
IPC IPC(8): G06F17/30
Inventor 孙二林
Owner 北京宸瑞科技股份有限公司
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Try Eureka
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap