Table Functions

Table functions are methods for constructing tables.

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

Most table functions need the CREATE TEMPORARY TABLE privilege to run, so a SELECT or INSERT that executes one is refused when readonly is set to 1.

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Usage

​Usage

Usage