DYNAMIC MAPPING OF EXTENSIBLE DATASETS TO RELATIONAL DATABASE SCHEMAS

• US 20150234870A1
• Filed: 07/23/2014
• Published: 08/20/2015
• Est. Priority Date: 02/14/2014
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Kumar, Srinivasan, Avudai, Kannan, Bog, Anja, Singh, Enakshi
• Granted Patent: US 9,639,542 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/13 File access structures, e.g. distributed indices arrangements of input from, or output to, record carriers G06F3/06
• CPC Class Codes: G06F 16/164 File meta data generation
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/86 Mapping to a database
• CPC Class Codes: G06F 16/9017 using directory or table look-up use of a directory or look-up table in file systems G06F16/13
• CPC Class Codes: G06F 16/907 Retrieval characterised by using metadata, e.g. metadata not derived from the content or metadata generated manually
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques
• CPC Class Codes: G16B 50/00 ICT programming tools or database systems specially adapted for bioinformatics
• CPC Class Codes: G16B 50/30 Data warehousing; Computing architectures
• CPC Class Codes: G16C 99/00 Subject matter not provided for in other groups of this subclass

Abstract

A text file is received. The text file is arranged in an extensible format and includes a plurality of metadata lines, a header line, and a plurality of content lines. Keys are retrieved from the content lines. For each key, a data type and a number from at least one metadata table is retrieved. Using a combination of each key and the corresponding data type and number, a column title is derived. Upon verification that the derived column title does not already exist, a column for each key is generated in a content table having a dynamic structure in a relational database schema. The column has the derived column title, a column type based on the corresponding retrieved data type, and the number of generated columns determined by the corresponding retrieved number. The retrieved value is mapped into the column associated with the corresponding key.

Claims

1. A computer implemented method comprising:

receiving data comprising a text file, the text file being arranged in an extensible format and comprising a plurality of metadata lines, a header line, and a plurality of content lines;

retrieving, from the content lines, at least one key;

retrieving, for each key, a data type and a number from at least one metadata table;

deriving at least one column title using a combination of each key and the corresponding data type and number;

generating, upon verification that the derived at least one column title does not already exist, at least one column for each key in a content table having a dynamic structure in a relational database schema, the at least one column having the derived column title, a column type based on the corresponding retrieved data type, and the number of generated columns determined by the corresponding retrieved number;

retrieving, from the content lines, at least one value corresponding to each key; and

mapping the retrieved at least one value into the at least one column associated with such key.

2. The method of claim 1 further comprising:

generating a lookup table storing a mapping of fields from the text file to the content tables, and the at least one column title of the at least one column associated with such key contained in the content tables.

3. The method of claim 1, wherein:

the retrieving from the content lines comprises retrieving the at least one key and the at least one value from at least one key-value pair identified from the content lines; and

the mapping comprises mapping each value retrieved from the key-value pair at each content line to a corresponding row in the at least one column associated with the key from the key-value pair.

4. The method of claim 3, wherein the retrieving from the content lines comprises:

identifying the at least one key-value pair from a column in the content lines section of the text file corresponding to an information parameter in the header line.

5. The method of claim 4, wherein the retrieving from the content lines comprises:

identifying the at least one key-value pair by parsing the text file and splitting up key-value pairs at a semicolon symbol.

6. The method of claim 1, wherein the retrieving from the content lines comprises:

retrieving the at least one key from a column associated with a format parameter in the header line, and

retrieving, corresponding to each key, at least one corresponding value from at least one column associated with data parameters from the header line, wherein

the at least one corresponding value is located in a similar parallel physical location as the corresponding key in the column associated with a format parameter from the header line.

7. The method of claim 6, wherein the mapping comprises:

mapping each value retrieved from at each content line, to a new row in the at least one column associated the key in the content table having the dynamic structure.

8. The method of claim 6, wherein the retrieving from the content lines comprises:

identifying the at least one key by parsing the text file and splitting up the at least one key at a colon symbol.

9. The method of claim 1, wherein the content table having a dynamic structure is a table that stores genomic variant information and corresponding chromosome number and variant position.

10. The method of claim 1, wherein the generating at least one column dynamically evolves the content table during runtime.

11. A non-transitory computer program product storing instructions which, when executed by at least one data processor forming part of at least one computing system, result in operations comprising:

receiving data comprising a text file, the text file being arranged in an extensible format and comprising a plurality of metadata lines, a header line, and a plurality of content lines;

retrieving, from the content lines, at least one key;

retrieving, for each key, a data type and a number from at least one metadata table;

deriving at least one column title using a combination of each key and the corresponding data type and number;

generating, upon verification that the derived at least one column title does not already exist, at least one column for each key in a content table having a dynamic structure in a relational database schema, the at least one column having the derived column title, a column type based on the corresponding retrieved data type, and the number of generated columns determined by the corresponding retrieved number;

retrieving, from the content lines, at least one value corresponding to each key; and

mapping the retrieved at least one value into the at least one column associated with such key.

12. The non-transitory computer program product as in claim 11, further comprising:

generating a lookup table storing a mapping of fields from the text file to the content tables, and the at least one column title of the at least one column associated with such key contained in the content tables.

13. The non-transitory computer program product as in claim 11, wherein:

the retrieving from the content lines comprises retrieving the at least one key and the at least one value from at least one key-value pair identified from the content lines; and

the mapping comprises mapping each value retrieved from the key-value pair at each content line to a corresponding row in the at least one column associated with the key from the key-value pair.

14. The non-transitory computer program product as in claim 11, wherein the retrieving from the content lines comprises:

identifying the at least one key-value pair from a column in the content lines section of the text file corresponding to an information parameter in the header line.

15. The non-transitory computer program product as in claim 14, wherein the retrieving from the content lines comprises:

identifying the at least one key-value pair by parsing the text file and splitting up key-value pairs at a semicolon symbol.

16. The non-transitory computer program product as in claim 11, wherein the retrieving from the content lines comprises:

retrieving the at least one key from a column associated with a format parameter in the header line, and

retrieving, corresponding to each key, at least one corresponding value from at least one column associated with data parameters from the header line, wherein the at least one corresponding value is located in a similar parallel physical location as the corresponding key in the column associated with a format parameter from the header line.

17. The non-transitory computer program product as in claim 16, wherein the mapping comprises:

mapping each value retrieved from at each content line, to a new row in the at least one column associated the key in the content table having the dynamic structure.

18. The non-transitory computer program product as in claim 16, wherein the retrieving from the content lines comprises:

identifying the at least one key by parsing the text file and splitting up the at least one key at a colon symbol.

19. The non-transitory computer program product as in claim 11, wherein the content table having a dynamic structure is a table that stores genomic variant information and corresponding chromosome number and variant position.

20. A system comprising:

at least one data processor; and

memory storing instructions which, when executed by the at least one data processor, result in operations comprising;

receiving data comprising a text file, the text file being arranged in an extensible format and comprising a plurality of metadata lines, a header line, and a plurality of content lines;

retrieving, from the content lines, at least one key;

retrieving, for each key, a data type and a number from at least one metadata table;

deriving at least one column title using a combination of each key and the corresponding data type and number;

generating, upon verification that the derived at least one column title does not already exist, at least one column for each key in a content table having a dynamic structure in a relational database schema, the at least one column having the derived column title, a column type based on the corresponding retrieved data type, and the number of generated columns determined by the corresponding retrieved number;

retrieving, from the content lines, at least one value corresponding to each key; and

mapping the retrieved at least one value into the at least one column associated with such key.