Skip to main content

Edit this page

Hive Partitioning

Examples​

Read data from a Hive partitioned dataset:

Query
SELECT *FROM read_parquet('orders/*/*/*.parquet', hive_partitioning = true);
Result
 id | name  | value----+-------+-------  1 | alpha |    10  2 | beta  |    20  1 | alpha |    10  2 | beta  |    20

Write a table to a Hive partitioned dataset:

Query
COPY ordersTO 'orders' (FORMAT parquet, PARTITION_BY (year, month));

Note that the PARTITION_BY options cannot use expressions. You can produce columns on the fly using the following syntax:

Query
COPY (SELECT *, year(timestamp) AS year, month(timestamp) AS month FROM services)TO 'test' (PARTITION_BY (year, month));

When reading, the partition columns are read from the directory structure and can be included or excluded depending on the hive_partitioning parameter.

Query
FROM read_parquet('test/*/*/*.parquet', hive_partitioning = false);
FROM read_parquet('test/*/*/*.parquet', hive_partitioning = true);
Result
 id | name  | value----+-------+-------  1 | alpha |    10  2 | beta  |    20
 id | name  | value | month | year----+-------+-------+-------+------  1 | alpha |    10 |     1 | 2024  2 | beta  |    20 |     2 | 2024

Hive Partitioning​

Hive partitioning 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.

Query
SELECT *FROM read_parquet('orders/*/*/*.parquet', hive_partitioning = true);
Result
 id | name  | value----+-------+-------  1 | alpha |    10  2 | beta  |    20  1 | alpha |    10  2 | beta  |    20

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:

Query
SELECT *FROM read_parquet('orders/*/*/*.parquet', hive_partitioning = true)WHERE year = 2022  AND month = 11;
Result
 id | name           | value | month | year----+----------------+-------+-------+------  1 | november order |   110 |    11 | 2022

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

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

Auto-detection​

By default the system tries to infer if the provided files are in a hive partitioned hierarchy. And if so, the hive_partitioning flag is enabled automatically. The auto-detection will look at the names of the folders and search for a 'key' = 'value' pattern.

Hive Types​

hive_types is a way to specify the logical types of the hive partitions in a struct:

Query
SELECT *FROM read_parquet(    'dir/**/*.parquet',    hive_partitioning = true,    hive_types = {'release': DATE, 'orders': BIGINT});
Result
 id | name  | value | orders | release----+-------+-------+--------+------------  1 | alpha |    10 |      5 | 2024-01-01  2 | beta  |    20 |      6 | 2024-02-01

hive_types will be auto-detected for the following types: DATE, TIMESTAMP and BIGINT. To switch off the auto-detection, the flag hive_types_autocast = 0 can be set.

Writing Partitioned Files​

See the Partitioned Writes section.