SCHEMA-LESS ACCESS TO STORED DATA

• US 20150088924A1
• Filed: 09/23/2013
• Published: 03/26/2015
• Est. Priority Date: 09/23/2013
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Daniel Abadi
• Inventors: ABADI, DANIEL, Tahara, Daniel, Diamond, Thaddeus
• Granted Patent: US 9,471,711 B2
• Time in Patent Office: Days
• US Class Current: 707/769
• CPC Class Codes: G06F 16/90335 Query processing

Abstract

A system, a method, and a computer program product for accessing stored partially structured data using a structure-based query language. A partially-structured data is stored in a data management system using at least one key-value pair. Using the data management system, a query of data is processed. The query is generated using a structured language.

Claims

1. A computer implemented method, comprising:

storing a partially-structured data in a data management system using at least one key-value pair;

processing, using the data management system, a query of data, wherein the query is generated using a structured language;

generating an interface containing a plurality of columns of data, wherein each column in the plurality of columns corresponds to a key in a plurality of stored key-value pairs; and

generating, using the generated interface, the query of data using the structured language;

wherein the generated query refers to at least one column in the generated interface;

wherein the plurality of columns includes a physical column stored in the data management system and a virtual column stored in an overflow column in the data management system, the overflow column contains at least one key-value pair having a key that is not associated with the physical column stored in the data management system;

wherein at least one of the storing, the processing, the generating an interface, and the generating the query of data is performed using at least one processor of at least one computing system.

5. The method according to claim 1, wherein the data management system stores the data using a plurality of keys and includes a plurality of physical columns and an overflow column, wherein each physical column stores data identified by a key in the plurality of keys and the overflow column stores data in a serialized format, the overflow column containing at least one virtual column.

6. The method according to claim 5, wherein the processing further comprises

determining whether the queried data is stored in at least one of the following:

at least one physical column in the plurality of physical columns and the overflow column; and

generating, by the data management system, a dataset responsive to the query, wherein the dataset includes data obtained from at least one of;

the at least one physical column and the overflow column.

7. The method according to claim 6, wherein the processing further comprises

materializing the at least one virtual column by extracting data corresponding to at least one key identified in the query.

8. The method according to claim 6, wherein the data management system stores data in a plurality of tables, each table in the plurality of tables containing logically associated data,

wherein the processing further comprises joining the plurality of tables.

9. The method according to claim 6, wherein the data management system stores data in a single table containing an array of data objects within the data.

10. The method according to claim 6, wherein the data management system stores data based on at least one document schema.

11. The method according to claim 10, where the at least one document schema identifies the stored data based on at least one of the following parameters:

at least one key corresponding to the data, a type of data, a number of occurrences of each key corresponding to the data, whether the data is stored in the at least one physical column in the plurality of physical columns, whether the data is stored in the overflow column, and a flag.

12. The method according to claim 11, wherein the at least one key corresponding to the data and the type of data form a global dictionary table containing information about data previously stored in and/or recently loaded into a plurality of tables of the data management system.

13. The method according to claim 11, wherein the parameters including the number of occurrences of each key corresponding to the data, whether the data is stored in the at least one physical column in the plurality of physical columns, whether the data is stored in the overflow column, and the flag are specific to each table in a plurality of tables of the data management system.

14. The method according to claim 11, wherein the flag indicates that the data corresponding to a key in the plurality of keys is stored in at least one of the following:

the at least one physical column and the overflow column.

15. The method according to claim 6, wherein the serialized format includes a header containing a number of data attributes stored in the at least one virtual column, an attribute identifier for each data attribute of data stored in the at least one virtual column, and an offset location of each value of data corresponding to each data attribute stored in the in the at least one virtual column.

16. The method according to claim 15, wherein the processing further comprises

de-serializing data stored in the serialized format in the overflow column by

obtaining, based on a key contained from the received query, an attribute identifier corresponding to the data stored in the at least one virtual column;

determining, based on the obtained attribute identifier, an offset location of each value of data corresponding to the obtained attribute identifier; and

retrieving, based on the determined offset location, a value of data, wherein the retrieved data corresponds to the key contained in the received query.

17. The method according to claim 6, wherein the data is loaded for storage in the data management system by

validating syntax of the data being loaded into the data management system;

and

serializing the loaded data prior to storing the data in the data management system into the serialized data format, wherein the serialization of loaded data includes determination of at least one presence, type and sparsity of the keys in the loaded data.

18. The method according to claim 6, further comprising

determining, by the data management system, whether to store data stored in the overflow column in the at least one physical column.

19. The method according to claim 18, wherein the determining further comprises

determining at least one of the following a sparsity of at least one attribute, a purity of at least one attribute, a cardinality of at least one attribute, and an attribute schema corresponding to the data stored in the overflow column.

20. The method according to claim 19, wherein data stored in the overflow column and corresponding to attributes having low sparsity is moved for storage into the at least one physical column.

21. The method according to claim 19, wherein data stored in the overflow column and corresponding to attributes having low purity is retained for storage into the overflow column and is not moved for storage in the at least one physical column.

22. The method according to claim 14, wherein based on a determination that data corresponding to a key in the plurality of keys is stored in the at least one physical column and the overflow column, the data stored in the overflow column is moved for storage in the at least one physical column.

23. The method according to claim 6, wherein the processing further comprises

determining whether the received query includes a reference to the at least one virtual column;

modifying the reference to include a key extraction over the overflow column when the reference to the at least one virtual column is a key in the plurality of keys, wherein the overflow column stores data corresponding to the same key.

24. The method according to claim 1, wherein the data includes at least one of the following:

a JavaScript Object Notation data, a binary-encoded serialization of JavaScript Object Notation data, Extensible Markup Language data, universal resource locator query string data, and Resource Description Framework data.

25. The method according to claim 1, wherein the query is a structured query language query.

2. (canceled)

3. The method according to claim 2, wherein the interface is a tabular view presented to a user.

4. The method according to claim 2, wherein the generated query refers to at least one column in the generated interface;

wherein the at least one column refers to at least one of the following;

a physical column stored in the data management system and a virtual column stored in an overflow column in the data management system.

26. A computer program product comprising a machine-readable medium storing instructions that, when executed by at least one programmable processor, cause the at least one programmable processor to perform operations comprising:

storing a partially-structured data in a data management system using at least one key-value pair; and

processing, using the data management system, a query of data, wherein the query is generated using a structured language;

generating an interface containing a plurality of columns of data, wherein each column in the plurality of columns corresponds to a key in a plurality of stored key-value pairs; and

generating, using the generated interface, the query of data using the structured language;

wherein the generated query refers to at least one column in the generated interface;

wherein the plurality of columns includes a physical column stored in the data management system and a virtual column stored in an overflow column in the data management system, the overflow column contains at least one key-value pair having a key that is not associated with the physical column stored in the data management system.

27. A system comprising:

at least one programmable processor; and

a machine-readable medium storing instructions that, when executed by the at least one programmable processor, cause the at least one programmable processor to perform operations comprising;

storing a partially-structured data in a data management system using at least one key-value pair; and

processing, using the data management system, a query of data, wherein the query is generated using a structured language;

generating an interface containing a plurality of columns of data, wherein each column in the plurality of columns corresponds to a key in a plurality of stored key-value pairs; and

generating, using the generated interface, the query of data using the structured language;

wherein the generated query refers to at least one column in the generated interface;

wherein the plurality of columns includes a physical column stored in the data management system and a virtual column stored in an overflow column in the data management system, the overflow column contains at least one key-value pair having a key that is not associated with the physical column stored in the data management system.