Exporting TPCDS Dataset to S3 using DuckDB

We all need an easy way to performance test while chosing any query engine and TPCDS query sets are the one of the best way to do it. But the problem is, generating data is never an easy way.

In this article we’ll see how to generate TPCDS dataset and load it into S3.

Inspiration:

Albert Wong’s article.

1. Install DuckDB using a simple pip command

python3.10 -m pip install duckdb

2. Run the below commands to launch duckdb and setup the necessary credentials for duckdb to write to S3.

duckdb

SET s3_region='us-west-2';
SET s3_access_key_id='your-access-key';
SET s3_secret_access_key='your-secret-key';

3. Install and load TPCDS dataset in duckdb.

INSTALL tpcds;
LOAD tpcds;

4. Load the tables and get it ready to write using the below syntax:

SELECT * FROM dsdgen(SCALE FACTOR);

Actual command:

SELECT * FROM dsdgen(sf=1);

5. Syntax to export the tables to S3:

EXPORT DATABASE 'TARGET_DIR' (FORMAT, [DELIMITER]);

We can use Parquet as the format too:

EXPORT DATABASE 's3://your-bucket-name/tpcds-output/parquet/1gb/' (FORMAT PARQUET);

That’s it! You can now validate the generated files in S3.

Optional:

To install the DuckDB Command Line Interface (CLI) on Amazon Linux 2023, follow these steps:

Download the DuckDB CLI Binary:

Use the following command to download the binary directly:

wget https://github.com/duckdb/duckdb/releases/download/v0.8.1/duckdb_cli-linux-amd64.zip

Install the unzip Utility (if not already installed):

Amazon Linux 2023 may not come with unzip pre-installed. Install it using:

sudo yum install unzip

Extract the Downloaded Binary:

Unzip the downloaded file:

unzip duckdb_cli-linux-amd64.zip

Make the Binary Executable:

Modify the permissions to make the binary executable:

chmod +x duckdb

Move the Binary to a Directory in Your PATH:

Move the duckdb binary to /usr/local/bin to make it accessible system-wide:

sudo mv duckdb /usr/local/bin/

Verify the Installation:

Run the following command to confirm that DuckDB is installed correctly:

duckdb --version

You should see output indicating the installed DuckDB version.

Note: Ensure that /usr/local/bin is included in your system's PATH. If it's not, you can add it by appending the following line to your ~/.bashrc or ~/.bash_profile and then sourcing the file:

echo 'export PATH=$PATH:/usr/local/bin' >> ~/.bashrc
source ~/.bashrc

By following these steps, you should have the DuckDB CLI installed and ready to use on your Amazon Linux 2023 system.