本文转载自微信公众号《Java大数据与数据仓库》，作者刘不二。转载本文请联系Java大数据与数据仓库公众号。爆炸和侧视图为什么把这两个放在一起，因为这两个经常一起用！explode和lateralview不应该出现在关系型数据库中，因为它的出现本身就是当操作不满足一个范式中的第一个Data（每个属性都不能进一步划分）时，本身已经违反了数据库的设计原则（无论是业务系统或数据仓库系统）。系统存储在非关系型数据库中，存储在json中的概率比较高。如果直接导入到基于hive的数据仓库系统中，则需要通过ETL过程对这类数据进行分析。在这个场景中，爆炸和侧视图大显身手。explode的用法在介绍如何处理之前，我们先来了解一下Hive内置的explode函数。官方的解释是：explode()接受一个数组（或地图）作为输入，并将数组（地图）的元素作为单独的行输出。UDTF可用于SELECT表达式列表并作为LATERALVIEW的一部分。意思是explode()接收一个数组或map类型的数据作为输入，然后将数组或map中的元素以每一行的形式输出。它可以与LATERALVIEW一起使用。光看文字描述不够直观，我们来看几个例子。配置单元（默认）>selectexplode（数组（'A'，'B'，'C'））;OKABCTimetaken：4.188seconds，Fetched：3row（s）配置单元（默认）>selectexplode（map（'a'，1，'b',2,'c',3));OKkeyvaluea1b2c3explode函数接收数组或map类型的数据，通常需要使用split函数生成数组。爆破配合解析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处理函数。selectget_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函数试过了，那接下来怎么办呢？，这时候就可以需要配合拆分处理了。为了方便操作，我直接使用上面操作的结果["SogouExplorer_embedupdate","SogouExplorer_embedupdate","SogouExplorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]让我们尝试处理一下首先，我们需要拆分上面的字符串，但在此之前，我们需要去掉两边的方括号，否则我们的数据将包含这两个符号SogouExplorer_embedupdate"]',"[\\[\\]]",'')然后我们就可以拆分爆炸了plorer_embedupdate","2345Explorer_embedupdate","SogouExplorer_embedupdate"]',"[\\[\\]]",''),','));image-20201231112616809这里解析json数组，我们本质上是用regexp_replace来替换括号，然后使用split函数拆分成数据，拆分成多行进行explode上面的写法有没有问题？功能可以完成，但是这里只提出了AppName字段，还有一个字段没有提取出来。如果要提取它，则必须再次执行上述步骤，接下来我们尝试引入json_tuple来简化我们的操作，我们先把它分解成一个多行的简单json字符串，然后用json_tuple进行处理selectexplode(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的逗号，因为我们是按照数组内容之间的分隔符来拆分的,所以这里可以看成是把数组字符串的分隔符换成逗号到#,然后按照#image-20201231122203730拆分然后调用json_tuplefunctionselectjson_tuple(data,'AppName','pepper')from(selectexplode(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"),'[\\[\\]]',''),'(},\\{)','}#\\{'),'#'))asdata)json_table;如图-20201231122505355，我们可以解析出我们需要横向视图的字段在开始之前，先说一下它的用法LATERALVIEWudtf(expression)tableAliasAScolumnAlias，可以把横向视图翻译成侧视图这样的样本数据（刘德华演员，导演，制片人，李小龙演员，导演，制片人，幕后，武术指导李连杰演员，武术指导刘亦菲演员这里我们希望转换成下面的格式刘德华演员安迪刘德华导演刘德华制片人李小龙演员李小龙导演李小龙制片人李小龙幕后制作李小龙武术导演创作etableods.ods_actor_data(usernamestring,userrolestring)ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t';loaddatalocalinpath"/Users/liuwenqiang/workspace/hive/lateral.data"overwriteintotableods.ods_actor_data;如图image-20201231133130769从我们之前的学习中，我们知道我们应该使用explode函数selectexplode(split(userrole,','))fromods.ods_actor_data;image-20201231134156444理论上，我们只需要选择用户名以及selectusername,explode(split(userrole,','))fromods.ods_actor_data;Error:Errorwhilecompilingstatement:FAILED:SemanticException[Error10081]:UDTF'sarenotsupportedoutsidetheSELECTclause,nornestedinexpressions(state=42000,code=10081)因为explode是一个UDTF，所以不能直接和其他领域如何搭配使用？在selectusername,rolefromods.ods_actor_dataLATERALVIEWexplode(split(userrole,','))tmpTableasrole;如图-20201231154758339，看来我们的实现到这里就结束了。为什么在侧视图里面多了一个OUTERouter这个关键字，其实你也可以猜到outerjoin有点像，就是为了避免我们主表的返回值在explode的时候返回值函数为空。请注意，它是null而不是空字符串selectusername,rolefromods.ods_actor_dataLATERALVIEWexplode(array())tmpTableasrole;如图-20201231160414501，添加外层关键字selectusername后，rolefromods.ods_actor_dataLATERALVIEWouterexplode(array())tmpTableasrole；如图-20201231160459117其实在一个SQL中可以多次使用横向视图，像下面这样SELECT*FROMexampleTableLATERALVIEWexplode(col1)myTable1ASmyCol1LATERALVIEWexplode(myCol1)myTable2ASmyCol2;横向视图的实现原理是什么？首先，我们知道explode()是一个UDTF，即一个输入进去多个输出出来，或者一行进去一列出来（多行）image-20201231162007648侧视图关键字是一个将每一行的特定字段赋值给explode函数的表达式，然后将输出结果与当前行进行笛卡尔积，如此循环，直到循环完表中所有数据，然后就变成了下面的安装（传递给的列）图中省略了explode字段）image-20201231162254979但是其实我这里有一个疑问，为什么要这样设计，而普通字段和UDTF函数的返回值直接结合在一起查询不是吗好，然后就是做原始字段的笛卡尔积和UDTF的返回值，为什么要横向看，哈哈横向看where的使用，你可能会说where不是那样用的，有什么不同，真的有，比如上面的信息只有刘德华，那你肯定会写下面的SQLselectusername,rolefromods.ods_actor_dataLATERALVIEWexplode(split(userrole,','))tmpTableasrolewhereusername='刘德华';如果我只想要director的，但是我们知道userrole字段不直接是director的，还包括director的演员，导演，制片人，幕后，武术指导，其实这个时候你可以使用以下别名字段roleselectusername,rolefromods.ods_actor_dataLATERALVIEWexplode(split(userrole,','))tmpTableasrolewhererole="director";如果你用它，你会做多次笛卡尔积；UDTF只能和侧视图一起使用；其实回过头来看，我们上面的处理过程就是将一行转化为多行。行转列的典型实现是SQL面试的高频测试点；
