ClickHouse/clickhouse-docs-embeddings huggingface dataset:
hudi Table Function

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/hudi.md)

Provides a read-only table-like interface to Apache [Hudi](https://hudi.apache.org/) tables in Amazon S3.

## Syntax[​](#syntax "Direct link to Syntax")

```
hudi(url [,aws_access_key_id, aws_secret_access_key] [,format] [,structure] [,compression] [,extra_credentials])
```

## Arguments[​](#arguments "Direct link to Arguments")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `url` Bucket url with the path to an existing Hudi table in S3.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `aws_access_key_id`, `aws_secret_access_key` Long-term credentials for the [AWS](https://aws.amazon.com/) account user. You can use these to authenticate your requests. These parameters are optional. If credentials are not specified, they are used from the ClickHouse configuration. For more information see [Using S3 for Data Storage](/docs/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-s3).| `format` The [format](/docs/interfaces/formats) of the file.| `structure` Structure of the table. Format `'column1_name column1_type, column2_name column2_type, ...'`.| `compression` Parameter is optional. Supported values: `none`, `gzip/gz`, `brotli/br`, `xz/LZMA`, `zstd/zst`. By default, compression will be autodetected by the file extension.| `extra_credentials` Parameter is optional. Used to pass a `role_arn` for role-based access in ClickHouse Cloud. See [Secure S3](/docs/cloud/data-sources/secure-s3) for configuration steps. | | | | | | | | | | | | | |

## Returned value[​](#returned_value "Direct link to Returned value")

A table with the specified structure for reading data in the specified Hudi table in S3.

## Virtual Columns[​](#virtual-columns "Direct link to Virtual Columns")

- `_path` — Path to the file. Type: `LowCardinality(String)`.
- `_file` — Name of the file. Type: `LowCardinality(String)`.
- `_size` — Size of the file in bytes. Type: `Nullable(UInt64)`. If the file size is unknown, the value is `NULL`.
- `_time` — Last modified time of the file. Type: `Nullable(DateTime)`. If the time is unknown, the value is `NULL`.
- `_etag` — The etag of the file. Type: `LowCardinality(String)`. If the etag is unknown, the value is `NULL`.

## Related[​](#related "Direct link to Related")

- [Hudi engine](/docs/engines/table-engines/integrations/hudi)
- [Hudi cluster table function](/docs/sql-reference/table-functions/hudiCluster)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Virtual Columns](#virtual-columns)- [Related](#related)

Was this page helpful?
icebergCluster Table Function

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/icebergCluster.md)

This is an extension to the [iceberg](/docs/sql-reference/table-functions/iceberg) table function.

