文档首页> MapReduce服务 MRS> 最佳实践> 数据分析> 使用Hive加载OBS数据并分析企业雇员信息
更新时间:2022-02-22 GMT+08:00
分享

使用Hive加载OBS数据并分析企业雇员信息

MRS Hadoop分析集群,提供Hive、Spark离线大规模分布式数据存储和计算,进行海量数据分析与查询。

本实践基于华为云MapReduce服务,用于指导您创建MRS集群后,使用Hive对OBS中存储的原始数据进行导入、分析等操作,展示了如何构建弹性、低成本的存算分离大数据分析。

基本内容如下所示:

  1. 创建MRS离线查询集群
  2. 创建OBS委托并绑定至MRS集群
  3. 创建Hive表并加载OBS中数据
  4. 基于HQL对数据进行分析

场景描述

Hive是建立在Hadoop上的数据仓库框架,提供大数据平台批处理计算能力,能够对结构化/半结构化数据进行批量分析汇总完成数据计算。提供类似SQL的Hive Query Language语言操作结构化数据,其基本原理是将HQL语言自动转换成MapReduce任务,从而完成对Hadoop集群中存储的海量数据进行查询和分析。

Hive主要特点如下:

  • 海量结构化数据分析汇总。
  • 将复杂的MapReduce编写任务简化为SQL语句。
  • 灵活的数据存储格式,支持JSON,CSV,TEXTFILE,RCFILE,SEQUENCEFILE,ORC(Optimized Row Columnar)这几种存储格式。

本实践以用户开发一个Hive数据分析应用为例,通过客户端连接Hive后,执行HQL语句访问OBS中的Hive数据。进行企业雇员信息的管理、查询。如果需要基于MRS服务提供的样例代码工程开发构建应用,您可以参考Hive应用开发简介

本实践中,雇员信息的原始数据包含以下两张表:

表1 表1 雇员信息数据

编号

姓名

支付薪水币种

薪水金额

纳税税种

工作地

入职时间

1

Wang

R

8000.01

personal income tax&0.05

China:Shenzhen

2014

3

Tom

D

12000.02

personal income tax&0.09

America:NewYork

2014

4

Jack

D

24000.03

personal income tax&0.09

America:Manhattan

2015

6

Linda

D

36000.04

personal income tax&0.09

America:NewYork

2014

8

Zhang

R

9000.05

personal income tax&0.05

China:Shanghai

2014

表2 雇员联络信息数据

编号

电话

邮箱

1

135 XXXX XXXX

xxxx@xx.com

3

159 XXXX XXXX

xxxxx@xx.com.cn

4

186 XXXX XXXX

xxxx@xx.org

6

189 XXXX XXXX

xxxx@xxx.cn

8

134 XXXX XXXX

xxxx@xxxx.cn

通过数据应用,进行以下分析:

  • 查看薪水支付币种为美元的雇员联系方式。
  • 查询入职时间为2014年的雇员编号、姓名等字段,并将查询结果加载到新表中。
  • 统计雇员信息共有多少条记录。
  • 查询使用以“cn”结尾的邮箱的员工信息。

创建MRS离线查询集群

  1. 登录华为云控制台,选择“大数据 > MapReduce服务”,单击“购买集群”,选择“快速购买”,填写软件配置参数,单击“下一步”。
    表3 软件配置(以下参数仅供参考,可根据实际情况调整)

    参数项

    取值

    区域

    华北-北京四

    计费模式

    按需计费

    集群名称

    MRS_demo

    集群版本

    MRS 3.1.0

    组件选择

    Hadoop分析集群

    可用区

    可用区1

    虚拟私有云

    vpc-01

    子网

    subnet-01

    企业项目

    default

    Kerberos认证

    不开启

    用户名

    root/admin

    密码

    设置密码登录集群管理页面及ECS节点用户的密码,例如:Test!@12345。

    确认密码

    再次输入设置用户密码

    通信安全授权

    勾选“确认授权”

  2. 单击“立即购买”,等待约15分钟,MRS集群创建成功。

