WO2021006720A1

WO2021006720A1 - Method and system for updating database

Info

Publication number: WO2021006720A1
Application number: PCT/MY2020/050043
Authority: WO
Inventors: Ahmad Raif Mohamed Noor Beg; Adline DSILVA; Zaid AHMAD
Original assignee: Mimos Berhad
Priority date: 2019-07-05
Filing date: 2020-06-17
Publication date: 2021-01-14

Abstract

The present invention relates to a method and system for updating a database. The method (10) comprises the steps of creating a raw table in a distributed environment (11), copying one or more records along with corresponding timestamps from a source table to the raw table at a preset frequency (12), generating a final table from the raw table (13), and transferring the final table to a target location in the database (14). The system comprises an input module for receiving the records from the source table. A consolidating module creates the raw table and copies the received records to the raw table. A processing module generates the final table, and an output module transfers the final table to the target location.

Description

METHOD AND SYSTEM FOR UPDATING DATABASE

FIELD OF THE DISCLOSURE

The disclosures made herein relate generally to the field of database management and, more particularly, to a method and system for updating a database.

BACKGROUND

Big data is a massive volume of both structured and unstructured data, and therefore, traditional database and software techniques are not very effective in processing big data. As big data analytics has become a vital technology in understanding and predicting human behavior by studying large volumes of unstructured data, new databases and software were developed to enable storing and processing big data.

Hadoop, an open source software framework, allows distributed storage and processing of big data. Hadoop was designed as a highly scalable storage platform for processing very large data across a huge number of computing nodes operating in parallel. Hadoop Distributed File System (HDFS) is a distributed, scalable, and portable file system for operating in the Hadoop framework.

Sqoop is a process carried out in Hadoop environment to transfer bulk data between relational databases and HDFS. Sqoop uses primary key (metadata) column to divide source data across mappers thereof. It is necessary to incrementally sqoop data frequently to keep the HDFS updated. However, incremental sqoop produces duplication in the HDFS which causes serious problems for users using the updated data in HDFS. Duplication can be minimized by utilizing operation log of the source.

Chinese patent publication no: CN 107402981 A discloses a data increment processing method and system based on a distributed off-line database. This invention aims to solve the problem of data duplication in a big data platform using operation logs of a source database. Further, a temporary table is generated to remove duplicates while updating a target database. Even though this invention is effective in avoiding duplicates, it cannot be applicable for all systems, specifically for the systems that do not generate operation logs. Further, when a size of the operation logs increases, this invention becomes time consuming in checking and eliminating duplicates.

Hence, there is a need for a method and system for updating database, applicable for all kinds of system and avoids a need for processing operation logs of the source database to remove duplicates.

SUMMARY

The present invention proposes a method and system for updating a database. The method comprises the steps of: creating a raw table in a distributed environment, copying one or more records from a source table to the raw table at a preset frequency, generating a final table from the raw table, and transferring the final table to a target location in the database

In one implementation, each record in the raw table is numerically tagged, wherein similar records are tagged with same tag number. A latest record is selected for each tag number and is copied to the final table.

By this way, the present invention removes duplicates without a need for processing operation logs of the source database during updating, and thus it is applicable even for systems that does not generate operation logs or if a size of operation logs is too big.

In one aspect of the present invention, the distributed environment is a Hadoop environment and the database is a Hadoop Distributed File System (HDFS).

Various objects, features, aspects and advantages of the inventive subject matter will become more apparent from the following detailed description of preferred embodiments, along with the accompanying drawing figures in which like numerals represent like components.

BRIEF DESCRIPTION OF THE ACCOMPANYING DRAWINGS In the figures, similar components and/or features may have the same reference label. Further, various components of the same type may be distinguished by following the reference label with a second label that distinguishes among the similar components. If only the first reference label is used in the specification, the description is applicable to any one of the similar components having the same first reference label irrespective of the second reference label.

FIGURE 1 illustrates a flow diagram of the method for updating a database, in accordance with an exemplary embodiment of the present invention.

FIGURE 2 illustrates a block representation of the system for updating a database, in accordance with an exemplary embodiment of the present invention.

DETAILED DESCRIPTION

In accordance with the present invention, there is provided a method and a system for updating a database, which will now be described with reference to the embodiment shown in the accompanying drawings. The embodiment does not limit the scope and ambit of the disclosure. The description relates purely to the exemplary embodiment and its suggested applications.

