StarRocks表设计_shemplle

网络投稿 02-07 4049

我们先来看一段代码

create table if not exists test ( 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 of ", channel INT COMMENT "" ) engine=olap DUPLICATE KEY(event_time ,event_type) PARTITION BY RANGE(event_time) ( PARTITION p1 VALUES LESS THAN ("2021-01-01") ) distributed by hash(k2) buckets 32 properties ( "storage_medium" = "SSD", "replication_num" = "3" ) 建表基本语句与数据类型 create table if not exists test ( 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 of ", channel INT COMMENT "" ) -- 这部分基本与mysql一致

col_type：列类型具体的列类型以及范围等信息如下：

TINYINT（1字节）范围：-2^7 + 1 ~ 2^7 - 1SMALLINT（2字节）范围：-2^15 + 1 ~ 2^15 - 1INT（4字节）范围：-2^31 + 1 ~ 2^31 - 1BIGINT（8字节）范围：-2^63 + 1 ~ 2^63 - 1LARGEINT（16字节）范围：-2^127 + 1 ~ 2^127 - 1FLOAT（4字节）支持科学计数法DOUBLE（12字节）支持科学计数法DECIMAL[(precision, scale)] (16字节) 保证精度的小数类型。默认是 DECIMAL(10, 0) precision: 1 ~ 38 scale: 0 ~ precision 其中整数部分为：precision - scale 不支持科学计数法DATE（3字节）范围：0000-01-01 ~ 9999-12-31DATETIME（8字节）范围：0000-01-01 00:00:00 ~ 9999-12-31 23:59:59CHAR[(length)] 定长字符串。长度范围：1 ~ 255。默认为1VARCHAR[(length)] 变长字符串。长度范围：1 ~ 65533HLL (1~16385个字节) hll列类型，不需要指定长度和默认值，长度根据数据的聚合程度系统内控制，并且HLL列只能通过配套的hll_union_agg、> Hll_cardinality、hll_hash进行查询或使用BITMAP bitmap列类型，不需要指定长度和默认值。表示整型的集合，元素最大支持到2^64 - 1 agg_type：聚合类型，如果不指定，则该列为 key 列。否则，该列为 value 列支持的聚合类型如下：SUM、MAX、MIN、REPLACEHLL_UNION(仅用于HLL列，为HLL独有的聚合方式)、BITMAP_UNION(仅用于 BITMAP 列，为 BITMAP 独有的聚合方式)、REPLACE_IF_NOT_NULL：这个聚合类型的含义是当且仅>当新导入数据是非NULL值时会发生替换行为，如果新导入的数据是NULL，那么StarRocks仍然会保留原值。注意：如果用在建表时REPLACE_IF_NOT_NULL列指定了NOT NULL，那么StarRocks仍然会将其转化NULL，不会向用户报错。用户可以借助这个类型完成「部分列导入」的功能。该类型只对聚合模型(key_desc的type为AGGREGATE KEY)有用，其它模型不能指这个。引擎 engine=olap

