基于EMR StarRocks实现游戏玩家画像和行为分析_云起实验室-阿里云开发者社区

场景简介

基于阿里云的EMR Serverless StarRocks，使用StarRocks的物化视图、通过DLF读写Paimon等最新能力，构建一个游戏玩家画像和行为分析平台。该案例通过收集玩家的行为日志，进行处理和分析，并最终将分析结果通过报表的形式展现给业务人员。

背景知识

本场景主要涉及以下云产品和服务：

开源大数据平台E-MapReduce
开源大数据开发平台E-MapReduce（简称EMR）是运行在阿里云平台上的一种大数据处理系统解决方案。EMR基于开源的Apache Hadoop和Apache Spark，让您可以方便地使用Hadoop和Spark生态系统中的其他周边系统分析和处理数据。EMR还可以与阿里云其他的云数据存储系统和数据库系统（例如，阿里云OSS和RDS等）进行数据传输。
数据湖构建Data Lake Formation
数据湖构建（Data Lake Formation，DLF）作为云原生数据湖架构核心组成部分，帮助用户快速地构建云原生数据湖架构。数据湖构建提供湖上元数据统一管理、企业级权限控制，并无缝对接多种计算引擎，打破数据孤岛，洞察业务价值。
智能商业分析 Quick BI
Quick BI是一款全场景数据消费式的BI平台，秉承全场景消费数据，让业务决策触手可及的使命，通过智能的数据分析和可视化能力帮助企业构建数据分析系统，您可以使用Quick BI制作漂亮的仪表板、格式复杂的电子表格、酷炫的大屏、有分析思路的数据门户，也可以将报表集成在您的业务流程中，并且通过邮件、钉钉、企业微信等分享给您的同事和合作伙伴。

数据架构图

前提条件

云起实验室将在您的账号下开通本次实操资源，资源按量付费，需要您自行承担本次实操的云资源费用。

重要

如果您领取的是E-MapReduce Serverless StarRocks免费试用实例和DLF 2.0则本次实验无费用产出（不包括Quick BI相关计费）。如果您调整了资源规格、使用时长，或执行了本方案以外的操作，可能导致费用发生变化，请以控制台显示的实际价格和最终账单为准。

进入实操前，请确保阿里云账号满足以下条件：

已通过实名认证并且账户余额充足。
云资源产生的费用需您自行承担，云起实验室不会向您征收额外费用。
所有实验操作将保留至您的账号，请谨慎操作。
实操结束后，您可以选择继续付费保留资源，或参考手册自动/手动释放资源。

创建专有网络VPC和交换机

在实验室页面右侧的操作区中，勾选我已阅读并同意《阿里云云起实践平台服务协议》，单击进入实操。

专有网络与交换机创建。

前往专有网络控制台。
在左侧导航栏中，单击专有网络。
在专有网络页⾯，在右上角切换⾄华东1（杭州）地域，单击创建专有网络。

在创建专有网络页⾯，根据下方参数说明配置1个专有网络（VPC）和1台交换机，然后单击确定。更多关于创建专有网络和交换机信息，详情请参见创建和管理专有网络。

说明

专有网络的可用区需要与StarRocks可用区一样，这里默认使用杭州可用区I。

	配置项	说明

	配置项	说明
专有网络	地域	选择华东1（杭州）。
	名称	自定义名称。
	IPv4网段	选择手动输入IPv4地址段。
	输入IPv4网段	输入IPv4网段，建议您使用RFC私网地址作为专有网络的网段如`10.0.0.0/8`，`172.16.0.0/12`，`192.168.0.0/16`。
交换机	名称	自定义名称。
	可用区	选择杭州可用区I。
	IPv4网段	使用默认的IPv4网段即可。

Image 21.png

领取E-MapReduce Serverless StarRocks免费试用

重要

如果您的阿里云账号有领取E-MapReduce Serverless StarRocks免费试用资格，请您根据下方操作进行领取。
如果您的阿里云账号已领取过E-MapReduce Serverless StarRocks免费试用资格，请您前往EMR-StarRocks控制台，创建Serverless StarRocks实例，并会产生一定的费用，详情请参见创建实例和按量付费。创建Serverless StarRocks实例时的相关配置，其中实例类型请选择存算一体版（按量付费），实例类型选择标准版，其余配置请参考本小节内容。

前往E-MapReduce Serverless StarRocks免费试用进行领取实例。
说明
- 专有网络的可用区需要与StarRocks可用区一样，这里默认使用杭州可用区I，填写实例名称与用户密码，用于后续创建连接。
- 密码长度必须为8~30个字符，且同时包含（大写字母，小写字母，数字，@#$%^*_+-中的特殊符号）。
前往EMR-StarRocks控制台，单击实例名称。
在实例详情 > 版本信息板块确认版本在3.2.11-1.79-1.6.44之上，若低于此版本，请先进行版本升级。
单击连接实例 > SQL Editor。在新建连接页面，选择地域（本场景示例地域为华东1（杭州）），输入用户名（用户名默认admin）和密码后，单击确定。

数据准备

创建StarRocks表。

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

在刚刚创建的文件中，执行如下代码创建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;

执行如下代码，将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任务的名称为新的名称即可。
**/

单击左侧导航栏导入任务 > Broker Load，查看任务状态和进度。
重要
- 问题处理：如出现 Unexpected exception: Label [user_profile_01] has already been used. 则按照错误提示,修改load任务的名称为新的名称即可。

执行如下命令，校验写入数据是否成功。

说明

执行此命令前需等待数据导入任务完成，数据导入一般需要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

校验结果如下。

执行如下命令，使用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

校验结果如下。

使用EMR-StarRocks实现湖仓极速分析（Paimon格式）

