---
title: "DuckDB Tutorial For Beginners"
canonical: "https://motherduck.com/blog/duckdb-tutorial-for-beginners/"
related:
  - title: "DuckDB Tutorial For Beginners | MotherDuck"
    url: "https://motherduck.com/videos/duckdb-tutorial-for-beginners/"
  - title: "MotherDuck Tutorial | MotherDuck Docs"
    url: "https://motherduck.com/docs/getting-started/e2e-tutorial/"
  - title: "Congratulations to DuckDB Labs On Reaching 1.0!"
    url: "https://motherduck.com/blog/motherduck-congratulates-duckdb-1.0-release/"
---

# DuckDB Tutorial For Beginners

<style type="text/css">
.admonition {
    padding: 5px 5px 5px 50px; /* Top, right, bottom, left padding */
    margin: 5px 0px 15px 0px;
    border-radius: 5px;
    position: relative;
    box-sizing: border-box; /* Ensures padding is included in the element's total width and height */
}
.admonition p {
    margin: 0; /* Removes default margin */
    padding: 0; /* Ensures there's no padding adding extra space */
}

.admonition-title {
    font-weight: bold;
    font-size: 1em;
}

.admonition-icon {
    position: absolute;
    top: 10px; /* Adjusts vertical alignment */
    left: 10px; /* Adjusts horizontal alignment */
    width: 26px; /* Icon size */
    height: 26px; /* Icon size */
}
.admonition ol {
    counter-reset: section; /* Resets the numbering */
    list-style-type: none; /* Removes default numbering */
}

.admonition li {
    counter-increment: section; /* Increases the counter */
    margin-bottom: 10px; /* Adds space between list items */
}

.admonition li:before {
    content: counter(section) ". "; /* Adds custom numbering */
    font-weight: bold; /* Makes the number bold */
}

.tip {
    background-color: #fff3cd; /* Light yellow background */
    border: 1px solid #ffeeba;
    color: #856404; /* Darker text for better contrast */
}

.tip .admonition-icon {
    background-image: url('https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/light_bulb_b55003f6a4.svg'); /* Path to your light bubble icon */
    background-size: cover;
    
}

.info {
    background-color: #d1ecf1; /* Light blue background */
    border: 1px solid #bee5eb;
    color: #0c5460; /* Dark blue text for visibility */
}

.info .admonition-icon {
    background-image: url('https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/info_b09d9ce06f.svg'); /* Path to your information icon */
    background-size: cover;
}

.footnote-ref {
    vertical-align: super;
    padding-left: 2px;
    font-size: smaller; /* makes the number smaller and keeps it inline as superscript */
    font-weight: bold; /* Makes the superscript bold */
    text-decoration: underline; /* Adds underline */
}

.footnote {
    border-top: 1px solid #ccc;
    padding-top: 5px;
    margin-top: 10px;
    font-size: 0.9em;
}

.footnote p {
    margin-bottom: 0;
}

.footnote sup {
    vertical-align: super;
    font-size: smaller;
    font-weight: bold; /* Makes the superscript bold */
    text-decoration: underline; /* Adds underline */
}
</style>

If you haven't had the chance to get up to speed with DuckDB, this tutorial is for you! We'll go over the essentials, from installation to workflow, getting to know the command-line interface (CLI), and diving into your first analytics project. If are too lazy to read, I also made a video for this tutorial.

<iframe width="560" height="315" src="https://www.youtube.com/embed/ZX5FdqzGT1E" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen style="margin-bottom: 15px"></iframe>

Let's start quacking some code!

<div class="admonition info">
    <div class="admonition-icon"></div>
    <p>
        Editor's note: this tutorial was originally published 2023-04-26 by Mehdi and has been updated by Ryan to reflect advancements in DuckDB.
    </p>
</div>


## What is DuckDB?

