Examples

-- read data from a hive partitioned data set
SELECT * FROM parquet_scan('orders/*/*/*.parquet', hive_partitioning=1);
-- write a table to a hive partitioned data set
COPY orders TO 'orders' (FORMAT PARQUET, PARTITION_BY (year, month));

Hive Partitioning

Hive partitionining is a partitioning strategy that is used to split a table into multiple files based on partition keys. The files are organized into folders. Within each folder, the partition key has a value that is determined by the name of the folder.

Below is an example of a hive partitioned file hierarchy. The files are partitioned on two keys (year and month).

orders
├── year=2021
│    ├── month=1
│    │   ├── file1.parquet
│    │   └── file2.parquet
│    └── month=2
│        └── file3.parquet
└── year=2022
     ├── month=11
     │   ├── file4.parquet
     │   └── file5.parquet
     └── month=12
         └── file6.parquet

Files stored in this hierarchy can be read using the hive_partitioning flag.

SELECT * FROM parquet_scan('orders/*/*/*.parquet', hive_partitioning=1);

When we specify the hive_partitioning flag, the values of the columns will be read from the directories.

Filter Pushdown

Filters on the partition keys are automatically pushed down into the files. This way the system skips reading files that are not necessary to answer a query. For example, consider the following query on the above dataset:

SELECT *
FROM parquet_scan('orders/*/*/*.parquet', hive_partitioning=1)
WHERE year=2022 AND month=11;

When executing this query, only the following files will be read:

orders
└── year=2022
     └── month=11
         ├── file4.parquet
         └── file5.parquet

Writing Partitioned Files

See the Partitioned Writes section.