公开(公告)号：US11347714B2

公开(公告)日：2022-05-31

申请号：US16182112

申请日：2018-11-06

Applicant: Snowflake Inc.

Inventor： Istvan Cseri ,  Torsten Grabs ,  Benoit Dageville

IPC: G06F17/30 ,  G06F16/23 ,  G06F16/27 ,  G06F16/2455

Abstract: Systems, methods, and devices for tracking changes to database data. A method includes determining a change to be executed on a micro-partition of a table of a database and executing the change on the table by generating a new micro-partition that embodies the change. The method includes updating a table history that includes a log of changes made to the table, wherein each change in the log of changes includes a timestamp, and wherein updating the table history includes inserting the change into the log of changes.

公开(公告)号：US20220075776A1

公开(公告)日：2022-03-10

申请号：US17455798

申请日：2021-11-19

Applicant: Snowflake Inc.

Inventor： Subramanian Muralidhar ,  Benoit Dageville ,  Thierry Cruanes ,  Nileema Shingte ,  Saurin Shah ,  Torsten Grabs ,  Istvan Cseri

IPC: G06F16/242 ,  G06F3/06 ,  G06F16/23 ,  G06F16/16 ,  G06F16/25 ,  G06F9/54 ,  G06F16/22

Abstract: Disclosed herein are systems and methods for pruning external data. In an embodiment, a database platform receives a query directed at least in part to external data in an external table on an external data storage platform. The external table includes partitions that correspond to storage locations in a source directory of the external data storage platform. The storage locations contain files that contain the external data. The database platform identifies, from external-table metadata that is stored by the database platform and that maps the partitions of the external table to the storage locations in the source directory, a subset of the partitions as including data that potentially satisfies the query. The database platform identifies data that satisfies the query by scanning the identified subset of the partitions, and responds to the query at least in part with the identified data that satisfies the query.

公开(公告)号：US11269868B2

公开(公告)日：2022-03-08

申请号：US17219854

申请日：2021-03-31

Applicant: Snowflake Inc.

Inventor： Subramanian Muralidhar ,  Benoit Dageville ,  Thierry Cruanes ,  Nileema Shingte ,  Saurin Shah ,  Torsten Grabs ,  Istvan Cseri

IPC: G06F16/00 ,  G06F16/242 ,  G06F3/06 ,  G06F16/23 ,  G06F16/16 ,  G06F16/25 ,  G06F9/54 ,  G06F16/22

Abstract: Systems, methods, and devices for automated maintenance of external tables in database systems are disclosed. A method includes receiving, by a database platform, read access to content in an external data storage platform that is separate from the database platform. The method includes defining an external table based on the content in the external data storage platform. The method includes connecting the database platform to the 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 generating metadata for the external table, the metadata comprising information about data stored in the external table. The method includes receiving a notification that a modification has been made to the content in the external data storage platform, the modification comprising one or more of an addition of a file, a deletion of a file, or an update to a file in a source location for the external table. The method includes refreshing the metadata for the external table in response to the modification being made to the content in the external data storage platform.

公开(公告)号：US11106661B2

公开(公告)日：2021-08-31

申请号：US17219600

申请日：2021-03-31

Applicant: Snowflake Inc.

Inventor： Torsten Grabs ,  Istvan Cseri ,  Benoit Dageville

IPC: G06F17/00 ,  G06F16/23 ,  G06F16/22 ,  G06F16/2455

Abstract: Systems, methods, and devices for tracking a series of changes to database data are disclosed. A method includes executing a transaction to modify data in a micro-partition of a table of a database by generating a new micro-partition that embodies the transaction. The method includes associating transaction data with the new micro-partition, wherein the transaction data comprises a timestamp when the transaction was fully executed, and further includes associating modification data with the new micro-partition that comprises an indication of one or more rows of the table that were modified by the transaction. The method includes joining the transaction data with the modification data to generate joined data and querying the joined data to determine a listing of intermediate modifications made to the table between a first timestamp and a second timestamp.

公开(公告)号：US20210248127A1

公开(公告)日：2021-08-12

申请号：US17232927

申请日：2021-04-16

Applicant: Snowflake Inc.

Inventor： Istvan Cseri ,  Torsten Grabs ,  Benoit Dageville

IPC: G06F16/23 ,  G06F16/27 ,  G06F16/2455

Abstract: The subject technology executes a change on an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition. The subject technology receives a request for a delta for the table between a first timestamp and a second timestamp. The subject technology responsively determines the delta based on a table history, the delta including information indicating at least one operation that was performed to at least one row of a set of rows of the table, without including information as to intermediate changes made to at least one row of the set of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the set of rows of the table.

