---
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**

\<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)](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**

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](https://www.tinybird.co/blog/real-time-dashboards-are-they-worth-it).

### **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](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**

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](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):

\<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](https://www.tinybird.co/blog/best-database-for-real-time-analytics) for their workload patterns, concurrency targets, and operational constraints.