前言

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

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个环节构建一个性能与成本均衡的游戏玩家画像和行为分析平台。

1. 将OSS中的数据导入StarRocks中；

2. 在StarRocks内表对玩家数据进行即席分析；

3. 使用StarRocks的物化视图对玩家数据进行数仓分层建模；

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

实践教程

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

a. 创建专有网络与交换机。

b. 前往EMR-StarRocks 管控台：

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

数据准备

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;

c. 执行如下代码，将OSS数据导入StarRocks中。

重要：本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_event
union all
select
     count(1),'ods_user_profile' as tb
    from
      ods_user_profile
) t

查询结果如图所示。

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

执行如下命令，创建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_profile
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 HOUR) -- 每隔小时刷新一次
AS
SELECT  *  FROM ods_user_profile;

DROP MATERIALIZED VIEW IF EXISTS dwd_mv_user_event;
CREATE MATERIALIZED VIEW IF NOT EXISTS dwd_mv_user_event
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 HOUR) -- 每隔小时刷新一次
AS
SELECT  *  FROM ods_user_event;

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

校验结果如下。

c. 执行如下命令，使用EMR-StarRocks物化视图，自动化构建ADS。


--- ********************************************************************** ---
--- 使用StarRocks物化视图,自动化构建数据仓库ADS层
--- ********************************************************************** ---
use game_db;
--1. 创建ADS_MV_USER_RETENTION (用户留存率)
CREATE MATERIALIZED VIEW IF NOT EXISTS ADS_MV_USER_RETENTION
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT
    DATE_TRUNC('day', registration_date) AS registration_day,
    DATE_TRUNC('day', last_login_date) AS last_login_day,
    COUNT(DISTINCT user_id) AS users_retained
FROM dwd_mv_user_profile
GROUP 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_DISTRIBUTION
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT
    location AS geographic_location,
    COUNT(DISTINCT user_id) AS total_users
FROM dwd_mv_user_profile
GROUP BY
    location;

-- 3. ADS_MV_USER_GEOGRAPHIC_DISTRIBUTION (设备使用习惯)
CREATE MATERIALIZED VIEW IF NOT EXISTS ADS_MV_USER_DEVICE_PREFERENCE
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT
    device_type,
    COUNT(DISTINCT user_id) AS total_users
FROM dwd_mv_user_profile
GROUP BY
    device_type;

-- 4. ADS_MV_USER_PURCHASE_TRENDS (用户购买趋势)
-- 该视图用于分析玩家每天的购买趋势变化
CREATE MATERIALIZED VIEW IF NOT EXISTS ADS_MV_USER_PURCHASE_TRENDS
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT
    DATE(timestamp) AS purchase_date,
    COUNT(user_id) AS daily_purchase_events
FROM dwd_mv_user_event
WHERE event_type = '购买'
GROUP BY
    purchase_date
ORDER BY
    purchase_date;

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

-- 校验上面步骤的数据加工结果 -- 
use game_db;
select * from (
    select
     count(1),'ADS_MV_USER_RETENTION' as tb
    from
      ADS_MV_USER_RETENTION
union all
select
     count(1),'ADS_MV_USER_GEOGRAPHIC_DISTRIBUTION' as tb
    from
      ADS_MV_USER_GEOGRAPHIC_DISTRIBUTION
union all
select
     count(1),'ADS_MV_USER_DEVICE_PREFERENCE' as tb
    from
      ADS_MV_USER_DEVICE_PREFERENCE
union all
select
     count(1),'ADS_MV_USER_PURCHASE_TRENDS' as tb
    from
      ADS_MV_USER_PURCHASE_TRENDS
) t

校验结果如下。

3. 创建RAM用户与授权

准备阿里云RAM账号。
为RAM账号添加EMR StarRocks用户权限。
在添加用户对话框中，完成用户来源、用户名、密码、用户类型等参数填写后，单击确定。

4. 写入查询Paimon数据

开通DLF2.0。进入数据湖构建DLF2.0控制台，根据页面引导开通DLF2.0。
单击左侧导航栏数据目录 > 新建Catalogs。（本场景示例地域为杭州）
在数据湖构建页面输入Catalog名称后，单击创建Catalog。
单击刚刚创建的Catalog名称，选择权限 > 授权，并完成基本信息和权限参数以添加RAM账号授权。
新建EMR-StarRocks RAM账号连接。
写入数据湖中（Paimon格式）。

${DLF_CATALOG_ID}需要替换为刚刚创建的Catalogs ID。

--- ********************************************************************** ---
--- 写入数据湖中（Paimon格式）- 初始化Catalog信息
--- ********************************************************************** ---
-- 1. 在StarRocks中创建Catalog
-- MyFirstCatalog可以根据您的实际情况调整。
-- DROP CATALOG `myfirstcatalog`;
CREATE EXTERNAL CATALOG `myfirstcatalog`
PROPERTIES (
    "type"  =  "paimon",
    "paimon.catalog.type"  =  "dlf-paimon",
    "dlf.catalog.instance.id"  = "${DLF_CATALOG_ID}"
);

执行如下命令，将StarRocks数据写入Paimon。

--- ********************************************************************** ---
--- 写入数据湖中（Paimon格式）
--- ********************************************************************** ---
create database if not exists myfirstcatalog.game_db;
CREATE TABLE IF NOT EXISTS myfirstcatalog.game_db.ADS_USER_PURCHASE_TRENDS(
    purchase_date DATE COMMENT '购买日期',
    daily_purchase_events INT COMMENT '每日购买事件数量'
);

-- ADS：ETL加工数据
INSERT
    INTO myfirstcatalog.game_db.ADS_USER_PURCHASE_TRENDS
SELECT * from default_catalog.game_db.ADS_MV_USER_PURCHASE_TRENDS;

执行如下命令，通过StarRocks查询ADS数据。

-- 校验写入数据湖中的数据情况
select
     count(1),'myfirstcatalog.game_db.ADS_USER_PURCHASE_TRENDS' as tb
    from
      myfirstcatalog.game_db.ADS_USER_PURCHASE_TRENDS

重要：在完成实验后，如果无需继续使用资源，请根据以下步骤，先删除相关资源后，再结束实操，否则资源会持续运行产生费用。

来源 | 阿里云开发者公众号

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

前言

实践教程

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

前言

实践教程

热门文章

最新文章

相关课程

相关电子书

相关实验场景