Hive实战练习

实验预计耗时：45分钟

1. 课程背景

1.1 课程目的

弹性 MapReduce（EMR）是腾讯云结合云技术和 Hadoop、Spark 等社区开源技术，提供的安全、低成本、高可靠、可弹性伸缩的云端托管 Hadoop 服务。您可以在数分钟内创建安全可靠的专属 Hadoop 集群，以分析位于集群内数据节点或 COS 上的 PB 级海量数据。

本实验将借助腾讯云EMR服务构建一个Hadoop集群，通过使用Hive常见命令与HQL语法，让学员掌握Hive基本功能的使用。

1.2 课前知识准备

学习本课程前，学员需要掌握以下前置知识：

1、能力基础

• Linux基本操作：掌握Linux远程登录、文件与目录管理、vim编辑器使用等。
• SQL语法基础：包括SQL基本语法、数据类型、常用内置函数等。

2、相关技术

• Hadoop：是由Apache基金会所开发的分布式系统基础架构，其核心包括以下几个组件：
  • HDFS：提供数据高吞吐量访问的分布式文件系统。
  • YARN：作业调度和集群资源管理框架。
  • MapReduce：一种基于YARN的大型数据集并行处理系统。
• Hive：基于Hadoop的一个数据仓库工具，Hive结构可以分为以下几部分：
  • 用户接口/界面：Hive是一个数据仓库基础工具软件，可以创建用户和HDFS之间互动。Hive支持Web GUI用户界面，Hive命令行，以及JDBC等接入方式。
  • Hive元数据存储：Hive将元数据存储在数据库中，如MySQL、Derby。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
  • 执行引擎：完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成，执行引擎一般采用MapReduce、TEZ、Spark等。
  • 数据存储：Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成。

2. 实验环境

2.1 实验操作环境

本课程需要以下实验操作环境：

1. 可以接入互联网的笔记本电脑或者台式机，本实验使用Windows系统
2. 实验环境：计算机本地+腾讯云控制台

2.2 实验架构图

本实验将使用EMR三节点集群（Master节点和两个Core节点），使用PuTTY连接Master节点的方式访问集群。Hive执行HQL语句时，Hive接收HQL请求后对复杂请求进行MapReduce转换，并在YARN上进行运行程序，其架构图如下：

2.3 实验的数据规划表

PuTTY下载

3. 实验流程

实验共分为五个任务，首先在计算机准备实验环境，使用腾讯云弹性MapReduce服务创建一个包含Hive组件的Hadoop集群。

其余任务会带您依次练习Hive语法（HQL）的使用，包括Hive基础的DDL（数据定义语言），DML（数据操纵语言），分区表的创建和查询以及join多表查询。

4. 实验步骤

任务1 实验环境准备

【任务目标】

通过EMR集群的搭建练习，使学员可以熟练搭建EMR集群，快速构建实验所需大数据平台环境。

【任务步骤】

1、EMR集群选购

1.在腾讯云官网，找到弹性MapReduce首页，点击立即购买。

2.可用区与软件配置如下：

确认配置无误后，点击下一步：硬件配置。

3.硬件配置如下：

启动高可用选项可以自定义选择，默认是选择的，如果取消需要手动取消选择。由于我们这里的实验环境仅仅是一个学习的实验环境所以这里我们将此选项取消，实际生产中要根据实际环境合理选择是否需要这个配置。

确认硬件配置信息无误后，点击下一步：基础配置。

4.基础配置如下：

确认信息无误后，点击购买，会自动跳转至集群页。图中的集群实例状态中显示集群创建中。

等待10min左右，集群构建成功，截图如下：

2、第三方工具连接EMR集群

1.复制集群页的主节点外网IP，打开PuTTY创建连接，将复制的外网IP粘贴至Host Name，端口默认22，如图：

2.点击Open，第一次连接会弹出安全警告，点击是（Y）。

3.接下在login as：后填写用户名为root，密码为构建EMR的时候设置的密码：

备注：这里只能使用root用户进行连接。

回车确认后，我们即可成功访问主节点实例。输入java进程查看命令jps，可看到应用进程已经启动。

jps

任务2 Hive数据库与表创建

