> Site index: https://usehardal.com/llms.txt
> Every content route also serves markdown at <url>.md

# Moving 122 Million Analytics Rows Between ClickHouse Regions

How we moved 122 million live analytics rows from Google Cloud Europe to Huawei Cloud Istanbul with ClickHouse remoteSecure, materialized views, and a long-running SSH session.

Source: https://usehardal.com/clickhouse-data-residency-migration-122-million-rows
Published: 2025-02-25
Updated: 2026-09-27
Author: Mert Enercan
Category: Inside Hardal

---

We moved 122 million analytics rows from a ClickHouse deployment on Google Cloud in Europe to Huawei Cloud in Istanbul. The goal was to run the workload in the selected hosting region without rebuilding the analytics pipeline around a file export.

The successful copy took about 24 hours. The failed attempts cost us roughly another day and a small amount of sanity.

![ClickHouse data transfer overview](https://imge.usehardal.com/cdn/post-contents/122-milyon-sat%C4%B1r-analytics-datas%C4%B1n%C4%B1-nas%C4%B1l-t%C3%BCrkiyeye-getirdik/swtaiytcrvoemuoayqk8.png)

Our ClickHouse deployment uses tables from the `MergeTree` family. Raw event data lands in the `analytics` table. The system also has configuration and telemetry tables, plus two materialized views that shape analytics events for faster reads.

![ClickHouse and PostgreSQL performance comparison](https://imge.usehardal.com/cdn/post-contents/122-milyon-sat%C4%B1r-analytics-datas%C4%B1n%C4%B1-nas%C4%B1l-t%C3%BCrkiyeye-getirdik/flpkeuhofwlvtpzfdksx.png)

The table layout made the migration smaller than it first appeared. We could create the destination schema, copy the raw `analytics` table, and let inserts into that table feed the destination materialized views. We did not need to export and import each derived dataset separately.

![Raw analytics data feeding materialized views](https://imge.usehardal.com/cdn/post-contents/122-milyon-sat%C4%B1r-analytics-datas%C4%B1n%C4%B1-nas%C4%B1l-t%C3%BCrkiyeye-getirdik/sxtwszh6liomgqsjrzzb.png)

We considered two approaches:

1. Export the source table to CSV or ClickHouse Native files, move the files, then import them into the destination.
2. Let ClickHouse copy the rows directly between servers with its `remote` or `remoteSecure` table function.

We tried the file route first. At this volume, export and import time turned a simple plan into a long chain of intermediate files, storage requirements, and retry points. The direct ClickHouse transfer was a better fit for a one-time migration between compatible schemas.

The production query followed this pattern:

```sql
INSERT INTO FUNCTION remoteSecure(
  'destination.example.com:9440',
  'default',
  'analytics',
  'migration_user',
  'password'
)
SELECT *
FROM analytics;
```

Use a restricted migration account and a secret-management method appropriate for your environment. The placeholders above are not real credentials.

The first 1 percent took about five minutes. We multiplied by 100 and expected the job to finish in roughly eight hours. We left it running overnight.

That was optimistic.

Google Cloud's browser-based SSH session kept timing out during the long query. Worse, the source table was still live. Thousands of new rows arrived while we were trying to copy the old ones, so the finish line kept moving.

![A failed ClickHouse transfer attempt](https://imge.usehardal.com/cdn/post-contents/122-milyon-sat%C4%B1r-analytics-datas%C4%B1n%C4%B1-nas%C4%B1l-t%C3%BCrkiyeye-getirdik/ibhhpn2cdux2pp1ophjb.png)

After three or four failed attempts, we stopped running the migration inside a browser tab. We opened a direct SSH connection from a local terminal and kept the process in a persistent terminal session.

A local terminal avoids the lifecycle of a browser-based SSH tab, but the migration should not depend on one uninterrupted network connection either. Run a long copy inside `tmux` or `screen`, or submit it through an orchestration system that records progress and survives a disconnect.

That setup gave us three things:

- The query kept running if the SSH client disconnected.
- We could reconnect and inspect progress from another terminal.
- Closing a browser or laptop no longer killed the migration.

After 12 hours, the transfer had reached 50 percent without another SSH timeout.

Our other mistake was treating 122 million rows as a fixed dataset. It was a live analytics table. Every failed day added more rows to the next attempt.

For an active table, define the cutover before starting the bulk copy. A safer plan is:

1. Record a high-water mark, such as an event timestamp or monotonically increasing ID.
2. Copy rows up to that boundary.
3. Replicate or pause writes during a short cutover window.
4. Copy the remaining delta.
5. Point writers and readers at the destination.
6. Compare row counts and time ranges, then check aggregates or checksums by partition.

The exact method depends on the table engine and ingestion path. The fixed boundary is the part we underestimated.

The destination schema should exist before the copy, including partition keys, ordering keys, codecs, and materialized view definitions. We would also test a representative partition first, measure throughput from ClickHouse rather than extrapolating from the first percentage point, and document the delta-copy plan before touching the full table.

After roughly 24 hours, the base analytics table was running in the Istanbul region and the destination materialized views had received the copied rows. The transfer worked. The next one gets a fixed cutover boundary and a persistent job runner from minute one.

- [ClickHouse `remote` and `remoteSecure` table functions](https://clickhouse.com/docs/reference/functions/table-functions/remote)