Hive常用函数

UDF官网 : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

github FunctionRegistry.java 查看具体注册代码

Hive的参数传递

hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-e query-string>] [-S]
1、   -i 从文件初始化HQL。
2、   -e从命令行执行指定的HQL 
3、   -f 执行HQL脚本 
4、   -v 输出执行的HQL语句到控制台 
5、   -p <port> connect to Hive Server on port number 
6、   -hiveconf x=y Use this to set hive/hadoop configuration variables.  设置hive运行时候的参数配置

Hive参数大全:

https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

开发Hive应用时,不可避免地需要设定Hive的参数。设定Hive的参数可以调优HQL代码的执行效率,或帮助定位问题。然而实践中经常遇到的一个问题是,为什么设定的参数没有起作用?这通常是错误的设定方式导致的。

对于一般参数,有以下三种设定方式:

# 1. 配置文件 ,以及读取的hadoop配置
hive-site.xml > hive-default.xml > hadoop配置

# 2. 命令行参数  启动hive客户端的时候可以设置参数
bin/hive -hiveconf hive.root.logger=INFO,console

# 3. 参数声明   进入客户端以后设置的一些参数  set 
bin/hive
set mapred.reduce.tasks=100;

# 优先级: 参数声明  >   命令行参数   >  配置文件参数(hive)
hiveconf使用说明

hiveconf用于定义HIVE执行上下文的属性(配置参数),可覆盖覆盖hive-site.xml(hive-default.xml)中的参数值,如用户执行目录、日志打印级别、执行队列等。例如我们可以使用hiveconf来覆盖我们的hive属性配置,

hiveconf变量取值必须要使用hiveconf作为前缀参数,具体格式如下:

${hiveconf:key} 
bin/hive --hiveconf "mapred.job.queue.name=root.default"
hivevar使用说明

hivevar用于定义HIVE运行时的变量替换,类似于JAVA中的“PreparedStatement”,与\${key}配合使用或者与 ${hivevar:key}

对于hivevar取值可以不使用前缀hivevar,具体格式如下:

使用前缀:
 ${hivevar:key}
不使用前缀:
 ${key}
--hivevar  name=zhangsan    ${hivevar:name}  
也可以这样取值  ${name}
define使用说明
define与hivevar用途完全一样,还有一种简写“-d
bin/hive --hiveconf "mapred.job.queue.name=root.default" -d my="201809" --database mydb
执行SQL
select * from mydb where concat(year, month) = ${my} limit 10;
hiveconf与hivevar使用实战
# 脚本文件如下 /kfly/sql/query.sql
select * from student left join score on student.s_id = score.s_id where score.month = ${hiveconf:month} and score.s_score > ${hivevar:s_score} and score.c_id = ${c_id};   
# 执行脚本文件
bin/hive --hiveconf month=201807 --hivevar s_score=80 --hivevar c_id=03  -f /kfly/sql/query.sql

常用函数

# 1.查看系统自带的函数
hive> show functions;

# 2.显示自带的函数的用法
hive> desc function upper;

# 3.详细显示自带的函数的用法
hive> desc function extended upper;

1、数值计算

1、取整函数: round

语法: round(double a)
返回值: BIGINT
说明: 返回double类型的整数值部分 (遵循四舍五入)

hive> select round(3.1415926) from tableName;
3
hive> select round(3.5) from tableName;
4
hive> create table tableName as select round(9542.158) from tableName;

2、指定精度取整函数: round

语法: round(double a, int d)
返回值: DOUBLE
说明: 返回指定精度d的double类型

hive> select round(3.1415926,4) from tableName;
3.1416
3、向下取整函数: floor

语法: floor(double a)
返回值: BIGINT
说明: 返回等于或者小于该double变量的最大的整数

hive> select floor(3.1415926) from tableName;
3
hive> select floor(25) from tableName;
25


4、向上取整函数: ceil

语法: ceil(double a)
返回值: BIGINT
说明: 返回等于或者大于该double变量的最小的整数

hive> select ceil(3.1415926) from tableName;
4
hive> select ceil(46) from tableName;
46