Allows processing files from Apache [Iceberg](https://iceberg.apache.org/) in parallel from many nodes in a specified cluster. On initiator it creates a connection to all nodes in the cluster and dispatches each file dynamically. On the worker node it asks the initiator about the next task to process and processes it. This is repeated until all tasks are finished.

## Syntax[​](#syntax "Direct link to Syntax")

```
icebergS3Cluster(cluster_name, url [, NOSIGN | access_key_id, secret_access_key, [session_token]] [,format] [,compression_method] [,extra_credentials])
icebergS3Cluster(cluster_name, named_collection[, option=value [,..]])

icebergAzureCluster(cluster_name, connection_string|storage_account_url, container_name, blobpath, [,account_name], [,account_key] [,format] [,compression_method])
icebergAzureCluster(cluster_name, named_collection[, option=value [,..]])

icebergHDFSCluster(cluster_name, path_to_table, [,format] [,compression_method])
icebergHDFSCluster(cluster_name, named_collection[, option=value [,..]])
```

## Arguments[​](#arguments "Direct link to Arguments")

- `cluster_name` — Name of a cluster that is used to build a set of addresses and connection parameters to remote and local servers.
- Description of all other arguments coincides with description of arguments in equivalent [iceberg](/docs/sql-reference/table-functions/iceberg) table function.
- An optional `extra_credentials` parameter can be used to pass a `role_arn` for role-based access in ClickHouse Cloud. See [Secure S3](/docs/cloud/data-sources/secure-s3) for configuration steps.

**Returned value**

A table with the specified structure for reading data from cluster in the specified Iceberg table.

**Examples**

```
SELECT * FROM icebergS3Cluster('cluster_simple', 'http://test.s3.amazonaws.com/clickhouse-bucket/test_table', 'test', 'test')
```

## Virtual Columns[​](#virtual-columns "Direct link to Virtual Columns")

- `_path` — Path to the file. Type: `LowCardinality(String)`.
- `_file` — Name of the file. Type: `LowCardinality(String)`.
- `_size` — Size of the file in bytes. Type: `Nullable(UInt64)`. If the file size is unknown, the value is `NULL`.
- `_time` — Last modified time of the file. Type: `Nullable(DateTime)`. If the time is unknown, the value is `NULL`.
- `_etag` — The etag of the file. Type: `LowCardinality(String)`. If the etag is unknown, the value is `NULL`.

**See Also**

- [Iceberg engine](/docs/engines/table-engines/integrations/iceberg)
- [Iceberg table function](/docs/sql-reference/table-functions/iceberg)

- [Syntax](#syntax)- [Arguments](#arguments)- [Virtual Columns](#virtual-columns)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/input.md)

`input(structure)` - table function that allows effectively converting and inserting data sent to the
server with a given structure to a table with another structure.

`structure` - structure of data sent to the server in following format `'column1_name column1_type, column2_name column2_type, ...'`.
For example, `'id UInt32, name String'`.

This function can be used only in `INSERT SELECT` query and only once but otherwise behaves like ordinary table function
(for example, it can be used in subquery, etc.).

Data can be sent in any way like for ordinary `INSERT` query and passed in any available [format](/docs/sql-reference/formats)
that must be specified in the end of query (unlike ordinary `INSERT SELECT`).

The main feature of this function is that when server receives data from client it simultaneously converts it
according to the list of expressions in the `SELECT` clause and inserts into the target table. Temporary table
with all transferred data is not created.

## Examples[​](#examples "Direct link to Examples")

- Let the `test` table has the following structure `(a String, b String)`
  and data in `data.csv` has a different structure `(col1 String, col2 Date, col3 Int32)`. Query for insert
  data from the `data.csv` into the `test` table with simultaneous conversion looks like this:

```
$ cat data.csv | clickhouse-client --query="INSERT INTO test SELECT lower(col1), col3 * col3 FROM input('col1 String, col2 Date, col3 Int32') FORMAT CSV";
```

- If `data.csv` contains data of the same structure `test_structure` as the table `test` then these two queries are equal:

```
$ cat data.csv | clickhouse-client --query="INSERT INTO test FORMAT CSV"
$ cat data.csv | clickhouse-client --query="INSERT INTO test SELECT * FROM input('test_structure') FORMAT CSV"
```

- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/jdbc.md)

Note

clickhouse-jdbc-bridge contains experimental codes and is no longer supported. It may contain reliability issues and security vulnerabilities. Use it at your own risk.
ClickHouse recommend using built-in table functions in ClickHouse which provide a better alternative for ad-hoc querying scenarios (Postgres, MySQL, MongoDB, etc).

JDBC table function returns table that is connected via JDBC driver.

This table function requires separate [clickhouse-jdbc-bridge](https://github.com/ClickHouse/clickhouse-jdbc-bridge) program to be running.
It supports Nullable types (based on DDL of remote table that is queried).

## Syntax[​](#syntax "Direct link to Syntax")

```
jdbc(datasource, external_database, external_table)
jdbc(datasource, external_table)
jdbc(named_collection)
```

## Examples[​](#examples "Direct link to Examples")

Instead of an external database name, a schema can be specified:

```
SELECT * FROM jdbc('jdbc:mysql://localhost:3306/?user=root&password=root', 'schema', 'table')
```

```
SELECT * FROM jdbc('mysql://localhost:3306/?user=root&password=root', 'select * from schema.table')
```

```
SELECT * FROM jdbc('mysql-dev?p1=233', 'num Int32', 'select toInt32OrZero(''{{p1}}'') as num')
```

```
SELECT *
FROM jdbc('mysql-dev?p1=233', 'num Int32', 'select toInt32OrZero(''{{p1}}'') as num')
```

```
SELECT a.datasource AS server1, b.datasource AS server2, b.name AS db
FROM jdbc('mysql-dev?datasource_column', 'show databases') a
INNER JOIN jdbc('self?datasource_column', 'show databases') b ON a.Database = b.name
```

- [Syntax](#syntax)- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/loop.md)

## Syntax[​](#syntax "Direct link to Syntax")

```
SELECT ... FROM loop(database, table);
SELECT ... FROM loop(database.table);
SELECT ... FROM loop(table);
SELECT ... FROM loop(other_table_function(...));
```

## Arguments[​](#arguments "Direct link to Arguments")

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `database` database name.|  |  |  |  | | --- | --- | --- | --- | | `table` table name.|  |  | | --- | --- | | `other_table_function(...)` other table function. Example: `SELECT * FROM loop(numbers(10));` `other_table_function(...)` here is `numbers(10)`. | | | | | | | |

## Returned values[​](#returned_values "Direct link to Returned values")

Infinite loop to return query results.

## Examples[​](#examples "Direct link to Examples")

Selecting data from ClickHouse:

```
SELECT * FROM loop(test_database, test_table);
SELECT * FROM loop(test_database.test_table);
SELECT * FROM loop(test_table);
```

Or using other table functions:

```
SELECT * FROM loop(numbers(3)) LIMIT 7;
   ┌─number─┐
1. │      0 │
2. │      1 │
3. │      2 │
   └────────┘
   ┌─number─┐
4. │      0 │
5. │      1 │
6. │      2 │
   └────────┘
   ┌─number─┐
7. │      0 │
   └────────┘
```

```
SELECT * FROM loop(mysql('localhost:3306', 'test', 'test', 'user', 'password'));
...
```

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned values](#returned_values)- [Examples](#examples)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/merge.md)

Creates a temporary [Merge](/docs/engines/table-engines/special/merge) table.
The table schema is derived from underlying tables by using a union of their columns and by deriving common types.
The same virtual columns are available as for the [Merge](/docs/engines/table-engines/special/merge) table engine.

## Syntax[​](#syntax "Direct link to Syntax")

```
merge(['db_name',] 'tables_regexp')
```

## Arguments[​](#arguments "Direct link to Arguments")

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  | | --- | --- | --- | --- | | `db_name` Possible values (optional, default is `currentDatabase()`):  - database name,  - constant expression that returns a string with a database name, for example, `currentDatabase()`,  - `REGEXP(expression)`, where `expression` is a regular expression to match the DB names.| `tables_regexp` A regular expression to match the table names in the specified DB or DBs. | | | | | |

## Related[​](#related "Direct link to Related")

- [Merge](/docs/engines/table-engines/special/merge) table engine

- [Syntax](#syntax)- [Arguments](#arguments)- [Related](#related)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/mergeTreeProjection.md)

Represents the contents of some projection in MergeTree tables. It can be used for introspection.

## Syntax[​](#syntax "Direct link to Syntax")

```
mergeTreeProjection(database, table, projection)
```

## Arguments[​](#arguments "Direct link to Arguments")

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `database` The database name to read projection from.|  |  |  |  | | --- | --- | --- | --- | | `table` The table name to read projection from.|  |  | | --- | --- | | `projection` The projection to read from. | | | | | | | |

## Returned value[​](#returned_value "Direct link to Returned value")

A table object with columns provided by given projection.

## Usage Example[​](#usage-example "Direct link to Usage Example")

```
CREATE TABLE test
(
    `user_id` UInt64,
    `item_id` UInt64,
    PROJECTION order_by_item_id
    (
        SELECT _part_offset
        ORDER BY item_id
    )
)
ENGINE = MergeTree
ORDER BY user_id;

INSERT INTO test SELECT number, 100 - number FROM numbers(5);
```

```
SELECT *, _part_offset FROM mergeTreeProjection(currentDatabase(), test, order_by_item_id);
```

```
   ┌─item_id─┬─_parent_part_offset─┬─_part_offset─┐
1. │      96 │                   4 │            0 │
2. │      97 │                   3 │            1 │
3. │      98 │                   2 │            2 │
4. │      99 │                   1 │            3 │
5. │     100 │                   0 │            4 │
   └─────────┴─────────────────────┴──────────────┘
```

```
DESCRIBE mergeTreeProjection(currentDatabase(), test, order_by_item_id) SETTINGS describe_compact_output = 1;
```

```
   ┌─name────────────────┬─type───┐
1. │ item_id             │ UInt64 │
2. │ _parent_part_offset │ UInt64 │
   └─────────────────────┴────────┘
```

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Usage Example](#usage-example)

Was this page helpful?
mergeTreeTextIndex Table Function

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/mergeTreeTextIndex.md)

Represents the dictionary of a text index in MergeTree tables.
Returns tokens with their posting list metadata.
It can be used for introspection.

## Syntax[​](#syntax "Direct link to Syntax")

```
mergeTreeTextIndex(database, table, index_name)
```

## Arguments[​](#arguments "Direct link to Arguments")

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `database` The database name to read text index from.|  |  |  |  | | --- | --- | --- | --- | | `table` The table name to read text index from.|  |  | | --- | --- | | `index_name` The text index to read from. | | | | | | | |

## Returned value[​](#returned_value "Direct link to Returned value")

A table object with tokens and their posting list metadata.

## Usage Example[​](#usage-example "Direct link to Usage Example")

```
CREATE TABLE tab
(
    id UInt64,
    s String,
    INDEX idx_s (s) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO tab SELECT number, concatWithSeparator(' ', 'apple', 'banana') FROM numbers(500);
INSERT INTO tab SELECT 500 + number, concatWithSeparator(' ', 'cherry', 'date') FROM numbers(500);

SELECT * FROM mergeTreeTextIndex(currentDatabase(), tab, idx_s);
```

Result:

```
   ┌─part_name─┬─token──┬─dictionary_compression─┬─cardinality─┬─num_posting_blocks─┬─has_embedded_postings─┬─has_raw_postings─┬─has_compressed_postings─┐
1. │ all_1_1_0 │ apple  │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
2. │ all_1_1_0 │ banana │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
3. │ all_2_2_0 │ cherry │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
4. │ all_2_2_0 │ date   │ front_coded            │         500 │                  1 │                     0 │                0 │                       0 │
   └───────────┴────────┴────────────────────────┴─────────────┴────────────────────┴───────────────────────┴──────────────────┴─────────────────────────┘
```

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Usage Example](#usage-example)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/null.md)

Creates a temporary table of the specified structure with the [Null](/docs/engines/table-engines/special/null) table engine. According to the `Null`-engine properties, the table data is ignored and the table itself is immediately dropped right after the query execution. The function is used for the convenience of test writing and demonstrations.

## Syntax[​](#syntax "Direct link to Syntax")

```
null('structure')
```

## Argument[​](#argument "Direct link to Argument")

- `structure` — A list of columns and column types. [String](/docs/sql-reference/data-types/string).

## Returned value[​](#returned_value "Direct link to Returned value")

A temporary `Null`-engine table with the specified structure.

## Example[​](#example "Direct link to Example")

Query with the `null` function:

```
INSERT INTO function null('x UInt64') SELECT * FROM numbers_mt(1000000000);
```

can replace three queries:

```
CREATE TABLE t (x UInt64) ENGINE = Null;
INSERT INTO t SELECT * FROM numbers_mt(1000000000);
DROP TABLE IF EXISTS t;
```

## Related[​](#related "Direct link to Related")

- [Null table engine](/docs/engines/table-engines/special/null)

- [Syntax](#syntax)- [Argument](#argument)- [Returned value](#returned_value)- [Example](#example)- [Related](#related)

Was this page helpful?
numbers Table Function

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/numbers.md)

- `numbers()` – Returns an infinite table with a single `number` column (UInt64) that contains integers in ascending order, starting from 0. Use `LIMIT` (and optionally `OFFSET`) to restrict the number of rows.
- `numbers(N)` – Returns a table with a single `number` column (UInt64) that contains integers from 0 to `N - 1`.
- `numbers(N, M)` – Returns a table with a single `number` column (UInt64) that contains `M` integers from `N` to `N + M - 1`.
- `numbers(N, M, S)` – Returns a table with a single `number` column (UInt64) that contains values in `[N, N + M)` with step `S` (about `M / S` rows, rounded up). `S` must be `>= 1`.

This is similar to the [`system.numbers`](/docs/operations/system-tables/numbers) system table. It can be used for testing and generating successive values.

The following queries are equivalent:

```
SELECT * FROM numbers(10);
SELECT * FROM numbers(0, 10);
SELECT * FROM numbers() LIMIT 10;
SELECT * FROM system.numbers LIMIT 10;
SELECT * FROM system.numbers WHERE number BETWEEN 0 AND 9;
SELECT * FROM system.numbers WHERE number IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);
```

The following queries are also equivalent:

```
SELECT * FROM numbers(10, 10);
SELECT * FROM numbers() LIMIT 10 OFFSET 10;
SELECT * FROM system.numbers LIMIT 10 OFFSET 10;
```

The following queries are also equivalent:

```
SELECT number * 2 FROM numbers(10);
SELECT (number - 10) * 2 FROM numbers(10, 10);
SELECT * FROM numbers(0, 20, 2);
```

### Examples[​](#examples "Direct link to Examples")

The first 10 numbers.

```
SELECT * FROM numbers(10);
```

```
 ┌─number─┐
 │      0 │
 │      1 │
 │      2 │
 │      3 │
 │      4 │
 │      5 │
 │      6 │
 │      7 │
 │      8 │
 │      9 │
 └────────┘
```

Generate a sequence of dates from 2010-01-01 to 2010-12-31.

```
SELECT toDate('2010-01-01') + number AS d FROM numbers(365);
```

Find the first `UInt64` `>= 10^15` whose `sipHash64(number)` has 20 trailing zero bits.

```
SELECT number
FROM numbers()
WHERE number >= 1e15
  AND bitAnd(sipHash64(number), 0xFFFFF) = 0
LIMIT 1;
```

```
 ┌───────────number─┐
 │ 1000000000056095 │ -- 1.00 quadrillion
 └──────────────────┘
```

### Notes[​](#notes "Direct link to Notes")

- For performance reasons, if you know how many rows you need, prefer bounded forms (`numbers(N)`, `numbers(N, M[, S])`) over unbounded `numbers()` / `system.numbers`.
- For parallel generation, use `numbers_mt(...)` or the [`system.numbers_mt`](/docs/operations/system-tables/numbers_mt) table. Note that results may be returned in any order.

- [Examples](#examples)- [Notes](#notes)

Was this page helpful?
paimonCluster Table Function

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/table-functions/paimonCluster.md)

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

This is an extension to the [paimon](/docs/sql-reference/table-functions/paimon) table function.

Allows processing files from Apache [Paimon](https://paimon.apache.org/) in parallel from many nodes in a specified cluster. On initiator it creates a connection to all nodes in the cluster and dispatches each file dynamically. On the worker node it asks the initiator about the next task to process and processes it. This is repeated until all tasks are finished.

## Syntax[​](#syntax "Direct link to Syntax")

```
paimonS3Cluster(cluster_name, url [,aws_access_key_id, aws_secret_access_key] [,format] [,structure] [,compression] [,extra_credentials])

paimonAzureCluster(cluster_name, connection_string|storage_account_url, container_name, blobpath, [,account_name], [,account_key] [,format] [,compression_method])

paimonHDFSCluster(cluster_name, path_to_table, [,format] [,compression_method])
```

## Arguments[​](#arguments "Direct link to Arguments")

- `cluster_name` — Name of a cluster that is used to build a set of addresses and connection parameters to remote and local servers.
- Description of all other arguments coincides with description of arguments in equivalent [paimon](/docs/sql-reference/table-functions/paimon) table function.
- An optional `extra_credentials` parameter can be used to pass a `role_arn` for role-based access in ClickHouse Cloud. See [Secure S3](/docs/cloud/data-sources/secure-s3) for configuration steps.

**Returned value**

A table with the specified structure for reading data from cluster in the specified Paimon table.

## Virtual Columns[​](#virtual-columns "Direct link to Virtual Columns")

- `_path` — Path to the file. Type: `LowCardinality(String)`.
- `_file` — Name of the file. Type: `LowCardinality(String)`.
- `_size` — Size of the file in bytes. Type: `Nullable(UInt64)`. If the file size is unknown, the value is `NULL`.
- `_time` — Last modified time of the file. Type: `Nullable(DateTime)`. If the time is unknown, the value is `NULL`.
- `_etag` — The etag of the file. Type: `LowCardinality(String)`. If the etag is unknown, the value is `NULL`.

**See Also**

- [Paimon table function](/docs/sql-reference/table-functions/paimon)

- [Syntax](#syntax)- [Arguments](#arguments)- [Virtual Columns](#virtual-columns)

Was this page helpful?