创建OBS委托并绑定至MRS集群

  • MRS在IAM的委托列表中预置了MRS_ECS_DEFAULT_AGENCY委托,可在创建自定义过程中可以直接选择该委托,该委托拥有对象存储服务的OBSOperateAccess权限和在集群所在区域拥有CESFullAccess(对开启细粒度策略的用户)、CES Administrator和KMS Administrator权限。
  • 如需使用自定义委托,请参考如下步骤进行创建委托(创建或修改委托需要用户具有Security Administrator权限)。
  1. 登录华为云管理控制台。
  2. 在服务列表中选择“管理与监管 > 统一身份认证服务 IAM”。
  3. 选择“委托 > 创建委托”。
  4. 设置“委托名称”,“委托类型”选择“云服务”,在“云服务”中选择“弹性云服务器ECS 裸金属服务器BMS”,授权ECS或BMS调用OBS服务。
  5. “持续时间”选择“永久”并单击“下一步”。
    图1 创建委托
  6. 在弹出授权页面的搜索框内,搜索“OBS OperateAccess”策略,勾选“OBS OperateAccess”策略。
    图2 配置权限
  7. 单击“下一步”,选择权限范围方案,默认选择“所有资源”,单击“展开其他方案”,选择“全局服务资源”。
  8. 在弹出的提示框中单击“知道了”,开始授权。界面提示“授权成功。”,单击“完成”,委托成功创建。
  9. 返回MRS控制台,在集群列表中,单击已创建好的MRS集群名称,在集群的“概览”页面中,单击“管理委托”,选择创建好的OBS委托后单击“确定”。

