# Extract data from Databases into DuckLake

# Extract data from Databases into DuckLake

In the ever-evolving landscape of data engineering, the tools we use are constantly getting better, faster, and more efficient. DuckLake is one such innovation, building on the phenomenal success of DuckDB to offer a robust, ACID-compliant data lake format. It's designed for scalability and flexibility, supporting various backends for both its catalog and data storage. 

But how do you get your data *into* DuckLake? Whether your data lives in a production PostgreSQL database, a MySQL instance, or any other database, you need a simple and powerful way to extract and load it.

This is where Sling comes in. Sling is a modern data movement tool designed to make transferring data between different sources and destinations as easy as possible. In this article, we'll walk you through how to use Sling to extract data from most databases and load it directly into your DuckLake instance. The list of connections that Sling supports continues to grow. You can see the [full list](https://slingdata.io/en/connectors) here, but it supports all the major platforms including [Clickhouse](https://docs.slingdata.io/connections/database-connections/clickhouse), [DuckDB](https://docs.slingdata.io/connections/database-connections/duckdb), [Google BigQuery](https://docs.slingdata.io/connections/database-connections/bigquery), [Google BigTable](https://docs.slingdata.io/connections/database-connections/bigtable), [MariaDB](https://docs.slingdata.io/connections/database-connections/mariadb), [MongoDB](https://docs.slingdata.io/connections/database-connections/mongodb), [MotherDuck](https://docs.slingdata.io/connections/database-connections/motherduck), [MySQL](https://docs.slingdata.io/connections/database-connections/mysql), [Oracle](https://docs.slingdata.io/connections/database-connections/oracle), [PostgreSQL](https://docs.slingdata.io/connections/database-connections/postgres), [Prometheus](https://docs.slingdata.io/connections/database-connections/prometheus), [Redshift](https://docs.slingdata.io/connections/database-connections/redshift), [Snowflake](https://docs.slingdata.io/connections/database-connections/snowflake), [SQL Server](https://docs.slingdata.io/connections/database-connections/sqlserver), [SQLite](https://docs.slingdata.io/connections/database-connections/sqlite), [StarRocks](https://docs.slingdata.io/connections/database-connections/starrocks), [Databricks](https://docs.slingdata.io/connections/database-connections/databricks) and more.

### What is DuckLake?

Before we dive in, let's quickly recap what makes DuckLake special. DuckLake is a data lake format specification that combines the power of DuckDB with flexible catalog backends and scalable data storage. It provides versioned, ACID-compliant tables, which brings database-like reliability to your data lake. See Ducklake's website for more details: https://ducklake.select/.

Key features include:
* **Flexible Catalog:** Use DuckDB, SQLite, PostgreSQL, or MySQL as your catalog backend. 
* **Scalable Storage:** Store your data files locally, or in cloud storage like AWS S3, Azure Blob Storage, or Google Cloud Storage. 

### Step 1: Configure Your Connections

First things first, we need to tell Sling how to connect to our source database and our target DuckLake instance. We'll use the `sling conns` command, which makes managing connections a breeze. 

#### Source Database: PostgreSQL

Let's assume our source data is in a PostgreSQL database. See the complete list of databases sling can connect to [here](https://docs.slingdata.io/connections/database-connections).

We can set up a connection named `PG_CONN` like this:

```bash
sling conns set PG_CONN type=postgres host=mypg.host user=myuser password=mypass port=5432 database=analytics

# or use environment variable
export PG_CONN='postgresql://myuser:mypass@mypg.host:5432/analytics?sslmode=require'
```

You can then run `sling conns test pg_conn` to ensure it can successfully connect.

#### Target: DuckLake

