StarRocks进阶

举报
一颗苹果 发表于 2024/11/19 14:04:23 2024/11/19
【摘要】 表类型 主键表 明细表 聚合表 排序键 表引擎 表 删除表 数据类型 数值类型 字符串类型 日期类型 半结构化类型 ARRAY JSON 索引 唯一索引 函数 字符串函数 日期和时间 条件函数 case coalesce if ifnull nullif 地理位置 ST_Distance_Sphere Array函数 DDL CREATE TABLE AS SELECT ALTER DML...

表类型

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，即
    
            PRIMARY KEY (order_id,dt,merchant_id)
            PARTITION BY date_trunc('day', dt)           
            DISTRIBUTED BY HASH (merchant_id)
  • ORDER BY 指定排序键

明细表

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

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


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"
);

表

删除表


 drop table    user

数据类型

数值类型

  • 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

半结构化类型

ARRAY

数组（Array) 是数据库中的一种扩展数据类型，其相关特性在众多数据库系统中均有支持，可以用作用户标签分析、人群画像等场景。StarRocks 当前支持多维数组嵌套、数组切片、比较、过滤等特性。

数组列的定义形式为 ARRAY<type>，其中 type 表示数组内的元素类型。

数组元素目前支持以下数据类型：BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、VARCHAR、CHAR、DATETIME、DATE、JSON、BINARY (3.0 及以后）、MAP (3.1 及以后）、STRUCT (3.1 及以后）、Fast Decimal (3.1 及以后)。

创建ARRAY列示例：


-- 建表并指定其中的 `c1` 列为一维数组，元素类型为 INT。
create table t0(
  c0 INT,
  c1 ARRAY<INT>
)
duplicate key(c0)
distributed by hash(c0);

-- 建表并指定 `c1` 为两层的嵌套数组，元素类型为 VARCHAR。
create table t1(
  c0 INT,
  c1 ARRAY<ARRAY<VARCHAR(10)>>
)
duplicate key(c0)
distributed by hash(c0);

-- 建表并定义 NOT NULL 数组的列。
create table t2(
  c0 INT,
  c1 ARRAY<INT> NOT NULL
)
duplicate key(c0)
distributed by hash(c0);

查询示例：


-- 查询 tags 列中包含 3504010403 的数据
SELECT * FROM tag where  tags is not null and  array_contains(tags,'3504010403')

-- 查询 tag_code 列中包含 2 或者 3 的数据
SELECT * from  camera3 where array_contains(tag_code , '2') or    array_contains(tag_code , '3')

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 类型

索引

唯一索引

SR中创建唯一索引有两个办法：设置为主键；物化视图

函数

字符串函数

  • 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);

Array函数

  • array_contains(data_array, ‘666’) 字段是否包含666
  • array_append([1, 2], 3) 给数组追加元素，返回新数组
  • select array_concat([57.73,null,324.2], [3], [‘3’]) as res 将多个数组拼接起来，返回新数组，拼接的元素必须为数组
  • SELECT ARRAY_JOIN ([“351810”,“3504010403”,NULL] , ‘,’,‘NULL’); 将array转字符串，使用逗号分隔，若数组元素为NULL，使用字符串NULL代替
  • array_length 求数组长度
  • cardinality 计算数组中的元素个数，返回值类型是 INT。如果输入参数是 NULL，返回值也是 NULL。数组中的 NULL 元素会计入长度
  • array_remove 移除指定元素
  • reverse：反转数组元素

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;

ALTER

修改表名：


ALTER TABLE <tbl_name> RENAME <new_tbl_name>;

修改表注释：


ALTER TABLE [<db_name>.]<tbl_name> COMMENT = "<new table comment>";

DML

SHOW

展示建表语句


SHOW  create table   tableName

时区

  • 查看时区 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"

特别说明

  • 不支持存储过程
  • 不支持自增列
  • 只支持utf8编码，对gbk不支持
  • 不能修改列名和列注释

引用

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

https://forum.mirrorship.cn/t/topic/31

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
DBA 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入