Aggregate and transactional networked database query processing

Patent metadata

Field	Value
Publication number	US-11675806-B2
Application number	US-202117249598-A
Country	US
Kind code	B2
Filing date	Mar 5, 2021
Priority date	Dec 14, 2020
Publication date	Jun 13, 2023
Grant date	Jun 13, 2023

Abstract

A hybrid network-based database system for handling OLTP and OLAP queries using decoupled compute and storage devices. A set of decoupled compute instances perform transactions on an OLTP database, and the data is replicated to an OLAP database, which is managed by another set of decoupled compute instances. Further, in response to queries, the database system can retrieve data from the OLTP and OLAP database for merging and processing according to the query.

First claim

What is claimed is:

1. A method comprising:
receiving, by a first set of decoupled compute instances of a distributed database, a plurality of transactional queries against a transactional processing database stored on a first set of decoupled storage instances, the first set of decoupled compute instances being remote from the first set of decoupled storage instances, each of the plurality of transactional queries comprising data manipulation language statements that change data in the transactional processing database, one or more of the first set of decoupled compute instances locally caching portions of the transactional processing database and bypassing a database compiler of the distributed database to process the plurality of transactional queries using locally cached data;
receiving one or more select queries for processing on an aggregated analytical query database of the distributed database;
replicating, by one or more hardware processors, the changed data from the transactional processing database to an object store database of an aggregated analytical query database managed by a second set of decoupled compute instances of the distributed database, the aggregated analytical query database being stored on a second set of decoupled storage instances, the second set of decoupled compute instances being remote from the second set of decoupled storage instances;
generating, by the database compiler of the distributed database, a query plan for execution of the one or more select queries using the second set of compute instances; and
generating, by the second set of decoupled compute instances, an aggregated analytical query result dataset for the one or more select queries according to the query plan.

2. The method of claim 1 , wherein the transactional processing database is an Online Transactional Processing (OLTP) database that tracks a large volume of data transactions, and wherein the aggregated analytical query database is an Online Analytical Processing (OLAP) database comprising aggregated queries and a low volume of data transactions relative to the OLTP database.

3. The method of claim 2 , further comprising:
receiving a query and determining whether the query is an OLTP query or an OLAP query.

4. The method of claim 3 , further comprising: in response to determining the query is a OLAP query, directing the query to the OLAP database and processing the query using the second set of decoupled compute instances.

5. The method of claim 3 , further comprising: in response to determining the query is a OLTP query, directing the query to the OLTP database and processing the query using the first set of decoupled compute instances.

6. The method of claim 1 , wherein the first set of decoupled compute instances are remotely connected over a network to the first set of decoupled storage instances of the transaction processing database, and wherein the second set of decoupled compute instances are remotely connected over the network to the second set of decoupled storage instances of the aggregated analytical query database.

7. The method of claim 1 , further comprising:
receiving a transactional query for processing on transactional data in the transactional processing database;
determining that the transactional data is cached on the first set of decoupled compute instances; and
generating transactional results data by applying the transactional query on the transactional data, the first set of decoupled compute instances bypassing the database compiler in generating the transactional results data and returning results to a client device that generated the transactional query, the bypassing comprising setting an active session between the client device and the first set of decoupled compute instances.

8. A system comprising:
one or more hardware processors; and
at least one memory storing instructions that, when executed by the one or more hardware processors, cause the one or more hardware processors to perform operations comprising:
receiving, by a first set of decoupled compute instances of a distributed database, a plurality of transactional queries against a transactional processing database stored on a first set of decoupled storage instances, the first set of decoupled compute instances being remote from the first set of decoupled storage instances, each of the plurality of transactional queries comprising data manipulation language statements that change data in the transactional processing database, one or more of the first set of decoupled compute instances locally caching portions of the transactional processing database and bypassing a database compiler of the distributed database to process the plurality of transactional queries using locally cached data;
receiving one or more select queries for processing on a aggregated analytical query database of the distributed database;
replicating the changed data from the transactional processing database to an object store database of an aggregated analytical query database managed by a second set of decoupled compute instances of the distributed database, the aggregated analytical query database being stored on a second set of decoupled storage instances, the second set of decoupled compute instances being remote from the second set of decoupled storage instances;
generating, by a database compiler of the distributed database, a query plan for execution of the one or more select queries using the second set of compute instances; and
generating, by the second set of decoupled compute instances, aggregated analytical query result dataset for the one or more select queries according to the query plan.

9. The system of claim 8 , wherein the transactional processing database is an Online Transactional Processing (OLTP) database that tracks a large volume of data transactions, and wherein the aggregated analytical query database is an Online Analytical Processing (OLAP) database comprising aggregated queries and a low volume of data transactions.

10. The system of claim 9 , the operations further comprising:
receiving a query and determining whether the query an OLTP query or an OLAP query.

11. The system of claim 10 , the operations further comprising:
in response to the query being the OLAP query, directing the query to the OLAP database and processing the query using the second set of decoupled compute instances.

12. The system of claim 10 , the operations further comprising:
in response to the query being the OLTP query, directing the query to the OLTP database and processing the query using the first set of decoupled compute instances.

13. The system of claim 8 , wherein the first set of decoupled compute instances are remotely connected over a network to the first set of decoupled storage instances of the transaction processing database, and wherein the second set of decoupled compute instances are remotely connected over the network to the second set of decoupled storage instances of the aggregated analytical query database.

14. The system of claim 8 , wherein the operations further comprise:
receiving a transactional query for processing on transactional data in the transactional processing database;
determining that the transactional data is cached on the first set of decoupled compute instances; and
generating transactional results data by applying the transactional query on the transactional data, the first set of decoupled compute instances bypassing the database compiler in generating the transactional results data and returning results to a client device that generated the transactional query, the bypassing comprising setting an active session b

Assignees

• Snowflake Inc

Inventors

• Galanis Leonidas
• Miller Alexander
• Waddington William
• Yagoub Khaled

Classifications

• G06F16/24524

  Access plan code generation and invalidation; Reuse of access plans · CPC title

• G06F16/27

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title

• G06F16/283 Primary

  Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP · CPC title

• G06F16/24564

  Applying rules; Deductive queries · CPC title

• G06F16/256

  in federated or virtual databases · CPC title