5、向上取整函数: ceiling

语法: ceiling(double a)
返回值: BIGINT
说明: 与ceil功能相同

hive> select ceiling(3.1415926) from tableName;
4
hive> select ceiling(46) from tableName;
46

6、取随机数函数: rand

语法: rand(),rand(int seed)
返回值: double
说明: 返回一个0到1范围内的随机数。如果指定种子seed,则会等到一个稳定的随机数序列

hive> select rand() from tableName;
0.5577432776034763
hive> select rand() from tableName;
0.6638336467363424
hive> select rand(100) from tableName;
0.7220096548596434
hive> select rand(100) from tableName;
0.7220096548596434

2、日期函数

1、UNIX时间戳转日期函数: from_unixtime

语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式

hive> select from_unixtime(1323308943,'yyyyMMdd') from tableName;
20111208


2、获取当前UNIX时间戳函数: unix_timestamp

语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳

hive> select unix_timestamp() from tableName;
1323309615

3、日期转UNIX时间戳函数: unix_timestamp

语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为”yyyy-MM-dd HH:mm:ss”的日期到UNIX时间戳。如果转化失败,则返回0。

hive> select unix_timestamp('2011-12-07 13:01:03') from tableName;
1323234063
4、指定格式日期转UNIX时间戳函数: unix_timestamp

语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败,则返回0。

hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from tableName;
1323234063

5、日期时间转日期函数: to_date

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。

hive> select to_date('2011-12-08 10:03:01') from tableName;
2011-12-08
6、日期转年函数: year

语法: year(string date)
返回值: int
说明: 返回日期中的年。

hive> select year('2011-12-08 10:03:01') from tableName;
2011
hive> select year('2012-12-08') from tableName;
2012


7、日期转月函数: month

语法: month (string date)
返回值: int
说明: 返回日期中的月份。

hive> select month('2011-12-08 10:03:01') from tableName;
12
hive> select month('2011-08-08') from tableName;
8


8、日期转天函数: day

语法: day (string date)
返回值: int
说明: 返回日期中的天。

hive> select day('2011-12-08 10:03:01') from tableName;
8
hive> select day('2011-12-24') from tableName;
24


9、日期转小时函数: hour

语法: hour (string date)
返回值: int
说明: 返回日期中的小时。

hive> select hour('2011-12-08 10:03:01') from tableName;
10

10、日期转分钟函数: minute

语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。

hive> select minute('2011-12-08 10:03:01') from tableName;
3

hive> select second('2011-12-08 10:03:01') from tableName;
1
12、日期转周函数: weekofyear

语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。

hive> select weekofyear('2011-12-08 10:03:01') from tableName;
49

13、日期比较函数: datediff

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。

hive> select datediff('2012-12-08','2012-05-09') from tableName;
213

14、日期增加函数: date_add

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。

hive> select date_add('2012-12-08',10) from tableName;
2012-12-18

15、日期减少函数: date_sub

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。

hive> select date_sub('2012-12-08',10) from tableName;
2012-11-28

3、条件函数

1、If函数: if

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull

hive> select if(1=2,100,200) from tableName;
200
hive> select if(1=1,100,200) from tableName;
100


2、非空查找函数: COALESCE

语法: COALESCE(T v1, T v2, …)
返回值: T
说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL

hive> select COALESCE(null,'100','50') from tableName;
100


3、条件判断函数:CASE

语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
返回值: T
说明:如果a等于b,那么返回c;如果a等于d,那么返回e;否则返回f

hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
tim


4、条件判断函数:CASE

语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值: T
说明:如果a为TRUE,则返回b;如果c为TRUE,则返回d;否则返回e

hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
tom


4、字符串函数

1、字符串长度函数:length

语法: length(string A)
返回值: int
说明:返回字符串A的长度

hive> select length('abcedfg') from tableName;

2、字符串反转函数:reverse

语法: reverse(string A)
返回值: string
说明:返回字符串A的反转结果

hive> select reverse('abcedfg') from tableName;
gfdecba
3、字符串连接函数:concat

