ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

Patch parts can be merged among themselves to reduce the amount of applied patches on `SELECT` queries and reduce the overhead. Merging of patch parts uses the [replacing](/docs/engines/table-engines/mergetree-family/replacingmergetree) merge algorithm with `_data_version` as a version column.
Therefore patch parts always store the latest version for each updated row in the part.

Lightweight updates don't wait for currently running merges and mutations to finish and always use a current snapshot of data parts to execute an update and produce a patch part.
Because of that there can be two cases of applying patch parts.

For example if we read part `A`, we need to apply patch part `X`:

- if `X` contains part `A` itself. It happens if `A` was not participating in merge when `UPDATE` was executed.
- if `X` contains part `B` and `C`, which are covered by part `A`. It happens if there was a merge (`B`, `C`) -> `A` running when `UPDATE` was executed.

For these two cases there are two ways to apply patch parts respectively:

- Using merge by sorted columns `_part`, `_part_offset`.
- Using join by `_block_number`, `_block_offset` columns.

The join mode is slower and requires more memory than the merge mode, but it is used less often.

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

- [`ALTER UPDATE`](/docs/sql-reference/statements/alter/update) - Heavy `UPDATE` operations
- [Lightweight `DELETE`](/docs/sql-reference/statements/delete) - Lightweight `DELETE` operations
- [`APPLY PATCHES`](/docs/sql-reference/statements/alter/apply-patches) - Force physical materialization of patches to data parts (mutation operation)

