Fast Data Exports with DuckDB's Partitioned Writes and DuckLake

What's This "Hive Partitioning" Thing Anyway?

Before we dive into the code, let's quickly understand what we're trying to create.

Hive Partitioning is essentially an organizational pattern that structures data files into directories based on column values from specific fields. Imagine you have sales data with year and month columns—instead of one massive file, you'd get a directory structure like:

Copy code
sales_data/
├── year=2023/
│   ├── month=11/
│   │   └── data_0.parquet
│   └── month=12/
│       ├── data_0.parquet
│       └── data_1.parquet
└── year=2024/
    └── month=01/
        └── data_0.parquet

Notice the pattern? column_name=value is used for each directory level. This structure is particularly useful because query engines (including our feathered friend DuckDB) can use these directory names to skip over data that's irrelevant to your query.

If you run a query like WHERE year = 2024 AND month = 1, the engine knows it only needs to look inside the year=2024/month=01/ directory, completely ignoring everything else. This capability, known as filter pushdown or partition pruning, can improve query performance on large datasets, especially on slower storage systems like S3 or HDFS.

While COPY TO ... PARTITION_BY directly creates this physical directory structure based on the partition columns, DuckLake also utilizes partitioning, albeit with a different underlying mechanism. In DuckLake, partitioning keys are defined on a table, and new data is split into separate files based on these keys - the partition keys for a file are stored in the DuckLake metadata catalog as well as the file paths. This allows DuckLake to use the catalog database in addition to the file paths for efficient query planning and pruning.

Writing Partitioned Data with COPY TO

DuckDB makes writing data in this Hive-partitioned format straightforward using the PARTITION_BY clause within the COPY TO statement. The basic concept is telling DuckDB which columns to use for creating the directory structure.

Let's assume we have an orders table in DuckDB that looks like this:

For the examples that follow, assume we've added year and month columns derived from order_date.

Copy code
COPY orders TO 'output_orders_parquet' (
    FORMAT PARQUET,
    PARTITION_BY (year, month)
);

Copy code

COPY orders TO 'output_orders_csv' (
    FORMAT CSV,
    HEADER, -- Include a header row in the CSV files
    PARTITION_BY (year, month),
    OVERWRITE_OR_IGNORE TRUE -- Use with caution!
);

Copy code
COPY orders TO 'output_orders_compressed_csv' (
    FORMAT CSV,
    HEADER,
    PARTITION_BY (year, month),
    COMPRESSION GZIP
);

Copy code
COPY new_orders TO 'output_orders_parquet' (
    FORMAT PARQUET,
    PARTITION_BY (year, month),
    APPEND TRUE
);

Copy code
-- Using an index
COPY orders TO 'output_orders_indexed' (
    FORMAT PARQUET,
    PARTITION_BY (year, month),
    OVERWRITE_OR_IGNORE TRUE,
    FILENAME_PATTERN 'order_data_{i}' -- {i} gets replaced by an index
);

-- Using a UUID for guaranteed unique names
COPY orders TO 'output_orders_uuid' (
    FORMAT PARQUET,
    PARTITION_BY (year, month),
    OVERWRITE_OR_IGNORE TRUE,
    FILENAME_PATTERN 'file_{uuid}' -- {uuid} gets replaced by a unique ID
);

Copy code
COPY (
    SELECT
        *, -- Select all original columns
        year(order_timestamp) AS year, -- Extract year
        month(order_timestamp) AS month -- Extract month
    FROM raw_orders -- Assuming raw_orders has order_timestamp
)
TO 'output_orders_generated_partitions' (
    FORMAT PARQUET,
    PARTITION_BY (year, month), -- Use the newly created columns
    OVERWRITE_OR_IGNORE TRUE
);

Copy code
COPY (
    SELECT
        *, -- Select all original columns
        (rowid/100000)::int as partition_id
    FROM raw_orders -- Assuming raw_orders has order_timestamp
)
TO 'output_orders_generated_partitions' (
    FORMAT PARQUET,
    PARTITION_BY partition_id, -- Use the newly created columns
    OVERWRITE_OR_IGNORE TRUE
);

Partitioned Writes with DuckLake

