Replicate data from Supabase to ClickHouse®

Supabase is an open-source Firebase alternative built around Postgres. It inherits all the advantages of Postgres, such as scalability and extensibility, but just like other OLTP databases, its main use scenarios may not cover all of your needs.

With DoubleCloud, you can replicate your data in real time from Supabase to ClickHouse — a database designed for analytics. This will enable you to run analytical queries faster, use less storage, and save costs, while still benefiting from using an OLTP database

This tutorial guides you through replicating data from Supabase to ClickHouse® with DoubleCloud Transfer — a real-time data replication tool. You will learn how to create a ClickHouse® cluster, set up a data pipeline, apply data transformations, and run queries.

Before you start

  1. If you haven’t already, create a DoubleCloud account .

    1. If you don’t have a Supabase instance with data that you can use for this tutorial:

    2. Create a Supabase account and project

    3. Load sample data to your project

      How to load sample data

      1. Open your project in Supabase and click SQL editor in the left menu.

      2. Click Quickstarts → Countries.

        Screenshot showing the SQL editor page in Supabase

      3. Click Run.

        When the data is loaded into the public.countries table, you’ll see the Success. No rows returned message.

Step 1. (Optional) Enable real-time replication

Tip

If you’re only looking to make a snapshot of data and not transfer any incremental updates, you can skip this step.

  1. Open your project in Supabase and click Table editor in the left menu.

  2. Select your table and click the realtime button at the top right.

    Screenshot showing the realtime button on the Table editor page in Supabase

  3. In the dialog, confirm enabling realtime for the table.

Step 2. Enable access to Supabase and get connection details

By default, databases in your Supabase project have no access restrictions and can be accessed by any IP address.

  1. If you have limited access to the database, add Transfer’s IP addresses to the allowlist. To do that:

    1. Open your project in Supabase, click Project settings in the left menu and select Database

    2. Scroll down to the Network restrictions section and click Add restriction.

      Screenshot showing the "Add restriction" button on the project settings page in Supabase

    3. Add Transfer’s IP addresses:

      # IPv6
2a05:d014:e78:3500::/56

      # IPv4
3.77.1.232/32
3.74.181.206/32
3.125.212.122/32
3.77.29.32/32
3.68.105.66/32
52.59.249.9/32
18.184.232.195/32
3.76.190.60/32

  2. Open your project in Supabase, click Project settings in the left menu and select Database

  3. Under Database settings, copy the URI connection string. You will need this string when configuring the source endpoint later in this tutorial.

Step 3. Create a Managed ClickHouse® cluster

  1. Go to the Clusters page in the DoubleCloud console.

  2. Click Create cluster at the top right and select ClickHouse.

    Tip

    For this tutorial, you don’t need to modify cluster settings on this page. If you proceed with the default settings, you get a fully functional ClickHouse® cluster that you can use for testing and development.

    When creating a production cluster, refer to Create a Managed ClickHouse® cluster for a list of ways to create and configure a cluster.

  3. Under Basic settings, enter a cluster name, such as clickhouse-dev.

  4. Click Submit.

    Creating a cluster takes around five minutes depending on the provider, region, and settings.

Step 3. Create a ClickHouse® database

  1. After the cluster status changes from Creating to Alive, select it in the cluster list.

  2. Click WebSQL at the top right.

  3. In WebSQL, click on any database in the connection manager on the left to open the query editor.

  4. Create a new database named listings using the following command:

    CREATE DATABASE IF NOT EXISTS countries ON CLUSTER default;

  5. Check that the database has been created:

    SHOW DATABASES

    ┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ _system            │
│ default            │
│ countries          │  // your database
│ information_schema │
│ system             │
└────────────────────┘

Step 4. Create a source PostgreSQL endpoint

Because Supabase is essentially a Postgres database, you can connect to it by configuring a source PostgreSQL endpoint.

To create a source endpoint:

  1. Go to the Transfer page in the console and click Create → Source endpoint at the top right.

  2. In Source type, select PostgreSQL.

  3. Enter an endpoint name, such as supabase-source-dev.

  4. In connection type select SRV.

  5. Under Endpoint parameters, fill out connection details for your Supabase database. You can get the connection details from the connection string that you copied earlier. This string has the following format:

    postgres://<username>:<password>@<hostname>:<port>/<database>

  6. Under Collection filter, add countries in Included collections.

  7. Click Submit.

Step 5. Create a target ClickHouse® endpoint

To create a target endpoint:

  1. On the Transfer page, click Create → Target endpoint.

  2. In Target type, select ClickHouse.

  3. Enter an endpoint name, such as clickhouse-target-dev.

  4. In Connection type, select Managed cluster and select the cluster that you created from the dropdown.

    You can leave the default values in other fields.

  5. Click Submit.

Step 6. Create a transfer

Now that your endpoints are ready, you can create a transfer.

  1. On the Transfer page, click Create → Transfer at the top right.

  2. Under Endpoints, select the endpoints you just created — supabase-source-dev and clickhouse-target-dev as the source and target respectively.

  3. Under Basic settings, enter a transfer name, such as supabase-clickhouse-dev.

  4. In Transfer type, select Snapshot and replication.

    This transfer type transfers the current state of the source database, and then keeps all the added data in sync.

  5. Leave the default preselected options in Snapshot settings and Runtime environment

  6. In List of objects for transfer, click + Object and enter the name of the collection you want to transfer — public.countries.

    1. Click Submit.

Step 7. Activate the transfer and query transferred data

Now that you transfer is fully configured, you can use it to replicate data from Supabase to ClickHouse®.

  1. On the transfer page, click Activate at the top right.

    After the transfer is activated, it fetches data from the source database and uploads it to the target database.

    The initial activation may take a few minutes. When some data is successfully replicated, you can see green bars in the Status history section.

  2. Go to the Clusters page in the console.

  3. Select the cluster you created earlier.

  4. Click WebSQL at the top right.

  5. In the Connection manager on the left, select the countries database.

  6. In the query editor, run a SELECT query:

    SELECT * FROM public_countries LIMIT 100;

  7. The query output is displayed in the Results section.

What’s next

Previous
Replicate data from MongoDB to ClickHouse®
Next
Replicate and visualize data from Yandex Metrica