DoubleCloud

DoubleCloud helps businesses harness their data by providing a set of managed open-source solutions. They offer managed services for data integration, orchestration, storage, analytics, and visualization. DoubleCloud is an engineer-friendly, easy-to-use platform that lets you build data analytics infrastructure in one day.

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.

Documentation

DoubleCloud is a managed data platform that helps engineering teams build data infrastructure with zero-maintenance open-source technologies, such as ClickHouse.

ClickHouse is an open-source column-oriented database that's able to query billions of rows in milliseconds. It's designed to efficiently handle analytical queries, which makes it the go-to solution for data warehousing and analytics applications. Thanks to the columnar storage format, data can be compressed and retrieved more efficiently, allowing some analytical queries to execute 100 times faster compared to traditional databases like Postgres.

This tutorial guides you through creating a Managed ClickHouse cluster and transferring data to it with DoubleCloud Transfer — a real-time data replication tool. Transfer natively supports ClickHouse data types, data mutations, automated migrations (adding columns), as well as emulating insertions and deletions.

Before you start

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

   If you're a new DoubleCloud user, this tutorial won't incur you any costs — you can use the trial period credits to test the platform, including creating fully operational clusters.

2. If you don't have a Supabase instance with data that you want to transfer, load sample data to your project. To do that:

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

   2. Click Quickstarts → Countries.

      

   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

You need to enable replication if you want to transfer a snapshot of the existing data and then transfer all eventual incremental updates. If don't want to transfer updates, you can skip this step.

To enable real-time replication:

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.

   

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.

      

   3. Add Transfer's IP addresses:

      
      

      _10

      # IPv6

      _10

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

      
      
      
      

      _10

      # IPv4

      _10

      3.77.1.232

      _10

      3.74.181.206

      _10

      3.78.156.2

      _10

      3.77.29.32

      _10

      3.125.212.122

      
      

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. In the DoubleCloud console, go to the Clusters page.

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

   You can proceed with the default settings on this page to get a fully functional ClickHouse cluster that you can use for testing and development.

   When creating a production cluster, refer to the Create a Managed ClickHouse cluster guide 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 in the query editor:

   
   

   _10

   CREATE DATABASE IF NOT EXISTS countries ON CLUSTER default;

   
   

5. Check that the database has been created:

   
   

   _10

   SHOW DATABASES

   
   
   
   

   _10

   ┌─name───────────────┐

   _10

   │ INFORMATION_SCHEMA │

   _10

   │ _system │

   _10

   │ default │

   _10

   │ countries │ // your database

   _10

   │ information_schema │

   _10

   │ system │

   _10

   └────────────────────┘

   
   

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 DoubleCloud 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. 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:

   
   

   _10

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

   
   

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

6. 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.

   

7. 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 DoubleCloud 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 SQL editor, run a SELECT query:

   
   

   _10

   SELECT * FROM public_countries LIMIT 100;

   
   

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

What's next

• Create a Managed ClickHouse cluster on DoubleCloud for your production data

• Learn how to use DoubleCloud Transfer to replicate, migrate, and transform data

• Visualize the data from your Managed ClickHouse cluster

For more implementation guides, code examples, and support, visit DoubleCloud documentation or join the Slack community.