Linux, macOS, FreeBSD

Frequently asked questions

7 things to consider when choosing a cloud data warehouse

Data lakes are popular for storing [structured, semi-structured, or unstructured data](https://clickhouse.com/engineering-resources/structured-unstructured-semi-structured-data). Parquet files, which can handle large volumes efficiently, are often used to store this data. 

Each Parquet file can have its own schema, which provides much flexibility, but it becomes problematic when you query multiple files. If the schema of one file doesn’t match another, your queries can fail, making it difficult to analyze or pull insights from data spread across the lake.

Data evolves, and you might need to update or change the schema as your data grows. However, changing the schema in one file can break things at query time. If your query engine reads one file first and infers a schema, it will fail if it encounters a file with a different schema.

So, how do we handle this? The answer lies in open table formats.

## What are open table formats?

Open table formats introduce a layer of metadata management on top of your Parquet files. This ensures that all your files follow a consistent schema, even as their data evolves.

Think of it like giving your Parquet files the structure and reliability of a traditional database table. With open table formats, schema consistency is guaranteed, and you can easily evolve your schema without breaking queries or data processing. You interact with the open table format’s API to manage this consistency, rather than modifying the Parquet files directly.

This approach lets you maintain metadata governance and ensure a consistent view of your data, even as you scale and evolve your [data lake](https://clickhouse.com/resources/engineering/data-lakehouse).

## Which are the main open table formats?

Key players in open table formats include Iceberg, Hudi, and Delta Lake. Different companies developed these formats to solve similar problems with data lakes, which is why we now have multiple competing options.

### Iceberg

Netflix initially developed [Iceberg](https://clickhouse.com/engineering-resources/apache-iceberg) to address the challenges of managing large datasets in data lakes. As the project gained traction, the creators spun out a company called [Tabular](https://www.tabular.io/blog/introducing-tabular/) to continue its development. Eventually, [Tabular was acquired by Databricks](https://www.databricks.com/blog/databricks-tabular), which also develops the Delta Lake format. So while Iceberg started at Netflix, Databricks now plays a role in both formats.

### Delta Lake

[Delta Lake](https://delta.io/) is an open-source project developed by Databricks. Like Iceberg, it provides a way to manage large-scale data and ensure ACID transactions and schema evolution. Delta Lake is widely used in the Databricks ecosystem and helps users achieve reliability and consistency in their data lakes, especially when working with Parquet files.

### Hudi

Hudi [was developed at Uber](https://www.uber.com/en-GB/blog/hoodie/) to handle large-scale datasets with the ability to manage updates and deletes efficiently. Hudi is also open-source and provides features to streamline data ingestion and ensure consistency across large datasets. While it's less commonly used than Iceberg or Delta Lake, it's still a strong contender, especially for use cases requiring complex updates. 

## What features do open table formats provide?

Open table formats bring several powerful features to data lakes, addressing the limitations of working directly with immutable Parquet files.

### CRUD operations

In a typical database, we can apply CRUD (Create, Read, Update, and Delete) operations. However, data stored in a data lake is typically immutable, meaning you can’t easily update or delete it. 

To make changes, you'd have to write a new version of a file. When querying the data, we’d need to remember which version of the file to query. This limitation means you can only perform Create and Read operations (C and R) on your data.

Open table formats change this by enabling efficient updates and data deletion, enabling the U and D in CRUD.

### ACID transactions

Another benefit of open table formats is support for ACID transactions (Atomicity, Consistency, Isolation, Durability). This enables multiple updates to the data to be made concurrently without conflicts, ensuring that your data remains consistent even as it evolves. With ACID support, open table formats make data lakes more reliable, allowing for complex, concurrent data operations without the risk of data corruption or inconsistencies.

### Schema evolution

Open table formats allow for seamless schema evolution. This means you can change your data schema without breaking existing queries or applications that depend on the data. Whether adding new fields, changing data types, or making other adjustments, the format ensures your data can evolve without disrupting processes.

### Partition pruning and query optimizations

Optimizing queries to avoid scanning every file in your data lake is essential when working with large datasets. Open table formats support partition pruning, which allows the query engine to skip irrelevant partitions and only scan the necessary data. This drastically improves query performance and reduces costs.

In addition to partition pruning, other optimizations, such as predicate pushdown and indexing, can speed up queries by processing data more efficiently at the storage layer.

### Time travel and data versioning

Some open table formats support time travel and data versioning. This allows you to query historical versions of your data or revert to a previous state if something goes wrong. Time travel makes it easy to track changes over time, compare different versions of your data, or recover from mistakes by returning to a known good state.

## Writing open table formats

Writing data to open table formats is typically done using **Apache Spark**, a powerful tool for distributed data processing. Spark integrates well with Iceberg, Delta Lake, and Hudi.

The real advantage of using Spark to write data to open table formats is scalability. Spark enables you to process and write massive datasets in parallel, which is crucial when working with data lakes that store petabytes of information. 

The [**PyIceberg**](https://py.iceberg.apache.org/) library is [becoming increasingly popular for writing to Iceberg](https://clickpy.clickhouse.com/dashboard/pyiceberg). The library provides an easy-to-use interface for interacting with Iceberg tables, allowing users to write and manage data efficiently. 

## Querying open table formats

**Spark** is still one of the most commonly used engines for querying data in open table formats, particularly for large-scale, distributed data processing. 
However, progress has been made, and now other distributed query engines and real-time analytical databases can query open table formats.

### Distributed Query Engines

**Trino** (formerly known as Presto) is a powerful distributed query engine that can directly query open table formats. 
It can handle large datasets across multiple storage systems and work with data in formats like Iceberg, Hudi, and Delta Lake. 
Trino allows users to query data with high performance and low latency, making it an excellent choice for interactive analytics.

### Real-Time Analytical Databases

**ClickHouse** also supports [querying open table formats](https://clickhouse.com/blog/climbing-the-iceberg-with-clickhouse). 
This allows for fast, low-latency queries across massive datasets stored in these formats, combining the power of open table formats with the speed of real-time analytics.

Below is an example of querying an Iceberg table from ClickHouse:

<pre><code type='click-ui' language='sql'>
SELECT
    round(avg(devices), 2) AS avg_devices,
    arrayMap(m -> round(m / 1000), quantiles(0.5, 0.9, 0.99, 0.999)(avg_d_kbps)) AS download_mbps
FROM iceberg('https://datasets-documentation.s3.eu-west-3.amazonaws.com/ookla/iceberg/');
</code></pre>


Open table formats

Data catalogs are essential for modern data architectures, but they're often misunderstood or overlooked. Let's explore what data catalogs are, why they matter for open table formats, and how they enable powerful analytical capabilities.

## What is a data catalog?

A data catalog is a centralized repository that stores metadata about an organization's data assets. It serves as an index or inventory system for data, making data assets discoverable, understandable, and manageable. 

Fundamentally, a data catalog is an architectural component rather than a standalone product - it's a system that tracks where data and metadata reside without necessarily storing the actual data itself.       

In this article, we will focus on data catalogs for [open table formats](https://clickhouse.com/engineering-resources/open-table-formats) - a specialized type of catalog designed to work with modern analytical data stored in columnar file formats like Apache Parquet and managed through table frameworks such as [Apache Iceberg](https://clickhouse.com/engineering-resources/apache-iceberg), Delta Lake, or Apache Hudi. These specialized catalogs address the unique requirements of [data lakehouse](https://clickhouse.com/resources/engineering/data-lakehouse) architectures, where performance, scalability, and transactional capabilities are paramount.

Unlike general-purpose data catalogs that might cover a wide range of data assets (from traditional databases to unstructured documents), open table format catalogs are optimized for tracking table states, schema versions, and optimization metadata specifically for analytical data workloads. They function as the critical lookup system for query engines to locate and process data stored across distributed storage systems efficiently. They provide the technical metadata for operations like partition pruning, predicate pushdown, and time travel queries.

These catalogs enable multiple processing engines to consistently access the same datasets with transactional guarantees, even as the underlying data evolves through updates, schema changes, and optimization operations.

## What are the characteristics of a data catalog?

Now we know what a data catalog is, let’s learn about some of its characteristics.

### Metadata repository

Its function as a metadata repository is at the core of any data catalog. For open table formats, this repository stores specialized metadata about table assets, including their current version, schema definitions, and file locations, without containing the data itself. The metadata is typically structured hierarchically, organized into elements such as databases or schemas, tables, and columns. In open table catalogs, this structure often includes additional technical metadata like partition specifications, file manifests, and statistics that enable efficient query planning. This structure provides a single source of truth about all table assets within an organization, allowing query engines to understand what data exists and how to access it efficiently.

### Data discovery and classification

Data catalogs for open table formats provide mechanisms for data discovery and classification tailored to analytical workloads. While they may include automated discovery tools that scan data sources, these catalogs often work with explicit table registrations rather than general-purpose crawlers. In open table ecosystems, the focus shifts from general classification to tracking specific table properties like format version, optimization status, and transaction history. Search functionality still allows users to find relevant tables across the organization, but with additional technical context specific to analytical processing needs.

### Schema management

Schema management is particularly critical for open table format catalogs. They track current schemas and maintain a history of schema evolution, enabling time travel queries and ensuring backward compatibility. Open table catalogs specialize in handling the complex schema evolution patterns supported by formats like Iceberg, Delta Lake, and Hudi, including column additions, removals, and type changes. While these catalogs primarily focus on structured data in columnar formats like Parquet and ORC, they provide sophisticated versioning capabilities that ensure consistent data access even as schemas evolve.

### Access control and security

Security remains paramount in open table format catalogs, which implement access control mechanisms for analytical workloads. They define and enforce permission models that determine who can access specific tables and operations, often integrating with data governance frameworks like Apache Ranger or AWS Lake Formation. For open table formats, these controls may extend to specific operations like compaction, schema evolution, or time travel queries. These integrate with organizational identity systems to ensure consistent security policies across the data lakehouse ecosystem.

### Lineage and governance

Open table format catalogs maintain specialized lineage information, tracking data origins and the sequence of transactions and operations performed on tables. This capability is essential for features like time travel, where users need to access specific historical versions of data. For open table formats, governance features focus on ensuring data integrity, tracking optimization operations, and maintaining audit trails of schema and data changes. This technical lineage information complements broader data governance initiatives by providing a detailed operational history.

### Integration capabilities

A key strength of open table format catalogs is their ability to integrate with diverse query engines and processing frameworks. They provide consistent interfaces that allow engines like Spark, Flink, Trino, and ClickHouse to access the same table definitions and metadata. This engine-agnostic approach ensures that different tools can work with the same data without inconsistencies. Open table catalogs typically offer standardized APIs based on specifications like the Iceberg API or Delta Protocol, enabling interoperability across the modern data stack. This integration capability is essential for building flexible data architectures that avoid vendor lock-in.

## What are some common data catalogs?

Several data catalog implementations have emerged to support open table formats, each with different strengths, integration capabilities, and deployment models. Below, we describe some of the most common data catalogs.

### Apache Hive Metastore

The [Apache Hive Metastore](https://hive.apache.org/) is the original and most widely supported catalog for big data ecosystems. Despite being developed before modern open table formats, it remains a common choice due to its universal compatibility with data processing engines. However, it lacks native features specifically designed for the advanced capabilities of open table formats.

### AWS Glue Data Catalog

[AWS Glue Data Catalog](https://docs.aws.amazon.com/glue/latest/dg/manage-catalog.html) is a fully managed, serverless catalog service that has added native support for open table formats. It integrates deeply with AWS services like Athena, EMR, and Redshift, providing a zero-maintenance option for organizations already operating in the AWS ecosystem.

### Databricks Unity Catalog

[Unity Catalog](https://www.databricks.com/product/unity-catalog) is Databricks' metadata management solution designed primarily for Delta Lake but expanding to support other formats. It offers unified governance across data, AI models, and notebooks with multi-cloud support spanning AWS, Azure, and GCP.

### Project Nessie

[Project Nessie](https://projectnessie.org/) is an open-source catalog specifically designed for open table formats. It focuses on Git-like versioning semantics and enables Git-inspired operations like branches and commits for data, making it particularly suitable for collaborative data engineering workflows.

## Querying data catalogs with ClickHouse

ClickHouse offers seamless integration with popular data catalogs, allowing you to directly leverage existing metadata repositories and query open table formats. This capability transforms ClickHouse from a standalone analytical database into a powerful query engine that can work with your existing data lake infrastructure.       

### Querying Unity Catalog with ClickHouse

ClickHouse can connect directly to [Unity Catalog](https://clickhouse.com/docs/use-cases/data-lake/unity-catalog), enabling you to query Delta Lake and Iceberg tables without data duplication. For Delta Lake tables, the configuration is straightforward:

<pre><code type='click-ui' language='sql'>
CREATE DATABASE unity
ENGINE = DataLakeCatalog('https://<workspace-id>.cloud.databricks.com/api/2.1/unity-catalog')
SETTINGS   
  warehouse = 'CATALOG_NAME',   
  catalog_credential = '<PAT>',   
  catalog_type = 'unity';
</code></pre>

ClickHouse also supports Iceberg tables through Unity Catalog's REST interface, offering more flexibility with OAuth authentication:       

<pre><code type='click-ui' language='sql'>
CREATE DATABASE unity
ENGINE = DataLakeCatalog('https://<workspace-id>.cloud.databricks.com/api/2.1/unity-catalog/iceberg')
SETTINGS   
  catalog_type = 'rest',   
  catalog_credential = '<client-id>:<client-secret>',   
  warehouse = 'workspace', 
  oauth_server_uri = 'https://<workspace-id>.cloud.databricks.com/oidc/v1/token',   
  auth_scope = 'all-apis,sql';
</code></pre>

Once configured, you can query the underlying tables using standard SQL syntax, just as you would with native ClickHouse tables. The catalog abstraction makes the experience seamless:

<pre><code type='click-ui' language='sql'>
SELECT count(*)
FROM `uniform.delta_hits`;
</code></pre>

Behind the scenes, ClickHouse leverages Unity Catalog's metadata to locate the data files, optimize query planning, and apply predicate pushdown where possible, delivering impressive performance even on large datasets.       

### Querying AWS Glue Catalog with ClickHouse

ClickHouse integrates with the [AWS Glue Catalog](https://clickhouse.com/docs/use-cases/data-lake/glue-catalog) to provide direct access to tables defined in your AWS environment. Setting up the connection requires AWS credentials and region information:

<pre><code type='click-ui' language='sql'>
CREATE DATABASE glue
ENGINE = DataLakeCatalog
SETTINGS
   catalog_type = 'glue',
   region = 'us-west-2',
   aws_access_key_id = '<access-key>',
   aws_secret_access_key = '<secret-key>';
</code></pre>

You can also use IAM roles instead of hardcoded credentials for enhanced security in production environments, particularly when running ClickHouse on EC2 instances or EKS clusters with appropriate IAM permissions.

After establishing the connection, you can query Iceberg tables registered in the Glue Catalog:

<pre><code type='click-ui' language='sql'>
SELECT count(*) FROM `iceberg-benchmark.hitsiceberg`;
</code></pre>

This integration is compelling for AWS-centric architectures, allowing you to maintain a single source of truth in the Glue Catalog while enabling ClickHouse to serve as a high-performance query engine for interactive analytics.       


Resource hub

Engineering

7 things to consider when choosing a cloud data warehouse

Open table formats

Data catalog