aman
← all writeups

One INSERT, 2.8M rows, no ETL

We copied one tenant's data from a MySQL read replica into ClickHouse with a single INSERT ... SELECT through the mysql() table function. The hard parts were a mistyped column and ClickHouse having no unique keys.

Date
2026-09-15
Type
build
Impact
2,794,333 contact rows for one tenant were exported into ClickHouse, and a second table was re-synced to close a 15,032-row gap, with no intermediate files or pipeline code.

We needed one tenant’s HubSpot contact data in ClickHouse for migration testing: 2,794,333 rows. The obvious plan was a Python script that dumps to CSV, chunks the file and loads it. We didn’t need any of that.

What we built

A repeatable export that’s one SQL statement plus a runbook. ClickHouse can read a MySQL table directly with the mysql() table function. The ClickHouse EC2 instance could already reach the RDS read replica over the network, so the whole copy runs inside ClickHouse:

INSERT INTO hs_contact_split (org_id, segment, overwritten, is_marketing, ...)
SELECT toString(org_id), toString(segment),
       ifNull(overwritten, 0), ifNull(is_marketing, 0), ...
FROM mysql('<replica>:3306', '<db>', 'hs_contact_split', '<ro_user>', '<password>')
WHERE org_id = <tenant>

How it works

  1. Tunnel. An SSM port-forward to the ClickHouse HTTP interface. No bastion, and no public port.
  2. Find the table. Most tenant tables are in the central database on the replica. Some tenant-specific databases aren’t replicated at all.
  3. Diff the schemas. SHOW COLUMNS on MySQL, DESCRIBE TABLE on ClickHouse. The target table already existed from earlier work and wasn’t a mirror: org_id was Nullable(String), not a 64-bit int, so WHERE org_id = <number> failed with error 386 (There is no supertype for types String, UInt32). segment was also a string, two UInt8 columns were non-nullable where MySQL allows NULL, and a few columns existed on only one side. Hence the casts above. Only use SELECT * when the columns match 1:1.
  4. Run it. As a background job with a padded max_execution_time. Observed rate: about 2 minutes per 250K rows.
  5. Verify with counts only. count() on both sides. We never pull rows to “check”.
  6. Refresh by delete-then-reinsert. MergeTree doesn’t enforce uniqueness, so re-running the INSERT silently duplicates every row. For the web-tracking table that meant ALTER TABLE ... DELETE WHERE org_id = X SETTINGS mutations_sync = 1, then the full insert again, which closed a 15,032-row gap since the previous export.

The runbook also keeps a table of which tables and tenants have been exported, so nobody has to piece it together from chat history again.

What didn’t work

Trusting the app’s config. .env pointed at a migration-test database, but the data was actually in default. We also assumed the ClickHouse table matched MySQL because it had the same name. It didn’t.

Try the table function before writing ETL. For a one-off copy, it’s often all you need.

← all writeups