如何掌握SQL语法Explode和Lateral View

64次阅读

共计 5976 个字符，预计需要花费 15 分钟才能阅读完成。

这篇文章主要讲解了“如何掌握 SQL 语法 Explode 和 Lateral View”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“如何掌握 SQL 语法 Explode 和 Lateral View”吧！

explode 和 lateral view

为什么把这两个放一块呢，因为这两个经常放在一起用啊

explode 与 lateral view 在关系型数据库中本身是不该出现的，因为他的出现本身就是在操作不满足第一范式的数据 (每个属性都不可再分)，本身已经违背了数据库的设计原理 (不论是业务系统还是数据仓库系统)，不过大数据技术普及后，很多类似 pv，uv 的数据，在业务系统中是存贮在非关系型数据库中，用 json 存储的概率比较大，直接导入 hive 为基础的数仓系统中，就需要经过 ETL 过程解析这类数据，explode 与 lateral view 在这种场景下大显身手。

explode 用法

在介绍如何处理之前，我们先来了解下 Hive 内置的 explode 函数，官方的解释是：explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows. UDTFs can be used in the SELECT expression list and as a part of LATERAL VIEW. 意思就是 explode() 接收一个 array 或 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。其可以配合 LATERAL VIEW 一起使用。光看文字描述很不直观，咱们来看看几个例子吧。

