StarRocks2.3 MPP数据库_表、导入和查询数据、表设计

Lemonade22
994 阅读7分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第30天，点击查看活动详情

1 建表

如何在 StarRocks 中创建表以及进行相关操作。

1.1 连接 StarRocks

前面，我们成功 部署 StarRocks 集群 。

现在，可以通过 MySQL 客户端连接任意一个 FE 节点的 query_port（默认为 9030）以连接 StarRocks。StarRocks 内置 root 用户，密码默认为空。

image.png

1.2 创建数据库

CREATE DATABASE example_db;

image.png

+--------------------+
| Database           |
+--------------------+
| _statistics_       |
| example_db         |
| information_schema |
+--------------------+
3 rows in set (0.00 sec)

1.3 创建表

在新建的数据库中创建表。

  • 在 StarRocks 中，字段名不区分大小写，表名区分大小写。
  • 建表时，DISTRIBUTED BY 为必填字段。

StarRocks 支持 多种数据模型，以适用不同的应用场景。

use example_db;
CREATE TABLE IF NOT EXISTS detailDemo (
    recruit_date  DATE           NOT NULL COMMENT "YYYY-MM-DD",
    region_num    TINYINT        COMMENT "range [-128, 127]",
    num_plate     SMALLINT       COMMENT "range [-32768, 32767] ",
    tel           INT            COMMENT "range [-2147483648, 2147483647]",
    id            BIGINT         COMMENT "range [-2^63 + 1 ~ 2^63 - 1]",
    password      LARGEINT       COMMENT "range [-2^127 + 1 ~ 2^127 - 1]",
    name          CHAR(20)       NOT NULL COMMENT "range char(m),m in (1-255) ",
    profile       VARCHAR(500)   NOT NULL COMMENT "upper limit value 65533 bytes",
    hobby         STRING         NOT NULL COMMENT "upper limit value 65533 bytes",
    leave_time    DATETIME       COMMENT "YYYY-MM-DD HH:MM:SS",
    channel       FLOAT          COMMENT "4 bytes",
    income        DOUBLE         COMMENT "8 bytes",
    account       DECIMAL(12,4)  COMMENT "",
    ispass        BOOLEAN        COMMENT "true/false"
) ENGINE=OLAP
DUPLICATE KEY(recruit_date, region_num)
DISTRIBUTED BY HASH(recruit_date, region_num) BUCKETS 8;

建表语句说明

（1）排序键

由 DUPLICATE KEY 指定排序列。

存储数据时会按照指定列排序，这些列为排序列（Sort Key）。

（2）字段类型

字段类型介绍详见 数据类型章节。

image.png

（3）分区分桶

PARTITION 关键字用于给表 创建分区。以上示例中使用 recruit_date 进行范围分区，从 11 日到 15 日每天创建一个分区。

为了优化生产环境的查询性能，强烈建议您为表制定合理的数据分区计划。

DISTRIBUTED 关键字用于给表 创建分桶，以上示例中使用 recruit_date 以及 region_num 两个字段通过 Hash 算法创建 8 个桶。

创建表时合理的分区和分桶设计可以优化表的查询性能。

有关分区分桶列如何选择，详见 数据分布。

（4）数据模型

DUPLICATE 关键字表示当前表为明细模型，KEY 中的列表示当前表的排序列。

StarRocks 支持多种数据模型，分别为 明细模型，聚合模型，更新模型，主键模型。

不同模型的适用于多种业务场景，合理选择可优化查询效率。

（5）索引

StarRocks 默认会给 Key 列创建稀疏索引加速查询，具体规则见 排序键。

支持的索引类型有 Bitmap 索引，Bloomfilter 索引 等。

（6）ENGINE 类型

默认 ENGINE 类型为 OLAP，对应 StarRocks 集群内部表。

其他可选项包括 mysql，elasticsearch，hive，以及 ICEBERG，分别代表所创建的表为相应类型的 外部表。

1.4 查看表信息

  • 查看当前数据库中所有的表
SHOW TABLES;
  • 查看表的结构
DESC table_name;
  • 查看建表语句
SHOW CREATE TABLE table_name;

1.5 修改表结构

增加列

删除列

取消修改表结构

1.6 创建用户并授权

在 StarRocks 中，只有拥有 CREATE_PRIV 权限 的用户才可建立数据库。

example_db 数据库创建完成之后，您可以使用 root 账户创建 test 账户，并授予其 example_db 的读写权限 。

CREATE USER 'test' IDENTIFIED by '123456';
GRANT ALL on example_db to test;

通过登录被授权的 test 账户，就可以操作 example_db 数据库。

mysql -h 127.0.0.1 -P9030 -utest -p123456

2 导入和查询数据

2.1 导入

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

starrocks_ecology

