Clickhouse® integration with HDFS solves distinct challenges for teams with data lakes on Hadoop: (1) querying files in HDFS without loading into Clickhouse®, (2) loading data from HDFS to MergeTree tables for low-latency serving, (3) continuous ingestion from HDFS as files arrive, and (4) using HDFS as remote storage tier (with caveats).

The critical distinction: HDFS excels at distributed storage, Clickhouse® excels at analytical queries—integration patterns bridge these strengths.

Integration matters in 2025 because enterprise data lakes remain predominantly on HDFS despite cloud migration trends. Teams need sub-100ms queries over lakehouse data (Parquet, ORC, Iceberg) without rewriting entire data platforms.

HDFS provides proven durability and compliance-ready infrastructure, while Clickhouse® delivers analytical performance impossible with traditional Hive/Spark batch queries.

Common patterns include: federated queries (Parquet in HDFS queried directly), staging to serving (HDFS as landing zone before MergeTree), lakehouse integration (Hive/Iceberg tables consumed by Clickhouse®), and tiered storage (hot data local, cold data in HDFS).

This article examines integration approaches, performance considerations, and operational practices for production deployments.

Integration Patterns Overview

HDFS Architecture: Why It Matters for Clickhouse®

HDFS separates metadata (NameNode) from data (DataNodes). NameNode stores directory tree and file metadata in memory. DataNodes store actual data blocks. This architecture creates specific bottlenecks Clickhouse® integration must address.

NameNode bottleneck: Listing thousands of small files creates metadata overhead. Each file requires NameNode RPC call, memory allocation, and directory traversal. Performance degrades dramatically with small files.

DataNode reads: After metadata resolution, Clickhouse® reads blocks from DataNodes. Network bandwidth, block locality, and concurrent reads determine throughput. Co-locating Clickhouse® with DataNodes enables short-circuit reads (UNIX domain socket, bypassing network).

Small files problem: HDFS block size typically 128 MB. Files smaller than block size waste NameNode memory and create overhead. Cloudera analysis shows hundreds of small files require same metadata as single large file but deliver fraction of throughput.

Authentication: Kerberos Configuration

Enterprise HDFS runs in secure mode with Kerberos authentication. Clickhouse® authenticates via principal and keytab. Configure globally or per-user.

Kerberos Configuration Example

<Clickhouse®>
<hdfs>
<hadoop_kerberos_keytab>/etc/security/keytabs/Clickhouse®.keytab</hadoop_kerberos_keytab>
<hadoop_kerberos_principal>Clickhouse®@REALM.COM</hadoop_kerberos_principal>
<hadoop_security_authentication>kerberos</hadoop_security_authentication>
</hdfs>
</Clickhouse®>

Keytab requirements: File must be readable by Clickhouse®-server user. Verify with klist -kt /path/to/keytab. Permissions typically 0400 or 0600 owned by Clickhouse® user.

Ticket refresh: Clickhouse® 25.4 added automatic Kerberos ticket refresh on SASL errors during HDFS reads. Earlier versions may require manual ticket renewal or service restarts.

Per-user configuration: Different principals per Clickhouse® user possible with <hdfs_user> sections. Useful for multi-tenant environments requiring separate HDFS credentials.

High Availability: NameNode Failover Support

Production HDFS uses NameNode HA (active/standby) with logical nameservice. Pointing Clickhouse® to specific hdfs://host:8020/ breaks on failover. Correct approach: reference nameservice configured in hdfs-site.xml.

HA Configuration Steps

Step 1: Copy HDFS configuration files (core-site.xml, hdfs-site.xml) from Hadoop cluster to Clickhouse® servers. Place in /etc/Clickhouse®-server/.

Step 2: Configure Clickhouse® to use HDFS configuration:

<Clickhouse®>
<hdfs>
<libhdfs3_conf>/etc/Clickhouse®-server/hdfs-site.xml</libhdfs3_conf>
</hdfs>
</Clickhouse®>

