百度360必应搜狗淘宝本站头条
mqtt服务器xftp6vscode使用技巧mybatiscollectionkeyerror
当前位置：网站首页 > 热门文章 > 正文

Hive SQL中的 lateral view 与 explode、posexplode

bigegpt 2024-09-20 14:02 4 浏览

炸裂函数

  1. Explode
将hive某列一行中复杂的 array 或 map 结构拆分成多行（只能输入array或map）;
通常，explode函数会与lateral view一起结合使用;

语法 :  explode(col)
           select explode(arraycol) as newcol from tablename;

解释 : 
          explode()：函数中的参数传入的是arrary/map数据类型的列名。
          newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名。
          tablename：原表名
 
示例 :
	explode(array) 使得结果中将array列表里的每个元素生成一行;
		select explode(array(1,2,3,4));
      +------+
      | col  |
      +------+
      | 1    |
      | 2    |
      | 3    |
      | 4    |
      +------+
     
  explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列;
		select explode(map('a',1,'b',2));
      +------+--------+
      | key  | value  |
      +------+--------+
      | a    | 1      |
      | b    | 2      |
      +------+--------+
        
      给  key 和 value 列取别名为 aa 和 bb
     	select explode(map('a',1,'b',2)) as (aa,bb);
      +-----+-----+
      | aa  | bb  |
      +-----+-----+
      | a   | 1   |
      | b   | 2   |
      +-----+-----+
  1. posexplode()函数
对一列进行炸裂可以使用 explode()函数，但是如果想实现对两列都进行多行转换，
那么用explode()函数就不能实现了，可以用posexplode()函数，因为该函数可以将index和数据都取出来，
使用两次posexplode并令两次取到的index相等就行了。

示例 ;
  select posexplode(array('a','b','c','d'));
    +------+------+
    | pos  | val  |
    +------+------+
    | 0    | a    |
    | 1    | b    |
    | 2    | c    |
    | 3    | d    |
    +------+------+

Lateral View

官方文档 : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView#LanguageManualLateralView-Description

		Lateral View配合 split, explode 等UDTF函数一起使用，它能够将一列数据拆成多行数据，
并且对拆分后结果进行聚合，即将多行结果组合成一个支持别名的虚拟表;
		Lateral View主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，
不能包含其它字段以及多个UDTF的情况（不能添加额外的select列的问题）;
		一个 FROM 子句可以有多个 LATERAL VIEW 子句。后续的 LATERAL VIEWS 可以引用出现在 LATERAL VIEW 左侧的任何表格中的列。

语法 :
		格式一 : lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*

解释 :
		lateral view在UDTF前使用，表示连接UDTF所分裂的字段。
    UDTF(expression)：使用的UDTF函数，例如explode()。
    tableAlias：表示UDTF函数转换的虚拟表的名称。
    columnAlias：
          表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；
          如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开;
          从 Hive 0.12.0 开始，可以省略列别名;
    
格式二 :  lateral view outer udtf(expression) tableAlias as columnAlias (,columnAlias)*
  
解释 : 
			outer : udtf 函数中指定的列值为 null 时,添加 outer 结果会显示包含 null 的这一行数据,否则会过滤掉此行数据;
    	为了避免 当udtf 没有得到任何结果时最终虚拟结果表里丢失原数据行的问题。具体来将，
	由于later view 的工作原理是将原表与 udtf 产生的虚拟表做 inner join 操作，所以如果 udtf 不产生任何结果时，
  那么对应原表的那一行也会在 inner join 操作后消失。outer关键字就是来解决这个问题的，加上这个关键字之后执行的就是 outer join 操作了，因此原表数据会被完全保留下来。


注：
    1）lateral view的位置是from后where条件前
    2）生成的虚拟表的表名不可省略
    3）from后可带多个lateral view
    3）如果要拆分的字段有null值，需要使用lateral view outer 替代，避免数据缺失
    
示例 :
	hive 建表 :
      create table  lateral_tal(
        km string,
        rq string
        );
	插入测试数据 :
			insert into lateral_tal values("hive	spark	flink	line	null	so	easy","date	todate	firstday	day	day	no	bug");
	
	单列炸裂-posexplode : t_1 虚表有索引列字段;
				select km, c_1 ,c_2   from lateral_tal lateral view posexplode(split(km,'\t')) t_1 as c_1,c_2;
