手把手进行数据分析，解锁游戏行为画像

一款游戏的成功不仅依赖于其引人入胜的故事情节和精美的画面，更在于能否精准地理解和满足玩家的需求。准确刻画出玩家的画像行为就能更好地提升游戏的可玩性，那么你想知道自己的游戏玩家画像是什么吗？

E-MapReduce StarRocks 版是阿里云提供的 Serverless StarRocks 全托管服务，提供高性能、全场景、极速统一的数据分析体验，内核 100% 兼容 StarRocks，性能比传统 OLAP 引擎提升 3-5 倍，3.X版本提供了物化视图，Paimon读写等多种新能力。

Apache Paimon作为新兴的数据湖存储技术，2024 年 4 月完成孵化成为Apache顶级项目。Apache Paimon 采用开放的数据格式和技术理念，提供高吞吐、低延迟的数据摄入、更新及查询能力，且与诸多业界主流计算对接，是 LakeHouse 架构中不可或缺的一部分。

本文将基于阿里云的EMR Serverless StarRocks，将模拟数据用户表与行为表导入到StarRocks，作为ODS层数据，然后通过使用StarRocks的物化视图，自动化构建数据仓库中DWD层与用于分析的ADS层数据表（按小时自动刷新），最后我们将通过数据湖DLF体验EMR StarRocks读写Paimon的最新能力，通过3个环节构建一个性能与成本均衡的游戏玩家画像和行为分析平台。

4. 将ADS层数据回写Paimon，并直接查询Paimon数据；

在方案体验之前请先完成环境准备：

https://emr-next.console.aliyun.com/ 进行连接实例。（ 实例 可通过免费试用领取）

1. 数据准备

a. 创建StarRocks表。进入 EMR StarRocks Manager 控制台，单击左侧导航栏 SQL Editor > ＋ 文件 。在新建文件对话框中，输入 名称 后单击 确认 。

b. 在刚刚创建的文件中，执行如下代码创建StarRocks表，用于接收导入的OSS数据。

--- ********************************************************************** ------ 初始化：创建StarRocks表，用于接收导入的OSS数据。--- ********************************************************************** ----- 创建用户画像(user_profile) 与 用户行为表(user_event)CREATE DATABASE IF NOT EXISTS game_db;use game_db;-- DROP TABLE IF Exists openlake_win.sr_etl_db.user_profile;--用户信息表CREATE TABLE IF NOT EXISTS ods_user_profile (    user_id INT NOT NULL,    registration_date DATE NOT NULL,    last_login_date DATE,    age_group VARCHAR(20),    gender VARCHAR(10),    location VARCHAR(50),    game_hours INT,    favorite_game_mode VARCHAR(20),    play_frequency VARCHAR(20),    device_type VARCHAR(20),    os_version VARCHAR(20),    current_level INT,    total_deaths INT,    active_time VARCHAR(20),    language_preference VARCHAR(10)) PRIMARY KEY (user_id)DISTRIBUTED BY HASH(user_id)PROPERTIES (    "replication_num" = "1");
-- 用户事件表-- DROP TABLE IF Exists openlake_win.sr_etl_db.user_event;CREATE TABLE IF NOT EXISTS ods_user_event (    `user_id` INT,    `event_type` STRING,    `timestamp` datetime,    `location` STRING,    `level` INT,    `event_details` STRING)DISTRIBUTED BY HASH(user_id)PROPERTIES (    "replication_num" = "1");
-- 清空之前数据truncate table game_db.ods_user_profile;truncate table game_db.ods_user_event;

重要： 本SQL会以杭州为例，需将${REGION}替换为cn-hangzhou。如果是其他Reigon请将代码中${REGION}部分替换为相应地域。

--- ********************************************************************** ------ 使用Broker Load 将OSS数据导入数据到StarRocks表中--- ********************************************************************** ---use game_db;--导入新的数据LOAD LABEL game_db.user_profile_20240902_22(    DATA INFILE("oss://emr-starrocks-benchmark-resource-${REGION}/sr_game_demo/user_profile/*")    INTO TABLE ods_user_profile    FORMAT AS "parquet")WITH BROKER(    "fs.oss.endpoint" = "oss-${REGION}-internal.aliyuncs.com")PROPERTIES(    "timeout" = "3600");
LOAD LABEL game_db.user_event_20240902_22(    DATA INFILE("oss://emr-starrocks-benchmark-resource-${REGION}/sr_game_demo/user_event/*")    INTO TABLE ods_user_event    FORMAT AS "parquet")WITH BROKER(    "fs.oss.endpoint" = "oss-${REGION}-internal.aliyuncs.com")PROPERTIES(    "timeout" = "3600");
/**     -- 导入完成后,如果要查看导入进度,可以到StarRocks控制台的Manager中连接数据库后查看.    -- 控制台地址: https://emr-next.console.aliyun.com/olap-dm
    -- 问题处理    -- 如出现 Unexpected exception: Label [user_profile_01] has already been used.   则按照错误提示,修改load任务的名称为新的名称即可。**/