Step 3: Use nameservice in URIs instead of specific hosts:

SELECT *
FROM hdfs('hdfs://my_nameservice/data/events/*.parquet', 'Parquet', 'event_time DateTime, user_id UInt64, event_name String');

Configuration discovery: libhdfs3 (Clickhouse®'s HDFS client library) searches configuration via LIBHDFS3_CONF, HADOOP_CONF_DIR, HADOOP_INSTALL, or default paths (/etc/hadoop/conf).

Service vs shell: Configuration working in shell (with HADOOP_CONF_DIR exported) may fail for Clickhouse®-server daemon (no environment variables). Explicit libhdfs3_conf path recommended.

Direct Queries: hdfs() Table Function

The hdfs() table function enables querying files in HDFS directly without creating tables. Supports SELECT and INSERT. Glob patterns for multiple files (*, **, {N..M}, {abc,def}).

Query Syntax

SELECT
count() AS total_events,
uniq(user_id) AS unique_users,
min(event_time) AS earliest,
max(event_time) AS latest
FROM hdfs(
'hdfs://namenode:9000/data/events/dt=2026-01-*/*.parquet',
'Parquet',
'event_time DateTime, user_id UInt64, event_name LowCardinality(String), properties String'
)
WHERE event_time >= '2026-01-31 00:00:00';

URI: Full HDFS path with namenode/nameservice. Port typically 8020 (RPC) or 9000 (legacy default).

Format: Parquet, ORC, CSV, TSV, JSONEachRow—any Clickhouse®-supported format.

Structure: Column definitions with types. Must match file schema or parsing fails. Parquet/ORC more forgiving than CSV due to embedded schema.

When Direct Queries Make Sense

Exploratory analysis: Data quality checks, schema validation, sampling before loading. Query without commitment to storage in Clickhouse®.

Staging validation: HDFS as landing zone—validate structure and content before incremental load to MergeTree. Catch schema mismatches early.

Federated queries: Join HDFS data with Clickhouse® tables for one-off analysis. Example: enrich internal events with external dataset in HDFS.

Cost optimization: Avoid double storage when queries infrequent. Pay network/compute cost on-demand versus continuous storage cost.

A common exploratory scenario involves device telemetry from the Internet of Things (IoT), where teams validate schema and data quality on time-partitioned Parquet before committing any long-term storage or serving changes in ClickHouse®.

When Direct Queries Don't Make Sense

High-concurrency dashboards: NameNode metadata calls, network latency, cold reads create latency variability. Sub-100ms low latency SLAs require local MergeTree.

Small files problem: Thousands of tiny files create per-file overhead (metadata lookup, connection handshake, block allocation). Throughput collapses.

Complex queries: Heavy aggregations, multiple joins, window functions over remote data perform poorly. Load to MergeTree first for production analytics.

Parallel Reads: hdfsCluster() Function

For large datasets, hdfsCluster() distributes file processing across Clickhouse® cluster. Initiator resolves glob pattern, assigns files to worker nodes dynamically. Workers read assigned files in parallel.

Cluster Parallelization Example

SELECT
toYYYYMMDD(event_time) AS date,
count() AS events,
uniq(user_id) AS users
FROM hdfsCluster(
'analytics_cluster',
'hdfs://nameservice/events/dt=2026-01-*/*.parquet',
'Parquet',
'event_time DateTime, user_id UInt64, event_name String'
)
WHERE event_time >= '2026-01-01'
GROUP BY date
ORDER BY date;

Cluster name: References Clickhouse® cluster definition in config.xml. Example: <remote_servers><analytics_cluster>...</analytics_cluster></remote_servers>.

Work distribution: Files distributed among cluster nodes. Each node processes assigned files locally. Critical for multi-TB queries where single-node throughput insufficient.

Performance Considerations

File granularity: Works best with many files per partition (100s). Few large files limit parallelism—single file read by single node.

NameNode load: Large glob patterns create metadata overhead. Thousands of files cause NameNode RPC storms. Mitigate with partition pruning in WHERE clause.

Network: Workers fetch data from DataNodes (potentially remote). Co-locating Clickhouse® nodes with DataNodes enables short-circuit reads (local access via UNIX socket, bypassing TCP).

HDFS Table Engine: Persistent Mapping

HDFS table engine creates persistent table mapped to HDFS path. Similar to File or URL engines but for Hadoop. Important: Marked "sketchy quality" and not supported by Clickhouse® engineers in official documentation—use with caution.

Table Engine Configuration

CREATE TABLE staging.events_hdfs
(
event_time DateTime,
user_id UInt64,
event_name String,
properties String
)
ENGINE = HDFS('hdfs://nameservice/staging/events', 'TSV');

Read and write: SELECT reads from HDFS. INSERT writes to HDFS. Useful for staging tables where HDFS is source or destination.

Glob patterns: If path contains wildcards (*), table becomes read-only. File list resolved during SELECT, not at table creation.

No ALTER support: Cannot modify table structure. No SAMPLE clause, no indexes. Limited functionality versus MergeTree.

Critical Limitations

Not supported in Clickhouse® Cloud: HDFS engine unavailable in managed offering. Self-managed only.

Quality warning: Official docs state "sketchy quality" and "not supported by Clickhouse® engineers". Use at own risk for non-critical workloads.

Partition BY: Supports PARTITION BY clause but creates separate files per partition. Excessive partitioning creates small files problem.

Hive Table Engine: Lakehouse Integration

For data modeled in Hive metastore, Hive table engine enables SELECT queries respecting Hive partitions and formats. Read-only access—no writes to Hive tables.

Hive Table Configuration

CREATE TABLE analytics.hive_events
ENGINE = Hive('thrift://hive-metastore:9083', 'default', 'events')
PARTITION BY dt;

Metastore connection: Thrift URL to Hive metastore service. Port typically 9083.

Database and table: Reference existing Hive database and table names.

Partition support: PARTITION BY clause enables partition pruning. Queries with partition filters read only relevant HDFS paths.

Format Support

Text formats: CSV, TSV with Hive SerDe compatibility.

Columnar formats: Parquet and ORC with partition columns. Best performance for analytical queries.

Complex types: Limited support for nested types (arrays, maps, structs). Flatten complex types for reliable querying.

Iceberg Integration: Modern Lakehouse

For Apache Iceberg tables on HDFS, iceberg() table function enables read-only queries. Leverages Iceberg metadata (manifests, snapshots) for efficient pruning.

Iceberg Query Example

SELECT
count() AS row_count,
min(event_date) AS earliest_date,
max(event_date) AS latest_date
FROM iceberg('hdfs://nameservice/warehouse/events', 'hdfs', 'nameservice:9000');

Metadata advantages: Iceberg manifests provide file-level statistics (row counts, min/max values). Clickhouse® uses for pruning—reads only relevant files.

Snapshot support: Query specific snapshot via parameters. Enables time-travel queries and consistent reads.

Read-only: No writes to Iceberg tables from Clickhouse®. Use Spark/Flink/Trino for writes, Clickhouse® for fast reads.

Loading to MergeTree: Production Serving

For production analytics, load data from HDFS to MergeTree tables. HDFS serves as staging, MergeTree provides query performance for real-time dashboards.

Incremental Load Pattern

INSERT INTO analytics.events
SELECT
event_time,
user_id,
event_name,
JSONExtractString(properties, 'utm_source') AS utm_source,
JSONExtractString(properties, 'utm_campaign') AS utm_campaign
FROM hdfs(
'hdfs://nameservice/staging/events/dt=2026-01-31/*.parquet',
'Parquet',
'event_time DateTime, user_id UInt64, event_name String, properties String'
)
WHERE event_time >= '2026-01-31 00:00:00'
AND event_time < '2026-02-01 00:00:00';

Incremental approach: Load by date partition or timestamp range. Track processed partitions to avoid reloading. Idempotent loads enable safe retries, and the pattern supports both batch loads and real-time data ingestion depending on freshness requirements.

Table Design for HDFS Ingestion

Partition key: Match HDFS partition structure. If HDFS uses dt=YYYY-MM-DD/, partition Clickhouse® by toYYYYMMDD(event_time). Enables partition dropping for data lifecycle.

Sorting key: Design ORDER BY based on query filters. Example: filtering by user_id and event_time requires ordering (user_id, toStartOfHour(event_time)).

Deduplication: HDFS may contain duplicates from failed jobs. Use ReplacingMergeTree with version column or application-level deduplication.

Cluster Parallelization for Loads

INSERT INTO analytics.events
SELECT *
FROM hdfsCluster(
'analytics_cluster',
'hdfs://nameservice/events/dt=2026-01-*/*.parquet',
'Parquet',
'event_time DateTime, user_id UInt64, event_name String'
);

Distributed reads: Workers read assigned files in parallel. Critical for multi-TB loads where single-node I/O insufficient.

NameNode consideration: Massive glob patterns create NameNode pressure. Consider loading partition by partition rather than single query spanning months.

Remote Storage: HDFS as Cold Tier (UNSUPPORTED)

Clickhouse® allows configuring HDFS as remote disk for MergeTree tiered storage. Critical warning: Marked "HDFS (unsupported)" in official documentation—production use not recommended.

Storage Configuration Example

<Clickhouse®>
<storage_configuration>
<disks>
<hdfs_cold>
<type>hdfs</type>
<endpoint>hdfs://nameservice/Clickhouse®/cold/</endpoint>
<skip_access_check>true</skip_access_check>
</hdfs_cold>

<hdfs_cache>
<type>cache</type>
<disk>hdfs_cold</disk>
<path>/var/lib/Clickhouse®/cache/hdfs/</path>
<max_size>100Gi</max_size>
</hdfs_cache>
</disks>

<policies>
<hot_to_hdfs>
<volumes>
<hot>
<disk>default</disk>
</hot>
<cold>
<disk>hdfs_cache</disk>
</cold>
</volumes>
</hot_to_hdfs>
</policies>
</storage_configuration>
</Clickhouse®>

TTL for Automatic Tiering

CREATE TABLE analytics.events
(
event_time DateTime,
user_id UInt64,
event_name String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (user_id, event_time)
TTL event_time + INTERVAL 30 DAY TO VOLUME 'cold'
SETTINGS storage_policy = 'hot_to_hdfs';

TTL rule: Data 30 days old moves from local disk to HDFS. Background merges execute moves asynchronously.

Why "Unsupported" Matters

Production risk: Clickhouse® engineers do not support HDFS remote storage. Corner cases, bugs, data corruption potential higher than S3/GCS.

Cache critical: Without cache, every query hits HDFS over network. Filesystem cache essential for acceptable performance.

Alternative recommendation: For production tiering, use S3/GCS (fully supported) instead of HDFS. If HDFS required, limit to experimental workloads.

Performance Optimization: Files and Formats

Query performance over HDFS depends on minimizing NameNode calls and optimizing DataNode reads. File layout and format choice dominate performance.

File Size Best Practices

Target 128-512 MB per file: Aligns with HDFS block size. Amortizes NameNode metadata overhead. Balances parallelism versus file count.

Small files disaster: Thousands of 1 MB files create metadata storm. NameNode memory exhausted, RPC queues saturated, query latency explodes. Cloudera analysis: 100 small files require same NameNode memory as 1 large file but deliver 10x worse throughput.

Compaction required: streaming data ingestion creates small files. Daily compaction merging small files into large files critical for query performance.

Parquet Optimization

Row group size: Target 1M rows per row group for analytical queries. Too small (100K) creates metadata overhead. Too large (10M) reduces parallelism.

Column statistics: Parquet stores min/max per column per row group. Clickhouse® uses for coarse-grained pruning—skips row groups outside filter range.

Sorting: Sort data by query filter columns before writing Parquet. Example: partition by date, sort by (date, user_id). Maximizes pruning effectiveness.

Compression: SNAPPY for balanced speed/compression. ZSTD for maximum compression (slower writes). Avoid GZIP (slow decompression).

Network and Timeout Configuration

HDFS network issues (NameNode saturation, DataNode failures, rack switches) cause queries to hang without proper timeouts.

RPC Timeout Configuration

Configure in core-site.xml (Hadoop configuration referenced by libhdfs3):

<property>
<name>ipc.client.rpc-timeout.ms</name>
<value>60000</value>
<description>Timeout for RPC calls to NameNode (60 seconds)</description>
</property>

<property>
<name>ipc.client.connect.timeout</name>
<value>20000</value>
<description>Timeout for establishing RPC connection (20 seconds)</description>
</property>

Fail fast: Prefer quick failure with retry versus queries hanging indefinitely. Orchestration layer (Airflow, Luigi) handles retries.

WebHDFS timeouts: If using HttpFS/WebHDFS gateway (HTTP instead of RPC), configure socket timeouts separately in hdfs-site.xml.

Short-Circuit Local Reads

When Clickhouse® and DataNodes co-located (same host or shared storage), enable short-circuit reads bypassing network stack.

Configure in hdfs-site.xml:

<property>
<name>dfs.client.read.shortcircuit</name>
<value>true</value>
</property>

<property>
<name>dfs.domain.socket.path</name>
<value>/var/lib/hadoop-hdfs/dn_socket</value>
</property>

Requirements: UNIX domain socket with correct permissions (hdfs user or root). DataNode and client on same host or shared storage access.

Performance gain: 30-50% latency reduction for local reads. Critical for co-located deployments.

HttpFS Gateway: HDFS Behind Firewall

Corporate environments often restrict RPC access to HDFS (internal ports, Kerberos complexity, network segmentation). HttpFS provides HTTP gateway to HDFS enabling access without native HDFS client.

HttpFS Architecture

Gateway deployment: HttpFS runs on edge node with HDFS access. Exposes WebHDFS REST API over HTTP/HTTPS.

Authentication: Supports Kerberos SPNEGO (negotiate), pseudo-authentication (simple), and delegation tokens. Example with curl --negotiate after kinit.

Clickhouse® integration: Use url() or urlCluster() table functions instead of hdfs(). Query HDFS data via HTTP without HDFS client library.

URL Function Example

SELECT *
FROM url(
'https://httpfs-gateway:14000/webhdfs/v1/data/events/dt=2026-01-31/*.parquet?op=OPEN&user.name=Clickhouse®',
'Parquet',
'event_time DateTime, user_id UInt64, event_name String'
);

Trade-offs: Adds HTTP overhead versus native RPC. Simplifies network/security. Useful when RPC impossible due to firewall/Kerberos constraints.

ViewFS and Federation: Edge Cases

HDFS federation with ViewFS creates logical namespace mounting multiple NameNodes. URIs use viewfs:// scheme mapping to underlying hdfs:// paths.

Clickhouse® limitation: ViewFS not supported via libhdfs3. Queries fail with viewfs:// URIs.

Workaround: Resolve ViewFS mount to actual hdfs://nameservice/path and use directly. Check mount configuration in core-site.xml (ViewFs mountTable).

Alternative: Use HttpFS gateway if ViewFS required—gateway resolves mounts server-side.

Atomic Commits: Rename Pattern

Prevent reading partially written files by using HDFS atomic rename as commit operation.

Staging Pattern

Step 1: Write to temporary path:

/data/events/_tmp/dt=2026-02-02/part-00000.parquet

Step 2: Rename to final path after complete write:

hdfs dfs -mv /data/events/_tmp/dt=2026-02-02 /data/events/dt=2026-02-02

Step 3: Clickhouse® queries only final paths (excludes _tmp).

Atomicity: Hadoop FileSystem rename() atomic within same filesystem root. Not atomic across encryption zones or different volumes.

Clickhouse® integration: Query patterns exclude temporary paths:

SELECT *
FROM hdfs('hdfs://nameservice/data/events/dt=*/*.parquet', ...)
-- Excludes /data/events/_tmp/ due to glob pattern

Decision Framework

Choose hdfs() Function when:

• Ad-hoc analysis over data lake without loading
• Staging validation before Clickhouse® ingestion
• Federated queries joining HDFS and Clickhouse® data
• Infrequent queries—double storage cost unjustified
• Not recommended: High-concurrency dashboards (latency variability), thousands of small files

Choose hdfsCluster() when:

• Large datasets (multi-TB) requiring distributed reads
• Clickhouse® cluster available for parallel processing
• Many files per partition (100s-1000s enabling parallelism)
• Batch analytics where latency acceptable
• Not recommended: Single-node Clickhouse®, few large files (no parallelism benefit)

Choose HDFS Table Engine when:

• Self-managed Clickhouse® (not Cloud)
• Simple staging tables reading/writing HDFS
• Non-critical workloads where unsupported status acceptable
• File-based processing without complex table features
• Not recommended: Production critical systems (unsupported), Clickhouse® Cloud

Choose Hive Table Engine when:

• Hive metastore already managing HDFS data
• Partition metadata maintained in Hive
• Read-only analytics over Hive tables
• Legacy lakehouse migration to Clickhouse®
• Not recommended: Write workloads, complex nested types heavily used

Choose Iceberg Function when:

• Modern lakehouse with Iceberg format
• Manifest-based pruning critical for performance
• Time-travel queries needed via snapshots
• Read-only analytics over evolving schemas
• Not recommended: Write workloads (use Spark/Flink for writes)

Choose Load to MergeTree when:

• Production serving with sub-100ms SLAs
• High query concurrency (multiple dashboards/users)
• Complex queries (heavy aggregations, joins, windows)
• Predictable performance required
• Not recommended: Infrequent queries (storage cost high), constantly changing data

Choose HDFS Remote Disk when:

• Experimental workloads only—not production
• Understand "unsupported" risk and have fallback plan
• Cache infrastructure in place for acceptable performance
• S3/GCS not available and tiering required
• Not recommended: Production systems (use S3/GCS instead), critical data

Conclusion

Clickhouse® integration with HDFS enables real-time queries over enterprise data lakes while maintaining compatibility with existing Hadoop infrastructure. The key patterns—direct queries, parallel reads, Hive/Iceberg integration, staging to serving—each solve specific architectural challenges while respecting HDFS's design constraints.

However, a critical principle remains: HDFS optimizes for distributed storage and batch processing, not interactive queries.

Teams should architect recognizing this trade-off: HDFS for durable lakehouse storage (Parquet, ORC, Iceberg), Clickhouse® MergeTree for query serving (dashboards, APIs, real-time analytics), and integration patterns minimizing NameNode overhead (proper file sizing, partition design, cluster parallelization). By choosing the right pattern for each use case—direct queries for exploration, MergeTree for serving, Hive/Iceberg for lakehouse compatibility—teams deliver both the durability of HDFS and the sub-100ms query latency of Clickhouse®.

The integration succeeds when teams understand they're composing complementary systems: HDFS for proven enterprise storage, Clickhouse® for real-time analytical queries, and patterns that minimize small files and leverage partition pruning. This separation delivers both lakehouse economics and interactive performance without compromising either.

For technology selection, teams should benchmark ClickHouse against the broader landscape and consider the best database for real-time analytics for their workload patterns, concurrency targets, and operational constraints.