Hive SQL数据查询进阶

教材第五章 Hive SQL数据查询进阶实验

一 Hive 内置函数

1 查看Hive提供的内置函数

hive> show functions;

2 常用数学函数
加减乘除运算hive> select 10*2;
OK
20

hive> select 10/2;
OK
5.0

round()函数：四舍五入
hive> select round(88.947,2), round(77.912,1), round(55.667, 2);
OK
88.95    77.9    55.67

ceil()函数：向上取整
hive>   select ceil(99.9);
OK
100

floor()函数：向下取整
hive> select floor(100.1);
OK
100

pow()函数：指数运算
hive> select pow(2,8);
OK
256.0

pmod(()函数：取模运算
hive> select pmod(13,3);
OK

3 常用字符函数 
hive>  select lower("ABCDEFG") ;
OK
abcdefg

hive> select upper("abcdefg") ;
OK
ABCDEFG

 hive>  select length("hive");
OK
4

hive> select substr("hivesparkhbasehadoop", 5);
OK
sparkhbasehadoop

hive> select concat("hive", " on hbase");
OK
hive on hbase

hive> select trim("   hive on hadoop   ");
OK
hive on hadoop

hive> select locate('abc', 'xxxabcxxabcxxabcxxabcxxabcxxxx', 7);
OK
9
说明：子字符串'abc'在字符串'xxxabcxxabcxxabcxxabcxxabcxxxx'从第7个字符开始首次出现的位置

4 json处理函数：get_json_object 

hive> create table weixin(json string);

在Linux终端创建数据文件  vi weixin.txt

[{"name":"zhangsan","age":23,"address":"hubei"}]
[{"name":"lisi","age":22,"address":"henan"}]
[{"name":"wangwu","age":21,"address":"beijing"}]
[{"name":"zhaoliu","age":20,"address":"shanghai"}]

hive>load data local inpath '/root/weixin.txt' into table weixin;

hive> select substr(json, 2, length(json)-2) as j from weixin;
OK
{"name":"zhangsan","age":23,"address":"hubei"}
{"name":"lisi","age":22,"address":"henan"}
{"name":"wangwu","age":21,"address":"beijing"}
{"name":"zhaoliu","age":20,"address":"shanghai"}

说明：substr(json, 2, length(json)-2)是提取从第2个字符到倒数第2个字符构成的子字符串

hive> select get_json_object(a.j, '$.name'), get_json_object(a.j, '$.age'), get_json_object(a.j, '$.address')  from (select substr(json, 2, length(json)-2) as j from weixin ) a;
OK
zhangsan    23    hubei
lisi    22    henan
wangwu    21    beijing
zhaoliu    20    shanghai

说明：get_json_object(a.j, '$.name')是提取每一行中的键name对应的值张三

hive>  create table weixin_data(name string, age string, address string);

hive> insert into weixin_data(name, age, address)  select get_json_object(a.j, '$.name'), get_json_object(a.j, '$.age'), get_json_object(a.j, '$.address')  from (select substr(json, 2, length(json)-2) as j from weixin ) a;

说明：创建一张新表weixin_data，并将上一个SQL语句的执行结果inert到这张新表中

hive> select * from weixin_data;
OK
zhangsan    23    hubei
lisi    22    henan
wangwu    21    beijing
zhaoliu    20    shanghai

说明：使用get_json_object函数，将只含有一列json格式的列的数据表weixin中的<key,value>逐个提取出来，并插入到新表weixin_data中

5 类型转换函数:cast

hive>select cast(99 as double);
OK
99.0

hive> select cast('2021-10-12' as date);
OK
2021-10-12

6 日期时间函数
hive> select year('2021-10-12 08:41:55'), month('2021-10-12 08:41:55'), day('2021-10-12 08:41:55');
OK
2021    10    12

hive> select to_unix_timestamp('2021-10-12 08:41:55');
OK
1633999315

hive> select hour('2021-10-12 08:41:55'), minute('2021-10-12 08:41:55'), second('2021-10-12 08:41:55');
OK
8    41    55

6 条件表达式
hive> select  ename, job, sal, case job when 'manager' then sal+2000 when 'clerk' then sal+1000 else sal + 400 end from emp;
OK
lucyc    clerk    8000.0    9000.0
tomiy    saleman    8000.0    8400.0
smith    clerk    8000.0    9000.0
zhang    saleman    8000.0    8400.0
liyan    analysit    8000.0    8400.0
qiaof    manager    8000.0    10000.0
xiezc    clerk    8000.0    9000.0
dengy    manager    8000.0    10000.0
说明：条件表达式语法格式是 case A when B then C when D then E else F end

7 聚合函数(重点)

hive> select count(*) from emp;
hive> select count(1) from emp;
hive> select count(ename) from emp;
说明：count是统计函数,统计员工总人数，count(*)和count(1)的结果相同，如果所有行的name列没有缺失值null，则count(name) 结果也相同

hive> select 1 from emp;
OK
1
1
1
1
1
1
1
1

说明：emp表有多少行， select 1 from emp就输出多少个1，因此count(1)就是总的行数

hive> select job, sum(sal) from emp group by job;
OK
analysit    8000.0
clerk    24000.0
manager    16000.0
saleman    16000.0
说明：按岗位job分组统计各个job的员工工资总数，sum是求和函数

hive> select job, min(sal) from emp group by job;
说明：按岗位job分组统计各个job的员工工资最大值，min是取最小值函数

hive> select job, max(sal) from emp group by job;
说明：按岗位job分组统计各个job的员工工资最小值，max是取最大值函数

二 Hive综合案例：sogou搜索引擎日志数据分析

1  将sogou搜索引擎日志数据文件导入到hive的动态分区表

1）使用XShell的XFtp文件传输工具，将Win7系统D:\Hive教学\教学课件\Hive离线计算-配套视频代码数据资料\02 数据定义与操作\讲义\分区表实验\sogou.500w.utf8数据文件和sogou-log-extend.sh脚本文件上传到虚拟机Linux系统的/root目录

提示：如果实验时间不够，可用强大的sed命令截取部分数据行，以缩短命令执行时间
以下命令提取原数据文件的1~10000行，1000001~1010000行，2000001~2010000行，3000001~3010000行，4000001~4010000行，共5万行数据，输出重定向到一个新的数据文件sogou.5w.utf8，在Linux终端依次执行以下命令：
sed -n '1, 10000p' sogou.500w.utf8 > sogou.5w.utf8
sed -n '1000001, 1010000p' sogou.500w.utf8 >> sogou.5w.utf8
sed -n '2000001, 2010000p' sogou.500w.utf8 >> sogou.5w.utf8
sed -n '3000001, 3010000p' sogou.500w.utf8 >> sogou.5w.utf8
sed -n '4000001, 4010000p' sogou.500w.utf8 >> sogou.5w.utf8

2）执行cd /root命令进入root目录，执行命令less sogou.500w.utf8分页浏览数据文件sogou.500w.utf8的内容

3）查看数据文件sogou.500w.utf8的行数，执行命令wc -l sogou.500w.utf8，输出结果为
共500万行

4）执行Linux终端尝试执行命令  ./sogou-log-extend.sh sogou.500w.utf8 sogou.500w.utf8.ext
提示：-bash: ./sogou-log-extend.sh: 权限不够

5）执行ll命令查看/root目录的文件详细列表
-rw-r--r--. 1 root root 573670020 9月  27 09:28 sogou.500w.utf8
说明：文件sogou.500w.utf8不具有可执行权限

6）Linux终端执行命令chmod 777 sogou-log-extend.sh  修改文件权限全部为可读可写可执行
再执行ll sogou-log-extend.sh命令查看/root目录的文件详细列表
-rwxrwxrwx. 1 root root 204 9月  27 09:33 sogou-log-extend.sh
说明：发现文件sogou.500w.utf8已具有可执行权限

7）Linux终端执行脚本命令 ./sogou-log-extend.sh sogou.500w.utf8 sogou.500w.utf8.ext
说明：a）sogou-log-extend.sh脚本文件的作用是为原数据文件扩充新列，具体是将数据文件的第1个列字段“用户访问时间”按年份、月份、日期、小时进行拆分，并将年份、月份、日期、小时做为新的列字段扩充到每行数据中
b）sogou-log-extend.sh脚本文件中的awk命令是著名的Linux三剑客命令之一，完整命令是awk -F '\t' '{print $0"\t"substr($1,1,4)"\t"substr($1,5,2)"\t"substr($1,7,2)"\t"substr($1,9,2)}' $infile > $outfile，该命令的作用是将每行数据按照制表符\t拆分成若干列，$0是原数据行，$1是取拆分的第1个列，将第1列按年月日小时取子字符串，追加到原数据行$0，将对原数据文件sogou.500w.utf8处理结果重定向到新的数据文件sogou.500w.utf8.ext
Linux终端执行命令less sogou.500w.utf8.ext  分页浏览数据文件sogou.500w.utf8.ext的内容，发现每行数据后面都追加了年、月、日、小时共四个新的列字段，例如

20111230000011  7c54c43f3a8a0af0951c26d94a57d6c8        百度一下 你就知道       1       1       http://www.baidu.com/    2011    12    30    00

8）创建一个普通的外部表sogou_table(说明：原数据文件sogou.500w.utf8后面的数据分析用不到,也可以不创建本表，步骤８和步骤９可以不执行)
hive> create external table sogou_table(ts string, uid string, keyword string, rank int, seq int, url string) comment 'this is data of one day from sogou search engine' row format delimited fields terminated by '\t';

说明：comment 'this is data of one day from sogou search engine'是注释语句，教学环境可有可无；生产环境中数据表很多，创建表时建议对表的作用做一个简单的描述

hive> desc sogou_table;
OK
ts                      string                                      
uid                     string                                      
keyword                 string                                      
rank                    int                                         
seq                     int                                         
url                     string
说明：ts访问时间，uid用户ID，keyword搜索的关键词，rank在返回结果的排名，seq用户点击的顺序号ÿ