Table Functions - ClickHouse Documentation

Table functions are methods for constructing tables.

Page	Description
azureBlobStorage	Provides a table-like interface to select/insert files in Azure Blob Storage. Similar to the s3 function.
azureBlobStorageCluster	Allows processing files from Azure Blob storage in parallel with many nodes in a specified cluster.
clusterAllReplicas	Allows accessing all shards (configured in the `remote_servers` section) of a cluster without creating a Distributed table.
deltaLake	Provides a read-only table-like interface to the Delta Lake tables in Amazon S3.
deltaLakeCluster	This is an extension to the deltaLake table function.
dictionary	Displays the dictionary data as a ClickHouse table. Works the same way as the Dictionary engine.
executable	The `executable` table function creates a table based on the output of a user-defined function (UDF) that you define in a script that outputs rows to stdout.
file	A table engine which provides a table-like interface to SELECT from and INSERT into files, similar to the s3 table function. Use `file` when working with local files, and `s3` when working with buckets in object storage such as S3, GCS, or MinIO.
fileCluster	Enables simultaneous processing of files matching a specified path across multiple nodes within a cluster. The initiator establishes connections to worker nodes, expands globs in the file path, and delegates file-reading tasks to worker nodes. Each worker node is querying the initiator for the next file to process, repeating until all tasks are completed (all files are read).
filesystem	Provides access to the file system to list files and return their metadata and contents.
format	Parses data from arguments according to specified input format. If structure argument is not specified, it’s extracted from the data.
gcs	Provides a table-like interface to `SELECT` and `INSERT` data from Google Cloud Storage. Requires the `Storage Object User` IAM role.
fuzzQuery	Perturbs the given query string with random variations.
fuzzJSON	Perturbs a JSON string with random variations.
generateRandom	Generates random data with a given schema. Allows populating test tables with that data. Not all types are supported.
mergeTreeTextIndex	Represents the dictionary of a text index in a MergeTree table. It can be used for introspection.
mergeTreeProjection	Represents the contents of some projection in MergeTree tables. It can be used for introspection.
mergeTreeIndex	Represents the contents of index and marks files of MergeTree tables. It can be used for introspection.
hdfs	Creates a table from files in HDFS. This table function is similar to the url and file table functions.
hdfsCluster	Allows processing files from HDFS in parallel from many nodes in a specified cluster.
hudi	Provides a read-only table-like interface to Apache Hudi tables in Amazon S3.
ytsaurus	The table function allows to read data from the YTsaurus cluster.
hudiCluster Table Function	An extension to the hudi table function. Allows processing files from Apache Hudi tables in Amazon S3 in parallel with many nodes in a specified cluster.
iceberg	Provides a read-only table-like interface to Apache Iceberg tables in Amazon S3, Azure, HDFS or locally stored.
paimon	Provides a read-only table-like interface to Apache Paimon tables in Amazon S3, Azure, HDFS or locally stored.
icebergCluster	An extension to the iceberg table function which allows processing files from Apache Iceberg in parallel from many nodes in a specified cluster.
paimonCluster	An extension to the paimon table function which allows processing files from Apache Paimon in parallel from many nodes in a specified cluster.
input	Table function that allows effectively converting and inserting data sent to the server with a given structure to a table with another structure.
jdbc	Returns a table that is connected via JDBC driver.
merge	Creates a temporary Merge table. The structure will be derived from underlying tables by using a union of their columns and by deriving common types.
mongodb	Allows `SELECT` queries to be performed on data that is stored on a remote MongoDB server.
mysql	Allows `SELECT` and `INSERT` queries to be performed on data that are stored on a remote MySQL server.
null	Creates a temporary table of the specified structure with the Null table engine. The function is used for the convenience of test writing and demonstrations.
timeSeriesMetrics	timeSeriesMetrics returns the metrics table used by table `db_name.time_series_table` whose table engine is the TimeSeries engine.
timeSeriesSelector	Reads time series from a TimeSeries table filtered by a selector and with timestamps in a specified interval.
timeSeriesTags	timeSeriesTags table function returns the tags table use by table `db_name.time_series_table` whose table engine is the TimeSeries engine.
prometheusQueryRange	Evaluates a prometheus query using data from a TimeSeries table.
zeros	Used for test purposes as the fastest method to generate many rows. Similar to the `system.zeros` and `system.zeros_mt` system tables.
primes	Returns a table with a single `prime` column that contains prime numbers.
numbers	Returns a table with a single `number` column that contains a sequence of integers.
prometheusQuery	Evaluates a prometheus query using data from a TimeSeries table.
timeSeriesSamples	timeSeriesSamples returns the samples table used by table `db_name.time_series_table` whose table engine is TimeSeries.
generate_series (generateSeries)	Returns a table with the single `generate_series` column (UInt64) that contains integers from start to stop inclusively.
odbc	Returns the table that is connected via ODBC.
postgresql	Allows `SELECT` and `INSERT` queries to be performed on data that is stored on a remote PostgreSQL server.
redis	This table function allows integrating ClickHouse with Redis.
remote, remoteSecure	Table function `remote` allows to access remote servers on-the-fly, i.e. without creating a distributed table. Table function `remoteSecure` is same as `remote` but over a secure connection.
s3 Table Function	Provides a table-like interface to select/insert files in Amazon S3 and Google Cloud Storage. This table function is similar to the hdfs function, but provides S3-specific features.
s3Cluster	An extension to the s3 table function, which allows processing files from Amazon S3 and Google Cloud Storage in parallel with many nodes in a specified cluster.
sqlite	Allows to perform queries on data stored in a SQLite database.
arrowFlight	Allows reading from and writing to data exposed via an Apache Arrow Flight server.
url	Creates a table from the `URL` with given `format` and `structure`
urlCluster	Allows processing files from URL in parallel from many nodes in a specified cluster.
view	Turns a subquery into a table. The function implements views.
values	creates a temporary storage which fills columns with values.
loop	The loop table function in ClickHouse is used to return query results in an infinite loop.

Usage

Table functions can be used in the FROM clause of a SELECT query. For example, you can SELECT data from a file on your local machine using the file table function.

Query

echo "1, 2, 3" > example.csv

Response

./clickhouse client
:) SELECT * FROM file('example.csv')
┌─c1─┬─c2─┬─c3─┐
│  1 │  2 │  3 │
└────┴────┴────┘

You can also use table functions for creating a temporary table that is available only in the current query. For example:

Query

SELECT * FROM generateSeries(1,5);

Response

┌─generate_series─┐
│               1 │
│               2 │
│               3 │
│               4 │
│               5 │
└─────────────────┘

The table is deleted when the query finishes. Table functions can be used as a way to create tables, using the following syntax:

Query

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()

For example:

Query

CREATE TABLE series AS generateSeries(1, 5);
SELECT * FROM series;

Response

┌─generate_series─┐
│               1 │
│               2 │
│               3 │
│               4 │
│               5 │
└─────────────────┘

Finally, table functions can be used to INSERT data into a table. For example, we could write out the contents of the table we created in the previous example to a file on disk using the file table function again:

Query

INSERT INTO FUNCTION file('numbers.csv', 'CSV') SELECT * FROM series;

Query

cat numbers.csv
1
2
3
4
5

You can’t use table functions if the allow_ddl setting is disabled.

​Usage

Usage