Tipe data SelectDB - ApsaraDB for SelectDB

Pemilihan tipe data yang tepat untuk bidang tabel meningkatkan performa database dan efisiensi penyimpanan, sekaligus menjaga integritas data, keterbacaan, keamanan, serta konsistensi. Topik ini menjelaskan tipe data yang didukung oleh ApsaraDB for SelectDB. Anda juga dapat menjalankan pernyataan SHOW DATA TYPES; untuk melihat semua tipe data yang didukung oleh SelectDB.

Numeric types

Type name	Bytes	Description
BOOLEAN	1	Nilai Boolean. 0: false 1: true
TINYINT	1	Bilangan bulat bertanda. Rentang nilai: [-128, 127]
SMALLINT	2	Bilangan bulat bertanda. Rentang nilai: [-32768, 32767]
INT	4	Bilangan bulat bertanda. Rentang nilai: [-2147483648, 2147483647]
BIGINT	8	Bilangan bulat bertanda. Rentang nilai: [-9223372036854775808, 9223372036854775807]
LARGEINT	16	Bilangan bulat bertanda. Rentang nilai: [-2^127 + 1 ~ 2^127 - 1]
FLOAT	4	Bilangan titik mengambang. Rentang nilai: [-3.4×10^38 ~ 3.4×10^38]
DOUBLE	8	Bilangan titik mengambang. Rentang nilai: [-1.79×10^308 ~ 1.79×10^308]
DECIMAL	4/8/16	Bilangan titik tetap berpresisi tinggi. Format definisi: `DECIMAL(M[,D])` M: Jumlah total digit signifikan (presisi). Rentang nilai: [1, 38] D: Jumlah digit di belakang koma desimal (skala). Rentang nilai: [0, presisi] Ketika 0 <= presisi <= 9, menempati 4 byte. Ketika 9 < presisi <= 18, menempati 8 byte. Ketika 16 < presisi <= 38, menempati 16 byte.

Date types

Type name

Bytes

Description

DATE

Date type.

Value range: ['0000-01-01', '9999-12-31']

Default output format: yyyy-MM-dd

DATETIME

Date and time type.

Definition format: DATETIME([P])

Catatan

Time precision.
Default value: 0
Value range: [0, 6], supporting up to 6 decimal places (microseconds).

Value range: ['0000-01-01 00:00:00[.000000]', '9999-12-31 23:59:59[.999999]']

Default output format: yyyy-MM-dd HH:mm:ss.SSSSSS

String types

Type name	Bytes	Description
CHAR	M	Fixed-length string. Definition format: `CHAR(M)` Catatan M represents the byte length of the fixed-length string. Storage size: 1 to 255 bytes.
VARCHAR	Variable	Variable-length string. Definition format: `VARCHAR(M)` Catatan M represents the byte length of the variable-length string. Variable-length strings are stored in UTF-8 encoding. English characters typically use 1 byte, while Chinese characters use 3 bytes. Storage size: 1 to 65533 bytes.
STRING	Variable	Variable-length string. Default minimum storage size: 1048576 bytes (1 MB) Maximum storage size: 2147483643 bytes (2 GB) Penting You can adjust its storage size using the parameter `string_type_length_soft_limit_bytes`. STRING type can only be used for Value columns. It cannot be used for Key columns or partitioning/bucketing columns.

Semi-structured types

Type name	Bytes	Description
ARRAY	Variable	An array of elements of type T. Definition format: ARRAY<T> Catatan T is a basic data type. Supported types for T: `BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE, DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRING` Usage notes: Cannot be used as a Key column. Only supported in tables with Duplicate or Unique models.
MAP	Variable	A map of key-value pairs of types K and V. Definition format: MAP<K,V> Catatan Supported types for K and V: `BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DECIMALV3, DATE, DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRING` Usage notes: Cannot be used as a Key column. Only supported in tables with Duplicate or Unique models.
STRUCT	Variable	A struct composed of multiple fields, which can also be viewed as a collection of columns. Definition format: STRUCT<field_name:field_type, ... > Catatan field_name: Field identifier. Must be unique. field_type: Field data type. Usage notes: Cannot be used as a Key column. Only supported in tables with the Duplicate model.
JSON	Variable	Stored in binary JSON format. Access internal fields using JSON functions. Default supported storage size: 1048576 bytes (1 MB) Maximum supported storage size: 2147483643 bytes (2 GB) Catatan You can adjust its storage size using the parameter `jsonb_type_length_soft_limit_bytes`.
VARIANT	Variable	A dynamic data type designed for semi-structured data such as JSON. It accepts any valid JSON input and automatically splits JSON fields into sub-columns for improved storage efficiency and query analysis performance. VARIANT is especially useful for handling complex nested structures that may change over time. For more information, see VARIANT. Penting VARIANT is only supported in SelectDB kernel version `4.0.0` and later. If your version is older, upgrade the kernel version.

Aggregation types

Type name	Bytes	Description
HLL	Variable	HLL provides approximate deduplication. It performs better than Count Distinct on large datasets. Usage notes: Approximation error: Typically around 1%, sometimes up to 2%. Cannot be defined as a Key column during table creation. The aggregation type must be HLL_UNION. No need to specify length or default value. Storage size is internally managed based on data aggregation. Queries or operations must use functions such as `hll_union_agg`, `hll_raw_agg`, `hll_cardinality`, and `hll_hash` to take effect.
BITMAP	Variable	Used for exact deduplication, such as UV counting and audience segmentation. Usage notes: BITMAP columns can be defined in Aggregate, Unique, or Duplicate tables but must be non-Key columns. In Aggregate tables, they must be used with the BITMAP_UNION aggregation type. No need to specify length or default value. Storage size is internally managed based on data aggregation. BITMAP columns can only be queried or used with dedicated functions such as `bitmap_union_count`, `bitmap_union`, `bitmap_hash`, and `bitmap_hash64`.
QUANTILE_STATE	Variable	Used to compute approximate quantiles. During data ingestion, pre-aggregation is performed on different Values with the same Key. If the number of Values <= 2048: Store all values explicitly. If the number of Values > 2048: Use the TDigest algorithm to cluster data and store centroid points. Usage notes: Cannot be defined as a Key column during table creation. The table's aggregation type must be QUANTILE_UNION. No need to specify length or default value. Storage size is internally managed based on data aggregation. Can only be queried or used with dedicated functions such as `QUANTILE_PERCENT`, `QUANTILE_UNION`, and `TO_QUANTILE_STATE`.
AGG_STATE	Variable	Aggregate functions Used to accelerate aggregation computations. It must be used together with the `state/merge/union` function combinators. Usage notes: AGG_STATE cannot be used as a Key column. During table creation, you must also declare the signature of the aggregate function. No need to specify length or default value. Actual storage size depends on the function implementation.

IP types

Penting

Tipe data berikut hanya didukung di SelectDB kernel versi 4.0.0 atau yang lebih baru. Jika instance Anda menjalankan versi sebelumnya, Anda harus melakukan upgrade kernel. Untuk informasi selengkapnya, lihat Upgrade kernel version.

Type name	Bytes	Description
IPv4	4 bytes	Stores IPv4 addresses as 4-byte binary values. Use with ipv4_* family functions.
IPv6	16 bytes	Stores IPv6 addresses as 16-byte binary values. Use with ipv6_* family functions.