Capabilities of different table types
Key columns and sort key
| Primary Key table | Duplicate Key table | Aggregate table | Unique Key table | |
|---|---|---|---|---|
| Key columns and UNIQUE constraints | The primary key has the UNIQUE constraint and NOT NULL constraint. | The duplicate key does not have the UNIQUE constraint. | The aggregate key has the UNIQUE constraint. | The unique key has the UNIQUE constraint. |
| Logical relationships between key columns and data changes | If the primary key value of a new data row is the same as that of an existing data row in the table, unique constraint violation occurs. Then the new data row will replace the existing data row. Compared to the Unique Key table, the Primary Key table has an enhanced underlying storage engine and therefore can replace the Unique Key table. | The duplicate key does not have the UNIQUE constraint. So, if the duplicate Key value of a new data row is the same as that of an existing data row in the table, both the new and old data rows are retained in the table. | If the aggregate Key value of a new data row is the same as that of an existing data row in the table, the new and old data rows will be aggregated based on the aggregate key and the aggregate functions of value columns. | If the unique key value of a new data row is the same as that of an existing data row in the table, the new data row will replace the existing data row. The Unique Key table can be seen as an Aggregate table whose aggregate function is replace. |
| Relationship between key columns and sort key | Since v3.0.0, the sort key is decoupled from the primary key in the Primary Key table. | The key columns and sort key are coupled. | ||
| Data types supported by key columns and sort key | Numeric (including integers and BOOLEAN), string, and date (DATE and DATETIME). | Numeric (including integers, BOOLEAN, and DECIMAL), string, and date (DATE and DATETIME). | ||
| Relationship between key columns and partitioning/bucketing columns | Partitioning columns and bucketing columns must be in the primary key. | None | Partitioning columns and bucketing columns must be in the aggregate key. | Partitioning columns and bucketing columns must be in the unique key. |
Data types of key and value columns
The key columns support the following data types: numeric (including integers, BOOLEAN, and DECIMAL), string, and date (DATE and DATETIME).
note
The key columns of a Primary Key table do not support the DECIMAL data type.
On the other hand, the value columns support basic data types, including numeric, string, and date (DATE and DATETIME). The support for BITMAP, HLL, and semi-structured types varies for value columns of different types of tables, as detailed below:
| Primary Key table | Duplicate Key table | Aggregate table | Unique Key table | |
|---|---|---|---|---|
| BITMAP | Supported | Not supported | Supported. The aggregate functions must be bitmap_union, replace, or replace_if_not_null. | Supported |
| HLL | Supported | Not supported | Supported. The aggregate functions must be hll_union, replace, or replace_if_not_null. | Supported |
| PERCENTILE | Supported | Not supported | Supported. The aggregate functions must be percentile_union, replace, or replace_if_not_null. | Supported |
| semi-structured data types：JSON/ARRAY/MAP/STRUCT | Supported | Supported | Supported. The aggregate functions must be replace or replace_if_not_null. | Supported |
Data change
| Primary Key Table | Duplicate Key table | Aggregate table | Unique Key table | |
|---|---|---|---|---|
| INSERT through data loading | Supported. Configure __op=0 in the load job to achieve INSERT. In the internal implementation, StarRocks considers both INSERT and UPDATE operations as UPSERT operations. | Supported | Supported (Data rows with the same aggregate Key value will be aggregated.) | Supported (Data rows with the same unique key value will be updated.) |
| UPDATE through data loading | Not supported | Supported (This can be achieved by using replace as the aggregate function.) | Supported (The Unique Key table itself can be considered as an Aggregate table using the replace aggregate function.) | |
| DELETE through data loading | Supported. Configure __op=1 in the load job to achieve DELETE. | Not supported | ||
| Integrity of data column values to be loaded | By default, all column values need to be loaded. However, if partial column update (partial_update) is enabled, or a column has a default value, not all column values need to be loaded. | By default, all column values need to be loaded. However, if a column has a default value, not all column values need to be loaded. | By default, all column values need to be loaded. However, Aggregate tables can achieve partial column update by specifying the aggregate functions for value columns as REPLACE_IF_NOT_NULL. For details, see aggr_type. Also, if a column has a default value, not all column values need to be loaded. | By default, all column values need to be loaded. However, if a column has a default value, not all column values need to be loaded. |
| DML INSERT | Supported | |||
| DML UPDATE |
| Not supported | ||
| DML DELETE |
|
|
| |
Compatibility with other features
| Primary Key table | Duplicate Key table | Aggregate table | Unique Key table | ||
|---|---|---|---|---|---|
| Bitmap index/bloom filter index | Build indexes on key columns | Supported | |||
| Build indexes on value columns | Supported | Supported | Not supported | Not supported | |
| Partitioning/bucketing | Expression partitioning/list partitioning | Supported | |||
| Random bucketing | Not supported | Supported from v3.1 onwards | Not supported | Not supported | |
| Materialized views | Asynchronous materialized views | Supported | |||
| Synchronous materialized views | Not supported | Supported | Supported | Supported | |
| Other features | CTAS | Supported | Supported | Not supported | Not supported |
| Backup & restore | Supported from v2.5 onwards | Supported | |||