【任务目标】

使用Hive客户端，练习常见DDL（数据定义语言）的基本使用。

电影数据表 tx.tx_movies

【任务步骤】

1、创建Hive数据库和表

1.进入Hive环境；

首先切换为hadoop用户；

su hadoop

再使用hive启动Hive客户端；

hive

2.创建Hive数据库tx；

CREATE DATABASE tx;

使用数据库tx；

USE tx;

验证：使用下面命令查看数据库。

SHOW DATABASES;

3.在tx库中创建Hive表 tx_movies；

CREATE TABLE tx.tx_movies (
  movieid string COMMENT '电影ID',
  title string COMMENT '电影标题',
  genre string COMMENT '官方题材'
  )COMMENT '电影数据集'
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\t'
    STORED AS TEXTFILE;

4.查看 movies表结构；

DESC formatted tx.tx_movies;

下图为查看数据库tx下的tx_movies这张表的表结构：

2、尝试删除数据库

1.非空的数据库是不允许删除的，使用drop命令尝试删除tx数据库：

DROP DATABASE tx;

下图为删除数据库tx返回的报错信息。

2.退出Hive客户端

quit;

任务3 Hive数据加载

【任务目标】

通过向Hive表加载数据，练习Hive DML（数据操纵语言）相关操作。

【任务步骤】

1、向表中加载数据

1.创建文件夹/test，用于存放数据。使用下面链接下载电影数据。

首先切换用户为root；

su root

创建文件夹test；

mkdir /test

切换到test路径下；

cd /test

下载数据集。

wget https://course-public-resources-1252758970.cos.ap-chengdu.myqcloud.com/PracticalApplication/202001bigdata/3-hive/movies_tmp.dat

2.检查数据与字段对应关系，并修改文件的权限。

使用tail命令查看数据信息：

tail movies_tmp.dat

3.重新进入Hive客户端。

切换用户；

su hadoop

进入hive客户端；

hive

从本地路径向tx_movies表导入数据，HQL语句如下：

LOAD DATA LOCAL INPATH '/test/movies_tmp.dat' OVERWRITE INTO TABLE tx.tx_movies;

2、查看导入数据

1.打开查询时显示列名称。

默认Hive查询数据时不显示列的名称，使用下列命令显示列的名称；

SET hive.cli.print.header=true;

2.查询数据。

SELECT * FROM tx.tx_movies LIMIT 10;

任务4 Hive分区表操作

【任务目标】

通过创建Hive分区表，练习分区表的操作。

【任务步骤】

1、创建分区表

1.创建以日期字段dt为分区条件的分区表：

CREATE TABLE tx.tx_movies_part (
  movieId string COMMENT '电影ID',
  title string COMMENT '电影标题',
  genre string COMMENT '官方题材'
  )COMMENT '电影数据集'
   PARTITIONED BY (dt string)
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\t'
    STORED AS TEXTFILE;

验证表格创建成功；

SHOW TABLES;

2.利用select已有数据方式insert到指定分区。

INSERT INTO TABLE tx.tx_movies_part PARTITION  (dt='2017-10-04') SELECT * FROM tx.tx_movies LIMIT 10;

可以看到该语句执行过程中启动了MapReduce程序：

2、查看分区

1.查看分区，命令如下：

SHOW partitions tx.tx_movies_part;

结果如下，可以知道目前有一个分区：

2.查看指定分区详情。

DESC formatted tx.tx_movies_part partition(dt='2017-10-04');

可以通过位置详细信息知道，目前数据存放在HDFS内，结果如下：

3.在控制台找到组件管理，点击HDFS原生WebUI访问地址，初次访问需要输入用户名（root），用户名密码（EMR创建时设置的登录密码）。

注：如果浏览器提示安全警告，点击高级>允许访问。

4.点击上方Utilities，选择Browser the file system。

5.可以在/usr/hive/warehouse/tx.db/tx_movies_part目录下看到，Hive以分区名“dt=2017-10-04”创建了文件夹，故一个分区下的数据存储在同一个HDFS文件夹下：

3、统计分区数据

1.对分区下数据执行统计查询，可以执行如下语句。

