一、 任务描述
本实验任务主要完成基于Hive环境的除了一些常用的Hive SQL语句操作外,通过完成本实验任务,还要求学生熟练掌握Hive中一些自带的函数的使用,学会使用Hive中的函数会很好的帮助学生更快的处理数据,也为进一步学习自定义函数做基础。
二、 任务目标
掌握Hive的数据类型
掌握hive的数据类型的使用
掌握hive的DDL语句的操作
三、 任务环境
本次环境是:Ubuntu16.04+hadoop-2.7.3.+apache-hive-1.2.2-bin
四、 任务分析
Hive支持多种不同长度的整型和浮点型数据类型,支持布尔类型,也支持无长度限制的字符串类型。
Hive中的列支持使用struct,map和array集合数据类型。
♥ 知识链接
hive建表语法中的分隔符设定
name | Value |
ROW FORMAT DELIMITED | 分隔符设置开始语句 |
FIELDS TERMINATED BY: | 设置字段与字段之间的分隔符 |
COLLECTION ITEMS TERMINATED BY: | 设置一个复杂类型(array,struct)字段的各个item之间的分隔符 |
MAP KEYS TERMINATED BY: | 设置一个复杂类型(Map)字段的key value之间的分隔符 |
LINES TERMINATED BY: | 设置行与行之间的分隔符 |
ROW FORMAT DELIMITED | 必须在其它分隔设置之前,也就是分隔符设置语句的最前 |
LINES TERMINATED BY | 必须在其它分隔设置之后,也就是分隔符设置语句的最后,否则会报错 |
实验所需数据和格式如下:
employees.txt内容
John Doe,100000.0,Mary Smith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1 Michigan Ave.|Chicago|IL|60600 Mary Smith,100000.0,Mary Smith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1MichiganAve.|Chicago|IL|60601 Todd Jones,800000.0,Mary Smith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1MichiganAve.|Chicago|IL|60603 Bill King,800000.0,Mary Smith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1 Michigan Ave.|Chicago|IL|60605 Boss Man,100000.0,Mary Smith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1 Michigan Ave.|Chicago|IL|60604 Fred Finance,800000.0,Mary Smith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1 Michigan Ave.|Chicago|IL|60400 Stacy Accountant,800000.0,Mary Smith|Todd Jones,Federal Taxes:.2|State Taxes:.05|Insurance:.1,1 Michigan Ave.|Chicago|IL|60300
五、 任务实施
步骤1、新建hql文件
在命令终端,新建hql文件,分别执行命令:cd /simple
和touch create.hql
,如图1所示。
图1 创建文件
向其中写入hql语句,在命令终端中,在/simple目录下执行命令:vim create.hql
1. 向其中添加以下语句 2. 3. create table employee( 4. 5. name string, 6. 7. salary float, 8. 9. subordinate array<string>, 10. 11. deduction map<string,float>, 12. 13. address struct<street:string,city:string,state:string,zip:int> 14. 15. ) 16. 17. row format delimited fields terminated by "," 18. 19. collection items terminated by "|" 20. 21. map keys terminated by ":";
最终创建如图2所示。
图2 代码显示
步骤2、hive中执行此文件
在命令终端中,执行命令:start-all.sh
启动hadoop服务,如图3所示。
图3 启动hadoop服务
执行命令:service mysql start
启动mysql服务,然后执行命令:cd /simple/hive/bin
进入hive的安装目录bin目录下,执行命令:./hive
,并在hive命令行中执行以下语句:source /simple/create.hql;
如果想在执行hive的命令时,不打印日志信息在shell界面,可以执行命令:./hive -S
即可,如图4所示。
图4 进入hive shell界面
可以看出创建成功。
hive命令行中输入:describe employee;
如图5所示。
图5 显示表结构
步骤3、向表中插入数据
通过load命令把本地数据加载到表中,如图6所示。
图6 向表中加载数据
步骤4、Select语句查询详解
使用索引查询集合数据类型中的元素。数组索引是基于0的,这和java中是一样的,下面是选择subordinate数组中的第一个元素的查询,如图7所示。
图7 查询数组数据
引用map类型中的一个元素。用户可以使用ARRAY[…]语法,但是使用的是键值而不是整数索引,如图8所示。
图8 查询map数据
引用struct中的一个元素,用户可以使用“.“符号,如图9所示。
图9 查询struct数据
步骤5、使用列值进行计算
查询所有用户的年薪(税前),假设一年工资为15个月,如图10所示。
图10 所有用户的年薪(税前)
查询用户扣除联邦税后实际到手的年薪,如图11所示。
图11 用户扣除联邦税后实际到手的年薪
执行语句“select count(*) from employee;
“查询一共有多少员工,如图12所示。
图12 员工总数
可以看出底层的查询是通过map-reduce来完成的。
执行语句”select avg(salary) from employee;
”查询员工的平均工资,如图13所示。
图13 员工平均工资
可以看出底层的查询是通过map-reduce来完成的。
执行语句”select sum(salary) from employee;
”查询出公司发放的薪水总额,如图14所示。
图14 公司发放的薪水总额