DuckLake offers a different paradigm for managing partitioned data within a lakehouse context. Instead of relying solely on the physical Hive directory structure, DuckLake uses a dedicated metadata catalog (stored in a SQL database) to manage information about data files and their partitions.

When you define partition keys for a DuckLake table using ALTER TABLE ... SET PARTITIONED BY, DuckLake ensures that new data written to that table is physically split into separate data files based on those keys, similar to Hive partitioning. However, the key difference is that DuckLake stores the mapping between partition values and the corresponding data files in its catalog in addition to the file paths.

This partitioning strategy provides several advantages: improved query planning where query engines can leverage the metadata in the catalog for more efficient partition pruning without needing to list directories or read file footers on the storage layer, partition evolution where you can change the partitioning scheme of a DuckLake table over time and this only affects new data written while previously written data retains its original partitioning, and flexibility where partition keys do not necessarily need to be embedded in the file paths, offering more flexibility in file organization.

Here's how you interact with partitioning in DuckLake:

Copy code
-- Install the ducklake extension 
INSTALL ducklake;
-- Attach to a DuckLake database
ATTACH 'ducklake:my_ducklake.ducklake' AS my_ducklake;
USE my_ducklake;

-- Copy the previous orders table into the ducklake
CREATE TABLE my_ducklake.orders AS SELECT * FROM my_database.orders;

-- Set partitioning keys for the table
ALTER TABLE orders SET PARTITIONED BY (year, month);

-- Subsequent INSERT or COPY operations into 'my_ducklake.orders'
-- will automatically partition data by year and month based on the table definition.

-- Remove partitioning keys
ALTER TABLE orders RESET PARTITIONED BY;

This approach shifts the focus from the physical directory structure as the primary source of partitioning information to the centralized metadata catalog, enabling more dynamic and robust data management.

Encrypted Data with DuckLake

Securing data is important, especially when storing it in a data lake on potentially untrusted storage locations. DuckLake provides a built-in mechanism for encrypting data files written to the data store.

When the encrypted mode is enabled for a DuckLake catalog, all Parquet files created by DuckLake operations are automatically encrypted using Parquet encryption. The encryption keys are not static—DuckLake automatically generates unique encryption keys for each file when it's written. These file-specific encryption keys are then securely stored within the DuckLake metadata catalog itself, in the encryption_key field of the ducklake_data_file table.

When data needs to be read from encrypted files, DuckLake retrieves the corresponding encryption keys from the catalog and automatically uses them to decrypt the files during the read process. This process is transparent to the user, allowing interaction with encrypted DuckLake databases in the same manner as unencrypted ones.

Enabling encryption is done during the initialization of the DuckLake catalog using the ATTACH statement with the ENCRYPTED flag:

Copy code
-- Attach to or create an encrypted DuckLake database
ATTACH 'ducklake:encrypted_mylake.ducklake'
    (DATA_PATH 'untrusted_location/', ENCRYPTED);

-- Now, any data written to tables within 'encrypted_mylake'
-- will be automatically encrypted.

Managing the encryption keys within the trusted metadata catalog database adds an extra layer of security compared to storing keys alongside the data files or relying on external key management systems for this specific function. This feature is a useful advantage of using DuckLake for building a secure data lakehouse.

Performance Considerations & Best Practices

While partitioning and formats like DuckLake can make your data much more navigable for query engines, there are some key considerations to keep in mind:

Copy code
SET partitioned_write_max_open_files = 50; -- Default is 100

Wrapping Up

There you have it—a practical look at how DuckDB's COPY TO ... PARTITION_BY can help you export data into a structured, Hive-partitioned layout, and how DuckLake builds upon the concept of partitioning with advanced features like catalog-managed metadata and data encryption.

While no data organization strategy fits all use cases, partitioning gives you flexibility to match your storage layout to your query patterns. Though, remember the trade-offs: partitioning adds some overhead during writes and works best when your query patterns align with your partition keys. But when used appropriately, both COPY TO PARTITION_BY and DuckLake's partitioning are useful techniques to have in your data engineering arsenal.

So next time you're about to export that massive dataset, consider whether a bit of partitioning (whether via COPY TO or within a DuckLake structure) might make your future self thank you. As we like to say in the data world, "A minute of partitioning saves an hour of querying!"