ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Encryption functions

**Examples**

**Create table and insert data**

```
-- Let's create a table where user_id is the unique user id, encrypted is an encrypted string field, iv is an initial vector for decrypt/encrypt.
-- Assume that users know their id and the key to decrypt the encrypted field:
CREATE TABLE decrypt_null
(
    dt DateTime,
    user_id UInt32,
    encrypted String,
    iv String
)
ENGINE = MergeTree;

-- Insert some data:
INSERT INTO decrypt_null VALUES
('2022-08-02 00:00:00', 1, encrypt('aes-256-gcm', 'value1', 'keykeykeykeykeykeykeykeykeykey01', 'iv1'), 'iv1'),
('2022-09-02 00:00:00', 2, encrypt('aes-256-gcm', 'value2', 'keykeykeykeykeykeykeykeykeykey02', 'iv2'), 'iv2'),
('2022-09-02 00:00:01', 3, encrypt('aes-256-gcm', 'value3', 'keykeykeykeykeykeykeykeykeykey03', 'iv3'), 'iv3');

-- Try decrypt with one key
SELECT
    dt,
    user_id,
    tryDecrypt('aes-256-gcm', encrypted, 'keykeykeykeykeykeykeykeykeykey02', iv) AS value
FROM decrypt_null
ORDER BY user_id ASC
```

```
┌──────────────────dt─┬─user_id─┬─value──┐
│ 2022-08-02 00:00:00 │       1 │ ᴺᵁᴸᴸ   │
│ 2022-09-02 00:00:00 │       2 │ value2 │
│ 2022-09-02 00:00:01 │       3 │ ᴺᵁᴸᴸ   │
└─────────────────────┴─────────┴────────┘
```