hive (default)  select explode(array( A , B , C  OK A B C Time taken: 4.188 seconds, Fetched: 3 row(s) hive (default)  select explode(map( a , 1,  b , 2,  c , 3)); OK key value a 1 b 2 c 3

explode 函数接收一个数组或者 map 类型的数据，通常需要用 split 函数生成数组。

explode 配合解析 Json 数组

这里有数据：

{info :[ { AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : 2345Explorer_embedupdate , plugin : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1} ]}

现在需要将 AppName 和 pepper 提取出来，然后按行存放，一行一个, 首先我们按照上一节我们学习的 Json 处理的函数进行尝试

select get_json_object(  {info :[ { AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : 2345Explorer_embedupdate , plugin : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1} ]} ,  $.info[*].AppName  );

如图

image-20201231111231311

但是我们注意到这里虽然提取出来了但是返回值是一个字符串啊, 我为啥知道它是字符串，但是看起来像是一个数组啊，因为我用 explode 函数试过了，那接下来怎么处理呢，这个时候就可以需要配合 split 处理了，为了方便操作我直接用上么的结果进行操作

[SogouExplorer_embedupdate , SogouExplorer_embedupdate , SogouExplorer_embedupdate , 2345Explorer_embedupdate , SogouExplorer_embedupdate]

然我我们尝试处理一下上面这个字符串，首先我们需要 split 一下，但是在此之前我们需要将两边的中括号去掉，否则到时候我们的数据会包含这个两个符号的

select regexp_replace([SogouExplorer_embedupdate , SogouExplorer_embedupdate , SogouExplorer_embedupdate , 2345Explorer_embedupdate , SogouExplorer_embedupdate] , [\\[\\]] , )

然后我们就可以 split 和 explode 的了

select explode(split(regexp_replace( [SogouExplorer_embedupdate , SogouExplorer_embedupdate , SogouExplorer_embedupdate , 2345Explorer_embedupdate , SogouExplorer_embedupdate] , [\\[\\]] , ), ,

image-20201231112616809

这里解析 json 数组，我们本质上还是使用 regexp_replace 替换掉中括号，然后再使用 split 函数拆分为数据，给 explode 去分裂成多行。上面的这种写法有问题吗，功能是可以完成，但是这里只是提出来了 AppName 这个字段，还有一个字段没有提取出来呢，要是想把它提取出来，上面的步骤你还得再来一遍才可以，接下来我们尝试引入 json_tuple 来简化一下我们的操作, 我们先将其 explode 成多行简单 json 字符串，然后再使用 json_tuple 进行处理

select explode( split( regexp_replace( regexp_replace( get_json_object(  {info :[ { AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : 2345Explorer_embedupdate , plugin : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1} ]} , $.info ) , [\\[\\]]  , ) , (},\\{) , }#\\{) , #) );

这里两次调用了 regexp_replace，第一次是为了去掉两边的中括号，第二次是为了将,jons 里面的逗号和分割 json 的逗号进行区分，因为我们按照数组内容之间的分隔符进行 split，所以这里可以看做是将数组字符串的分隔符有逗号换成了# 号，然后就按照# split 了

image-20201231122203730

接下来就可以调用 json_tuple 函数了

select json_tuple(data, AppName , pepper) from( select explode( split( regexp_replace( regexp_replace( get_json_object(  {info :[ { AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1}, {AppName : 2345Explorer_embedupdate , plugin : -1}, {AppName : SogouExplorer_embedupdate , pepper : -1} ]} , $.info ) , [\\[\\]]  , ) , (},\\{) , }#\\{) , #) ) as data ) json_table;

如图

image-20201231122505355

这样我们就将我们需要的字段解析出来了

lateral view

开始之前我们先说一下它的用法 LATERAL VIEW udtf(expression) tableAlias AS columnAlias，你可以将 lateral view 翻译为侧视图

我们有这样的一份样本数据 (

 刘德华   演员, 导演, 制片人   李小龙   演员, 导演, 制片人, 幕后, 武术指导   李连杰   演员, 武术指导   刘亦菲   演员

这里我们希望转换成下面这样的格式

 刘德华   演员   刘德华   导演   刘德华   制片人   李小龙   演员   李小龙   导演   李小龙   制片人   李小龙   幕后   李小龙   武术指导  create table ods.ods_actor_data( username string, userrole string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY  \t  load data local inpath  /Users/liuwenqiang/workspace/hive/lateral.data  overwrite into table ods.ods_actor_data;

如图

image-20201231133130769

从我们前面的学习，我们知道这里应该用 explode 函数

select explode(split(userrole, ,)) from ods.ods_actor_data;

image-20201231134156444

理论上我们这下只要把 username 也选出来就可以了

select username,explode(split(userrole, ,)) from ods.ods_actor_data;

Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF s are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

因为 explode 是一个 UDTF，所以你不能直接和其他字段一起使用，那应该怎么做呢在

select username,role from ods.ods_actor_data LATERAL VIEW explode(split(userrole, ,)) tmpTable as role ;

如图

image-20201231154758339

看起来到这里我们的实现就结束了

lateral view outer

为什么会多了一个 OUTER 关键字呢，其实你也可以猜到了 outer join 有点像，就是为了避免 explode 函数返回值是 null 的时候，影响我们主表的返回，注意是 null 而不是空字符串

select username,role from ods.ods_actor_data LATERAL VIEW explode(array()) tmpTable as role ;

如图

image-20201231160414501

加上 outer 关键字之后

select username,role from ods.ods_actor_data LATERAL VIEW outer explode(array()) tmpTable as role ;

如图

image-20201231160459117

其实一个 SQL 你可以多次使用 lateral view 也是可以的, 就像下面这样

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

lateral view 的实现原理是什么

首先我们知道 explode() 是一个 UDTF 就是一个输入进去，多个输出出来，或者是进去一行，出来一列 ( 多行)

image-20201231162007648

lateral view 关键字就是将每一行的特定字段交给 explode 函数的表达式，然后将输出结果和当前行做笛卡尔积, 然后重复，直到循环完表里的全部数据，然后就变成下面装了 (图中省略了传给 explode 字段的那一列)

image-20201231162254979

但其实到这里我就产生了一个疑问，为啥要这样设计，直接将普通字段和 UDTF 的函数的返回值一起查询不好吗，然后将原始字段和 UDTF 的返回值做笛卡尔积就行了啊，为啥还要 lateral view 呢，哈哈。

lateral view 中 where 的使用

你可能会说 where 不就那么用吗，还有啥不一样的，还真有，例如我上面的信息只要刘德华的，那你肯定会写出下面的 SQL

select username,role from ods.ods_actor_data LATERAL VIEW explode(split(userrole, ,)) tmpTable as role where username= 刘德华  ;

要是我只要导演的呢，但是我们知道 userrole 这个字段是包没有直接是导演的，但是又包含导演的演员, 导演, 制片人, 幕后, 武术指导, 其实这个时候你可以用下面的别名字段 role

select username,role from ods.ods_actor_data LATERAL VIEW explode(split(userrole, ,)) tmpTable as role where role= 导演  ;

如图

image-20201231165856030

感谢各位的阅读，以上就是“如何掌握 SQL 语法 Explode 和 Lateral View”的内容了，经过本文的学习后，相信大家对如何掌握 SQL 语法 Explode 和 Lateral View 这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！

正文完