How Spark reads and writes ClickHouse for ClickHouse integration work
Spark jobs that need to read from or backfill a self-managed ClickHouse cluster have two documented options, and the choice between them shapes how much of the work your team owns. The Spark connector is the recommended method. It implements the Spark DataSourceV2 API and manages its own catalog configuration, so ClickHouse tables can be referenced from Spark SQL and DataFrame code without hand-built connection logic for each job. The second option is Spark JDBC, which uses a generic JDBC data source against the ClickHouse driver. Both paths run inside infrastructure you control: the Spark driver and executors, the ClickHouse servers, the network between them and the credentials Spark uses all sit in your cloud accounts, data centers or restricted networks.
Before choosing, confirm three things with your platform owners. First, the connector release must match your Spark line, and Spark 4 requires Scala 2.13 builds, so the Scala version of your job artifacts is a gating check. Second, confirm which Spark deployment model you run (standalone, YARN or Kubernetes), because it determines where the connector jar is distributed and how secrets reach executors. Third, decide who owns the ClickHouse user that Spark authenticates as. Give that account only the privileges a job needs, following the access control model in ClickHouse, rather than reusing an administrative account. The version and artifact pairings are listed in the ClickHouse Spark integration docs, and those pages should be the reference for the exact connector coordinates your team pins.
The practical trade-off is control over the load path. The connector gives Spark a catalog view of ClickHouse tables, which suits repeatable backfills and joins across sources. JDBC is simpler to reason about when a team already runs JDBC jobs against other databases, but it needs more explicit tuning of batch sizes and partitioning, and it does not give you the catalog behavior. Whichever you choose, run a small pilot against a pre-production copy with representative partitions and measure insert behavior before scheduling a full historical load.
Prerequisites for a Spark to ClickHouse connection
A Spark to ClickHouse connection depends on decisions made on both sides before any job runs. The cards below cover the version pairs, the Spark cluster manager, the network path and TLS, and the ClickHouse user that the connector authenticates as.
Pin supported ClickHouse lines
Choose a ClickHouse line from the supported list in the security policy. At the time of the supplied facts, versions 26.9, 26.8, 26.7 and 26.3 receive security updates and all other versions are unsupported. Prefer a stable release for most production use, or an LTS line if your change window is longer. Record the exact server version in your runbook, because the connector must match it and upgrades on the ClickHouse side can change behavior. Confirm the server line before you pick a connector.
Pair Spark and Scala versions
Spark 4 is built with Scala 2.13 and no longer supports Scala 2.12, so any connector artifact, application jar or custom code must target 2.13 for Spark 4. Spark 3.5.x remains available with an extended LTS that receives security fixes only. Decide early which Spark line your platform will run for the next few quarters, since the connector artifact name encodes both the Spark and Scala versions. Match the Scala suffix to the Spark line before you build.
Confirm the connector artifact
ClickHouse recommends the Spark connector, which implements DataSourceV2 and manages its own catalogs, over Spark JDBC. Before building, open the ClickHouse Spark integration documentation and copy the exact artifact coordinates and the version compatibility table for your Spark and ClickHouse lines. Do not assume that a version from one Spark line works on another. Record the coordinates in your dependency management so every driver node and the driver program load the same jar.
Choose the cluster manager
Spark's cluster overview lists three cluster managers: the standalone manager, Hadoop YARN and Kubernetes. The choice decides how the connector jar reaches executors, how secrets are injected, and who restarts failed drivers. On Kubernetes, the Apache Spark Kubernetes Operator is an Apache-2.0 subproject that manages applications through the operator pattern. Spark does not provide a notebook, scheduler UI or catalog, so plan those components alongside the cluster manager you choose.
Open the network path and TLS
Spark driver and executor nodes must reach the ClickHouse HTTP or native ports on every replica they will read from or write to, and the path should cross only the firewall rules you have approved. Decide whether traffic is encrypted with TLS and which certificate authority signs the ClickHouse server certificates, then make the trust store available to the driver and every executor. Test the path from each worker node, not only from the driver, because a single blocked subnet causes partial failures during large backfills.
Create a scoped ClickHouse user
Do not let Spark jobs authenticate as the default user, which starts with all permissions. Create a dedicated user with roles that grant only the database, table and operations the job needs, such as SELECT for reads and INSERT for backfills into named tables. Apply a settings profile and quotas to limit runaway queries, and restrict the password or certificate handling through your secrets manager rather than job code. Review the grants against the access control model before the first production run.
Configuring the Spark connector step by step
This sequence takes a Spark environment from no ClickHouse access to a verified read and a verified write, with each step closing on a check that proves it worked. It is written for the engineers who will own the integration after the build.
Choose the connector or Spark JDBC
Decide between the Spark connector and Spark JDBC before anything is installed. ClickHouse documents the Spark connector, which implements DataSourceV2 and manages its own catalog, as the recommended path, so choose JDBC only if you have a specific reason, such as a constraint your platform team already enforces. Record the decision along with the ClickHouse version, the Spark version and the cluster manager you will use. This step is done when the team agrees on the choice in writing and the target Spark and ClickHouse versions are fixed for the rest of the build. Confirm the supported version pairs against the ClickHouse Spark integration documentation rather than assuming them.
Add the artifact matching your Spark and Scala versions
Add the ClickHouse Spark connector artifact to the Spark classpath, either through the job's dependency configuration or the session's package settings. The artifact must match the Spark line and the Scala build your cluster runs, and Spark 4 is pre-built with Scala 2.13 with Scala 2.12 dropped, so a mismatch is a common cause of failures at startup. Your platform or data engineering team usually owns this step, and it is repeated for each Spark image or job runtime you maintain. It is done when a minimal Spark session starts with the connector loaded and no class-not-found errors appear in the driver log.
Register the ClickHouse catalog in the Spark session
Configure a catalog entry in the Spark session that points at your self-hosted ClickHouse endpoint, including host, port, protocol and database. Keep credentials out of job code by passing them through your secrets mechanism or cluster configuration, and confirm the exact configuration keys in the ClickHouse Spark documentation for your connector version. The data engineering lead and the platform team usually agree on the catalog name, since jobs will reference it. This step is done when Spark lists the namespaces and tables from ClickHouse, which proves the catalog resolves and the network path is open.
Create a read-only user and a write user
Ask the ClickHouse administrator to create two separate users rather than sharing one account. The read-only user receives only the grants needed to select from the source tables, and the write user is limited to the staging database or table used for loads. ClickHouse access control supports users, roles and row policies, and its documentation recommends restricting the default user, which starts with all permissions. Store each user's password in your secrets system and confirm that the default user is locked down. This step is done when each account can perform its intended action and the read-only account is refused an insert in a test.
Run a test read on a small table
Using the read-only user, read a small table through the Spark catalog and check row counts, column names and data types against a direct query run in the ClickHouse client. Pay attention to nullable columns, dates and decimal precision, since type differences are the most common surprise when Spark reads ClickHouse data. Keep the query small and filtered so the test does not add meaningful load to the cluster. This step is done when the Spark result matches the ClickHouse result for the sampled table, and any type mapping differences are written down for the data team.
Run a test write to a staging table
Using the write user, write a small sample DataFrame to a dedicated staging table that has the same schema as the production target. Do not write to a production table during the first test. After the write, count the rows and compare sample values against the source DataFrame, then confirm that the write user cannot touch tables outside the staging scope. Record the write mode, batch size and any partitioning you used, because these settings carry into the backfill pattern. This step is done when the staging table contains exactly the rows written, with matching values, and the permissions test passes.
Document the working configuration and hand it over
Write down the artifact versions, Spark and Scala versions, catalog settings with secrets referenced rather than embedded, user grants and the results of each test. Store the record alongside the job code so upgrades of either Spark or ClickHouse can be checked against it. Plan a repeat of the test sequence after each Spark or ClickHouse upgrade, and confirm the upgrade path with the ClickHouse production guidance on reading changelogs before upgrading. This step is done when a second engineer can reproduce the connection from the document alone.
Mapping Spark types and ClickHouse table settings
This table pairs common Spark data types with the ClickHouse column types, engine and ordering key that the write path expects. Read each row as a decision to settle before the first load, and treat the mapping as a hypothesis until a sample load confirms it. The main takeaway is that type and key choices are costly to change after a backfill.
| Spark side | ClickHouse column | What to verify |
|---|---|---|
| StringType | String | Confirm how empty strings and nulls load, and whether the column needs Nullable. |
| Nullable Spark fields | Nullable(T) | Wrap only columns that contain nulls, since Nullable adds storage and can slow some queries. |
| DecimalType(p, s) | Decimal(p, s) | Keep precision and scale identical on both sides and check the connector's supported range. |
| TimestampType | DateTime64 with stated precision | Spark applies a session time zone, so fix the zone on both sides and test a daylight saving boundary. |
| DateType | Date or Date32 | Test the earliest and latest dates in your data to confirm the chosen type covers them. |
| ArrayType, MapType, StructType | Array(T), Map(K, V), Tuple or Nested | Complex fields map differently by type, so confirm each one in the connector docs before backfill. |
| Target table engine | MergeTree family, ReplicatedMergeTree on replicated clusters | Replicated tables depend on ClickHouse Keeper, so confirm the engine in the DDL before writing. |
| ORDER BY (ordering key) | Set when the table is created | Pick columns from the most common filters, and agree the key with query owners before the first backfill. |
| Sample load | Verification step | Compare row counts, null counts, sums and time values against the source before running the full load. |
Batch loading patterns for backfills into ClickHouse
Backfills from Spark into ClickHouse are safest when they are split into partition-sized units that can each be rerun on their own. Load one partition or one date range per Spark job, write it to a staging table with the same schema, verify row counts and checksums against the source, and only then move the verified partition into the target table. ClickHouse supports ALTER TABLE ... REPLACE PARTITION and MOVE PARTITION operations for this kind of swap, so a failed run never leaves a half-written partition visible to queries. Confirm the exact statements and their restrictions (matching partition keys and table structure) against the ClickHouse documentation for your server version before you build the job around them.
Idempotent reruns depend on deciding, before the first run, what identifies a unit of work. A partition key, a source extract ID and a load timestamp stored in a control table let the scheduler skip completed units and re-execute failed ones without duplicating rows. Batch size is a tuning question rather than a fixed number: a large single write produces a large number of new parts that background merges must combine, which increases merge pressure and can slow queries and inserts while the work is running. Smaller, ordered batches that land in the same partition keep the part count manageable. Test batch sizes on representative data in a pre-production environment and watch the parts and merge activity in the system tables while you do.
Plan the backfill window with the operations team as well as the data team. Staging tables and swapped partitions double the storage needed for the partitions in flight, and running backfills alongside live ingestion competes for the same merge and disk capacity. Ask your Spark and ClickHouse administrators to agree on concurrency limits, a maintenance window if the cluster is shared, and a rollback path that keeps the previous partition until the new one has passed validation. Also confirm how replicated tables behave during the swap, since the partition operation must reach every replica before queries see a consistent result.
Securing the Spark access path
A Spark job that reads or writes ClickHouse is one more client of the cluster, so it needs the same access discipline as any application. The controls below cover who the job authenticates as, how the connection is protected, where credentials live, and how shared clusters keep one job from affecting others.
Dedicated least-privilege user
Create a separate ClickHouse user for each Spark application or team, not a shared account reused across jobs. Grant only the databases and tables the job reads or writes, and separate read-only extraction jobs from backfill jobs that insert. Review grants when a pipeline changes, because a backfill that once needed INSERT on one table often ends up with broader rights than it needs. Ask your team to document which role each Spark job uses before the first production run.
Restrict the default user
The default user starts with all permissions, so a cluster left with it enabled gives any Spark job or stray client administrative reach. Disable or restrict the default account and confirm that no connector configuration falls back to it. Check the connector settings during testing, since a missing username can silently cause a connection to use an account you did not intend.
TLS on the Spark connection
Spark jobs usually cross network segments, from executors on Kubernetes or YARN nodes to ClickHouse servers, so plaintext traffic can expose both credentials and data. Enable TLS on the ClickHouse HTTP and native interfaces and configure the connector to verify the server certificate against your internal CA. Confirm the certificate names match the hosts Spark resolves, and test failure behavior with an incorrect CA before go-live.
Credentials outside application code
Passwords committed to job code, notebooks or images are hard to rotate and easy to leak through logs. Pass ClickHouse credentials through Spark configuration injected at submit time, or pull them from your secrets manager at runtime, and keep them out of the code repository. Rotate them on a schedule and on staff changes, and make sure driver and executor logs do not print connection properties.
Row policies and quotas on shared clusters
When several teams share a cluster, a large backfill can starve dashboards and other pipelines. Use row policies to limit which rows a Spark role can see when tables hold mixed tenants or sensitive records, and use quotas to cap queries, rows or execution time for the role. Set these values from measured backfill behavior in a pre-production environment, not from guesses, and confirm the limits do not break the job's own partitioned reads.
External authentication options
If your organization already centralizes identity, ClickHouse can authenticate users through LDAP directories, Kerberos, SSL X.509 certificates, an HTTP authenticator or JWT tokens. Decide early which mechanism fits your Spark deployment, since Kerberos and JWT setups depend on how executors obtain tokens. Confirm with your identity team which options your connector version supports for the authentication method you choose, and test token expiry during long-running jobs.
Common Spark to ClickHouse failures and fixes
This table lists the failures teams most often hit when Spark reads or writes ClickHouse, with the likely cause and the fix for each. Use it as a triage checklist before changing cluster settings, and confirm exact connector and driver behavior against the version you deploy.
| Failure | Likely cause | Fix |
|---|---|---|
| Connector fails to load or throws class errors | Connector artifact built for a different Spark or Scala line than the cluster runs, for example a Scala 2.12 artifact on Spark 4 builds that use Scala 2.13 | Pair the connector with the exact Spark and Scala versions in the ClickHouse Spark docs, and confirm the Spark 4 Scala 2.13 requirement before upgrading |
| Authentication rejected at job start | Wrong user, password or database in the catalog or JDBC options, or a user without grants on the target database | Create a dedicated Spark user with only the grants it needs, store credentials in the Spark secrets mechanism, and test the login with clickhouse-client first |
| TLS handshake or certificate errors | Connection uses the HTTPS or secure port without the cluster CA in the Spark driver and executor trust stores, or the certificate name does not match the host | Distribute the CA certificate to driver and every executor, set the secure flag and port together, and confirm the certificate subject matches the hostname Spark uses |
| Type mismatch on write | Spark column types (for example decimals, timestamps with time zones or nullable fields) do not match the ClickHouse column definitions | Cast DataFrame columns to the target types before writing, and check Nullable and precision settings on both sides with a small sample write |
| Read timeouts on large tables | A single query scans too much data in one task, or the HTTP read timeout is shorter than the time needed to stream a large result | Partition reads by a key with a good distribution, push filters into the query, and raise the client timeout only after confirming the scan is bounded |
| Executor out-of-memory or slow tasks | Batches too large for executor heap, or too many concurrent tasks writing to the same ClickHouse parts | Reduce batch size and partition count, lower parallelism, and size executor memory against the largest batch a task holds |
| Duplicate rows after a retried backfill | ClickHouse MergeTree does not deduplicate on insert, so a rerun of a failed or speculative task writes the same rows again | Make backfills idempotent: write to a staging partition, verify counts, then replace the partition, or use ReplacingMergeTree with a versioned key and confirm deduplication timing with queries using FINAL |
Questions buyers ask before connecting Spark to ClickHouse
What drives the cost of a ClickHouse and Spark integration project?
The cost follows the written scope, and four factors move it most: the number of ClickHouse clusters and Spark environments involved, the number of pipelines that read or write, the data types and nested or nullable columns that need mapping, and how much testing the team expects before go-live. A single batch job that reads one table is a small scope. A set of backfills writing partitioned tables from several Spark jobs, with reconciliation checks and a restore test, is a larger one. Setup work is one-time. Upgrades, new pipelines and monitoring recur. Ask any quote to list each pipeline, the data types in scope and the test cases separately so you can compare options on the same basis.
How long does a Spark to ClickHouse integration take?
Duration depends on the same drivers as cost: the number of pipelines, the schema complexity, the state of the source data and how many environments need validation. The ClickHouse documentation describes the connector and JDBC options, but it does not give a timeline for your environment, so no duration can be stated here. Ask for a schedule that separates a proof of concept on one table, the production pipelines, the backfill runs and the acceptance tests, and ask which of those steps depend on access your team has to grant.
Can our in-house data team build this without a partner?
Yes, many teams do, provided they have engineers who know Spark, ClickHouse table design and the operations of both platforms. The work that tends to take the longest in-house is not the first read or write. It is choosing partitioning and ordering keys that suit batch loads, proving that a backfill can be rerun without duplicates, and setting up credentials, TLS and monitoring to the standard your security team requires. If those skills are thin, a fixed-scope engagement can cover them while your engineers take over the runbooks. Be honest about which of these skills you already have.
What do we need to provide before the integration work starts?
The work needs three things from your side. First, access to the environments: Spark clusters or the platform that runs them, the ClickHouse cluster in a non-production form, and the networks and credentials needed to reach both. Second, representative data samples, including the schemas, sample rows with the awkward values such as nulls, decimals and timestamps with time zones, and expected row counts per partition. Third, named owners on your side for the data, the ClickHouse cluster, the Spark platform and the security approvals. Missing any one of these usually delays testing more than the build itself.
Should we use the Spark connector or Spark JDBC for ClickHouse?
The ClickHouse documentation describes two options: the Spark connector, which implements DataSourceV2 with its own catalog management, and Spark JDBC. The documentation recommends the connector. Choose JDBC only when the connector cannot run in your environment, for example because your Spark version or deployment does not match the connector build you need, or because a standard JDBC driver is already approved by your security team and the connector is not. Compare both against your Spark version, your ClickHouse version and the operations you need, such as catalog-based table access. Confirm the pairing for your exact versions before you commit.
How do Spark or ClickHouse upgrades affect the integration?
Each upgrade can change the connector pairing, so treat the Spark version, the connector version and the ClickHouse version as one set. Before upgrading any of them, check the ClickHouse release notes and the Spark versioning policy, which describes how long each release line is maintained. Spark has feature releases on a quarterly cadence from 4.3.0, and its LTS lines receive security fixes for a set period. The ClickHouse security policy lists which versions still receive security updates. Test the full pipeline, including a backfill and a reconciliation, on a non-production cluster before each upgrade on either side.
What security controls does a Spark to ClickHouse pipeline need?
The main controls are a dedicated ClickHouse user for Spark with only the grants it needs, rather than the default user, which starts with all permissions. ClickHouse access control covers users, roles, row policies and quotas, so limit the Spark account to the target databases and tables and consider a quota for batch loads. Store the credentials in your secrets system, use TLS between Spark and ClickHouse, and confirm how your organization authenticates service accounts. Ask the implementer which external authenticator your environment uses and how the Spark job's secrets are rotated.
What does a backfill from Spark need to avoid duplicate rows?
A backfill that writes to ClickHouse needs a repeatable load pattern before it runs at scale. Define the partition or time window each job writes, load each window in a way that can be rerun without creating duplicates, and reconcile row counts and key totals against the source after each window. Test that rerun on a spare cluster, because ClickHouse backups and restores are only trusted after a restore test. Ask any proposal to show how it handles a failed job halfway through a window, and who approves a rerun in production.
How DeployOpen scopes and delivers the ClickHouse and Spark integration
DeployOpen plans, builds, tests and documents the ClickHouse and Apache Spark integration inside infrastructure you control. The work starts with a written scope that names the connector, Spark versions, load patterns and environments before any build begins.
How we scope
- Inventory your ClickHouse version, Spark distribution and Scala build, and confirm they pair under the connector documentation.
- Define which tables are read, written or backfilled, and the expected batch sizes and windows.
- Agree in writing the catalog configuration, credential handling, network paths and acceptance tests before work starts.
How we build
- Configure the Spark connector catalog against a pre-production ClickHouse cluster with representative data.
- Build repeatable batch loading jobs for backfills, with checkpoints so a failed run can resume safely.
- Test read and write paths, type mappings and row counts against source data before any production run.
How we hand over
- Deliver runbooks covering connector versions, upgrade order across Spark and ClickHouse, and rollback steps.
- Document the job layout, configuration keys, credential rotation and the restore tests tied to backfill targets.
- Walk your platform and data teams through the deployed setup and the acceptance results.
How we support
- Ongoing support, version upgrades and continuous testing are available as a retainer after handover.
- Re-test the integration when Spark or ClickHouse moves to a new release line, using your changelog review.
- Confirm support terms, response expectations and included upgrades in the written agreement before signing.