-- 查询分区下的数据统计
SELECT COUNT(1) FROM tx.tx_movies_part WHERE dt = '2017-10-04';

查询结果如下：

2.确认操作无误后，退出hive客户端。退出命令如下：

quit;

任务5 电影平均评分查询

【任务目标】

通过电影平均评分查询练习，使学员掌握条件查询和join查询的基本使用。

电影评分表 tx.tx_ratings

注意：时间戳的英文名是timestamp，在这里使用timestame的命名是由于timestamp是HQL中的关键词，不可以将列的名字命名为timestamp，故改名为timestame。

电影标签表 tx.tx_tags

【任务步骤】

1、创建tx_ratings表和tx_tags表并加载数据

1.获取数据。

切换用户到root，需要输入密码；

su root

切换/test目录；

cd /test

获取数据ratings_tmp.dat；

wget https://course-public-resources-1252758970.cos.ap-chengdu.myqcloud.com/PracticalApplication/202001bigdata/3-hive/ratings_tmp.dat

获取数据tags_tmp.dat；

wget https://course-public-resources-1252758970.cos.ap-chengdu.myqcloud.com/PracticalApplication/202001bigdata/3-hive/tags_tmp.dat

切换到hadoop用户；

su hadoop

登录hive客户端；

hive

2.依次创建tx_ratings和tx_tags两个表，分隔符为 ‘\t’，存储类型选择 TEXTFILE。

用户对电影的评分表；

CREATE TABLE tx.tx_ratings(
  userid string COMMENT '用户id',
  movieid string COMMENT '电影ID',
  rate decimal(3,1) COMMENT '用户userId给电影movieId的打分',
  timestame string COMMENT '记录时间戳'
  )COMMENT '用户对电影的打分数据'
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\t'
   STORED AS TEXTFILE;

电影标签数据表；

CREATE TABLE tx.tx_tags(
  userid string COMMENT '用户id',
  movieid string COMMENT '电影ID',
  tag string COMMENT 'tag为用户userId给电影movieId打的标签',
  timestame string COMMENT '记录时间戳'
  )COMMENT '电影标签数据'
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\t'
   STORED AS TEXTFILE;

3.分别为tx.tx_ratings和tx.tx_tags加载数据。

向tx.tx_ratings加载数据；

LOAD DATA LOCAL INPATH '/test/ratings_tmp.dat'  INTO TABLE tx.tx_ratings  ;

向tx.tx_tags加载数据；

LOAD DATA LOCAL INPATH '/test/tags_tmp.dat'  INTO TABLE tx.tx_tags  ;

执行信息如下图所示，可以看出速度还是比较快的：

4.结果验证。

开启列名称显示；

SET hive.cli.print.header=true;

查看查询；

SELECT movieId,rate FROM tx.tx_ratings ORDER BY rate DESC LIMIT 10;

下图为查询表tx_ratings中前十的电影的分数。

2、HQL查询每一个电影的平均评分

1.创建表tmp_movies_ratings。

CREATE TABLE tx.tmp_movies_ratings (
  movieid string COMMENT '电影ID',
  title string COMMENT '电影标题',
  avg_rate decimal(3,1) COMMENT '平均评分'
  )ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\t'
    STORED AS TEXTFILE;

2.由于是跨表查询，这需要使用到join查询，执行结果会执行MapReduce程序，请耐心等待。

INSERT INTO tx.tmp_movies_ratings 
SELECT a.movieid,a.title,b.avg_rate
  FROM tx.tx_movies a JOIN 
  (SELECT movieid,avg(rate) AS avg_rate FROM tx.tx_ratings GROUP BY movieId )
   b ON a.movieId = b.movieId;

3.查询表tmp_movies_ratings中的前十条记录。

SELECT * FROM tx.tmp_movies_ratings ORDER BY avg_rate DESC LIMIT 10;

结果如下：

至此，您已经完成了Hive实战练习的全部任务，相信您已经学会了使用Hive完成创建普通表，创建分区表，执行join查询等操作。您可以根据自己的需求，进行其他Hive功能的练习。

5. 注意事项

如实验资源无需保留，请在实验结束后及时销毁，以免产生额外费用。