简介《数据仓库与数据挖掘》课程配套的“创建OLAP实例”实验报告面向数据库课程学习者、数据仓库初学者及需要完成OLAP实训作业的学生。报告以华兴商业银行为案例完整记录了使用SQL Server 2005 Management Studio与Business Intelligence Development Studio构建数据仓库、导入贷款数据、设计数据库关系图、创建Analysis Services多维数据集并按年、季度对正常、关注、可疑、损失等贷款类别进行多维浏览与分析的操作步骤包含删除重复记录存储过程、外键设置、部署项目等关键细节可直接作为实验参考或答辩材料。资源为1个doc文档大小1.05MB内容约含有实验目的、环境、过程、结果与结论等完整模块已有351人学习下载。1. OLAP实例不是建个库那么简单大多数团队第一次接触 OLAP都是从“把数据从业务库同步过来然后跑个报表”开始的。等真到创建 OLAP 实例这一步才发现它和建一个 MySQL 库完全是两码事表结构要按维度建模重排数据要按粒度和聚合层级预先处理查询引擎要选对列存储和索引方式。更麻烦的是OLAP 实例的创建还牵扯到数据仓库分层、数据挖掘所需的宽表结构、以及后续的增量更新策略——每一步都在决定这个实例是能扛住三年业务还是上线三个月就卡死。本文就围绕“创建 OLAP 实例”这条主线把数据仓库与数据挖掘场景下从模型设计到实例落地再到查询优化的一条完整路径讲清楚。2. 先定模型OLAP 实例的星型与雪花选型数据仓库分层的落点2.1 为什么 OLAP 实例必须先建模而不是先建表很多刚接触数据仓库的工程师容易搞反顺序先建库建表再考虑怎么查。OLAP 的查询特征决定了它的物理结构必须服务于“多维分析”这个核心诉求——用户会从时间、地区、产品、渠道等多个维度去切片、钻取、旋转数据。如果表结构还是业务库那种三范式设计每一次多维查询都要关联七八张表性能直接崩。OLAP 的理论基础是维度建模由 Kimball 提出。核心思想是“事实表 维度表”事实表存放度量值销售额、订单数、点击量维度表存放描述性属性日期、城市、商品分类。创建实例时选星型还是雪花型取决于数据仓库的维护成本和查询性能的平衡星型模型维度表直接关联事实表不做层级拆解。冗余大但查询路径短适合大多数报表场景是数据仓库中“最常用”的设计。雪花模型维度表进一步规范化比如把“地区”拆成“省—市—区”三张表。减少冗余但 join 变多查询变慢适合对存储极其敏感且查询模式固定的场景。从数据仓库分层的视角看OLAP 实例一般落在 DWD 层之上。DWD 层做清洗和规范化DWS 层做轻度汇总ADS 层做应用指标。OLAP 实例直接对接 DWS 或 ADS 的数据因为它需要的是“已经清洗好、粒度和维度明确、度量值可聚合”的数据。2.2 维度建模的粒度声明和退化维度处理在创建实例之前必须先写清楚模型的“粒度”。粒度决定事实表中每一行代表什么——一笔订单、一个订单项、一次页面访问还是某个用户一天的累计行为。粒度不先定死后续所有聚合都会失真。实际落地时最常见的做法是三层声明粒度订单级别一行代表一个订单 维度下单日期、下单省份、商品一级类目、支付渠道 度量订单金额、订单数、支付成功订单数这里有个容易踩的坑订单表里通常有“下单时间”和“支付时间”两个时间戳建模时要把其中一个作为事实表的“时间维度外键”另一个如果也做维度就会造成同一个事实被重复计数。常见做法是“下单时间”进维度表“支付时间”只作为普通字段保留在事实表中聚合分析时默认按下单时间切片。退化维度指的是原本应该单独建维度表、但实际只有一两个属性的维度比如“订单编号”。这类字段不需要单独建表直接冗余在事实表里即可。创建 OLAP 实例时退化维度要做成“低基数列”来存储避免被误判成度量值参与聚合计算。2.3 事实表设计中的度量一致性事实表中的度量列必须在创建实例前统一口径。同一个“销售额”在业务库可能是含税价在财务口径是不含税价。这个如果不一致OLAP 实例里的汇总数据就是垃圾。设计度量列时我一般遵循以下规则所有可加度量金额、数量保持统一的单位和精度金额统一到“分”而不是“元”半可加度量库存、余额禁止直接 SUM必须走 AVG 或 LAST_VALUE不可加度量比率、单价提前在 ETL 阶段计算好避免 OLAP 查询时做除法运算-- 数据仓库 DWS 层建表示例订单事实表 CREATE TABLE dws.dws_order_fact ( order_id BIGINT COMMENT 退化维度订单号, date_key INT COMMENT 下单日期维度外键 YYYYMMDD, province_id INT COMMENT 省份维度外键, category_id INT COMMENT 商品类目维度外键, pay_channel_id INT COMMENT 支付渠道维度外键, order_amount BIGINT COMMENT 订单金额单位分, order_cnt INT COMMENT 订单数恒为1便于后续 SUM, pay_order_cnt INT COMMENT 支付成功订单数值为 0 或 1, partition_date STRING COMMENT 分区字段 ) PARTITIONED BY (partition_date) STORED AS PARQUET;这段 DDL 可以看作创建 OLAP 实例前的事实表模板。order_cnt 恒为 1 是全表最核心的“作弊”设计后续不管怎么聚合只要 SUM 这一列就能得到订单总数不需要 COUNT(DISTINCT order_id) 这种重操作。pay_order_cnt 设计成 0/1 标记计算支付转化率时直接 SUM 后除以 SUM(order_cnt) 即可。参数说明里有个重点PAY_CHANNEL_ID 是维度外键这里没用字符串的渠道名称而是用 ID是为了减少事实表存储占用。渠道名称在维表层维护查询时再关联补齐。3. 用 StarRocks 或 ClickHouse 创建 OLAP 实例的最小可复现步骤3.1 选型依据列存、向量化执行与存储引擎当前主流 OLAP 引擎里Apache Doris / StarRocks 和 ClickHouse 是团队自建数仓时入场最顺的两个。它们共同点是列式存储 向量化执行引擎都支持标准 SQL都能跑在 x86 裸机或容器上。差异在于StarRocks对数据更新场景支持更好Unique Key 模型适合数据仓库里有大量维度修正、状态变更的场景同时它在多表 join 优化上明显强于 ClickHouse较大宽表的关联查询不容易崩。ClickHouse单表聚合查询速度更快但多表 join 能力较弱数据更新成本高更适合日志分析、事件流分析这类“只追加、少更新”的场景。从“数据仓库与数据挖掘”这个目标出发一般工作流是数据挖掘需要找特征、做宽表特征是不断迭代修正的更新频率比日志场景高得多。这种场景 StarRocks 和 Doris 更顺手。3.2 最小实例搭建从空集群到可查询以 StarRocks 为例创建 OLAP 实例从来不是 CREATE DATABASE 一句话的事。但有的人确实把它当作“创建数据库实例”来搜——实际上在绝大多数数据仓库架构里OLAP 实例指的是“一个可对外提供多维查询服务的计算与存储集群”它可以通过一套建库建表 导入任务的组合编排来落地。先在你已有的 StarRocks 集群里建库CREATE DATABASE IF NOT EXISTS olap_dw; USE olap_dw;然后创建维度表。维度表的特点是数据量小、需要支持实时更新和点查关联CREATE TABLE dim_province ( province_id INT, province_name VARCHAR(64), region_name VARCHAR(64), update_time DATETIME ) PRIMARY KEY (province_id) DISTRIBUTED BY HASH(province_id) PROPERTIES (replication_num 1);CREATE TABLE 之后DISTRIBUTED BY HASH(province_id) 是在声明数据分桶方式。OLAP 实例的查询性能很大一部分取决于分桶键选得对不对。province_id 作为维度表主键、同时是查询时的等值过滤条件按它哈希分桶数据分布最均匀。接着建事实表这是 OLAP 实例里最核心的对象CREATE TABLE olap_order_fact ( date_key INT, province_id INT, category_id INT, pay_channel_id INT, order_amount BIGINT, order_cnt INT, pay_order_cnt INT ) DUPLICATE KEY(date_key, province_id) DISTRIBUTED BY HASH(category_id) BUCKETS 24 PROPERTIES (replication_num 1);注意到 DUPLICATE KEY 是 StarRocks 的三种数据模型之一专门服务“事实表只追加不更新”的场景。DUPLICATE KEY 声明了排序键数据在存储引擎中按 date_key province_id 有序排列Range 查询能走前缀裁剪。BUCKETS 24 是分桶数经验值是单个桶的数据量控制在 100MB 到 1GB 之间数据量越大桶数越多。导入数据时OLAP 实例的常见做法是走 Broker Load 从 HDFS 或对象存储拉取 Parquet 文件。首日全量导入LOAD LABEL olap_dw.etl_20250101_first_load ( DATA INFILE(s3://datahouse/dws/dws_order_fact/*) INTO TABLE olap_order_fact FORMAT AS parquet (date_key, province_id, category_id, pay_channel_id, order_amount, order_cnt, pay_order_cnt) ) PROPERTIES ( timeout 3600, max_filter_ratio 0.01 );LOAD LABEL 是任务的唯一标识必须是全局唯一的字符串用于后续查看导入状态和回滚。max_filter_ratio 是容错率允许 1% 的数据因类型转换失败被过滤掉第一次导入建议设成 0避免脏数据安静地流失。导入完成后通过 SHOW LOAD WHERE LABEL etl_20250101_first_load 查看状态返回 FINISHED 才算建成。对数据量不大、团队也没有 Hadoop 生态的常见的轻量替代方向是直接走 SQL 方式导入。这也方便后续工单里把“创建OLAP实例”的交付物收敛为一段可重复执行的脚本组合。3.3 实例创建后必做的三件验证实例建完不等于能用常规做法是先跑三条验证语句。第一条验证维度关联不丢数据SELECT COUNT(*) FROM olap_order_fact f LEFT JOIN dim_province p ON f.province_id p.province_id WHERE p.province_name IS NULL;返回 0 行说明关联完备。第二条验证聚合正确性拿明细层数据交叉比对SELECT SUM(order_amount) FROM olap_order_fact WHERE date_key 20250101;拿这个结果对比数仓 DWS 层同一分区的汇总值误差必须在千分之一以内超过这个范围优先检查是否丢分区或者度量口径不一致。第三条验证分桶是否引发数据倾斜扫描表的 Tablet 元数据看每台 BE 节点的数据量分布。任何单节点数据量偏差超过 30%立刻调整分桶键或增加桶数。4. 数据仓库到 OLAP 的管道设计维度退化与数据装载4.1 数据装载周期的归属同步任务该放哪一层刚才建好的 OLAP 表本质上还是在做一个“按查询场景重排结构”的动作。跟常见的数据仓库交付物相比它的技术债务更容易出在装载环节—— OLAP 实例需要“周期性刷新”刷新频率由业务对数据新鲜度的容忍度决定常见如 T1、小时级。这一层装载任务在数据仓库的规范里属于“调度系统”的范畴通常由 DolphinScheduler 或 Airflow 统一编排。DolphinScheduler 和 Airflow 之间很多人只看“谁更流行”但在数据仓库的 OLAP 场景里有个更硬的指标DolphinScheduler 更贴近数据仓库的“工作流”概念DolphinScheduler 的任务类型里直接内置了 SQL、SPARK、PYTHON 等类型配置工作量小一些Airflow 在复杂依赖和跨团队数据协议上更强但代码量也更大。小型数据仓库团队5 人以下我一般建议用 DolphinScheduler 起步架构上的坑比 Airflow 少。大型团队看数据平台基建的历史惯性去选。4.2 增量装载的物化视图策略既然要刷新就会涉及两个选型方向之间的比较每日全量重建分区还是小时级增量 UPSERT。常见做法是“分区级全量计算、目标端增量合并”。OLAP 事实表按天做二级分区ALTER TABLE olap_dw.olap_order_fact ADD PARTITION p20250101 VALUES LESS THAN (2025-01-02) DISTRIBUTED BY HASH(category_id) BUCKETS 24;每次调度先算前一天数据、写入新分区维度表则通过 Stream Load 的 upsert 语义更新。这个模式的好处是事实表底层每个分区是独立的目录旧分区没动过就不需要重算查询裁剪时也只扫需要的新分区。但大多数查询业务并不只是“截止昨日”而是“近 30 天滚动”。每日全量重建 30 个分区很浪费这时就看出物化视图的用场。StarRocks 3.x 的异步物化视图能对“多表 JOIN 聚合”的 SQL 自动刷新对创建 OLAP 实例的团队来说这类能力让“建一个能直接支撑指标查询的物化视图”越来越像创建实例的默认选项。4.3 数据挖掘特有的宽表装载数据仓库跟数据挖掘直接衔接的一步是特征宽表。OLAP 实例建好了数据挖掘模型还要从里面取特征——算法同学通常不会去写复杂的多表关联 SQL他们期望拉到一张“一个用户一行、每列是数值特征”的表。这时候仓库工程师需要额外建一张特征宽表。CREATE TABLE user_feature_wide ( user_id BIGINT, -- 用户维度特征 user_reg_days INT, user_level INT, -- 近30天行为特征 order_cnt_30d BIGINT, order_amount_30d BIGINT, avg_order_amount_30d DOUBLE, -- 近7天行为特征 order_cnt_7d BIGINT, active_day_cnt_7d BIGINT ) DUPLICATE KEY(user_id) DISTRIBUTED BY HASH(user_id) BUCKETS 48;特征表要覆盖时间窗口特征时一般建议在 SQL 里写死窗口边界不做“相对当前日期”的计算这样可以保证特征结果可回放、离线训练和在线推理的特征口径一致。5. 预聚合选择RollUp 表、物化视图与查询下推的边界5.1 什么时候必须用 RollUp 而不是等查询时临时聚合创建 OLAP 实例之后的第一个性能瓶颈通常出现在“大时间段 多维度 GROUP BY”的查询上。以订单事实表为例用户在前端 BI 上按“月省份类目”拉取 2024 年全年数据计算引擎要扫全年 365 个分区、聚合出结果。如果明细量是日均 1000 万行一次拖出 36.5 亿行做聚合任何引擎都吃不消。预聚合的思路是提前把“月、省份、类目”这组维度组合的聚合结果算好存起来。StarRocks 的 RollUp 表机制物化视图的索引形态就是干这个的CREATE MATERIALIZED VIEW mv_month_prov_cat AS SELECT date_key, province_id, category_id, SUM(order_amount) AS total_amount, SUM(order_cnt) AS total_cnt FROM olap_order_fact GROUP BY date_key, province_id, category_id;创建物化视图本身在 StarRocks 中不立即触发全量计算后台有线程异步构建。构建期间旧查询仍走明细表不会阻塞业务。物化视图的名字是可以自定义的查询时也无需手动指定它优化器会自动匹配。这里要说明的是物化视图不是建得越多越好。每新增一张导入时的额外计算开销就多一份后台合并的负担也重一层。常见的落地策略是先给“最高频的 3 到 5 个维度组合”建不是把“所有可能组合”都建了。5.2 查询下推哪些计算应该留在 OLAP 引擎里数据挖掘场景里经常有特征提取 SQL 需要“按用户分组、按时间排序、取最近一条”这种窗口函数操作。这部分计算不要拉到 Spark 或 Flink 里做。OLAP 引擎大多支持窗口函数下推。但有个边界如果窗口函数里用了 PARTITION BY 高基数列比如 user_id引擎内部仍然会把整个分区数据拉到一个 BE 节点计算内存容易打爆。此时要评估是不是改为“明细表预排序 分组聚合”的写法更稳-- 不推荐大数据量下 PARTITION BY user_id 窗口函数 SELECT user_id, order_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date_key DESC) AS rn FROM olap_order_fact; -- 推荐按用户哈希分桶后桶内排序取 top1 SELECT user_id, order_amount FROM ( SELECT user_id, order_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date_key DESC) AS rn FROM olap_order_fact ) t WHERE rn 1;第二段看起来多包了一层子查询但它给了优化器“先过滤再计算窗口”的下推机会。如果表已经按 user_id 分桶物理执行时每个桶独立算排序不涉及跨节点数据重分布。5.3 数据一致性检查物化视图与基线表的自动校验物化视图的坑在于“查询走了视图但数据没更新”或者“更新了但部分数据丢失”。常见做法是写个校验任务每天对比基表和 RollUp 表的聚合值-- 校验物化视图是否正确 SELECT (SELECT SUM(total_amount) FROM mv_month_prov_cat WHERE date_key IN (20241201, 20241202)) AS mv_sum, (SELECT SUM(order_amount) FROM olap_order_fact WHERE date_key IN (20241201, 20241202)) AS base_sum;两个值如果不等优先排查昨天的装载任务有没有失败或者物化视图刷新有没有卡在中间状态。等值检查通过后再对查询引擎做一次“命中计划确认”用 EXPLAIN 语句查看物化视图是否被正确改写命中。6. 数据挖掘场景下的 OLAP 验证用物化视图做特征一致性校验最后一章给一个实战落点你创建完 OLAP 实例给它喂了数据也建好了物化视图但数据挖掘团队跟你说“特征不对”。这个“不对”通常不是因为特征算错而是 OLAP 里的“实时口径”和训练时的“离线口径”不一致。特征不一致高发在两个点。一是时间窗口边界离线训练时“近 30 天”通常是自然日 [D-30, D-1]但线上实时写入 OLAP 时如果习惯用 INTERVAL 30 DAY 来计算这个值会根据运行时刻动态浮动导致大部分请求落到 D-31 或 D0特征分布直接跳变。常规解法是要求所有特征 SQL 显式指定起止分区值而不是用相对时间函数。二是用户维度的空值处理。数据仓库里注册表维度 join 不上的 user_id在事实表中表现为 NULL。许多聚合函数SUM、COUNT会自动忽略 NULL但 COUNT(DISTINCT user_id) 不会——它会把 NULL 当作一个独立值计入。在验证特征“用户覆盖数”时这个差距可能让模型跑出来的样本量和预期差一个量级。这种情况下用下面的查询快速定位SELECT COUNT(DISTINCT user_id) AS all_user_cnt, COUNT(DISTINCT IF(user_id IS NOT NULL, user_id, NULL)) AS non_null_user_cnt FROM olap_dw.olap_order_fact WHERE date_key 20250101;两张数值不一样说明宽表装载逻辑里漏了“NULL 用户统一改写为 0”的规则。数据仓库规范里通常要求在 DWD 层就把 user_id 填成 0 而不是 NULL0 再关联不上维度表时也能直观暴露问题。接下来把“可用实例”验证落到一个可重复执行的模板里。创建一个 OLAP 实例最后要做三件事跑一遍全链路 SQL 脚本、核对一张基线宽表的行数、确认预聚合对象的刷新延迟。可以把验证脚本存成一个独立 SQL 文件纳入调度系统作为指标前置检查-- 验证脚本olap_health_check.sql -- 1. 检查物化视图刷新时间是否晚于数据装载时间 SELECT mv_name, last_refresh_time FROM information_schema.materialized_views WHERE mv_name mv_month_prov_cat AND last_refresh_time (SELECT MAX(partition_update_time) FROM olap_dw.olap_order_fact); -- 2. 检查疑似数据倾斜的桶 SHOW TABLET FROM olap_dw.olap_order_fact;验证设计里信息架构的 information_schema 视图里能查到物化视图的元数据这个指标的意义是“确认刷新链路完整”而不只是“查询跑得动”。数据挖掘团队依赖 OLAP 实例做特征探索、宽表计算和交叉验证时他们真正需要的不是最快的查询而是结果稳定可复现。把可复现性验证固化到实例交付环节里比任何性能调优都更能减少跨团队扯皮。如果更追求把验证做成自动化常见做法是写一个 Python 脚本用 pymysql 连到 StarRocks 的 MySQL 协议端口执行验证 SQL然后把结果推送到飞书或钉钉群import pymysql conn pymysql.connect( host10.0.0.11, port9030, userroot, passwordyour_password, databaseolap_dw ) with conn.cursor() as cur: cur.execute(SELECT last_refresh_time FROM information_schema.materialized_views WHERE mv_name mv_month_prov_cat) mv_time cur.fetchone()[0] cur.execute(SELECT MAX(partition_update_time) FROM olap_dw.olap_order_fact) base_time cur.fetchone()[0] if mv_time base_time: print(HEALTH_CHECK_PASS) else: raise SystemExit(HEALTH_CHECK_FAIL: mv refresh lagging behind base table)脚本里用了两个时间源做比较物化视图刷新时间和基表的数据更新时间前者滞后后者意味着预聚合对象可能没把最新分区纳入查询结果大概率缺一天数据挖掘训练集和验证集也会跟着缺样本。把这段脚本挂上 crontab 或调度系统每天早上业务查询前跑一遍OLAP 实例才真正进入了“可放心使用”的状态。跑批不是把自己困在运维琐事里而是把潜在数据质量问题在变成线上故障之前拦截下来。本文还有配套的精品资源点击获取