--- title: "Clickhouse® Integration HDFS for Sub-100ms Lake Queries" excerpt: "ClickHouse® integrates with HDFS to deliver sub-100 ms data-lake queries without replacing existing Hadoop systems." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-02-06 00:00:00" publishedOn: "2026-02-06 00:00:00" updatedOn: "2026-02-06 00:00:00" status: "published" --- 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** | Pattern | Use Case | Latency Profile | Best For | | ----- | ----- | ----- | ----- | | Direct Query (hdfs() function) | Exploratory analytics, data validation | Variable (NameNode \+ network) | Ad-hoc analysis, staging validation | | hdfsCluster() Parallel Reads | Large dataset queries across cluster | Improved (distributed reads) | Multi-TB queries, partition scanning | | HDFS Table Engine | Persistent table mapped to HDFS path | Variable (depends on cache) | Staging tables, semi-structured data | | Hive Table Engine | Query Hive metastore tables | Variable (partition pruning helps) | Lakehouse with Hive metadata | | Iceberg Table Function | Query Iceberg tables (read-only) | Optimized (manifest pruning) | Modern lakehouse architectures | | Load to MergeTree | Production serving, dashboards | Sub-100ms (local storage) | High-concurrency analytics | | Remote Disk (UNSUPPORTED) | Tiered storage with HDFS cold tier | Mixed (cache critical) | Experimental only—not production | ## **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** ```xml /etc/security/keytabs/clickhouse.keytab clickhouse@REALM.COM kerberos ``` **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 `` 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: ```xml /etc/clickhouse-server/hdfs-site.xml ``` **Step 3**: Use nameservice in URIs instead of specific hosts: ```sql 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** ```sql 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)](https://www.ibm.com/think/topics/internet-of-things), 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](https://www.cisco.com/site/us/en/learn/topics/cloud-networking/what-is-low-latency.html) 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** ```sql 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: `...`. **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** ```sql 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** ```sql 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** ```sql 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](https://www.tinybird.co/blog/real-time-dashboards-are-they-worth-it). ### **Incremental Load Pattern** ```sql 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](https://www.tinybird.co/blog/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** ```sql 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** ```xml hdfs hdfs://nameservice/clickhouse/cold/ true cache hdfs_cold /var/lib/clickhouse/cache/hdfs/ 100Gi default hdfs_cache ``` ### **TTL for Automatic Tiering** ```sql 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](https://www.ibm.com/think/topics/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): ```xml ipc.client.rpc-timeout.ms 60000 Timeout for RPC calls to NameNode (60 seconds) ipc.client.connect.timeout 20000 Timeout for establishing RPC connection (20 seconds) ``` **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`: ```xml dfs.client.read.shortcircuit true dfs.domain.socket.path /var/lib/hadoop-hdfs/dn_socket ``` **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** ```sql 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: ```bash 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: ```sql 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](https://www.tinybird.co/blog/best-database-for-real-time-analytics) for their workload patterns, concurrency targets, and operational constraints.