公开(公告)号：US20210182268A1

公开(公告)日：2021-06-17

申请号：US17162790

申请日：2021-01-29

Applicant: Snowflake Inc.

Inventor： Istvan Cseri ,  Torsten Grabs ,  Benoit Dageville

IPC: G06F16/23 ,  G06F16/27 ,  G06F16/2455

Abstract: The subject technology executes a change on an existing micro-partition of a table of a database, the executing of the change comprising generating a new micro-partition that embodies the change. The subject technology receives a request for a delta for the table between a first timestamp and a second timestamp. The subject technology queries at least one change tracking column to determine the delta between the first timestamp and the second timestamp, the delta including information indicating at least one database operation that was performed to at least one row of a set of rows of the table, without including information as to intermediate changes made to at least one row of the set of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the set of rows of the table.

公开(公告)号：US11030191B2

公开(公告)日：2021-06-08

申请号：US16841831

申请日：2020-04-07

Applicant: Snowflake Inc.

Inventor： Subramanian Muralidhar ,  Benoit Dageville ,  Thierry Cruanes ,  Nileema Shingte ,  Saurin Shah ,  Torsten Grabs ,  Istvan Cseri

IPC: G06F16/242 ,  G06F3/06 ,  G06F16/2453 ,  G06F16/25 ,  G06F16/23

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.

公开(公告)号：US20200183908A1

公开(公告)日：2020-06-11

申请号：US16213554

申请日：2018-12-07

Applicant: Snowflake Inc.

Inventor： Subramanian Muralidhar ,  Istvan Cseri ,  Torsten Grabs ,  Benoit Dageville

IPC: G06F16/23 ,  G06F16/27 ,  G06F16/18 ,  G06F16/245

Abstract: Systems, methods, and devices for generating a transactional change tracking summary for a database. A method includes executing a transaction on a table of a database, wherein the table includes a micro-partition and the transaction is executed on the micro-partition. The method includes, in response to the transaction being fully executed, generating a change tracking entry comprising an indication of one or modifications made to the table by the transaction. The method includes storing the change tracking entry in a change tracking stream, wherein the change tracking stream includes one or more sequential change tracking entries that each correspond to a different transaction that is fully executed on the table.

公开(公告)号：US11928098B2

公开(公告)日：2024-03-12

申请号：US17809203

申请日：2022-06-27

Applicant: Snowflake Inc.

Inventor： Subramanian Muralidhar ,  Istvan Cseri ,  Torsten Grabs ,  Benoit Dageville

IPC: G06F16/23 ,  G06F16/18 ,  G06F16/245 ,  G06F16/27 ,  G06F9/46

CPC classification number: G06F16/2358 ,  G06F16/1865 ,  G06F16/2322 ,  G06F16/2365 ,  G06F16/245 ,  G06F16/278 ,  G06F9/466

Abstract: A system includes one or more processors and data storage containing instructions executable by the one or more processors to perform operations. The operations include detecting a first executed transaction causing a first modification to table data stored in a table. The table data is associated with a corresponding metadata file with metadata information of the table. A new metadata file is generated responsive to the first executed transaction. The new metadata file includes the metadata information and additional metadata associated with the first modification. A second executed transaction causing a second modification to the table data is detected. The table data is updated with a change tracking column. The change tracking column includes lineage of executed transactions on the table data. The lineage indicates at least the first transaction and the second transaction.

公开(公告)号：US11762838B2

公开(公告)日：2023-09-19

申请号：US18162513

申请日：2023-01-31

Applicant: Snowflake Inc.

Inventor： Subramanian Muralidhar ,  Istvan Cseri ,  Torsten Grabs ,  Benoit Dageville

IPC: G06F16/23 ,  G06F16/18 ,  G06F16/245 ,  G06F16/27 ,  G06F9/46

CPC classification number: G06F16/2358 ,  G06F16/1865 ,  G06F16/2322 ,  G06F16/2365 ,  G06F16/245 ,  G06F16/278 ,  G06F9/466

Abstract: A method for table data processing includes retrieving, by at least one hardware processor, first table data associated with a first table version from a plurality of partitions of a storage device. The method further includes detecting, by the at least one hardware processor, an executed transaction causing a modification to the first table data resulting in a second table data associated with a second table version. The second table data is modified to include partition metadata. The partition metadata includes table version information with a mapping of the first table version and the second table version to a plurality of transactions associated with generating the first table data and the second table data.