Doris创建表、创建索引、设置聚合列SQL示例

原创

a772304419 2024-09-29 11:06:06 ©著作权

文章标签 sql 数据库 doris 数据仓库 ci 文章分类 Html/CSS 前端开发

©著作权归作者所有：来自51CTO博客作者a772304419的原创作品，请联系作者获取转载授权，否则将追究法律责任

创建表

假设我们要创建一个名为 orders 的表，并且我们想要定义一些列、主键、唯一键和索引。

CREATE TABLE orders (
  order_id INT NOT NULL,
  customer_id INT NOT NULL,
  order_date DATE,
  amount DECIMAL(10, 2),
  status VARCHAR(50)
)
UNIQUE KEY(`order_id`) -- 定义唯一键
DISTRIBUTED BY HASH(`order_id`) BUCKETS 10 -- 分布式散列，桶数为10
PROPERTIES (
  "replication_num" = "3"  -- 设置副本数量
);

创建索引

CREATE INDEX idx_customer_id ON orders (customer_id) USING BITMAP COMMENT 'Index for customer_id';

在 Apache Doris 中，如果不指定索引类型（即不使用 USING BITMAP），默认创建的索引类型是前缀索引（Prefix Index），也称为短键索引（Short Key Index）。这种索引是 Doris 自动维护的内建智能索引，主要用于加速等值查询和范围查询。

前缀索引的工作原理是在数据按照排序列排序存储的基础上，每隔一定行数（默认是 1024 行）创建一个稀疏的前缀索引。这个索引项包含了排序列的值，用于快速定位到数据存储的位置。前缀索引的键是基于表中定义的排序列（即建表时指定的 KEY 列）的前缀构建的，通常是排序列的前几个字节。

例如，如果你有一个表 orders，并且建表时指定了 order_id 为排序列，那么 Doris 会为 order_id 列自动创建前缀索引。这个索引会帮助加速对 order_id 的等值查询和范围查询。

在某些情况下，Doris 也支持其他类型的索引，如 ZoneMap 索引、BloomFilter 索引等，但这些通常是针对特定的查询优化场景，并且可能需要用户根据查询需求手动创建。

总结来说，如果不指定索引类型，Doris 默认会创建前缀索引，这种索引适用于加速等值查询和范围查询，特别是当查询条件与表的排序列相关时。

聚合列SQL示例

-- 这是一个用户消费和行为记录的数据表
CREATE TABLE IF NOT EXISTS test.ex_user
(
 `user_id` LARGEINT NOT NULL COMMENT "用户 id",
 `date` DATE NOT NULL COMMENT "数据灌入日期时间",
 `city` VARCHAR(20) COMMENT "用户所在城市",
 `age` SMALLINT COMMENT "用户年龄",
 `sex` TINYINT COMMENT "用户性别",
 -- 聚合列
 `last_visit_date` DATETIME REPLACE  DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
 `cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
 `max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
 `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间" 
 )
ENGINE=olap
AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 1;

这段 SQL 语句是用于在 Apache Doris 中创建一个名为 ex_user 的表，该表存储用户的消费和行为记录。下面是对这个语句的详细解释：

表和列定义：

test.ex_user：指定了数据库名 test 和表名 ex_user。
user_id LARGEINT NOT NULL COMMENT "用户 id"：定义了一个名为 user_id 的列，数据类型为 LARGEINT（大整数），不允许为空（NOT NULL），并提供了列的注释。
date DATE NOT NULL COMMENT "数据灌入日期时间"：定义了一个名为 date 的列，数据类型为 DATE，同样不允许为空，用于存储数据灌入的日期。
city VARCHAR(20) COMMENT "用户所在城市"：定义了一个名为 city 的列，数据类型为 VARCHAR(20)，存储用户所在城市。
age SMALLINT COMMENT "用户年龄"：定义了一个名为 age 的列，数据类型为 SMALLINT，存储用户年龄。
sex TINYINT COMMENT "用户性别"：定义了一个名为 sex 的列，数据类型为 TINYINT，存储用户性别。

替换和聚合列：

last_visit_date DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间"：定义了一个名为 last_visit_date 的替换列，如果新行的该列值与现有行相同，则替换现有行。默认值为 1970-01-01 00:00:00。
cost BIGINT SUM DEFAULT "0" COMMENT "用户总消费"：定义了一个名为 cost 的聚合列，使用 SUM 函数来聚合值，默认值为 0。
max_dwell_time INT MAX DEFAULT "0" COMMENT "用户最大停留时间"：定义了一个名为 max_dwell_time 的聚合列，使用 MAX 函数来找出最大值，默认值为 0。
min_dwell_time INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"：定义了一个名为 min_dwell_time 的聚合列，使用 MIN 函数来找出最小值，默认值为 99999。

表引擎和聚合键：

ENGINE=olap：指定使用 OLAP 存储引擎，这是 Doris 的主要存储引擎，适用于分析型查询。
AGGREGATE KEY(user_id, date, city, age, sex)：定义了一个聚合键，包含多个列。这意味着表中的数据将根据这些列的组合进行聚合。在插入新行时，如果这些列的组合与现有行相同，则会根据定义的聚合函数（如 SUM、MAX、MIN）更新行。