+-------------------------------------+------+--------+
|                 km                 															 | c_1  |  c_2   |
+-------------------------------------+------+--------+
| hive  spark   flink   line    line    so      easy  | 0    | hive   |
| hive  spark   flink   line    line    so      easy  | 1    | spark  |
| hive  spark   flink   line    line    so      easy  | 2    | flink  |
| hive  spark   flink   line    line    so      easy  | 3    | line   |
| hive  spark   flink   line    line    so      easy  | 4    | line   |
| hive  spark   flink   line    line    so      easy  | 5    | so     |
| hive  spark   flink   line    line    so      easy  | 6    | easy   |
| hive  spark   flink   line    null    so      easy  | 0    | hive   |
| hive  spark   flink   line    null    so      easy  | 1    | spark  |
| hive  spark   flink   line    null    so      easy  | 2    | flink  |
| hive  spark   flink   line    null    so      easy  | 3    | line   |
| hive  spark   flink   line    null    so      easy  | 4    | null   |
| hive  spark   flink   line    null    so      easy  | 5    | so     |
| hive  spark   flink   line    null    so      easy  | 6    | easy   |
+-------------------------------------+------+--------+

	单列炸裂-explode :  t_1 虚表没有索引列字段;
				select km, c_1   from lateral_tal lateral view posexplode(split(km,'\t')) t_1 as c_1;
+-------------------------------------+--------+
|                 km                  															|  c_1   |
+-------------------------------------+--------+
| hive  spark   flink   line    line    so      easy  | hive   |
| hive  spark   flink   line    line    so      easy  | spark  |
| hive  spark   flink   line    line    so      easy  | flink  |
| hive  spark   flink   line    line    so      easy  | line   |
| hive  spark   flink   line    line    so      easy  | line   |
| hive  spark   flink   line    line    so      easy  | so     |
| hive  spark   flink   line    line    so      easy  | easy   |
| hive  spark   flink   line    null    so      easy  | hive   |
| hive  spark   flink   line    null    so      easy  | spark  |
| hive  spark   flink   line    null    so      easy  | flink  |
| hive  spark   flink   line    null    so      easy  | line   |
| hive  spark   flink   line    null    so      easy  | null   |
| hive  spark   flink   line    null    so      easy  | so     |
| hive  spark   flink   line    null    so      easy  | easy   |
+-------------------------------------+--------+

	双列炸裂 : 通过索引列(c_1和c_3)关联 t_1 和 t_2 两张虚标;
			select  km ,rq,c_1 ,c_2,c_3,c_4   from lateral_tal lateral view posexplode(split(km,'\t')) t_1 as c_1,c_2 
 lateral view  posexplode(split(rq,'\t')) t_2 as c_3,c_4 where c_1=c_3;
+-------------------------------------+--------------------------------------+------+--------+------+-----------+
|                 km                  															|                  rq                                                               | c_1  |  c_2   | c_3  |    c_4    |
+-------------------------------------+--------------------------------------+------+--------+------+-----------+
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 0    | hive   | 0    | date      |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 1    | spark  | 1    | todate    |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 2    | flink  | 2    | firstday  |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 3    | line   | 3    | day       |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 4    | line   | 4    | day       |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 5    | so     | 5    | no        |
| hive  spark   flink   line    line    so      easy  | date    todate  firstday        day     day     no      bug  | 6    | easy   | 6    | bug       |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 0    | hive   | 0    | date      |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 1    | spark  | 1    | todate    |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 2    | flink  | 2    | firstday  |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 3    | line   | 3    | day       |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 4    | null   | 4    | day       |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 5    | so     | 5    | no        |
| hive  spark   flink   line    null    so      easy  | date    todate  firstday        day     day     no      bug  | 6    | easy   | 6    | bug       |
+-------------------------------------+--------------------------------------+------+--------+------+-----------+

实现 :

hive 建表 :
    create table  lateral_tal_2(
        column1 string,
        column2 string,
        column3 string,
        x1 string,
        x2 string,
        x3 string,
        x4 string
    );
插入数据 :
	insert into lateral_tal_2 values('A1','A2','A3',5,6,1,4);
	insert into lateral_tal_2 values('B1','B2','B3',5,6,1,4);