The embodiment herein and the various features and advantageous details thereof are explained with reference to the non-limiting embodiment in the following description. Descriptions of well-known components and processing techniques are omitted so as to not unnecessarily obscure the embodiments herein. The examples used herein are intended merely to facilitate an understanding of ways in which the embodiment herein may be practiced and to further enable those of skill in the art to practice the embodiment herein. Accordingly, the description should not be construed as limiting the scope of the embodiment herein. The description hereinafter, of the specific embodiment will so fully reveal the general nature of the embodiments herein that others can, by applying current knowledge, readily modify or adapt or perform both for various applications such specific embodiment without departing from the generic concept, and, therefore, such adaptations and modifications should and are intended to be comprehended within the meaning and range of equivalents of the disclosed embodiments. It is to be understood that the phraseology or terminology employed herein is for the purpose of description and not of limitation.

As will be appreciated by one skilled in the art, the present invention may be embodied as a system, method or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment (including firmware or programmable instructions) or an embodiment combining software and hardware aspects that may all generally be referred to herein as an“unit,”“module,” or“system.”

Various terms as used herein are defined below. To the extent a term used in a claim is not defined below, it should be given the broadest definition persons in the pertinent art have given that term as reflected in printed publications and issued patents at the time of filing. Definitions:

Updating: Adding new data or modifying/deleting existing data in a database.

Database: A collection of structured or unstructured data electronically stored and accessed using a computer system.

Distributed environment - A network of distributed computing systems allowing resource sharing by systems connected to the network.

The present invention provides a method and system for updating a database. The present invention operates in a distributed environment and enables removing duplicates without a need for processing operation logs of a source database during updating. Referring to the accompanying drawings, FIGURE 1 illustrates a flow diagram of the method for updating a database, in accordance with an exemplary embodiment of the present invention. The method (10) comprises the steps of: creating a raw table in a distributed environment (1 1 ), wherein the distributed environment is a Fladoop environment. One or more records from a source table are copied along with corresponding timestamps to the raw table at a preset frequency (12). A final table is generated from the raw table (13) and then transferred to a target location in the database (14). In one embodiment, the database is a Fladoop Distributed File System (FIDFS). The source table includes one or more records, each with a number of fields, as shown below.

Table 1 : Source Table

It is to be understood that the above table is for illustration purpose only, and in actual, the source table may include thousands of records and fields. At least one of the fields serves as a primary key of the corresponding record.

The raw table is created with the same fields as in the source table, except for an additional field called“tag field”, as shown below. At a preset frequency, the record is copied from the source table to the raw table. The frequency is based on update cycles. Suppose the record is expected to be modified twice a day, setting the frequency for once a week will lose too many data. Similarly, if the record is expected to be modified once in a week, setting the frequency for once a day will consume too much processing time. Therefore, the frequency shall be directly proportional to the update cycles. The frequency may range from once in a day to once in a week or even once in a month according to the update cycles. For example, in Table 2, the record is copied to the raw table each time the record is updated. By this way, a backup of all updates is made available.

Table 2: Raw Table

After copying the record to the raw table, each record is numerically tagged. The primary key of the record is identified and a tag number is assigned to the primary key. Each record is tagged based on the corresponding primary key, wherein two or more records with same primary key are tagged with same tag number. For example, in table 2,“file no.” is identified as the primary key of the record, and“1” is assigned as the tag number for the primary key“1201 1255609”. All records with the primary key“1201 1255609” are tagged as“1”.

Further, the latest record among the similar records (records with same tag number) is selected and updated in the final table. For example, among the records with tag number“1”, the latest record which was last updated on 27 September 2016 at 14:32:03 is copied to the final table as below.

Table 3: Final Table

Each selected record is partitioned into two or more segments. One or more segments different from corresponding segments in the final table are identified in the selected record. The identified segment is updated in the final table by merging or overwriting it in the final table. FIGURE 2 illustrates a block representation of the system for updating a database, in accordance with an exemplary embodiment of the present invention. The system (20) comprises an input module (21 ), a consolidating module (22), a processing module (23) and an output module (24). The input module (21 ) receives one or more records from a source table (1 ). The consolidating module (22) creates a raw table (2) in a distributed environment and copies the received records to the raw table (2) along with corresponding timestamps. In an exemplary embodiment, the distributed environment is a Hadoop environment and the database (50) is a Hadoop Distributed File System (HDFS).

The processing module (23) generates a final table (3) from the raw table (2). The output module (24) transfers the final table (3) to a target location in the database (50). In one embodiment, the final table (3) is transferred to a big data analytics program. Alternatively, the final table may be transferred to a machine learning program for training a computing system.

The processing module (23) includes a tag module (not shown), a selection module (not shown) and a copying module (not shown). The tag module numerically tags each record in the raw table (2), wherein similar records are tagged with same tag number. The tag module is configured to identify a primary key of each record and to assign a tag number to each primary key. Further, the tag module tags each record based on the corresponding primary key, wherein two or more records with same primary key are tagged with the same tag number.

