KR100693720B1 - Controlling method of source system log table for data warehouse - Google Patents

Abstract

The present invention relates to a method of controlling raw system log table data for building a data warehouse. When the raw system uses a log table for archiving historical data, data is periodically extracted from the log table and transmitted to the data warehouse. And then the data loading to the raw table of the data warehouse as much as possible by correcting the time overlapping and omission of the data to be carried out at a fixed time without the need for a separate operator operation, thereby reducing the operator's time and human costs You can prevent duplicate and missing raw table data as much as possible.

You can keep files created on the raw system for a certain period of time without the operator's effort to prepare for data loss due to transmission errors, and the data caused by errors that do not exactly match the log table time range and log table update intervals. It is possible to increase the reliability of the raw table by correcting the missing and duplicated as much as possible.

Description

Controlling method of source system log table for data warehouse}

1 is an overall configuration according to the present invention.

FIG. 2 is a block diagram illustrating a data extraction and transmission operation in the raw system shown in FIG.

3 is a configuration diagram for explaining a data loading operation in the data warehouse shown in FIG.

The present invention relates to a method of controlling raw system log table data for building a data warehouse. More particularly, the present invention relates to a method of controlling raw system log table data. For the data warehouse deployment, the data warehouse is transferred to the data warehouse and back to the raw table of the data warehouse, so that the timely redundancy and omission of the data can be corrected and performed in a predetermined time without the need for a separate operator. A method of controlling raw system log table data.

In general, the contents of the raw system database are transformed into a form that can be transported or transferred to the data warehouse, collected, and loaded into the database of the data warehouse. When the operators of each system work directly and intervene each time, the time and human costs are excessive. The possibility of error due to operator error remains.

In addition, when collecting data from the log table of the raw system database, if the time range of the log table data does not exactly match the log table update period due to operator error or system abnormality (for example, a log with a data update cycle of 1 day) If the table should have data for August 23, 1999, if the data for August 23, 1999 01:00 data from August 24, 1999 03:00 data is insufficient, There was a problem that duplicates or omissions may occur.

Accordingly, the present invention has been made to solve the above-mentioned conventional problems, and when the raw system uses the log table for archiving historical data, the data is periodically extracted from the log table and transmitted to the data warehouse and Control of raw system log table data for data warehouse construction, which allows the loading of the data warehouse to the raw table of the data warehouse as much as possible by correcting the overlapping and omission of the data as much as possible without the need for a separate operator operation. The purpose is to provide a method.

The present invention for achieving the above object is a first step of extracting the log table of the raw system database to a file of a format that can be recognized in the data warehouse and transmitting to the data warehouse, and the file received from the data warehouse Storing data in a temporary table, comparing a data time range of a file stored in the temporary table with a data time range of a file stored in a raw table, and storing the data in the raw table according to the result; And comparing the time range of the data stored in the table with the data range of the file stored in the raw table one day ago and loading the data of the storage table into the raw table according to the result. do.

Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings.

The overall configuration of the present invention is composed of the raw system 10 and the data warehouse 20, as shown in Figure 1, specifically, the log table 12a of the raw system database 12 of the raw system 10 Data extraction and transmission part that extracts the file periodically and transfers it to the data warehouse 20 and the data that periodically loads the file received from the raw system 10 to the raw table 22a of the data collection system database It is composed of parts.

Here, the data extraction and transmission part periodically extracts a file from the log table 12a of the raw system database 12 to the data warehouse 20 while managing data for a certain period of time in preparation for an error in data transmission. As shown in FIG. 2, file name change, file extraction, and file transfer are performed in three steps.

Meanwhile, the data loading portion corrects the case where the time range of the file transmitted from the raw system 10 does not exactly match the log table update period, and periodically corrects the raw table 22a of the data warehouse database 22. The part that loads into the table consists of tables as shown in FIG. 3, which is executed in the data warehouse.

The temporary table is a table for loading data of a file which is a target of a data loading operation, and the storage table is a table for storing data of the current date at which the loading operation is performed among the data of the temporary table for the next cycle of loading operation. A table is a table that inserts data that fixes the temporal duplication and omission of data transmitted from the raw system using temporary tables and archive tables.

Meanwhile, the extraction, transmission and stacking operations of log table data through the raw system and data warehouse configured as described above will be described in detail.

First, as shown in FIG. 1, a file (assuming Exp.sh) implementing data extraction and transmission parts is set to be periodically executed at a predetermined time using a crontab command, which is a UNIX command in a raw system. The execution cycle is the same as the update interval of the log table.

Then, the file that implements the data loading part (assuming Load.sh) is set to be executed periodically at a certain time using the crontab command in the data warehouse. At this time, the execution cycle is the same as the log table update cycle (Exp.sh execution cycle). The time set in contab is after the completion of Exp.sh in the raw system.

At the time specified by the crontab command on the raw system, Exp.sh is run to extract the data from the log tables to a file and transfer it to the data warehouse.