语法: concat(string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,支持任意个输入字符串

hive> select concat('abc','def','gh') from tableName;
abcdefgh

4、字符串连接并指定字符串分隔符:concat_ws

语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符

hive> select concat_ws(',','abc','def','gh')from tableName;
abc,def,gh

5、字符串截取函数:substr

语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明:返回字符串A从start位置到结尾的字符串

hive> select substr('abcde',3) from tableName;
cde
hive> select substring('abcde',3) from tableName;
cde
hive>  select substr('abcde',-1) from tableName;  (和ORACLE相同)
e

6、字符串截取函数:substr,substring

语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
说明:返回字符串A从start位置开始,长度为len的字符串

hive> select substr('abcde',3,2) from tableName;
cd
hive> select substring('abcde',3,2) from tableName;
cd
hive>select substring('abcde',-2,2) from tableName;
de

7、字符串转大写函数:upper,ucase

语法: upper(string A) ucase(string A)
返回值: string
说明:返回字符串A的大写格式

hive> select upper('abSEd') from tableName;
ABSED
hive> select ucase('abSEd') from tableName;
ABSED

8、字符串转小写函数:lower,lcase

语法: lower(string A) lcase(string A)
返回值: string
说明:返回字符串A的小写格式

hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed

9、去空格函数:trim

语法: trim(string A)
返回值: string
说明:去除字符串两边的空格

hive> select trim(' abc ') from tableName;
abc

10、url解析函数 parse_url

语法:
parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
说明:返回URL中指定的部分。partToExtract的有效值为:HOST, PATH,
QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') 
from tableName;
www.tableName.com 
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
 from tableName;
v1

11、json解析 get_json_object

语法: get_json_object(string json_string, string path)
返回值: string
说明:解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。

hive> select  get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"[email protected]_for_json_udf_test.net","owner":"amy"}','$.owner') from tableName;

12、重复字符串函数:repeat

语法: repeat(string str, int n)
返回值: string
说明:返回重复n次后的str字符串

hive> select repeat('abc',5) from tableName;
abcabcabcabcabc

13、分割字符串函数: split

语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str,会返回分割后的字符串数组

hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]

5、集合统计函数

1、个数统计函数: count

语法: count(*), count(expr), count(DISTINCT expr[, expr_.])
返回值:Int

说明: count(*)统计检索出的行的个数,包括NULL值的行;count(expr)返回指定字段的非空值的个数;count(DISTINCT
expr[, expr_.])返回指定字段的不同的非空值的个数

hive> select count(*) from tableName;
20
hive> select count(distinct t) from tableName;
10


2、总和统计函数: sum

语法: sum(col), sum(DISTINCT col)
返回值: double
说明: sum(col)统计结果集中col的相加的结果;sum(DISTINCT col)统计结果中col不同值相加的结果

hive> select sum(t) from tableName;
100
hive> select sum(distinct t) from tableName;
70


3、平均值统计函数: avg

语法: avg(col), avg(DISTINCT col)
返回值: double
说明: avg(col)统计结果集中col的平均值;avg(DISTINCT col)统计结果中col不同值相加的平均值

hive> select avg(t) from tableName;
50
hive> select avg (distinct t) from tableName;
30


4、最小值统计函数: min

语法: min(col)
返回值: double
说明: 统计结果集中col字段的最小值

hive> select min(t) from tableName;
20


5、最大值统计函数: max

语法: maxcol)
返回值: double
说明: 统计结果集中col字段的最大值

hive> select max(t) from tableName;
120

6、复合类型构建函数

1、Map类型构建: map

语法: map (key1, value1, key2, value2, …)
说明:根据输入的key和value对构建map类型

create table score_map(name string, score map<string,int>)
row format delimited fields terminated by '\t' 
collection items terminated by ',' map keys terminated by ':';

创建数据内容如下并加载数据
cd /kfly/install/hivedatas/
vim score_map.txt

zhangsan    数学:80,语文:89,英语:95
lisi    语文:60,数学:80,英语:99