SQL 语句 :
		select column1,column2,column3,key,value from (
select  column1,column2,column3,map('x1',x1,'x2',x2,'x3',x3,'x4',x4) m from lateral_tal_2) a lateral view explode(m) t1 as key,value;

+----------+----------+----------+------+--------+
| column1  | column2  | column3  | key  | value  |
+----------+----------+----------+------+--------+
| A1       | A2       | A3       | x1   | 5      |
| A1       | A2       | A3       | x2   | 6      |
| A1       | A2       | A3       | x3   | 1      |
| A1       | A2       | A3       | x4   | 4      |
| B1       | B2       | B3       | x1   | 5      |
| B1       | B2       | B3       | x2   | 6      |
| B1       | B2       | B3       | x3   | 1      |
| B1       | B2       | B3       | x4   | 4      |
+----------+----------+----------+------+--------+

如何产生1-100的连续的数字？

结合space函数与split函数，posexplode函数，lateral view函数获得
方法一 :
      select
      id_start+pos as id
      from(
          select
          1 as id_start,
          100 as id_end
      ) m  lateral view posexplode(split(space(id_end-id_start), '')) t as pos, val;

方法二 :
      select
        row_number() over() as id
      from  
        (select split(space(99), ' ') as x) t
      lateral view
      explode(x) ex;

方法三 :
      select
        pos+1
      from  
        (select split(space(99), ' ') as x) t
      lateral view
      posexplode(x) ex;

如何产生开始日期到结束日期的连续的日期？

SELECT
	DATE_ADD(START_DATE, pos)
FROM
	(
	SELECT
		"2023-03-13" AS START_DATE,
		"2023-03-22" AS END_DATE
) s1 lateral VIEW posexplode(split(SPACE(DATEDIFF(END_DATE, START_DATE)),
	" ")) s2 AS pos,null_ele;

lateral view json_tuple(转成多列)

lateral view json_tuple 函数解析非结构化的json数据类型

hive 建表 :
    create table  lateral_tal_3(
    id int,
    col1 string,
    col2 string
    );

插入数据 :
		insert into lateral_tal_3 values(1234,'{"part1" : "61", "total" : "623", "part2" : "560", "part3" : "1", "part4" : "1"}','	{"to_part2" : "0", "to_part4" : "0", "to_up" : "0", "to_part3" : "0", "to_part34" : "0"}'),
(4567,'{"part1" : "451", "total" : "89928", "part2" : "88653", "part3" : "789", "part4" : "35"}','{"to_part2" : "54", "to_part4" : "6", "to_up" : "65", "to_part3" : "2", "to_part34" : "3"}'),
(7890,'{"part1" : "142", "total" : "351808", "part2" : "346778", "part3" : "4321", "part4" : "567"}','{"to_part2" : "76", "to_part4" : "23", "to_up" : "65", "to_part3" : "14", "to_part34" : "53"}');


SQL 语句 : json_tuple : 第一个参数是json 字符串所在的列名,其它参数是获取 json 字符串中的哪些key值;
使用lateral view json_tuple函数 从两列中分别选出part3,part4, to_part3,to_part4的key对应的数据值：
      SELECT
          id,part3,part4 ,part1,
          to_part3,
          to_part4,
          IF(part3=0,0.0, to_part3/part3) as ratio3,
          IF(part4=0,0.0, to_part4/part4) as ratio4
      FROM
      lateral_tal_3
      lateral VIEW json_tuple(col1, 'part3', 'part4', 'part1') json1 AS part3,part4,part1
      lateral VIEW json_tuple(col2, 'to_part3', 'to_part4') json2 AS to_part3,to_part4
      ;

lateral view json_tuple VS lateral view explode

key 固定如果key 多但是许多key对应的值不需要,使用 lateral view json_tuple,否则使用 lateral view explode;

相关推荐

方差分析简介（方差分析通俗理解）

介绍方差分析（ANOVA，AnalysisofVariance）是一种广泛使用的统计方法，用于比较两个或多个组之间的均值。单因素方差分析是方差分析的一种变体，旨在检测三个或更多分类组的均值是否存在...

正如404页面所预示，猴子正成为断网元凶--吧嗒吧嗒真好吃

吧嗒吧嗒，绘图：MakiNaro你可以通过加热、冰冻、水淹、模塑、甚至压溃压力来使网络光缆硬化。但用猴子显然是不行的。光缆那新挤压成型的塑料外皮太尼玛诱人了，无法阻挡一场试吃盛宴的举行。印度政府正...