重要

请确认版本在3.2.11-1.79-1.6.44之上，若低于此版本，请先进行实例升级。

准备阿里云RAM账号。
1. 前往RAM控制台，填写好相关参数后，单击确定创建用户。
为RAM账号添加EMR StarRocks用户权限。
1. 返回在EMR StarRocks Manager控制台。单击左侧导航栏安全中心 > 用户管理 > 添加用户。安全中心-用户管理-添加用户，添加刚才创建的RAM用户为超级管理员，记住用户密码。
2. 在添加用户对话框中，根据如下参数填写后，单击确定。
  - 用户来源：RAM用户
  - 用户名：刚刚创建的RAM用户
  - 密码：输入自定义密码
  - 确认密码：输入自定义密码
  - 用户类型：超级管理员

授权EMR-StarRocks用户访问DLF数据湖构建

开通DLF2.0。
1. 进入数据湖构建DLF2.0控制台，根据页面引导开通DLF2.0。
  说明
  本场景所使用的数据湖构建DLF版本为2.0。
单击左侧导航栏数据目录 > 新建Catalogs。（本场景示例地域为杭州）
在数据湖构建页面输入Catalog名称后，单击创建Catalog。
单击刚刚创建的Catalog名称，选择权限 > 授权，根据如下参数添加RAM账号授权。
- 基本信息。
  - 用户/角色：用户；
  - 选择授权用户：刚刚创建的RAM用户；
  - 预置授权类型：Custom；
- 权限。
  - 数据目录：ALL；
  - 数据目录下所有资源：ALL；
新建EMR-StarRocks RAM账号连接。
1. 返回EMR StarRocks Manager控制台，单击新建连接。输入在前面步骤中创建的RAM账号与密码，单击确定。
  说明
  账号密码在《使用EMR-StarRocks实现湖仓极速分析（Paimon格式）》小节步骤2中。
2. 确认RAM账号连接成功后，单击左侧导航栏SQL Editor > ＋文件。在新建文件对话框中，输入名称后单击确认。

写入数据湖中（Paimon格式）。

执行如下命令，初始化Catalog信息。

重要

${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

（可选）通过Quick BI进行报表分析和展示

通过Quick BI可以直接查询StarRocks中最终ADS层的数据，进行报表页面展示。

打开Quick BI控制台：https://bi.aliyun.com/workspace/dashboard。
配置好StarRocks数据源。
设计报表和数据集。
1. 数据集SQL1：select * from game_db.ADS_MV_USER_RETENTION;
2. 数据集SQL2：select * from game_db.ADS_MV_USER_GEOGRAPHIC_DISTRIBUTION;
3. 数据集SQL3：select * from game_db.ADS_MV_USER_DEVICE_PREFERENCE;
4. 数据集SQL4：select * from game_db.ADS_MV_USER_PURCHASE_TRENDS;
预览效果图。

清理资源

在完成实验后，如果无需继续使用资源，请根据以下步骤，先删除相关资源后，再结束实操，否则资源会持续运行产生费用。
- 删除EMR-StarRocks控制台。
  1. 进入EMR-StarRocks控制台。
  2. 在左侧导航栏中，单击StarRocks。找到目标实例，单击右侧的释放按钮。
  3. 在释放集群对话框中，单击确定。
- 删除数据湖构建Catalogs。
  1. 进入数据湖构建DLF2.0控制台。
    说明
    若要删除数据目录需要将其内的数据表及库全部删除。
  2. 单击左侧导航栏数据目录，单击目标名称进入数据库。
  3. 单击数据库选项卡下的game_db，进入数据库资源列表。
  4. 在资源列表选项卡下，找到表右侧的删除按钮，将全部表删除。在弹出的确认对话框中，单击确定。
  5. 返回至数据库页面，单击目标数据库右侧的删除按钮，删除数据库。在弹出的确认对话框中，单击确定。
    重要
    当此数据库中无数据表时，才可删除。
  6. 返回至DLF控制台，单击左侧导航栏数据目录，找到目标名称单击右侧的删除。
  7. 在删除确认对话框中单击确定。
- 删除VPC和交换机。
  1. 登录专有网络管理控制台。
  2. 在左侧导航栏中，单击交换机。
  3. 单击交换机右侧的删除按钮。
  4. 在弹出的删除交换机对话框中，单击确定。
  5. 在左侧导航栏中，单击专有网络。
  6. 单击VPC右侧删除按钮。
  7. 在删除专有网络对话框中，单击确定。
- 删除RAM用户。
  1. 进入RAM控制台。
  2. 单击左侧导航栏身份管理 > 用户，进入用户页面。单击目标RAM用户右侧的删除按钮。
  3. 在删除用户对话框中，输入要删除的目标RAM用户名称，单击移入回收站。
- 删除相关资源后，单击结束实操。在结束实操对话框中，单击确定。
在完成实验后，如果需要继续使用资源，您可跳过释放相关资源的操作，直接单击结束实操。在结束实操对话框中，单击确定。请随时关注账户扣费情况，避免发生欠费。

操作手册

【实践】基于EMR StarRocks实现游戏玩家画像和行为分析

场景简介

背景知识

数据架构图

前提条件

创建专有网络VPC和交换机

领取E-MapReduce Serverless StarRocks免费试用

数据准备

使用EMR-StarRocks物化视图，自动化构建数仓DWD-ADS

使用EMR-StarRocks实现湖仓极速分析（Paimon格式）

授权EMR-StarRocks用户访问DLF数据湖构建

（可选）通过Quick BI进行报表分析和展示

清理资源

【实践】基于EMR StarRocks实现游戏玩家画像和行为分析

【实践】基于EMR StarRocks实现游戏玩家画像和行为分析