Partition-based scanning of external tables for query processing

Patent metadata

Field	Value
Publication number	US-11675780-B2
Application number	US-202217650462-A
Country	US
Kind code	B2
Filing date	Feb 9, 2022
Priority date	Apr 16, 2019
Publication date	Jun 13, 2023
Grant date	Jun 13, 2023

Abstract

Disclosed herein are embodiments of systems and methods for partition-based scanning of external tables for query processing. In an example embodiment, a database platform receives a query that includes one or more predicates, where the query is directed at least to data in an external table that is stored in an external storage platform that is external to the database platform. The database platform identifies, based on metadata that summarizes the data in the external table, one or more partitions of the external table that potentially include data that satisfies the one or more predicates. The database platform also identifies, from the one or more identified partitions, data that satisfies the one or more predicates. The database platform sends a response to the query to the client, the response comprising the data satisfying the one or more predicates.

First claim

What is claimed is:

1. A method performed by a database platform executing instructions on at least one hardware processor, the method comprising:
receiving a query comprising one or more predicates, the query directed at least to data in an external table that is stored in an external storage platform that is external to the database platform, the external table being based on a source directory of the external storage platform, the source directory comprising a hierarchical structure of storage locations in which data of the external table is stored in the external storage platform, the storage locations defining a plurality of partitions of the external table;
identifying, based on metadata that summarizes the data in the external table, one or more partitions in the plurality of partitions of the external table to be scanned for data satisfying the one or more predicates;
identifying, from the one or more identified partitions, data satisfying the one or more predicates;
generating a materialized view over the external table;
storing the materialized view in an internal storage platform that is internal to the database platform; and
sending a response to the query to the client, the response comprising the data satisfying the one or more predicates, the response further comprising data from the stored materialized view.

2. The method of claim 1 , further comprising:
generating a query plan based on the query, the query plan comprising a plurality of discrete subtasks, the plurality of discrete subtasks comprising instructions to scan the identified one or more partitions of the external table for data satisfying the one or more predicates; and
assigning, based on the metadata, the plurality of discrete subtasks to one or more nodes in an execution platform.

3. The method of claim 2 , wherein assigning, based on the metadata, the plurality of discrete subtasks further comprises:
reading the metadata to determine whether a first partition is stored in a cache of any of a plurality of nodes of the execution platform; and
assigning, in response to the first partition being stored in the cache of a first node of the execution platform, a task to the first node, the task instructing the first node to scan the first partition.

4. The method of claim 1 , wherein:
the metadata further summarizes data in an internal table that is stored in an internal storage platform that is internal to the database platform; and
the method further comprises:
identifying, based on the metadata, one or more partitions of the internal table to be scanned for data satisfying the one or more predicates;
identifying, from the one or more identified partitions of the internal table, second data satisfying the one or more predicates; and
including the second data in the response to the query.

5. The method of claim 1 , further comprising:
receiving a notification that a modification has been made to the external table; and
refreshing the metadata in response to the modification being made to the external table.

6. The method of claim 1 , further comprising refreshing, in response to a threshold number of modifications being made to the external table, the metadata to reflect the modifications.

7. The method of claim 1 , further comprising storing the metadata in a partition storage object on a shared storage platform associated with the database platform.

8. A database platform comprising:
at least one hardware processor; and
one or more non-transitory computer-readable storage media containing instructions that, when executed by the at least one hardware processor, cause the database platform to perform operations comprising:
receiving a query comprising one or more predicates, the query directed at least to data in an external table that is stored in an external storage platform that is external to the database platform, the external table being based on a source directory of the external storage platform, the source directory comprising a hierarchical structure of storage locations in which data of the external table is stored in the external storage platform, the storage locations defining a plurality of partitions of the external table;
identifying, based on metadata that summarizes the data in the external table, one or more partitions in the plurality of partitions of the external table to be scanned for data satisfying the one or more predicates;
identifying, from the one or more identified partitions, data satisfying the one or more predicates;
generating a materialized view over the external table;
storing the materialized view in an internal storage platform that is internal to the database platform; and
sending a response to the query to the client, the response comprising the data satisfying the one or more predicates, the response further comprising data from the stored materialized view.

9. The database platform of claim 8 , the operations further comprising:
generating a query plan based on the query, the query plan comprising a plurality of discrete subtasks, the plurality of discrete subtasks comprising instructions to scan the identified one or more partitions of the external table for data satisfying the one or more predicates; and
assigning, based on the metadata, the plurality of discrete subtasks to one or more nodes in an execution platform.

10. The database platform of claim 9 , wherein assigning, based on the metadata, the plurality of discrete subtasks further comprises:
reading the metadata to determine whether a first partition is stored in a cache of any of a plurality of nodes of the execution platform; and
assigning, in response to the first partition being stored in the cache of a first node of the execution platform, a task to the first node, the task instructing the first node to scan the first partition.

11. The database platform of claim 8 , wherein:
the metadata further summarizes data in an internal table that is stored in an internal storage platform that is internal to the database platform; and
the operations further comprise:
identifying, based on the metadata, one or more partitions of the internal table to be scanned for data satisfying the one or more predicates;
identifying, from the one or more identified partitions of the internal table, second data satisfying the one or more predicates; and
including the second data in the response to the query.

12. The database platform of claim 8 , the operations further comprising:
receiving a notification that a modification has been made to the external table; and
refreshing the metadata in response to the modification being made to the external table.

13. The database platform of claim 8 , the operations further comprising refreshing, in response to a threshold number of modifications being made to the external table, the metadata to reflect the modifications.

14. The database platform of claim 8 , the operations further comprising storing the metadata in a partition storage object on a shared storage platform associated with the database platform.

15. One or more non-transitory computer-readable storage media containing instructions that, when executed by at least one hardware processor of a database platform, cause the database platform to perform operations comprising:
receiving a query comprising one or more predicates, the query directed at least to data in an external table that is stored in an external storage platform that is external to the database platform, the external table being based on a source directory of the external storage platform, the source directory comprising a hierarchical stru

Assignees

• Snowflake Inc

Inventors

• Muralidhar Subramanian
• Dageville Benoit
• Cruanes Thierry
• Shingte Nileema
• Shah Saurin
• Grabs Torsten
• Cseri Istvan

Classifications

• G06F16/24542

  Plan optimisation · CPC title

• G06F16/24535

  of sub-queries or views · CPC title

• G06F16/2423 Primary

  Interactive query statement specification based on a database schema · CPC title

• G06F3/0605

  by facilitating the interaction with a user or administrator · CPC title

• G06F16/2393 Primary

  Updating materialised views · CPC title