Multi-input SQL-MR

• US 10,452,632 B1
• Filed: 06/27/2014
• Issued: 10/22/2019
• Est. Priority Date: 06/29/2013
• Status: Active Grant

• Current Assignee: Teradata Corporation
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Simmen, David, Wang, Ling, Joshi, Milind
• Primary Examiner(s): Skhoun, Hickham
• Application Number:
  US14/317,995
• Time in Patent Office: 1,943 Days
• Field of Search: 707610
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/285 Clustering or classification
• CPC Class Codes: H04L 67/1097 for distributed storage of data in networks, e.g. transport arrangements for network file system [NFS], storage area networks [SAN] or network attached storage [NAS]

Abstract

A system may include an array of storage devices configured to store a data. The system may further include an array of processing nodes in communication with the array of storage devices. The array of processing nodes may receive a request to perform at least one task associated with the data. The request may include a function call to a function configured to operate on a first data table and a second data table included in the data. The array of processing nodes may partition the first data table among respective subsets of the processing nodes based on a partition key. The array of processing nodes may distribute the second data table among the partitions based on the partition key. The array of processing nodes may execute the function on the first data table and the second data table at each of the partitions. A method and computer-readable medium may also be implemented.

Claims

1. A system comprising:

an array of storage devices configured to store data;

an array of processing nodes in communication with the array of storage devices, the array of processing nodes configured to;

receive a request to perform at least one task associated with the data, wherein the request includes a function call to a function configured to operate on a first data table and a second data table included in the data;

partition rows of the first data table into a plurality of row partitions among respective subsets of the processing nodes based on a partition key common to both the first data table and the second data table, wherein at least one row partition of the first data table comprises a plurality of rows;

for each partition key, generate a relation, wherein each relation is a data structure configured to include a plurality of columns in a single row, wherein each column of the relation is configured to maintain multiple column values from at least one row of a different data table;

for each relation, insert a plurality of row values from at least one row of the first data table from one of the row partitions into a single column of a row of the relation according to the partition key;

distribute row values from at least one row of the second data table into another single column of at least one relation according to the partition key, wherein at least one row value from at least one column of the second data table is distributed into a column of a row of a relation and, wherein the column of the row of the relation is in a row of the relation having at least one row value from the first data table in a different column of the relation; and

execute the function on each relation to generate at least one output data object.

2. The system of claim 1, wherein the plurality of processing nodes is further configured to:

group generated relations; and

for each row in the grouped relations, iteratively process the rows of the first data table and the second data table according to the function to generate zero or more output rows.

3. The system of claim 1, wherein the plurality of processing nodes is further configured to order rows of the first data table per row partition.

4. The system of claim 1, wherein the plurality of processing nodes is further executable to partition the second data table into row partitions based on the partition key.

5. The system of claim 1, wherein the plurality of processing nodes is further executable to replicate the second data table at each row partition.

6. The system of claim 1, wherein the partition key is specified in the request.

7. A computer-implemented method executable by a plurality of processing nodes, the method comprising:

receiving a request to perform at least one task associated with data in a storage device, wherein the request includes a function call to a function configured to operate on a plurality of data tables included in the data;

retrieving the plurality of data tables from at least one storage device;

partitioning rows of a first one of the plurality of data tables into a plurality of row partitions among respective subsets of the processing nodes based on a partition key common to each data table, wherein at least one row partition of the first one of the plurality of data tables comprises a plurality of rows;

for each partition key, generating a relation, wherein each relation is a data structure configured to include a plurality of columns in a single row, wherein each column of the relation is configured to maintain multiple column values from at least one row of a different data table;

for each relation, inserting a plurality of row values from at least one row from a row partition of the first one of the plurality of data tables into a single column of the relation according to the partition key;

distribute row values from at least one row of the second data table into another single column of at least one relation according to the partition key, wherein at least one row value from at least one column of the second data table is distributed into a column of a row of a relation and, wherein the column of the row of the relation is in a row of the relation having at least one row value from the first data table in a different column of the relation;

distributing row values of other data tables into a respective column of at least one relation according to the partition key; and

executing the function on each partition to generate at least one output data object.

8. The method of claim 7 further comprising:

grouping generated relations; and

for each row in the grouped relations, iteratively processing the rows of the plurality of data tables according to the function to generate zero or more output rows.

9. The method of claim 7 further comprising ordering rows of the first data table per row partition.

10. The method of claim 7, wherein distributing the other data tables comprises partitioning at least one of the other data tables among the row partitions based on the partition key.

11. The method of claim 7, wherein distributing the other data tables comprises replicating at least one of the other tables at each row partition.

12. The method of claim 7, wherein the partition key is specified in the request.

13. A non-transitory computer-readable medium encoded with a plurality of instructions executable by a processor, the plurality of instructions comprising:

instructions to receive a request to perform at least one task associated with data stored in at least one storage device, wherein the request includes a function call to a function configured to operate on a first data table and a second data table included in the data;

instructions to retrieve the first data table and the second data table from the at least one storage device;

instructions to partition rows of the first data table into a plurality of row partitions among respective subsets of the processing nodes based on a partition key common to both the first data table and the second data table, wherein at least one row partition of the first data table comprises a plurality of rows;

instructions to generate a relation for each partition key, wherein each relation is a data structure configured to include a plurality of columns in a single row, wherein each column of the relation is configured to maintain multiple column values from at least one row of a different data table;

instructions to insert, for each relation, a plurality of row values from at least one row of the first data table from one of the row partitions into a single column of a row of the relation according to the partition key;

instructions to distribute row values from at least one row of the second data table into another single column of at least one relation according to the partition key, wherein at least one row value from at least one column of the second data table is distributed into a column of a row of a relation and, wherein the column of the row of the relation is in a row of the relation having at least one row value from the first data table in a different column of the relation; and

instructions to execute the function on each relation to generate at least one output data object.

14. The computer-readable medium of claim 13, wherein the plurality of instructions further comprises:

instructions to group generated relations; and

instructions to iteratively process, for each row in the grouped relations, the rows of the first data table and the second data table according to the function to generate zero or more output rows.

15. The non-transitory computer-readable medium of claim 13, wherein the instructions to distribute the second data table the further comprise instructions to partition the second data table into row partitions based on the partition key.

16. The non-transitory computer-readable medium of claim 13, wherein the instructions to distribute the second data table comprise instructions to replicate the second data table at each row partition.

17. The non-transitory computer-readable medium of claim 13, wherein the partition key is specified in the request.