# US 11,030,191 B2 · Querying over external tables in database systems

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 11,030,191 B2
- Document ID: US11030191B2
- Status: Granted
- Kind: B2
- Application number: 16/841,831
- Filed: 2020-04-07
- Published: 2020-10-22
- Granted: 2021-06-08
- Earliest claimed: 2019-04-16
- Assignees: Snowflake Inc.
- Applicants: Snowflake Inc.
- Inventors: Subramanian Muralidhar; Benoit Dageville; Thierry Cruanes; Nileema Shingte; Saurin Shah; Torsten Grabs; Istvan Cseri
- Web page: https://patentgrep.com/patent/US11030191B2

## CPC

- G06F 16/2358: {Change logging, detection, and notification (replication G06F16/27)}
- G06F 16/2423: {Interactive query statement specification based on a database schema}
- G06F 3/0605: {by facilitating the interaction with a user or administrator}
- G06F 3/0644: {Management of space entities, e.g. partitions, extents, pools}
- G06F 3/067: {Distributed or networked storage systems, e.g. storage area networks [SAN], network attached storage [NAS]}
- G06F 16/2393: {Updating materialised views}
- G06F 16/24535: {of sub-queries or views}
- G06F 16/24542: {Plan optimisation}
- G06F 16/254: {Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

Systems, methods, and devices for querying over an external table are disclosed. A method includes connecting a database platform to an external table such that the database platform has read access for the external table and does not have write access for the external table. The method includes receiving a query comprising a predicate, the query directed at least to data in the external table. The method includes determining, based on metadata, one or more partitions in the external table comprising data satisfying the predicate. The method includes pruning, based on the metadata, all partitions in the external table that do not comprise any data satisfying the predicate. The method includes generating a query plan comprising a plurality of discrete subtasks. The method includes assigning, based on the metadata, the plurality of discrete subtasks to one or more nodes in an execution platform.

## Claims

### Claim 1

A method comprising:
receiving, from a client, by a database platform comprising one or more hardware processors, a query comprising one or more predicates, the query directed at least in part to an external table stored in an external storage platform that is external to the database platform, the external table comprising a plurality of rows and a plurality of columns, data in the external table being organized according to a hierarchical structure of a source directory of the external storage platform, the hierarchical structure comprising subfolders and further comprising leaves corresponding to files in which the data in the external table is arranged, the files being stored in the subfolders;
receiving, from the client, by the database platform, an indication of the hierarchical structure;
generating, by the database platform and based on the received indication of the hierarchical structure, metadata that includes defining different partitions of the external table as corresponding to different ones of the subfolders, the metadata being internal to the database platform, the metadata comprising, for each of the different partitions, ranges of values in at least one column of the plurality of columns;
determining, by the database platform, based on the one or more predicates of the query and based further on the ranges of values in the metadata, that a given partition of the external table comprises data satisfying the one or more predicates of the query;
identifying, based on the metadata, that the given partition of the external table corresponds to a given subfolder in the hierarchical structure;
retrieving, by the database platform, the data satisfying the one or more predicates of the query from the given subfolder corresponding to the given partition; and
responding, by the database platform, to the query at least in part with the retrieved data.

### Claim 2 (depends on 1)

The method of claim 1, wherein:
the database platform has read access to the external table; and
the database platform does not have write access to the external table.

### Claim 3 (depends on 1)

The method of claim 1, wherein:
the metadata further pertains to an internal table of the database platform;
the method further comprises determining, based on the metadata, that the internal table comprises data satisfying the one or more predicates of the query; and
responding to the query comprises responding to the query at least in part with the data from the internal table that satisfies the one or more predicates of the query.

### Claim 4 (depends on 3)

The method of claim 3, wherein:
the data in the external table is in a first file format;
data in the internal table is in a second file format; and
the first file format is different from the second file format.

### Claim 5 (depends on 1)

The method of claim 1, further comprising:
determining that a change has occurred to the external table; and
modifying the metadata to reflect the change.

### Claim 6 (depends on 1)

The method of claim 1, further comprising:
generating a materialized view over the external table; and
storing the materialized view internal to the database platform.

### Claim 7 (depends on 6)

The method of claim 6, wherein generating the materialized view over the external table comprises generating the materialized view over the external table in response to receiving a previous query.

### Claim 8 (depends on 6)

The method of claim 6, further comprising:
determining that a change has occurred to the external table; and
refreshing the materialized view to reflect the change.

### Claim 9

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 at least one hardware processor to perform operations comprising:
receiving a query from a client, the query comprising one or more predicates, the query directed at least in part to an external table stored in an external storage platform that is external to the database platform, the external table comprising a plurality of rows and a plurality of columns, data in the external table being organized according to a hierarchical structure of a source directory of the external storage platform, the hierarchical structure comprising subfolders and further comprising leaves corresponding to files in which the data in the external table is arranged, the files being stored in the subfolders;
receiving, from the client, an indication of the hierarchical structure;
generating, based on the received indication of the hierarchical structure, metadata that includes defining different partitions of the external table as corresponding to different ones of the subfolders, the metadata being internal to the database platform, the metadata comprising, for each of the different partitions, ranges of values in at least one column of the plurality of columns;
determining, based on the one or more predicates of the query and based further on the ranges of values in the metadata, that a given partition of the external table comprises data satisfying the one or more predicates of the query;
identifying, based on the metadata, that the given partition of the external table corresponds to a given subfolder in the hierarchical structure;
retrieving the data satisfying the one or more predicates of the query from the given subfolder corresponding to the given partition; and
responding to the query at least in part with the retrieved data.

### Claim 10 (depends on 9)

The database platform of claim 9, wherein:
the database platform has read access to the external table; and
the database platform does not have write access to the external table.

### Claim 11 (depends on 9)

The database platform of claim 9, wherein:
the metadata further pertains to an internal table of the database platform;
the operations further comprise the database platform determining, based on the metadata, that the internal table comprises data satisfying the one or more predicates of the query; and
responding to the query comprises responding to the query at least in part with the data from the internal table that satisfies the one or more predicates of the query.

### Claim 12 (depends on 11)

The database platform of claim 11, wherein:
the data in the external table is in a first file format;
data in the internal table is in a second file format; and
the first file format is different from the second file format.

### Claim 13 (depends on 9)

The database platform of claim 9, the operations further comprising:
determining that a change has occurred to the external table; and
modifying the metadata to reflect the change.

### Claim 14 (depends on 9)

The database platform of claim 9, the operations further comprising:
generating a materialized view over the external table; and
storing the materialized view internal to the database platform.

### Claim 15 (depends on 14)

The database platform of claim 14, wherein generating the materialized view over the external table comprises generating the materialized view over the external table in response to receiving a previous query.

### Claim 16 (depends on 14)

The database platform of claim 14, the operations further comprising:
determining that a change has occurred to the external table; and
refreshing the materialized view to reflect the change.

### Claim 17

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 at least one hardware processor to perform operations comprising:
receiving a query from a client, the query comprising one or more predicates, the query directed at least in part to an external table stored in an external storage platform that is external to the database platform, the external table comprising a plurality of rows and a plurality of columns, data in the external table being organized according to a hierarchical structure of a source directory of the external storage platform, the hierarchical structure comprising subfolders and further comprising leaves corresponding to files in which the data in the external table is arranged, the files being stored in the subfolders;
receiving, from the client, an indication of the hierarchical structure;
generating based on the received indication of the hierarchical structure, metadata that includes defining different partitions of the external table as corresponding to different ones of the subfolders, the metadata being internal to the database platform, the metadata comprising, for each of the different partitions, ranges of values in at least one column of the plurality of columns;
determining, based on the one or more predicates of the query and based further on the ranges of values in the metadata, that a given partition of the external table comprises data satisfying the one or more predicates of the query;
retrieving the data satisfying the one or more predicates of the query from the given subfolder corresponding to the given partition; and
responding to the query at least in part with the retrieved data.

### Claim 18 (depends on 17)

The non-transitory computer readable storage media of claim 17, wherein:
the database platform has read access to the external table; and
the database platform does not have write access to the external table.

### Claim 19 (depends on 17)

The non-transitory computer readable storage media of claim 17, wherein:
the metadata further pertains to an internal table of the database platform;
the operations further comprise the database platform determining, based on the metadata, that the internal table comprises data satisfying the one or more predicates of the query; and
responding to the query comprises responding to the query at least in part with the data from the internal table that satisfies the one or more predicates of the query.

### Claim 20 (depends on 19)

The non-transitory computer readable storage media of claim 19, wherein:
the data in the external table is in a first file format;
data in the internal table is in a second file format; and
the first file format is different from the second file format.

### Claim 21 (depends on 17)

The non-transitory computer readable storage media of claim 17, the operations further comprising:
determining that a change has occurred to the external table; and
modifying the metadata to reflect the change.

### Claim 22 (depends on 17)

The non-transitory computer readable storage media of claim 17, the operations further comprising:
generating a materialized view over the external table; and
storing the materialized view internal to the database platform.

### Claim 23 (depends on 22)

The non-transitory computer readable storage media of claim 22, wherein generating the materialized view over the external table comprises generating the materialized view over the external table in response to receiving a previous query.

### Claim 24 (depends on 22)

The non-transitory computer readable storage media of claim 22, the operations further comprising:
determining that a change has occurred to the external table; and
refreshing the materialized view to reflect the change.

### Claim 25 (depends on 1)

The method of claim 1, wherein the metadata reflects the contents of comprises expression properties for the different partitions of the external table.

### Claim 26 (depends on 5)

The method of claim 5, wherein the change comprises an addition of a file to a subfolder in the source directory, a deletion of a file from a subfolder in the source directory, or an update to a file in a subfolder in the source directory.

### Claim 27 (depends on 9)

The database platform of claim 9, wherein the metadata comprises expression properties for the different partitions of the external table.

### Claim 28 (depends on 15)

The database platform of claim 15, wherein the change comprises an addition of a file to a subfolder in the source directory, a deletion of a file from a subfolder in the source directory, or an update to a file in a subfolder in the source directory.

### Claim 29 (depends on 17)

The non-transitory computer readable storage media of claim 17, wherein the metadata comprises expression properties for the different partitions of the external table.

### Claim 30 (depends on 21)

The non-transitory computer readable storage media of claim 21, wherein the change comprises an addition of a file to a subfolder in the source directory, a deletion of a file from a subfolder in the source directory, or an update to a file in a subfolder in the source directory.