创建Hive表并加载OBS中数据

  1. 在服务列表中选择“存储 > 对象存储服务 OBS”,登录OBS控制台,单击“并行文件系统 > 创建并行文件系统”,填写以下参数,单击“立即创建”。
    表4 并行文件系统参数

    参数项

    取值

    区域

    华北-北京四

    文件系统名称

    hiveobs

    数据冗余存储策略

    单AZ存储

    策略

    私有

    归档数据直读

    关闭

    企业项目

    default

    标签

    -

    等待并行文件系统创建成功,单击并行文件系统名称,选择“文件 > 上传文件”,将数据文件上传至OBS桶内。

  2. 下载并安装MRS集群客户端,例如在主Master节点上安装,客户端安装目录为“/opt/client”,相关操作可参考安装客户端

    也可直接使用Master节点中自带的集群客户端,安装目录为“/opt/Bigdata/client”。

  3. 为主Master节点绑定一个弹性IP并在安全组中放通22端口,然后使用root用户登录主Master节点,进入客户端所在目录并加载变量。

    cd /opt/client

    source bigdata_env

  4. 执行beeline命令进入Hive Beeline命令行界面。

    执行以下命令创建一个与原始数据字段匹配的雇员信息数据表“employees_info”:

    create external table if not exists employees_info

    (

    id INT,

    name STRING,

    usd_flag STRING,

    salary DOUBLE,

    deductions MAP<STRING, DOUBLE>,

    address STRING,

    entrytime STRING

    )

    row format delimited fields terminated by ',' map keys terminated by '&'

    stored as textfile

    location 'obs://hiveobs/employees_info';

    执行以下命令创建一个与原始数据字段匹配的雇员联系信息数据表“employees_contact”:

    create external table if not exists employees_contact

    (

    id INT,

    phone STRING,

    email STRING

    )

    row format delimited fields terminated by ','

    stored as textfile

    location 'obs://hiveobs/employees_contact';

  5. 查看表是否创建成功:

    show tables;

    +--------------------------+
    |         tab_name         |
    +--------------------------+
    | employees_contact        |
    | employees_info           |
    +--------------------------+
  6. 将数据导入OBS对应表目录下。

    Hive内部表会默认在指定的存储空间中建立对应文件夹,只要把文件放入,表就可以读取到数据(需要和表结构匹配)。

    登录OBS控制台,在已创建的文件系统的“文件”页面,将本地的原始数据分别上传至生成的“employees_info”、“employees_contact”文件夹下。

    例如原始数据格式如下:

    info.txt:

    1,Wang,R,8000.01,personal income tax&0.05,China:Shenzhen,2014
    3,Tom,D,12000.02,personal income tax&0.09,America:NewYork,2014
    4,Jack,D,24000.03,personal income tax&0.09,America:Manhattan,2015
    6,Linda,D,36000.04,personal income tax&0.09,America:NewYork,2014
    8,Zhang,R,9000.05,personal income tax&0.05,China:Shanghai,2014

    contact.txt:

    1,135 XXXX XXXX,xxxx@xx.com
    3,159 XXXX XXXX,xxxx@xx.com.cn
    4,189 XXXX XXXX,xxxx@xx.org
    6,189 XXXX XXXX,xxxx@xx.cn
    8,134 XXXX XXXX,xxxx@xxx.cn
  7. 在Hive Beeline客户端中,执行以下命令,查询源数据是否被正确加载。
    select * from employees_info;
    +--------------------+----------------------+--------------------------+------------------------+-------------------------------+-------------------------+---------------------------+
    | employees_info.id  | employees_info.name  | employees_info.usd_flag  | employees_info.salary  |   employees_info.deductions   | employees_info.address  | employees_info.entrytime  |
    +--------------------+----------------------+--------------------------+------------------------+-------------------------------+-------------------------+---------------------------+
    | 1                  | Wang                 | R                        | 8000.01                | {"personal income tax":0.05}  | China:Shenzhen          | 2014                      |
    | 3                  | Tom                  | D                        | 12000.02               | {"personal income tax":0.09}  | America:NewYork         | 2014                      |
    | 4                  | Jack                 | D                        | 24000.03               | {"personal income tax":0.09}  | America:Manhattan       | 2015                      |
    | 6                  | Linda                | D                        | 36000.04               | {"personal income tax":0.09}  | America:NewYork         | 2014                      |
    | 8                  | Zhang                | R                        | 9000.05                | {"personal income tax":0.05}  | China:Shanghai          | 2014                      |
    +--------------------+----------------------+--------------------------+------------------------+-------------------------------+-------------------------+---------------------------+

    select * from employees_contact;

    +-----------------------+--------------------------+--------------------------+
    | employees_contact.id  | employees_contact.phone  | employees_contact.email  |
    +-----------------------+--------------------------+--------------------------+
    | 1                     | 135 XXXX XXXX            | xxxx@xx.com              |
    | 3                     | 159 XXXX XXXX            | xxxx@xx.com.cn           |
    | 4                     | 186 XXXX XXXX            | xxxx@xx.org              |
    | 6                     | 189 XXXX XXXX            | xxxx@xx.cn               |
    | 8                     | 134 XXXX XXXX            | xxxx@xxx.cn              |
    +-----------------------+--------------------------+--------------------------+

基于HQL对数据进行分析

