Automated data conversion and route tracking in distributed databases

• US 10,380,131 B2
• Filed: 08/24/2012
• Issued: 08/13/2019
• Est. Priority Date: 10/09/2008
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lee, Adrian
• Primary Examiner(s): Featherstone, Mark D
• Assistant Examiner(s): Gmahl, Navneet
• Application Number:
  US13/594,157
• Publication Number:
  US 20120323949A1
• Time in Patent Office: 2,545 Days
• CPC Class Codes: G06F 16/2471 Distributed queries
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

A determination is made as to whether a data type associated with a received logical table definition associated with a database query within a distributed database system differs from a data type associated with a local logical table definition. A data conversion is performed on data retrieved from a locally-stored physical table referenced by the local logical table definition in response to determining that the data types differ. Local data conversion information is created identifying the data conversion performed on the retrieved data in response to performing the data conversion. At least one of the retrieved data and the converted data with the created local data conversion information is forwarded to at least one of a distributed database node and an application-level module.

Claims

1. A method, comprising:

receiving, by a local sub-system from a remote sub-system of a distributed database system, over a communication network and from a requestor, a database query that comprises as received (i) a query select statement to be performed locally by the local sub-system, and (ii) a remote logical table definition that comprises remote logical table element names and element data type formatting of a remote logical table defined at the remote sub-system;

parsing the database query to determine logical table definition data indicative of the remote logical table element names and element data type formatting of the remote logical table definition;

determining, by use of the received remote logical table element names and element data type formatting, whether a data type associated with element(s) of the remote logical table definition received within the database query and requested by the received query select statement differs from a data type associated with element(s) of a local logical table definition that comprises matching logical table element names;

performing a data conversion on data retrieved from a locally-stored physical table referenced by the local logical table definition in response to determining that the data type associated with the requested element(s) of the remote logical table definition differs from the data type associated with the element(s) of the local logical table definition that comprises the matching logical table element names;

creating local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion; and

forwarding at least one of the retrieved data and the converted data with the created local data conversion information to at least one of a distributed database node and an application-level module.

2. The method of claim 1, where performing the data conversion on the retrieved data comprises at least one of promoting a data type associated with the retrieved data to the data type associated with the received remote logical table definition, demoting a data type associated with the retrieved data to the data type associated with the received remote logical table definition, and casting a data type associated with the retrieved data to the data type associated with the received remote logical table definition.

3. The method of claim 1, where creating the local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion comprises adding a node identifier of the local sub-system to the local data conversion information.

4. The method of claim 1, further comprising one of:

matching, by comparison of the remote logical table definition with local logical table definitions, element(s) of the remote logical table definition requested by the query select statement to element(s) of the local logical table definition that references a locally-stored physical table against which to perform the query select statement;

or

determining whether the data conversion performed on the retrieved data results in a loss of precision associated with the converted data.

5. The method of claim 1, where creating the local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion comprises adding local conversion precision loss tracking information to the local data conversion information in response to determining that the data conversion performed on the retrieved data results in a loss of precision associated with the converted data.

6. The method of claim 1, further comprising:

receiving remote data and remote data conversion information in association with the received remote logical table definition, where the remote data conversion information identifies at least one data conversion operation performed on the received remote data by at least one other node within the distributed database system; and

performing a data conversion on the received remote data in response to determining that the data type associated with the received remote logical table definition differs from the data type associated with the respective element(s) of the local logical table definition that comprise the matching logical table element names.

7. The method of claim 6, where creating the local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion comprises appending the created local data conversion information to the received remote data conversion information.

8. The method of claim 7, further comprising responding to the database query with at least one of the retrieved data and the converted data with the created local data conversion information, the received remote data conversion information with the appended local data conversion information, and at least one of the received remote data and the converted remote data to the at least one of the distributed database node and the application-level module.

9. The method of claim 6, further comprising:

determining whether the received remote data conversion information comprises remote conversion precision loss tracking information;

determining whether the data conversion performed on the received remote data results in a loss of precision that is larger than a configured precision loss tolerance based upon the received remote conversion precision loss tracking information; and

rejecting the received remote data in response to determining that the data conversion performed on the received remote data is larger than the configured precision loss tolerance.

10. The method of claim 6, further comprising identifying preferred query routing within the distributed database system that minimizes future data casting operations based upon the received remote data conversion information.

11. A system, comprising:

a memory adapted to store data conversion and route tracking information; and

a processor programmed to;