- [Examples](#examples)- [Lightweight updates do not update data immediately](#lightweight-update-does-not-update-data-immediately)- [Lightweight updates requirements](#lightweight-update-requirements)- [Lightweight deletes](#lightweight-delete)- [Performance considerations](#performance-considerations)- [Concurrent operations](#concurrent-operations)- [Update permissions](#update-permissions)- [Details of the implementation](#details-of-the-implementation)- [Related Content](#related-content)

Was this page helpful?
title_wip

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

```
USE [DATABASE] db
```

Lets you set the current database for the session.

The current database is used for searching for tables if the database is not explicitly defined in the query with a dot before the table name.

This query can't be made when using the HTTP protocol, since there is no concept of a session.

Was this page helpful?
title_wip

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

Deprecated feature

This feature is deprecated and will be removed in the future.

For your convenience, the old documentation is located [here](https://pastila.nl/?007cd3ec/47276db1eb25eb10c6ee043a44fdf597#AESDirdloBX4wF5BjPSZSA==)

Was this page helpful?
Table Functions

| --- | --- | --- | --- | --- | --- | | [arrowFlight](/docs/sql-reference/table-functions/arrowflight) Allows to perform queries on data exposed via an Apache Arrow Flight server.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [url](/docs/sql-reference/table-functions/url) Creates a table from the `URL` with given `format` and `structure`| [urlCluster](/docs/sql-reference/table-functions/urlCluster) Allows processing files from URL in parallel from many nodes in a specified cluster.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [values](/docs/sql-reference/table-functions/values) creates a temporary storage which fills columns with values.|  |  |  |  | | --- | --- | --- | --- | | [view](/docs/sql-reference/table-functions/view) Turns a subquery into a table. The function implements views.|  |  | | --- | --- | | [loop](/docs/sql-reference/table-functions/loop) The loop table function in ClickHouse is used to return query results in an infinite loop. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
Table Functions

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

Table functions can be used in the [`FROM`](/docs/sql-reference/statements/select/from)
clause of a `SELECT` query. For example, you can `SELECT` data from a file on your local
machine using the `file` table function.

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

```
./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:

```
SELECT * FROM generateSeries(1,5);
```

```
┌─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:

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

For example:

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

```
┌─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:

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

```
cat numbers.csv
1
2
3
4
5
```

Note

You can't use table functions if the [allow\_ddl](/docs/operations/settings/settings#allow_ddl) setting is disabled.

- [Usage](#usage)

Was this page helpful?
title_wip

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

Allows to perform queries on data exposed via an [Apache Arrow Flight](/docs/interfaces/arrowflight) server.

**Syntax**

```
arrowFlight('host:port', 'dataset_name' [, 'username', 'password'])
```

**Arguments**

- `host:port` — Address of the Arrow Flight server. [String](/docs/sql-reference/data-types/string).
- `dataset_name` — Name of the dataset or descriptor available on the Arrow Flight server. [String](/docs/sql-reference/data-types/string).
- `username` - Username to use with basic HTTP style authentication.
- `password` - Password to use with basic HTTP style authentication.
  If `username` and `password` are not specified, it means that authentication is not used
  (that will work only if the Arrow Flight server allows it).

**Returned value**

- A table object representing the remote dataset. The schema is inferred from the Arrow Flight response.

**Example**

Query:

```
SELECT * FROM arrowFlight('127.0.0.1:9005', 'sample_dataset') ORDER BY id;
```

Result:

```
┌─id─┬─name────┬─value─┐
│  1 │ foo     │ 42.1  │
│  2 │ bar     │ 13.3  │
│  3 │ baz     │ 77.0  │
└────┴─────────┴───────┘
```

**See Also**

- [Arrow Flight](/docs/engines/table-engines/integrations/arrowflight) table engine
- [Apache Arrow Flight SQL](https://arrow.apache.org/docs/format/FlightSql.html)

Was this page helpful?
deltaLakeCluster Table Function

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

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

Allows processing files from [Delta Lake](https://github.com/delta-io/delta) tables in Amazon S3 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")

```
deltaLakeCluster(cluster_name, url [,aws_access_key_id, aws_secret_access_key] [,format] [,structure] [,compression] [,extra_credentials])
deltaLakeCluster(cluster_name, named_collection[, option=value [,..]])

deltaLakeS3Cluster(cluster_name, url [,aws_access_key_id, aws_secret_access_key] [,format] [,structure] [,compression] [,extra_credentials])
deltaLakeS3Cluster(cluster_name, named_collection[, option=value [,..]])

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

`deltaLakeS3Cluster` is an alias to `deltaLakeCluster`, both are for S3.

## 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 [deltaLake](/docs/sql-reference/table-functions/deltalake) 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[​](#returned_value "Direct link to Returned value")

A table with the specified structure for reading data from cluster in the specified Delta Lake 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")

- [deltaLake engine](/docs/engines/table-engines/integrations/deltalake)
- [deltaLake table function](/docs/sql-reference/table-functions/deltalake)

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

Was this page helpful?
dictionary Table Function

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

Displays the [dictionary](/docs/sql-reference/statements/create/dictionary) data as a ClickHouse table. Works the same way as [Dictionary](/docs/engines/table-engines/special/dictionary) engine.

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

```
dictionary('dict')
```

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

- `dict` — A dictionary name. [String](/docs/sql-reference/data-types/string).

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

A ClickHouse table.

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

Input table `dictionary_source_table`:

```
┌─id─┬─value─┐
│  0 │     0 │
│  1 │     1 │
└────┴───────┘
```

Create a dictionary:

```
CREATE DICTIONARY new_dictionary(id UInt64, value UInt64 DEFAULT 0) PRIMARY KEY id
SOURCE(CLICKHOUSE(HOST 'localhost' PORT tcpPort() USER 'default' TABLE 'dictionary_source_table')) LAYOUT(DIRECT());
```

Query:

```
SELECT * FROM dictionary('new_dictionary');
```

Result:

```
┌─id─┬─value─┐
│  0 │     0 │
│  1 │     1 │
└────┴───────┘
```

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

- [Dictionary engine](/docs/engines/table-engines/special/dictionary)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Examples](#examples)- [Related](#related)

Was this page helpful?
title_wip

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

Parses data from arguments according to specified input format. If structure argument is not specified, it's extracted from the data.

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

```
format(format_name, [structure], data)
```

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

- `format_name` — The [format](/docs/sql-reference/formats) of the data.
- `structure` - Structure of the table. Optional. Format 'column1\_name column1\_type, column2\_name column2\_type, ...'.
- `data` — String literal or constant expression that returns a string containing data in specified format

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

A table with data parsed from `data` argument according to specified format and specified or extracted structure.

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

Without `structure` argument:

**Query:**

```
SELECT * FROM format(JSONEachRow,
$$
{"a": "Hello", "b": 111}
{"a": "World", "b": 123}
{"a": "Hello", "b": 112}
{"a": "World", "b": 124}
$$)
```

**Result:**

```
┌───b─┬─a─────┐
│ 111 │ Hello │
│ 123 │ World │
│ 112 │ Hello │
│ 124 │ World │
└─────┴───────┘
```

**Query:**

```
DESC format(JSONEachRow,
$$
{"a": "Hello", "b": 111}
{"a": "World", "b": 123}
{"a": "Hello", "b": 112}
{"a": "World", "b": 124}
$$)
```

**Result:**

```
┌─name─┬─type──────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ b    │ Nullable(Float64) │              │                    │         │                  │                │
│ a    │ Nullable(String)  │              │                    │         │                  │                │
└──────┴───────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
```

With `structure` argument:

**Query:**

```
SELECT * FROM format(JSONEachRow, 'a String, b UInt32',
$$
{"a": "Hello", "b": 111}
{"a": "World", "b": 123}
{"a": "Hello", "b": 112}
{"a": "World", "b": 124}
$$)
```

**Result:**

```
┌─a─────┬───b─┐
│ Hello │ 111 │
│ World │ 123 │
│ Hello │ 112 │
│ World │ 124 │
└───────┴─────┘
```

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

- [Formats](/docs/interfaces/formats)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Examples](#examples)- [Related](#related)

Was this page helpful?
title_wip

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

Perturbs the given query string with random variations.

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

```
fuzzQuery(query[, max_query_length[, random_seed]])
```

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

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `query` (String) - The source query to perform the fuzzing on.|  |  |  |  | | --- | --- | --- | --- | | `max_query_length` (UInt64) - A maximum length the query can get during the fuzzing process.|  |  | | --- | --- | | `random_seed` (UInt64) - A random seed for producing stable results. | | | | | | | |

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

A table object with a single column containing perturbed query strings.

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

```
SELECT * FROM fuzzQuery('SELECT materialize(\'a\' AS key) GROUP BY key') LIMIT 2;
```

```
   ┌─query──────────────────────────────────────────────────────────┐
1. │ SELECT 'a' AS key GROUP BY key                                 │
2. │ EXPLAIN PIPELINE compact = true SELECT 'a' AS key GROUP BY key │
   └────────────────────────────────────────────────────────────────┘
```

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

Was this page helpful?
title_wip

```
SELECT * FROM gcs('https://storage.googleapis.com/my-test-bucket-768/**/test-data.csv.gz', 'CSV', 'name String, value UInt32', 'gzip');
```

For production use cases it is recommended to use [named collections](/docs/operations/named-collections). Here is the example:

```
CREATE NAMED COLLECTION creds AS
        access_key_id = '***',
        secret_access_key = '***';
SELECT count(*)
FROM gcs(creds, url='https://s3-object-url.csv')
```

## Partitioned Write[​](#partitioned-write "Direct link to Partitioned Write")

If you specify `PARTITION BY` expression when inserting data into `GCS` table, a separate file is created for each partition value. Splitting the data into separate files helps to improve reading operations efficiency.

**Examples**

1. Using partition ID in a key creates separate files:

```
INSERT INTO TABLE FUNCTION
    gcs('http://bucket.amazonaws.com/my_bucket/file_{_partition_id}.csv', 'CSV', 'a String, b UInt32, c UInt32')
    PARTITION BY a VALUES ('x', 2, 3), ('x', 4, 5), ('y', 11, 12), ('y', 13, 14), ('z', 21, 22), ('z', 23, 24);
```

As a result, the data is written into three files: `file_x.csv`, `file_y.csv`, and `file_z.csv`.

2. Using partition ID in a bucket name creates files in different buckets:

```
INSERT INTO TABLE FUNCTION
    gcs('http://bucket.amazonaws.com/my_bucket_{_partition_id}/file.csv', 'CSV', 'a UInt32, b UInt32, c UInt32')
    PARTITION BY a VALUES (1, 2, 3), (1, 4, 5), (10, 11, 12), (10, 13, 14), (20, 21, 22), (20, 23, 24);
```

As a result, the data is written into three files in different buckets: `my_bucket_1/file.csv`, `my_bucket_10/file.csv`, and `my_bucket_20/file.csv`.

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

- [S3 table function](/docs/sql-reference/table-functions/s3)
- [S3 engine](/docs/engines/table-engines/integrations/s3)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Examples](#examples)- [Usage](#usage)- [Partitioned Write](#partitioned-write)- [Related](#related)

Was this page helpful?
title_wip

Note

`generateRandom(generateRandomStructure(), [random seed], max_string_length, max_array_length)` with a large enough `max_array_length` can generate a really huge output due to possible big nesting depth (up to 16) of complex types (`Array`, `Tuple`, `Map`, `Nested`).

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

- Blog: [Generating random data in ClickHouse](https://clickhouse.com/blog/generating-random-test-distribution-data-for-clickhouse)

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

Was this page helpful?
title_wip

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

Alias: `generateSeries`

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

Returns a table with the single 'generate\_series' column (`UInt64`) that contains integers from start to stop inclusively:

```
generate_series(START, STOP)
```

Returns a table with the single 'generate\_series' column (`UInt64`) that contains integers from start to stop inclusively with spacing between values given by `STEP`:

```
generate_series(START, STOP, STEP)
```

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

The following queries return tables with the same content but different column names:

```
SELECT * FROM numbers(10, 5);
SELECT * FROM generate_series(10, 14);
```

And the following queries return tables with the same content but different column names (but the second option is more efficient):

```
SELECT * FROM numbers(10, 11) WHERE number % 3 == (10 % 3);
SELECT * FROM generate_series(10, 20, 3);
```

- [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/hdfsCluster.md)

Allows processing files from HDFS in parallel from many nodes in a specified cluster. On initiator it creates a connection to all nodes in the cluster, discloses asterisks in HDFS file path, 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")

```
hdfsCluster(cluster_name, URI, format, structure)
```

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

|  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Argument Description|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `cluster_name` Name of a cluster that is used to build a set of addresses and connection parameters to remote and local servers.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `URI` URI to a file or a bunch of files. Supports following wildcards in readonly mode: `*`, `**`, `?`, `{'abc','def'}` and `{N..M}` where `N`, `M` — numbers, `abc`, `def` — strings. For more information see [Wildcards In Path](/docs/engines/table-engines/integrations/s3#wildcards-in-path).| `format` The [format](/docs/sql-reference/formats) of the file.| `structure` Structure of the table. Format `'column1_name column1_type, column2_name column2_type, ...'`. | | | | | | | | | |

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

A table with the specified structure for reading data in the specified file.

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

1. Suppose that we have a ClickHouse cluster named `cluster_simple`, and several files with following URIs on HDFS:

- 'hdfs://hdfs1:9000/some\_dir/some\_file\_1'
- 'hdfs://hdfs1:9000/some\_dir/some\_file\_2'
- 'hdfs://hdfs1:9000/some\_dir/some\_file\_3'
- 'hdfs://hdfs1:9000/another\_dir/some\_file\_1'
- 'hdfs://hdfs1:9000/another\_dir/some\_file\_2'
- 'hdfs://hdfs1:9000/another\_dir/some\_file\_3'

2. Query the amount of rows in these files:

```
SELECT count(*)
FROM hdfsCluster('cluster_simple', 'hdfs://hdfs1:9000/{some,another}_dir/some_file_{1..3}', 'TSV', 'name String, value UInt32')
```

3. Query the amount of rows in all files of these two directories:

```
SELECT count(*)
FROM hdfsCluster('cluster_simple', 'hdfs://hdfs1:9000/{some,another}_dir/*', 'TSV', 'name String, value UInt32')
```

Note

If your listing of files contains number ranges with leading zeros, use the construction with braces for each digit separately or use `?`.

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

- [HDFS engine](/docs/engines/table-engines/integrations/hdfs)
- [HDFS table function](/docs/sql-reference/table-functions/hdfs)

- [Syntax](#syntax)- [Arguments](#arguments)- [Returned value](#returned_value)- [Examples](#examples)- [Related](#related)

Was this page helpful?