- [HMAC](#HMAC)- [aes\_decrypt\_mysql](#aes_decrypt_mysql)- [aes\_encrypt\_mysql](#aes_encrypt_mysql)- [decrypt](#decrypt)- [encrypt](#encrypt)- [tryDecrypt](#tryDecrypt)

Was this page helpful?
Functions for splitting strings

**Returned value**

Returns the resulting array of tokens from input string. [`Array`](/docs/sql-reference/data-types/array)

**Examples**

**Default tokenizer**

```
SELECT tokensForLikePattern('%test1,test2,test3%') AS tokens;
```

```
['test2']
```

- [alphaTokens](#alphaTokens)- [arrayStringConcat](#arrayStringConcat)- [extractAllGroupsVertical](#extractAllGroupsVertical)- [ngrams](#ngrams)- [reverseBySeparator](#reverseBySeparator)- [splitByChar](#splitByChar)- [splitByNonAlpha](#splitByNonAlpha)- [splitByRegexp](#splitByRegexp)- [splitByString](#splitByString)- [splitByWhitespace](#splitByWhitespace)- [tokens](#tokens)- [tokensForLikePattern](#tokensForLikePattern)

Was this page helpful?
FORMAT Clause

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

ClickHouse supports a wide range of [serialization formats](/docs/interfaces/formats) that can be used on query results among other things. There are multiple ways to choose a format for `SELECT` output, one of them is to specify `FORMAT format` at the end of query to get resulting data in any specific format.

Specific format might be used either for convenience, integration with other systems or performance gain.

## Default Format[​](#default-format "Direct link to Default Format")

If the `FORMAT` clause is omitted, the default format is used, which depends on both the settings and the interface used for accessing the ClickHouse server. For the [HTTP interface](/docs/interfaces/http) and the [command-line client](/docs/interfaces/client) in batch mode, the default format is `TabSeparated`. For the command-line client in interactive mode, the default format is `PrettyCompact` (it produces compact human-readable tables).

## Implementation Details[​](#implementation-details "Direct link to Implementation Details")

When using the command-line client, data is always passed over the network in an internal efficient format (`Native`). The client independently interprets the `FORMAT` clause of the query and formats the data itself (thus relieving the network and the server from the extra load).

- [Default Format](#default-format)- [Implementation Details](#implementation-details)

Was this page helpful?
INTO OUTFILE Clause

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

`INTO OUTFILE` clause redirects the result of a `SELECT` query to a file on the **client** side.

Compressed files are supported. Compression type is detected by the extension of the file name (mode `'auto'` is used by default). Or it can be explicitly specified in a `COMPRESSION` clause. The compression level for a certain compression type can be specified in a `LEVEL` clause.

**Syntax**

```
SELECT <expr_list> INTO OUTFILE file_name [AND STDOUT] [APPEND | TRUNCATE] [COMPRESSION type [LEVEL level]]
```

`file_name` and `type` are string literals. Supported compression types are: `'none'`, `'gzip'`, `'deflate'`, `'br'`, `'xz'`, `'zstd'`, `'lz4'`, `'bz2'`.

`level` is a numeric literal. Positive integers in following ranges are supported: `1-12` for `lz4` type, `1-22` for `zstd` type and `1-9` for other compression types.

## Implementation Details[​](#implementation-details "Direct link to Implementation Details")

- This functionality is available in the [command-line client](/docs/interfaces/client) and [clickhouse-local](/docs/operations/utilities/clickhouse-local). Thus a query sent via [HTTP interface](/docs/interfaces/http) will fail.
- The query will fail if a file with the same file name already exists.
- The default [output format](/docs/interfaces/formats) is `TabSeparated` (like in the command-line client batch mode). Use [FORMAT](/docs/sql-reference/statements/select/format) clause to change it.
- If `AND STDOUT` is mentioned in the query then the output that is written to the file is also displayed on standard output. If used with compression, the plaintext is displayed on standard output.
- If `APPEND` is mentioned in the query then the output is appended to an existing file. If compression is used, append cannot be used.
- When writing to a file that already exists, `APPEND` or `TRUNCATE` must be used.

**Example**

Execute the following query using [command-line client](/docs/interfaces/client):

```
clickhouse-client --query="SELECT 1,'ABC' INTO OUTFILE 'select.gz' FORMAT CSV;"
zcat select.gz
```

Result:

```
1,"ABC"
```

- [Implementation Details](#implementation-details)

Was this page helpful?
JOIN clause

Example:

```
SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10
```

```
┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘
```

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

- Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Part 1](https://clickhouse.com/blog/clickhouse-fully-supports-joins)
- Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 2](https://clickhouse.com/blog/clickhouse-fully-supports-joins-hash-joins-part2)
- Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 3](https://clickhouse.com/blog/clickhouse-fully-supports-joins-full-sort-partial-merge-part3)
- Blog: [ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 4](https://clickhouse.com/blog/clickhouse-fully-supports-joins-direct-join-part4)

- [Supported types of JOIN](#supported-types-of-join)- [Settings](#settings)- [ON section conditions](#on-section-conditions)- [JOIN with inequality conditions for columns from different tables](#join-with-inequality-conditions-for-columns-from-different-tables)- [NULL values in JOIN keys](#null-values-in-join-keys)- [ASOF JOIN usage](#asof-join-usage)- [PASTE JOIN usage](#paste-join-usage)- [Distributed JOIN](#distributed-join)- [Implicit type conversion](#implicit-type-conversion)- [Usage recommendations](#usage-recommendations)
                    - [Processing of empty or NULL cells](#processing-of-empty-or-null-cells)- [Syntax](#syntax)- [Syntax Limitations](#syntax-limitations)- [Performance](#performance)- [Memory limitations](#memory-limitations)- [Examples](#examples)- [Related content](#related-content)

Was this page helpful?
iceberg Table Function

Note

- Only Iceberg format version 2 tables are supported (v1 snapshots do not guarantee `manifest-list`, which is required to safely identify files for cleanup)
- The current snapshot is always preserved, even if it is older than the specified timestamp
- Requires the `allow_insert_into_iceberg` setting to be enabled
- Requires the `allow_experimental_expire_snapshots` setting to be enabled
- The catalog's own authorization (REST catalog auth, AWS Glue IAM, etc.) is enforced independently when ClickHouse updates the metadata

## See Also[​](#see-also "Direct link to See Also")

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

- [Syntax](#syntax)- [Arguments](#arguments)
    - [Returned value](#returned-value)- [Example](#example)- [Defining a named collection](#defining-a-named-collection)- [Using a data catalog](#iceberg-writes-catalogs)- [Schema Evolution](#schema-evolution)- [Partition Pruning](#partition-pruning)- [Time Travel](#time-travel)- [Processing of tables with deleted rows](#deleted-rows)
                - [Basic usage](#basic-usage)- [Important considerations](#important-considerations)- [Example scenarios](#example-scenarios)- [Metadata File Resolution](#metadata-file-resolution)
                  - [Candidate Search (in Priority Order)](#candidate-search)- [Selecting the Most Recent File](#most-recent-file)- [Metadata cache](#metadata-cache)- [Aliases](#aliases)- [Virtual Columns](#virtual-columns)- [Writes into iceberg table](#writes-into-iceberg-table)
                          - [Creating table](#create-iceberg-table)- [Example](#example-iceberg-writes-create)- [INSERT](#writes-inserts)- [Example](#example-iceberg-writes-insert)- [DELETE](#iceberg-writes-delete)- [Example](#example-iceberg-writes-delete)- [Schema evolution](#iceberg-writes-schema-evolution)- [Example](#example-iceberg-writes-evolution)- [Compaction](#iceberg-writes-compaction)- [Expire Snapshots](#iceberg-expire-snapshots)- [See Also](#see-also)

Was this page helpful?
ClickStack demo days

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/clickstack/demo-days/index.md)

## ClickStack demo days[​](#clickstack-demo-days "Direct link to ClickStack demo days")

These folders contain recordings from our Demo Days - our internal dev calls, where we share what we’re currently building and exploring in ClickStack.

Some of what you'll see will make it into the product. Some is shared to get early feedback. And some is just experimental and may never ship.

If something catches your eye, check out the linked PRs and let us know what you think. We want this to be a space where the community can stay close to what's happening and help shape where ClickStack goes next.

If you've got questions or feedback, reach out on the ClickHouse Slack channel [#o11y-clickstack](https://clickhouse.com/slack) or via our [public repository](https://github.com/hyperdxio/hyperdx/tree/main).

- [ClickStack demo days](#clickstack-demo-days)

Was this page helpful?
Demo days - 03/04/2026

The demo shows a group with a few customisation options toggled: collapsible on and off, border visible or hidden, tabs enabled or not. At demo time the PR was still in review with design feedback being gathered. Once it lands, it should give dashboard authors a more flexible and less confusing set of building blocks compared to the old two-container-type model.

**Related PRs:** [#1972](https://github.com/hyperdxio/hyperdx/pull/1972) Dashboard groups with tabs and collapsible/bordered options, [#2015](https://github.com/hyperdxio/hyperdx/pull/2015) Unify section/group into single Group

## ClickStack CLI[​](#clickstack-cli "Direct link to ClickStack CLI")

*Demo by [@wrn14897](https://github.com/wrn14897)*

The ClickStack CLI (`hdx`) is a new terminal TUI for searching, tailing, and inspecting logs and traces without leaving your terminal. It connects to your HyperDX instance using the same web session mechanism as the browser, so there's no separate API key to manage. After a one-time `hdx auth login` with your instance URL and email, it stays authenticated.

The TUI surface is the same search interface you'd get in the web app: same query syntax, same source selection, same ability to drill into individual log entries. One highlight is the trace waterfall view, where clicking into a log entry shows the full distributed trace rendered directly in the terminal. The demo also previews an early experiment with agentic usage: by giving an AI agent access to the CLI's schema introspection output alongside the ability to run queries via the ClickHouse proxy, the agent can investigate issues autonomously. The demo takes it further and shows the agent using web sessions to navigate the HyperDX UI via Playwright, pulling metrics from rendered charts to cross-reference with what it found in the logs.

**Related PRs:** [#2043](https://github.com/hyperdxio/hyperdx/pull/2043) Add @hyperdx/cli package — terminal TUI for searching and tailing events

- [New dashboard and saved search listing pages](#new-dashboard-and-saved-search-listing-pages)- [Filters for filters](#filters-for-filters)- [RBAC for predefined dashboards](#rbac-for-predefined-dashboards)- [Optimizations for searching](#optimizations-for-searching)- [Copy row and configurable filter sizes](#copy-row-and-configurable-filter-sizes)- [Tabs and groups in dashboards](#tabs-and-groups-in-dashboards)- [ClickStack CLI](#clickstack-cli)

Was this page helpful?
Changelog 2026

#### Experimental Feature[​](#experimental-feature "Direct link to Experimental Feature")

- Add experimental support for WebAssembly-based user-defined functions (UDFs), allowing custom function logic to be implemented in WebAssembly and executed within ClickHouse. Special thanks to [Alexey Smirnov](https://github.com/lioshik) for contributing the Wasmtime backend support. [#88747](https://github.com/ClickHouse/ClickHouse/pull/88747) ([Vladimir Cherkasov](https://github.com/vdimir)). Incremental improvements for WASM UDF support. [#99373](https://github.com/ClickHouse/ClickHouse/pull/99373) ([Vasily Chekalkin](https://github.com/bacek)).
- Add support for external SQL dialects using `polyglot` library. [#99496](https://github.com/ClickHouse/ClickHouse/pull/99496) ([Alexey Milovidov](https://github.com/alexey-milovidov)).
- Add `ALP` floating-point compression codec (without ALP\_rd fallback for non-compressible doubles). [#91362](https://github.com/ClickHouse/ClickHouse/pull/91362) ([Nazarii Piontko](https://github.com/nazarii-piontko)).
- Add experimental lazy type hints for `JSON` columns. When enabled via `allow_experimental_json_lazy_type_hints`, `ALTER TABLE ... MODIFY COLUMN json JSON(path TypeName)` that only adds or modifies type hints completes instantly as a metadata-only operation, without rewriting historical data. Type hints are applied at query time for old parts and materialized during INSERTs and background merges. [#97412](https://github.com/ClickHouse/ClickHouse/pull/97412) ([tanner-bruce](https://github.com/tanner-bruce)).
- Enable parallel reads from YTsaurus table engine. [#97343](https://github.com/ClickHouse/ClickHouse/pull/97343) ([MikhailBurdukov](https://github.com/MikhailBurdukov)).

#### Performance Improvement[​](#performance-improvement "Direct link to Performance Improvement")
Changelog 2026

- Support text index built on `mapValues(map)` with `IN` operator. [#99286](https://github.com/ClickHouse/ClickHouse/pull/99286) ([Anton Popov](https://github.com/CurtizJ)).
- Shell-like completion support in clickhouse keeper-client (handle completions of arguments in quotes, i.e. `'foo ba'`, handle escaped arguments, i.e. `foo\ ba`, make `ls` print quoted nodes, if they have whitespaces). [#99312](https://github.com/ClickHouse/ClickHouse/pull/99312) ([Azat Khuzhin](https://github.com/azat)).
- Prevent Keeper `mntr` command from getting stuck because of lock contention. [#99472](https://github.com/ClickHouse/ClickHouse/pull/99472) ([Antonio Andelic](https://github.com/antonio2368)).
- Reduce lock contention in Keeper dispatcher by invoking callbacks and dispatching read requests outside mutex scope, and add profiled lock guards for observability. [#99751](https://github.com/ClickHouse/ClickHouse/pull/99751) ([Antonio Andelic](https://github.com/antonio2368)).
- Tolerate missing padding at the end of the last block of parquet files. [#99857](https://github.com/ClickHouse/ClickHouse/pull/99857) ([Seva Potapov](https://github.com/seva-potapov)).
Changelog 2026

#### Experimental Feature[​](#experimental-feature-1 "Direct link to Experimental Feature")

- The text index is now GA. [#96794](https://github.com/ClickHouse/ClickHouse/pull/96794) ([Robert Schulze](https://github.com/rschu1ze)).
- The `QBit` data type for quantized bit-packed vector storage (used for approximate nearest-neighbor search) is now generally available and no longer requires enabling an experimental setting. [#95358](https://github.com/ClickHouse/ClickHouse/pull/95358) ([Raufs Dunamalijevs](https://github.com/rienath)).
- Vector search in ClickHouse can now use replicas in the cluster to *distribute* the load and search of vector index parts. This enables ClickHouse to support large vector indexes that exceed the memory capacity of a single VM. [#95876](https://github.com/ClickHouse/ClickHouse/pull/95876) ([Shankar Iyer](https://github.com/shankar-iyer)).
- Add server-side AST fuzzer controlled by `ast_fuzzer_runs` and `ast_fuzzer_any_query` settings. When enabled, the server runs randomized mutations of each query after its normal execution, discarding the results. [#97568](https://github.com/ClickHouse/ClickHouse/pull/97568) ([Alexey Milovidov](https://github.com/alexey-milovidov)).
- Add `iif` function to the experimental KQL dialect. [#94790](https://github.com/ClickHouse/ClickHouse/pull/94790) ([happyso](https://github.com/sunyeongchoi)).
- Schema inference now respects `allow_experimental_nullable_tuple_type`. When enabled, it allows inferred tuple types to be `Nullable(Tuple(...))`, so missing nested objects can become `NULL` instead of a tuple of `NULL` elements. [#95525](https://github.com/ClickHouse/ClickHouse/pull/95525) ([Nihal Z. Miaji](https://github.com/nihalzp)).
- The `use_statistics_cache` setting is now enabled by default, so column statistics are cached in memory to speed up query optimization without needing to reload them from each part. [#95950](https://github.com/ClickHouse/ClickHouse/pull/95950) ([Han Fei](https://github.com/hanfei1991)).

#### Performance Improvement[​](#performance-improvement-1 "Direct link to Performance Improvement")
Changelog 2026

- Fix constant string comparisons not respecting the settings `cast_string_to_date_time_mode`, `bool_true_representation`, `bool_false_representation`, and `input_format_null_as_default`. Closes [#91681](https://github.com/ClickHouse/ClickHouse/issues/91681). [#95040](https://github.com/ClickHouse/ClickHouse/pull/95040) ([Nihal Z. Miaji](https://github.com/nihalzp)).
- Fix data race in the filesystem cache. [#95064](https://github.com/ClickHouse/ClickHouse/pull/95064) ([Alexey Milovidov](https://github.com/alexey-milovidov)).
- Fix a rare race condition in the Parquet reader. [#95068](https://github.com/ClickHouse/ClickHouse/pull/95068) ([Alexey Milovidov](https://github.com/alexey-milovidov)).
- Fix crash in the top K optimization when `LIMIT` is zero. Closes [#93893](https://github.com/ClickHouse/ClickHouse/issues/93893). [#95072](https://github.com/ClickHouse/ClickHouse/pull/95072) ([Alexey Milovidov](https://github.com/alexey-milovidov)).
- Converting from DateTime/integers to Time64 extracts the time-of-day component using `toTime`, which is not monotonic. The `ToDateTimeMonotonicity` template incorrectly claimed this conversion was monotonic, causing "Invalid binary search result in MergeTreeSetIndex" exception in debug builds. [#95125](https://github.com/ClickHouse/ClickHouse/pull/95125) ([Alexey Milovidov](https://github.com/alexey-milovidov)).
- Recreated list of manifest file entries only if necessary (previously it was done on each iteration). [#95162](https://github.com/ClickHouse/ClickHouse/pull/95162) ([Daniil Ivanik](https://github.com/divanik)).
Changelog 2026

#### Build/Testing/Packaging Improvement[​](#buildtestingpackaging-improvement-2 "Direct link to Build/Testing/Packaging Improvement")

- Add a set of tools for profiling memory allocations in the ClickHouse SQL parser using jemalloc's heap profiling capabilities. [#94072](https://github.com/ClickHouse/ClickHouse/pull/94072) ([Ilya Yatsishin](https://github.com/qoega)).
- Added a tool that simplifies debugging of memory allocations in parser. It uses jemalloc `stats.allocated` metric before and after we parse query to AST representation to show what is allocated. Also it supports memory profiling mode that dumps profile before and after to build reports where allocations occurred. [#93523](https://github.com/ClickHouse/ClickHouse/pull/93523) ([Ilya Yatsishin](https://github.com/qoega)).
- Remove transitive libc++ includes. [#92523](https://github.com/ClickHouse/ClickHouse/pull/92523) ([Raúl Marín](https://github.com/Algunenano)).
- Make some sequential tests parallel: <https://github.com/ClickHouse/ClickHouse/pull/93030/changes#diff-c3a73510dae653c9bbfa24300b32f5d6ec663fd4e72cc4a3d5daa6e4342915df>. [#93030](https://github.com/ClickHouse/ClickHouse/pull/93030) ([Nikita Fomichev](https://github.com/fm4v)).
- Cleanup some build flags. [#93679](https://github.com/ClickHouse/ClickHouse/pull/93679) ([Raúl Marín](https://github.com/Algunenano)).
- Bump c-ares from v1.34.5 to v1.34.6. This addresses c-ares' `CVE-2025-62408`, which is not relevant for ClickHouse. [#94129](https://github.com/ClickHouse/ClickHouse/pull/94129) ([Govind R Nair](https://github.com/Revertionist)).
- Use `curl` 8.18.0. [#94742](https://github.com/ClickHouse/ClickHouse/pull/94742) ([Konstantin Bogdanov](https://github.com/thevar1able)).

- [ClickHouse release 26.3 LTS, 2026-03-26. Presentation, Video](#263)- [ClickHouse release 26.2, 2026-02-26. Presentation, Video](#262)- [ClickHouse release 26.1, 2026-01-29. Presentation, Video](#261)

Was this page helpful?
2019 Changelog

#### Experimental Feature[​](#experimental-feature-2 "Direct link to Experimental Feature")

- Input and output data format `Template`. It allows to specify custom format string for input and output. [#4354](https://github.com/ClickHouse/ClickHouse/issues/4354) [#6727](https://github.com/ClickHouse/ClickHouse/pull/6727) ([tavplubix](https://github.com/tavplubix))
- Implementation of `LIVE VIEW` tables that were originally proposed in [#2898](https://github.com/ClickHouse/ClickHouse/pull/2898), prepared in [#3925](https://github.com/ClickHouse/ClickHouse/issues/3925), and then updated in [#5541](https://github.com/ClickHouse/ClickHouse/issues/5541). See [#5541](https://github.com/ClickHouse/ClickHouse/issues/5541) for detailed description. [#5541](https://github.com/ClickHouse/ClickHouse/issues/5541) ([vzakaznikov](https://github.com/vzakaznikov)) [#6425](https://github.com/ClickHouse/ClickHouse/pull/6425) ([Nikolai Kochetov](https://github.com/KochetovNicolai)) [#6656](https://github.com/ClickHouse/ClickHouse/pull/6656) ([vzakaznikov](https://github.com/vzakaznikov)) Note that `LIVE VIEW` feature may be removed in next versions.

#### Bug Fix[​](#bug-fix-8 "Direct link to Bug Fix")