StarRocks进阶-阿里云开发者社区

[toc]

表类型

StarRocks 支持四种表类型，分别是明细表 (Duplicate key table)、聚合表 (Aggregate table)、更新表 (Unique Key table) 和主键表 ( Primary Key table)，更新表逐渐被主键表取代。

建表注意事项：

建表后，表类型不支持修改
在建表语句中，排序键必须定义在其他列之前
在创建表时，您可以将一个或多个列定义为排序键。排序键在建表语句中的出现次序，为数据存储时多重排序的次序
不支持排序键的数据类型为 BITMAP、HLL
前缀索引的长度限制为 36 字节。如果排序键中全部列的值的长度加起来超过 36 字节，则前缀索引仅会保存限制范围内排序键的若干前缀列。所以排序键的列数量不宜太多，每列存储的数据不宜太大。

主键表

主键表中的主键具有唯一非空约束，用于唯一标识数据行。如果新数据的主键值与表中原数据的主键值相同，则存在唯一约束冲突，此时新数据会替代原数据。

主键表优势在于支撑实时数据更新的同时，也能保证高效的复杂即席查询性能。


CREATE TABLE primary_key_table (
    order_id bigint NOT NULL,
    dt date NOT NULL,
    merchant_id int NOT NULL,
    user_id int NOT NULL,
    good_id int NOT NULL,
    good_name string NOT NULL,
    price int NOT NULL,
    cnt int NOT NULL,
    revenue int NOT NULL,
    state tinyint NOT NULL
)
PRIMARY KEY (order_id,dt,merchant_id)
PARTITION BY date_trunc('day', dt)
DISTRIBUTED BY HASH (merchant_id)
ORDER BY (dt,merchant_id)
PROPERTIES (
    "enable_persistent_index" = "true"
);

PRIMARY KEY标识表类型为主键表，并指定主键列
DISTRIBUTED BY HASH 由于主键表仅支持分桶策略为哈希分桶，因此您还需要通过 DISTRIBUTED BY HASH () 定义哈希分桶键
enable_persistent_index 持久化主键索引
如果使用了数据分布策略，由于目前主键表要求主键必须包括分区列和分桶列，假设采用的数据分布策略是将 dt 作为分区列并且 merchant_id 作为哈希分桶列，则主键还需要包括 dt 和 merchant_id，即
```sql
```
      PRIMARY KEY (order_id,dt,merchant_id)
      PARTITION BY date_trunc('day', dt)           
      DISTRIBUTED BY HASH (merchant_id)
```



* ORDER BY 指定排序键


### 明细表


明细表适用于日志数据分析等场景，支持追加新数据，不支持修改历史数据。


明细表是默认创建的表类型。如果在建表时未指定任何 key，默认创建的是明细表。


```sql

CREATE TABLE IF NOT EXISTS duplicate_key_table (
    event_time DATETIME NOT NULL COMMENT "datetime of event",
    event_type INT NOT NULL COMMENT "type of event",
    user_id INT COMMENT "id of user",
    device_code INT COMMENT "device code",
    channel INT COMMENT ""
)
DUPLICATE KEY(event_time, event_type)
DISTRIBUTED BY HASH(user_id)
PROPERTIES (
"replication_num" = "3"
);

建表时必须使用 DISTRIBUTED BY HASH 子句指定分桶键，否则建表失败
DUPLICATE KEY 排序键必须定义在其他列之前
如果未指定排序键，则默认选择表的前三列作为排序键

聚合表

建表时，支持定义排序键和指标列，并为指标列指定聚合函数。当多条数据具有相同的排序键时，指标列会进行聚合。在分析统计和汇总数据时，聚合表能够减少查询时所需要处理的数据，提升查询效率。

例如需要分析某一段时间内，来自不同城市的用户，访问不同网页的总次数。则可以将网页地址 site_id、日期 date 和城市代码 city_code 作为排序键，将访问次数 pv 作为指标列，并为指标列 pv 指定聚合函数为 SUM。


CREATE TABLE IF NOT EXISTS aggregate_key_table (
    site_id LARGEINT NOT NULL COMMENT "id of site",
    date DATE NOT NULL COMMENT "time of event",
    city_code VARCHAR(20) COMMENT "city_code of user",
    pv BIGINT SUM DEFAULT "0" COMMENT "total page views"
)
AGGREGATE KEY(site_id, date, city_code)
DISTRIBUTED BY HASH(site_id)
PROPERTIES (
"replication_num" = "3"
);

AGGREGATE KEY 标识表类型为聚合表，同时指定排序键
排序键可以通过 AGGREGATE KEY 显式定义，若未显示定义，默认除指标列之外的列均为排序键
排序键必须满足唯一性约束，必须包含全部维度列，并且列的值不会更新
SUM 通过在列名后指定聚合函数，定义该列为指标列。一般为需要汇总统计的数据

排序键