At the time specified by the crontab command in the data warehouse, the contents of load.sh are performed to load the transferred files from the raw system into the raw tables in the database.

Herein, the operation of extracting and transmitting data will be described in more detail with reference to FIG. 2. For the detailed description, the following case is configured as an example.

The log table update and data extraction cycle is one day, the directory name for generating the extract file is EXP, and the file names and number for storing the past data are EXT1, EXT2, and EXT3 (three).

The file name extracted from the log table is named EXT1, and the file transfer date and the extracted file name transferred to the data warehouse are named August 24, 1999, EXT990824.

First, the names of the files in the destination directory (EXP) where the data extraction files are stored are changed as shown in the figure (P1).

The log table of the raw system database to be collected is extracted into a file that can be recognized by the data warehouse, and the file name is set to EXP1 (P2).

Send the EXP1 file to the data warehouse using the rcp command on UNIX, at which time the name of the file received from the data warehouse is displayed using the date command on UNIX using EXP`date + '% y% m% d' (eg, August 1999). If it is 24 days, it is set to EXP990824) (P3).

That is, the file is transferred to the data warehouse through the file name change and file extraction process.

In operation of the data loading portion through the transmitted file, as shown in FIG. 3, if there is a temporary table to be loaded data, the table is dropped (P4).

Create a temporary table for the "EXT current date" file (ex: EXT990824 if it is 24 August 1999). In this case, if there is no error in the time range in the log table created on August 24, 1999, the temporary table includes only data between 00:00:00 and 23:59:59 on August 23, 1999 (P5).

If there is data two days before the current day of the temporary table (eg, August 22, 1999), data having an hr_id larger than max (hr_id) of the current raw table is inserted into the raw table (P6).

Here, hr_id means a time column name of a temporary table.

On the other hand, if data exists in the current archive table, hr_id is smaller than min (hr_id) in the data of the temporary table one day before the current day of the temporary table (e.g., August 23, 1999 in the case of August 24, 1999). Insert (P7).

Thereafter, data one day before the current day of the temporary table (eg, August 23, 1999) is inserted into the raw table (P8).

Truncate the archive table (P9).

If the temporary table contains current day data (August 24, 1999 data), the temporary table is inserted into the archive table (P10).

 As described above, the present invention can reduce the time and human costs because the data extraction, transfer and loading of the data warehouse raw table in the raw system is performed without a separate operation of each system operator. In addition, the files created in the raw system can be kept for a certain period of time without the operator's effort to prepare for data loss due to transmission errors, and the data due to errors in which the log table time range and the log table update cycle do not match exactly. You can improve the reliability of the raw table by correcting the omission and duplication of the table as much as possible.

In addition, preferred embodiments of the present invention are disclosed for the purpose of illustration, those skilled in the art will be able to various modifications, changes, additions, etc. within the spirit and scope of the present invention, such modifications and modifications belong to the following claims You will have to look.

Claims (7)

Extracting a log table of a raw system database into a file in a format recognizable by the data warehouse and transferring the log table to the data warehouse; The data of the file received from the data warehouse is stored in a temporary table, and the data time range of the file stored in the temporary table is compared with the data time range of the file stored in the raw table. 2nd process loading to; And And comparing the time range of the data stored in the storage table with the data time range of the file stored in the raw table, and loading the data in the storage table into the raw table according to the result. To control raw system log table data for building a data warehouse. The method of claim 1, wherein the first process comprises: Rename the file in the target directory to save the data extraction file, and using the date command of the UNIX (UNIX) data transfer characterized in that to transfer the extraction file of the current date to the data warehouse How to control raw system log table data for house building. The method of claim 1 or 2, wherein the second process and the third process, The method of controlling raw system log table data for building a data warehouse, characterized in that the load is processed using the date command through a load program (load.sh) of the data warehouse. The method of claim 1, wherein the second process is A step 2-1 of dropping the temporary table if a temporary table to be loaded with data already exists and creating a temporary table for a file received from the raw system; And If there is data two days before the current day of the temporary table, the second step of inserting the data having a value of the time column is greater than the maximum value of the time column of the current raw table to the raw table of the second step; Raw system log table data control method for building a data warehouse, characterized in that the. The method of claim 1 or 4, wherein the third process comprises: Step 3-1 of inserting data into which the value of the time column is smaller than the minimum value of the time column of the data one day before the current day of the temporary table, if data exists in the archive table; Step 3-2 of inserting data one day prior to the temporary table into a raw table and truncating a storage table; And And a third process of inserting the temporary table into the storage table if there is current data in the temporary table. 3. According to claim 1 or claim 4, wherein the storage table of the third process, The control method of the raw system log table data for building a data warehouse, characterized in that for storing the data of the current date, the load operation is performed for the next cycle of the temporary table data. The method of claim 1 or 4, wherein the second and third raw table, And inserting data correcting temporal duplication and omission of data transmitted from the raw system by using the temporary table and the storage table.

Images