ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

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

A date. Stored in two bytes as the number of days since 1970-01-01 (unsigned). Allows storing values from just after the beginning of the Unix Epoch to the upper threshold defined by a constant at the compilation stage (currently, this is until the year 2149, but the final fully-supported year is 2148).

Supported range of values: [1970-01-01, 2149-06-06].

The date value is stored without the time zone.

**Example**

Creating a table with a `Date`-type column and inserting data into it:

```
CREATE TABLE dt
(
    `timestamp` Date,
    `event_id` UInt8
)
ENGINE = TinyLog;
```

```
-- Parse Date
-- - from string,
-- - from 'small' integer interpreted as number of days since 1970-01-01, and
-- - from 'big' integer interpreted as number of seconds since 1970-01-01.
INSERT INTO dt VALUES ('2019-01-01', 1), (17897, 2), (1546300800, 3);

SELECT * FROM dt;
```

```
┌──timestamp─┬─event_id─┐
│ 2019-01-01 │        1 │
│ 2019-01-01 │        2 │
│ 2019-01-01 │        3 │
└────────────┴──────────┘
```

**See Also**

- [Functions for working with dates and times](/docs/sql-reference/functions/date-time-functions)
- [Operators for working with dates and times](/docs/sql-reference/operators#operators-for-working-with-dates-and-times)
- [`DateTime` data type](/docs/sql-reference/data-types/datetime)

Was this page helpful?
title_wip

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

A date. Supports the date range same with [DateTime64](/docs/sql-reference/data-types/datetime64). Stored as a signed 32-bit integer in native byte order with the value representing the days since `1900-01-01`. **Important!** 0 represents `1970-01-01`, and negative values represent the days before `1970-01-01`.

**Examples**

Creating a table with a `Date32`-type column and inserting data into it:

```
CREATE TABLE dt32
(
    `timestamp` Date32,
    `event_id` UInt8
)
ENGINE = TinyLog;
```

```
-- Parse Date
-- - from string,
-- - from 'small' integer interpreted as number of days since 1970-01-01, and
-- - from 'big' integer interpreted as number of seconds since 1970-01-01.
INSERT INTO dt32 VALUES ('2100-01-01', 1), (47482, 2), (4102444800, 3);

SELECT * FROM dt32;
```

```
┌──timestamp─┬─event_id─┐
│ 2100-01-01 │        1 │
│ 2100-01-01 │        2 │
│ 2100-01-01 │        3 │
└────────────┴──────────┘
```

**See Also**

- [toDate32](/docs/sql-reference/functions/type-conversion-functions#toDate32)
- [toDate32OrZero](/docs/sql-reference/functions/type-conversion-functions#toDate32OrZero)
- [toDate32OrNull](/docs/sql-reference/functions/type-conversion-functions#toDate32OrNull)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/data-types/domains/index.md)

Domains are special-purpose types that add extra features on top of existing base types, while leaving the on-wire and on-disk format of the underlying data type intact. Currently, ClickHouse does not support user-defined domains.

You can use domains anywhere corresponding base type can be used, for example:

- Create a column of a domain type
- Read/write values from/to domain column
- Use it as an index if a base type can be used as an index
- Call functions with values of domain column

### Extra Features of Domains[​](#extra-features-of-domains "Direct link to Extra Features of Domains")

- Explicit column type name in `SHOW CREATE TABLE` or `DESCRIBE TABLE`
- Input from human-friendly format with `INSERT INTO domain_table(domain_column) VALUES(...)`
- Output to human-friendly format for `SELECT domain_column FROM domain_table`
- Loading data from an external source in the human-friendly format: `INSERT INTO domain_table FORMAT CSV ...`

### Limitations[​](#limitations "Direct link to Limitations")

- Can't convert index column of base type to domain type via `ALTER TABLE`.
- Can't implicitly convert string values into domain values when inserting data from another column or table.
- Domain adds no constrains on stored values.

- [Extra Features of Domains](#extra-features-of-domains)- [Limitations](#limitations)

Was this page helpful?
title_wip

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

A fixed-length string of `N` bytes (neither characters nor code points).

To declare a column of `FixedString` type, use the following syntax:

```
<column_name> FixedString(N)
```

Where `N` is a natural number.

The `FixedString` type is efficient when data has the length of precisely `N` bytes. In all other cases, it is likely to reduce efficiency.

Examples of the values that can be efficiently stored in `FixedString`-typed columns:

- The binary representation of IP addresses (`FixedString(16)` for IPv6).
- Language codes (ru\_RU, en\_US ... ).
- Currency codes (USD, RUB ... ).
- Binary representation of hashes (`FixedString(16)` for MD5, `FixedString(32)` for SHA256).

To store UUID values, use the [UUID](/docs/sql-reference/data-types/uuid) data type.

When inserting the data, ClickHouse:

- Complements a string with null bytes if the string contains fewer than `N` bytes.
- Throws the `Too large value for FixedString(N)` exception if the string contains more than `N` bytes.

Let's consider the following table with the single `FixedString(2)` column:

```
INSERT INTO FixedStringTable VALUES ('a'), ('ab'), ('');
```

```
SELECT
    name,
    toTypeName(name),
    length(name),
    empty(name)
FROM FixedStringTable;
```

```
┌─name─┬─toTypeName(name)─┬─length(name)─┬─empty(name)─┐
│ a    │ FixedString(2)   │            2 │           0 │
│ ab   │ FixedString(2)   │            2 │           0 │
│      │ FixedString(2)   │            2 │           1 │
└──────┴──────────────────┴──────────────┴─────────────┘
```

Note that the length of the `FixedString(N)` value is constant. The [length](/docs/sql-reference/functions/array-functions#length) function returns `N` even if the `FixedString(N)` value is filled only with null bytes, but the [empty](/docs/sql-reference/functions/array-functions#empty) function returns `1` in this case.

Selecting data with `WHERE` clause return various result depending on how the condition is specified:

- If equality operator `=` or `==` or `equals` function used, ClickHouse *doesn't* take `\0` char into consideration, i.e. queries `SELECT * FROM FixedStringTable WHERE name = 'a';` and `SELECT * FROM FixedStringTable WHERE name = 'a\0';` return the same result.
- If `LIKE` clause is used, ClickHouse *does* take `\0` char into consideration, so one may need to explicitly specify `\0` char in the filter condition.

```
SELECT name
FROM FixedStringTable
WHERE name = 'a'
FORMAT JSONStringsEachRow

{"name":"a\u0000"}


SELECT name
FROM FixedStringTable
WHERE name = 'a\0'
FORMAT JSONStringsEachRow

{"name":"a\u0000"}


SELECT name
FROM FixedStringTable
WHERE name = 'a'
FORMAT JSONStringsEachRow

Query id: c32cec28-bb9e-4650-86ce-d74a1694d79e

{"name":"a\u0000"}


SELECT name
FROM FixedStringTable
WHERE name LIKE 'a'
FORMAT JSONStringsEachRow

0 rows in set.


SELECT name
FROM FixedStringTable
WHERE name LIKE 'a\0'
FORMAT JSONStringsEachRow

{"name":"a\u0000"}
```

Was this page helpful?
title_wip

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

## IPv4[​](#ipv4 "Direct link to IPv4")

IPv4 addresses. Stored in 4 bytes as UInt32.

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

```
CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY url;

DESCRIBE TABLE hits;
```

```
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┐
│ url  │ String │              │                    │         │                  │
│ from │ IPv4   │              │                    │         │                  │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┘
```

OR you can use IPv4 domain as a key:

```
CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY from;
```

`IPv4` domain supports custom input format as IPv4-strings:

```
INSERT INTO hits (url, from) VALUES ('https://wikipedia.org', '116.253.40.133')('https://clickhouse.com', '183.247.232.58')('https://clickhouse.com/docs/en/', '116.106.34.242');

SELECT * FROM hits;
```

```
┌─url────────────────────────────────┬───────────from─┐
│ https://clickhouse.com/docs/en/ │ 116.106.34.242 │
│ https://wikipedia.org              │ 116.253.40.133 │
│ https://clickhouse.com          │ 183.247.232.58 │
└────────────────────────────────────┴────────────────┘
```

Values are stored in compact binary form:

```
SELECT toTypeName(from), hex(from) FROM hits LIMIT 1;
```

```
┌─toTypeName(from)─┬─hex(from)─┐
│ IPv4             │ B7F7E83A  │
└──────────────────┴───────────┘
```

IPv4 addresses can be directly compared to IPv6 addresses:

```
SELECT toIPv4('127.0.0.1') = toIPv6('::ffff:127.0.0.1');
```

```
┌─equals(toIPv4('127.0.0.1'), toIPv6('::ffff:127.0.0.1'))─┐
│                                                       1 │
└─────────────────────────────────────────────────────────┘
```

**See Also**

- [Functions for Working with IPv4 and IPv6 Addresses](/docs/sql-reference/functions/ip-address-functions)

- [IPv4](#ipv4)
  - [Basic Usage](#basic-usage)

Was this page helpful?
title_wip

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

## IPv6[​](#ipv6 "Direct link to IPv6")

IPv6 addresses. Stored in 16 bytes as UInt128 big-endian.

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

```
CREATE TABLE hits (url String, from IPv6) ENGINE = MergeTree() ORDER BY url;

DESCRIBE TABLE hits;
```

```
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┐
│ url  │ String │              │                    │         │                  │
│ from │ IPv6   │              │                    │         │                  │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┘
```

OR you can use `IPv6` domain as a key:

```
CREATE TABLE hits (url String, from IPv6) ENGINE = MergeTree() ORDER BY from;
```

`IPv6` domain supports custom input as IPv6-strings:

```
INSERT INTO hits (url, from) VALUES ('https://wikipedia.org', '2a02:aa08:e000:3100::2')('https://clickhouse.com', '2001:44c8:129:2632:33:0:252:2')('https://clickhouse.com/docs/en/', '2a02:e980:1e::1');

SELECT * FROM hits;
```

```
┌─url────────────────────────────────┬─from──────────────────────────┐
│ https://clickhouse.com          │ 2001:44c8:129:2632:33:0:252:2 │
│ https://clickhouse.com/docs/en/ │ 2a02:e980:1e::1               │
│ https://wikipedia.org              │ 2a02:aa08:e000:3100::2        │
└────────────────────────────────────┴───────────────────────────────┘
```

Values are stored in compact binary form:

```
SELECT toTypeName(from), hex(from) FROM hits LIMIT 1;
```

```
┌─toTypeName(from)─┬─hex(from)────────────────────────┐
│ IPv6             │ 200144C8012926320033000002520002 │
└──────────────────┴──────────────────────────────────┘
```

IPv6 addresses can be directly compared to IPv4 addresses:

```
SELECT toIPv4('127.0.0.1') = toIPv6('::ffff:127.0.0.1');
```

```
┌─equals(toIPv4('127.0.0.1'), toIPv6('::ffff:127.0.0.1'))─┐
│                                                       1 │
└─────────────────────────────────────────────────────────┘
```

**See Also**

- [Functions for Working with IPv4 and IPv6 Addresses](/docs/sql-reference/functions/ip-address-functions)

- [IPv6](#ipv6)
  - [Basic Usage](#basic-usage)

Was this page helpful?
title_wip

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

Allows to store special marker ([NULL](/docs/sql-reference/syntax)) that denotes "missing value" alongside normal values allowed by `T`. For example, a `Nullable(Int8)` type column can store `Int8` type values, and the rows that do not have a value will store `NULL`.

`T` can't be any of the following composite data types:

- [Array](/docs/sql-reference/data-types/array) — Not supported
- [Map](/docs/sql-reference/data-types/map) — Not supported
- [Tuple](/docs/sql-reference/data-types/tuple) — Experimental support available\*

However, composite data types **can contain** `Nullable` type values, e.g. `Array(Nullable(Int8))` or `Tuple(Nullable(String), Nullable(Int64))`.

Experimental: Nullable Tuples

- [Nullable(Tuple(...))](/docs/sql-reference/data-types/tuple#nullable-tuple) is supported when `allow_experimental_nullable_tuple_type = 1` is enabled.

A `Nullable` type field can't be included in table indexes.

`NULL` is the default value for any `Nullable` type, unless specified otherwise in the ClickHouse server configuration.

## Storage Features[​](#storage-features "Direct link to Storage Features")

To store `Nullable` type values in a table column, ClickHouse uses a separate file with `NULL` masks in addition to normal file with values. Entries in masks file allow ClickHouse to distinguish between `NULL` and a default value of corresponding data type for each table row. Because of an additional file, `Nullable` column consumes additional storage space compared to a similar normal one.

Note

Using `Nullable` almost always negatively affects performance, keep this in mind when designing your databases.

## Finding NULL[​](#finding-null "Direct link to Finding NULL")

It is possible to find `NULL` values in a column by using `null` subcolumn without reading the whole column. It returns `1` if the corresponding value is `NULL` and `0` otherwise.

**Example**

Query:

```
CREATE TABLE nullable (`n` Nullable(UInt32)) ENGINE = MergeTree ORDER BY tuple();

INSERT INTO nullable VALUES (1) (NULL) (2) (NULL);

SELECT n.null FROM nullable;
```

Result:

```
┌─n.null─┐
│      0 │
│      1 │
│      0 │
│      1 │
└────────┘
```

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

```
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog
```

```
INSERT INTO t_null VALUES (1, NULL), (2, 3)
```

```
SELECT x + y FROM t_null
```

```
┌─plus(x, y)─┐
│       ᴺᵁᴸᴸ │
│          5 │
└────────────┘
```

- [Storage Features](#storage-features)- [Finding NULL](#finding-null)- [Usage Example](#usage-example)

Was this page helpful?
title_wip

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

The `QBit` data type reorganizes vector storage for faster approximate searches. Instead of storing each vector's elements together, it groups the same binary digit positions across all vectors.
This stores vectors at full precision while letting you choose the fine-grained quantization level at search time: read fewer bits for less I/O and faster calculations, or more bits for higher accuracy. You get the speed benefits of reduced data transfer and computation from quantization, but all the original data remains available when needed.

To declare a column of `QBit` type, use the following syntax:

```
column_name QBit(element_type, dimension)
```

- `element_type` – the type of each vector element. The allowed types are `BFloat16`, `Float32` and `Float64`
- `dimension` – the number of elements in each vector

## Creating QBit[​](#creating-qbit "Direct link to Creating QBit")

Using the `QBit` type in table column definition:

```
CREATE TABLE test (id UInt32, vec QBit(Float32, 8)) ENGINE = Memory;
INSERT INTO test VALUES (1, [1, 2, 3, 4, 5, 6, 7, 8]), (2, [9, 10, 11, 12, 13, 14, 15, 16]);
SELECT vec FROM test ORDER BY id;
```

```
┌─vec──────────────────────┐
│ [1,2,3,4,5,6,7,8]        │
│ [9,10,11,12,13,14,15,16] │
└──────────────────────────┘
```

## QBit subcolumns[​](#qbit-subcolumns "Direct link to QBit subcolumns")

`QBit` implements a subcolumn access pattern that allows you to access individual bit planes of the stored vectors. Each bit position can be accessed using the `.N` syntax, where `N` is the bit position:

```
CREATE TABLE test (id UInt32, vec QBit(Float32, 8)) ENGINE = Memory;
INSERT INTO test VALUES (1, [0, 0, 0, 0, 0, 0, 0, 0]);
INSERT INTO test VALUES (1, [-0, -0, -0, -0, -0, -0, -0, -0]);
SELECT bin(vec.1) FROM test;
```

```
┌─bin(tupleElement(vec, 1))─┐
│ 00000000                  │
│ 11111111                  │
└───────────────────────────┘
```

The number of accessible subcolumns depends on the element type:

- `BFloat16`: 16 subcolumns (1-16)
- `Float32`: 32 subcolumns (1-32)
- `Float64`: 64 subcolumns (1-64)

## Vector search functions[​](#vector-search-functions "Direct link to Vector search functions")

These are the distance functions for vector similarity search that use `QBit` data type:

- [`L2DistanceTransposed`](/docs/sql-reference/functions/distance-functions#L2DistanceTransposed)
- [`cosineDistanceTransposed`](/docs/sql-reference/functions/distance-functions#cosineDistanceTransposed)

- [Creating QBit](#creating-qbit)- [QBit subcolumns](#qbit-subcolumns)- [Vector search functions](#vector-search-functions)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/data-types/special-data-types/nothing.md)

The only purpose of this data type is to represent cases where a value is not expected. So you can't create a `Nothing` type value.

For example, literal [NULL](/docs/sql-reference/syntax#null) has type of `Nullable(Nothing)`. See more about [Nullable](/docs/sql-reference/data-types/nullable).

The `Nothing` type can also used to denote empty arrays:

```
SELECT toTypeName(array())
```

```
┌─toTypeName(array())─┐
│ Array(Nothing)      │
└─────────────────────┘
```

Was this page helpful?
title_wip

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

Strings of an arbitrary length. The length is not limited. The value can contain an arbitrary set of bytes, including null bytes.
The String type replaces the types VARCHAR, BLOB, CLOB, and others from other DBMSs.

When creating tables, numeric parameters for string fields can be set (e.g. `VARCHAR(255)`), but ClickHouse ignores them.

Aliases:

- `String` — `LONGTEXT`, `MEDIUMTEXT`, `TINYTEXT`, `TEXT`, `LONGBLOB`, `MEDIUMBLOB`, `TINYBLOB`, `BLOB`, `VARCHAR`, `CHAR`, `CHAR LARGE OBJECT`, `CHAR VARYING`, `CHARACTER LARGE OBJECT`, `CHARACTER VARYING`, `NCHAR LARGE OBJECT`, `NCHAR VARYING`, `NATIONAL CHARACTER LARGE OBJECT`, `NATIONAL CHARACTER VARYING`, `NATIONAL CHAR VARYING`, `NATIONAL CHARACTER`, `NATIONAL CHAR`, `BINARY LARGE OBJECT`, `BINARY VARYING`,

## Encodings[​](#encodings "Direct link to Encodings")

ClickHouse does not have the concept of encodings. Strings can contain an arbitrary set of bytes, which are stored and output as-is.
If you need to store texts, we recommend using UTF-8 encoding. At the very least, if your terminal uses UTF-8 (as recommended), you can read and write your values without making conversions.
Similarly, certain functions for working with strings have separate variations that work under the assumption that the string contains a set of bytes representing a UTF-8 encoded text.
For example, the [length](/docs/sql-reference/functions/array-functions#length) function calculates the string length in bytes, while the [lengthUTF8](/docs/sql-reference/functions/string-functions#lengthUTF8) function calculates the string length in Unicode code points, assuming that the value is UTF-8 encoded.

- [Encodings](#encodings)

Was this page helpful?
title_wip

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

In this section, we will take a look at ClickHouse's support for Data Lakes.
ClickHouse supports many of the most popular table formats and data catalogs, including Iceberg, Delta Lake, Hudi, AWS Glue, REST Catalog, Unity Catalog and Microsoft OneLake.

## Iceberg[​](#iceberg "Direct link to Iceberg")

See [iceberg](https://clickhouse.com/docs/sql-reference/table-functions/iceberg) which supports reading from Amazon S3 and S3-compatible services, HDFS, Azure and local file systems. [icebergCluster](https://clickhouse.com/docs/sql-reference/table-functions/icebergCluster) is the distributed variant of the `iceberg` function.

## Delta Lake[​](#delta-lake "Direct link to Delta Lake")

See [deltaLake](https://clickhouse.com/docs/sql-reference/table-functions/deltalake) which supports reading from Amazon S3 and S3-compatible services, Azure and local file systems. [deltaLakeCluster](https://clickhouse.com/docs/sql-reference/table-functions/deltalakeCluster) is the distributed variant of the `deltaLake` function.

## Hudi[​](#hudi "Direct link to Hudi")

See [hudi](https://clickhouse.com/docs/sql-reference/table-functions/hudi) which supports reading from Amazon S3 and S3-compatible services. [hudiCluster](https://clickhouse.com/docs/sql-reference/table-functions/hudiCluster) is the distributed variant of the `hudi` function.

# Data catalogs

## AWS Glue[​](#aws-glue "Direct link to AWS Glue")

AWS Glue Data Catalog can be used with Iceberg tables. You can use it with the `iceberg` table engine, or with the [DataLakeCatalog](https://clickhouse.com/docs/engines/database-engines/datalakecatalog) database engine.

## Iceberg REST Catalog[​](#iceberg-rest-catalog "Direct link to Iceberg REST Catalog")

The Iceberg REST Catalog can be used with Iceberg tables. You can use it with the `iceberg` table engine, or with the [DataLakeCatalog](https://clickhouse.com/docs/engines/database-engines/datalakecatalog) database engine.

## Unity Catalog[​](#unity-catalog "Direct link to Unity Catalog")

Unity Catalog can be used with both Delta Lake and Iceberg tables. You can use it with the `iceberg` or `deltaLake` table engines, or with the [DataLakeCatalog](https://clickhouse.com/docs/engines/database-engines/datalakecatalog) database engine.

## Microsoft OneLake[​](#microsoft-onelake "Direct link to Microsoft OneLake")

Microsoft OneLake can be used with both Delta Lake and Iceberg tables. You can use it with the [DataLakeCatalog](https://clickhouse.com/docs/engines/database-engines/datalakecatalog) database engine.

- [Iceberg](#iceberg)- [Delta Lake](#delta-lake)- [Hudi](#hudi)- [AWS Glue](#aws-glue)- [Iceberg REST Catalog](#iceberg-rest-catalog)- [Unity Catalog](#unity-catalog)- [Microsoft OneLake](#microsoft-onelake)

Was this page helpful?
title_wip

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

By default, the `CREATE`, `DROP`, `ALTER`, and `RENAME` queries affect only the current server where they are executed. In a cluster setup, it is possible to run such queries in a distributed manner with the `ON CLUSTER` clause.

For example, the following query creates the `all_hits` `Distributed` table on each host in `cluster`:

```
CREATE TABLE IF NOT EXISTS all_hits ON CLUSTER cluster (p Date, i Int32) ENGINE = Distributed(cluster, default, hits)
```

In order to run these queries correctly, each host must have the same cluster definition (to simplify syncing configs, you can use substitutions from ZooKeeper). They must also connect to the ZooKeeper servers.

The local version of the query will eventually be executed on each host in the cluster, even if some hosts are currently not available.

References

The order for executing queries within a single host is guaranteed.

Was this page helpful?