数据导入至某个类型的表，会按照建表时指定的一列或多列排序后存储，这部分用于排序的列就称为排序键。

排序键通常为查询时过滤条件频繁使用的一个或者多个列，用以加速查询。

明细表中，数据按照排序键 DUPLICATE KEY 排序，并且排序键不需要满足唯一性约束。
聚合表中，数据按照排序键 AGGREGATE KEY 聚合后排序，并且排序键需要满足唯一性约束。
更新表中，数据按照排序键 UNIQUE KEY REPLACE 后排序，并且排序键需要满足唯一性约束。
主键表支持分别定义主键和排序键，主键 PRIMARY KEY 需要满足唯一性和非空约束，主键相同的数据进行 REPLACE（替换）。排序键是用于排序，由 ORDER BY 指定。

表引擎

默认为 olap，表示创建的是 StarRocks 内部表

可选值：mysql、elasticsearch、hive、jdbc (2.3 及以后)、iceberg、hudi（2.2 及以后）。如果指定了可选值，则创建的是对应类型的外部表 (external table)，在建表时需要使用 CREATE EXTERNAL TABLE。

StarRocks 支持以外部表 (External Table) 的形式，接入其他数据源。外部表指的是保存在其他数据源中的数据表，而 StartRocks 只保存表对应的元数据，并直接向外部表所在数据源发起查询。目前 StarRocks 已支持的第三方数据源包括 MySQL、StarRocks、Elasticsearch、Apache Hive™、Apache Iceberg 和 Apache Hudi。对于 StarRocks 数据源，现阶段只支持 Insert 写入，不支持读取，对于其他数据源，现阶段只支持读取，还不支持写入。

在创建外部表的时候，需要在 PROPERTIES 中指定连接到外部数据库服务器的信息，如下所示：


CREATE EXTERNAL RESOURCE jdbc0
PROPERTIES (
    "type" = "jdbc",
    "user" = "postgres",
    "password" = "changeme",
    "jdbc_uri" = "jdbc:postgresql://127.0.0.1:5432/jdbc_test",
    "driver_url" = "https://repo1.maven.org/maven2/org/postgresql/postgresql/42.3.3/postgresql-42.3.3.jar",
    "driver_class" = "org.postgresql.Driver"
);

数据类型

数值类型

TINYINT ：1 字节有符号整数，范围 [-128, 127]
SMALLINT：2 字节有符号整数，范围 [-32768, 32767]
INT ：4 字节有符号整数，范围 [-2147483648, 2147483647]。
BIGINT ：8 字节有符号整数，范围 [-9223372036854775808, 9223372036854775807]
LARGEINT：16 字节有符号整数，范围 [-2^127 + 1, 2^127 - 1]
DECIMAL：DECIMAL(p,s) ，P 代表一共有多少个有效数字 (precision)，S 代表小数点后最多有多少数字 (scale)，默认是 Decimal（10，0）
DOUBLE：8字节浮点数
FLOAT：4字节浮点数
BOOLEAN：与 TINYINT 一样，0 代表 false，1 代表 true

字符串类型

STRING：字符串，最大长度 65533 字节
BINARY/VARBINARY：自 3.0 版本起，StarRocks 支持 BINARY/VARBINARY 数据类型，用于存储二进制数据，单位为字节。支持的最大长度与 VARCHAR 类型相同，M 的取值范围为 [1,1048576]。如果未指定 M，默认为最大值 1048576。BINARY 是 VARBINARY 的别名，用法与 VARBINARY 相同。
CHAR：CHAR(M) 定长字符串，M 代表的是定长字符串的长度。单位：字节。M 的范围是 [1, 255]
VARCHAR：VARCHAR(M)，变长字符串。M 代表变长字符串长度，单位：字节，默认取值为 1，
M 的取值范围为 [1, 1048576]

日期类型

DATE：日期类型，目前的取值范围是 ['0000-01-01', '9999-12-31']，默认的打印形式是 YYYY-MM-DD
DATETIME：日期时间类型，取值范围是 ['0000-01-01 00:00:00', '9999-12-31 23:59:59']。打印的形式是 YYYY-MM-DD HH: MM: SS

半结构化类型

JSON

StarRocks 支持存储和高效查询分析 JSON 数据。StarRocks 采用二进制格式编码来存储 JSON 数据，而不是直接存储所输入文本，因此在数据计算查询时，降低解析成本，从而提升查询效率

当前 JSON 类型数据支持的最大长度为 16 MB

支持使用 <，<=，>，>=， =，!= 运算符查询 JSON 数据，不支持使用 IN 运算符


CREATE TABLE `tj` (
    `id` INT(11) NOT NULL COMMENT "",
    `j`  JSON NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
    "replication_num" = "1",
    "storage_format" = "DEFAULT"
);


