一站式大数据业务开发

本实践通过数据湖工厂（DLF）和数据湖探索（DLI）服务对电影评分原始数据进行分析，输出评分最高和最活跃Top20电影。用户可以学习到DLF脚本编辑、作业编辑、作业调度等功能，以及DLI的SQL基本语法。

操作流程如下：

1. 准备原始数据，并上传到OBS中。

2. 创建DLF到DLI的数据连接，之后用户可以在DLF界面中操作DLI，例如：创建数据库、创建数据表、分析数据。

3. 创建DLI SQL脚本，通过DLI SQL脚本可以创建数据表、分析数据。

4. 创建DLF作业，通过编排作业和配置作业调度策略，定期执行DLI SQL脚本，使得用户可以每天获取到最新的Top20电影结果。

环境准备

• 已开通对象存储服务（OBS），并创建桶，例如“s3a://obs-movies”，用于存放原始数据和分析结果数据。

• 已开通数据湖探索服务（DLI）。

数据准备

1. 获取原始数据（演示数据来自：https://grouplens.org/datasets/movielens/），并保存为csv格式的文件。原始数据说明如下：

● movies.csv
   保存电影的基本信息，包含电影ID、名称、类型。部分数据如表1所示。

表1 movies.csv部分数据

movieId	title	genres
1	Toy Story (1995)	Adventure|Animation|Children|Comedy|Fantasy
2	Jumanji (1995)	Adventure|Children|Fantasy
3	Grumpier Old Men (1995)	Comedy|Romance
4	Waiting to Exhale (1995)	Comedy|Drama|Romance
5	Father of the Bride Part II (1995)	Comedy

● ratings.csv
   保存电影评分，包含用户ID、电影ID、评分（0~5）、评分时间。部分数据如表2所示。 

表2 ratings.csv部分数据

userId	movieId	rating	timestamp
1	31	2.5	1260759144
1	1029	3	1260759179
1	1061	3	1260759182
1	1129	2	1260759185
1	1172	4	1260759205

2. 将movies.csv、ratings.csv两个原始数据上传至OBS桶，例如“s3a://obs-movies”，后续DLF和DLI将直接对OBS桶中的数据进行处理。

创建DLI数据连接、数据库、数据表

用户可以通过DLI或DLF的编辑器执行SQL来创建数据表，本文以使用DLF编辑器为例。

1. 创建一个DLF到DLI的连接，数据连接名称为“dli”。

   
   

   图1 创建数据连接
   
   

   
   

2. 参见图2创建一个数据库，用于存放数据表，数据库名称为“movies”。

   
   

   图2 创建数据库
   
   

   
   

创建一个DLI SQL脚本，通过SQL语句来创建数据表。

其中，movie、rating为OBS表，数据存储在OBS中，两张表用于存放原始数据。top_rating_movie、top_active_movie为DLI表，两张表用于存放分析结果。

图3 创建数据表

关键操作说明：

• 图3中的脚本开发区为临时调试区，关闭脚本页签后，开发区的内容将丢失。如需保留该SQL脚本，请单击，将脚本保存至指定的目录中。

关键参数说明：

  ●  数据连接：1中创建的DLI数据连接。

  ●  数据库：2中创建的数据库。

  ●  资源队列：使用DLI提供的默认资源队列“default”。

  ●  SQL语句：如下所示。

create table movie(movieId int,title varchar(256),genres varchar(256)) using csv options (path 's3a://obs-movies/movies.csv');
create table rating(userId int,movieId int,rating float,rating_date long) using csv options (path 's3a://obs-movies/ratings.csv');
create table top_rating_movie(title varchar(256),avg_rating float,rating_user_number int);
create table top_active_movie(title varchar(256),avg_rating float,rating_user_number int);

创建DLI SQL脚本top_rating_movie（用于存放评分最高的Top20电影）

评分最高Top20电影的计算方法是：先计算出每部电影的总评分和参与评分的用户数，过滤掉参与评分的用户数小于100的记录，返回电影名称、平均评分和参与评分用户数。

1. 创建和开发一个DLI SQL脚本，从movie和rating表中计算出评分最高的Top20电影，将结果存放到top_rating_movie表。

   
   

   图4 脚本（评分最高Top20电影）
   
   

   关键参数说明：

• 数据连接：1中创建的DLI数据连接。

• 数据库：2中创建的数据库。

• 资源队列：使用DLI提供的默认资源队列“default”。

• SQL语句：如下所示。

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

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

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

3. 
   

   脚本调试无误后，我们需要保存该脚本，脚本名称为“top_rating_movie”。在后续DLF作业开发和作业调度引用该脚本。
   

创建DLI SQL脚本top_active_movie（用于存放最活跃的Top20电影）

最活跃Top20电影的计算方法是：平均评分大于3.5中用户评分数最多的20部电影。

1. 创建和开发一个DLI SQL脚本，从movie和rating表中计算出最活跃的Top20电影，将结果存放到top_active_movie表。

   
   

   图5 脚本（分析最活跃Top20电影）
   
   

   关键参数说明：

• 数据连接：1中创建的DLI数据连接。

• 数据库：2中创建的数据库。

• 资源队列：使用DLI提供的默认资源队列“default”。

• SQL语句：如下所示。

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

3. 脚本调试无误后，我们需要保存该脚本，脚本名称为“top_active_movie”。在后续DLF作业开发和作业调度引用该脚本。

DLF作业开发和作业调度

假设“movie”和“rating”表是实时变动的，我们希望每天更新Top20电影，那么这里可以使用DLF作业编排和作业调度功能。

1. 创建一个DLF空作业，作业名称为“topmovie”。

   
   

   图6 创建作业
   
   

   
   

2. 然后进入到作业开发页面，拖动Dummy和DLI SQL节点到画布中，连接并配置节点的属性。

   
   

   图7 连接和配置节点属性
   
   

   关键说明：

• Begin（Dummy节点）：不执行任何操作，只作为起始点的标识。

• top_rating_movie（DLI SQL节点）：在节点属性中，关联创建DLI SQL脚本top_rating_movie（用于存放评分最高的Top20电影）中开发完成的DLI SQL脚本“top_rating_movie”。

• top_active_movie（DLI SQL节点）：在节点属性中，关联创建DLI SQL脚本top_active_movie（用于存放最活跃的Top20电影）中开发完成的DLI SQL脚本“top_active_movie”。

3. 作业编排完成后，单击，测试运行作业。

4. 如果日志运行正常，单击画布空白处，在右侧的“调度配置”页面，配置作业的调度策略。

   
   

   图8 调度配置
   
   

   说明：

• 2019/05/07至2019/05/17，每天0点执行一次作业。

5. 最后我们需要保存作业（单击），并执行调度作业（单击）。实现作业每天自动运行，Top20电影的结果自动保存到“top_active_movie”和“top_rating_movie”表。

监控作业执行情况

用户如果需要及时了解作业的执行结果是成功还是失败，可以通过DLF的监控界面、邮件通知、短信通知进行了解。以下展示如何进入监控界面查看执行结果。

图9 查看作业执行情况