Snowflake Inc.

Microsoft Technology Licensing, LLC

Yi Fang

Benoit Dageville

Yizhi Zhu

Martin Hentschel

Ashish Motivala

Spyridon Triantafyllis

Subramanian Muralidhar

Jiaqi Yan

Nithin Mahesh

Varun Ganesh

Ryan Michael Thomas Shelly

Lin Chan

Vasileios Papadimos

Yiming Kang

Vikram Wakade

Xinglian Liu

Rishabh Singh Ahluwalia

Philip Shao

Brian Andrew Kirby

Spyros Triantafyllis

Spyros Triantafylis

Described herein are techniques for compact representation of table columns via templatization. Column templates can model the collection of columns in a table as a logical entity. The logical entity can be split into two objects. A first object can contain a subset of column attributes (e.g., fields) that can be shared with other tables, such as name and data type. A second object can contain another subset of column attributes that are unique to a table, such as time stamps and created-on information.

Compact representation of table columns via templatization

Embodiments of the present disclosure describe systems, methods, and computer program products for improving query processing of a database. An example method can include: storing table data for a table in a plurality of micro-partitions, each micro-partition comprising a portion of the table data for the table; for each micro-partition of the plurality of micro-partitions, storing metadata for the micro-partition in at least one of a plurality of expression properties; and selecting, by a processing device, a subset of the plurality of expression properties to be grouped into a grouping expression property based at least partially on the metadata of the subset of the plurality of the expression properties. The grouping expression property may include cumulative metadata associated with the metadata of the subset of the plurality of expression properties.

Metadata clustering

The subject technology receives first metadata corresponding to a set of micro-partitions. The subject technology stores a first data structure and a second data structure in storage as a first file and a second file, first data structure including the first metadata and a second data structure including second metadata, the first metadata corresponding to a set of micro-partitions, the second metadata for a grouping of the first metadata, the second data structure including information associating the second metadata to the first metadata. The subject technology stores third metadata for a table, the third metadata comprising information about data stored in a micro-partition of the table.

Multi-level data for database systems

The subject technology receives first metadata corresponding to a set of micro-partitions. The subject technology stores a first data structure and a second data structure in storage as a first file and a second file, first data structure including the first metadata and a second data structure including second metadata, the first metadata corresponding to a set of micro-partitions, the second metadata for a grouping of the first metadata, the second data structure including information associating the second metadata to the first metadata. The subject technology stores third metadata for a table, the third metadata comprising: cumulative table metadata comprising global information about a plurality of micro-partitions of the table, the cumulative table metadata being stored in a metadata micro-partition associated with the table.

Storing metadata using data structures in database systems

The subject technology generates a first data structure including first metadata and a second data structure including second metadata, the first metadata corresponding to a set of micro-partitions, the second metadata for a grouping of the first metadata, the second data structure including information associating the second metadata to the first metadata. The subject technology stores the first data structure and the second data structure in storage as a first file and a second file. The subject technology stores third metadata for a table, the third metadata comprising: cumulative table metadata comprising global information about a plurality of micro-partitions of the table, the cumulative table metadata being stored in a metadata micro-partition associated with the table.

Generating data structures for storing database metadata

The subject technology stores database data in a table, the table comprising a plurality of micro-partitions each having rows and columns. The subject technology stores metadata for the table in a data structure, the metadata comprising a first level of the data structure comprising a column expression property comprising information about data stored in a column of a micro-partition of the table, a micro-partition expression property comprising information about data stored in the micro-partition of the table, and a second level of the data structure comprising a grouping expression property comprising information about data stored in the micro-partition and one or more additional micro-partitions of the table.

Storing database metadata in multiple levels

Systems, methods, and devices for multiple level metadata organization in database systems. A system includes a plurality of shared storage devices collectively storing database data across a plurality of tables that each comprise one or more micro-partitions constituting immutable storage devices. The system includes a resource manager configured to manage the storage of database data stored in a table across one or more of the plurality of shared storage devices. The resource manager is configured to manage the storage of metadata for the table. The metadata includes a column expression property with information about data stored in a column, a micro-partition expression property with information about data stored in a micro-partition, a grouping expression property with information about data stored in two or more micro-partitions, and cumulative table metadata with global information about all micro-partitions of the table.

Multi-level metadata in database systems

The subject technology receives first metadata corresponding to a set of micro-partitions. The subject technology generates second metadata for a grouping of the first metadata. The subject technology generates a first data structure including the first metadata and a second data structure including the second metadata, the second data structure including information associating the second metadata to the first metadata. The subject technology stores the first data structure and the second data structure in persistent storage as a first file and a second file. The subject technology receives a query on a table. Further, the subject technology analyzes the query against cumulative table metadata to determine whether data stored in the table matches the query.

Storing multi-level metadata in database systems

The subject technology receives first metadata corresponding to a set of micro-partitions. The subject technology generates second metadata for a grouping of the first metadata. The subject technology generates a first data structure including the first metadata and a second data structure including the second metadata, the second data structure including information associating the second metadata to the first metadata. Further, the subject technology stores the first data structure and the second data structure in persistent storage as a first file and a second file.

Systems, methods, and devices for multiple level metadata organization in database systems. A system includes a plurality of shared storage devices collectively storing database data across a plurality of tables that each comprise one or more micro-partitions including immutable storage devices. The system includes a resource manager configured to manage the storage of database data stored in a table across one or more of the plurality of shared storage devices. The resource manager is configured to manage the storage of metadata for the table. The metadata includes a column expression property with information about data stored in a column, a micro-partition expression property with information about data stored in a micro-partition, a grouping expression property with information about data stored in two or more micro-partitions, and cumulative table metadata with global information about all micro-partitions of the table.

A database management system that manages data in a database includes a query optimizer that performs query optimization based on statistics related to data in the database. The query optimizer incrementally updates the statistics. The query optimizer organizes the statistics related to the data in the database into a statistics tree structure having a root node corresponding to global statistics, internal nodes corresponding to summary statistics of descendant nodes, and leaf nodes corresponding to disjoint data ranges in the database. The query optimizer performs statistics tree transformation operations on the statistics tree structure. The transformation operations transform the statistics tree structure into a form that at least partially minimizes the system resources needed to update the statistics. The query optimizer updates the statistics corresponding to those nodes of the statistics tree structure where a change has occurred in the disjoint data ranges.

Growing community of inventors

Kirkland, WA, United States of America

Yi Fang