目录

  • 背景介绍
  • explode的使用
  • 查看array的元素
  • 使用explode
  • lateral view（侧视图）
  • Lateral View 语法
  • 描述
  • 举个栗子
  • Multiple Lateral Views


一般写sql经常会遇到行转列或者列转行之类的操作，就像concat_ws之类的函数被广泛的使用，今天这个也是经常要使用的拓展方法。

背景介绍

explode与lateral view在关系型数据库中本身是不该出现的。
因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分）。
本身已经违背了数据库的设计原理（不论是业务系统还是数据仓库系统），在面向分析的数据库 数据仓库中，发生了改变。
explode函数可以将一个array或者map展开，
其中explode(array)使得结果中将array列表里的每个元素生成一行；
explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列，
一般情况下，直接使用即可，也可以根据需要结合lateral view 使用

explode的使用

001,allen,usa|china|japan,1|3|7
002,kobe,usa|england|japan,2|3|5
create table test_message(id int,name string,location array<string>,city array<int>) row format delimited fields terminated by ","
collection items terminated by '|';

load data local inpath "/root/hivedata/test_message.txt" into table test_message;

查看array的元素

用下标进行寻找，类似于其他编程语言中的数组访问
```
select location[1] from test_message;
```

使用explode

```
select explode(location) from test_message;

select name,explode(location) from test_message; 报错
当使用UDTF函数的时候,hive只允许对拆分字段进行访问的。
```

lateral view（侧视图）

  • lateral view为侧视图,意义是为了配合UDTF来使用,把某一行数据拆分成多行数据。
  • 不加lateral view的UDTF只能提取单个字段拆分,并不能塞会原来数据表中。
  • 加上lateral view就可以将拆分的单个字段数据与原始表数据关联上.
    在使用lateral view的时候需要指定视图别名和生成的新列别名
tabelA lateral view UDTF(xxx) 视图别名（虚拟表名） as a,b,c

select subview.* from test_message lateral view explode(location) subview as itcast;
subview为视图别名,lc为指定新列别名

select name,subview.* from test_message lateral view explode(location) subview as lc;  
lateral view explode 相当于一个拆分location字段的虚表,然后与原表进行关联.
  • json_tuple()函数也是UDTF函数,因为一个json字符串对应了解析出n个字段.与原表数据关联的时候需要使用lateral view
    select id from table lateral view json_tuple(property,'tag_id','tag_type’);

Lateral View 语法

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

描述

横向视图与用户定义的表生成函数（如explode（））结合使用。 如内置表生成函数中所述，UDTF为每个输入行生成零个或多个输出行。 横向视图首先将UDTF应用于基表的每一行，然后将结果输出行连接到输入行，以形成具有所提供的表别名的虚拟表。

举个栗子

下表 pageAds. 它有两个字段: pageid (页码) and adid_list (页面上的adid):

Column name

Column type

pageid

STRING

adid_list

Array

另外一个实例:

pageid

adid_list

front_page

[1, 2, 3]

contact_page

[3, 4, 5]

用户想要统计各个页面出现的广告的次数

此时可以使用lateral view 和explode处理adid_list 字段得到如下效果

SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

The resulting output will be

pageid (string) adid (int)
 
“front_page”	1
“front_page”	2
“front_page”	3
“contact_page”	3
“contact_page”	4
“contact_page”	5

因为要统计频次，所以使用group by聚合操作

SELECT adid, count(1)
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
GROUP BY adid;

int adid

count(1)

1

1

2

1

3

2

4

1

5

1

Multiple Lateral Views

FROM子句可以有多个LATERAL VIEW子句。 后续的LATERAL VIEWS可以引用出现在LATERAL VIEW左侧的任何表格中的列。

如下所示：

SELECT * FROM exampleTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(myCol1) myTable2 AS myCol2;

上面的示例sql中后面一个直接饮用之前的输出结果作为输入，假设有以下数据表：

Array<int> col1	Array<string> col2
[1, 2]	[a”, “b”, “c”]
[3, 4]	[d”, “e”, “f”]

查询语句

SELECT myCol1, col2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1;

将会生成：

int mycol1	Array<string> col2
1	[a”, “b”, “c”]
2	[a”, “b”, “c”]
3	[d”, “e”, “f”]
4	[d”, “e”, “f”]

当使用两次lateral view查询之后：

SELECT myCol1, myCol2 FROM baseTable
LATERAL VIEW explode(col1) myTable1 AS myCol1
LATERAL VIEW explode(col2) myTable2 AS myCol2;

生成如下的结果：

int myCol1 string myCol2
1 “a”
1 “b”
1 “c”
2 “a”
2 “b”
2 “c”
3 “d”
3 “e”
3 “f”
4 “d”
4 “e”
4 “f”

谋定而后动，知止而有得