大家好我是专注于大数据技术栈的博主。在数据仓库选型与实践中Apache Doris 以其极速的实时分析能力脱颖而出而合理的数据表设计是发挥其性能优势的基石。很多开发者在初次接触 Doris 时面对其丰富的表模型和建表语法感到无从下手。本文将系统性地拆解 Doris 数据表的创建过程从核心概念到实战建表再到生产环境的最佳实践手把手带你掌握 Doris 表设计的精髓。无论你是正在评估 Doris 的新手还是需要优化现有表结构的开发者都能从本文中找到清晰的路径和可复用的代码。1. Doris 数据表核心概念与模型选择在动手创建表之前理解 Doris 提供的几种表模型及其适用场景至关重要。选错模型可能会在后续的查询性能、数据更新和存储成本上埋下隐患。1.1 三大表模型详解Doris 主要支持三种数据模型Duplicate明细模型、Aggregate聚合模型和 Unique唯一模型。它们并非三种独立的表类型而是通过在建表语句中指定不同的AGGREGATE KEY、UNIQUE KEY或DUPLICATE KEY来定义的数据组织方式。1. Duplicate 明细模型这是最基础、最灵活的模型。它不做任何预聚合完全保留导入数据的原始细节。即使两行数据的所有列值都相同也会被视为两行独立数据而存储。适用场景需要存储原始明细数据的日志分析、行为流水、事务记录等。适合进行任意维度的 Ad-hoc 查询、数据回溯和详细排查。特点存储成本相对较高但灵活性最好。2. Aggregate 聚合模型该模型适用于有明确统计汇总需求的场景。它会在数据导入阶段根据建表时指定的聚合函数如 SUM、MAX、MIN、REPLACE对相同维度列的数据进行预聚合。适用场景报表类、指标看板等需要快速查询汇总结果的业务。例如电商的每日商品销量统计、广告的点击消耗汇总。特点能极大提升聚合查询效率降低存储空间但牺牲了部分明细查询能力。3. Unique 唯一模型这是聚合模型的一个特例可以视为聚合模型中聚合函数为REPLACE的情况。它保证在同一批导入数据中对于相同的唯一键Unique Key后传入的数据会替换先前的数据。适用场景需要按主键进行数据更新的场景如用户画像表一个用户一条记录信息随时间更新、设备状态表等。特点实现了类似“主键更新”的能力简化了数据更新的ETL流程。1.2 如何选择合适的数据模型选择模型可以遵循以下决策路径是否有数据更新的需求如果有且是主键更新模式优先考虑Unique 模型。是否主要进行汇总查询且维度固定如果是例如总看“每天的销售额”选择Aggregate 模型并为指标列设置 SUM 函数。是否需要保留所有原始明细进行灵活的多维度分析如果是或者业务场景不确定选择Duplicate 明细模型。是否既有明细查询需求又有部分列需要更新可以考虑使用 Duplicate 模型记录明细同时创建一张 Aggregate 或 Unique 模型的物化视图或 Rollup 表来满足特定查询或更新需求。理解这些模型是创建高效 Doris 表的第一步。接下来我们将在准备好的环境中进行实战。2. 环境准备与前置条件为了完成后续的建表示例你需要一个可用的 Doris 环境。这里以单机部署为例这是学习和测试的最佳方式。2.1 系统与软件要求操作系统CentOS 7 或 Ubuntu 16.04。本文示例基于 CentOS 7.9。JavaJDK 8必须是 Oracle JDK 8u201 或 OpenJDK 8u322。运行java -version确认。Doris 版本本文使用 Apache Doris 1.2.4 版本一个长期稳定版本。请从 Apache Doris 官网 或 GitHub Release 页面下载。客户端工具可以使用 Doris 自带的 MySQL 客户端mysql也可以使用任何兼容 MySQL 协议的图形化工具如 DBeaver、DataGrip。2.2 单机 Doris 快速部署FE BE假设你已经下载了apache-doris-1.2.4-bin-x64.tar.gz安装包。# 1. 解压安装包 tar -zxvf apache-doris-1.2.4-bin-x64.tar.gz -C /opt/ cd /opt/apache-doris-1.2.4/ # 2. 配置 Frontend (FE) cd fe vim conf/fe.conf # 添加或修改以下关键配置指定本机IP假设为192.168.1.100 priority_networks 192.168.1.0/24 # 其他配置如日志目录等可按需调整 # 3. 启动 FE ./bin/start_fe.sh --daemon # 查看日志确认启动成功 tail -f log/fe.log # 看到 thrift server started 和 http server started 字样表示成功 # 4. 使用 MySQL 客户端连接 FE初始无密码 mysql -h 127.0.0.1 -P 9030 -uroot # 在 MySQL 客户端内执行以下 SQL 初始化 root 密码并创建数据库 SET PASSWORD FOR root PASSWORD(your_password); CREATE DATABASE demo_db; USE demo_db; # 5. 配置 Backend (BE) # 新开一个终端窗口 cd /opt/apache-doris-1.2.4/be vim conf/be.conf # 添加或修改 priority_networks 192.168.1.0/24 # 设置数据存储路径确保目录存在且有权限 storage_root_path /path/to/doris_storage # 6. 启动 BE ./bin/start_be.sh --daemon tail -f log/be.log # 看到 heartbeat success 等字样表示启动成功 # 7. 在 FE 节点上添加 BE 节点 # 回到刚才的 MySQL 客户端连接着 FE ALTER SYSTEM ADD BACKEND 192.168.1.100:9050; # 查看 BE 状态确认 Alive 为 true SHOW BACKENDS\G环境就绪后我们就可以在demo_db数据库中开始创建各种类型的数据表了。3. 建表语法深度解析Doris 的建表语句CREATE TABLE兼容 MySQL 协议但包含大量扩展参数以适应其分布式、列式存储的特性。掌握核心子句是灵活建表的关键。3.1 基础建表语句结构一个完整的 Doris 建表语句包含以下主要部分CREATE TABLE [IF NOT EXISTS] [database.]table_name ( column_name column_type [KEY | AGGREGATE KEY] [NULL | NOT NULL] [DEFAULT default_value] [COMMENT column_comment], ... ) [ENGINE olap] -- Doris 默认引擎通常无需指定 [KEY(column_name, ...)] -- 指定 Duplicate 模型的排序列 [COMMENT table_comment] [DISTRIBUTED BY HASH(column_name, ...) [BUCKETS bucket_num]] -- 分桶方式 [PARTITION BY RANGE(column_name) (...)] -- 分区方式 [PROPERTIES (keyvalue, ...)]; -- 表属性3.2 核心子句详解1. 列定义与键类型列定义中的KEY关键字至关重要它定义了该列是否为“维度列”。在 Aggregate 和 Unique 模型中只有被定义为KEY的列才参与数据的去重和比较。在 Duplicate 模型中KEY列仅表示数据按照这些列排序并非唯一约束。AGGREGATE KEY用于定义聚合模型的维度列。UNIQUE KEY用于定义唯一模型的维度列即主键。2. 分区PARTITION BY RANGE分区用于将表划分为多个独立管理的部分通常是按时间范围如天、月。分区可以加速查询利用分区裁剪查询时只扫描相关分区。简化数据管理轻松删除或增加某个时间范围的数据如删除旧分区。均衡负载结合分桶进一步分散数据。3. 分桶DISTRIBUTED BY HASH分桶是 Doris 数据分布的最终单位。数据通过 Hash 算法分散到各个 Bucket 中每个 Bucket 对应一个数据分片Tablet是数据复制和迁移的最小单元。BUCKETS 指定分桶数量。数量适中通常建议在10-100之间单个 Tablet 数据量在100MB-1GB为宜。分桶列应选择高基数列如用户ID且常作为查询条件。4. 表属性PROPERTIES这是 Doris 表的高级控制中心常用属性包括replication_num 数据副本数默认3单机测试可设为1。storage_medium/storage_cooldown_time 定义数据存储介质SSD/HDD和冷却时间用于冷热数据分层。dynamic_partition 启用动态分区自动创建新分区。4. 三种数据模型建表示战现在我们将在demo_db数据库中创建三种不同模型的表并模拟数据导入让你直观感受其区别。4.1 创建 Duplicate 明细模型表假设我们要记录用户每一次的点击行为流水。USE demo_db; CREATE TABLE IF NOT EXISTS user_click_log_dup ( user_id BIGINT NOT NULL COMMENT 用户ID, item_id INT NOT NULL COMMENT 商品ID, category_id SMALLINT COMMENT 商品类目ID, click_time DATETIME NOT NULL COMMENT 点击时间, city VARCHAR(20) COMMENT 所在城市, device VARCHAR(50) COMMENT 设备型号, channel VARCHAR(20) COMMENT 流量渠道 ) ENGINE olap DUPLICATE KEY(user_id, item_id, click_time) -- 指定排序列 COMMENT 用户点击行为明细表 DISTRIBUTED BY HASH(user_id) BUCKETS 10 PROPERTIES ( replication_num 1, -- 单机部署副本数设为1 storage_format V2 );关键点解析DUPLICATE KEY 这里指定了(user_id, item_id, click_time)意味着数据在文件内会按照这个顺序排序存储有利于范围查询。但它不保证唯一性。所有列都没有定义聚合函数数据原样存储。4.2 创建 Aggregate 聚合模型表假设我们需要一张表来快速查询每个城市-渠道的每日点击总量和独立用户数。CREATE TABLE IF NOT EXISTS city_channel_daily_agg ( dt DATE NOT NULL COMMENT 数据日期, city VARCHAR(20) NOT NULL COMMENT 城市, channel VARCHAR(20) NOT NULL COMMENT 渠道, click_count BIGINT SUM DEFAULT 0 COMMENT 点击总次数, unique_user_count BIGINT BITMAP_UNION COMMENT 独立用户数(使用Bitmap计算), last_click_time DATETIME MAX COMMENT 最后点击时间 ) ENGINE olap AGGREGATE KEY(dt, city, channel) -- 维度列 COMMENT 城市-渠道每日聚合表 PARTITION BY RANGE(dt) -- 按天分区 ( PARTITION p202401 VALUES LESS THAN (2024-02-01), PARTITION p202402 VALUES LESS THAN (2024-03-01) ) DISTRIBUTED BY HASH(city, channel) BUCKETS 8 PROPERTIES ( replication_num 1, storage_medium SSD, storage_format V2 );关键点解析AGGREGATE KEY 定义了维度列(dt, city, channel)。所有查询的 GROUP BY 条件必须是这些维度列的子集。聚合函数click_count BIGINT SUM 导入时相同维度的click_count值会被累加。unique_user_count BIGINT BITMAP_UNION 这是 Doris 的高级聚合函数用于精确计算基数如UV。导入时需要传入用户ID构成的Bitmap。last_click_time DATETIME MAX 保留相同维度下最大的时间。PARTITION BY RANGE 按日期分区便于管理历史数据。4.3 创建 Unique 唯一模型表假设我们需要一张用户最新信息表一个用户只有一条最新记录。CREATE TABLE IF NOT EXISTS user_profile_unique ( user_id BIGINT NOT NULL COMMENT 用户ID, user_name VARCHAR(50) REPLACE COMMENT 用户名取最新, city VARCHAR(20) REPLACE COMMENT 城市取最新, age SMALLINT REPLACE COMMENT 年龄取最新, last_login_time DATETIME REPLACE COMMENT 最后登录时间, total_score BIGINT REPLACE_IF_NOT_NULL COMMENT 总积分非NULL值才更新 ) ENGINE olap UNIQUE KEY(user_id) -- 唯一键即主键 COMMENT 用户画像表唯一模型 DISTRIBUTED BY HASH(user_id) BUCKETS 12 PROPERTIES ( replication_num 1, enable_unique_key_merge_on_write true, -- 启用 Merge-on-Write提升点查性能 storage_format V2 );关键点解析UNIQUE KEY(user_id) 指定user_id为唯一键。所有非键列必须指定聚合函数通常是REPLACE。REPLACE 后到的数据直接替换之前的数据。REPLACE_IF_NOT_NULL 只有后到的数据非 NULL 时才替换旧值。这对于部分字段更新非常有用。enable_unique_key_merge_on_write 设置为true启用 Merge-on-Write 模式在数据导入时即完成合并对于点查优化显著但会牺牲部分导入速度。需要根据业务查询模式权衡。5. 数据导入与验证表创建好后我们通过简单的 Stream Load 方式导入一些测试数据验证表的行为。5.1 准备测试数据文件创建 CSV 格式的测试数据文件test_data.csv。-- 文件test_dup.csv (用于明细表) 1001,2001,5,2024-01-15 10:01:02,北京,iPhone13,organic 1001,2001,5,2024-01-15 10:01:05,北京,iPhone13,organic -- 完全重复的一行 1002,2003,8,2024-01-15 10:02:01,上海,Xiaomi12,adwords -- 文件test_agg.csv (用于聚合表需对应维度) 2024-01-15,北京,organic,2,1001|1002,2024-01-15 10:01:05 -- 注意Bitmap列在实际Stream Load时需要特殊处理这里仅为示意 -- 文件test_unique.csv (用于唯一表) 1001,张三,北京,25,2024-01-15 09:00:00,1500 1001,张老三,北京,26,2024-01-16 10:00:00,NULL -- 同user_id更新信息积分不变5.2 使用 Stream Load 导入数据通过 HTTP 协议向 Doris 导入数据。# 导入数据到明细表 curl --location-trusted -u root:your_password \ -H label:label_click_$(date %s) \ # Label需唯一用于保证导入幂等性 -H column_separator:, \ -T /path/to/test_dup.csv \ http://127.0.0.1:8030/api/demo_db/user_click_log_dup/_stream_load # 导入数据到唯一表 curl --location-trusted -u root:your_password \ -H label:label_profile_$(date %s) \ -H column_separator:, \ -H columns: user_id, user_name, city, age, last_login_time, total_score \ -T /path/to/test_unique.csv \ http://127.0.0.1:8030/api/demo_db/user_profile_unique/_stream_load5.3 查询验证结果连接 Doris 的 MySQL 端口进行查询。-- 1. 查询明细表会看到两行完全一样的数据 SELECT * FROM user_click_log_dup ORDER BY click_time; -- 结果1001 对应的记录会出现两行证明明细模型保留所有数据。 -- 2. 查询唯一表user_id1001 只保留最新一条 SELECT * FROM user_profile_unique WHERE user_id 1001; -- 结果姓名为“张老三”年龄为26积分为1500因为REPLACE_IF_NOT_NULL新记录的NULL未覆盖旧值。6. 进阶动态分区与索引优化基础表创建后在生产环境中还需要考虑自动化管理和查询加速。6.1 配置动态分区手动管理分区很繁琐。可以修改聚合表启用动态分区自动创建未来分区并删除旧分区。-- 首先删除旧的聚合表测试环境操作生产环境请谨慎 DROP TABLE IF EXISTS city_channel_daily_agg; -- 重新创建带动态分区属性的表 CREATE TABLE IF NOT EXISTS city_channel_daily_agg ( ... -- 列定义与之前相同 ) ... PARTITION BY RANGE(dt)() DISTRIBUTED BY HASH(city, channel) BUCKETS 8 PROPERTIES ( replication_num 1, dynamic_partition.enable true, dynamic_partition.time_unit DAY, dynamic_partition.start -7, -- 动态创建从7天前开始的分区 dynamic_partition.end 3, -- 动态创建到3天后的分区 dynamic_partition.prefix p, dynamic_partition.buckets 8, storage_medium SSD );这样系统会自动维护分区例如每天自动创建p20240301这样的分区。6.2 使用 Rollup 进行索引优化Rollup物化索引是 Doris 中一种预计算的索引结构可以显著加速特定查询。例如我们的明细表经常需要按city和category_id进行筛选。-- 为 user_click_log_dup 表创建一个 Rollup ALTER TABLE user_click_log_dup ADD ROLLUP rollup_city_category (city, category_id, user_id, click_time, device); -- 这个Rollup将数据按照 (city, category_id) 重新排序存储 -- 创建完成后Doris 优化器会自动选择是否使用该Rollup。 -- 你可以通过 EXPLAIN 命令查看查询是否命中Rollup EXPLAIN SELECT city, category_id, COUNT(*) FROM user_click_log_dup WHERE city北京 GROUP BY city, category_id;7. 常见问题与排查思路在创建和管理 Doris 表时你可能会遇到以下典型问题。问题现象常见原因解决思路建表失败报错Failed to create partition分区起始值大于结束值分区键类型与表达式不匹配。检查PARTITION BY RANGE语句确保VALUES LESS THAN的值是递增的且类型正确。数据导入失败报错ETL_QUALITY_UNSATISFIED数据格式错误如 NULL 值导入到 NOT NULL 列数值越界。1. 检查CSV文件列分隔符、换行符。2. 检查数据是否违反列约束。3. 使用-H max_filter_ratio:0.1容忍一定比例错误先观察具体错误行。查询聚合表时结果与预期汇总值不符1. 查询的GROUP BY列不是AGGREGATE KEY的子集。2. 导入数据前未理解聚合模型语义。1. 确保SELECT中的非聚合列都在GROUP BY中且属于AGGREGATE KEY。2. 重新理解聚合模型相同维度列的数据在导入时已被聚合查询时无法再获取明细。唯一表Merge-on-Write模式导入速度慢Merge-on-Write 模式需要在导入时进行数据合并比普通导入慢。对于批量历史数据导入可临时设置enable_unique_key_merge_on_write false导入完成后再改为true并执行BASE COMPACTION。SHOW BACKENDS显示 BE 状态不为 AliveBE 进程未启动网络不通端口被占用。1. 检查 BE 进程 ps aux8. 生产环境最佳实践与工程建议基于大量项目经验以下建议能帮助你设计出更稳健、高效的 Doris 表。1. 分区分桶设计原则分区键选择 首选时间日期列便于冷热数据管理和生命周期操作。分区数量不宜过多通常不超过1000个避免元数据压力。分桶键选择 选择高基数、经常作为查询条件的列。避免选择低基数列如性别、状态标志会导致数据倾斜。分桶数建议是 BE 节点数的整数倍便于数据均匀分布。分桶数估算 目标使单个 Tablet 大小在 100MB 到 1GB 之间。例如表预计原始数据 100GB副本数2则总数据量 200GB。若期望 Tablet 为 500MB则分桶数约为200GB / 0.5GB 400。再根据 BE 节点数调整。2. 字段类型与编码优化使用合适的类型 能用INT不用BIGINT能用VARCHAR不用STRING。精确的类型能节省存储和内存。应用编码 对低基数的字符串列如城市名、枚举状态使用BITMAP或DICT编码可以极大提升压缩率和查询速度。3. 数据更新策略高频小批量更新 使用 Unique 模型Merge-on-Write。虽然导入性能有损耗但查询性能最佳。低频批量更新 使用 Unique 模型非 Merge-on-Write或 Aggregate 模型通过批量导入覆盖。仅追加无更新 使用 Duplicate 或 Aggregate 模型性能最优。4. 物化视图Rollup规划不要过度创建 Rollup每个 Rollup 都是一份冗余存储。优先为最核心、最耗时的查询创建。Rollup 的排序列必须是查询条件列或分组列的前缀才能生效。5. 变更管理表结构变更如 ADD COLUMN是轻量级操作但修改列类型或顺序是重量级操作需要谨慎。任何表结构变更和 Rollup 操作建议先在测试环境验证再在业务低峰期执行。掌握 Doris 建表就掌握了其高性能查询的钥匙。从理解三大模型开始到精心设计分区分桶再到利用 Rollup 加速每一步都影响着最终的数据效能。建议你在自己的测试环境中反复练习本文的示例并尝试根据一个真实的业务场景如订单表、日志表进行表设计。只有通过实践才能深入理解不同参数对系统行为的影响从而设计出最适合自己业务的数据表结构。
Apache Doris数据表设计实战：从三大模型选择到生产环境最佳实践
大家好我是专注于大数据技术栈的博主。在数据仓库选型与实践中Apache Doris 以其极速的实时分析能力脱颖而出而合理的数据表设计是发挥其性能优势的基石。很多开发者在初次接触 Doris 时面对其丰富的表模型和建表语法感到无从下手。本文将系统性地拆解 Doris 数据表的创建过程从核心概念到实战建表再到生产环境的最佳实践手把手带你掌握 Doris 表设计的精髓。无论你是正在评估 Doris 的新手还是需要优化现有表结构的开发者都能从本文中找到清晰的路径和可复用的代码。1. Doris 数据表核心概念与模型选择在动手创建表之前理解 Doris 提供的几种表模型及其适用场景至关重要。选错模型可能会在后续的查询性能、数据更新和存储成本上埋下隐患。1.1 三大表模型详解Doris 主要支持三种数据模型Duplicate明细模型、Aggregate聚合模型和 Unique唯一模型。它们并非三种独立的表类型而是通过在建表语句中指定不同的AGGREGATE KEY、UNIQUE KEY或DUPLICATE KEY来定义的数据组织方式。1. Duplicate 明细模型这是最基础、最灵活的模型。它不做任何预聚合完全保留导入数据的原始细节。即使两行数据的所有列值都相同也会被视为两行独立数据而存储。适用场景需要存储原始明细数据的日志分析、行为流水、事务记录等。适合进行任意维度的 Ad-hoc 查询、数据回溯和详细排查。特点存储成本相对较高但灵活性最好。2. Aggregate 聚合模型该模型适用于有明确统计汇总需求的场景。它会在数据导入阶段根据建表时指定的聚合函数如 SUM、MAX、MIN、REPLACE对相同维度列的数据进行预聚合。适用场景报表类、指标看板等需要快速查询汇总结果的业务。例如电商的每日商品销量统计、广告的点击消耗汇总。特点能极大提升聚合查询效率降低存储空间但牺牲了部分明细查询能力。3. Unique 唯一模型这是聚合模型的一个特例可以视为聚合模型中聚合函数为REPLACE的情况。它保证在同一批导入数据中对于相同的唯一键Unique Key后传入的数据会替换先前的数据。适用场景需要按主键进行数据更新的场景如用户画像表一个用户一条记录信息随时间更新、设备状态表等。特点实现了类似“主键更新”的能力简化了数据更新的ETL流程。1.2 如何选择合适的数据模型选择模型可以遵循以下决策路径是否有数据更新的需求如果有且是主键更新模式优先考虑Unique 模型。是否主要进行汇总查询且维度固定如果是例如总看“每天的销售额”选择Aggregate 模型并为指标列设置 SUM 函数。是否需要保留所有原始明细进行灵活的多维度分析如果是或者业务场景不确定选择Duplicate 明细模型。是否既有明细查询需求又有部分列需要更新可以考虑使用 Duplicate 模型记录明细同时创建一张 Aggregate 或 Unique 模型的物化视图或 Rollup 表来满足特定查询或更新需求。理解这些模型是创建高效 Doris 表的第一步。接下来我们将在准备好的环境中进行实战。2. 环境准备与前置条件为了完成后续的建表示例你需要一个可用的 Doris 环境。这里以单机部署为例这是学习和测试的最佳方式。2.1 系统与软件要求操作系统CentOS 7 或 Ubuntu 16.04。本文示例基于 CentOS 7.9。JavaJDK 8必须是 Oracle JDK 8u201 或 OpenJDK 8u322。运行java -version确认。Doris 版本本文使用 Apache Doris 1.2.4 版本一个长期稳定版本。请从 Apache Doris 官网 或 GitHub Release 页面下载。客户端工具可以使用 Doris 自带的 MySQL 客户端mysql也可以使用任何兼容 MySQL 协议的图形化工具如 DBeaver、DataGrip。2.2 单机 Doris 快速部署FE BE假设你已经下载了apache-doris-1.2.4-bin-x64.tar.gz安装包。# 1. 解压安装包 tar -zxvf apache-doris-1.2.4-bin-x64.tar.gz -C /opt/ cd /opt/apache-doris-1.2.4/ # 2. 配置 Frontend (FE) cd fe vim conf/fe.conf # 添加或修改以下关键配置指定本机IP假设为192.168.1.100 priority_networks 192.168.1.0/24 # 其他配置如日志目录等可按需调整 # 3. 启动 FE ./bin/start_fe.sh --daemon # 查看日志确认启动成功 tail -f log/fe.log # 看到 thrift server started 和 http server started 字样表示成功 # 4. 使用 MySQL 客户端连接 FE初始无密码 mysql -h 127.0.0.1 -P 9030 -uroot # 在 MySQL 客户端内执行以下 SQL 初始化 root 密码并创建数据库 SET PASSWORD FOR root PASSWORD(your_password); CREATE DATABASE demo_db; USE demo_db; # 5. 配置 Backend (BE) # 新开一个终端窗口 cd /opt/apache-doris-1.2.4/be vim conf/be.conf # 添加或修改 priority_networks 192.168.1.0/24 # 设置数据存储路径确保目录存在且有权限 storage_root_path /path/to/doris_storage # 6. 启动 BE ./bin/start_be.sh --daemon tail -f log/be.log # 看到 heartbeat success 等字样表示启动成功 # 7. 在 FE 节点上添加 BE 节点 # 回到刚才的 MySQL 客户端连接着 FE ALTER SYSTEM ADD BACKEND 192.168.1.100:9050; # 查看 BE 状态确认 Alive 为 true SHOW BACKENDS\G环境就绪后我们就可以在demo_db数据库中开始创建各种类型的数据表了。3. 建表语法深度解析Doris 的建表语句CREATE TABLE兼容 MySQL 协议但包含大量扩展参数以适应其分布式、列式存储的特性。掌握核心子句是灵活建表的关键。3.1 基础建表语句结构一个完整的 Doris 建表语句包含以下主要部分CREATE TABLE [IF NOT EXISTS] [database.]table_name ( column_name column_type [KEY | AGGREGATE KEY] [NULL | NOT NULL] [DEFAULT default_value] [COMMENT column_comment], ... ) [ENGINE olap] -- Doris 默认引擎通常无需指定 [KEY(column_name, ...)] -- 指定 Duplicate 模型的排序列 [COMMENT table_comment] [DISTRIBUTED BY HASH(column_name, ...) [BUCKETS bucket_num]] -- 分桶方式 [PARTITION BY RANGE(column_name) (...)] -- 分区方式 [PROPERTIES (keyvalue, ...)]; -- 表属性3.2 核心子句详解1. 列定义与键类型列定义中的KEY关键字至关重要它定义了该列是否为“维度列”。在 Aggregate 和 Unique 模型中只有被定义为KEY的列才参与数据的去重和比较。在 Duplicate 模型中KEY列仅表示数据按照这些列排序并非唯一约束。AGGREGATE KEY用于定义聚合模型的维度列。UNIQUE KEY用于定义唯一模型的维度列即主键。2. 分区PARTITION BY RANGE分区用于将表划分为多个独立管理的部分通常是按时间范围如天、月。分区可以加速查询利用分区裁剪查询时只扫描相关分区。简化数据管理轻松删除或增加某个时间范围的数据如删除旧分区。均衡负载结合分桶进一步分散数据。3. 分桶DISTRIBUTED BY HASH分桶是 Doris 数据分布的最终单位。数据通过 Hash 算法分散到各个 Bucket 中每个 Bucket 对应一个数据分片Tablet是数据复制和迁移的最小单元。BUCKETS 指定分桶数量。数量适中通常建议在10-100之间单个 Tablet 数据量在100MB-1GB为宜。分桶列应选择高基数列如用户ID且常作为查询条件。4. 表属性PROPERTIES这是 Doris 表的高级控制中心常用属性包括replication_num 数据副本数默认3单机测试可设为1。storage_medium/storage_cooldown_time 定义数据存储介质SSD/HDD和冷却时间用于冷热数据分层。dynamic_partition 启用动态分区自动创建新分区。4. 三种数据模型建表示战现在我们将在demo_db数据库中创建三种不同模型的表并模拟数据导入让你直观感受其区别。4.1 创建 Duplicate 明细模型表假设我们要记录用户每一次的点击行为流水。USE demo_db; CREATE TABLE IF NOT EXISTS user_click_log_dup ( user_id BIGINT NOT NULL COMMENT 用户ID, item_id INT NOT NULL COMMENT 商品ID, category_id SMALLINT COMMENT 商品类目ID, click_time DATETIME NOT NULL COMMENT 点击时间, city VARCHAR(20) COMMENT 所在城市, device VARCHAR(50) COMMENT 设备型号, channel VARCHAR(20) COMMENT 流量渠道 ) ENGINE olap DUPLICATE KEY(user_id, item_id, click_time) -- 指定排序列 COMMENT 用户点击行为明细表 DISTRIBUTED BY HASH(user_id) BUCKETS 10 PROPERTIES ( replication_num 1, -- 单机部署副本数设为1 storage_format V2 );关键点解析DUPLICATE KEY 这里指定了(user_id, item_id, click_time)意味着数据在文件内会按照这个顺序排序存储有利于范围查询。但它不保证唯一性。所有列都没有定义聚合函数数据原样存储。4.2 创建 Aggregate 聚合模型表假设我们需要一张表来快速查询每个城市-渠道的每日点击总量和独立用户数。CREATE TABLE IF NOT EXISTS city_channel_daily_agg ( dt DATE NOT NULL COMMENT 数据日期, city VARCHAR(20) NOT NULL COMMENT 城市, channel VARCHAR(20) NOT NULL COMMENT 渠道, click_count BIGINT SUM DEFAULT 0 COMMENT 点击总次数, unique_user_count BIGINT BITMAP_UNION COMMENT 独立用户数(使用Bitmap计算), last_click_time DATETIME MAX COMMENT 最后点击时间 ) ENGINE olap AGGREGATE KEY(dt, city, channel) -- 维度列 COMMENT 城市-渠道每日聚合表 PARTITION BY RANGE(dt) -- 按天分区 ( PARTITION p202401 VALUES LESS THAN (2024-02-01), PARTITION p202402 VALUES LESS THAN (2024-03-01) ) DISTRIBUTED BY HASH(city, channel) BUCKETS 8 PROPERTIES ( replication_num 1, storage_medium SSD, storage_format V2 );关键点解析AGGREGATE KEY 定义了维度列(dt, city, channel)。所有查询的 GROUP BY 条件必须是这些维度列的子集。聚合函数click_count BIGINT SUM 导入时相同维度的click_count值会被累加。unique_user_count BIGINT BITMAP_UNION 这是 Doris 的高级聚合函数用于精确计算基数如UV。导入时需要传入用户ID构成的Bitmap。last_click_time DATETIME MAX 保留相同维度下最大的时间。PARTITION BY RANGE 按日期分区便于管理历史数据。4.3 创建 Unique 唯一模型表假设我们需要一张用户最新信息表一个用户只有一条最新记录。CREATE TABLE IF NOT EXISTS user_profile_unique ( user_id BIGINT NOT NULL COMMENT 用户ID, user_name VARCHAR(50) REPLACE COMMENT 用户名取最新, city VARCHAR(20) REPLACE COMMENT 城市取最新, age SMALLINT REPLACE COMMENT 年龄取最新, last_login_time DATETIME REPLACE COMMENT 最后登录时间, total_score BIGINT REPLACE_IF_NOT_NULL COMMENT 总积分非NULL值才更新 ) ENGINE olap UNIQUE KEY(user_id) -- 唯一键即主键 COMMENT 用户画像表唯一模型 DISTRIBUTED BY HASH(user_id) BUCKETS 12 PROPERTIES ( replication_num 1, enable_unique_key_merge_on_write true, -- 启用 Merge-on-Write提升点查性能 storage_format V2 );关键点解析UNIQUE KEY(user_id) 指定user_id为唯一键。所有非键列必须指定聚合函数通常是REPLACE。REPLACE 后到的数据直接替换之前的数据。REPLACE_IF_NOT_NULL 只有后到的数据非 NULL 时才替换旧值。这对于部分字段更新非常有用。enable_unique_key_merge_on_write 设置为true启用 Merge-on-Write 模式在数据导入时即完成合并对于点查优化显著但会牺牲部分导入速度。需要根据业务查询模式权衡。5. 数据导入与验证表创建好后我们通过简单的 Stream Load 方式导入一些测试数据验证表的行为。5.1 准备测试数据文件创建 CSV 格式的测试数据文件test_data.csv。-- 文件test_dup.csv (用于明细表) 1001,2001,5,2024-01-15 10:01:02,北京,iPhone13,organic 1001,2001,5,2024-01-15 10:01:05,北京,iPhone13,organic -- 完全重复的一行 1002,2003,8,2024-01-15 10:02:01,上海,Xiaomi12,adwords -- 文件test_agg.csv (用于聚合表需对应维度) 2024-01-15,北京,organic,2,1001|1002,2024-01-15 10:01:05 -- 注意Bitmap列在实际Stream Load时需要特殊处理这里仅为示意 -- 文件test_unique.csv (用于唯一表) 1001,张三,北京,25,2024-01-15 09:00:00,1500 1001,张老三,北京,26,2024-01-16 10:00:00,NULL -- 同user_id更新信息积分不变5.2 使用 Stream Load 导入数据通过 HTTP 协议向 Doris 导入数据。# 导入数据到明细表 curl --location-trusted -u root:your_password \ -H label:label_click_$(date %s) \ # Label需唯一用于保证导入幂等性 -H column_separator:, \ -T /path/to/test_dup.csv \ http://127.0.0.1:8030/api/demo_db/user_click_log_dup/_stream_load # 导入数据到唯一表 curl --location-trusted -u root:your_password \ -H label:label_profile_$(date %s) \ -H column_separator:, \ -H columns: user_id, user_name, city, age, last_login_time, total_score \ -T /path/to/test_unique.csv \ http://127.0.0.1:8030/api/demo_db/user_profile_unique/_stream_load5.3 查询验证结果连接 Doris 的 MySQL 端口进行查询。-- 1. 查询明细表会看到两行完全一样的数据 SELECT * FROM user_click_log_dup ORDER BY click_time; -- 结果1001 对应的记录会出现两行证明明细模型保留所有数据。 -- 2. 查询唯一表user_id1001 只保留最新一条 SELECT * FROM user_profile_unique WHERE user_id 1001; -- 结果姓名为“张老三”年龄为26积分为1500因为REPLACE_IF_NOT_NULL新记录的NULL未覆盖旧值。6. 进阶动态分区与索引优化基础表创建后在生产环境中还需要考虑自动化管理和查询加速。6.1 配置动态分区手动管理分区很繁琐。可以修改聚合表启用动态分区自动创建未来分区并删除旧分区。-- 首先删除旧的聚合表测试环境操作生产环境请谨慎 DROP TABLE IF EXISTS city_channel_daily_agg; -- 重新创建带动态分区属性的表 CREATE TABLE IF NOT EXISTS city_channel_daily_agg ( ... -- 列定义与之前相同 ) ... PARTITION BY RANGE(dt)() DISTRIBUTED BY HASH(city, channel) BUCKETS 8 PROPERTIES ( replication_num 1, dynamic_partition.enable true, dynamic_partition.time_unit DAY, dynamic_partition.start -7, -- 动态创建从7天前开始的分区 dynamic_partition.end 3, -- 动态创建到3天后的分区 dynamic_partition.prefix p, dynamic_partition.buckets 8, storage_medium SSD );这样系统会自动维护分区例如每天自动创建p20240301这样的分区。6.2 使用 Rollup 进行索引优化Rollup物化索引是 Doris 中一种预计算的索引结构可以显著加速特定查询。例如我们的明细表经常需要按city和category_id进行筛选。-- 为 user_click_log_dup 表创建一个 Rollup ALTER TABLE user_click_log_dup ADD ROLLUP rollup_city_category (city, category_id, user_id, click_time, device); -- 这个Rollup将数据按照 (city, category_id) 重新排序存储 -- 创建完成后Doris 优化器会自动选择是否使用该Rollup。 -- 你可以通过 EXPLAIN 命令查看查询是否命中Rollup EXPLAIN SELECT city, category_id, COUNT(*) FROM user_click_log_dup WHERE city北京 GROUP BY city, category_id;7. 常见问题与排查思路在创建和管理 Doris 表时你可能会遇到以下典型问题。问题现象常见原因解决思路建表失败报错Failed to create partition分区起始值大于结束值分区键类型与表达式不匹配。检查PARTITION BY RANGE语句确保VALUES LESS THAN的值是递增的且类型正确。数据导入失败报错ETL_QUALITY_UNSATISFIED数据格式错误如 NULL 值导入到 NOT NULL 列数值越界。1. 检查CSV文件列分隔符、换行符。2. 检查数据是否违反列约束。3. 使用-H max_filter_ratio:0.1容忍一定比例错误先观察具体错误行。查询聚合表时结果与预期汇总值不符1. 查询的GROUP BY列不是AGGREGATE KEY的子集。2. 导入数据前未理解聚合模型语义。1. 确保SELECT中的非聚合列都在GROUP BY中且属于AGGREGATE KEY。2. 重新理解聚合模型相同维度列的数据在导入时已被聚合查询时无法再获取明细。唯一表Merge-on-Write模式导入速度慢Merge-on-Write 模式需要在导入时进行数据合并比普通导入慢。对于批量历史数据导入可临时设置enable_unique_key_merge_on_write false导入完成后再改为true并执行BASE COMPACTION。SHOW BACKENDS显示 BE 状态不为 AliveBE 进程未启动网络不通端口被占用。1. 检查 BE 进程 ps aux8. 生产环境最佳实践与工程建议基于大量项目经验以下建议能帮助你设计出更稳健、高效的 Doris 表。1. 分区分桶设计原则分区键选择 首选时间日期列便于冷热数据管理和生命周期操作。分区数量不宜过多通常不超过1000个避免元数据压力。分桶键选择 选择高基数、经常作为查询条件的列。避免选择低基数列如性别、状态标志会导致数据倾斜。分桶数建议是 BE 节点数的整数倍便于数据均匀分布。分桶数估算 目标使单个 Tablet 大小在 100MB 到 1GB 之间。例如表预计原始数据 100GB副本数2则总数据量 200GB。若期望 Tablet 为 500MB则分桶数约为200GB / 0.5GB 400。再根据 BE 节点数调整。2. 字段类型与编码优化使用合适的类型 能用INT不用BIGINT能用VARCHAR不用STRING。精确的类型能节省存储和内存。应用编码 对低基数的字符串列如城市名、枚举状态使用BITMAP或DICT编码可以极大提升压缩率和查询速度。3. 数据更新策略高频小批量更新 使用 Unique 模型Merge-on-Write。虽然导入性能有损耗但查询性能最佳。低频批量更新 使用 Unique 模型非 Merge-on-Write或 Aggregate 模型通过批量导入覆盖。仅追加无更新 使用 Duplicate 或 Aggregate 模型性能最优。4. 物化视图Rollup规划不要过度创建 Rollup每个 Rollup 都是一份冗余存储。优先为最核心、最耗时的查询创建。Rollup 的排序列必须是查询条件列或分组列的前缀才能生效。5. 变更管理表结构变更如 ADD COLUMN是轻量级操作但修改列类型或顺序是重量级操作需要谨慎。任何表结构变更和 Rollup 操作建议先在测试环境验证再在业务低峰期执行。掌握 Doris 建表就掌握了其高性能查询的钥匙。从理解三大模型开始到精心设计分区分桶再到利用 Rollup 加速每一步都影响着最终的数据效能。建议你在自己的测试环境中反复练习本文的示例并尝试根据一个真实的业务场景如订单表、日志表进行表设计。只有通过实践才能深入理解不同参数对系统行为的影响从而设计出最适合自己业务的数据表结构。