数据学习从0到1 Apache Hive进阶指南

语兴数据运营
111 阅读5分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第27天 juejin.cn/post/716729…

UDTF

什么是UDTF(User-Defined Table-Generating Functions )?

顾名思义，输入一行输出多行的函数，称之为UDTF函数，也叫表生成函数

以炸裂函数举例，来深入体会UDTF

  1. explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。

  2. explode函数在关系型数据库中本身是不该出现的。

  3. 他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分）

  4. 但是在面向分析的数据库或者数据仓库中，这些规范可以发生改变

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

)

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使用

官方链接：cwiki.apache.org/confluence/…

实例

--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中的表进行行列转换操作

行转列

  1. 实现

-- 建表

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;

  1. 解释

collect_list

  1. 用于将一列中的多行合并为一行，不进行去重

  2. 实例

select collect_list(col1) from row2col1;

+----------------------------+

| ["a","a","a","b","b","b"]  |

+----------------------------+

concat_ws

  1. 用于实现字符串拼接，可以指定分隔符

  2. 实例

select concat_ws*("-","it","And","study");

​

+-------------------+

​

| it-And-study |

​

+-------------------+

列转行

  1. 实现

select

col1,

col2,

lv.col3 as col3

from

col2row2

 lateral view

explode(split(col3, ',')) lv as col3;

  1. 解释

  2. 详见侧视图与炸裂函数

Json数据

示例数据：

字段解释：

  1. 设备名称【device】

  2. 设备型号【deviceType】

  3. 信号强度【signal】

  4. 信号发送时间【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;

最终效果

avatar
文章
阅读
粉丝