Skip to main content
Skip to main content

Table Functions

Table functions are methods for constructing tables.

You can use table functions in:

Note

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

PageDescription
generateRandomGenerates random data with a given schema. Allows populating test tables with that data. Not all types are supported.
generate_series (generateSeries)Returns a table with the single 'generate_series' column (UInt64) that contains integers from start to stop inclusively.
hdfsCreates a table from files in HDFS. This table function is similar to the url and file table functions.
hdfsClusterAllows processing files from HDFS in parallel from many nodes in a specified cluster.
hudiProvides a read-only table-like interface to Apache Hudi tables in Amazon S3.
hudiCluster Table FunctionAn 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.
icebergProvides a read-only table-like interface to Apache Iceberg tables in Amazon S3, Azure, HDFS or locally stored.
icebergClusterAn extension to the iceberg table function which allows processing files from Apache Iceberg in parallel from many nodes in a specified cluster.
inputTable function that allows effectively converting and inserting data sent to the server with a given structure to a table with another structure.
jdbcReturns a table that is connected via JDBC driver.
loopThe loop table function in ClickHouse is used to return query results in an infinite loop.
mergeCreates a temporary Merge table. The table structure is taken from the first table encountered that matches the regular expression.
mergeTreeIndexRepresents the contents of index and marks files of MergeTree tables. It can be used for introspection.
mongodbAllows SELECT queries to be performed on data that is stored on a remote MongoDB server.
mysqlAllows SELECT and INSERT queries to be performed on data that are stored on a remote MySQL server.
nullCreates a temporary table of the specified structure with the Null table engine. The function is used for the convenience of test writing and demonstrations.
numbersReturns tables with a single 'number' column that contains specifiable integers.
odbcReturns the table that is connected via ODBC.
postgresqlAllows SELECT and INSERT queries to be performed on data that is stored on a remote PostgreSQL server.
redisThis table function allows integrating ClickHouse with Redis.
remote, remoteSecureTable 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.
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.
s3ClusterAn 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.
sqliteAllows to perform queries on data stored in a SQLite database.
timeSeriesDatatimeSeriesData returns the data table used by table db_name.time_series_table whose table engine is TimeSeries.
timeSeriesMetricstimeSeriesMetrics returns the metrics table used by table db_name.time_series_table whose table engine is the TimeSeries engine.
timeSeriesTagstimeSeriesTags table function returns the tags table use by table db_name.time_series_table whose table engine is the TimeSeries engine.
urlCreates a table from the URL with given format and structure
urlClusterAllows processing files from URL in parallel from many nodes in a specified cluster.
viewTurns a subquery into a table. The function implements views.
zerosUsed for test purposes as the fastest method to generate many rows. Similar to the system.zeros and system.zeros_mt system tables.
azureBlobStorageProvides a table-like interface to select/insert files in Azure Blob Storage. Similar to the s3 function.
azureBlobStorageClusterAllows processing files from Azure Blob storage in parallel with many nodes in a specified cluster.
clusterAllReplicasAllows accessing all shards (configured in the remote_servers section) of a cluster without creating a Distributed table.
deltaLakeProvides a read-only table-like interface to the Delta Lake tables in Amazon S3.
deltaLakeClusterThis is an extension to the deltaLake table function.
dictionaryDisplays the dictionary data as a ClickHouse table. Works the same way as the Dictionary engine.
executableThe 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.
fileA 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.
fileClusterEnables 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).
formatParses data from arguments according to specified input format. If structure argument is not specified, it's extracted from the data.
fuzzJSONPerturbs a JSON string with random variations.
fuzzQueryPerturbs the given query string with random variations.
gcsProvides a table-like interface to SELECT and INSERT data from Google Cloud Storage. Requires the Storage Object User IAM role.