华为云大数据分析7天训练营（七）使用DGC实现每日动态发布电影排行榜

环境准备：

• 已开通DGC实例，并已具备CDM集群。
• 已开通对象存储服务（OBS），区域和DGC实例相同。
• 已开通数据湖探索服务（DLI），区域和DGC实例相同。

数据源准备：

在OBS中新建OBS桶“dgc-dli”，将movies.csv、ratings.csv两个原始数据上传至桶中，后续通过数据集成模块将数据集成到DLI中进行处理。

原始数据说明如下：

• movies.csv

  保存电影的基本信息，包含电影ID、名称、类型。详细见附件

• ratings.csv

  保存电影评分，包含用户ID、电影ID、评分（0~5）、评分时间。详细见附件

服务列表找到存储obs

打开obs 在obs中新建dgc-dli桶

确定新建信息后点击确认进行创建

等待创建成功

创建成功后将movies.csv、ratings.csv两个原始数据上传至桶中

数据湖准备：

在本示例中，选择数据湖探索（DLI）服务作为数据湖。
首先您需要在DLI中新建SQL队列。在数据迁移作业中需要用到DLI队列，而DLI的default队列无法在迁移作业中使用。

其次您需要准备数据库，后续的数据集成与开发操作均针对数据库和数据表进行。您可以通过DLF或DLI的编辑器执行SQL来创建数据表，本文以使用DLF编辑器为例。
操作步骤如下：
步骤1 进入DGC数据开发主页面，选择“连接管理 -> 新建数据连接”。 

步骤2 创建一个到DLI的连接，数据连接名称为“dli”。

步骤3 在DLI连接上右键单击，创建一个数据库用于存放数据表，数据库名称为“movies”。

步骤4 创建一个DLI SQL脚本，通过SQL语句来创建数据表。
其中，movies_item、rating_item为OBS迁移到DLI的表，两张表用于存放原始数据。top_rating_movie、top_active_movie为DLI表，两张表用于存放分析结果。 

在脚本开发页面下，新建DLI SQL脚本create_dli_table。输入参数与SQL语句后，点击“保存并提交版本”，最后运行脚本。

关键参数说明：
● 数据连接：步骤2中创建的DLI数据连接。
● 数据库：步骤3中创建的数据库。
● 资源队列：使用DLI提供的默认资源队列“default”。
● SQL语句：如下所示。 

DROP TABLE IF EXISTS `movies_item`;
DROP TABLE IF EXISTS `ratings_item`;
DROP TABLE IF EXISTS `top_rating_movie`;
DROP TABLE IF EXISTS `top_active_movie`;

CREATE TABLE IF NOT EXISTS movies_item(  
  `movieId` INT COMMENT '',
  `movieTitle` STRING COMMENT '',
  `videoReleaseDate` STRING COMMENT '',
  `IMDbURL` STRING COMMENT '',
  `unknown` INT COMMENT '',
  `Action` INT COMMENT '',
  `Adventure` INT COMMENT '',
  `Animation` INT COMMENT '',
  `Childrens` INT COMMENT '',
  `Comedy` INT COMMENT '',
  `Crime` INT COMMENT '',
  `Documentary` INT COMMENT '',
  `Drama` INT COMMENT '',
  `Fantasy` INT COMMENT '',
  `Film-Noir` INT COMMENT '',
  `Horror` INT COMMENT '',
  `Musical` INT COMMENT '',
  `Mystery` INT COMMENT '',
  `Romance` INT COMMENT '',
  `Sci-Fi` INT COMMENT '',
  `Thriller` INT COMMENT '',
  `War` INT COMMENT '',
  `Western` INT COMMENT ''
);


CREATE TABLE IF NOT EXISTS ratings_item(  
  `userId` INT COMMENT '',
  `movieId` STRING COMMENT '',
  `rating` INT COMMENT '',
  `timestamp` STRING COMMENT ''
);


CREATE TABLE IF NOT EXISTS top_rating_movie(
  `movieTitle` STRING COMMENT '',
  `avg_rating` float COMMENT '',
  `rating_user_number` int COMMENT ''
); 

CREATE TABLE IF NOT EXISTS top_active_movie(
  `movieTitle` STRING COMMENT '',
  `avg_rating` float COMMENT '',
  `rating_user_number` int COMMENT ''
); 

认证数据准备：

创建访问密钥（AK和SK）。

在创建访问密钥前，请确保登录控制台的帐号已通过实名认证。创建访问密钥（AK和SK）操作步骤如下：

1. 登录华为云控制台，鼠标移动至右上角用户名，在弹出菜单中单击“我的凭证”。
2. 在左侧导航树单击“访问密钥”。在访问密钥列表中，可以查看已有的访问密钥ID（即Access Key ID），但是，Secret Access Key只有在新增访问密钥时所下载的密钥文件中才可以获取到。
3. 单击“新增访问密钥”，然后单击“确定”保存密钥文件。每个用户最多可创建两个有效的访问密钥，如果当前已存在2个访问密钥，只能先删除现有的访问密钥，然后再重新创建。

    4.打开下载下来的“credentials.csv”文件即可获取到访问密钥（Access Key ID和Secret Access Key）。

OBS数据迁移到DLI

1. 进入DGC数据集成主页面，选择“作业管理”。

2.新建CDM到OBS的连接，数据连接名称为“obs_link”。

3.新建CDM到DLI的连接，数据连接名称为“dli_link”。

insert overwrite table top_rating_movie
select
a.movieTitle,
b.ratings / b.rating_user_number as avg_rating,
b.rating_user_number

from
movies_item a,
(
select
movieId,
sum(rating) ratings,
count(1) as rating_user_number

from
ratings_item
group by
movieId
) b
where
rating_user_number > 100
and a.movieId = b.movieId
order by
avg_rating desc
limit
20

insert overwrite table top_active_movie   
select * from 
(
select 
  a.movieTitle, 
  b.ratingSum / b.rating_user_number as avg_rating, 
  b.rating_user_number 
from 
  movies_item a, 
  ( 
    select 
      movieId, 
      sum(rating) ratingSum, 
      count(1) as rating_user_number 
    from 
      ratings_item 
    group by 
      movieId 
  ) b 
where  
  a.movieId = b.movieId 
  ) t
  where 
  t.avg_rating > 3.5 
order by 
  rating_user_number desc 
limit 
  20