创建Starrocks数据库和表
- 下载mysql客户端并上传到已购买的云服务器ECS:mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz,将客户端放置到云服务器的/opt目录下 图1 MySQL客户端下载
- 使用SSH登录工具,通过弹性IP远程登录到Linux弹性云服务器。具体登录操作步骤请参见弹性云服务器《用户指南》中的“ SSH密码方式登录”章节。
- 进入SSH工具的根目录,执行如下命令进行解压,并进入bin目录。
cd /opt;tar -xzvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz;cd /opt/mysql-5.7.22-linux-glibc2.12-x86_64/bin
- 执行下面命令连接StarRocks集群
./mysql -uadmin -ppassword -h集群内网地址 -P9030
- 执行如下sql进行建库、建表、物化视图的创建
###建库 CREATE DATABASE IF NOT EXISTS realtime_dw; USE realtime_dw; ### 3.2 ODS层(原始数据层) -- ODS 层:订单原始数据 CREATE TABLE IF NOT EXISTS ods_orders ( order_id BIGINT NOT NULL COMMENT '订单ID', customer_id INT NOT NULL COMMENT '客户ID', order_time DATETIME COMMENT '订单时间', amount DECIMAL(10,2) COMMENT '订单金额', channel VARCHAR(20) COMMENT '销售渠道:线上/线下', product_category VARCHAR(50) COMMENT '商品品类', payment_method VARCHAR(20) COMMENT '支付方式', city VARCHAR(50) COMMENT '城市' ) ENGINE=OLAP PRIMARY KEY(order_id) DISTRIBUTED BY HASH(order_id) BUCKETS 3 PROPERTIES ( "replication_num" = "2" ); -- 物化视图:DWD层订单明细宽表(数据加工与维度扩展) CREATE MATERIALIZED VIEW mv_dwd_order_detail COMMENT 'DWD层-订单明细宽表(含时间维度)' REFRESH IMMEDIATE ASYNC AS SELECT order_id, customer_id, order_time, CAST(order_time AS DATE) AS order_date, HOUR(order_time) AS order_hour, amount, channel, product_category, payment_method, city, -- 判断是否周末 CASE WHEN DAYOFWEEK(order_time) IN (1, 7) THEN 1 ELSE 0 END AS is_weekend, -- 判断时段 CASE WHEN HOUR(order_time) BETWEEN 7 AND 9 THEN '早高峰' WHEN HOUR(order_time) BETWEEN 11 AND 13 THEN '午高峰' WHEN HOUR(order_time) BETWEEN 17 AND 19 THEN '晚高峰' ELSE '其他' END AS time_period FROM ods_orders; -- 物化视图:DWS层客户消费汇总 CREATE MATERIALIZED VIEW mv_dws_customer_summary COMMENT 'DWS层-客户消费汇总表' REFRESH IMMEDIATE ASYNC AS SELECT customer_id, order_date AS stat_date, COUNT(*) AS total_orders, SUM(amount) AS total_amount, SUM(CASE WHEN channel = '线上' THEN amount ELSE 0 END) AS online_amount, SUM(CASE WHEN channel = '线下' THEN amount ELSE 0 END) AS offline_amount, AVG(amount) AS avg_order_amount FROM mv_dwd_order_detail GROUP BY customer_id, order_date; -- 物化视图:ADS层客户画像 CREATE MATERIALIZED VIEW mv_ads_customer_profile COMMENT 'ADS层-客户画像表' REFRESH IMMEDIATE ASYNC AS SELECT customer_id, SUM(total_orders) AS total_orders, SUM(total_amount) AS total_amount, SUM(online_amount) AS online_amount, SUM(offline_amount) AS offline_amount, -- 渠道偏好 CASE WHEN SUM(online_amount) > SUM(offline_amount) THEN '线上偏好' WHEN SUM(online_amount) < SUM(offline_amount) THEN '线下偏好' ELSE '均衡' END AS channel_preference, -- 客户价值分级 CASE WHEN SUM(total_amount) >= 2000 THEN '高价值' WHEN SUM(total_amount) >= 1000 THEN '中价值' ELSE '普通' END AS customer_value FROM mv_dws_customer_summary GROUP BY customer_id;数据分层说明:
- ods_orders:通过Flink CDC实时同步任务将RDS for Mysql订单基本信息同步至StarRocks作为ODS贴源层数据。
- mv_dwd_order_detail:StarRocks新建物化视图,通过对ODS贴源层ods_orders数据加工进行维度扩展,新增订单日期、订单小时、是否周末(周六/周日标记为1)、时段分类(早高峰/午高峰/晚高峰/其他)维度,作为DWD宽表明细层数据。
- mv_dws_customer_summary:StarRocks新建物化视图,对DWD层各维度数据进行汇总,生成订单总数、消费总金额、线上消费金额、线下消费金额、平均订单金额等维度汇总情况,作为DWS汇总层数据。
- mv_ads_customer_profile:StarRocks新建物化视图,在DWS汇总层数据基础上根据业务情况对数据进行指标加工,生成如下指标:1.基础统计:总订单数、总金额、线上/线下金额;2.渠道偏好:比较线上/线下金额,判断客户偏好;3.客户价值:根据总金额分级(≥2000高价值,≥1000中价值,其他普通),作为ADS数据应用层。
表结构说明:
- 表名: ods_orders
说明: 订单原始数据表,存储业务系统同步的原始订单数据
字段名
数据类型
字段说明
业务含义
order_id
BIGINT
订单ID
主键,订单唯一标识
customer_id
INT
客户ID
客户唯一标识
order_time
DATETIME
订单时间
订单创建时间
amount
DECIMAL(10,2)
订单金额
订单总金额
channel
VARCHAR(20)
销售渠道
线上/线下
product_category
VARCHAR(50)
商品品类
商品分类
payment_method
VARCHAR(20)
支付方式
支付渠道
city
VARCHAR(50)
城市
订单所在城市
- 视图名: mv_dwd_order_detail
说明: 订单明细宽表,包含时间维度扩展
字段名
数据类型
来源
字段说明
业务含义
order_id
BIGINT
ods_orders
订单ID
主键
customer_id
INT
ods_orders
客户ID
客户标识
order_time
DATETIME
ods_orders
订单时间
完整时间戳
order_date
DATE
计算字段
订单日期
日期维度
order_hour
INT
计算字段
订单小时
小时维度(0-23)
amount
DECIMAL(10,2)
ods_orders
订单金额
订单金额
channel
VARCHAR(20)
ods_orders
销售渠道
线上/线下
product_category
VARCHAR(50)
ods_orders
商品品类
商品分类
payment_method
VARCHAR(20)
ods_orders
支付方式
支付渠道
city
VARCHAR(50)
ods_orders
城市
城市维度
is_weekend
INT
计算字段
是否周末
0-工作日,1-周末
time_period
VARCHAR(20)
计算字段
时段分类
早高峰/午高峰/晚高峰/其他
- 视图名: mv_dws_customer_summary
说明: 按客户和日期汇总的消费统计
字段名
数据类型
计算逻辑
字段说明
customer_id
INT
分组键
客户ID
stat_date
DATE
分组键
统计日期
total_orders
BIGINT
COUNT(*)
订单总数
total_amount
DECIMAL
SUM(amount)
消费总金额
online_amount
DECIMAL
线上订单金额汇总
线上消费金额
offline_amount
DECIMAL
线下订单金额汇总
线下消费金额
avg_order_amount
DECIMAL
AVG(amount)
平均订单金额
- 视图名: mv_ads_customer_profile
说明: 客户画像标签,用于客户分析
字段名
数据类型
计算逻辑
字段说明
customer_id
INT
分组键
客户ID
total_orders
BIGINT
累计订单数
历史订单总数
total_amount
DECIMAL
累计消费金额
历史消费总额
online_amount
DECIMAL
累计线上消费
线上消费总额
offline_amount
DECIMAL
累计线下消费
线下消费总额
channel_preference
VARCHAR(20)
渠道偏好计算
线上偏好/线下偏好/均衡
favorite_category
VARCHAR
待计算
最常购买品类
favorite_payment
VARCHAR
待计算
最常用支付方式
customer_value
VARCHAR(20)
价值分层
高价值/中价值/普通