StarRocks_starrocks修改字段长度

目录

StarRocks

基本概念

系统架构

功能

排序键

物化视图

数据导入

使用

库表创建

数据类型

注意事项

新增分区

修改字段类型

问题

问题1：

问题2：

问题3：

问题4：

问题5：

ClickHouse与StarRocks的对比

---

StarRocks

StarRocks是新一代企业级MPP数据库。StarRocks重新定义了MPP分布式架构，集群可扩展至数百节点，支持PB

级数据规模，是当前唯一可以在大数据规模下进行在线弹性扩展的企业级分析型数据库。StarRocks还打造了全新

的向量化执行引擎，单节点每秒可处理多达100亿行数据。

• StarRocks致力于满足企业用户的多种数据分析场景，支持多种数据模型(明细表， 聚合表)， 多种导入方式

  （批量）， 可整合和接入多种现有系统(Spark， Flink， Hive， ElasticSearch)。

• StarRocks兼容MySQL协议， 可使用MySQL客户端和常用BI工具对接StarRocks来进行数据分析。

• StarRocks采用分布式架构， 对table进行水平划分并以多副本存储. 集群规模可以灵活伸缩， 能够支持10PB

  级别的数据分析; 支持MPP， 并行加速计算; 支持多副本， 具有弹性容错能力。

• StarRocks采用关系模型， 使用严格的数据类型， 使用列式存储引擎， 通过编码和压缩技术， 降低读写放大.

  使用向量化执行方式， 充分挖掘多核CPU的并行计算能力， 从而显著提升查询性能。

• StarRocks根据摄入数据和实际存储数据之间的映射关系, 其中明细表对应明细模型（Duplicate Key），聚合

  表对应聚合模型（Aggregate Key），更新表对应更新模型（Unique Key）和主键模型（Primary Key）。

为了描述方便, 我们借鉴关系模式中的主键概念, 称StarRocks表的维度列的取值构成数据表的排序键, StarRocks的

排序键对比传统的主键具有:

• 数据表所有维度列构成排序键, 所以后文中提及的排序列, key列本质上都是维度列。

• 排序键可重复, 不必满足唯一性约束。

• 数据表的每一列, 以排序键的顺序, 聚簇存储。

• 排序键使用稀疏索引。

基本概念

FE：FrontEnd StarRocks的前端节点，负责管理元数据，管理客户端连接，进行查询规划，查询调度等工作。

BE：BackEnd StarRocks的后端节点，负责数据存储，计算执行，以及compaction，副本管理等工作。

Broker：StarRocks中和外部HDFS/对象存储等外部数据对接的中转服务，辅助提供导入导出功能。

Tablet：StarRocks 表的逻辑分片，也是StarRocks中副本管理的基本单位，每个表根据分区和分桶机制被划分

成多个Tablet存储在不同BE节点上。

系统架构

StarRocks 集群由FE和BE构成, 可以使用MySQL客户端访问StarRocks集群。

查询流程如下：

① MySQL客户端执行DQL SQL命令。

② FE解析, 分析, 改写, 优化和规划, 生成分布式执行计划。

③ 分布式执行计划由 若干个可在单台be上执行的plan fragment构成， FE执行exec_plan_fragment, 将

plan fragment分发给BE，指定其中一台BE为coordinator。

④ BE执行本地计算, 比如扫描数据。

⑤ 其他BE调用transimit_data将中间结果发送给BE coordinator节点汇总为最终结果。

⑥ FE调用fetch_data获取最终结果。

⑦ FE将最终结果发送给MySQL client。

功能

排序键

StarRocks中为加速查询，在内部组织并存储数据时，会把表中数据按照指定的列进行排序，这部分用于排序的列

（可以是一个或多个列），可以称之为Sort Key。明细模型中Sort Key就是指定的用于排序的列（即 DUPLICATE

KEY 指定的列），聚合模型中Sort Key列就是用于聚合的列（即 AGGREGATE KEY 指定的列），更新模型中Sort

Key就是指定的满足唯一性约束的列（即 UNIQUE KEY 指定的列）。下图中的建表语句中Sort Key都为 (site_id、

city_code)。

如何选择排序列：

当Sort Key涉及多个列的时候，谁先谁后也有讲究，区分度高、经常查询的列建议放在前面。

有两点需要注意：

• 排序列的定义必须出现在建表语句中其他列的定义之前。

• 排序列的顺序是由create table语句中的列顺序决定的。

物化视图

物化视图是一种预先计算的技术，同RollUp表，预先计算是为了减少查询时现场计算量，从而降低查询延迟。明

细表包含所有原始的导入数据，因此可对明细表进行任意维度分析，而物化视图正是为了解决明细表的任意维度分

析的预先聚合而提出，同时物化视图的功能是RollUp表的超集，原有的RollUp功能都可通过物化视图来实现。

物化视图的使用场景有：

• 分析需求覆盖明细数据查询以及固定维度聚合查询两方面。

• 需要做对排序键前缀之外的其他列组合形式做范围条件过滤。

• 需要对明细表的任意维度做粗粒度聚合分析。