默认为 olap。可选 mysql, elasticsearch, hive 如果是 mysql，则需要在 properties 提供以下信息： properties ( “host” = “mysql_server_host”, “port” = “mysql_server_port”, “user” = “your_user_name”, “password” = “your_password”, “database” = “database_name”, “table” = “table_name” ) 在 StarRocks 创建 mysql 表的目的是可以通过 StarRocks 访问 mysql 数据库。而 StarRocks 本身并不维护、存储任何 mysql 数据。如果是 elasticsearch，则需要在 properties 提供以下信息： properties ( “hosts” = “http://192.168.0.1:8200,http://192.168.0.2:8200”, “user” = “root”, “password” = “root”, “index” = “tindex”, “type” = “doc” ) 其中host为ES集群连接地址，可指定一个或者多个,user/password为开启basic认证的ES集群的用户名/密码，index是StarRocks中的表对应的ES的index名字，可以是alias，type指定index的type，默认是doc。如果是 hive，则需要在 properties 提供以下信息： properties ( “database” = “hive_db_name”, “table” = “hive_table_name”, “hive.metastore.uris” = “thrift://127.0.0.1:9083” ) 其中 database 是 hive 表对应的库名字，table 是 hive 表的名字，hive.metastore.uris 是 hive metastore 服务地址。

数据模型 DUPLICATE KEY(event_time ,event_type) 1. Duplicate Key (默认) 特点如下 1. 需要保留原始数据 2. 查询方式灵活, 不局限于预先定义的分析方式, 传统的预聚合方式难以命中 3. 数据更新不频繁原理 ? 用户可以指定数据表的排序列, 没有明确指定的情况下, 那么StarRocks会为表选择默认的几个列作为排序列。这样，在查询中，有相关排序列的过滤条件时，StarRocks能够快速地过滤数据，降低整个查询的时延。注意：在向StarRocks明细模型表中导入完全相同的两行数据时，StarRocks会认为是两行数据。 2. Aggregate Key 特点 ? 在数据分析领域，有很多需要对数据进行统计和汇总操作的场景，就需要使用聚合模型适合采用聚合模型来分析的场景具有如下特点： 1. 业务方进行的查询为汇总类查询，比如sum、count、max等类型的查询； 2. 不需要召回原始的明细数据； 3. 老数据不会被频繁更新，只会追加新数据。注意事项 1. 聚合表中数据会分批次多次导入, 每次导入会形成一个版本. 相同排序键的数据行聚合有三种触发方式: 1. 数据导入时, 数据落盘前的聚合; 2. 数据落盘后, 后台的多版本异步聚合; 3. 数据查询时, 多版本多路归并聚合。 2. 数据查询时, 指标列采用先聚合后过滤的方式, 把没必有做指标的列存储为维度列。 3. UNIQUE KEY 1. 特点在有些场景下，数据会更新，StarRocks会采用更新模型来满足这种需求：1.已经写入的数据有大量的更新需求，2.需要进行实时数据分析。 2. 原理更新模型中, 排序键满足唯一性约束, 成为主键。 StarRocks存储内部会给每一个批次导入数据分配一个版本号, 同一主键的数据可能有多个版本, 查询时最大 (最新)版本的数据胜出。 4. Primary Key 适用场景 ? 相较更新模型，主键模型（Primary Key）可以更好地支持实时/频繁更新的功能。该类型的表要求有唯一的主键，支持对表中的行按主键进行更新和删除操作。需要注意的是：由于存储引擎会为主键建立索引，而在导入数据时会把主键索引加载在内存中，所以主键模型对内存的要求比较高，还不适合主键特别多的场景。目前primary主键存储在内存中，为防止滥用造成内存占满，限制主键字段长度全部加起来编码后不能超过127字节。

在电商场景下，我们可能需要对订单进行明细的统计

-- 我们就可以把表设计成为这样 create table orderDetail ( id varchar() NOT NULL COMMENT "订单明细id", order_id varchar() COMMENT "订单id", goods varchar COMMENT "商品名称" , num int COMMENT "数量" , price decimal(10,2) COMMENT "价格" , create_time DATETIME COMMENT "创建时间" , ... ) engine=olap duplicate key() partition by RANGE(create_time) ( partition p1 values less than ("2020-01-01") , partition p2 values less than ("2020-02-01") , ... ) distributed by hash(order_id) buckets 10 ; 分区分桶 PARTITION BY RANGE(event_time) ( PARTITION p1 VALUES LESS THAN ("2021-01-01") ) distributed by hash(k2) buckets 32

StarRocks中Range分布，被称之为分区，用于分布的列也被称之为分区列，Hash分布，则被称之为分桶，用于分布的列也被称之为分桶列。单分区建议控制在100G这个大小之内，分桶建议控制在100M-1G之间

我们还可以批量创建分区

PARTITION BY RANGE (datekey) ( START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL 1 day) ) 分区等价于： PARTITION p20210101 VALUES [('2021-01-01'), ('2021-01-02')), PARTITION p20210102 VALUES [('2021-01-02'), ('2021-01-03')), PARTITION p20210103 VALUES [('2021-01-03'), ('2021-01-04')) 还可以 START ("1") END ("5") EVERY (1) 等价于： PARTITION p1 VALUES [("1"), ("2")), PARTITION p2 VALUES [("2"), ("3")), PARTITION p3 VALUES [("3"), ("4")), PARTITION p4 VALUES [("4"), ("5")) 也可以同时使用不同时间类型 PARTITION BY RANGE (datekey) ( START ("2019-01-01") END ("2021-01-01") EVERY (INTERVAL 1 YEAR), START ("2021-01-01") END ("2021-05-01") EVERY (INTERVAL 1 MONTH), START ("2021-05-01") END ("2021-05-04") EVERY (INTERVAL 1 DAY) ) 等价于： PARTITION p2019 VALUES [('2019-01-01'), ('2020-01-01')), PARTITION p2020 VALUES [('2020-01-01'), ('2021-01-01')), PARTITION p202101 VALUES [('2021-01-01'), ('2021-02-01')), PARTITION p202102 VALUES [('2021-02-01'), ('2021-03-01')), PARTITION p202103 VALUES [('2021-03-01'), ('2021-04-01')), PARTITION p202104 VALUES [('2021-04-01'), ('2021-05-01')), PARTITION p20210501 VALUES [('2021-05-01'), ('2021-05-02')), PARTITION p20210502 VALUES [('2021-05-02'), ('2021-05-03')), PARTITION p20210503 VALUES [('2021-05-03'), ('2021-05-04')) properties properties ( "storage_medium" = "SSD", -- 存储介质 "replication_num" = "3" -- 设置副本数量 )