Configuring DuckLake involves specifying the catalog and the data storage path. See the documentation for details [here](https://docs.slingdata.io/connections/database-connections/ducklake).

For this example, we'll use a local SQLite file for the catalog and a local directory for the data. This setup is great for multi-client access on the same machine.

Here's how to set up a connection named `DUCKLAKE_CONN`:

```bash
sling conns set DUCKLAKE_CONN type=ducklake catalog_type=sqlite catalog_conn_string=ducklake_catalog.db data_path=./ducklake_data

# or use environment variable
export DUCKLAKE_CONN='{ 
  type: ducklake, 
  catalog_type: sqlite,
  catalog_conn_string: "ducklake_catalog.db",
  data_path: "./ducklake_data"
}'
```

After setting these, you can verify they're configured correctly by running `sling conns list`.

If you used the `sling conns set` command, your [`~/.sling/env.yaml`](https://docs.slingdata.io/sling-cli/environment#sling-env-file-env.yaml) file should now contain these configurations:

```yaml
connections:
  PG_CONN:
    type: postgres
    host: mypg.host
    user: myuser
    password: mypass
    port: 5432
    database: analytics

  DUCKLAKE_CONN:
    type: ducklake
    catalog_type: sqlite
    catalog_conn_string: ducklake_catalog.db
    data_path: ./ducklake_data
```

### Step 2: Create the Replication YAML

Now for the fun part. We'll define our data movement task in a simple YAML file. Sling's replication configs are powerful because you can define defaults and replicate many streams (e.g., tables) at once. See [here](https://docs.slingdata.io/concepts/replication) for full documentation.

Let's create a file named `db_to_ducklake.yaml`:

```yaml
source: pg_conn
target: ducklake_conn

defaults:
  mode: full-refresh
  object: '{stream_schema}.{stream_table}' # Dynamically name tables in DuckLake

streams:
  # Replicate all tables from the 'public' schema
  public.*:

  # You can also add specific tables or disable some
  public.forbidden:
    disabled: true
  
  analytics.users:
```

A few things to note here:

  * `source` and `target` refer to the connection names we just set up.
  * `defaults` applies the `full-refresh` mode to all our streams. This means the target tables in DuckLake will be dropped and recreated on each run.
  * The `object` name is the target table, and uses runtime variables `{stream_schema}` and `{stream_table}`. Sling will dynamically replace these with the actual schema and table names from the source.
  * The real power move is `public.*`. This single line tells Sling to find all tables in the `public` schema of our PostgreSQL database and replicate every single one of them.

### Step 3: Run the Replication

With our connections and replication file ready, all that's left is to run it:

```bash
sling run -r db_to_ducklake.yaml
```

That's it\! Sling will connect to your PostgreSQL database, read the tables from the `public` and `analytics` schemas, and write the data into your DuckLake instance, creating the tables and structuring the data files in the `./ducklake_data` directory.

### Going Further: Incremental Loads and Cloud Storage

This example is just the beginning. You can easily adapt this for more advanced use cases:

  * **Incremental Loads:** Change the `mode` to `incremental` and specify a `primary_key` and/or `update_key` to only process new or updated records, making your pipelines much more efficient.
  * **Cloud Storage:** To use cloud storage for your data, simply update your DuckLake connection's `data_path` to an S3, GCS, or Azure URI and provide the necessary credentials.

For example, to use an S3 bucket, your DuckLake connection in `env.yaml` might look like this:

```yaml
connections:
  DUCKLAKE_CONN_S3:
    type: ducklake
    catalog_type: postgres
    catalog_conn_string: "host=db.example.com port=5432 user=ducklake password=secret dbname=ducklake_catalog"
    data_path: "s3://my-data-lake-bucket/data/"
    s3_access_key_id: "AKIA..."
    s3_secret_access_key: "xxxx"
```

And here's a replication with [incremental mode](https://docs.slingdata.io/concepts/replication/modes) and custom SQL:

```yaml
source: pg_conn
target: ducklake_conn

streams:

  analytics.users:
    object: '{stream_schema}.{stream_table}'
    mode: incremental
    primary_key: id
    update_key: updated_at
  
  custom_stream:
    object: another_schema.report
    sql: |
      select ...
      from ...
```

### Conclusion

DuckLake brings exciting new capabilities to the world of data lakes, and with Sling, populating it from your existing databases is incredibly straightforward. With just a few lines of configuration, you can build scalable, repeatable, and robust data pipelines to feed your modern data stack.

Ready to give it a try? [Install the Sling CLI](https://docs.slingdata.io/sling-cli/getting-started) and check out the [official documentation](https://docs.slingdata.io/) to get started.