Migrating to ClickHouse using clickhouse-local

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/08_other_methods/01_clickhouse-local-etl.md)

You can use ClickHouse, or to be more specific,[`clickhouse-local`](/docs/operations/utilities/clickhouse-local)
as an ETL tool for migrating data from your current database system to ClickHouse Cloud, as long as for your current database system there is either a
ClickHouse-provided [integration engine](/docs/engines/table-engines#integration-engines) or [table function](/docs/sql-reference/table-functions), respectively,
or a vendor provided JDBC driver or ODBC driver available.

We sometimes call this migration method a "pivot" method, because it uses an intermediate pivot point or hop to move the data from the source database to the destination database. For example, this method may be required if only outbound connections are allowed from within a private or internal network due to security requirements, and therefore you need to pull the data from the source database with clickhouse-local, then push the data into a destination ClickHouse database, with clickhouse-local acting as the pivot point.

ClickHouse provides integration engines and table functions (that create integration engines on-the-fly) for [MySQL](/docs/engines/table-engines/integrations/mysql), [PostgreSQL](/docs/engines/table-engines/integrations/postgresql), [MongoDB](/docs/engines/table-engines/integrations/mongodb) and [SQLite](/docs/engines/table-engines/integrations/sqlite).
For all other popular database systems, there is JDBC driver or ODBC driver available from the vendor of the system.

## What is clickhouse-local?[​](#what-is-clickhouse-local "Direct link to What is clickhouse-local?")

Typically, ClickHouse is run in the form of a cluster, where several instances of the ClickHouse database engine are running in a distributed fashion on different servers.

On a single server, the ClickHouse database engine is run as part of the `clickhouse-server` program. Database access (paths, users, security, ...) is configured with a server configuration file.

The `clickhouse-local` tool allows you to use the ClickHouse database engine isolated in a command-line utility fashion for blazing-fast SQL data processing on an ample amount of inputs and outputs, without having to configure and start a ClickHouse server.

## Installing clickhouse-local[​](#installing-clickhouse-local "Direct link to Installing clickhouse-local")

You need a host machine for `clickhouse-local` that has network access to both your current source database system and your ClickHouse Cloud target service.

On that host machine, download the appropriate build of `clickhouse-local` based on your computer's operating system:

- Linux- macOS

1. The simplest way to download `clickhouse-local` locally is to run the following command:

```
curl https://clickhouse.com/ | sh
```

1. Run `clickhouse-local` (it will just print its version):

```
./clickhouse-local
```

1. The simplest way to download `clickhouse-local` locally is to run the following command:

```
curl https://clickhouse.com/ | sh
```

1. Run `clickhouse-local` (it will just print its version):

```
./clickhouse local
```

Important

The examples throughout this guide use the Linux commands for running `clickhouse-local` (`./clickhouse-local`).
To run `clickhouse-local` on a Mac, use `./clickhouse local`.

Add the remote system to your ClickHouse Cloud service IP Access List

In order for the `remoteSecure` function to connect to your ClickHouse Cloud service, the IP address of the remote system needs to be allowed by the IP Access List. Expand **Manage your IP Access List** below this tip for more information.

Manage your IP Access List

From your ClickHouse Cloud services list choose the service that you will work with and switch to **Settings**. If the IP Access List doesn't contain the IP Address or range of the remote system that needs to connect to your ClickHouse Cloud service, then you can resolve the problem with **Add IPs**:

Add the individual IP Address, or the range of addresses that need to connect to your ClickHouse Cloud service. Modify the form as you see fit and then **Save**.

## Example: Migrating from MySQL to ClickHouse Cloud with an Integration engine[​](#example-1-migrating-from-mysql-to-clickhouse-cloud-with-an-integration-engine "Direct link to Example: Migrating from MySQL to ClickHouse Cloud with an Integration engine")

We will use the [integration table engine](/docs/engines/table-engines/integrations/mysql) (created on-the-fly by the [mysql table function](/docs/sql-reference/table-functions/mysql)) for reading data from the source MySQL database and we will use the [remoteSecure table function](/docs/sql-reference/table-functions/remote)
for writing the data into a destination table on your ClickHouse cloud service.

### On the destination ClickHouse Cloud service:[​](#on-the-destination-clickhouse-cloud-service "Direct link to On the destination ClickHouse Cloud service:")

#### Create the destination database:[​](#create-the-destination-database "Direct link to Create the destination database:")

```
CREATE DATABASE db
```

#### Create a destination table that has a schema equivalent to the MySQL table:[​](#create-a-destination-table-that-has-a-schema-equivalent-to-the-mysql-table "Direct link to Create a destination table that has a schema equivalent to the MySQL table:")

```
CREATE TABLE db.table ...
```

Note

The schema of the ClickHouse Cloud destination table and schema of the source MySQL table must be aligned (the column names and order must be the same, and the column data types must be compatible).

### On the clickhouse-local host machine:[​](#on-the-clickhouse-local-host-machine "Direct link to On the clickhouse-local host machine:")

#### Run clickhouse-local with the migration query:[​](#run-clickhouse-local-with-the-migration-query "Direct link to Run clickhouse-local with the migration query:")

```
./clickhouse-local --query "
INSERT INTO FUNCTION
remoteSecure('HOSTNAME.clickhouse.cloud:9440', 'db.table', 'default', 'PASS')
SELECT * FROM mysql('host:port', 'database', 'table', 'user', 'password');"
```

Note

No data is stored locally on the `clickhouse-local` host machine. Instead, the data is read from the source MySQL table
and then immediately written to the destination table on the ClickHouse Cloud service.

- [What is clickhouse-local?](#what-is-clickhouse-local)- [Installing clickhouse-local](#installing-clickhouse-local)- [Example: Migrating from MySQL to ClickHouse Cloud with an Integration engine](#example-1-migrating-from-mysql-to-clickhouse-cloud-with-an-integration-engine)
      - [On the destination ClickHouse Cloud service:](#on-the-destination-clickhouse-cloud-service)- [On the clickhouse-local host machine:](#on-the-clickhouse-local-host-machine)

Was this page helpful?