properties里面我们可以设置非常多的东西。例如动态分区：

CREATE TABLE site_access( event_day DATE, site_id INT DEFAULT '10', city_code VARCHAR(100), user_name VARCHAR(32) DEFAULT '', pv BIGINT DEFAULT '0' ) DUPLICATE KEY(event_day, site_id, city_code, user_name) PARTITION BY RANGE(event_day)( PARTITION p20200321 VALUES LESS THAN ("2020-03-22"), PARTITION p20200322 VALUES LESS THAN ("2020-03-23"), PARTITION p20200323 VALUES LESS THAN ("2020-03-24"), PARTITION p20200324 VALUES LESS THAN ("2020-03-25") ) DISTRIBUTED BY HASH(event_day, site_id) BUCKETS 32 PROPERTIES( "dynamic_partition.enable" = "true", -- 是否打开动态分区 "dynamic_partition.time_unit" = "DAY", -- 动态分区的粒度 -- 1. 指定为DAY时分区后缀名称需要满足为yyMMdd的格式 -- 例如：PARTITION p20200321 VALUES LESS THAN ("2020-03-22") -- 2. 指定为 WEEK 时，分区名后缀需为yyyy_ww，例如2020_13代表2020年第13周。 -- 3. 指定为 MONTH 时，动态创建的分区名后缀格式为 yyyyMM，例如 202003。 "dynamic_partition.start" = "-3", -- 动态分区的开始时间 "dynamic_partition.end" = "3", -- 动态分区的结束时间 "dynamic_partition.prefix" = "p", -- 动态分区名称的前缀 "dynamic_partition.buckets" = "32" -- 动态分区的内部的分桶数量 ); -- 这是官网上的一个例子我们可以使用,来查看分区情况 show partitions from table_name ; 修改分区情况 alter table table_name set ("dynamic_partition.enable"="false") ; alter table table_name set ("dynamic_partition.enable"="true");