目录
1、hive参数传递
- 实际工作当中,我们一般都是将hive的hql语法开发完成之后,就写入到一个脚本里面去,然后定时的通过命令 hive -f 去执行hive的语法即可
- 然后通过定义变量来传递参数到hive的脚本当中去,那么我们接下来就来看看如何使用hive来传递参数。
- hive0.9以及之前的版本是不支持传参
- hive1.0版本之后支持 hive -f 传递参数
- 在hive当中我们一般可以使用==hivevar==或者==hiveconf==来进行参数的传递
1、hiveconf使用说明
- hiveconf用于定义HIVE 执行上下文的属性(配置参数),可覆盖覆盖hive-site.xml(hive-default.xml)中的参数值,如用户执行目录、日志打印级别、执行队列等。例如我们可以使用hiveconf来覆盖我们的hive属性配置,
- hiveconf变量取值必须要使用hiveconf作为前缀参数,具体格式如下:
${hiveconf:key}
bin/hive --hiveconf "mapred.job.queue.name=root.default"
2、 hivevar使用说明
- hivevar用于定义HIVE 运行时的变量替换,类似于JAVA中的”PreparedStatement”,与${key}配合使用或者与 ${hivevar:key}
- 对于hivevar取值可以不使用前缀hivevar,具体格式如下:
-- 使用前缀:
${hivevar:key}
-- 不使用前缀:
${key}
hive --hivevar name=zhangsan
${hivevar:name}
也可以这样取值 ${name}
1.3.3 define使用说明
- define与hivevar用途完全一样,还有一种简写”-d
hive --hiveconf "mapred.job.queue.name=root.default" -d my="201912" --database myhive
-- 执行SQL
hive > select * from myhive.score2 where concat(year, month) = ${my} limit 5;
1.3.4 hiveconf与hivevar使用实战
- 需求:hive当中执行以下hql语句,并将’201807’、’80’、’03’用参数的形式全部都传递进去
select * from student left join score on student.s_id = score.s_id where score.month = '201807' and score.s_score > 80 and score.c_id = 03;
第一步:创建student表并加载数据
hive (myhive)> create external table student
(s_id string, s_name string, s_birth string, s_sex string) row format delimited
fields terminated by '\t';
hive (myhive)> load data local inpath '/kkb/install/hivedatas/student.csv' overwrite into table student;
第二步:定义hive脚本
cd /kkb/instal/hivedatas
vim hivevariable.hql
use myhive;
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};
第三步:调用hive脚本并传递参数
- node03执行以下命令并
hive --hiveconf month=201912 --hivevar s_score=80 --hivevar c_id=03 -f /kkb/install/hivedatas/hivevariable.hql
2、Hive的常用函数
1、系统内置函数
1.查看系统自带的函数
hive> show functions;
2.显示自带的函数的用法
hive> desc function upper;
3.详细显示自带的函数的用法
hive> desc function extended upper;
2、数值计算
下面主要使用teacher展示
select * from teacher;
1、取整函数: round
- 语法: round(double a)
- 返回值: BIGINT
- 说明: 返回double类型的整数值部分 (遵循四舍五入)
hive> select round(3.1415926) from teacher;
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
3、日期函数
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 datetime)
- 返回值: string
- 说明: 返回日期时间字段中的日期部分。
hive> select year(‘2011-12-08 10:03:01’) from tableName;
2011
hive> select year(‘2012-12-08’) from tableName;
2012
6、日期转年函数: year、 mouth、day、hour、minute、语法一样
- 语法: year(string date)
- 返回值: int
- 说明: 返回日期中的年。
select weekofyear(‘2011-12-08 10:03:01’) from teacher;
7、日期转周函数: weekofyear
select weekofyear(‘2022-7-04 10:03:01’) from teacher;
8、日期比较函数: datediff
- 语法: datediff(string enddate, string startdate)
- 返回值: int
- 说明: 返回结束日期减去开始日期的天数。
select datediff(‘2012-12-08′,’2012-12-07’) from teacher;
9、日期增加函数: date_add
- 语法: date_add(string startdate, int days)
- 返回值: string
- 说明: 返回开始日期startdate增加days天后的日期。
hive> select date_add(‘2012-12-08’,10) from tableName;
2012-12-18
10、日期减少函数: 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
select if(1=2,100,200) from teacher;
2、非空查找函数: COALESCE
- 语法: COALESCE(T v1, T v2, …)
- 返回值: T
- 说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL
select COALESCE(null,’100′,’50’) from teacher;
3、条件判断函数:CASE
- 语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
- 返回值: T
- 说明:如果a等于b,那么返回c;如果a等于d,那么返回e;否则返回f
select case 100 when 50 then ‘tom’ when 100 then ‘mary’ else ‘tim’ end from teacher;
Select case 200 when 50 then ‘tom’ when 100 then ‘mary’ else ‘tim’ end from teacher;
4、条件判断函数:CASE
- 语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
- 返回值: T
- 说明:如果a为TRUE,则返回b;如果c为TRUE,则返回d;否则返回e
select case when 1=2 then ‘tom’ when 2=2 then ‘mary’ else ‘tim’ end from teacher;
4、字符串函数
1、字符串长度函数:length
- 语法: length(string A)
- 返回值: int
- 说明:返回字符串A的长度
select length(‘abcedfg’) from teacher;
2、字符串反转函数:reverse
- 语法: reverse(string A)
- 返回值: string
- 说明:返回字符串A的反转结果
select reverse(‘abcedfg’) from tableName;
gfdecba
3、字符串连接函数:concat
- 语法: concat(string A, string B…)
- 返回值: string
- 说明:返回输入字符串连接后的结果,支持任意个输入字符串
select concat(‘abc’,’def’,’gh’) from tableName;
abcdefgh
4、字符串连接并指定字符串分隔符:concat_ws
- 语法: concat_ws(string SEP, string A, string B…)
- 返回值: string
- 说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符
select concat_ws(‘,’,’abc’,’def’,’gh’) from tableName;
abc,def,ghselect concat_ws(‘——’,’abc’,’def’,’gh’) from teacher;
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’,-3,2) from tableName;
cdselect substr(‘jianghaojie’,3,8) from teacher;
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(‘ ab c ‘) from tableName;
ab c
10、url解析函数 parse_url
- 语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
- 返回值: string
- 说明:返回URL中指定的部分。partToExtract的有效值为:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
select parse_url
(‘https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’)
from teacher;
select parse_url
(‘https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’)
from teacher;
v1
11、json解析 get_json_object
- 语法: get_json_object(string json_string, string path)
- 返回值: string
- 说明:解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。
select get_json_object(‘{“store”:{“fruit”:[{“weight”:8,”type”:”apple”},{“weight”:9,”type”:”pear”}], “bicycle”:{“price”:19.95,”color”:”red”} },”email”:”amy@only_for_json_udf_test.net”,”owner”:”amy”}’,’$.owner’) from teacher;
12、重复字符串函数:repeat
- 语法: repeat(string str, int n)
- 返回值: string
- 说明:返回重复n次后的str字符串
select repeat(‘abc’, 5) from teacher;
13、分割字符串函数: split
- 语法: split(string str, string pat)
- 返回值: array
- 说明: 按照pat字符串分割str,会返回分割后的字符串数组
select split(‘abtcdtef’,’t’) from teacher;
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
- 语法: max(col)
- 返回值: 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
— 每列数据换行隔开
row format delimited fields terminated by ‘\t’
— map数据集合之间,逗号隔开数据
collection items terminated by ‘,’
— map数据之间冒号隔开
map keys terminated by ‘:’;
创建数据内容如下并加载数据
cd /kkb/install/hivedatas/
vim score_map.txtload data local inpath ‘/kkb/install/hivedatas/score_map.txt’ overwrite into table score_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;
构建一个map
select map(1, ‘zs’, 2, ‘lisi’);
2、Struct类型构建: struct
- 语法: struct(val1, val2, val3, …)
- 说明:根据输入的参数构建结构体struct类型,似于C语言中的结构体,内部数据通过X.X来获取,假设我
- 数据格式是这样的,电影ABC,有1254人评价过,打分为7.4分
建表
create table movie_score(name string, info struct
创建数据
cd /kkb/install/hivedatas/
vim struct.txt
电影ABC,有1254人评价过,打分为7.4分
ABC 1254:7.4
DEF 256:4.9
XYZ 456:5.4
hive加载数据
load data local inpath ‘/kkb/install/hivedatas/struct.txt’ overwrite into table movie_score;
查询数据 1
select * from movie_score;
select info.number, info.score from movie_score;
3、Array类型构建: array
- 语法: array(val1, val2, …)
- 说明:根据输入的参数构建数组array类型
创建表
create table person(name string, work_locations array
创建数据
— 加载数据到person表当中去
cd /kkb/install/hivedatas/
vim person.txt
— 数据内容格式如下
biansutao beijing,shanghai,tianjin,hangzhou
linan changchun,chengdu,wuhan
加载数据
load data local inpath ‘/kkb/install/hivedatas/person.txt’ overwrite into table person;
展示数据
select * from person;
— 按照下表索引进行查询
select work_locations[0] from person;
— 查询所有集合数据
select work_locations from person;
— 查询元素个数
select size(work_locations) from person;
7、复杂类型长度统计函数
1、Map类型长度函数: size(Map
- 语法: size(Map
- 返回值: int
- 说明: 返回map类型的长度
select size(map(1, ‘zs’, 2, ‘anzhulababy’)) from teacher;
2、array类型长度函数: size(Array
- 语法: size(Array
- 返回值: int
- 说明: 返回array类型的长度
select size(t) from arr_table2;
4
3、类型转换函数
- 类型转换函数: cast
- 语法: cast(expr as
- 返回值: Expected “=” to follow “type”
- 说明: 返回转换后的数据类型
select cast(‘1’ as bigint) from tableName;
1
8、行转列
1、相关函数说明
- CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
- CONCAT_WS(separator, str1, str2,…):它是一个特殊形式的 CONCAT()。
- 第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。
- 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
- COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生 array类型字段。
2、数据准备
- 数据准备
nameconstellationblood_type孙悟空白羊座A老王射手座A宋宋白羊座B猪八戒白羊座A按住啦baby射手座A
cd /kkb/install/hivedatas
vim constellation.txt孙悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
3、需求
- 把星座和血型一样的人归类到一起。结果如下:
射手座,A 老王|按住啦baby
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
4、建表并导入数据
create table person_info(name string, constellation string, blood_type string) row format delimited fields terminated by “\t”;
加载数据
load data local inpath ‘/kkb/install/hivedatas/constellation.txt’ into table person_info;
查询数据
select * from person_info;
5、合并并查询
合并内容
select name, concat(constellation, “,” , blood_type) base from person_info
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;
9、列转行
1、函数说明
- EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
- LATERAL VIEW
- 用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
- 解释:用于和split, explode等UDTF一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
2、数据准备
- 数据内容如下,字段之间都是使用\t进行分割
cd /kkb/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表
create table movie_info(movie string, category array
加载数据
load data local inpath “/kkb/install/hivedatas/movie.txt” into table movie_info;
查询数据
select * from movie_info;
select movie, category_name from movie_info
lateral view explode(category) table_tmp as category_name;
2、lateral view、explode练习
1、explode函数将Map和Array字段数据进行拆分
- explode还可以用于将hive一列中复杂的array或者map结构拆分成多行
第一步、建表
create table hive_explode.t3
(name string, children array
存储格式指定textFile;默认也是textFile格式
stored as textFile;
hive⽂件存储格式包括以下⼏类:
(1)、TEXTFILE
(2) 、SEQUENCEFILE
(3) 、RCFILE
(4) .ORCFILE(0.11以后出现)
(5) 、PARQUET
说明:
其中TEXTFILE为默认格式,建表时不指定默认为这个格式,导入数据时会直接把数据文件拷贝到hdfs上不进行处理;SEQUENCEFILE,RCFILE,ORCFILE.PARQUET格式的表不能直接从本地文件导入数据,数据要先导入到textfile格式的表中,然后再从表中用insert导入SequenceFle,RCFile,ORCFile,PARQUET各自表中;或者用复制表结构及数据的方式( create table as select * fromtable ) 。
一言以蔽之:如果为textfile的文件格式,直接load就OK,不需要走MapReduce;如果是其他的类型就需要走MapReduce了,因为其他的类型都涉及到了文件的压缩,这需要借助MapReduce的压缩方式来实现。
第二步、创建并加载数据
cd /kkb/install/hivedatas/
vim maparray
— 数据内容格式如下
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4load data local inpath ‘/kkb/install/hivedatas/maparray’ into table hive_explode.t3;
第三步:使用explode将hive当中数据拆开
array使用explode拆分
SELECT explode(children) AS myChild FROM hive_explode.t3;
explode将map当中的数据拆分开
SELECT explode(address) AS (myMapKey, myMapValue) FROM hive_explode.t3;
2、使用explode拆分json字符串
第一步、创建表
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 /kkb/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″}]
load data local inpath ‘/kkb/install/hivedatas/explode_json’ overwrite into table hive_explode.explode_lateral_view;
select explode(split(goods_id, ‘,’)) as goods_id from hive_explode.explode_lateral_view;
select explode(split(area, ‘,’)) as area from hive_explode.explode_lateral_view;
第五步:拆解json字段
select explode(split(regexp_replace(regexp_replace(sale_info,’\[\{‘,”),’}]’,”),’},\{‘)) as sale_info from hive_explode.explode_lateral_view;
配合LATERAL VIEW使用
- lateral view用于和split、explode等UDTF一起使用的,能将一行数据拆分成多行数据
- 在此基础上可以对拆分的数据进行聚合
- lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一行或者多行,lateral view在把结果组合,产生一个支持别名表的 虚拟表。
- 配合lateral view查询多个字段
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 笛卡尔积关联。
- 也可以多重使用,如下,也是三个表笛卡尔积的结果
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;
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 userCount, get_json_object(concat(‘{‘,sale_info_1,’}’),’$.score’) as score
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,防止丢失数据。
reflect函数
- reflect函数可以支持在sql中调用java中的自带函数,秒杀一切udf函数。
1、使用java.lang.Math当中的Max求两列中最大值
创建表
create table test_udf(col1 int,col2 int)
row format delimited fields terminated by ‘,’;
创建数据并添加
cd /kkb/install/hivedatas
vim test_udf
1,2
4,3
6,4
7,5
5,6
load data local inpath ‘/kkb/install/hivedatas/test_udf’ overwrite into table test_udf;
使用java.lang.Math当中的Max求两列当中的最大值
select reflect(“java.lang.Math”,”max”, col1, col2) from test_udf;
2、不同记录执行不同的java内置函数
建表
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
执行查询
select reflect(class_name, method_name, col1, col2) from test_udf2;
3、判断是否为数字
- 使用apache commons中的函数,commons下的jar已经包含在hadoop的classpath中,所以可以直接使用。
- 使用方式如下:
select reflect(“org.apache.commons.lang.math.NumberUtils”, “isNumber”, “123”);
hive自定义函数
1、自定义函数的基本介绍
- Hive 自带了一些函数,比如:max/min等,但是数量有限,自己可以通过自定义UDF来方便的扩展。
- 当Hive提供的内置函数无法满足你的业务处理需要时,此时就可以考虑使用用户自定义函数(UDF:user-defined function)
- 根据用户自定义函数类别分为以下三种:
- UDF(User-Defined-Function) 一进一出
- UDAF(User-Defined Aggregation Function) 聚集函数,多进一出,类似于:count/max/min
- UDTF(User-Defined Table-Generating Functions) 一进多出,如lateral view explode()
如lateral view explode()
(1)继承org.apache.hadoop.hive.ql.UDF
(2)需要实现evaluate函数;evaluate函数支持重载;
- 注意事项
(1)UDF必须要有返回类型,可以返回null,但是返回类型不能为void;
(2)UDF中常用Text/LongWritable等类型,不推荐使用java类型;
1、新建项目并导包
cloudera
https://repository.cloudera.com/artifactory/cloudera-repos/
org.apache.hadoop
hadoop-client
2.6.0-mr1-cdh5.14.2
org.apache.hadoop
hadoop-common
2.6.0-cdh5.14.2
org.apache.hadoop
hadoop-hdfs
2.6.0-cdh5.14.2
org.apache.hadoop
hadoop-mapreduce-client-core
2.6.0-cdh5.14.2
org.apache.hive
hive-exec
1.1.0-cdh5.14.2
org.apache.hive
hive-jdbc
1.1.0-cdh5.14.2
org.apache.hive
hive-cli
1.1.0-cdh5.14.2
org.apache.maven.plugins
maven-compiler-plugin
3.0
1.8
1.8
UTF-8
第二步:开发java类继承UDF,并重载evaluate 方法
package com.kkb.udf.MyUDF;
public class MyUDF extends UDF {
public Text evaluate(final Text s) {
if (null == s) {
return null;
}
//返回大写字母
return new Text(s.toString().toUpperCase());
}
}
第四步:添加我们的jar包
- 重命名我们的jar包名称
cd /kkb/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 /kkb/install/hive-1.1.0-cdh5.14.2/lib/udf.jar;
第五步:设置函数与我们的自定义函数关联
0: jdbc:hive2://node03:10000> create temporary function touppercase as 'com.kkb.udf.MyUDF';
第六步:使用自定义函数
0: jdbc:hive2://node03:10000>select touppercase ('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 /kkb/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.kkb.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‘;
Json数据解析UDF开发练习
数据如下
{“movie”:”1193″,”rate”:”5″,”timeStamp”:”978300760″,”uid”:”1″}
{“movie”:”661″,”rate”:”3″,”timeStamp”:”978302109″,”uid”:”1″}
{“movie”:”914″,”rate”:”3″,”timeStamp”:”978301968″,”uid”:”1″}
{“movie”:”3408″,”rate”:”4″,”timeStamp”:”978300275″,”uid”:”1″}
{“movie”:”2355″,”rate”:”5″,”timeStamp”:”978824291″,”uid”:”1″}
{“movie”:”1197″,”rate”:”3″,”timeStamp”:”978302268″,”uid”:”1″}
{“movie”:”1287″,”rate”:”5″,”timeStamp”:”978302039″,”uid”:”1″}
需要的结果如下
创建表
create table jsontest (data string);
创建数据并导入
cd /kkb/install/hivedatas
vim jsondata
输入上面数据
导入数据
load data local inpath ‘/kkb/install/hivedatas/jsondata’ overwrite into table jsontest;
查询数据
select get_json_object(data,’$.movie’) as movie ,
get_json_object(data,’$.rate’) as rate ,
get_json_object(data,’$.timeStamp’) as timestamp,
get_json_object(data,’$.uid’) as uid
from jsontest;
Hive的分桶表
分桶表原理
- 分桶是相对分区进行更细粒度的划分
- Hive表或分区表可进一步的分桶
- ==分桶将整个数据内容按照某列取hash值,对桶的个数取模的方式决定该条记录存放在哪个桶当中;具有相同hash值的数据进入到同一个文件中==
- 比如按照name属性分为3个桶,就是对name属性值的hash值对3取摸,按照取模结果对数据分桶。
- 取模结果为==0==的数据记录存放到一个文件
- 取模结果为==1==的数据记录存放到一个文件
- 取模结果为==2==的数据记录存放到一个文件
作用
- 1、取样sampling更高效。没有分桶的话需要扫描整个数据集。
- 2、提升某些查询操作效率,例如map side join
案例演示:创建分桶表
- 在创建分桶表之前要执行的命令
- ==set hive.enforce.bucketing=true;== 开启对分桶表的支持
- ==set mapreduce.job.reduces=4;== 设置与桶相同的reduce个数(默认只有一个reduce)
- 进入hive客户端然后执行以下命令
use myhive;
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=4;
— 创建分桶表
create table myhive.user_buckets_demo(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by ‘\t’;
— 创建普通表
create table user_demo(id int, name string)
row format delimited fields terminated by ‘\t’;
加载数据到普通表 user_demo 中
load data local inpath ‘/kkb/install/hivedatas/user_bucket.txt’ overwrite into table user_demo;
加载数据到桶表user_buckets_demo中
insert into table user_buckets_demo select * from user_demo;
如果导入出现,权限不足的错误
(org.apache.hadoop.security.AccessControlException): Permission denied: user=hadoop, access=EXECUTE, inode=”/tmp”:root:supergroup:drwxrwx–
给用户赋予权限
hadoop fs -chown -R hadoop:root /tmp
可以查看到该模块有四个分区
下载第一个查看分通的内容
查看所有内容
select * from user_buckets_demo;
抽样查询桶表的数据
- 官网地址
- tablesample抽样语句语法:tablesample(bucket x out of y)
- x表示从第几个桶开始做数据采样
- y与进行采样的桶数的个数、每个采样桶的采样比例有关;
select * from user_buckets_demo ;
需要采样的总桶数 = 分桶数/y = 结果ret
分两种情况
情况一:ret>1
需要采样的总桶数 = 分桶数/y = 4/2 = 2个
即从2个桶进行数据的采样
x = 1 先从第1个桶中取出数据
x+y = 1+2 = 3 再从第3个桶中取出数据
情况二:ret
Hive数据导入
1、直接向表中插入数据(强烈不推荐使用)
create table score3 like score;
insert into table score3 partition(month =’201807′) values (‘001′,’002′,’100’);
2、load加载
语法如下
load data [local] inpath ‘dataPath’ [overwrite] into table student [partition (partcol1=val1,…)];
通过load方式加载数据
load data local inpath ‘/kkb/install/hivedatas/score.csv’ overwrite into table score partition(month=’201806′);
3、 通过查询加载数据(必须掌握)
- 通过查询方式加载数据
- 语法;官网地址
hive (myhive)> create table score5 like score;
hive (myhive)> insert overwrite table score5 partition(month = ‘201806’) select s_id,c_id,s_score from score;create table score6 as select * from score;
4、创建表时指定location
create external table score7 (s_id string,c_id string,s_score int) row format delimited fields terminated by ‘\t’ location ‘/myscore7’;
上传数据到hdfs上,我们也可以直接在hive客户端下面通过dfs命令来进行操作hdfs的数据
hive (myhive)> dfs -mkdir -p /myscore7;
hive (myhive)> dfs -put /kkb/install/hivedatas/score.csv /myscore7;
5、export导出与import 导入 hive表数据(内部表操作)
hive (myhive)> create table teacher2 like teacher;
— 导出到hdfs路径
hive (myhive)> export table teacher to ‘/kkb/teacher’;
hive (myhive)> import table teacher2 from ‘/kkb/teacher’;
Hive数据导出
insert 导出
- 表 -> 文件
- 官方文档
- 语法
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
SELECT ... FROM ...
- 将查询的结果导出到本地
insert overwrite local directory '/kkb/install/hivedatas/stu' select * from stu;
- 将查询的结果 格式化导出到本地
insert overwrite local directory '/kkb/install/hivedatas/stu2' row format delimited fields terminated by ',' select * from stu
- 将查询的结果导出到HDFS上==(没有local)==
insert overwrite directory '/kkb/hivedatas/stu' row format delimited fields terminated by ',' select * from stu;
Hive Shell 命令导出
基本语法:
- hive -e “sql语句” > file
- hive -f sql文件 > file
- 在linux命令行中,运行如下命令;导出myhive.stu表的数据到本地磁盘文件/kkb/install/hivedatas/student1.txt
hive -e ‘select * from myhive.stu;’ > /kkb/install/hivedatas/student1.txt
export导出到HDFS上
export table myhive.stu to ‘/kkb/install/hivedatas/stuexport’;
Hive的静态分区和动态分区
1、 静态分区
第一步、创建静态分区表
create table order_partition(order_number string,order_price double,order_time string ) partitioned BY(month string) row format delimited fields terminated by ‘\t’;
第二步、创建数据并导入
cd /kkb/install/hivedatas
vim order.txt
10001 100 2019-03-02
10002 200 2019-03-02
10003 300 2019-03-02
10004 400 2019-03-03
10005 500 2019-03-03
10006 600 2019-03-03
10007 700 2019-03-04
10008 800 2019-03-04
10009 900 2019-03-04
第三步、加载数据到分区表,指定分区,不灵活
load data local inpath ‘/kkb/install/hivedatas/order.txt’ overwrite into table order_partition partition(month=’2019-03′);
第四步、根据分区查询数据
select * from order_partition where month=’2019-03′;
2、动态分区
曲线救国
- 按照需求实现把数据自动导入到表的相应分区中,==不需要手动指定分区字段的值==
- *需求:根据分区字段不同的值,自动将数据导入到分区表不同的分区中
–创建普通表
create table t_order (order_number string, order_price double, order_time string) row format delimited fields terminated by ‘\t’;
–创建目标分区表
create table order_dynamic_partition(
order_number string,
order_price double
)partitioned BY(order_time string)
row format delimited fields terminated by ‘\t’;
准备数据
cd /kkb/install/hivedatas
vim order_partition.txt
10001 100 2019-03-02
10002 200 2019-03-02
10003 300 2019-03-02
10004 400 2019-03-03
10005 500 2019-03-03
10006 600 2019-03-03
10007 700 2019-03-04
10008 800 2019-03-04
10009 900 2019-03-04
加载数据
load data local inpath ‘/kkb/install/hivedatas/order_partition.txt’ overwrite into table t_order;
— 要想进行动态分区,需要设置参数
— 开启动态分区功能
hive> set hive.exec.dynamic.partition=true;
— 设置hive为非严格模式
hive> set hive.exec.dynamic.partition.mode=nonstrict;
hive> insert into table order_dynamic_partition partition(order_time) select order_number, order_price, order_time from t_order;
查看order_dynamic_partition 中的数据
show partitions order_dynamic_partition;
分组
1、Group By 语句
- Group By语句通常会和==聚合函数==一起使用,按照一个或者多个列对结果进行分组,然后对每个组执行聚合操作。
- Group By时select后的字段要么是分组字段、要么是聚合函数
- ==查询非分组字段会报错==
- 案例实操:
- 计算每个学生的平均分数
select s_id, avg(s_score) from score group by s_id;
- 计算每个学生最高的分数
select s_id, max(s_score) from score group by s_id;
2、 Having语句
- having 与 where 不同点
- where针对==表中的列发挥作用==,查询数据;==having针对查询结果中的列==发挥作用,筛选数据
- where后面==不能写聚合函数==,而having后面可以==使用聚合函数==
- having只用于group by分组统计语句
- 案例实操
- 求每个学生的平均分数
select s_id, avg(s_score) from score group by s_id;
- 求每个学生平均分数大于60的人
select s_id, avg(s_score) as avgScore from score group by s_id having avgScore > 60;
等价于
select s_id, avg(s_score) as avgScore from score group by s_id having avg(s_score) > 60;
排序
1、order by 全局排序
- 全局排序,只有一个reduce
- 使用 ORDER BY 子句排序
- asc ( ascend) 升序 (默认)
- desc (descend) 降序
- order by 子句在select语句的结尾
- 案例实操
- 查询学生的成绩,并按照分数降序排列
select * from score s order by s_score desc ;
按照别名排序
- 按照学生分数的平均值排序
select s_id, avg(s_score) avgscore
from score
group by s_id
order by
avgscore
desc;
2、 Sort By
每个MapReduce内部排序(Sort By)局部排序
- sort by:每个reducer内部有序排序(局部有序),对全局结果集来说并非全局有序
设置reduce个数
set mapreduce.job.reduces=3;
user_demo 原数据为
查询成绩按照成绩降序排列
select * from user_demo s sort by s.id
insert overwrite local directory ‘/kkb/install/hivedatas/sort’ select * from score s sort by s.s_score;
导出文件有3个,因为设置mapreduce个数为3
打开第一个文件,显示局部有序
3、distribute by 分区排序
- distribute by:
- 类似MR中partition,==采集hash算法,在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中==。
- 结合sort by使用。
- 注意
- Hive要求 distribute by 语句要写在 sort by 语句之前。
- 案例实操
- 先按照学生 sid 进行分区,再按照学生成绩进行排序
- 设置reduce的个数
set mapreduce.job.reduces=3;
- 通过distribute by 进行数据的分区,,将不同的sid 划分到对应的reduce当中去
insert overwrite local directory '/kkb/install/hivedatas/distribute' select * from score distribute by s_id sort by s_score;
4、 cluster by
- 当distribute by和sort by字段相同时,可以使用cluster by方式代替
- 除了distribute by 的功能外,还会对该字段进行排序,所以cluster by = distribute by + sort by
*
--以下两种写法等价
insert overwrite local directory '/kkb/install/hivedatas/distribute_sort' select * from score distribute by s_score sort by s_score;
insert overwrite local directory '/kkb/install/hivedatas/cluster' select * from score cluster by s_score;
Original: https://blog.csdn.net/weixin_43288858/article/details/125562745
Author: 捡黄金的少年
Title: hive参数传递,hive常用函数
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/817589/
转载文章受原作者版权保护。转载请注明原作者出处!