d. 单击左侧导航栏 导入任务 > Broker Load ，查看任务 状态 和 进度 。

e. 执行如下命令，校验写入数据是否成功。执行此命令前需等待数据导入任务完成，数据导入一般需要1-3分钟左右。

use game_db;select * from (    select     count(1),'ods_user_event' as tb    from      ods_user_eventunion allselect     count(1),'ods_user_profile' as tb    from      ods_user_profile) t

查询结果如图所示。

2. EMR-StarRocks物化视图，自动构建DWD-ADS

1. 执行如下命令，创建DWD层EMR-StarRocks物化视图，每隔1小时刷新一次。

--- ********************************************************************** ------ 使用StarRocks物化视图，自动化构建数据仓库DWD层--- 说明：此处为了简化逻辑，仅直接将ODS层数据直接插入DWD，实际情况应该有更多业务逻辑需要处理。--- ********************************************************************** ---use game_db;DROP MATERIALIZED VIEW IF EXISTS dwd_mv_user_profile;CREATE MATERIALIZED VIEW IF NOT EXISTS dwd_mv_user_profileDISTRIBUTED BY RANDOMREFRESH ASYNC EVERY(INTERVAL 1 HOUR) -- 每隔小时刷新一次ASSELECT  *  FROM ods_user_profile;
DROP MATERIALIZED VIEW IF EXISTS dwd_mv_user_event;CREATE MATERIALIZED VIEW IF NOT EXISTS dwd_mv_user_eventDISTRIBUTED BY RANDOMREFRESH ASYNC EVERY(INTERVAL 1 HOUR) -- 每隔小时刷新一次ASSELECT  *  FROM ods_user_event;

-- 校验上面步骤的数据加工结果 -- use game_db;select * from (    select     count(1),'dwd_mv_user_profile' as tb    from      dwd_mv_user_profileunion allselect     count(1),'dwd_mv_user_event' as tb    from      dwd_mv_user_event) t

校验结果如下。

--- ********************************************************************** ------ 使用StarRocks物化视图,自动化构建数据仓库ADS层--- ********************************************************************** ---use game_db;--1. 创建ADS_MV_USER_RETENTION (用户留存率)CREATE MATERIALIZED VIEW IF NOT EXISTS ADS_MV_USER_RETENTIONDISTRIBUTED BY RANDOMREFRESH ASYNC EVERY(INTERVAL 1 HOUR)ASSELECT    DATE_TRUNC('day', registration_date) AS registration_day,    DATE_TRUNC('day', last_login_date) AS last_login_day,    COUNT(DISTINCT user_id) AS users_retainedFROM dwd_mv_user_profileGROUP BY    DATE_TRUNC('day', registration_date),    DATE_TRUNC('day', last_login_date);
-- 2. ADS_MV_USER_GEOGRAPHIC_DISTRIBUTION (用户地理分布)CREATE MATERIALIZED VIEW IF NOT EXISTS ADS_MV_USER_GEOGRAPHIC_DISTRIBUTIONDISTRIBUTED BY RANDOMREFRESH ASYNC EVERY(INTERVAL 1 HOUR)ASSELECT    location AS geographic_location,    COUNT(DISTINCT user_id) AS total_usersFROM dwd_mv_user_profileGROUP BY    location;
-- 3. ADS_MV_USER_GEOGRAPHIC_DISTRIBUTION (设备使用习惯)CREATE MATERIALIZED VIEW IF NOT EXISTS ADS_MV_USER_DEVICE_PREFERENCEDISTRIBUTED BY RANDOMREFRESH ASYNC EVERY(INTERVAL 1 HOUR)ASSELECT    device_type,    COUNT(DISTINCT user_id) AS total_usersFROM dwd_mv_user_profileGROUP BY    device_type;
-- 4. ADS_MV_USER_PURCHASE_TRENDS (用户购买趋势)-- 该视图用于分析玩家每天的购买趋势变化CREATE MATERIALIZED VIEW IF NOT EXISTS ADS_MV_USER_PURCHASE_TRENDSDISTRIBUTED BY RANDOMREFRESH ASYNC EVERY(INTERVAL 1 HOUR)ASSELECT    DATE(timestamp) AS purchase_date,    COUNT(user_id) AS daily_purchase_eventsFROM dwd_mv_user_eventWHERE event_type = '购买'GROUP BY    purchase_dateORDER BY    purchase_date;

执行如下命令，校验上面步骤的数据加工结果。

-- 校验上面步骤的数据加工结果 -- use game_db;