FAST BULK LOADING AND INCREMENTAL LOADING OF DATA INTO A DATABASE

• US 20090319550A1
• Filed: 06/23/2008
• Published: 12/24/2009
• Est. Priority Date: 06/23/2008
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Kickfire, Inc. (Teradata Corporation)
• Inventors: Chamdani, Joseph I., Meiyyappan, Krishnan, Surlaker, Kapil, Shau, James, Tran, Hung, Krishnamurthy, Ravi, Branscome, Jeremy
• Granted Patent: US 7,895,151 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

Embodiments of the present invention provide for batch and incremental loading of data into a database. In the present invention, the loader infrastructure utilizes machine code database instructions and hardware acceleration to parallelize the load operations with the I/O operations. A large, hardware accelerator memory is used as staging cache for the load process. The load process also comprises an index profiling phase that enables balanced partitioning of the created indexes to allow for pipelined load. The online incremental loading process may also be performed while serving queries.

Claims

1. A method for loading data into a relational database system, said method comprising:

receiving a request to load data from a load file into a database;

sampling the load file;

determining a first profile of the load file based on the samples;

determining at least one compression scheme for the data in the load file based on its profile;

compressing the data in the load file based on the at least one compression scheme as the data is loaded into the database;

writing, into a hardware accelerator memory, compressed data that is to be indexed;

determining a second profile of the compressed data that is to be indexed based on a machine code database instruction;

dividing the compressed data that is to be indexed into a set of balanced partitions based on the second profile;

determining a program of machine code database instructions based on the second profile;

distributing the compressed data that is to be indexed in the hardware accelerator memory into partitions in the hardware accelerator memory based on the program of machine code database instructions;

building, in parallel, a sub-index for each partition of the compressed data that is to be indexed, and

determining an index based on a combination of the sub-indexes.

2. The method of claim 1, wherein determining the profile of the compressed data that is to be indexed comprises determining a frequency count of values across ranges in the data.

3. The method of claim 2, wherein determining the profile of the compressed data that is to be indexed comprises determining a frequency count of values across ranges of rows in the data.

4. The method of claim 1, wherein determining the index based on a combination of the sub-indexes comprises merging the sub-indexes.

5. The method of claim 1, wherein dividing the compressed data that is to be indexed into the set of balanced partitions comprises dividing the compressed data that is to be indexed into partitions that are sized based on a size of a memory coupled to a host.

6. The method of claim 1, wherein dividing the compressed data that is to be indexed into partitions that are sized based on a size of the memory coupled to a host and a number processing cores of the host.

7. A method of loading data in existing tables of a database, said method comprising:

retrieving a portion of the data to be loaded;

identifying a compression scheme used to store data in the database;

determining whether the identified compression scheme is optimum based on the data to be loaded;

loading the data into the database based on the identified compression scheme when the compression is optimum;

determining a new compression scheme when the identified scheme is not optimum;

reorganizing a portion of the database based on the new compression scheme; and

loading the data into the database based on the new compression scheme when the identified scheme was not optimum.

8. The method of claim 7, wherein retrieving a portion of the data to be loaded comprises sampling a load file.

9. The method of claim 7, wherein retrieving a portion of the data to be loaded comprises retrieving at least a part of a tuple of the data to be loaded from a load file.

10. The method of claim 7, further comprising:

determining columns of data in the database that are indexed; and

updating, in parallel, indexes for the indexed columns of data using a hardware accelerator and based on a program of machine code database instructions.

11. A method of deleting data from existing tables of a database, said method comprising:

determining a portion of the data to be deleted from the database;

deleting the portion of the data from the database using a hardware accelerator and based on a program of machine code database instructions;

determining columns of data that are indexed and affected by the deletion of the portion of the data; and

updating, in parallel, indexes for the indexed columns of data using the hardware accelerator and based on another program of machine code database instructions

12. The method of claim 11, wherein determining columns of data that are indexed and affected by the deletion of the portion of the data comprises determining the columns of data that are indexed based on a program of machine code database instructions.

13. The method of claim 11, further comprising:

determining other data in the database that is related to the deleted data using the hardware accelerator and a program of machine code database instructions; and

deleting the related data using the hardware accelerator and a program of machine code database instruction.

14. The method of claim 13, wherein determining the other data in the database comprises determining the other data in the database that is related by a foreign key to the deleted data.

15. The method of claim 13, further comprising:

determining columns of the related data that are indexed and affected by the deletion of the related data;

updating, in parallel, indexes for the indexed columns of the related data using the hardware accelerator and based on a program of machine code database instructions.

16. A method of updating data in existing tables of a database, said method comprising:

determining a portion of the data to be updated in the database;

updating the portion of the data from the database using a hardware accelerator and based on a program of machine code database instructions;

determining columns of data that are indexed and affected by the update of the portion of the data; and

updating, in parallel, indexes for the indexed columns of data using the hardware accelerator and based on another program of machine code database instructions

17. The method of claim 16, further comprising:

determining other data in the database that is related to the updated data using the hardware accelerator and a program of machine code database instructions; and

updating the related data using the hardware accelerator and a program of machine code database instructions.

18. The method of claim 17, wherein determining the other data in the database comprises determining the other data in the database that is related by a foreign key to the deleted data.

19. The method of claim 17, further comprising:

determining columns of the related data that are indexed and affected by the deletion of the related data;

updating, in parallel, indexes for the indexed columns of the related data using the hardware accelerator and based on a program of machine code database instructions.

20. The method of claim 17, wherein determining columns of data that are indexed and affected by the update of the portion of the data comprises determining the columns of data that are indexed based on a program of machine code database instructions.

21. A method of writing data to a memory coupled to a database hardware accelerator based on scattered writes/reads, said method comprising:

gathering scattered index data into the memory;

passing collected index data back to a host processor;

receiving an updated index based on the collected index data;

distributing the collected index data into the memory based on the updated index data using a machine code database instruction; and

updating storage with the updated index data.

22. The method of claim 21, wherein distributing the collected index data into the memory comprises distributing the collected index data into the data based on an atomic operation of the machine code database instruction.

23. The method of claim 21, further comprising logging the distribution of the collected index data in the memory based on the updated index data.

24. The method of claim 21, further comprising maintaining the log of the distribution in the memory in a storage device.