注：

1. 如果表存在delete 操作，不建议创建物化视图（可能会导致删除数据失败）；

2. 仅明细模型支持聚合函数物化视图；

3. 物化视图的聚合函数的参数仅支持单列, 比如： sum(a+b)不支持。

4. 如果删除语句的条件列，在物化视图中不存在，则不能进行删除操作。如果一定要删除数据，则需要先将物化视图删除，然后方可删除数据。

5. 单表上过多的物化视图会影响导入的效率：导入数据时，物化视图和 base 表数据是同步更新的，如果一张表的物化视图表超过10张，则有可能导致导入速度很慢。这就像单次导入需要同时导入10张表数据是一样的。

6. 相同列，不同聚合函数，不能同时出现在一张物化视图中，比如：select sum(a), min(a) from table 不支持。 物化视图的创建语句目前不支持JOIN和WHERE, 也不支持GROUP BY的HAVING子句。

7. 不能同时创建多个物化视图, 只能等待上一个物化视图创建完成, 才能创建下一个物化视图。

数据导入

StarRocks提供了多种导入方式，用户可以根据数据量大小、导入频率等要求选择最适合自己业务需求的导入方式。

为适配不同的数据导入需求，StarRocks 系统提供了5种不同的导入方式，以支持不同的数据源（如HDFS、Kafka、本地文件等），或者按不同的方式（异步或同步）导入数据。

所有导入方式都支持 CSV 数据格式。其中 Broker Load 持 Parquet 和 ORC 数据格式。

1.Broker Load

Broker Load 通过 Broker 进程访问并读取外部数据源，然后采用 MySQL 协议向 StarRocks 创建导入作业。

提交的作业将异步执行，用户可通过 SHOW LOAD 命令查看导入结果。

Broker Load适用于源数据在Broker进程可访问的存储系统（如HDFS）中，数据量为几十GB到上百GB。

hdfs servicenames = HDFS43394

2.Spark Load

Spark Load 通过外部的 Spark 资源实现对导入数据的预处理，提高 StarRocks 大数据量的导入性能并且节省

StarRocks 集群的计算资源。Spark load 是一种异步导入方式，需要通过 MySQL 协议创建导入作业，并通过

SHOW LOAD 查看导入结果。

Spark Load适用于初次迁移大数据量（可到TB级别）到StarRocks的场景，且源数据在Spark可访问的存储系

统（如HDFS）中。

3.Stream Load

Stream Load是一种同步执行的导入方式。用户通过 HTTP 协议发送请求将本地文件或数据流导入到

StarRocks中，并等待系统返回导入的结果状态，从而判断导入是否成功。

Stream Load适用于导入本地文件，或通过程序导入数据流中的数据。

例： curl --location-trusted -u root:root -H "Expect:100-continue" -H "column_separator:," -T

/data/clickhouse-test/ads_guider_saas_share_rank_d/ads_guider_saas_share_rank_d_af.1 -XPUT http://

10.12.114.170:8030/api/db_sente_test/ads_guider_saas_share_rank_d/_stream_load

4.Routine Load

Routine Load（例行导入）提供了一种自动从指定数据源进行数据导入的功能。用户通过 MySQL 协议提交

例行导入作业，生成一个常驻线程，不间断的从数据源（如 Kafka）中读取数据并导入到 StarRocks 中。

5.Insert Into

类似 MySQL 中的 Insert 语句，StarRocks 提供 INSERT INTO tbl SELECT ...; 的方式从 StarRocks 的表中读取

数据并导入到另一张表。或者通过 INSERT INTO tbl VALUES(...); 插入单条数据。

使用

库表创建

一、 建库

建库语法：

CREATE DATABASE IF NOT EXISTS 库名;

例：

CREATE DATABASE IF NOT EXISTS mp_aries;

查看创建结果：

SHOW DATABASES;

二、 建表

1. 表引擎

   StarRocks支持4种表引擎，可以根据实际情况选择合适的表引擎

1. 表模型

   StarRocks支持3种数据模型，可以根据实际情况选择合适的模型

1. 创建表

   建表语法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
(column_definition1[, column_definition2, ...]
[, index_definition1[, ndex_definition12,]]) -- 列定义，基本类似mysql
[ENGINE = [olap|mysql|broker|hive]] -- engine一般就选默认的olap，可以省略这个语句
[key_desc] -- 设置数据模型以及主键字段
[COMMENT "table comment"] -- 表备注
[partition_desc] -- 分区设置，不是分区表可以不用写
[distribution_desc] -- 分布设置（哈希，分桶）
[rollup_index] -- 上卷设置（不使用可以忽略）
[PROPERTIES ("key"="value", ...)] -- 附加参数配置（副本数、存储介质、动态分区，
如果不是分区表可以用默认的不用设置）
[BROKER PROPERTIES ("key"="value", ...)]; -- broker附加参数（针对broker引擎，一般用不到）       

注意：

• 建表时，Key列不能用Float或者Double类型，可用Decimal类型表示小数。