加载数据到hive表当中去
load data local inpath '/kfly/install/hivedatas/score_map.txt' overwrite into table score_map;

map结构数据访问:
获取所有的value:
select name,map_values(score) from score_map;

获取所有的key:
select name,map_keys(score) from score_map;

按照key来进行获取value值
select name,score["数学"]  from score_map;

查看map元素个数
select name,size(score) from score_map;

2、Struct类型构建: struct

语法: struct(val1, val2, val3, …)
说明:根据输入的参数构建结构体struct类型,似于C语言中的结构体,内部数据通过X.X来获取,假设我们的数据格式是这样的,电影ABC,有1254人评价过,打分为7.4分

创建struct表
hive> create table movie_score( name string,  info struct<number:int,score:float> )row format delimited fields terminated by "\t"  collection items terminated by ":"; 

加载数据
cd /kfly/install/hivedatas/
vim struct.txt

ABC    1254:7.4  
DEF    256:4.9  
XYZ    456:5.4

加载数据
load data local inpath '/kfly/install/hivedatas/struct.txt' overwrite into table movie_score;


hive当中查询数据
hive> select * from movie_score;  
hive> select info.number,info.score from movie_score;  
OK  
1254    7.4  
256     4.9  
456     5.4  

3、array类型构建: array

语法: array(val1, val2, …)
说明:根据输入的参数构建数组array类型

hive> create table  person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

加载数据到person表当中去
cd /kfly/install/hivedatas/
vim person.txt

数据内容格式如下
biansutao    beijing,shanghai,tianjin,hangzhou
linan    changchu,chengdu,wuhan

加载数据
hive > load  data local inpath '/kfly/install/hivedatas/person.txt' overwrite into table person;

查询所有数据数据
hive > select * from person;

按照下表索引进行查询
hive > select work_locations[0] from person;

查询所有集合数据
hive  > select work_locations from person; 

查询元素个数
hive >  select size(work_locations) from person;   

7、复杂类型长度统计函数

1.Map类型长度函数: size(Map)

语法: size(Map)
返回值: int
说明: 返回map类型的长度

hive> select size(t) from map_table2;
2
2.array类型长度函数: size(Array)

语法: size(Array)
返回值: int
说明: 返回array类型的长度

hive> select size(t) from arr_table2;
4
3.类型转换函数

类型转换函数: cast
语法: cast(expr as )
返回值: Expected “=” to follow “type”
说明: 返回转换后的数据类型

hive> select cast('1' as bigint) from tableName;
1

8、hive当中的lateral view 与 explode以及reflect和分析函数

1、使用explode函数将hive表中的Map和Array字段数据进行拆分

lateral view用于和split、explode等UDTF一起使用的,能将一行数据拆分成多行数据,在此基础上可以对拆分的数据进行聚合,lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一行或者多行,lateral view在把结果组合,产生一个支持别名表的虚拟表。
其中explode还可以用于将hive一列中复杂的array或者map结构拆分成多行

需求:现在有数据格式如下
zhangsan    child1,child2,child3,child4    k1:v1,k2:v2
lisi    child5,child6,child7,child8     k3:v3,k4:v4

字段之间使用\t分割,需求将所有的child进行拆开成为一列

+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+

将map的key和value也进行拆开,成为如下结果

+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+
第一步:创建hive数据库

创建hive数据库

hive (default)> create database hive_explode;
hive (default)> use hive_explode;
第二步:创建hive表,然后使用explode拆分map和array
hive (hive_explode)> create  table hive_explode.t3(name string,children array<string>,address Map<string,string>) row format delimited fields terminated by '\t'  collection items    terminated by ','  map keys terminated by ':' stored as textFile;
第三步:加载数据

node03执行以下命令创建表数据文件

cd  /kfly/install/hivedatas/

vim maparray
数据内容格式如下

zhangsan    child1,child2,child3,child4    k1:v1,k2:v2
lisi    child5,child6,child7,child8    k3:v3,k4:v4

hive表当中加载数据

hive (hive_explode)> load data local inpath '/kfly/install/hivedatas/maparray' into table hive_explode.t3;

