page132
115
Snowflake’s internal, compressed, columnar format is not explained in detail.
However, for the curious, I believe Snowflake utilizes the Partition Attributes Across
(PAX) file format; you can find the whitepaper at
https://research.cs.wisc.edu/
multifacet/papers/vldb01_pax.pdf
.
Accessing Table Metadata
As you progress through this section, your investigation will identify programmatic
approaches to determining the number of micro-partitions for a table. I do not offer a
fully functional stored procedure-based approach but instead illustrate various methods
to identify information of interest.
Using the Information Schema
Note that the information schema views are specific to an individual database. The next
query illustrates the available information for tables:
SELECT *
FROM tpc.information_schema.tables
WHERE table_name LIKE '%_BASELINE%'
LIMIT 10;
There is no latency for information schema views, and dropped object information is
not available.
The attribute
clustering_key
is
NULL
for tables declared without an explicit
cluster key, discussed in detail within the next chapter.
Alternatively, you can use the
SHOW
command as this next SQL statement illustrates:
SHOW TABLES LIKE '%_BASELINE%';
Then convert the output to usable output by modifying the next SQL statement
according to your needs:
SELECT "name",
"rows",
"automatic_clustering"
FROM TABLE ( RESULT_SCAN ( last_query_id()));
ChaPTer 4
MiCro-ParTiTionS