Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 35 ยท Integrations and Operations

Table Functions and Integration Engines

Query and ingest from outside sources in ClickHouse: s3, url, file, remote and mysql table functions, Kafka and PostgreSQL engines, dictionaries and database engines.

ClickHouse rarely lives alone. Data arrives from Kafka topics, object storage, operational databases and files, and results go out to BI tools and applications. ClickHouse handles this through table functions (a function in the FROM clause that reads an external source on the spot), integration table engines (a permanent table that maps to an external system) and dictionaries (in-memory lookup tables). This lesson explains each one and when to choose it.

None of these can run on this site, so the ClickHouse snippets are read-only; credentials, bucket names and hosts are placeholders. The stand-ins use SQL to model the same ETL steps. Earlier lessons: loading data and materialized views.

Table functions: ad hoc access

A table function behaves like a table inside one query, nothing is stored.

SQL
-- Files on the server (inside user_files_path)
SELECT * FROM file('exports/*.csv', 'CSVWithNames') LIMIT 5;

-- Object storage
SELECT toStartOfMonth(ts) AS month, count()
FROM s3('https://my-bucket.s3.amazonaws.com/logs/2025/*.parquet', 'KEY', 'SECRET', 'Parquet')
GROUP BY month;

-- A web URL
SELECT * FROM url('https://example.com/data.json', 'JSONEachRow') LIMIT 3;

-- Another ClickHouse server
SELECT count() FROM remote('ch-host:9000', 'default', 'events', 'user', 'password');

-- Operational databases
SELECT * FROM postgresql('pg-host:5432', 'shop', 'orders', 'reader', 'password') LIMIT 10;
SELECT * FROM mysql('mysql-host:3306', 'shop', 'customers', 'reader', 'password') LIMIT 10;

Notes:

  • Globs (*, ?, {1..10}) read many files in parallel, and s3Cluster spreads that work over the whole cluster.
  • The format and structure can be inferred (DESCRIBE s3(...)), so you can omit the column list.
  • INSERT INTO FUNCTION s3(...) SELECT ... exports query results to files.
  • Never put secrets in query text you log. Use named collections (CREATE NAMED COLLECTION) or config-defined credentials, referenced by name: s3(my_s3_collection, filename = '...').

A common ingestion pattern is a one-off import:

SQL
INSERT INTO events
SELECT ts, site, user_id
FROM s3('https://my-bucket.s3.amazonaws.com/logs/2025/03/*.parquet', 'Parquet');

Integration table engines: a permanent mapping

An engine such as S3, URL, PostgreSQL, MySQL, MongoDB, Kafka, RabbitMQ or Iceberg/DeltaLake creates a table whose reads (and sometimes writes) go to the external system.

SQL
CREATE TABLE pg_orders
(
    id UInt64, customer_id UInt64, total Decimal(12, 2), created DateTime
)
ENGINE = PostgreSQL('pg-host:5432', 'shop', 'orders', 'reader', 'password');

This does not copy data: every query reads Postgres live, so it is useful for joins with small reference tables, or as the source of a periodic copy into MergeTree. Heavy analytics on a mapped table makes the operational database suffer; copy into a MergeTree table first.

A database engine goes one step further: CREATE DATABASE shop ENGINE = PostgreSQL(...) (or MySQL) exposes all tables of the remote database at once. There is also MaterializedPostgreSQL, which replicates a PostgreSQL database continuously using logical replication (check the status of this feature for your version before relying on it).

Streaming from Kafka

The Kafka engine consumes a topic, but a Kafka table cannot be queried repeatedly: reading advances the consumer offset. The standard pipeline has three parts:

flowchart LR
    K["Kafka topic"] --> T["Kafka engine table (consumer)"]
    T --> V["Materialized view (reads and transforms)"]
    V --> M["MergeTree table (storage)"]

The materialized view is what moves rows from the consumer into storage.

SQL
CREATE TABLE events_queue
(
    ts DateTime, site String, user_id UInt64
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list  = 'events',
         kafka_group_name  = 'clickhouse-events',
         kafka_format      = 'JSONEachRow',
         kafka_num_consumers = 2;

CREATE MATERIALIZED VIEW events_consumer TO events AS
SELECT ts, site, user_id FROM events_queue;

Detach the materialized view to pause consumption. Keep consumers at or below the number of topic partitions. Newer versions add a KeeperMap-based Kafka engine and managed connectors (ClickPipes in ClickHouse Cloud) that remove much of this plumbing.

Dictionaries: fast key lookups

A dictionary is an in-memory key-value structure loaded from a source (a table, file, URL, Postgres, MySQL, another ClickHouse table) and refreshed on a schedule. It is the right way to do "join with a small dimension table":

SQL
CREATE DICTIONARY country_names
(
    code String,
    name String
)
PRIMARY KEY code
SOURCE(CLICKHOUSE(TABLE 'countries'))
LIFETIME(MIN 300 MAX 600)
LAYOUT(HASHED());

SELECT dictGet('country_names', 'name', country) AS country_name, count()
FROM events
GROUP BY country_name;

dictGet is far faster than a join, and it works inside materialized views, which cannot join efficiently. Layouts include FLAT, HASHED, COMPLEX_KEY_HASHED, RANGE_HASHED (time-valid values) and CACHE.

Output and BI access

Applications connect over HTTP (8123), native TCP (9000) or the MySQL and PostgreSQL wire protocols (ports 9004 and 9005 when enabled), so tools such as Grafana, Superset, Metabase, Tableau and Power BI work through ready-made connectors or JDBC/ODBC drivers.

Stand-in: lookup instead of join, and import with a filter

The first stand-in shows what a dictionary lookup does (a join is the SQL way). The second models an INSERT ... SELECT import from a "staging source", with a transform.

SQL Stand-in: a dictionary lookup

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: INSERT ... SELECT from an external source

Press Run (or Ctrl + Enter) to see the result here.

Choosing the right tool

Situation Tool
One-off exploration of files in a bucket table function
Regular copy from Postgres/MySQL INSERT ... SELECT from the function, scheduled, or a refreshable materialized view
Continuous event stream Kafka engine + materialized view (or a managed connector)
Joining a small reference table dictionary
Data lake tables (Iceberg, Delta) the lake engines or table functions

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a table function like s3(...) store?

    A table function is evaluated per query; to keep the data you INSERT ... SELECT it into a MergeTree table.
  2. 2. Which pieces make up the standard Kafka ingestion pipeline?

    The Kafka table consumes; the materialized view reads it and writes into storage.
  3. 3. Why use a dictionary instead of a JOIN for a small lookup table?

    Dictionaries live in memory, refresh on a schedule and make lookups much cheaper than joins.
  4. 4. Why should you avoid running heavy analytics directly on a PostgreSQL engine table?

    The table reads PostgreSQL live; copy the data into MergeTree and query that instead.

Summary

  • Table functions (file, s3, url, remote, postgresql, mysql) read external data per query; INSERT INTO FUNCTION exports.
  • Integration engines map external systems permanently; copy data into MergeTree for analytics.
  • Kafka ingestion is a Kafka table, a materialized view and a MergeTree table.
  • Dictionaries replace small joins; keep secrets in named collections.

Next: Users, Roles, Quotas and Security