Table functions

Elena Dvoryadkina
Contents

Table functions create table objects in ADQM based on data from external sources and allow you to read and write data using the following queries:

  • SELECT …​ FROM <table_function> — creates a temporary table available only in the current query (the table will be dropped after the query completes);

  • CREATE TABLE [<db_name>.]<table_name> AS <table_function> — creates the same table as a table function returns (the created table will work in the same way as the corresponding table function);

  • INSERT INTO TABLE FUNCTION <table_function> …​ — inserts data into a table referenced by the table function.

IMPORTANT

To use table functions, enable the allow_ddl setting.

Functions

ADQM supports a set of table functions for integration with various external systems. The following table lists examples.

Function Return value

file

Table to select/insert local file data

url

Table to select/insert data on a remote HTTP/HTTPS server at the specified URL

hdfs, hdfsCluster

Table to select/insert data of a file in HDFS

mysql

Table to select/insert data stored on a remote MySQL server

postgresql

Table to select/insert data of a table in a remote PostgreSQL database

mongodb

Table to select data from a remote MongoDB server

s3, s3Cluster

Table to select/insert data of a file in Amazon S3

jdbc

Table connected to a data source via a JDBC driver

odbc

Table connected to a data source via ODBC

cluster

Dataset from a cluster configured in the remote_servers section (config.xml)

remote

Dataset from remote ClickHouse servers

To get the full list of supported table functions with their descriptions, refer to the Table Functions section of the ClickHouse documentation.

Еxamples

Queries below show how to use table functions on the example of the file function that allows you to read or write local file data. Before executing test requests, prepare a source file:

  1. In the config.xml configuration file, set the /var/lib/clickhouse/user_files folder as the user_files_path parameter value. Check the setting:

    $ sudo grep user_files_path /etc/clickhouse-server/config.xml
    <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>

  2. Create a CSV file with test data in the /var/lib/clickhouse/user_files folder:

    $ sudo cat > /var/lib/clickhouse/user_files/file_func_source.csv
1,2,3
4,5,6
7,8,9

Run the following queries to view how the file function works:

  1. CREATE TABLE

    CREATE TABLE file_table AS file('file_func_source.csv', 'CSV', 'column_1 UInt32, column_2 UInt32, column_3 UInt32');

    As a result, a table with the specified structure and data from the file_func_source.csv file is added to the current database.

  2. SELECT …​ FROM

    Both of the following queries return a table containing data from the specified file:

    SELECT * FROM file_table;
    SELECT * FROM file('file_func_source.csv', 'CSV', 'column_1 UInt32, column_2 UInt32, column_3 UInt32');

    The output:

    ┌─column_1─┬─column_2─┬─column_3─┐
│        1 │        2 │        3 │
│        4 │        5 │        6 │
│        7 │        8 │        9 │
└──────────┴──────────┴──────────┘

  3. INSERT INTO TABLE FUNCTION

    The following query inserts data into the source file:

    INSERT INTO FUNCTION file('file_func_source.csv', 'CSV', 'column_1 UInt32, column_2 UInt32, column_3 UInt32')
    VALUES (10, 20, 30), (40, 50, 60);

    Make sure the data is written to the file:

    $ sudo cat /var/lib/clickhouse/user_files/file_func_source.csv
1,2,3
4,5,6
7,8,9
10,20,30
40,50,60

    When calling the file table function, new data rows are also included in the selection:

    SELECT * FROM file_table;
    ┌─column_1─┬─column_2─┬─column_3─┐
│        1 │        2 │        3 │
│        4 │        5 │        6 │
│        7 │        8 │        9 │
│       10 │       20 │       30 │
│       40 │       50 │       60 │
└──────────┴──────────┴──────────┘
NOTE

For information on basic queries to operate data tables in ADQM, see Basic syntax.
Found a mistake? Seleсt text and press Ctrl+Enter to report it