Python数据可视化:箱线图多种库画法

概念箱线图通过数据的四分位数来展示数据的分布情况。例如：数据的中心位置，数据间的离散程度，是否有异常值等。把数据从小到大进行排列并等分成四份，第一分位数（Q1），第二分位数（Q2）和第三分位数（Q3）...

多组独立(完全随机设计)样本秩和检验的SPSS操作教程及结果解读

作者/风仕在上一期，我们已经讲完了两组独立样本秩和检验的SPSS操作教程及结果解读，这期开始讲多组独立样本秩和检验，我们主要从多组独立样本秩和检验介绍、两组独立样本秩和检验使用条件及案例的SPSS操作...

方差分析 in R语言 and Excel（方差分析r语言例题）

今天来写一篇实际中比较实用的分析方法，方差分析。通过方差分析，我们可以确定组别之间的差异是否超出了由于随机因素引起的差异范围。方差分析分为单因素方差分析和多因素方差分析，这一篇先介绍一下单因素方差分析...

可视化:前端数据可视化插件大盘点 图表/图谱/地图/关系图

前端数据可视化插件大盘点图表/图谱/地图/关系图全有在大数据时代，很多时候我们需要在网页中显示数据统计报表，从而能很直观地了解数据的走向，开发人员很多时候需要使用图表来表现一些数据。随着Web技术的...

matplotlib 必知的 15 个图（matplotlib各种图）

施工专题，我已完成20篇，施工系列几乎覆盖Python完整技术栈，目标只总结实践中最实用的东西，直击问题本质，快速帮助读者们入门和进阶：1我的施工计划2数字专题3字符串专题4列表专题5流程控制专题6编...

R ggplot2常用图表绘制指南（ggplot2绘制折线图）

ggplot2是R语言中强大的数据可视化包，基于“图形语法”（GrammarofGraphics），通过分层方式构建图表。以下是常用图表命令的详细指南，涵盖基本语法、常见图表类型及示例，适合...

Python数据可视化:从Pandas基础到Seaborn高级应用

数据可视化是数据分析中不可或缺的一环，它能帮助我们直观理解数据模式和趋势。本文将全面介绍Python中最常用的三种可视化方法。Pandas内置绘图功能Pandas基于Matplotlib提供了简洁的绘...

Python 数据可视化常用命令备忘录

本文提供了一个全面的Python数据可视化备忘单，适用于探索性数据分析(EDA)。该备忘单涵盖了单变量分析、双变量分析、多变量分析、时间序列分析、文本数据分析、可视化定制以及保存与显示等内容。所...

统计图的种类（统计图的种类及特点图片）

统计图是利用几何图形或具体事物的形象和地图等形式来表现社会经济现象数量特征和数量关系的图形。以下是几种常见的统计图类型及其适用场景：1.条形图（BarChart）条形图是用矩形条的高度或长度来表示...

实测，大模型谁更懂数据可视化?（数据可视化和可视化分析的主要模型）

大家好，我是Ai学习的老章看论文时，经常看到漂亮的图表，很多不知道是用什么工具绘制的，或者很想复刻类似图表。实测，大模型LaTeX公式识别，出乎预料前文，我用Kimi、Qwen-3-235B...

通过AI提示词让Deepseek快速生成各种类型的图表制作

在数据分析和可视化领域，图表是传达信息的重要工具。然而，传统图表制作往往需要专业的软件和一定的技术知识。本文将介绍如何通过AI提示词，利用Deepseek快速生成各种类型的图表，包括柱状图、折线图、饼...

数据可视化:解析箱线图(box plot)

箱线图/盒须图（boxplot）是数据分布的图形表示，由五个摘要组成：最小值、第一四分位数（25th百分位数）、中位数、第三四分位数（75th百分位数）和最大值。箱子代表四分位距（IQR）。IQR是...

[seaborn] seaborn学习笔记1-箱形图Boxplot

1箱形图Boxplot(代码下载)Boxplot可能是最常见的图形类型之一。它能够很好表示数据中的分布规律。箱型图方框的末尾显示了上下四分位数。极线显示最高和最低值，不包括异常值。seaborn中...

一周热门
最近发表
标签列表