第四步:使用explode将hive当中数据拆开

将array当中的数据拆分开

hive (hive_explode)> SELECT explode(children) AS myChild FROM hive_explode.t3;

将map当中的数据拆分开

hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM hive_explode.t3;

2、使用explode拆分json字符串

需求:现在有一些数据格式如下:

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

其中字段与字段之间的分隔符是 |

我们要解析得到所有的monthSales对应的值为以下这一列(行转列)

4900
2090
6987

第一步:创建hive表
hive (hive_explode)> create table hive_explode.explode_lateral_view  (area string, goods_id string, sale_info string)  ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS textfile;
第二步:准备数据并加载数据

准备数据如下

cd /kfly/install/hivedatas
vim explode_json

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]



加载数据到hive表当中去

hive (hive_explode)> load data local inpath '/kfly/install/hivedatas/explode_json' overwrite into table hive_explode.explode_lateral_view;
第三步:使用explode拆分Array
hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from hive_explode.explode_lateral_view;
第四步:使用explode拆解Map
hive (hive_explode)> select explode(split(area,',')) as area from hive_explode.explode_lateral_view;
第五步:拆解json字段
hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as  sale_info from hive_explode.explode_lateral_view;

然后我们想用get_json_object来获取key为monthSales的数据:

hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as  sale_info from hive_explode.explode_lateral_view;


然后出现异常FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能写在别的函数内
如果你这么写,想查两个字段,select explode(split(area,',')) as area,good_id from explode_lateral_view;
会报错FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'
使用UDTF的时候,只支持一个字段,这时候就需要LATERAL VIEW出场了
3、配合LATERAL VIEW使用

配合lateral view查询多个字段

hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

其中LATERAL VIEW explode(split(goods_id,’,’))goods相当于一个虚拟表,与原表explode_lateral_view笛卡尔积关联。

也可以多重使用

hive (hive_explode)> select goods_id2,sale_info,area2 from explode_lateral_view  LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 LATERAL VIEW explode(split(area,','))area as area2;

也是三个表笛卡尔积的结果

最终,我们可以通过下面的句子,把这个json格式的一行数据,完全转换成二维表的方式展现

hive (hive_explode)> select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source, get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales, get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,  get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view   LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;

总结:

Lateral View通常和UDTF一起出现,为了解决UDTF不允许在select字段的问题。
Multiple Lateral View可以实现类似笛卡尔乘积。
Outer关键字可以把不输出的UDTF的空结果,输出成NULL,防止丢失数据。

9、列转行

1.相关函数说明

CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,…):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。

2.数据准备

表6-6 数据准备

name constellation blood_type
孙悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
冰冰 射手座 A
3.需求

把星座和血型一样的人归类到一起。结果如下:

射手座,A            老王|冰冰
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

4.创建本地constellation.txt,导入数据

node03服务器执行以下命令创建文件,注意数据使用\t进行分割

cd /kfly/install/hivedatas
vim constellation.txt
孙悟空    白羊座    A
老王    射手座    A
宋宋    白羊座    B       
猪八戒    白羊座    A
凤姐    射手座    A
5.创建hive表并导入数据

创建hive表并加载数据

hive (hive_explode)> create table person_info(  name string,  constellation string,  blood_type string)  row format delimited fields terminated by "\t";

加载数据

hive (hive_explode)> load data local inpath '/kfly/install/hivedatas/constellation.txt' into table person_info;
6.按需求查询数据
select t1.base, concat_ws('|', collect_set(t1.name)) name from    (select name, concat(constellation, "," , blood_type) base from person_info) t1 group by  t1.base;

10、行转列

1.函数说明

EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释:用于和split, explode等UDTF一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。

2.数据准备

数据内容如下,字段之间都是使用\t进行分割

cd /kfly/install/hivedatas

vim movie.txt
《疑犯追踪》    悬疑,动作,科幻,剧情
《Lie to me》    悬疑,警匪,动作,心理,剧情
《战狼2》    战争,动作,灾难
3.需求

将电影分类中的数组数据展开。结果如下:

《疑犯追踪》    悬疑
《疑犯追踪》    动作
《疑犯追踪》    科幻
《疑犯追踪》    剧情
《Lie to me》    悬疑
《Lie to me》    警匪
《Lie to me》    动作
《Lie to me》    心理
《Lie to me》    剧情
《战狼2》    战争
《战狼2》    动作
《战狼2》    灾难
4.创建hive表并导入数据

创建hive表

hive (hive_explode)> create table movie_info(movie string, category array<string>) row format delimited fields terminated by "\t" collection items terminated by ",";

加载数据

load data local inpath "/kfly/install/hivedatas/movie.txt" into table movie_info;
5.按需求查询数据
hive (hive_explode)>  select movie, category_name  from  movie_info lateral view explode(category) table_tmp as category_name;

11、reflect函数

reflect函数可以支持在sql中调用java中的自带函数,秒杀一切udf函数。

使用java.lang.Math当中的Max求两列中最大值

创建hive表

hive (hive_explode)>  create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';

准备数据并加载数据

cd /kfly/install/hivedatas

vim test_udf

1,2
4,3
6,4
7,5
5,6

加载数据

hive (hive_explode)> load data local inpath '/kfly/install/hivedatas/test_udf' overwrite into table test_udf;

使用java.lang.Math当中的Max求两列当中的最大值

hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;
不同记录执行不同的java内置函数

创建hive表

hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';

准备数据

cd /export/servers/hivedatas

vim test_udf2

java.lang.Math,min,1,2
java.lang.Math,max,2,3

加载数据

hive (hive_explode)> load data local inpath '/kfly/install/hivedatas/test_udf2' overwrite into table test_udf2;

执行查询

hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;
判断是否为数字

使用apache commons中的函数,commons下的jar已经包含在hadoop的classpath中,所以可以直接使用。

使用方式如下:

hive (hive_explode)> select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123");

12、hive当中的分析函数—分组求topN

1、分析函数的作用介绍

对于一些比较复杂的数据求取过程,我们可能就要用到分析函数,分析函数主要用于分组求topN,或者求取百分比,或者进行数据的切片等等,我们都可以使用分析函数来解决

2、常用的分析函数介绍

1、ROW_NUMBER():

从1开始,按照顺序,生成分组内记录的序列,比如,按照pv降序排列,生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多,再比如,获取分组内排序第一的记录;获取一个session中的第一条refer等。

2、RANK() :

生成数据项在分组中的排名,排名相等会在名次中留下空位

3、DENSE_RANK() :

生成数据项在分组中的排名,排名相等会在名次中不会留下空位

4、CUME_DIST :

小于等于当前值的行数/分组内总行数。比如,统计小于等于当前薪水的人数,所占总人数的比例

5、PERCENT_RANK :

分组内当前行的RANK值/分组内总行数

6、NTILE(n) :

用于将分组数据按照顺序切分成n片,返回当前切片值,如果切片不均匀,默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。

3、需求描述

现有数据内容格式如下,分别对应三个字段,cookieid,createtime ,pv,求取每个cookie访问pv前三名的数据记录,其实就是分组求topN,求取每组当中的前三个值

cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7g

第一步:创建数据库表

在hive当中创建数据库表

CREATE EXTERNAL TABLE cookie_pv (
cookieid string,
createtime string, 
pv INT
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' ;
第二步:准备数据并加载

node03执行以下命令,创建数据,并加载到hive表当中去

cd /kfly/install/hivedatas
vim cookiepv.txt

cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7

加载数据到hive表当中去

load  data  local inpath '/kfly/install/hivedatas/cookiepv.txt'  overwrite into table  cookie_pv 
第三步:使用分析函数来求取每个cookie访问PV的前三条记录
SELECT * FROM (
    SELECT 
  cookieid,
  createtime,
  pv,
  RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
  DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
  FROM cookie_pv) temp 
WHERE temp.rn1 <=  3 ;

13、hive自定义函数

1、自定义函数的基本介绍

1)Hive 自带了一些函数,比如:max/min等,但是数量有限,自己可以通过自定义UDF来方便的扩展。