在Hive Beeline客户端中,执行HQL语句,对原始数据进行分析。
  1. 查看薪水支付币种为美元的雇员联系方式。

    创建新数据表进行数据清洗。

    create table employees_info_v2 as select id, name, regexp_replace(usd_flag, '\s+','') as usd_flag, salary, deductions, address, entrytime from employees_info;

    等待Map任务完成后,执行以下命令

    select a.* from employees_info_v2 a inner join employees_contact b on a.id = b.id where a.usd_flag='D';

    INFO  : MapReduce Jobs Launched: 
    INFO  : Stage-Stage-3: Map: 1   Cumulative CPU: 2.95 sec   HDFS Read: 8483 HDFS Write: 317 SUCCESS
    INFO  : Total MapReduce CPU Time Spent: 2 seconds 950 msec
    INFO  : Completed executing command(queryId=omm_20211022162303_c26d4f1b-a577-4d6c-919c-6cb96095b24b); Time taken: 26.259 seconds
    INFO  : OK
    INFO  : Concurrency mode is disabled, not creating a lock manager
    +-------+---------+-------------+-----------+-------------------------------+--------------------+--------------+
    | a.id  | a.name  | a.usd_flag  | a.salary  |         a.deductions          |     a.address      | a.entrytime  |
    +-------+---------+-------------+-----------+-------------------------------+--------------------+--------------+
    | 3     | Tom     | D           | 12000.02  | {"personal income tax":0.09}  | America:NewYork    | 2014         |
    | 4     | Jack    | D           | 24000.03  | {"personal income tax":0.09}  | America:Manhattan  | 2015         |
    | 6     | Linda   | D           | 36000.04  | {"personal income tax":0.09}  | America:NewYork    | 2014         |
    +-------+---------+-------------+-----------+-------------------------------+--------------------+--------------+
    3 rows selected (26.439 seconds)
  2. 查询入职时间为2014年的雇员编号、姓名等字段,并将查询结果加载进表employees_info_extended中的入职时间为2014的分区中。

    创建一个表:

    create table if not exists employees_info_extended (id int, name string, usd_flag string, salary double, deductions map<string, double>, address string) partitioned by (entrytime string) stored as textfile;

    insert into employees_info_extended partition(entrytime='2014') select id,name,usd_flag,salary,deductions,address from employees_info_v2 where entrytime = '2014';

    数据抽取成功后,查询表数据。

    select * from employees_info_extended;

    +-----------------------------+-------------------------------+-----------------------------------+---------------------------------+-------------------------------------+----------------------------------+------------------------------------+
    | employees_info_extended.id  | employees_info_extended.name  | employees_info_extended.usd_flag  | employees_info_extended.salary  | employees_info_extended.deductions  | employees_info_extended.address  | employees_info_extended.entrytime  |
    +-----------------------------+-------------------------------+-----------------------------------+---------------------------------+-------------------------------------+----------------------------------+------------------------------------+
    | 1                           | Wang                          | R                                 | 8000.01                         | {"personal income tax":0.05}        | China:Shenzhen                   | 2014                               |
    | 3                           | Tom                           | D                                 | 12000.02                        | {"personal income tax":0.09}        | America:NewYork                  | 2014                               |
    | 6                           | Linda                         | D                                 | 36000.04                        | {"personal income tax":0.09}        | America:NewYork                  | 2014                               |
    | 8                           | Zhang                         | R                                 | 9000.05                         | {"personal income tax":0.05}        | China:Shanghai                   | 2014                               |
    +-----------------------------+-------------------------------+-----------------------------------+---------------------------------+-------------------------------------+----------------------------------+------------------------------------+
  3. 统计雇员信息有多少条记录。

    select count(1) from employees_info_v2;

    +------+
    | _c0  |
    +------+
    | 5    |
    +------+
  4. 查询使用以“cn”结尾的邮箱的员工信息。

    select a.*, b.email from employees_info_v2 a inner join employees_contact b on a.id = b.id where b.email rlike '.*cn$';

    +-------+---------+-------------+-----------+-------------------------------+------------------+--------------+-----------------+
    | a.id  | a.name  | a.usd_flag  | a.salary  |         a.deductions          |    a.address     | a.entrytime  |     b.email     |
    +-------+---------+-------------+-----------+-------------------------------+------------------+--------------+-----------------+
    | 3     | Tom     | D           | 12000.02  | {"personal income tax":0.09}  | America:NewYork  | 2014         | xxxx@xx.com.cn  |
    | 6     | Linda   | D           | 36000.04  | {"personal income tax":0.09}  | America:NewYork  | 2014         | xxxx@xx.cn      |
    | 8     | Zhang   | R           | 9000.05   | {"personal income tax":0.05}  | China:Shanghai   | 2014         | xxxx@xxx.cn     |
    +-------+---------+-------------+-----------+-------------------------------+------------------+--------------+-----------------+
分享:

    相关文档

    相关产品

关闭导读