1.hive中基本操作;
DDL,DML
2.hive中函数
User-Defined Functions : UDF(用户自定义函数,简称JDF函数)
UDF: 一进一出 upper lower substring(进来一条记录,出去还是一条记录)
UDAF:Aggregation(用户自定的聚合函数) 多进一出 count max min sum ...
UDTF: Table-Generation 一进多出
3.举例
show functions显示系统支持的函数
行数举例:split(),explode()
exercise:使用hive统计单词出现次数
explode把数组转成多行的数据
[hadoop@hadoop000 data]$ vi hive-wc.txt
hello,world,welcome
hello,welcome
hive> create table hive_wc(sentence string);
OK
Time taken: 1.083 seconds
hive> load data local inpath '/home/hadoop/data/hive-wc.txt' into table hive_wc;
Loading data to table default.hive_wc
Table default.hive_wc stats: [numFiles=1, totalSize=35]
OK
Time taken: 1.539 seconds
hive> select * from hive_wc;
OK
hello,world,welcome
hello,welcome
Time taken: 0.536 seconds, Fetched: 3 row(s)
hive> select split(sentence,",") from hive_wc;
OK
["hello","world","welcome"]
["hello","welcome"]
[""]
Time taken: 0.161 seconds, Fetched: 3 row(s)
"hello"
"world"
"welcome"
"hello"
"welcome"
用一个SQL完成wordcount统计:
hive> select word, count(1) as c
> from (select explode(split(sentence,",")) as word from hive_wc) t
> group by word ;
Query ID = hadoop_20180613094545_920c2e72-5982-47eb-9a9c-5e5a30ebb1ae
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>
Starting Job = job_1528851144815_0001, Tracking URL = http://hadoop000:8088/proxy/application_1528851144815_0001/
Kill Command = /home/hadoop/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job -kill job_1528851144815_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2018-06-13 10:18:53,155 Stage-1 map = 0%, reduce = 0%
2018-06-13 10:18:59,605 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.42 sec
2018-06-13 10:19:07,113 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 4.31 sec
MapReduce Total cumulative CPU time: 4 seconds 310 msec
Ended Job = job_1528851144815_0001
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 4.31 sec HDFS Read: 7333 HDFS Write: 29 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 310 msec
OK
1
hello 2
welcome 2
world 1
Time taken: 26.859 seconds, Fetched: 4 row(s)
4.json类型数据
使用到的文件: rating.json
创建一张表 rating_json,上传数据,并查看前十行数据信息:
hive> create table rating_json(json string);
OK
hive> load data local inpath '/home/hadoop/data/rating.json' into table rating_json;
Loading data to table default.rating_json
Table default.rating_json stats: [numFiles=1, totalSize=34967552]
OK
hive> select * from rating_json limit 10;
OK
{"movie":"1193","rate":"5","time":"978300760","userid":"1"}
{"movie":"661","rate":"3","time":"978302109","userid":"1"}
{"movie":"914","rate":"3","time":"978301968","userid":"1"}
{"movie":"3408","rate":"4","time":"978300275","userid":"1"}
{"movie":"2355","rate":"5","time":"978824291","userid":"1"}
{"movie":"1197","rate":"3","time":"978302268","userid":"1"}
{"movie":"1287","rate":"5","time":"978302039","userid":"1"}
{"movie":"2804","rate":"5","time":"978300719","userid":"1"}
{"movie":"594","rate":"4","time":"978302268","userid":"1"}
{"movie":"919","rate":"4","time":"978301368","userid":"1"}
Time taken: 0.195 seconds, Fetched: 10 row(s)
对json的数据进行处理,json_tuple 是一个UDTF是 Hive0.7版本引进的:
hive> select
> json_tuple(json,"movie","rate","time","userid") as (movie,rate,time,userid)
> from rating_json limit 10;
OK
1193 5 978300760 1
661 3 978302109 1
914 3 978301968 1
3408 4 978300275 1
2355 5 978824291 1
1197 3 978302268 1
1287 5 978302039 1
2804 5 978300719 1
594 4 978302268 1
919 4 978301368 1
Time taken: 0.189 seconds, Fetched: 10 row(s)
5.时间类型的转换:
[hadoop@hadoop000 data]$ more hive_row_number.txt
1,18,ruoze,M
2,19,jepson,M
3,22,wangwu,F
4,16,zhaoliu,F
5,30,tianqi,M
6,26,wangba,F
[hadoop@hadoop000 data]$
hive> create table hive_rownumber(id int,age int, name string, sex string)
> row format delimited fields terminated by ',';
OK
Time taken: 0.451 seconds
hive> load data local inpath '/home/hadoop/data/hive_row_number.txt' into table hive_rownumber;
Loading data to table hive3.hive_rownumber
Table hive3.hive_rownumber stats: [numFiles=1, totalSize=84]
OK
Time taken: 1.381 seconds
hive> select * from hive_rownumber ;
OK
1 18 ruoze M
2 19 jepson M
3 22 wangwu F
4 16 zhaoliu F
5 30 tianqi M
6 26 wangba F
Time taken: 0.455 seconds, Fetched: 6 row(s)
需求:查询出每种性别中年龄最大的两条数据 -- > topn:
分析:order by 是全局的排序,是做不到分组内的排序的 ;组内进行排序,就要用到窗口函数or分析函数
select id,age,name.sex
from
(select id,age,name,sex,
row_number() over(partition by sex order by age desc)
from hive_rownumber) t
where rank<=2;
hive> select id,age,name,sex
> from
> (select id,age,name,sex,
> row_number() over(partition by sex order by age desc) as rank
> from hive_rownumber) t
> where rank<=2;
Query ID = hadoop_20180614202525_9829dc42-3c37-4755-8b12-89c416589ebc
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>
Starting Job = job_1528975858636_0001, Tracking URL = http://hadoop000:8088/proxy/application_1528975858636_0001/
Kill Command = /home/hadoop/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job -kill job_1528975858636_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2018-06-14 20:26:18,582 Stage-1 map = 0%, reduce = 0%
2018-06-14 20:26:24,010 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.48 sec
2018-06-14 20:26:31,370 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 3.86 sec
MapReduce Total cumulative CPU time: 3 seconds 860 msec
Ended Job = job_1528975858636_0001
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 3.86 sec HDFS Read: 8586 HDFS Write: 56 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 860 msec
OK
6 26 wangba F
3 22 wangwu F
5 30 tianqi M
2 19 jepson M
Time taken: 29.262 seconds, Fetched: 4 row(s)