The selection module selects the latest record for each tag number, and the copying module copies the selected records to the final table. The processing module (23) partitions each selected record into two or more segments and identifies one or more segments different from the corresponding segments in the final table (3). Further, the processing module (23) updates the identified segment in the final table (3) by merging or overwriting the identified segments to the final table (3).

Since the database (50) is updated without processing any operation log from the source table (1 ), the present invention can be applicable to any system irrespective of size and availability of operation logs of the system, while reducing time consumption. Further, the present invention copies only the latest record among similar records, and thus duplicates are eliminated in the final table (3) in a simple manner.

The terminology used herein is for the purpose of describing particular example embodiments only and is not intended to be limiting. As used herein, the singular forms "a", "an" and "the" may be intended to include the plural forms as well, unless the context clearly indicates otherwise.

The terms "comprises," "comprising,"“including,” and“having,” are inclusive and therefore specify the presence of stated features, integers, steps, operations, elements, or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, or groups thereof. The method steps, processes, and operations described herein are not to be construed as necessarily requiring their performance in the particular order discussed or illustrated, unless specifically identified as an order of performance. It is also to be understood that additional or alternative steps may be employed.

The use of the expression“at least” or“at least one” suggests the use of one or more elements, as the use may be in one of the embodiments to achieve one or more of the desired objects or results.

Various methods described herein may be practiced by combining one or more machine-readable storage media containing code that perform the steps according to the present invention with appropriate standard computer hardware to execute the code contained therein. An apparatus for practicing various embodiments of the present invention may involve one or more computers (or one or more processors within a single computer) and storage systems containing or having network access to computer program(s) coded in accordance with various methods described herein, and the method steps of the disclosure could be accomplished by modules, routines, subroutines, or subparts of a computer program product.

While the foregoing describes various embodiments of the disclosure, other and further embodiments of the disclosure may be devised without departing from the basic scope thereof. The scope of the disclosure is determined by the claims that follow. The disclosure is not limited to the described embodiments, versions or examples, which are included to enable a person having ordinary skill in the art to make and use the disclosure when combined with information and knowledge available to the person having ordinary skill in the art.

Claims

CLAIMS:

1. A method (10) for updating a database, comprising the steps of:

i. creating at least one raw table in a distributed environment (11 );

ii. copying one or more records along corresponding timestamps from at least one source table to said raw table at a preset frequency (12); iii. generating at least one final table from said raw table (13); and

iv. transferring said final table to a target location in said database (14), characterized in that the step of generating said final table (13) includes:

- numerically tagging each record in said raw table, wherein similar records are tagged with same tag number;

- selecting a latest record for each tag number; and

- updating said selected records in said final table.

2. The method as claimed in claim 1 , wherein said distributed environment is a Hadoop environment.

3. The method as claimed in claim 1 , wherein said database is a Hadoop Distributed File System, HDFS.

4. The method as claimed in claim 1 , wherein said numerically tagging of each record in said raw table includes:

- identifying at least one primary key of each record;

- assigning a tag number to each primary key; and

- tagging each record based on the corresponding primary key, wherein two or more records with same primary key are tagged with same tag number.

5. The method as claimed in claim 1 , wherein the step of updating said selected records in said final table includes:

- partitioning each selected record into two or more segments;

- identifying at least one segment differing from corresponding segment in said final table; and - updating the identified segment in said final table, wherein said identified segment is merged or overwritten in said final table.

6. A system (20) for updating a database (50), comprising:

i. at least one input module (21 ) for receiving one or more records from at least one source table (1 );

ii. at least one consolidating module (22) creating at least one raw table (2) in a distributed environment and copying said received records along with corresponding timestamps to said raw table (2);

iii. at least one processing module (23) generating at least one final table (3) from said raw table (2); and

iv. at least one output module (24) for transferring said final table (3) to a target location in said database (50), characterized in that said processing module (23) includes:

- at least one tag module for numerically tagging each record in said raw table (2), wherein similar records are tagged with same tag number;

- at least one selection module for selecting a latest record for each tag number; and

- at least one copying module for copying said selected records to said final table (3).

7. The system as claimed in claim 6, wherein said tag module is configured to identify at least one primary key of each record, assign a tag number to each primary key, and tag each record based on the corresponding primary key, wherein two or more records with same primary key are tagged with same tag number.

8. The system as claimed in claim 6, wherein said processing module (23) is configured to partition each selected record into two or more segments, identify at least one segment differing from corresponding segments in said final table (3), and update the identified segment in said final table (3), wherein said identified segment is merged or overwritten to said final table (3).