2)当Hive提供的内置函数无法满足你的业务处理需要时,此时就可以考虑使用用户自定义函数(UDF:user-defined function)。

3)根据用户自定义函数类别分为以下三种:

    (1)UDF(User-Defined-Function)

            一进一出

    (2)UDAF(User-Defined Aggregation Function)

            聚集函数,多进一出

            类似于:count/max/min

    (3)UDTF(User-Defined Table-Generating Functions)

            一进多出

            如lateral view explode()

4)官方文档地址

https://cwiki.apache.org/confluence/display/Hive/HivePlugins

5)编程步骤:

    (1)继承org.apache.hadoop.hive.ql.UDF

    (2)需要实现evaluate函数;evaluate函数支持重载;

6)注意事项

    (1)UDF必须要有返回类型,可以返回null,但是返回类型不能为void;

    (2)UDF中常用Text/LongWritable等类型,不推荐使用java类型;
2、自定义函数开发
第一步:创建maven java 工程,并导入jar包
<repositories>
    <repository>
        <id>cloudera</id>
 <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.6.0-cdh5.14.2</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.1.0-cdh5.14.2</version>
    </dependency>
</dependencies>
<build>
<plugins>
    <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.0</version>
        <configuration>
            <source>1.8</source>
            <target>1.8</target>
            <encoding>UTF-8</encoding>
        </configuration>
    </plugin>
     <plugin>
         <groupId>org.apache.maven.plugins</groupId>
         <artifactId>maven-shade-plugin</artifactId>
         <version>2.2</version>
         <executions>
             <execution>
                 <phase>package</phase>
                 <goals>
                     <goal>shade</goal>
                 </goals>
                 <configuration>
                     <filters>
                         <filter>
                             <artifact>*:*</artifact>
                             <excludes>
                                 <exclude>META-INF/*.SF</exclude>
                                 <exclude>META-INF/*.DSA</exclude>
                                 <exclude>META-INF/*/RSA</exclude>
                             </excludes>
                         </filter>
                     </filters>
                 </configuration>
             </execution>
         </executions>
     </plugin>
</plugins>
</build>
第二步:开发java类继承UDF,并重载evaluate 方法
public class MyUDF extends UDF {
     public Text evaluate(final Text s) {
         if (null == s) {
             return null;
         }
         //**返回大写字母         return new Text(s.toString().toUpperCase());
     }
 }
第三步:将我们的项目打包,并上传到hive的lib目录下

使用maven的package进行打包,将我们打包好的jar包上传到node03服务器的/kfly/install/hive-1.1.0-cdh5.14.2/lib 这个路径下

第四步:添加我们的jar包

重命名我们的jar包名称

cd /kfly/install/hive-1.1.0-cdh5.14.2/lib
mv original-day_hive_udf-1.0-SNAPSHOT.jar udf.jar

hive的客户端添加我们的jar包

0: jdbc:hive2://node03:10000> add jar /kfly/install/hive-1.1.0-cdh5.14.2/lib/udf.jar;
第五步:设置函数与我们的自定义函数关联
0: jdbc:hive2://node03:10000> create temporary function tolowercase as 'com.kfly.udf.MyUDF';
第六步:使用自定义函数
0: jdbc:hive2://node03:10000>select tolowercase('abc');

hive当中如何创建永久函数

在hive当中添加临时函数,需要我们每次进入hive客户端的时候都需要添加以下,退出hive客户端临时函数就会失效,那么我们也可以创建永久函数来让其不会失效

创建永久函数

1、指定数据库,将我们的函数创建到指定的数据库下面
0: jdbc:hive2://node03:10000>use myhive;

2、使用add jar添加我们的jar包到hive当中来
0: jdbc:hive2://node03:10000>add jar /kfly/install/hive-1.1.0-cdh5.14.2/lib/udf.jar;

3、查看我们添加的所有的jar包
0: jdbc:hive2://node03:10000>list  jars;

4、创建永久函数,与我们的函数进行关联
0: jdbc:hive2://node03:10000>create  function myuppercase as 'com.kfly.udf.MyUDF';

5、查看我们的永久函数
0: jdbc:hive2://node03:10000>show functions like 'my*';

6、使用永久函数
0: jdbc:hive2://node03:10000>select myhive.myuppercase('helloworld');

7、删除永久函数
0: jdbc:hive2://node03:10000>drop function myhive.myuppercase;

8、查看函数
 show functions like 'my*';

3. hive表的数据压缩

1、数据的压缩说明

  • 压缩模式评价

    • 可使用以下三种标准对压缩方式进行评价
      • 1、压缩比:压缩比越高,压缩后文件越小,所以压缩比越高越好
      • 2、压缩时间:越快越好
      • 3、已经压缩的格式文件是否可以再分割:可以分割的格式允许单一文件由多个Mapper程序处理,可以更好的并行化
  • 常见压缩格式

压缩方式 压缩比 压缩速度 解压缩速度 是否可分割
gzip 13.4% 21 MB/s 118 MB/s
bzip2 13.2% 2.4MB/s 9.5MB/s
lzo 20.5% 135 MB/s 410 MB/s
snappy 22.2% 172 MB/s 409 MB/s
  • Hadoop编码/解码器方式
压缩格式 对应的编码/解码器
DEFLATE org.apache.hadoop.io.compress.DefaultCodec
Gzip org.apache.hadoop.io.compress.GzipCodec
BZip2 org.apache.hadoop.io.compress.BZip2Codec
LZO com.hadoop.compress.lzo.LzopCodec
Snappy org.apache.hadoop.io.compress.SnappyCodec
 压缩性能的比较
压缩算法 原始文件大小 压缩文件大小 压缩速度 解压速度
gzip 8.3GB 1.8GB 17.5MB/s 58MB/s
bzip2 8.3GB 1.1GB 2.4MB/s 9.5MB/s
LZO 8.3GB 2.9GB 49.3MB/s 74.6MB/s

http://google.github.io/snappy/

On a single core of a Core i7 processor in 64-bit mode, Snappy compresses at about 250 MB/sec or more and decompresses at about 500 MB/sec or more.

2、压缩配置参数

要在Hadoop中启用压缩,可以配置如下参数(mapred-site.xml文件中):

参数 默认值 阶段 建议
io.compression.codecs (在core-site.xml中配置) org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec, org.apache.hadoop.io.compress.Lz4Codec 输入压缩 Hadoop使用文件扩展名判断是否支持某种编解码器
mapreduce.map.output.compress false mapper输出 这个参数设为true启用压缩
mapreduce.map.output.compress.codec org.apache.hadoop.io.compress.DefaultCodec mapper输出 使用LZO、LZ4或snappy编解码器在此阶段压缩数据
mapreduce.output.fileoutputformat.compress false reducer输出 这个参数设为true启用压缩
mapreduce.output.fileoutputformat.compress.codec org.apache.hadoop.io.compress. DefaultCodec reducer输出 使用标准工具或者编解码器,如gzip和bzip2
mapreduce.output.fileoutputformat.compress.type RECORD reducer输出 SequenceFile输出使用的压缩类型:NONE和BLOCK

3、开启Map输出阶段压缩

开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下:

案例实操:

1)开启hive中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true;

2)开启mapreduce中map输出压缩功能
hive (default)>set mapreduce.map.output.compress=true;

3)设置mapreduce中map输出数据的压缩方式
hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;

4)执行查询语句
   select count(1) from score;

4、 开启Reduce输出阶段压缩

当Hive将输出写入到表中时,输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false,这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true,来开启输出结果压缩功能。

案例实操:

1)开启hive最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true;

2)开启mapreduce最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true;

3)设置mapreduce最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

4)设置mapreduce最终数据输出压缩为块压缩
hive (default)>set mapreduce.output.fileoutputformat.compress.type=BLOCK;

5)测试一下输出结果是否是压缩文件
insert overwrite local directory '/kfly/install/hivedatas/snappy' select * from score distribute by s_id sort by s_id desc;