INSERT INTO tj (id, j) VALUES (1, parse_json('{"a": 1, "b": true}'));
INSERT INTO tj (id, j) VALUES (2, parse_json('{"a": 2, "b": false}'));
INSERT INTO tj (id, j) VALUES (3, parse_json('{"a": 3, "b": true}'));
INSERT INTO tj (id, j) VALUES (4, json_object('a', 4, 'b', false)); 


//查询 j 列中 a 为1 的数据行
select * from tj where j->'a' = 1;

PARSE_JSON 函数能够基于字符串类型的数据构造出 JSON 类型的数据。

JSON_OBJECT 函数能够构造出 JSON 对象类型的数据，可以将现有的表转成 JSON 类型

函数

字符串函数

concat (str,......) 拼接字符串

日期和时间

年月日


SELECT CURDATE()
SELECT  current_date()

时分秒


SELECT curtime();
SELECT current_time();

年月日时分秒


SELECT now();
SELECT current_timestamp()

格式化

date_format ，将日期类型按照 format 的类型转化为字符串

条件函数

case


-- 写法1
select gender, case gender when 1 then 'male' 
                                    when 0 then 'female' 
                                    else 'error' 
                   end gender_str
-- 写法2
select gender, case when gender = 1 then 'male' 
                          when gender = 0 then 'female' 
                    end gender_str

若未指定else ，匹配不到就是null

coalesce

coalesce(expr1,...); 从左向右返回参数中的第一个非 NULL 表达式


-- 返回3
select coalesce(3,NULL,1,1);

if

if(expr1,expr2,expr3); 若参数 expr1 成立，返回结果 expr2，否则返回结果 expr3

expr1: 支持的数据类型为 BOOLEAN。
expr2 和 expr3 必须在数据类型上能够兼容，否则返回报错。

ifnull

ifnull(expr1,expr2); 若 expr1 不为 NULL，返回 expr1。若 expr1 为 NULL，返回 expr2。 expr1 与 expr2 必须在数据类型上能够兼容，否则返回报错。

nullif

nullif(expr1,expr2); 若参数 expr1 与 expr2 相等，则返回 NULL，否则返回 expr1 的值。

地理位置

ST_Distance_Sphere

ST_Distance_Sphere(x_lng, x_lat, y_lng, y_lat)，计算地球两点之间的球面距离，单位是「米」。


-- 天安门到鸟巢的距离

 select st_distance_sphere(116.405778,39.909677, 116.402185,39.999775);

DDL

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT（简称 CTAS）语句可用于同步或异步查询原表并基于查询结果创建新表，然后将查询结果插入到新表中。您可以通过 SUBMIT TASK 创建异步 CTAS 任务。

示例：

同步查询原表 order 并根据查询结果创建新表 order_new，然后将查询结果插入到新表中


CREATE TABLE order_new
AS SELECT * FROM order;

同步查询原表 order中的 k1、k2 和 k3 列并根据查询结果创建新表 order_new，然后将查询结果插入到新表中，并指定新表中列的名称为 a、b 和 c


CREATE TABLE order_new (a, b, c)
AS SELECT k1, k2, k3 FROM order;

或者 

CREATE TABLE order_new
AS SELECT k1 AS a, k2 AS b, k3 AS c FROM order;

同步查询原表 customers 并根据查询结果创建新表 customers_new，然后将查询结果插入到新表中。并且指定新表为主键表并且指定其排序键为 first_name 和 last_name。


CREATE TABLE customers_pk
PRIMARY KEY (customer_id)
ORDER BY (first_name,last_name)
AS SELECT  * FROM customers;

异步查询原表 order_detail 并根据查询结果创建新表 order_statistics，然后将查询结果插入到新表中



SUBMIT TASK AS 
CREATE TABLE order_detail_plus AS 
SELECT * FROM order_detail;

查询任务信息：


SELECT * FROM INFORMATION_SCHEMA.tasks;

查询任务执行状态：


SELECT * FROM INFORMATION_SCHEMA.task_runs;

时区

查看时区 SHOW VARIABLES LIKE '%time_zone%';
设置时区：
- 全局设置： SET global time_zone ='Asia/Shanghai'
- session级别设置： SET time_zone ='Asia/Shanghai'

HttpApi

StarRocks 3.2.0 版本提供了 HTTP SQL API，方便用户通过 HTTP 协议使用 StarRocks 的查询功能，当前支持 SELECT、SHOW、EXPLAIN、KILL 语句

使用 curl 命令的一个语法示例：


curl -X POST 'http://<fe_ip>:<fe_http_port>/api/v1/catalogs/<catalog_name>/databases/<database_name>/sql' \
   -u '<username>:<password>'  -d '{"query": "<sql_query>;", "sessionVariables":{"<var_name>":<var_value>}}' \
   --header "Content-Type: application/json"

引用

https://docs.starrocks.io/zh/docs/introduction/

StarRocks进阶

表类型

主键表

聚合表

排序键

表引擎