• VARCHAR最长可为65533字节（由于前两个字节用于表示长度，因此比65535少两个字节）。

• StarRocks只支持UTF8编码，不支持GBK等编码。

• StarRocks不支持修改表中的列名。

• SQL 最大长度默认10000字节，可以通过fe.conf的 expr_child_limit 修改

数据类型

注意事项

1. Starrocks建表时字段默认都是可空的（NULL)，如果需要设置为非空，建表语句加上NOT NULL修饰 （这点跟官方文档不一样，待确认）

   建议在sql中显示指定，非空的就设置NOT NULL, 可空的就设置NULL

2. Starrocks没有0-1布尔类型，用tinyint取代，只存储0和1两种值即可

   为了使用方便、节省存储空间、查询性能等方面考虑，强烈建议库表设计时按照如下原则

   1）所有的字段都需要根据业务意义考虑好所使用的字段类型，并且在各个表中保持一致；

   例如：order_num，订单数量，设置为bigint了，那么就在所有涉及的表中，把表达订单数量这个含义的字段，都使用bigint

   再如：user_age，用户年龄，设计为tinyint了，那么就都是tinyint，不要这里设计成bigint，那里是tinyint

   2）在1）的前提下，考虑字段类型设计时，尽量设计成最小范围/最小性能消耗的字段

   例如：

   a) 尽量考虑字段设置为非空，允许为空会占用额外存储空间影响性能。主键必须设置非空，非主键在不影响业务需要前提下尽可能做到非空设置默认值

   b) 如果是时间日期类型，尽量用Date/DateTime，避免用字符串

   c) 如果能保证固定是数字，就不要用字符串

   d) 如果是整数，就不要用Float/Double/Decimal

   e) 对于金额之类场景，精度要求高的，可以用decimal； 普通的浮点数场景，可以使用Float/Double

   f) 如果能用范围小的整数，就不要用范围大的类型，比如age 导购年龄，我们用户不会超过100，那么tinyint (-128 - 127）就是一个合理的选择

   g) 对于固定的字符串类型，比如是、否，可以存储char(1);

新增分区

-- 首先, 关闭动态分区功能
alter table mp_sente.ads_cmn_saas_member_d set ("dynamic_partition.enable" = "false");
-- 然后，将历史的分区补上
-- 第一个日期写需要同步的历史数据最早日期
-- 第二个日期写当前表最早的那个分区
alter table mp_sente.ads_cmn_saas_member_d add partitions start ("2019-04-01") end ("2019-12-20") every (interval 1 day); -- 补上从2019-06-01(含)到2019-12-20(不含)的分区
-- 最后，恢复动态分区功能
alter table mp_sente.ads_cmn_saas_member_d set ("dynamic_partition.enable" = "true");

修改字段类型

期望是将bigint 修改为 decimal

1. 在sr支持的情况下是可以直接通过下面的修改命令操作，鉴于sr暂时不支持下面的修改操作，故选择了重新建表

2. 尝试替换原子表，发现还是用不了，替换命令需要保持两个原子表完全一样

3. 最终操作

   --第一步，创建新新结构表
   CREATE TABLE mp_sente.ads_gdr_saas_guide_olap_dwm_bak LIKE mp_sent.ads_gdr_saas_guide_olap_dwm;
   -- 第二步老表移动数据到新表
   INSERT INTO mp_sente.ads_gdr_saas_guide_olap_dwm_bak
   SELECT * FROM mp_sente.ads_gdr_saas_guide_olap_dwm;
   -- 第三步核对数据量是否一致
   SELECT 'new' t,count(1) sl        FROM mp_sente.ads_gdr_saas_guide_olap_dwm_bak
   UNION ALL
   SELECT 'old' t,count(1) sl        FROM mp_sente.ads_gdr_saas_guide_olap_dwm;
   --第四步修改表名称
   ALTER TABLE ads_gdr_saas_guide_olap_dwm RENAME ads_gdr_saas_guide_olap_dwm_1;
   ALTER TABLE ads_gdr_saas_guide_olap_dwm_bak RENAME ads_gdr_saas_guide_olap_dwm;
   --第五步删除老表
   DROP TABLE ads_gdr_saas_guide_olap_dwm_1;
   

问题

问题1：

解决方案，强制下载驱动文件

问题2：

可以直接删除表，重建，不用必须删除文件

问题3：

output_sr 两个文件需要先执行tmp文件，然后再执行这个

问题4：

数据质量问题，sr的建表默认设置的非空

解决办法，把hive的设置默认值，或者把sr的建表语句中的非空去掉

问题5：

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1064] [42000]:

The query contains multi count distinct or sum distinct, each can't have multi columns.

不能多字段去重

select
    count(DISTINCT user_id) as perCount,
    count(DISTINCT user_id , round_id) as shakeCount
from
    wall_t_shake_record
优化后
 
SELECT
    wall_id,
    count(DISTINCT user_id),
    count(DISTINCT concat(user_id,'_', round_id))
FROM
    wall_t_shake_record
GROUP BY
    wall_id ;

ClickHouse与StarRocks的对比