在ModelArts Notebook中连接DLI Spark集群进行电商BI报表分析

• 一、背景介绍
• 二、Notebook实践
  • 1.环境准备
    • a.创建demo数据库
    • b.创建OBS桶和对应路径
  • 2.基于ma-spark魔法命令连接DLI Spark集群
  • 3.电商数据BI分析
    • a.创建OBS表
    • b.将数据导入OBS表
    • c.简单的SQL数据分析
    • d.将数据保存至本地

一、背景介绍

本样例中，我们将介绍如何基于notebook连接DLI集群分析电商行业中用户对商品的评价行为。
具体数据来源见使用DLI进行电商BI报表分析， 请下载该数据并拖放解压置notebook中的/home/ma-user/work目录下。

ma-spark魔法命令
%reload_ext ma_magic : 加载ModelArts内置的自研魔法命令ma_magic

%ma_spark query cluster : 使用ma_spark魔法命令查询已注册的DLI队列，执行该命令之前需要先加载ma_magic命令

%ma_spark connect --cluster_name --ak --sk : 连接指定队列，执行该命令之前需要先加载ma_magic命令

sparkmagic魔法命令

sparkmagic魔法命令仅在Sparkmagic(DLI-PySpark)类型的Kernel中可用，详情见sparkmagic

%%local : 当前cell中的代码在notebook本地执行。

%%sql : 在当前cell中仅可执行sql命令，并将提交到DLI Spark集群中执行

sparkmagic其余魔法命令可通过 %%help 查看，若不加 %% 默认支持spark语法且会将代码提交到DLI远程Spark集群执行，执行完成后会将执行结果返回至notebook进行展示。

二、Notebook实践

本样例代码结构如下

• 1 环境准备
  • a. 创建数据库
  • b. 创建OBS桶和路径
• 2 基于ma-spark魔法命令连接DLI Spark集群
• 3 电商BI数据分析
  • a. 连接DLI集群
  • b. 创建OBS表
  • c. 将数据导入OBS表
  • d. 简单的SQL数据分析
  • e. 将查询结果保存至本地

1. 环境准备

a. 创建demo数据库

在notebook中无法执行创建数据库的命令，需到DLI管理界面手动创建数据库, 详情见DLI-创建数据库和表

b. 创建OBS桶和对应路径

创建OBS表之前必须提前在OBS上创建桶和空的文件夹。请创建名为 dli-test-demo 的OBS桶，详情见OBS-创建桶

创建OBS桶后，可以手动或者使用ModelArts SDK创建OBS文件夹，如下所示

%%local
from modelarts.session import Session
sess = Session()
dir_name_list = ['JData_User', 'JData_Product', 'JData_Comment', 'JData_Action', 'result']
for dir_name in dir_name_list:
    sess.obs.make_obs_dir(f'obs://dli-test-demo/{dir_name}')

2. 基于ma-spark魔法命令连接DLI Spark集群

在同一个实例中开启多个jupyter notebook执行远程Spark作业时，仅需连接DLI spark集群；
在Sparkmagic(DLI-PySpark)类型的Kernel中连接DLI/MRS集群，需要添加 %%local 命令；
在PySpark和python类型的kernel中连接DLI/MRS集群，请去除 %%local 命令；

查询已注册的队列

%%local
%reload_ext ma_magic
%ma_spark query cluster

连接队列

连接成功后，请先执行restart kernel，然后再执行spark相关操作

%%local
%reload_ext ma_magic
%ma_spark connect --cluster_name <dli-queue-name-placeholder> --ak <access-key-placeholder> --sk <secret-access-key-placeholder>

3. 电商数据BI分析

a. 创建OBS表

创建用户表

%%sql
create table if not exists demo.user(
  user_id int,
  age int,
  sexuality int,
  rank int,
  register_time string
) USING csv OPTIONS (path "obs://dli-test-demo/JData_User")

创建商品表

%%sql
create table if not exists demo.product(
  product_id int,
  a1 int,
  a2 int,
  a3 int,
  category int,
  brand int
) USING csv OPTIONS (path "obs://dli-test-demo/JData_Product")

创建评论表

%%sql
create table if not exists demo.comment(
  deadline string,
  product_id int,
  comment_num int,
  has_bad_comment int,
  bad_comment_rate float
) USING csv OPTIONS (path "obs://dli-test-demo/JData_Comment")

创建行为表

%%sql
create table if not exists demo.action(
  user_id int,
  product_id int,
  time string,
  model_id string,
  type string
) USING csv OPTIONS (path "obs://dli-test-demo/JData_Action")

b. 将数据导入OBS表

创建OBS表要求OBS路径为空，因此先创建OBS表，再上传数据至OBS路径中

将数据导入上述对应的OBS路劲中，如果数据不在notebook中，则跳过该步骤

%%local
from modelarts.session import Session
sess = Session()

sess.obs.copy('/home/ma-user/work/display/Best_Practice_4/data/JData_Product',
              'obs://dli-test-demo/JData_Product', keep_last_dir=False)
sess.obs.copy('/home/ma-user/work/display/Best_Practice_4/data/JData_Action',
              'obs://dli-test-demo/JData_Action', keep_last_dir=False)
sess.obs.copy('/home/ma-user/work/display/Best_Practice_4/data/JData_Comment',
              'obs://dli-test-demo/JData_Comment', keep_last_dir=False)
sess.obs.copy('/home/ma-user/work/display/Best_Practice_4/data/JData_User',
              'obs://dli-test-demo/JData_User', keep_last_dir=False)

c. 简单的SQL数据分析

指定使用的数据库demo, 此处USE database命令必须单独在一个cell中执行，不能和select等语句一起执行

%%sql
USE demo

查询用户点赞最多的十件商品

%%sql
SELECT
  product.brand as brand,
  COUNT(product.brand) as like_count
from
  action
  JOIN product ON (action.product_id = product.product_id)
WHERE
  action.type = 'like'
group by
  brand
ORDER BY like_count desc
limit
  10

查询用户评级最差的十件商品并写入新的数据表中

%%sql
CREATE TABLE IF NOT EXISTS result
(
   unique_product_id STRING,
   comment_num INT,
   bad_comment_rate FLOAT
)
STORED AS PARQUET
LOCATION "obs://dli-test-demo/result"

%%sql
INSERT INTO TABLE result
SELECT
  DISTINCT product_id,
  comment_num,
  bad_comment_rate 
from 
  comment 
where 
  comment_num > 3 
order by 
  bad_comment_rate desc 
limit
  10

d. 将数据保存至本地

通过魔法命令 %%sql 将其保存为名为df_local且类型为pandas.DataFrame的notebook本地变量，可在cell中执行 %%help 查看%%sql命令详情

%%sql -o df_local
SELECT * FROM result

%%local
df_local.head()
df_local.to_csv('df_local.csv', index=False)

三、附件

可执行ipynb文件见附件