System for providing structured query language access to non-relational data stores
Systems and methods for providing structured query language (SQL) access to non-relational data stores are described. SQL access can be provided to a non-relational data store in one embodiment by mapping storage attributes of the data store to one or more virtual relational tables. The virtual tables can include a schema representation of data stored in a non-relational data store without actually including the data in the virtual tables. In some embodiments, these systems and methods therefore advantageously merge certain benefits of relational database architectures with the scalability benefits of certain non-relational data store architectures. Certain features of the systems and methods described herein can also be used with existing relational databases. For example, the systems and methods described herein can perform join or similar operations between a relational table in one data store and a data object in another data store.
1. A system for providing structured query language access to a data object, the system comprising a computer processor interoperably coupled to memory, wherein the computer processor is operable to execute:
a mapping module configured to:
identify storage attributes of a data object stored within a data store, the data object being incompatible with a subset of structured query language (SQL),
analyze the storage attributes to determine whether the storage attributes comprise any static attributes and any repeating attributes,
in response to identifying a static attribute in the storage attributes, creating a first virtual table comprising a column having a first relational attribute that is based at least partly on the static attribute; and
in response to identifying repeating data in the storage attributes, generating a second virtual table related to the first virtual table, the second virtual table comprising a column having a second relational attribute that is derived from the repeating data, wherein the second virtual table logically represents data stored in the data object without storing a copy of the data in the data object; and
a proxy layer configured to:
receive a request to execute a selected SQL command on one or both of the first and second virtual tables,
determine the data store to be capable of performing join operations;
in response to the determination, split the SQL command into a first SQL join command to be applied to the data store and a second SQL join command to be applied to a second data store comprising a second data object,
in response to receiving the request to execute the selected SQL command, send a first instruction to the data store to cause the data store to perform a join operation on the data object responsive to the first SQL join command,
receive first results from the join operation performed by the data store,
send a second instruction to the second data store to cause the second data store to return second results, and
apply the second SQL join command to the first results and the second results.
2. The system of claim 1 , wherein the computer processor is further operable to execute a query translator, wherein the query translator is configured to send said instruction from the proxy layer to the data store by making an application programming interface (API) call to the data store.
3. The system of claim 1 , wherein the mapping module is further configured to map a second data object of the data store to a third virtual table.
4. The system of claim 2 , wherein one or both of the mapping module and the query translator are included in a plug-in to a database management system (DBMS).
5. The system of claim 1 , wherein the system comprises a plurality of computing devices.
6. A method of providing structured query language access to a data object, the method comprising:
identifying storage attributes of a data object stored within a non-relational data store;
analyzing the storage attributes by a computer system comprising computer hardware to determine whether the storage attributes comprise any static attributes and any repeating data;
in response to identifying a static attribute of the storage attributes, programmatically creating a first virtual table comprising a first column that is based at least partly on the static attribute; and
in response to identifying repeating data of the storage attributes, programmatically generating a second virtual table related to the first virtual table, the second virtual table comprising a second column that is derived from the repeating data;
receiving a request to execute a selected SQL command on one or both of the first and second virtual tables;
determining the data store to be capable of performing join operations;
in response to the determining, splitting the selected SQL command into a first SQL join command to be applied to the data store and a second SQL join command to be applied to a second data store comprising a second data object;
in response to receiving the request to execute the selected SQL command, sending a first instruction to the data store to cause the data store to perform a join operation on the data object responsive to the first SQL join command;
receiving first results from the join operation performed by the data store;
sending a second instruction to the second data store to cause the second data store to return second results; and
applying the second SQL join command to the first results and the second results.
7. The method of claim 6 , wherein said identifying the repeating data further comprises identifying a variable number of entries in records of the data object.
8. The method of claim 6 , wherein said identifying the repeating data comprises identifying dynamic data in column names of the data object.
9. The method of claim 6 , wherein said identifying the repeating data comprises identifying one or more of the following in the data object: a column family, a supercolumn, and a nested document.
10. The method of claim 6 , wherein the first virtual table comprises a mapping from the static attribute to the first column without including actual data stored in the data object.
11. The method of claim 6 , wherein the second virtual table comprises a mapping from the repeating data to the second column without including actual data stored in the data object.
12. The method of claim 6 , wherein the data store comprises a distributed database.
13. The method of claim 6 , wherein the data store comprises one of the following: a document store, a column-oriented data store, and a key-value store.
14. The method of claim 6 , further comprising providing a user interface comprising functionality for a user to adjust a data type of the first or second column.
15. The method of claim 6 , further comprising providing a user interface comprising functionality for the user to adjust a constraint on the first or second column.
16. Non-transitory physical computer storage comprising computer-executable instructions that direct a computing system to implement a method of providing structured query language access to a data object, the method comprising:
identifying attributes of a data object stored within a data store, the data object being incompatible with a subset of structured query language (SQL); and
mapping the attributes to a relational schema to thereby enable SQL queries to be performed with respect to the data object by at least performing one or more of the following:
moving repeating data of the data object to a subtable having a relational association with a parent table created from static data of the data object, and
projecting the data object from a higher dimensionality to a lower dimensionality;
receiving a request to execute a selected SQL command on the data;
determining the data store to be capable of performing join operations;
in response to the determining, splitting the selected SQL command into a first SQL join command to be applied to the data store and a second SQL join command to be applied to a second data store comprising a second data object;
in response to receiving the request to execute the selected SQL command, sending a first instruction to the data store to cause the data store to perform a join operation on the data object responsive to the first SQL join command;
receiving first results from the join operation performed by the data store;
sending a second instruction to the second data store to cause the second data store to return second results; and
applying the second SQL join command to the first results and the second results.
17. The non-transitory physical computer storage of claim 16 , wherein the attributes of the data object comprise dimensions of a cube data store.
18. The non-transitory physical computer storage of claim 17 , wherein said projecting the data object comprises creating one or more of the following columns: a level column, a slice column, and a cross-join column, thereby enabling one or more of a level, slice, and cross-join to be selected from the cube data store.
19. The non-transitory physical computer storage of claim 17 , further comprising passing cube selection criteria in the SQL command directly to the cube data store, bypassing a SQL engine.
20. The non-transitory physical computer storage of claim 16 , wherein said mapping comprises at least partially normalizing the data object.
21. The non-transitory physical computer storage of claim 16 , in combination with a computer system comprising computer hardware.