SYSTEMS AND METHODS FOR USING A STRUCTURED QUERY DIALECT TO ACCESS DOCUMENT DATABASES AND MERGING WITH OTHER SOURCES
Disclosed are systems and methods that allow a database engine to access a non-relational document database and dynamically apply a schema that maps documents comprising semi-structured data into a relational data structure. In embodiments, mapping may be accomplished by parsing and analyzing a set of sample documents to empirically generate a normalized tabular schema. In embodiments, the schema accommodates format evolution and variability of documents having different structures and different sets of attributes is automatically executed in a background process without requiring user involvement. This normalization process eliminates the need to create unique modeling languages for semi-structured data. In embodiments, an SQL dialect may be used to join query results and combine the resulting document data with tables from different dimensional or relational databases to generate a combined result without the need to create additional temporary tables for non-conforming documents.
1 . A system to generate a relational data model from a non-relational document database, the system comprising:
at least one processor;
a non-transitory computer-readable medium or media comprising one or more sequences of instructions which, when executed by the at least one processor, causes steps to be performed comprising:
sampling a set of documents from the non-relational document database, one or more of the documents in the set of documents comprising semi-structured document data;
generating a schema for items contained within a set of documents, the schema maps the semi-structured document data into a relational data structure; and
using Structured Query Language to query at least one of a relational and a non-relational database.
2 . The system according to claim 1 , wherein the schema merges multi-level target structures of relational data that are generated from at least the non-relational document database.
3 . The system according to claim 2 , wherein the merged schema is generated by forming aggregates that represent at least the sampled set of documents.
4 . The system according to claim 1 , further comprising storing the schema as a configuration in a normalized tabular format.
5 . The system according to claim 1 , wherein generating the schema comprises the steps of:
sampling, parsing, and analyzing the set of documents to identify non-repeating elements and repeating elements;
generating a multi-level target structure that separates the repeating elements into individual structures that comprise no repeating elements; and
using foreign keys to associate individual structures with at least a single-level target structure.
6 . The system according to claim 5 , further comprising, for a non-repeating element that is not defined in column format, determining a data type and updating or adding a column to the single-level target structure.
7 . The system according to claim 5 , wherein the multi-level target structure comprises relational tables that comprise sub-tables for the repeating elements.
8 . The system according to claim 7 , further comprising recursively generating the sub-tables via a control loop.
9 . The system according to claim 7 , wherein the relational tables form a group of relational SQL tables that can be queried using regular SQL statements.
10 . The system according to claim 5 , wherein the multi-level target structure conforms to guidelines for First Normal Form.
11 . A system to access documents in a non-relational document database by using a schema and a relational database query, the system comprising a database engine that performs the steps of:
in response to receiving the relational database query, using a schema to generate a non-relational document database query that is equivalent to the relational database query;
accessing a non-relational document database to execute the non-relational document database query and obtain data elements;
using the schema to format at least the data elements as two-dimensional tabular data; and
outputting the two-dimensional tabular data as a relational database query result.
12 . The system according to claim 11 , wherein the data elements are placed in a single row of a two-dimensional table.
13 . The system according to claim 11 , wherein generating comprises mapping names of elements in the non-relational document database to columns.
14 . The system according to claim 11 , wherein executing the non-relational document database query comprises applying one or more data manipulation methods that reduce an amount of data elements received from the non-relational document database.
15 . The system according to claim 14 , wherein the one or more data manipulation methods comprise applying at least one of a filtering condition and data aggregation.
16 . A system to generate, from documents sampled from a non-relational document database, a relational data model that collectively describes the sampled documents, the system comprising a database engine that performs the steps of:
obtaining a document from the non-relational document database, the document comprising semi-structured document data;
analyzing the document for fields and sub-documents to identify non-repeating elements and repeating elements;
generating a schema that maps the semi-structured document data into a relational data structure that comprises a top-level relational table for non-repeating elements by separating a repeating element into a sub-table that relates back to the top-level table via at least a foreign key, such that the sub-table comprises no repeating element; and
using Structured Query Language to query at least one of a relational and a non-relational database.
17 . The system according to claim 16 , wherein the non-relational document database is sampled in batches to create a chronological diverse spread of documents over evolving document formats.
18 . The system according to claim 16 , wherein the step of analyzing the document is automated.
19 . The system according to claim 18 , wherein, based on a result if analyzing the document, the database engine formats the document as tabular data.
20 . The system according to claim 16 , wherein at least one of the table and the sub-table comprises a set of named columns that comprise unique names, the names being defined by a type of data and a document reference to the element that contains data for one of the set of named columns.