ApsaraDB for ClickHouse:Data types

Category

Keyword

Data type

Valid value or description

Integer

Int8

INT8

Valid values: -128 to 127.

Int16

Int16

Valid values: -32768 to 32767.

Int32

Int32

Valid values: -2147483648 to 2147483647.

Int64

Int64

Valid values: -9223372036854775808 to 9223372036854775807.

Floating-point

Float32

Single-precision floating-point type

Similar to a FLOAT value in the C++ programming language, a single-precision floating-point number takes up 4 bytes. Single-precision floating-point numbers are 32-bit binary data.

Float64

Double-precision floating-point type

Similar to a DOUBLE value in the C++ programming language, a double-precision floating-point number takes up 8 bytes. Double-precision floating-point numbers are 64-bit binary data.

Decimal

Decimal

Decimal

DECIMAL values are signed fixed-point numbers. The numeric precision of these values can be maintained during the following mathematical operations: addition, subtraction, and multiplication. DECIMAL values can be in the following formats:

• Decimal(P, S)

• Decimal32(S)

• Decimal64(S)

• Decimal128(S)

String

String

STRING

A string can be of a random length and can contain a random set of bytes, including null bytes. Therefore, the STRING type in ApsaraDB for ClickHouse is equivalent to the VARCHAR, BLOB, and CLOB types in other database management systems.

FixedString

FIXEDSTRING

The FIXEDSTRING type is efficient when data has the length of precisely N bytes. In other cases, efficiency may be impaired. Examples of the values that can be efficiently stored in FIXEDSTRING-typed columns:

• Binary representation of IP addresses. For IPv6 addresses, use the FIXEDSTRING(16) type.

• Language codes, such as ru_RU and en_US.

• Currency codes, such as USD and RUB.

• Binary representation of hash values. For MD5 hash values, use the FIXEDSTRING(16) type. For SHA-256 hash values, use the FIXEDSTRING(32) type.

Date and time

Date

DATE

A DATE value takes up two bytes and indicates the number of days that have elapsed since Thursday, 1 January 1970. DATE values are unsigned. DATE values are stored without the time zone.

DateTime

TIMESTAMP

A TIMESTAMP value takes up four bytes and indicates the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970. TIMESTAMP values are unsigned. The value range of the TIMESTAMP type is the same as the value range of the DATE type. The minimum TIMESTAMP value is 1970-01-01 00:00:00. TIMESTAMP values are accurate to seconds. Leap seconds are not supported. The time zone of a TIMESTAMP value is the system time zone of the related client or server.

Datetime64

DATETIME64

You can use this data type to store both the date and time of a specific point in time.

Boolean

Boolean

Boolean

ClickHouse does not support the BOOLEAN type. Use the UINT8 type for Boolean values. Valid values are 0 and 1.

Array

Array

ARRAY

An ARRAY value is a collection of elements of the same data type. The elements can be of a random data type, even the ARRAY type. However, we recommend that you do not use multi-dimensional arrays, because ClickHouse supports multi-dimensional arrays only to a limited extent. For example, multi-dimensional arrays cannot be stored in MergeTree tables.

Tuple

Tuple

TUPLE

A TUPLE value is a collection of elements of different data types. TUPLE values cannot be stored in tables, except for memory tables. You can use TUPLE values to group temporary columns. In queries, you can use IN expressions and lambda functions with specific parameters to group temporary columns.

Domain

Domain

Domain

The implementation of the DOMAIN type varies based on different values:

If the values are IPv4 addresses, the DOMAIN type is binary compatible with the UINT32 type. Compared with the UINT32 type, the DOMAIN type saves the binary storage space and supports more readable input and output formats.

If the values are IPv6 addresses, the DOMAIN type is binary compatible with the FIXEDSTRING(16) type. Compared with the FIXEDSTRING(16) type, the DOMAIN type saves the binary storage space and supports more readable input and output formats.

Enumeration

Enum8

Enum8

Valid values: -128 to 127.

Enum16

Enum16

Valid values: -32768 to 32767.

ENUM16 values can be empty.

Nullable

Nullable

Unless otherwise stated in ClickHouse server configurations, the default value of the NULLABLE type is NULL. NULLABLE values cannot be included in table indexes.

Nested

nested

nested

A nested data structure is similar to a table inside a cell. You can specify the parameters of a nested data structure, such as field name and data type, the same way that you specify parameters in a CREATE TABLE statement. Each row in a CREATE TABLE statement can correspond to a random number of rows in a nested data structure.