Replicating Flat Files to MotherDuck

The goal of this guide is to show users simple examples of loading data from flat file sources into MotherDuck. Examples are shown for both the MotherDuck Web UI and the DuckDB CLI. To install the DuckDB CLI, check out the instructions first.

CSV

MotherDuck UI

From the UI, follow these steps:

1. Navigate to the Add Data section.
2. Select the file. This file will be uploaded into your browser so that it can be queried by DuckDB.
3. Execute the generated query which will create a table for you.
   1. Modify the query as needed to suit the correct Database / Schema / Table name.

DuckDB CLI

In the CLI, you can load a CSV file using the read_csv function. For example:

Local File

CREATE TABLE my_table AS 
SELECT * FROM read_csv('path/to/local_file.csv');

S3 File

To load from S3, ensure your DuckDB instance is configured with S3 secrets. Then:

CREATE TABLE my_table AS 
SELECT * FROM read_csv('s3://bucket-name/path-to-file.csv');

JSON

MotherDuck UI

From the UI, follow these steps:

1. Navigate to the Add Data section.
2. Select the file. This file will be uploaded into your browser so that it can be queried by DuckDB.
3. Execute the generated query which will create a table for you.
   1. Modify the query as needed to suit the correct Database / Schema / Table name.

DuckDB CLI

In the CLI, use the read_json function to load JSON files.

Local File

CREATE TABLE my_table AS 
SELECT * FROM read_json('path/to/local_file.json');

S3 File

Make sure S3 support is enabled as described in the S3 secrets documentation.

CREATE TABLE my_table AS 
SELECT * FROM read_json('s3://bucket-name/path-to-file.json');

Parquet

MotherDuck UI

From the UI, follow these steps:

1. Navigate to the Add Data section.
2. Select the file. This file will be uploaded into your browser so that it can be queried by DuckDB.
3. Execute the generated query which will create a table for you.
   1. Modify the query as needed to suit the correct Database / Schema / Table name.

DuckDB CLI

In the CLI, use the read_parquet function to load Parquet files.

Local File

CREATE TABLE my_table AS 
SELECT * FROM read_parquet('path/to/local_file.parquet');

S3 File

Ensure S3 support is enabled as described in the S3 secrets documentation.

CREATE TABLE my_table AS 
SELECT * FROM read_parquet('s3://bucket-name/path-to-file.parquet');

Handling More Complex Workflows

Production use cases tend to be much more complex and include things like incremental builds & state management. In those scenarios, please take a look at our ingestion partners, which includes many options including some that offer native python. An overview of the MotherDuck Ecosystem is shown below.