Amazon Redshift to ClickHouse migration guide

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/06_redshift/02_migration_guide.md)

## Introduction[​](#introduction "Direct link to Introduction")

[Amazon Redshift](https://aws.amazon.com/redshift/) is a popular cloud data warehousing solution that is part of the Amazon Web Services offerings. This guide presents different approaches to migrating data from a Redshift instance to ClickHouse. We will cover three options:

From the ClickHouse instance standpoint, you can either:

1. **[PUSH](#push-data-from-redshift-to-clickhouse)** data to ClickHouse using a third party ETL/ELT tool or service
2. **[PULL](#pull-data-from-redshift-to-clickhouse)** data from Redshift leveraging the ClickHouse JDBC Bridge
3. **[PIVOT](#pivot-data-from-redshift-to-clickhouse-using-s3)** using S3 object storage using an "Unload then load" logic

Note

We used Redshift as a data source in this tutorial. However, the migration approaches presented here aren't exclusive to Redshift, and similar steps can be derived for any compatible data source.

## Push Data from Redshift to ClickHouse[​](#push-data-from-redshift-to-clickhouse "Direct link to Push Data from Redshift to ClickHouse")

In the push scenario, the idea is to leverage a third-party tool or service (either custom code or an [ETL/ELT](https://en.wikipedia.org/wiki/Extract,_transform,_load#ETL_vs._ELT)) to send your data to your ClickHouse instance. For example, you can use a software like [Airbyte](https://www.airbyte.com/) to move data between your Redshift instance (as a source) and ClickHouse as a destination ([see our integration guide for Airbyte](/docs/integrations/airbyte))

### Pros[​](#pros "Direct link to Pros")

- It can leverage the existing catalog of connectors from the ETL/ELT software.
- Built-in capabilities to keep data in sync (append/overwrite/increment logic).
- Enable data transformation scenarios (for example, see our [integration guide for dbt](/docs/integrations/dbt)).

### Cons[​](#cons "Direct link to Cons")

- You need to set up and maintain an ETL/ELT infrastructure.
- Introduces a third-party element in the architecture which can turn into a potential scalability bottleneck.

## Pull Data from Redshift to ClickHouse[​](#pull-data-from-redshift-to-clickhouse "Direct link to Pull Data from Redshift to ClickHouse")

In the pull scenario, the idea is to leverage the ClickHouse JDBC Bridge to connect to a Redshift cluster directly from a ClickHouse instance and perform `INSERT INTO ... SELECT` queries:

### Pros[​](#pros-1 "Direct link to Pros")

- Generic to all JDBC compatible tools
- Elegant solution to allow querying multiple external data sources from within ClickHouse

### Cons[​](#cons-1 "Direct link to Cons")

- Requires a ClickHouse JDBC Bridge instance which can turn into a potential scalability bottleneck

Note

Even though Redshift is based on PostgreSQL, using the ClickHouse PostgreSQL table function or table engine isn't possible since ClickHouse requires PostgreSQL version 9 or above and the Redshift API is based on an earlier version (8.x).

### Tutorial[​](#tutorial "Direct link to Tutorial")

To use this option, you need to set up a ClickHouse JDBC Bridge. ClickHouse JDBC Bridge is a standalone Java application that handles JDBC connectivity and acts as a proxy between the ClickHouse instance and the data sources. For this tutorial, we used a pre-populated Redshift instance with a [sample database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html).

Deploy ClickHouse JDBC Bridge

#### Deploy ClickHouse JDBC Bridge[​](#deploy-clickhouse-jdbc-bridge "Direct link to Deploy ClickHouse JDBC Bridge")

Deploy the ClickHouse JDBC Bridge. For more details, see our user guide on [JDBC for External Data sources](/docs/integrations/jdbc/jdbc-with-clickhouse)

Note

If you're using ClickHouse Cloud, you will need to run your ClickHouse JDBC Bridge on a separate environment and connect to ClickHouse Cloud using the [remoteSecure](/docs/sql-reference/table-functions/remote) function

Configure your Redshift datasource

#### Configure your Redshift datasource[​](#configure-your-redshift-datasource "Direct link to Configure your Redshift datasource")

Configure your Redshift datasource for ClickHouse JDBC Bridge. For example, `/etc/clickhouse-jdbc-bridge/config/datasources/redshift.json` 

```
{
 "redshift-server": {
   "aliases": [
     "redshift"
   ],
   "driverUrls": [
   "https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/2.1.0.4/redshift-jdbc42-2.1.0.4.jar"
   ],
   "driverClassName": "com.amazon.redshift.jdbc.Driver",
   "jdbcUrl": "jdbc:redshift://redshift-cluster-1.ckubnplpz1uv.us-east-1.redshift.amazonaws.com:5439/dev",
   "username": "awsuser",
   "password": "<password>",
   "maximumPoolSize": 5
 }
}
```

Query your Redshift instance from ClickHouse

#### Query your Redshift instance from ClickHouse[​](#query-your-redshift-instance-from-clickhouse "Direct link to Query your Redshift instance from ClickHouse")

Once ClickHouse JDBC Bridge deployed and running, you can start querying your Redshift instance from ClickHouse

```
SELECT *
FROM jdbc('redshift', 'select username, firstname, lastname from users limit 5')
```

```
Query id: 1b7de211-c0f6-4117-86a2-276484f9f4c0

┌─username─┬─firstname─┬─lastname─┐
│ PGL08LJI │ Vladimir  │ Humphrey │
│ XDZ38RDD │ Barry     │ Roy      │
│ AEB55QTM │ Reagan    │ Hodge    │
│ OWY35QYB │ Tamekah   │ Juarez   │
│ MSD36KVR │ Mufutau   │ Watkins  │
└──────────┴───────────┴──────────┘

5 rows in set. Elapsed: 0.438 sec.
```

```
SELECT *
FROM jdbc('redshift', 'select count(*) from sales')
```

```
Query id: 2d0f957c-8f4e-43b2-a66a-cc48cc96237b

┌──count─┐
│ 172456 │
└────────┘

1 rows in set. Elapsed: 0.304 sec.
```

Import Data from Redshift to ClickHouse

#### Import Data from Redshift to ClickHouse[​](#import-data-from-redshift-to-clickhouse "Direct link to Import Data from Redshift to ClickHouse")

In the following, we display importing data using an `INSERT INTO ... SELECT` statement

```
# TABLE CREATION with 3 columns
CREATE TABLE users_imported
(
   `username` String,
   `firstname` String,
   `lastname` String
)
ENGINE = MergeTree
ORDER BY firstname
```

```
Query id: c7c4c44b-cdb2-49cf-b319-4e569976ab05

Ok.

0 rows in set. Elapsed: 0.233 sec.
```

```
INSERT INTO users_imported (*) SELECT *
FROM jdbc('redshift', 'select username, firstname, lastname from users')
```

```
Query id: 9d3a688d-b45a-40f4-a7c7-97d93d7149f1

Ok.

0 rows in set. Elapsed: 4.498 sec. Processed 49.99 thousand rows, 2.49 MB (11.11 thousand rows/s., 554.27 KB/s.)
```

## Pivot Data from Redshift to ClickHouse using S3[​](#pivot-data-from-redshift-to-clickhouse-using-s3 "Direct link to Pivot Data from Redshift to ClickHouse using S3")

In this scenario, we export data to S3 in an intermediary pivot format and, in a second step, load the data from S3 into ClickHouse.

### Pros[​](#pros-2 "Direct link to Pros")

- Both Redshift and ClickHouse have powerful S3 integration features.
- Leverages the existing features such as the Redshift `UNLOAD` command and ClickHouse S3 table function / table engine.
- Scales seamlessly thanks to parallel reads and high throughput capabilities from/to S3 in ClickHouse.
- Can leverage sophisticated and compressed formats like Apache Parquet.

### Cons[​](#cons-2 "Direct link to Cons")

- Two steps in the process (unload from Redshift then load into ClickHouse).

### Tutorial[​](#tutorial-1 "Direct link to Tutorial")

Export data into an S3 bucket using UNLOAD

#### Export data into an S3 bucket using UNLOAD[​](#export-data-into-an-s3-bucket-using-unload "Direct link to Export data into an S3 bucket using UNLOAD")

Using Redshift's [UNLOAD](https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html) feature, export the data into an existing private S3 bucket:

It will generate part files containing the raw data in S3

Create the table in ClickHouse

#### Create the table in ClickHouse[​](#create-the-table-in-clickhouse "Direct link to Create the table in ClickHouse")

Create the table in ClickHouse: