Hive高阶应用(一)
UDTF
什么是UDTF(User-Defined Table-Generating Functions )?
顾名思义，输入一行输出多行的函数，称之为UDTF函数，也叫表生成函数
以炸裂函数举例，来深入体会UDTF
- explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。
- explode函数在关系型数据库中本身是不该出现的。
- 他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分）
- 但是在面向分析的数据库或者数据仓库中，这些规范可以发生改变
explode使用
select explode(`array`(11,22,33)) as item; select explode(`map`("id",10086,"name","zhangsan","age",18));
清晰明了，当参数指定array时，展开为3行item，当参数指定为map，按照key value的顺序进行展开
explode案例
业务数据如下：
Chicago Bulls,1991|1992|1993|1996|1997|1998 San Antonio Spurs,1999|2003|2005|2007|2014 Golden State Warriors,1947|1956|1975|2015 Boston Celtics,1957|1959|1960|1961|1962|1963|1964|1965|1966|1968|1969|1974|1976|1981|1984|1986|2008 L.A. Lakers,1949|1950|1952|1953|1954|1972|1980|1982|1985|1987|1988|2000|2001|2002|2009|2010 Miami Heat,2006|2012|2013 Philadelphia 76ers,1955|1967|1983 Detroit Pistons,1989|1990|2004 Houston Rockets,1994|1995 New York Knicks,1970|1973
字段解释：
球队名称，总冠军年份
代码实现：
-- 建表 create table the_nba_championship( team_name string, champion_year array<string> ) row format delimited fields terminated by ',' collection items terminated by '|'; -- 加载数据 load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship; -- 使用explode select explode(champion_year) from the_nba_championship;
explode使用限制
在select条件中，如果只有explode函数表达式，程序执行是没有任何问题的； 但是如果在select条件中，包含explode和其他字段，就会报错。
错误信息为：UDTF's are not supported outside the SELECT clause, nor nested in expressions
explode 语法限制原因
1、 explode函数属于UDTF函数，即表生成函数；
2、 explode函数执行返回的结果可以理解为一张虚拟的表，其数据来源于源表；
3、 在select中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题;
4、但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表;
5、 通俗点讲，有两张表，不能只查询一张表但是返回分别属于两张表的字段；
6、 从SQL层面上来说应该对两张表进行关联查询
7、 Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数以满足上述需要。
Lateral View侧视图
Lateral View是一种特殊的语法，主要用于搭配UDTF类型功能的函数一起使用，用于解决UDTF函数的一些查询限制的问题。
侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。
一般只要使用UDTF，就会固定搭配lateral view使用
官方链接：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView
实例
--lateral view侧视图基本语法如下 select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……; select a.team_name ,b.year from the_nba_championship a lateral view explode(champion_year) b as year --根据年份倒序排序 select a.team_name ,b.year from the_nba_championship a lateral view explode(champion_year) b as year order by b.year desc;
Hive高阶应用(二)
行列转换
实际工作场景中经常需要实现对于Hive中的表进行行列转换操作
行转列
- 原始数据
Col1 |
Col2 |
Col3 |
a |
b |
1 |
a |
b |
2 |
a |
b |
3 |
c |
d |
4 |
c |
d |
5 |
c |
d |
6 |
- 目标数据
Col1 |
Col2 |
Col3 |
a |
b |
1,2,3 |
c |
d |
4,5,6 |
- 实现
-- 建表 create table row2col2( col1 string, col2 string, col3 int )row format delimited fields terminated by '\t'; -- 查询 select col1, col2, concat_ws(',', collect_list(cast(col3 as string))) as col3 from row2col2 group by col1, col2;
- 解释
collect_list
- 用于将一列中的多行合并为一行，不进行去重
- 实例
select collect_list(col1) from row2col1; +----------------------------+ | ["a","a","a","b","b","b"] | +----------------------------+
concat_ws
- 用于实现字符串拼接，可以指定分隔符
- 实例
select concat_ws*("-","it","And","study"); +-------------------+ | it-And-study | +-------------------+
列转行
- 原始数据
Col1 |
Col2 |
Col3 |
a |
b |
1,2,3 |
c |
d |
4,5,6 |
- 目标数据
Col1 |
Col2 |
Col3 |
a |
b |
1 |
a |
b |
2 |
a |
b |
3 |
c |
d |
4 |
c |
d |
5 |
c |
d |
6 |
- 实现
select col1, col2, lv.col3 as col3 from col2row2 lateral view explode(split(col3, ',')) lv as col3;
- 解释
- 详见侧视图与炸裂函数
Hive高阶应用(三)
Json数据
示例数据：
字段解释：
- 设备名称【device】
- 设备型号【deviceType】
- 信号强度【signal】
- 信号发送时间【time】
get_json_object
用于解析JSON字符串，可以从JSON字符串中返回指定的某个对象列的值
-- 创建表 create table tb_json_test1 ( json string ); -- 加载数据 load data local inpath -- 展开json select --获取设备名称 get_json_object(json,"$.device") as device, --获取设备类型 get_json_object(json,"$.deviceType") as deviceType, --获取设备信号强度 get_json_object(json,"$.signal") as signal, --获取时间 get_json_object(json,"$.time") as stime from tb_json_test1;
json_tuple
用于实现JSON字符串的解析，可以通过指定多个参数来解析JSON返回多列的值
-- 创建表 create table tb_json_test1 ( json string ); -- 加载数据 load data local inpath -- 展开json select json,device,deviceType,signal,stime from tb_json_test1 -- 搭配侧视图展开数据 lateral json_tuple(json,"device","deviceType","signal","time") b as device,deviceType,signal,stime;
JSONSerde
指定Json文件进行建表
-- 创建表 create table tb_json_test2 ( device string, deviceType string, signal double, `time` string ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;
最终效果