DuckDB is an in-process SQL [OLAP](/learn/what-is-OLAP/) database, which means it is a database optimized for analytics, often structured as a [Star Schema](https://motherduck.com/learn/star-schema-data-warehouse-guide/) and runs within the same process as the application using it. This unique feature allows DuckDB to offer the advantages of a database without the complexities of managing one. But, as with any software concept, the best way to learn is to dive in and get your hands dirty.

We'll be showing examples using the DuckDB command-line client (CLI), but you can also use DuckDB from within Python, R, and other languages, or any tool supporting JDBC or ODBC drivers.  There is a community-contributed selection of example queries and code for many of these languages on the [DuckDB Snippets](https://duckdbsnippets.com/) website.

*In the below snippets, any code example prefixed with `$` means that it's a bash command. Otherwise we assume that these would run within a DuckDB process, which uses a `D` prompt.*

## How to Install DuckDB

Installing DuckDB is a breeze. Visit the [DuckDB documentation](https://duckdb.org/docs/installation/index) and download the binary for your operating system. 

For MacOS and Windows users, you can leverage package managers to make the DuckDB CLI directly available in your PATH, simplifying upgrades and installations.

To install DuckDB on MacOS using Homebrew, run the following command:

```bash
$ brew install duckdb
```

To install DuckDB on Windows using winget, run the following command:

```bash
C:\> winget install DuckDB.cli
```

You can now launch DuckDB by simply calling the `duckdb` CLI command.

```jsx
$ duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D 
```

## Workflow with VSCode

To follow along with our exploration of DuckDB, check out this [GitHub repository](https://github.com/mehd-io/duckdb-playground-tutorial). I recommend working with an editor, a SQL file, and sending commands to the terminal for a lightweight setup. This approach offers visibility on all commands, enables you to safely version control them, and allows you to leverage formatting tools and AI friends like Copilot.

In our example, we'll use Visual Studio Code (VSCode). To configure a custom shortcut to send commands from the editor to the terminal, open the keyboard shortcuts JSON file and add a key binding to the following command : 

```jsx
{
    "key": "shift+enter",
    "command": "workbench.action.terminal.runSelectedText"
}
```

Of course, this workflow can be pretty easily replicated with any editor or IDE!

### Data Persistence with DuckDB: Overview

By default, DuckDB is an in-memory process and won't persist any data. To demonstrate this, let's create a simple table based on a query result:

```
$ duckdb
D CREATE TABLE ducks AS SELECT 3 AS age, 'mandarin' AS breed;
FROM ducks;
┌───────┬──────────┐
│  age  │  breed   │
│ int32 │ varchar  │
├───────┼──────────┤
│     3 │ mandarin │
└───────┴──────────┘
```

This query creates and populates a "ducks" table.   However, if we exit the CLI and reopen it, the table will be gone. 

<div class="admonition info">
    <div class="admonition-icon"></div>
    <p>
      Unlike standard SQL, the query above uses the <code>FROM</code> statement without any <code>SELECT *</code> statement. This is a neat shortcut in DuckDB and there are plenty more <a href="https://duckdb.org/2022/05/04/friendlier-sql.html">DuckDB SQL shortcuts</a>!
      </p>
</div>

### Data Persistence with DuckDB: Creating a Database

To persist data, you have two options:

1. Provide a path to a database file when starting DuckDB. The file can have any extension, but common choices are `.db`, `.duckdb`, or `.ddb`. If no database exists at the specified path, DuckDB will create one. 
    
    ```bash
    $ duckdb /data/myawesomedb.db
    ```

    You can also launch DuckDB with a database in read-only mode to avoid modifying the database:
    
    ```bash
    $ duckdb -readonly /data/myawesomedb.db
    ```
    
2. If DuckDB is already running, use the `attach` command to connect to a database at the specified file path.
    
    ```
    ATTACH DATABASE '/path/to/your/database.db' AS mydb;
    ```
    

The database file uses DuckDB's custom single-file format (all tables are included), which supports transactional [ACID compliance](/learn/acid-transactions-sql/) and stores data in a compressed [columnar format](/learn/columnar-storage-guide/) for optimal aggregation performance.  DuckDB is [regularly adding](https://duckdb.org/2022/10/28/lightweight-compression.html) new compression algorithms to improve performance.  

While the DuckDB team often improves the file format with new releases, it is [backward compatible](https://duckdb.org/docs/internals/storage.html) as of DuckDB 1.0, meaning that new releases are able to read files produced by early releases of DuckDB.

If you use MotherDuck as your cloud data warehouse, it automatically manages the DuckDB databases for you, so you create a MotherDuck database using the familiar [`CREATE DATABASE`](/docs/sql-reference/motherduck-sql-reference/create-database/) SQL statement.

## Reading and Displaying Data

Next, let's explore reading and writing data in CSV and [Parquet](/learn/why-choose-parquet-table-file-format/) formats. We'll use a small dataset from Kaggle containing daily Netflix Top 10 Movie/TV Show data for the United States from 2020 to March 2022.

To load the CSV dataset, use the [read_csv_auto](https://duckdb.org/docs/data/csv/overview#read_csv_auto-function) command, which infers the schema and detects the delimiter. You can also use the `read_csv` command and pass the schema and delimiter as parameters.

```
SELECT * FROM read_csv_auto('path/to/your/file.csv');
```

When you use this command, the dataset is read, but an actual table is not created in your DuckDB database. To create a table, use a `CREATE TABLE x AS` (CTAS) statement:

```
CREATE TABLE netflix_top10 AS SELECT * FROM read_csv_auto('path/to/your/file.csv');
```

To write data to a CSV file, use the `COPY` command and specify the delimiter. For Parquet files, simply specify the file format:

```
COPY ./data/netflix_top10.csv TO 'path/to/your/output/file.csv' WITH (FORMAT 'CSV', DELIMITER ',');
COPY ./data/netflix_top10.csv TO 'path/to/your/output/file.parquet' WITH (FORMAT 'PARQUET');
```

To read data from a Parquet file, use the `read_parquet` command:

```
SELECT * FROM read_parquet('path/to/your/file.parquet');
```

DuckDB supports a wide variety of different file formats, including the native DuckDB database file used above, CSV, [JSON](/blog/analyze-json-data-using-sql/), Parquet, [Iceberg](/docs/integrations/file-formats/apache-iceberg/), [Delta Lake](/docs/integrations/file-formats/delta-lake/) and more. You can read these files from your local filesystem, a http endpoint or a cloud blob store like AWS S3, Cloudflare R2, Azure Blob Storage or Google Cloud Storage.


## Display Modes, Output Options

DuckDB CLI offers various ways to enhance your experience by customizing the data display and  output options. 

You can use the `.mode` command to change the appearance of tables returned in the terminal output. For instance, if you are dealing with long nested JSON, you can change the mode to `line` or `JSON` to have a better view of your data.

```jsx
.mode line
SELECT * FROM './data/sales.json';
sales_data = [{'order_id': 1, 'customer': {'id': 101, 'name': John Doe, 'email': john.doe@example.com}, 'items': [{'product_id': 301, 'product_name': Laptop, 'quantity': 1, 'price': 1200}, {'product_id': 302, 'product_name': Mouse, 'quantity': 1, 'price': 25}], 'total_amount': 1225, 'date': 2023-03-24}, {'order_id': 2, 'customer': {'id': 102, 'name': Jane Smith, 'email': jane.smith@example.com}, 'items': [{'product_id': 303, 'product_name': Keyboard, 'quantity': 1, 'price': 50}, {'product_id': 304, 'product_name': Monitor, 'quantity': 1, 'price': 200}], 'total_amount': 250, 'date': 2023-03-25}]
```

Next to that, you can output elsewhere the data by redirecting the terminal output to a file.

Let's say you would like to output the result to a Markdown file, you can set the display mode to Markdown with `.mode markdown`. Combine this with the `.output` or `.once` command to write the result directly to a specific file. The `.output` command writes all the output of the different results you run, while `.once` does it just once.

```
.mode markdown
.output myfile.md
```

## Running Commands and Exiting

DuckDB CLI allows you to run a SQL statement and exit using the `-c` option parameter. For example, if you use a `SELECT` statement to read a Parquet file:

```jsx
$ duckdb -c "SELECT * FROM read_parquet('path/to/your/file.parquet');"
```

This feature is lightweight, fast, and easy. You can even build your own [bash functions](https://duckdbsnippets.com/snippets/6/quickly-convert-a-csv-to-parquet-bash-function) using the DuckDB CLI for various operations on CSV/Parquet files, such as converting a CSV to Parquet.

DuckDB also offers flags for configuration that you can fine-tune, such as setting the thread count, memory limits, ordering of null values and more. You can find the full list of flag options and their current values from the `duckdb_settings()` table function.

```
FROM duckdb_settings();
```

## Working with Extensions

Extensions are like packages that you can install within DuckDB to enjoy specific feature. DuckDB supports a number of core extensions. Not all are included by default, but DuckDB has a mechanism for remote extension installation. To view the available core extensions, execute the following statement:

```
FROM duckdb_extensions();
```

To install an extension, such as the popular `httpfs` extension that allows reading/writing remote files over HTTPS and S3, use the `INSTALL` command followed by the extension name. Once installed, DuckDB downloads the extension to the `$HOME/.duckdb/` folder (modifiable by setting the `extension_directory` parameter).

Next, load the extension in the DuckDB process with the `LOAD` command.

```
INSTALL httpfs;
LOAD httpfs;
```

<div class="admonition info">
    <div class="admonition-icon"></div>
    <p>
        DuckDB supports autoloading of the core extensions, so you often do not need to manually load these.  As an example. if you read from a CSV file with a `https://` scheme, the `httpfs` extension will be autoloaded.
    </p>
</div>


If you're using a third-party extension or your own extension not bundled by default, set the `allow_unsigned_extensions` flag to `True`, or use the `-unsigned` flag parameter when launching DuckDB.

```jsx
$ duckdb -unsigned
```

Extensions are powerful and versatile. You can create your own using the [template](https://github.com/duckdb/extension-template) provided by the DuckDB Labs team to kickstart your extension development journey.

There is now a [Community Extensions repository](https://duckdb.org/docs/extensions/community_extensions.html) for you to share any custom extensions with the wider DuckDB community for easy installation. 

## First analytics project

We have the mentioned Netflix dataset hosted on a public AWS S3 bucket. In this simple project, we will answer the most existential question : what were people in the US binge-watching during the COVID lockdown?

As the data is sitting on AWS S3, we'll start by installing the extension httpfs.

```jsx
-- Install extensions
INSTALL httpfs;
LOAD httpfs;
-- Minimum configuration for loading S3 dataset if the bucket is public
SET s3_region='us-east-1';
```

We can now read our dataset : 

```jsx
D CREATE TABLE netflix AS SELECT * FROM read_parquet('s3://us-prd-motherduck-open-datasets/netflix/netflix_daily_top_10.parquet');
FROM netflix;
┌────────────┬───────┬───────────────────┬───┬────────────────┬──────────────────┐
│   As of    │ Rank  │ Year to Date Rank │ … │ Days In Top 10 │ Viewership Score │
│    date    │ int64 │      varchar      │   │     int64      │      int64       │
├────────────┼───────┼───────────────────┼───┼────────────────┼──────────────────┤
│ 2020-04-01 │     1 │ 1                 │ … │              9 │               90 │
│ 2020-04-01 │     2 │ 2                 │ … │              5 │               45 │
│ 2020-04-01 │     3 │ 3                 │ … │              9 │               76 │
│ 2020-04-01 │     4 │ 4                 │ … │              5 │               30 │
│ 2020-04-01 │     5 │ 5                 │ … │              9 │               55 │
│ 2020-04-01 │     6 │ 6                 │ … │              4 │               14 │
```

Finally, getting the top watched movies as follow : 

```jsx
-- Display the most popular TV Shows
SELECT Title, max("Days In Top 10") from netflix
where Type='Movie'
GROUP BY Title
ORDER BY max("Days In Top 10") desc
limit 5;
┌────────────────────────────────┬───────────────────────┐
│             Title              │ max("Days In Top 10") │
│            varchar             │         int64         │
├────────────────────────────────┼───────────────────────┤
│ The Mitchells vs. The Machines │                    31 │
│ How the Grinch Stole Christmas │                    29 │
│ Vivo                           │                    29 │
│ 365 Days                       │                    28 │
│ Despicable Me 2                │                    27 │
└────────────────────────────────┴───────────────────────┘

-- Copy the result to CSV
COPY (
SELECT Title, max("Days In Top 10") from netflix
where Type='TV Show'
GROUP BY Title
ORDER BY max("Days In Top 10") desc
limit 5
) TO 'output.csv' (HEADER, DELIMITER ',');
```

What's fun is that for both Movies and TV shows, the top 5 mostly include kids show. We all know that kids doesn't bother to see multiple time the same thing…

<div class="admonition info">
    <div class="admonition-icon"></div>
    <p>DuckDB and MotherDuck also support accessing private S3 buckets by using <a href="/docs/integrations/cloud-storage/amazon-s3/"><code>CREATE SECRET</code></a> to specify and store your credentials.</p>
</div>

## Exploring Beyond the Pond

That's it for this tutorial! If you're interested in delving deeper into DuckDB, check out these resources:

- The official DuckDB docs : [https://duckdb.org/](https://duckdb.org/)
- The DuckDB discord : [https://discord.com/invite/tcvwpjfnZx](https://discord.com/invite/tcvwpjfnZx)

To elevate your experience with DuckDB and scale it with a cloud data warehouse, explore [MotherDuck](https://motherduck.com/product/)! Dive into our [end-to-end tutorial](https://motherduck.com/docs/getting-started/e2e-tutorial) to discover the user-friendly web interface, AI-based SQL query fixing, global and organization-wide data sharing capabilities, and more.

Additionally, stay tuned to our [monthly newsletter](/duckdb-news/) and [YouTube channel](https://youtube.com/@motherduckdb/), where we'll continue to share more DuckDB-related content!

## What's Next?

Ready to go deeper? Here are some recommended next steps:

**Watch:** For a more comprehensive walkthrough, check out our [36-minute DuckDB & MotherDuck for Beginners video](/videos/duckdb-motherduck-for-beginners-your-ultimate-guide/) that covers everything from setup to advanced features.

**Learn more:**
- [DuckDB vs SQLite: Choosing the Right Database](/learn/duckdb-vs-sqlite-databases/) - Understand when to use each database
- [Working with Pandas DataFrames and DuckDB](/learn/pandas-dataframes-guide/) - Integrate DuckDB into your Python workflows

**Read:**
- [DuckDB in Enterprise: 5 Key Categories](/blog/duckdb-enterprise-5-key-categories/) - How organizations are using DuckDB at scale
- [Faster Data Pipelines with MCP, DuckDB, and AI](/blog/faster-data-pipelines-with-mcp-duckdb-ai/) - Build modern data pipelines

Keep quacking, keep coding.