receive, at a local sub-system from a remote sub-system of a distributed database system, over a communication network and from a requestor, a database query that comprises as received (i) a query select statement to be performed locally by the local sub-system, and (ii) a remote logical table definition that comprises remote logical table element names and element data type formatting of a remote logical table defined at the remote sub-system;

parse the database query to determine logical table definition data indicative of the remote logical table element names and element data type formatting of the remote logical table definition;

determine, by use of the received remote logical table element names and element data type formatting, whether a data type associated with element(s) of the remote logical table definition received within the database query and requested by the received query select statement differs from a data type associated with element(s) of a local logical table definition that comprises matching logical table element names;

perform a data conversion on data retrieved from a locally-stored physical table referenced by the local logical table definition in response to determining that the data type associated with the requested element(s) of the remote logical table definition differs from the data type associated with the element(s) of the local logical table definition that comprises the matching logical table element names;

create local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion;

store the local data conversion information to the memory; and

forward at least one of the retrieved data and the converted data with the created local data conversion information to at least one of a distributed database node and an application-level module.

12. The system of claim 11, where, in being programmed to perform the data conversion on the retrieved data, the processor is programmed to at least one of promote a data type associated with the retrieved data to the data type associated with the received remote logical table definition, demote a data type associated with the retrieved data to the data type associated with the received remote logical table definition, and cast a data type associated with the retrieved data to the data type associated with the received remote logical table definition.

13. The system of claim 11, where, in being programmed to create the local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion, the processor is programmed to add a node identifier of the local sub-system to the local data conversion information.

14. The system of claim 11, where the processor is further programmed to one of:

match, by comparison of the remote logical table definition with local logical table definitions, element(s) of the remote logical table definition requested by the query select statement to element(s) of the local logical table definition that references a locally-stored physical table against which to perform the query select statement;

or

determine whether the data conversion performed on the retrieved data results in a loss of precision associated with the converted data.

15. The system of claim 11, where, in being programmed to create the local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion, the processor is programmed to add local conversion precision loss tracking information to the local data conversion information in response to determining that the data conversion performed on the retrieved data results in a loss of precision associated with the converted data.

16. The system of claim 11, where the processor is further programmed to:

receive remote data and remote data conversion information in association with the received remote logical table definition, where the remote data conversion information identifies at least one data conversion operation performed on the received remote data by at least one other node within the distributed database system; and

perform a data conversion on the received remote data in response to determining that the data type associated with the received remote logical table definition differs from the data type associated with the respective element(s) of the local logical table definition that comprise the matching logical table element names.

17. The system of claim 16, where, in being programmed to create the local data conversion information identifying the data conversion performed on the retrieved data in response to performing the data conversion, the processor is programmed to append the created local data conversion information to the received remote data conversion information.

18. A method, comprising:

receiving, by a local sub-system of a distributed database system, a local table definition defining a local table of data, the local table definition including information indicative of;

(i) a plurality of local table element names, and (ii) for each local table element name of the plurality of local table element names, a respectively corresponding data type;

receiving, by the local sub-system, from a remote requestor sub-system of the distributed database system, a database query that includes;

(1) a query select statement including information indicative of requested data from the local table of data, and (2) a remote requestor table definition including information indicative of;

(a) a plurality of remote requestor table element names, and (b) for each remote requestor table element name of the plurality of remote requestor table element names, a respectively corresponding data type;

determining that a first remote requestor table element name, included in the query select statement, corresponds to a first local table element name;

retrieving raw query response data from the local table of data, with the raw query response data being data that is responsive to the query select statement, and with the raw query response data including raw first element data corresponding to the first local table element name;

determining that a data type corresponding to the first local table element name does not match a data type corresponding to the first remote requestor table element name; and

responsive to the determination that the data type corresponding to the first local table element name does not match the data type corresponding to the first remote requestor table element name, converting the raw first element data, having the data type corresponding to first local table element name, to converted first element data, having the data type corresponding to the first remote requestor table element name.

19. The method of claim 18, further comprising:

responsive to the conversion of the raw first element data of the raw query response data, creating first conversion information identifying the conversion of the raw first element data of the raw query response data; and

sending, by the local sub-system, over a communication network and to a destination device, the raw query response data and the first conversion information.

20. The method of claim 18, further comprising:

replacing the raw first element data of the raw query response data with the converted first element data to obtain converted query response data;

responsive to the conversion of the raw first element data of the raw query response data, creating first conversion information identifying the conversion of the raw first element data of the raw query response data; and

sending, by the local sub-system, over a communication network and to a destination device, the converted query response data and the first conversion information.