Querying a not only structured query language (NoSQL) database using structured query language (SQL) commands

• US 10,496,640 B2
• Filed: 12/18/2013
• Issued: 12/03/2019
• Est. Priority Date: 12/19/2012
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Taylor, James
• Primary Examiner(s): Gurmu, Muluemebet
• Application Number:
  US14/133,431
• Publication Number:
  US 20140172833A1
• Time in Patent Office: 2,176 Days
• Field of Search: 707722
• CPC Class Codes: G06F 16/24534 Query rewriting; Transformation

Abstract

Techniques for querying a Not-Only/Non Structured Query Language (NoSQL) database using Structure Query Language (SQL) commands. A SQL query is received. The SQL query is transformed into one or more non-relational database scans having associated row key ranges. The one or more non-relational database scans are executed in parallel for each row key range. The results from the parallel scans of the non-relational database are combined. The combined results are presented as results of the SQL query.

Claims

1. A method comprising:

receiving, with a Structured Query Language (SQL)-to-Non-Structured Query Language (NoSQL) agent, a SQL query, wherein the SQL query is received through a first application programming interface (API) at a first architectural level;

transforming, with a server-side JAVA®

Database Connectivity (JDBC) driver associated with the SQL-to-NoSQL agent, the SQL query into a set of one or more non-relational database scans having associated row key ranges and having no joins in the set by

building a map of feature values, and

returning one aggregated row per distinct group;

sending, with the SQL-to-NoSQL agent, the set of one or more non-relational database scans having no joins with instructions for execution in parallel for each row key range, wherein the non-relational database scans are sent to the non-relational database through a second API at a second architectural level that is lower than the first architectural level and the SQL query language is to be used to manage schemas at a lower level;

causing results from the set of one or more non-relational database scans to be joined;

receiving, with the SQL-to-NoSQL agent, results from the set of one or more non-relational database scans through the second API;

performing, with the server-side JDBC driver, merge sorting on the results to generate combined results to provide an aggregate result to the SQL; and

transmitting, with the SQL-to-NoSQL agent, the aggregated result as results of the SQL query through the first API.

2. The method of claim 1 wherein the non-relational database comprises a non-relational distributed database.

3. The method of claim 1 wherein the non-relational database is part of a multitenant database environment.

4. The method of claim 1 further comprising receiving a schema for data in the non-relational database.

5. A non-transitory computer-readable medium having stored thereon instructions that, when executed, are configurable to cause one or more processors to:

receive, with a Structured Query Language (SQL)-to-Non-Structured Query Language (NoSQL) agent, a SQL query, wherein the SQL query is received through a first application programming interface (API) at a first architectural level;

transform, with a server-side JAVA®

Database Connectivity (JDBC) driver associated with the SQL-to-NoSQL agent, the SQL query into a set of one or more non-relational database scans having associated row key ranges and having no joins in the set by

building a map of feature values, and

returning one aggregated row per distinct group;

send, with the SQL-to-NoSQL agent, the set of one or more non-relational database scans having no joins with instructions for execution in parallel for each row key range, wherein the non-relational database scans are sent to the non-relational database through a second API at a second architectural level that is lower than the first architectural level and the SQL query language is to be used to manage schemas at a lower level;

cause results from the set of one or more non-relational database scans to be joined;

receive, with the SQL-to-NoSQL agent, results from the set of one or more non-relational database scans through the second API;

perform, with the server-side JDBC driver, merge sorting on the results to generate combined results to provide an aggregate result to the SQL; and

transmit, with the SQL-to-NoSQL agent, the aggregated result as results of the SQL query through the first API.

6. The non-transitory computer-readable medium of claim 5 wherein the non-relational database comprises a non-relational distributed database.

7. The non-transitory computer-readable medium of claim 5 wherein the non-relational database is part of a multitenant database environment.

8. The non-transitory computer-readable medium of claim 5 further comprising instructions that, when executed by the one or more processors, cause the one or more processors to receive a schema for data in the non-relational database.

9. A system comprising:

one or more interfaces to communicate with one or more user systems;

a server system having one or more hardware processors communicatively coupled with the one or more user systems via a network through the one or more interfaces, the server system to provide a multitenant environment, wherein the multitenant environment includes data for multiple client entities, each identified by a tenant identifier (ID) having one of one or more users associated with the tenant ID, users of each of multiple client identities can only access data identified by a tenant ID associated with the respective client entity, and the multitenant environment is at least a hosted database provided by an entity separate from the client entities, and provides on-demand database service to the client entities, the server system further configurable to receive, with a Structured Query Language (SQL)-to-Non-Structured Query Language (NoSQL) agent, a SQL query, wherein the SQL query is received through a first application programming interface (API) at a first architectural level, to transform, with a server-side JAVA®

Database Connectivity (JDBC) driver associated with the SQL-to-NoSQL agent, the SQL query into a set of one or more non-relational database scans having associated row key ranges and having no joins in the set by building a map of feature values and returning one aggregated row per distinct group, to send, with the SQL-to-NoSQL agent, the set of one or more non-relational database scans having no joins with instructions for execution in parallel for each row key range, wherein the non-relational database scans are sent to the non-relational database through a second API at a second architectural level that is lower than the first architectural level and the SQL query language is to be used to manage schemas at a lower level, to cause results from the set of one or more non-relational database scans to be joined, to receive, with the SQL-to-NoSQL agent, results from the set of one or more non-relational database scans through the second API, to perform, with the server-side JDBC driver, merge sorting on the results to generate combined results to provide an aggregate result to the SQL, and to transmit, with the SQL-to-NoSQL agent, the aggregated result as results of the SQL query through the first API.

10. The system of claim 9 wherein the non-relational database comprises a non-relational distributed database.

11. The system of claim 9 wherein the non-relational database is part of a multitenant database environment.

12. The system of claim 9 further to cause the one or more processors to receive a schema for data in the non-relational database.