Integrations

Table functions vs table engines#

A table function is ad hoc. Connection details are passed inline, nothing persists, and the function is used directly in a query:

SELECT * FROM s3('https://bucket.s3.amazonaws.com/data/*.parquet', 'Parquet') LIMIT 10;

A table engine creates a persistent table object that stores its connection configuration and can be queried repeatedly by name:

CREATE TABLE s3_table (id UInt32, name String)
ENGINE = S3('https://bucket.s3.amazonaws.com/data/*.parquet', 'Parquet');

Use a function for one-off reads and INSERT ... SELECT loads. Use an engine for streaming consumption and for anything a materialized view must reference.

Kafka#

Streaming from Kafka uses three objects: a Kafka engine table that consumes the topic, a MergeTree table that stores the data, and a materialized view that moves rows from one to the other.

CREATE TABLE kafka_events
(
    timestamp DateTime,
    user_id UInt64,
    event String
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'localhost:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse_consumer',
         kafka_format = 'JSONEachRow';
 
CREATE TABLE events_store
(
    timestamp DateTime,
    user_id UInt64,
    event String
)
ENGINE = MergeTree
ORDER BY (timestamp, user_id);
 
CREATE MATERIALIZED VIEW kafka_to_store TO events_store
AS SELECT * FROM kafka_events;

The Kafka engine table is a consuming stream, not storage. Selecting from it directly consumes messages, so query events_store instead.

Object storage#

S3, Google Cloud Storage, and Azure Blob Storage are all reachable as functions and as engines. Glob patterns read many files at once:

-- Inspect before loading
DESCRIBE s3('https://bucket.s3.amazonaws.com/data/2024-01-*.parquet', 'Parquet');
 
-- Load
INSERT INTO events
SELECT * FROM s3('https://bucket.s3.amazonaws.com/data/2024-01-*.parquet', 'Parquet');

See inserting data for schema inference caveats.

PostgreSQL and MySQL#

Query a live table in either system, or persist the connection as an engine:

-- Ad hoc
SELECT * FROM postgresql('host:5432', 'database', 'table', 'user', 'password');
 
-- Persistent
CREATE TABLE pg_table (id UInt32, name String)
ENGINE = PostgreSQL('host:5432', 'database', 'table', 'user', 'password');

For continuous replication rather than queries, MaterializedPostgreSQL replicates an entire Postgres database into ClickHouse through logical replication.

ClickPipes for managed ingestion#

In ClickHouse Cloud, ClickPipes runs continuous ingestion without you operating a connector: Kafka, S3, Postgres CDC, MySQL CDC, and Kinesis. Pricing depends on the connector type — see pricing.

Analytics and transformation tools#

Language clients#

First-party clients exist for Python, Go, Java, JavaScript and Node.js, Rust, and C++, alongside ODBC and JDBC drivers.

When no client exists for your language, the HTTP interface works from anything that can make an HTTP request.

Data lake catalogs#

ClickHouse reads Apache Iceberg and Delta Lake tables directly, so a lakehouse can be queried without copying data into ClickHouse first.

Related#

• HTTP interface — query ClickHouse over REST
• Insert data efficiently — batching and formats for loading
• Materialized views — required for Kafka ingestion