2.1.1 Broker Load

  • 一种异步数据导入模式

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

  • 适用场景：适用于源数据在 Broker 进程可访问的存储系统（如 HDFS，S3）中的情景，可以支撑数据量达数百 GB 的导入作业。该导入方式支持的数据源有 Apache Hive™ 等。

2.1.2 Spark Load

  • 一种异步数据导入模式
  • 通过外部的 Apache Spark™ 资源实现对导入数据的预处理，提高 StarRocks 大数据量的导入性能并且节省 StarRocks 集群的计算资源。
  • 适用场景：适用于初次向 StarRocks 迁移大数据量（TB 级别）的场景。该导入方式支持的数据源应位于 Apache Spark™ 可访问的存储系统（如 HDFS）中。

2.1.3 Stream Load

  • 一种同步数据导入模式。
  • 用户通过 HTTP 协议发送请求将本地文件或数据流导入到 StarRocks 中，并等待系统返回导入的结果状态，从而判断导入是否成功。
  • 适用场景：适用于导入本地文件，或通过程序导入数据流中的数据。

如下成功导入数据：

HTTP 地址中 IP 为 FE 节点 IP，端口为 fe.conf 中配置的 http port。

image.png

image.png

2.1.4 Routine Load

  • 提供从指定数据源进行自动数据导入的功能。

2.1.5 Insert Into

  • 一种同步数据导入模式

  • 类似 MySQL 中的 Insert 语句，StarRocks 支持通过 INSERT INTO tbl SELECT ...; 的方式从 StarRocks 的表中读取数据并导入到另一张表。

  • 该导入方式支持的数据源有 DataX/DTS、Kettle/Informatic、以及 StarRocks 本身。

2.2 查询

StarRocks 兼容 MySQL 协议，其查询语句基本符合 SQL92 标准。

StarRocks 支持多种 select 用法，包括：Join，子查询，With 子句 等，详见 查询章节。

2.3 扩展

StarRocks 拓展支持多种函数、视图、以及外部表。

2.4 慢查询分析

StarRocks 支持通过多种方式分析查询瓶颈以及优化查询效率。

查看 Profile 并分析查询瓶颈

  • 查看查询计划。
explain costs select * from detailDemo;

StarRocks 1.19 以前版本需使用 explain sql 查看查询计划。

3 表设计

3.1 数据模型

基本概念

StarRocks 支持四种数据模型，分别是明细模型 (Duplicate Key Model)、聚合模型 (Aggregate Key Model)、更新模型 (Unique Key Model) 和主键模型 (Primary Key Model)。

这四种数据模型能够支持多种数据分析场景，例如日志分析、数据汇总分析、实时分析等。

注意事项

（1）明细模型（默认）

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

  • 创建表

    • 建表时必须使用 DISTRIBUTED BY HASH 子句指定分桶键。分桶键的更多说明，请参见分桶。

  • 使用说明

    • 排序键
      • 在建表语句中，排序键必须定义在其他列之前。
      • 明细模型中的排序键可以为部分或全部维度列。

（2）聚合模型

  • 适用场景：适用于分析统计和汇总数据。
    • 通过分析网站或 APP 的访问流量，统计用户的访问总时长、访问总次数。
    • 广告厂商为广告主提供的广告点击总量、展示总量、消费统计等。
    • 通过分析电商的全年交易数据，获得指定季度或者月份中，各类消费人群的爆款商品。
  • 举例

例如，导入如下数据至聚合模型中：

DateCountryPV
2020.05.01CHN1
2020.05.01CHN2
2020.05.01USA3
2020.05.01USA4

在聚合模型中，以上四条数据会聚合为两条数据。这样在后续查询处理的时候，处理的数据量就会显著降低。

DateCountryPV
2020.05.01CHN3
2020.05.01USA7

（3）更新模型

  • 概述：相对于明细模型，更新模型简化了数据导入流程，能够更好地支撑实时和频繁更新的场景。

  • 适用场景：实时和频繁更新的业务场景，例如分析电商订单。在电商场景中，订单的状态经常会发生变化，每天的订单更新量可突破上亿。

（4）主键模型

  • 适用场景

    • 适用于实时和频繁更新的场景。
    • 相对于其他模型，主键模型对内存的要求比较高。目前主键模型中，主键编码后，占用内存空间上限为 127 字节。
    • 如下两个场景中，主键占用空间相对可控：
      • （1）数据有冷热特征，即最近几天的热数据才经常被修改，老的冷数据很少被修改。例如，MySQL订单表实时同步到 StarRocks 中提供分析查询。
      • （2）大宽表（数百到数千列）。主键只占整个数据的很小一部分，其内存开销比较低。比如用户状态和画像表，虽然列非常多，但总的用户数不大（千万至亿级别），主键索引内存占用相对可控。

  • 明细模型中的排序键可以为部分或全部维度列。

avatar
文章
阅读
粉丝