Using clickhouse-local as a migration ETL tool: when a separate host is enough
A separate host is enough when the team needs a one-time or periodic bulk move of analytical data into self-managed ClickHouse and does not want the cluster exposed to the source network or a dedicated ETL platform to run. clickhouse-local is the answer for that case: it is the same ClickHouse engine packaged as a local process that reads from a source (a database through a table function, or files in formats ClickHouse understands) and writes into a remote cluster over the network. The ClickHouse server itself is open source under the Apache License 2.0 (Apache-2.0), so the same engine and SQL dialect used in the cluster are available on the migration host without a separate product purchase. The move involves three things: a staging host with network routes to both the source database and the target cluster, credentials scoped to read on the source and insert on specific target tables, and a set of SQL statements that map source columns to target types. The cluster stays closed to the source network because only the staging host talks to both sides. Effort depends mostly on the number of source tables, how much type mapping and cleanup each one needs, and whether the data must be loaded more than once. Confirm with your own team the source database version, the target cluster version, and whether the host can reach each side on the ports those systems use.
Before committing, decide three things. First, whether the staging host is short-lived and destroyed after cutover, which keeps standing credentials and network paths out of the estate. Second, which account the host uses on the cluster: it should be a dedicated user with insert rights on named databases only, not the default user, which starts with all permissions and should be restricted on any production cluster. Third, how the team will prove the load is complete, for example by comparing row counts and checksums per partition between source and target before any reporting moves over. Run a full dry run against a pre-production cluster with representative volumes first. If the migration needs continuous change capture rather than batch loads, a different path is the better fit, and the sibling topics on CDC and MySQL cover that choice.
What maps cleanly from the source and what does not
This table compares what each common source offers with how ClickHouse handles the same capability during an clickhouse-local extract and load. Read each row as a planning question: if the answer is a mismatch, the migration needs a data-model or type change, not just a copy step. The main takeaway is that analytical reads and bulk loads map cleanly, while updates, deletes, constraints and transactions need deliberate redesign.
| Capability | Source database (PostgreSQL or MySQL) | ClickHouse target |
|---|---|---|
| Table reads and bulk export | clickhouse-local reads through its postgresql or mysql table functions, which fit one-off or scheduled full copies | Loads through INSERT ... SELECT into MergeTree tables, a clean fit for append-heavy analytical data |
| File formats | Parquet, CSV, JSON and other files read directly by clickhouse-local, with no source database connection needed | Same formats load into ClickHouse, so flat-file sources often map with the least rework |
| Data types | Numeric, decimal, timestamp and JSON types need explicit mapping; timezone and precision differences can shift values | Requires a reviewed type map per column, with Nullable and LowCardinality chosen deliberately |
| Updates and deletes | Rows change in place and deletes are routine | Mutations are heavy; model changes as ReplacingMergeTree or CollapsingMergeTree, and confirm behavior against your version |
| Indexes | B-tree indexes serve point lookups and joins | Sorting key and skip indexes replace them; choose the ORDER BY key from the query pattern, not the source index list |
| Transactions and sequences | Commits are atomic and auto-increment keys enforce uniqueness | No multi-table transactions; generate keys in the pipeline and validate row counts per batch instead |
| Constraints and views | Foreign keys, unique and check constraints, and views are enforced or stored in the source | Constraints are not enforced in the same way; move checks into validation queries and rebuild views as materialized views or queries |
Documented import paths clickhouse-local can use
clickhouse-local can read from a source database, a file or object storage and then write into a remote ClickHouse cluster. Each path below has different credential, network and type-mapping consequences, so the choice shapes the whole migration job. Confirm which functions and engines exist in the exact release you will run before you design the pipeline.
MySQL table function reads
The mysql table function lets clickhouse-local query a MySQL table directly and stream rows into a ClickHouse table. The MySQL connection runs from the ETL host, so that host needs network access to the source and a read-only account. Check how each MySQL column type maps to a ClickHouse type, because mismatches such as decimal precision and datetime zones surface only when you compare counts and sums after the load. Confirm the function signature and options in your target release.
PostgreSQL table function reads
The postgresql table function reads PostgreSQL tables from the ETL host, which keeps the source database and the cluster on separate network paths. Plan a snapshot-consistent read for busy tables, since a long scan without a stable snapshot can mix states across the copy. Verify that the function's array, JSON and numeric handling matches what your downstream queries expect. Confirm availability and parameters in the release you deploy.
File and object storage reads
The file and s3 table functions read CSV, JSON, Parquet and similar files from local disk or S3-compatible storage. This path suits exports that a source team already produces, and it lets you rerun a load from the same files. Decide where the files are retained, who can write to that bucket, and how you record which files a load has consumed. Confirm the supported formats and compression options in your target version.
Remote inserts over the cluster interface
clickhouse-local can push query results into a target table using the remote or remoteSecure table functions, which connect to the cluster over its native or TLS port. Only the ETL host needs an allowed path to the cluster, so the cluster does not need to reach back into the source network. Create a dedicated ingest user with insert rights on the target tables only, and confirm the exact remote function signature and TLS options in your release.
Version and function availability checks
Table functions, formats and TLS options change between ClickHouse releases, so the function list in your documentation may not match the binary you install. Run each source path against a small sample in a pre-production environment with the same version as production. Record the clickhouse-local version used for each job so a rerun later produces the same results. The production FAQ advises reading the changelog before upgrading for this reason.
Sequence from inventory to decommissioning the old source
This sequence moves data from the old source into ClickHouse in controlled stages, with each stage proving something specific before the next one starts. It ends with clients cut over to ClickHouse and the old source retired with a recorded sign-off.
Inventory tables, volumes and consumers
List every table or file set to be moved, its row counts, column types, update and delete patterns, and the queries and dashboards that read it. Record which tables carry timestamps or monotonic keys that the incremental load can use. The migration owner and the data owner for each source sign off on this inventory, and the output is a scoped table list that defines what the pilot and full load must cover.
Build the clickhouse-local host and credentials
Install clickhouse-local on a separate host that can reach the source database or files and the target cluster, and keep the cluster itself off the source network. Create a read-only source account and a scoped target user with insert rights only on the migration databases, then confirm both connections from the host. The platform engineer signs off that the host can reach both ends and that no inbound path to the cluster was opened.
Run a pilot load into a non-production cluster
Load a representative subset, including the hardest type mappings and the largest partition, into a non-production ClickHouse cluster running the same version planned for production. Check that the schema, types and ordering key behave as intended under realistic query load. The data engineer and the owner of the affected reports sign off, and the output is a list of type or schema fixes applied before the full load.
Validate and complete the full load
Run the full copy by partition or date range so that each batch can be retried independently, and log row counts and checksums per batch on both sides. Compare totals and a sample of aggregates between source and target for each table. The data engineer signs off per table, and the output is a reconciliation report showing every batch loaded and matched, with any mismatch explained.
Catch up incremental changes
Repeat the load for rows that changed or arrived after the full copy, using the timestamp or key recorded during inventory, and handle deletes and updates according to the table design. Repeat the catch-up until the delta is small enough that the cutover window can absorb it. The migration owner confirms the last catch-up run, and the output is a recorded high-water mark for each table.
Run a parallel comparison period
Keep the old source live while ClickHouse receives the same incremental loads, and run the key reports and queries against both systems on the same inputs. Investigate every difference in totals, time zones, null handling or rounding before accepting it. The business owner of each report signs off that results match within the agreed tolerance, and the output is a comparison log that becomes the evidence for cutover.
Cut over clients and freeze the source
Point dashboards, services and users at ClickHouse, make the source read-only, and run one final incremental load to confirm the high-water mark. Confirm that the cutover queries return expected results and that the rollback path, which keeps the source intact, is still available. The migration owner and the service owners sign off on the cutover record.
Decommission the old source
After the agreed period of stable operation, take a final backup or export of the source where your retention policy requires it, remove the clickhouse-local load credentials and host access, and retire the source. Record where the final export is kept and who may restore it. The data owner and security lead sign off, and the output is a closed migration record with access revoked and the retained copy documented.
One-time copy versus staged loads with catch-up
A one-time copy fits data that will not change while it moves, such as closed fiscal years, archived log files or a frozen export from a source system. You run clickhouse-local once, read a bounded set of files or a fixed query, insert the rows into the target, and then compare counts and checksums before the source is retired. Because nothing changes between reads, you do not need watermark columns, and the whole job can be rerun from scratch if a step fails.
- Freeze the scope: Confirm in writing that the source partitions or files in scope will not be written to, or export them to files first so the copy reads a stable snapshot.
- Record the boundary: Keep the exact file list, row counts and a checksum per partition so the copy can be proved complete later.
- Use bounded reads: Filter by partition or date range in each run so one failure does not force a full reread of the source.
- Load to a staging table first: Insert into a staging table, validate it, then move partitions into the final table so a bad run never reaches users.
- Plan the rerun: Drop and reload a partition rather than appending to it, so a retry does not create duplicates.
Before you call the copy complete, confirm with the data owner that the frozen scope matches what they expect, and that row counts and aggregate checksums match between source and target for every partition.
A staged load fits tables that keep receiving writes during the migration. You run a bulk backfill first, then repeated incremental passes that pick up only rows changed since the last pass, and a final cutover pass that runs while writes are paused or briefly queued. The pattern depends on a watermark: a column such as an insert timestamp or a monotonically increasing ID that tells you where the last pass stopped.
- Choose the watermark carefully: An update timestamp is only safe if the source sets it on every change and clocks are consistent. A pure auto-increment ID misses updated rows. Test both on a sample before you rely on either.
- Overlap the window: Re-read a short overlap behind the stored watermark so late-committed rows are not skipped, then rely on idempotent inserts to absorb the duplicates.
- Store the watermark outside the job: Record the last successful value in a control table in the target, updated only after the insert succeeds.
- Make inserts idempotent: Use a key that identifies each source row and a table engine that collapses duplicates, such as ReplacingMergeTree with a version column, so a rerun produces the same end state.
- Pause for cutover: Run a final catch-up pass after writes stop, then compare counts before switching readers.
Before go-live, test one forced rerun of an incremental pass and confirm the target row count and checksums still match the source.
Watermark-based loads see inserts and updates but not deletes, because a deleted row no longer appears in the source query that filters on a timestamp or ID. If deletes matter to reporting or to a compliance record, you must choose how the target learns about them. The answer depends on whether the source keeps tombstones, soft-delete flags or change logs, and on how long deleted rows must remain queryable.
- Soft deletes: If the source sets a deleted flag and updates the timestamp, the incremental pass carries the deletion across like any other change.
- Periodic key comparison: If the source hard-deletes, compare the full set of source keys against the target on a schedule and remove target rows whose keys are gone. This costs more reads and should be scoped by partition.
- Change logs: If the source exposes a change feed or binary log, consume it instead of polling, but confirm retention on the source side covers your catch-up window.
- Record the decision: Write down whether deletes are propagated, how often, and whether deleted data must be purged from backups, which is a question for your data protection owner.
- Verify deletes separately: Count deleted keys in both systems at cutover, since row count checks alone can hide missing deletions.
Ask your source database team which delete mechanism the source uses and how long it keeps history, because that answer decides the whole delete design.
What drives the effort and duration of the move
This table lists the factors that change how much work a clickhouse-local migration takes, whether each one is paid once or keeps recurring, and what to ask for in a written quote. Read it before comparing estimates, because two migrations of the same size can differ sharply on these drivers. The main takeaway is that the source-side and downstream query work often costs more than the copy itself.
| Driver | Why it changes effort | One-time or recurring | What to ask for in a quote |
|---|---|---|---|
| Data volume and partition count | Larger tables mean longer pulls, more batches and more checks on each load. Partitioning by date affects how the job can be split and resumed. | One-time for the initial load, recurring if the feed is repeated | Batch plan, expected load windows, and how resume after failure is handled |
| Number of source tables and files | Each table needs its own extract query, type mapping, target DDL and load step, so effort grows with table count more than with row count. | One-time | Per-table inventory with a complexity rating and the list of tables that are out of scope |
| Type and schema mapping | Source types such as decimals, timestamps with time zones, JSON and enums need explicit mapping. Nullable and LowCardinality choices affect both correctness and storage. | One-time | Written type map for each source table and a list of values the mapping cannot represent |
| Change rate on the source | Tables that receive updates or deletes need an incremental strategy, watermark columns or a re-extract window. Constant change raises the cost of every reconciliation run. | Recurring while both systems run in parallel | How changes are detected, how deletes are captured, and the cutover freeze window |
| Network path and bandwidth | The clickhouse-local host must reach the source and the cluster. Firewall rules, transit links and cross-zone traffic set transfer speed and the number of retries. | One-time for setup, recurring for transfer costs and monitoring | Network diagram, approved flows, bandwidth assumptions and who opens and closes the rules |
| Validation depth | Row counts alone miss type drift and silent truncation. Checksums, sampled value comparisons and business aggregates each add build and run time. | One-time for the build, recurring for each reconciliation run | Validation checks per table, pass criteria, and the sign-off owner |
| Downstream queries to rewrite | Reports and dashboards written for the source engine need changes for ordering keys, joins and functions. Each query is tested against the new results. | One-time, with ongoing changes for new queries | Count of queries in scope, the query owners, and the result-comparison method |
| Cluster and replica topology | Each shard and replica receives data through the right distributed or replicated table path. More nodes mean more load targets and more checks after each run. | One-time for setup, recurring for operations | Target topology, which tables are replicated, and how load is spread across shards |
| Credentials, secrets and access scope | The job needs source and target credentials with narrow rights. Rotating them and auditing their use adds steps to every run. | One-time setup, recurring rotation | How secrets are stored on the host, who can run the job, and the audit trail produced |
| Scheduling and rerun handling | Repeated runs need idempotent loads, so a failed batch does not duplicate rows. Scheduler choice and alerting shape the operating effort. | One-time build, recurring operation | Rerun behavior, alert routing, and the runbook for partial failures |
What usually goes wrong with ETL from a staging host
A staging host running clickhouse-local holds credentials to the source and the target at the same time, and it converts data on the way through. Most failures in this setup come from silent conversions, retries that duplicate rows, overly broad access and a restore path nobody has exercised.
Silent type coercion
clickhouse-local will read a source column into whatever type the query produces, and a mismatched target type can truncate, cast to a default or drop values without an error. Nullable columns, decimals and enums are the usual sources of surprise. Before any load, compare the schema the source reports with the target table definition, then run a row count and a checksum on a sample of wide and nullable columns after the first load. Treat any difference as a blocker.
Timezone and precision drift
Timestamps copied through a staging host can shift when the host, the source session and the target server use different timezone settings, and high-precision values such as microsecond timestamps or large integers can lose digits in intermediate formats. The result is data that loads cleanly and joins to the wrong hour or key. Fix the session timezone explicitly in every pipeline run, pin the precision of each timestamp column and compare min and max values on both sides for each batch.
Duplicate rows after retries
A failed or timed-out batch that is retried without a plan will insert the same rows twice, because plain MergeTree tables do not deduplicate on the key. This is the most common cause of inflated counts after a migration. Give every batch a deterministic window such as a day or a primary key range, load each window with a delete or partition replacement step first, and confirm that a rerun of the same window leaves row counts unchanged.
Broad credentials on the staging host
The staging host needs read access to the source and write access to the target, so a compromise of that host exposes both. Create a dedicated ClickHouse user with insert rights limited to the staging tables, set a settings profile and quota for it, and avoid reusing the default user, which starts with all permissions. Use a read-only source account scoped to the tables being moved and store its password in a secrets manager rather than in shell history or scripts.
Load that outruns the cluster's merges
Pushing many small inserts from a fast staging host can create parts faster than background merges consolidate them, which slows queries and can trigger insert throttling on the cluster. Batch inserts into large blocks, pause between windows when the part count rises, and watch the number of active parts on the target during the run. Agree a maximum load rate with the platform owner before the first run rather than tuning it during an incident.
No restore test for the target
A migration target that has never been restored from a backup gives no assurance that the migrated data can be recovered. ClickHouse BACKUP and RESTORE should be run against the loaded tables, and the restored copy should be compared with the source on row counts and key aggregates. Replication protects against a lost node but not against a bad load or a dropped table, so schedule the restore test on a spare cluster before cutover.
Questions teams ask before moving data with clickhouse-local
How much effort does a clickhouse-local migration take?
Effort depends mainly on the number of sources, the size and shape of each dataset, and how much type mapping and cleanup the target schema needs. Most of the cost is labor: someone designs the table mapping, writes the extraction queries, sets up scheduling, and checks row counts and sample values against the source. Recurring costs include the host that runs clickhouse-local, the cluster capacity that stores the data, and maintenance whenever a source schema changes. When you ask for a quote, request a list of sources, data volumes per source, required transformations, validation steps, and whether ongoing loads are included. DeployOpen agrees a fixed scope and deliverables in writing before work starts, so that written scope is the basis for any quote.
How long does it take to move data with clickhouse-local?
The timeline is set by the number of sources, the volume of data to copy, how much read load the source can tolerate, and how many validation rounds the business needs. The initial load depends on network throughput between the intermediate host and the source, and on the source database's capacity during the copy window. ClickHouse's production guidance recommends testing with representative data and queries before production use, which adds a rehearsal stage that should be planned from the start. Confirm load windows with the source owners before anything else. Ask for a schedule with milestones in the written scope, and make sure each milestone names the check that closes it.
What does our team need to provide for the migration?
Your team supplies access and decisions that only your organization can give. The main items are a read-only account on each source, a network path from the intermediate host to the source and the cluster, a target cluster endpoint with an insert-scoped user, and named owners for the schemas and business definitions. You also need someone who signs off reconciliation results and approves the cutover window. Ask the implementation partner for a written list of these inputs with owners and due points, so gaps show up before the first load rather than during it. Also name one owner for the intermediate host, who handles patching, credentials and access reviews after the migration ends.
Can our internal team run this migration alone?
Yes, if the team has strong SQL skills, working knowledge of ClickHouse table design, and enough time to test. The skills that matter most are choosing ordering keys and column types, writing repeatable extraction queries, scheduling loads, handling credentials securely, and reconciling results against the source. The main risks for an internal team are loads that are never validated end to end and cutover decisions made without a rehearsal. A useful first step is a pilot on one dataset with a representative query set, which shows the actual effort before you commit to the full scope. If the pilot exposes gaps in those skills, a partner can take on the design and validation work while your team keeps ownership of the pipelines.
Does clickhouse-local replace a full ETL platform?
For a small number of sources and scheduled pulls into ClickHouse, clickhouse-local can run SQL against a source or files and insert the results into the cluster, so a separate ETL platform may not be needed. Platforms usually add features that matter once pipelines multiply, such as a central interface, dependency management, managed retries, alerting, and a catalog of prebuilt connectors. Confirm which of those your team needs before choosing. If you need many pipelines with shared retries and run history, an orchestrator is often the better fit for that layer, with clickhouse-local still doing the data movement. Write the pipeline count and failure-handling requirements into the scope so the decision rests on your workload.
How do we keep the source network separate from the ClickHouse cluster?
Run clickhouse-local on a dedicated intermediate host that has outbound connections to the source and to the cluster, so neither network needs to accept inbound traffic from the other. Give the source a read-only account, give the cluster a user limited to inserting into the target tables, and store both sets of credentials in a secrets store on the host. Restrict host firewall rules to the exact ports each side needs, and enable TLS wherever the source and cluster support it. ClickHouse access control recommends managing users and roles through SQL and restricting the default user, which starts with all permissions. Test each connection from the intermediate host and record the results as part of the cutover evidence.
What changes for ClickHouse version compatibility during the move?
The clickhouse-local version should match a supported ClickHouse line that you plan to run, and both the intermediate host and the cluster should stay within the support window. ClickHouse ships stable releases roughly monthly, and its LTS releases ship twice a year and are supported for a year after release. The security policy lists which versions receive security updates, so check it before you pin a version. Read the changelog before any upgrade, because minor backward-incompatible changes can occur. Rerun your pipelines on representative data after each version change, and keep the previous binary available until validation passes.
Matching the clickhouse-local version to the cluster before you load
Before any data moves, confirm that the clickhouse-local binary on the staging host and the target cluster run the same release line, and that line is one the ClickHouse security policy lists as supported. The policy currently lists versions 26.9, 26.8, 26.7 and 26.3 as receiving security updates and marks all others as unsupported, so a staging host pinned to an older build is a risk in its own right. Pin the binary version in your staging host's configuration, record it alongside each migration run, and check the cluster's version with SELECT version() on every node so that a mixed-version cluster is caught before the first insert. Because the staging host runs the same engine that parses and converts your source data, a mismatch can change how types or functions behave between the two ends.
Your pre-production check should use representative data and queries, as the ClickHouse production FAQ recommends. Load a sample of the largest and most type-diverse tables from the source into a non-production cluster that matches the target version, then compare row counts, checksums and a set of business queries against the source. Read the changelog for every release between the version you are running and the target, because the FAQ notes that minor releases can include backward-incompatible changes. Decide in advance who signs off on the staging version and who approves a change to it.
Restore testing belongs in this check as well. The backup documentation warns that a backup never test-restored will probably not restore properly when it is needed, and it recommends regular restore tests on a spare cluster. Before you rely on clickhouse-local as the only copy of a migrated dataset, take a BACKUP of the target database to storage that is separate from the cluster, restore it to a spare cluster on the same version, and confirm that the restored row counts and key queries match. Ask your platform team to confirm where backups are stored, how long they are kept, and who runs the restore drill, since this work recurs with each upgrade and not only once.
Official ClickHouse sources for this migration
The ClickHouse documentation sets the rules that a clickhouse-local migration has to respect: which version to run, how the target cluster replicates and recovers, how backups are proven, and who can connect. Read these before you give a source credentials or open a path into the cluster.
Choose the version for both ends
Pin clickhouse-local to the same major line as the target cluster, or to a version inside the window the security policy still covers. The production FAQ recommends stable releases for most production use and advises reading the changelog first, because minor backward-incompatible changes can occur. Check the exact version of the binary on the migration host, not only the cluster version.
Confirm what a replica recovery covers
Replication keeps the target available, but it does not stop a bad ETL run from writing wrong rows to every replica. Confirm how ReplicatedMergeTree tables and ClickHouse Keeper behave in your topology, and plan a load into a staging table before the production insert. Ask your team who approves a load that has already replicated.
Plan backups before the first load
Replication does not protect against human error or corruption, so take a BACKUP of the target database before a bulk import and keep it until the migration is validated. The backup guidance is explicit that a backup never test-restored will probably not restore when needed. Schedule a restore into a spare cluster as part of the cutover checklist.
Scope the ETL user's permissions
The host running clickhouse-local should authenticate as a dedicated account with only the grants needed for the target tables, not as the default user, which starts with all permissions. Use roles and settings profiles so the loader can insert into staging databases and nothing else. Review these grants again when the migration ends and the account is retired.
Review the security policy
Before the migration host is connected, check which versions receive security updates and how vulnerabilities are reported. The policy lists the supported lines, and any other version is unsupported. Record the version you deploy on the migration host and set a reminder to remove it once the load is complete.
How DeployOpen scopes and runs the migration
DeployOpen plans and runs clickhouse-local ETL work as a written, fixed-scope engagement inside your own network, with the source access, target cluster and host boundaries agreed before any data moves. The work ends with a tested pipeline, documentation and a handover your team can run without us.
How we scope
- Inventory the source systems, tables or files, row volumes and change patterns that clickhouse-local will read.
- Agree the host placement, network paths, credentials and firewall rules so the cluster never opens to the source network.
- Confirm the target ClickHouse version and the data-model changes, such as type mapping and ordering keys, in the written scope.
How we build
- Write and version the clickhouse-local scripts, with source queries, insert statements and checkpoint logic kept in your repository.
- Run the initial copy and incremental loads in a pre-production environment with representative data before any production run.
- Reconcile row counts, checksums and sample query results between source and target, and record the acceptance criteria used.
How we hand over
- Document the runbook covering scheduling, rerun and recovery steps, credential rotation and host patching for the ETL host.
- Walk your operators through a supervised production load and a deliberate rerun so they can repeat the process alone.
- Deliver the scripts, configuration templates and validation queries together with the written handover record.
How we support
- Offer ongoing support for the ETL host, scripts and clickhouse-local version changes as a retainer, if your team wants it.
- Review the changelog and test scripts before any ClickHouse or clickhouse-local upgrade that could change behavior.
- Confirm with your team at scoping which retention, audit and access requirements apply to the ETL host and its logs.
