Multi-modal SQL engine connector for accessing data in a non-SQL document store
A SQL database system is disclosed for reading and writing a non-SQL document store using SQL. The database system includes a SQL query engine configured to use different types of dynamically loadable connectors adapted to communicate with the non-SQL document store via its data access interface. The connectors may include a first connector that treats data within an individual document in the document store as multiple table rows, and a second connector that treats individual documents as individual table rows. In some embodiments, both types of document access modes may be implemented by a single multi-modal connector. In some embodiments, the connector may enable a table to be stored across multiple documents and provide the document identifier of the documents as an attribute of the table. Advantageously, by allowing multiple rows to be stored in individual documents, a table can be stored using less storage space and accessed more efficiently.
1 . A system comprising:
one or more computer devices that implement a structured query language (SQL) database system, configured to:
receive a connection request from a client, wherein the connection request requests a connection to a non-SQL document store that stores table data under multiple storage modes, wherein the connection request specifies (a) a client credential of the client and (b) a connection parameter that selects a first storage mode of the multiple storage modes to use for the connection;
dynamically load a multi-modal connector that is configured to connect to the non-SQL document store and communicate with the non-SQL document store under the multiple storage modes;
execute the multi-modal connector to establish the connection to the non-SQL document store, wherein the connection is established after authentication of the client credential with the non-SQL document store and data transmitted over the connection is encrypted according to transport layer security (TLS) protocol;
execute a SQL query engine to perform received SQL queries on the non-SQL document store via the multi-modal connector; and
during the connection, execute the multi-modal connector to:
translate the received SQL queries to fetch instructions for the non-SQL document store according to the first storage mode and decrypt TLS-encrypted table data received from the non-SQL document store for the SQL query engine;
in response to a SQL query from the client directed to a table, read metadata about the table stored in the non-SQL document store and change the connection from the first storage mode to a second storage mode based on the metadata;
translate the SQL query to fetch instructions according to the second storage mode and access table data of the table under the second storage mode;
subsequent to the connection, receive a client request from the client to change the storage mode of the table to the second storage mode, and in response:
update the metadata to change the storage mode of the table to the second storage mode; and
issue one or more update instructions to the non-SQL document store to convert the table from the first storage mode to the second storage mode, wherein the conversion converts table data of the table from one or more documents in the non-SQL document store to one or more new documents in the non-SQL document store;
wherein under the first storage mode, the multi-modal connector accesses data within a single document in the non-SQL document store that stores multiple table rows, and
wherein under the second storage mode, the multi-modal connector accesses individual documents in the non-SQL document that individually store single table rows.
2 . The system of claim 1 , wherein:
the non-SQL document store is hosted by a service provider that provides computing and storage resources for a plurality of clients; and
the multi-modal connector is configured to access the non-SQL document store via a service interface provided by the service provider.
3 . The system of claim 1 , wherein the SQL query engine is configured to perform SQL queries on another type of non-SQL document store using another type of connector adapted to communicate with the other type of non-SQL document store.
4 . The system of claim 1 , wherein the SQL query engine receives SQL queries via a command line interface of the SQL database system.
5 . The system of claim 1 , wherein the multiple storage modes include a third storage mode, and under the third storage mode, the multi-modal connector is configured to access a compressed binary object containing a group of table rows stored in a document.
6 . The system of claim 1 , wherein the SQL query engine is implemented in a private network of a company behind a firewall, and the connection is established over a public network outside the firewall.
7 . The system of claim 1 , wherein the one or more update instructions causes the non-SQL document store to initiate a conversion process to convert the table data of the table, wherein the table data is accessible under the first storage mode before completion of the conversion process, wherein the metadata is updated atomically to switch the table to the second storage mode after completion of the conversion process.
8 . The system of claim 1 , wherein, under the first storage mode, the multi-modal connector is configured to cause the non-SQL document store to store a table as a plurality of documents, each document containing an array of objects representing individual rows of the table.
9 . The system of claim 8 , wherein the multi-modal connector is configured to:
in response to a SQL query received by the SQL query engine:
cause the non-SQL document store to return objects from the documents along with document identifiers of the documents; and
add the document identifiers as an attribute of table rows returned for the SQL query.
10 . The system of claim 9 , wherein the multi-modal connector is configured to:
translate a query predicate of the SQL query into a search predicate; and
cause the non-SQL document store to search the arrays in the documents using the search predicate.
11 . The system of claim 8 , wherein the multi-modal connector is configured to:
cause the non-SQL document store to store objects in the documents in a compressed form; and
decompress the objects returned from the document.
12 . A method comprising:
performing, by a structured query language (SQL) database system:
receiving a connection request from a client, wherein the connection request requests a connection to a non-SQL document store that stores table data under multiple storage modes, wherein the connection request specifies (a) a client credential of the client and (b) a connection parameter that selects a first storage mode of the multiple storage modes to use for the connection;
dynamically loading a multi-modal connector that is configured to connect to the non-SQL document store and communicate with the non-SQL document store under the multiple storage modes;
executing the multi-modal connector to establish the connection to the non-SQL document store, wherein the connection is established after authentication of the client credential with the non-SQL document store and data transmitted over the connection is encrypted according to transport layer security (TLS) protocol;
executing a SQL query engine via the multi-modal connector to perform received SQL queries on the non-SQL document store; and
during the connection, executing the multi-modal connector, including:
translating the received SQL queries to fetch instructions for the non-SQL document store according to the first storage mode and decrypting TLS-encrypted table data received from the non-SQL document store for the SQL query engine;
in response to a SQL query from the client directed to a table, reading metadata about the table stored in the non-SQL document store and changing the connection from the first storage mode to a second storage mode based on the metadata;
translating the SQL query to fetch instructions according to the second storage mode and access table data of the table under the second storage mode;
subsequent to the connection, receiving a client request from the client to change the storage mode of the table to the second storage mode, and in response:
updating the metadata to change the storage mode of the table to the second storage mode; and
issuing one or more update instructions to the non-SQL document store to convert the table from the first storage mode to the second storage mode, wherein the conversion converts table data of the table from one or more documents in the non-SQL document store to one or more new documents in the non-SQL document store;
wherein under the first storage mode, a single document in the non-SQL document store stores multiple table rows, and
wherein under the second storage mode, the multi-modal connector accesses individual documents in the non-SQL document that individually store single table rows.
13 . The method of claim 12 , further comprising the multi-modal connector accessing the non-SQL document store via a service interface of a service provider hosting the non-SQL document store.
14 . The method of claim 12 , wherein the SQL query engine receives SQL queries via a command line interface of the SQL database system.
15 . The method of claim 12 , the multiple storage modes include a third storage mode, and under the third storage mode, the multi-modal connector is configured to access a compressed binary object containing a group of table rows stored in a document.
16 . The method of claim 12 , wherein the SQL query engine is implemented in a private network of a company behind a company firewall, and the connection is established over a public network outside the firewall.
17 . The method of claim 12 , wherein the one or more update instructions causes the non-SQL document store to initiate a conversion process to convert the table data of the table, wherein the table data is accessible under the first storage mode before completion of the conversion process, wherein the metadata is updated atomically to switch the table to the second storage mode after completion of the conversion process.
18 . The method of claim 12 , further comprising performing, by the multi-modal connector and in the first access mode:
causing the non-SQL document store to store the table as a plurality of documents, each document containing an array of objects representing individual rows of the table.
19 . The method of claim 18 , further comprising performing, by the multi-modal connector and in response to a SQL query:
causing the non-SQL document store to return objects from the documents along with document identifiers of the documents; and
adding the document identifiers as an attribute of table rows returned for the SQL query.
20 . One or more non-transitory computer-accessible storage media storing program instructions that when executed on one or more processors implement a structured query language (SQL) database system and cause the SQL database system to:
receive a connection request from a client, wherein the connection request requests a connection to a non-SQL document store that stores table data under multiple storage modes, wherein the connection request specifies (a) a client credential of the client and (b) a connection parameter that selects a first storage mode of the multiple storage modes to use for the connection;
dynamically load a multi-modal connector that is configured to connect to the non-SQL document store and communicate with the non-SQL document store under the multiple storage modes;
execute the multi-modal connector to establish the connection to the non-SQL document store, wherein the connection is established after authentication of the client credential with the non-SQL document store and data transmitted over the connection is encrypted according to transport layer security (TLS) protocol;
execute a SQL query engine to perform received SQL queries on the non-SQL document store via the multi-modal connector; and
during the connection, execute the multi-modal connector to:
translate the received SQL queries to fetch instructions for the non-SQL document store according to the first storage mode and decrypt TLS-encrypted table data received from the non-SQL document store for the SQL query engine;
in response to a SQL query from the client directed to a table, read metadata about the table stored in the non-SQL document store and change the connection from the first storage mode to a second storage mode based on the metadata; and
translate the SQL query to fetch instructions according to the second storage mode and access table data of the table under the second storage mode;
subsequent to the connection, receive a client request from the client to change the storage mode of the table to the second storage mode, and in response:
update the metadata to change the storage mode of the table to the second storage mode; and
issue one or more update instructions to the non-SQL document store to convert the table from the first storage mode to the second storage mode, wherein the conversion converts table data of the table from one or more documents in the non-SQL document store to one or more new documents in the non-SQL document store;
wherein under the first storage mode, the multi-modal connector accesses data within a single document in the non-SQL document store that stores multiple table rows, and
wherein under the second storage mode, the multi-modal connector accesses individual documents in the non-SQL document that individually store single table rows.