ApsaraDB for SelectDB:Data types

DECIMAL

Definition format: DECIMAL(M[,D])

Storage size is determined by the precision value:

DATETIME

Definition format: DATETIME([P])

P controls the sub-second precision (number of decimal places):

• Default: 0 (second-level precision)

• Range: [0, 6], where 6 gives microsecond precision

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

Output format: yyyy-MM-dd HH:mm:ss.SSSSSS

ARRAY

Supported element types for T:

BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE,
DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRING

Limitations:

• Cannot be used as a Key column.

• Only supported in tables with the Duplicate or Unique model.

MAP

Supported types for K and V:

BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DECIMALV3, DATE,
DATEV2, DATETIME, DATETIMEV2, CHAR, VARCHAR, STRING

Limitations:

• Cannot be used as a Key column.

• Only supported in tables with the Duplicate or Unique model.

STRUCT

• field_name: Field identifier. Must be unique within the struct.

• field_type: Data type of the field.

Limitations:

• Cannot be used as a Key column.

• Only supported in tables with the Duplicate model.

JSON

• Default storage size: 1,048,576 bytes (1 MB). Maximum: 2,147,483,643 bytes (2 GB).

• Adjust the storage size with the jsonb_type_length_soft_limit_bytes parameter.

VARIANT

• Accepts any valid JSON input and automatically splits JSON fields into sub-columns for improved storage efficiency and query performance.

• Useful for complex nested structures that may change over time.

• For details, see VARIANT.

HLL

HLL provides approximate deduplication with a typical error rate of ~1% (up to 2%).

Limitations and requirements:

• Cannot be defined as a Key column. The aggregation type must be HLL_UNION.

• No length or default value is required — storage is managed internally based on data aggregation.

• Query HLL columns using functions: hll_union_agg, hll_raw_agg, hll_cardinality, hll_hash.

BITMAP

BITMAP provides exact deduplication and is commonly used for UV counting and audience segmentation.

Limitations and requirements:

• Supported in Aggregate, Unique, and Duplicate tables, but only as non-Key columns. In Aggregate tables, use with the BITMAP_UNION aggregation type.

• No length or default value is required — storage is managed internally based on data aggregation.

• Query BITMAP columns using functions: bitmap_union_count, bitmap_union, bitmap_hash, bitmap_hash64.

QUANTILE_STATE

QUANTILE_STATE computes approximate quantiles. During ingestion, values with the same Key are pre-aggregated:

• If the number of values <= 2048: all values are stored explicitly.

• If the number of values > 2048: the TDigest algorithm clusters data and stores centroid points.

Limitations and requirements:

• Cannot be defined as a Key column. The table's aggregation type must be QUANTILE_UNION.

• No length or default value is required — storage is managed internally based on data aggregation.

• Query QUANTILE_STATE columns using functions: QUANTILE_PERCENT, QUANTILE_UNION, TO_QUANTILE_STATE.

AGG_STATE

AGG_STATE stores intermediate aggregation state to accelerate aggregation computations. Use it with the state, merge, and union function combinators.

Limitations and requirements:

• Cannot be used as a Key column.

• When creating a table, declare the signature of the aggregate function alongside the AGG_STATE column.

• No length or default value is required — actual storage size depends on the function implementation.