US 11,960,505 B2Granted

Export data from tables into partitioned folders on an external data lake

AssigneeSnowflake Inc.

InventorsVasile Paraschiv, Saurin Shah, Marianne Shaw, Nileema Shingte

Life of this patent

Estimating the term…

Loading patent…

Abstract

A database export system exports data using a plurality of nodes that process the data to generate structured result files that are partitioned by an export parameter in an export request. The database export system distributes the data and merges the files to avoid small file creation and increase processing speed via parallelism. The database export system generates the result files of a specified maximum size in a final format, where the files are processed merged in a temporary file format. The parallel processing is optimized and constrained per the amount of processing nodes, available memory, requested final file sizes, and operation based ordering to complete data exports in a scalable multi-stage approach.

Claims27

Dependent claims

A method comprising:

identifying a database statement request to export data from a database stored in a network-based data system to an external datastore in export files having a user-specified file size limit, the user-specified file size limit being an individual file size limit for the export files to be exported to the external datastore;
determining, by one or more processors in the network-based data system, that generating the export files having the user-specified file size limit using a first set of threads within respective nodes of the database will exceed available memory for the respective nodes;
in response to determining that the first set of threads will exceed the available memory in generating the export files according to the user-specified file size limit in the database statement request, activating a second set of the threads to export the data, the second set of the threads having fewer threads than the first set of threads and being a subset of the first set of threads;
generating, using the second set of the threads of the one or more processors, the export files according to the user-specified file size limit in the database statement request, generating the export files including:
generating, using the second set of threads within each node, a first set of merge files by performing a table scan, a projection operation, and a partition unload operation to generate the first set of merge files,
generating, using the second set of threads within each node, a second set of merge files including hashing the first set of merge files using a partition key specified in the database statement request based on respective destination partition, and
querying, using the second set of threads within each node, a corresponding portion of merged data in the second set of merge files to generate the export files;
and
transmitting the export files to the external datastore.

6 more elements

A system comprising:

one or more processors;
and
one or more memories storing instructions that, when executed by the one or more processors, cause the one or more processors to perform operations comprising:
identifying a database statement request to export data from a database stored in a network-based data system to an external datastore in export files having a user-specified file size limit, the user-specified file size limit being an individual file size limit for the export files to be exported to the external datastore;
determining, by one or more processors in the network-based data system, that generating the export files having the user-specified file size limit using a first set of threads within respective nodes of the database will exceed available memory for the respective nodes;
in response to determining that the first set of threads will exceed the available memory in generating the export files according to the user-specified file size limit in the database statement request, activating a second set of the threads to export the data, the second set of the threads having fewer threads than the first set of threads and being a subset of the first set of threads;
generating, using the second set of the threads of the one or more processors, the export files according to the user-specified file size limit in the database statement request, generating the export files including:
generating, using the second set of threads within each node, a first set of merge files by performing a table scan, a projection operation, and a partition unload operation to generate the first set of merge files,
generating, using the second set of threads within each node, a second set of merge files including hashing the first set of merge files using a partition key specified in the database statement request based on respective destination partition, and
querying, using the second set of threads within each node, a corresponding portion of merged data in the second set of merge files to generate the export files;
and
transmitting the export files to the external datastore.

9 more elements

A non-transitory computer-storage medium embodying instructions that, when executed by one or more processors, cause the one or more hardware processors to perform operations comprising:

identifying a database statement request to export data from a database stored in a network-based data system to an external datastore in export files having a user-specified file size limit, the user-specified file size limit being an individual file size limit for the export files to be exported to the external datastore;
determining, by one or more processors in the network-based data system, that generating the export files having the user-specified file size limit using a first set of threads within respective nodes of the database will exceed available memory for the respective nodes;
in response to determining that the first set of threads will exceed the available memory in generating the export files according to the user-specified file size limit in the database statement request, activating a second set of the threads to export the data, the second set of the threads having fewer threads than the first set of threads and being a subset of the first set of threads;
generating, using the second set of the threads of the one or more processors, the export files according to the user-specified file size limit in the database statement request, generating the export files including:
generating, using the second set of threads within each node, a first set of merge files by performing a table scan, a projection operation, and a partition unload operation to generate the first set of merge files,
generating, using the second set of threads within each node, a second set of merge files including hashing the first set of merge files using a partition key specified in the database statement request based on respective destination partition, and
querying, using the second set of threads within each node, a corresponding portion of merged data in the second set of merge files to generate the export files;
and
transmitting the export files to the external datastore.

6 more elements

Dependencies are inferred from the claim text.

Facts

Application: 17/664,144Filed 19 May 2022 · continuation
Priority: 15 Oct 2020
Published: US 2022/0121673 A11 Sep 2022
Granted: 16 Apr 2024
Term ends: Not estimatedThe source records don’t support an estimate.

Classification10 codes

G06F 16/278
Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system…: data…
G06F 9/3009
To perform miscellaneous control operations, e.g. NOP: thread control instructions
G06F 16/137
File access structures, e.g. distributed indices: hash-based
G06F 16/182
File system types: distributed file systems

Family

Application63/092,347Us provisional application · filed 15 Oct 2020
Application17/086,221Parent of this continuation · filed 30 Oct 2020
US 2022/0277024 A1Pre-grant publication · published 1 Sep 2022
US 11,354,331 B2Parent of this continuation
US 2022/0121673 A1Published application
US 11,960,505 B2This patent

From the US filing history. Foreign family members aren’t listed.