Hive中的explode函数、posexplode函数与later view函数

这篇具有很好参考价值的文章主要介绍了Hive中的explode函数、posexplode函数与later view函数。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1.概述

  在离线数仓处理通过HQL业务数据时,经常会遇到行转列或者列转行之类的操作,就像concat_ws之类的函数被广泛使用,今天这个也是经常要使用的拓展方法。

2.explode函数

2.1 函数语法

-- explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns 
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDTFExplode
Function type:BUILTIN
-- explode()用于array的语法如下
select explode(arraycol) as newcol from tablename;
-- explode()用于map的语法如下:
select explode(mapcol) as (keyname,valuename) from tablename;

2.2 函数说明

  • explode 函数是UDTF函数,将hive一列中复杂的array或者map结构拆分成多行。
  • Explode函数是不允许在select再有其他字段,
    • explode(ARRAY) 列表中的每个元素生成一行。
    • explode(MAP) map中每个key-value对,生成一行,key为一列,value为一列。

2.3 使用案例

-- explode (array)
select explode(array('A','B','C'));
select explode(array('A','B','C')) as col;
select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;
select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf as col;
-- 结果
col
A
B
C
-- explode (map)
select explode(map('A',10,'B',20,'C',30));
select explode(map('A',10,'B',20,'C',30)) as (key,value);
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf;
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value;
-- 结果
key value
A	10
B	20
C	30

3.posexplode函数

2.1 函数语法

-- posexplode(a) - behaves like explode for arrays, but includes the position of items in the original array
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDTFPosExplode
Function type:BUILTIN
select posexplode(ARRAY<T> a)
-- Explodes an array to multiple rows with additional positional column of int type (position of items in the original array, starting with 0). Returns a row-set with two columns (pos,val), one row for each element from the array.

2.2 函数说明

  • posexplode 函数,将ARRAY数组a展开,每个Value一行,每行两列分别对应数组从0开始的下标和数组元素。

2.3 使用案例

-- posexplode (array)
select posexplode(array('A','B','C'));
select posexplode(array('A','B','C')) as (pos,val);
select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf;
select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf as pos,val;
-- 结果
pos val
0	A
1	B
2	C

4.later view

4.1 语法

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
-- columnAlias是给udtf(expression)列起的别名。
-- tableAlias 虚拟表的别名。

4.2 用法描述

  • lateral view为侧视图,意义是为了配合UDTF来使用,把某一行数据拆分成多行数据。
  • 不加lateral view的UDTF只能提取单个字段拆分,并不能塞回原来数据表中。
  • 加上lateral view就可以将拆分的单个字段数据与原始表数据关联上。
  • lateral view函数会将UDTF生成的结果放到一个虚拟表中,然后虚拟表中的数据和输入行进行join来达到连接UDTF外的select字段的目的。(本质是笛卡尔积)

4.3 使用案例

4.3.1 准备数据

下表 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]

需求: 统计各个页面出现的广告的次数

4.3.2 代码实现

第一步: 使用 lateral view 和 explore() 函数将 adid_list 列的 list 拆分,sql代码如下:

select pageid, adid
FROM pageAds lateral view explode(adid_list) ad_view as adid;

可的如下结果

pageid adid
front_page 1
front_page 2
front_page 3
contact_page 4
contact_page 5

第二步: 使用 count/group by 语句统计出每个adid出现的次数:

select adid,count(1) as cnt
FROM pageAds lateral view explode(adid_list) ad_view as adid
group by adid;
adid cnt
1 1
2 1
3 2
4 1
5 1

4.4 Multiple Lateral Views

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

例如,如下查询:

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

例如使用以下基表:

Array pageid_list Array adid_list
[1, 2, 3] [“a”, “b”, “c”]
[3, 4] [“c”, “d”]

单个Lateral View查询:

SELECT pageid_list, adid
FROM pageAds_1
         LATERAL VIEW explode(adid_list) adTable AS adid;
[1,2,3]	a
[1,2,3]	b
[1,2,3]	c
[4,5]	c
[4,5]	d

多个Lateral View查询:

select pageid,adid FROM pageAds_1
lateral view explode(pageid_list) adTable as pageid
lateral view explode(adid_list) adTable as adid;
1,a
1,b
1,c
2,a
2,b
2,c
3,a
3,b
3,c
3,c
3,d
4,c
4,d

4.5 later view json_tuple()

4.5.1 准备数据
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"}');
id col1 col2
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”}

需求: 解析非结构化的json数据类型

“json_tuple(jsonStr, p1, p2, …, pn) - like get_json_object, but it takes multiple names and return a tuple. All the input parameters and output column types are string.”
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDTFJSONTuple
Function type:BUILTIN

json_tuple : 第一个参数是json 字符串所在的列名,其它参数是获取 json 字符串中的哪些key值;

4.5.2 代码实现
SELECT id,
       part1,
       part3,
       part4,
       to_part2,
       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_part2','to_part3', 'to_part4') json2 AS to_part2, to_part3, to_part4
;

1234,61,1,1,0,0,0,0,0
4567,451,789,35,54,2,6,0.0025348542458808617,0.17142857142857143
7890,142,4321,567,76,14,23,0.0032399907428835918,0.04056437389770723

5.使用案例

需求1: 如何产生1-100的连续的数字?

--方式1: 结合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;
  
-- 方式2:结合space函数与split函数,explode函数,lateral view函数+窗口函数获得
select row_number() over () as id
from (select split(space(99), '') as x) t
         lateral view
             explode(x) ex;
-- 方式2:结合space函数与split函数,posexplode函数,lateral view函数获取
from (select split(space(99), ' ') as x) t
         lateral view
             posexplode(x) ex as pos,val;

需求2: 获取2024-07-15至2024-07-29间所有的日期文章来源地址https://www.toymoban.com/news/detail-845491.html

SELECT pos,
       date_add(start_date, pos) dd
FROM (SELECT '2024-07-15' AS start_date, '2024-07-29' AS end_date) temp
         lateral VIEW
             posexplode(split(space(datediff(end_date, start_date)), '')) t
         AS pos, val;

到了这里,关于Hive中的explode函数、posexplode函数与later view函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Hive Lateral View + explode 详解

    hive中的函数分为3类,UDF函数、UDAF函数、UDTF函数 UDF:一进一出 UDAF:聚集函数,多进一出,类似于:count/max/min UDTF:一进多出,如explore()、posexplode(),UDTF函数的时候只允许一个字段 百度explode()时,经常会出现lateral view + explode相关的文章,很少单独写explode()。 分别 了解 ecplode() 与

    2023年04月11日
    浏览(38)
  • HIVE SQL通过Lateral View + explode实现列转行

    原表: a b Andy 碟中谍,谍影重重,007 MOMO 小鞋子,朋友啊你的家在哪里 David ‘’ Lily NULL 实现效果 a b Andy 碟中谍 Andy 谍影重重 Andy 007 MOMO 小鞋子 MOMO 朋友啊你的家在哪里 David ‘’ 实现代码: 注: explode函数:处理map结构的字段,将数组转换成多行,所以此处使用了split函数将

    2024年02月12日
    浏览(37)
  • explode与lateral view使用详解(spark及hive环境对比)

    定义: explode函数能够将array及map类型的数据炸开,实现一行变多行 格式: select explode(array/map) from table 示例 原始数据tmp表 name id goods a 1 book_a,food_a b 2 book_b,food_b c 3 null  输出结果 goods_col book_a food_a book_b food_b 定义: Lateral 也是用来对数组进行列转行的,Lateral View主要解决在

    2024年02月03日
    浏览(33)
  • 大数据Hive篇:explode 和 posexplode

    一. explode单独使用。 1.1. 用于array类型的数据 table_name 表名 array_col 为数组类型的字段 new_col array_col被explode之后对应的列 1.2. 用于map类型的数据 由于map是kay-value结构的,所以它在转换的时候会转换成两列,一列是kay转换而成的,一列是value转换而成的。 table_name 表名 map_col 为

    2024年02月13日
    浏览(39)
  • hive lateral view 实践记录(Array和Map数据类型)

    目录 一、Array 1.建表并插入数据  2.lateral view explode 二、Map 1、建表并插入数据 2、lateral view explode() 3、查询数据 正确插入数据: 原数据 结果:  --------最开始错误的插入数据法-------  原数据  step1: step2: 备注: 比原表数据少了 双引号 综上,以上的插入数据是不对的!!

    2024年02月11日
    浏览(41)
  • MySQL实现数据炸裂拆分(类似Hive的explode函数的拆分数组功能)

    背景描述 ​ 在Hive中,\\\"explode\\\"函数用于将数组类型的列拆分为多行,以便对数组中的每个元素进行处理。然而,在MySQL中,并没有直接的类似功能。但是,我们可以使用一些技巧来模拟这个功能,实现在MySQL中拆分数组并进行查询的操作。本文将介绍如何在MySQL中实现类似Hiv

    2024年02月11日
    浏览(42)
  • HiveSQL题——炸裂函数(explode/posexplode)

    目录 一、炸裂函数的知识点 1.1 炸裂函数  explode  posexplode 1.2 lateral view 侧写视图 二、实际案例 2.1 每个学生及其成绩 0 问题描述 1 数据准备 2 数据分析 3 小结 2.2 日期交叉问题 0 问题描述 1 数据准备 2 数据分析 3 小结 2.3 用户消费金额 0 问题描述 1 数据准备 2 数据分析 3 小

    2024年03月15日
    浏览(43)
  • Hive SQL——explode拆分函数&多行(列)合并为一行(列)&reflect函数

    cd /data/import/ sudo vi test_explode_map_array.txt 添加以下文件内容 小明    产品1,产品2,产品3    性别:男,年龄:24 小花    产品4,产品5,产品6    性别:女,年龄:22  map_key map_value 年龄 24 性别 男 年龄 22 性别 女 prod_arr_new 产品1 产品2 产品3 产品4 产品5 产品6 name prod_arr_new 小明 产品1

    2024年02月15日
    浏览(55)
  • 解决Hive视图View数据乱码的问题

    在Hive中,基于某个表创建视图,直接引用表的字段是不会有问题的;但如果增加一个不存在表中的字段,且字段值为中文,就会出现乱码的问题。         在网上找了一圈,原因是Hive的元数据库默认编码是 Latin1(ISO-8859-1),解决方案基本都是去Hive元数据库修改编码。

    2024年02月17日
    浏览(42)
  • hive中的函数

    COALESCE 函数返回第一个非 NULL 表达式。 语法 参数 参数 类型 值 exprN 任意 变量或表达式 注意事项 当找到第一个非 NULL 表达式时,不会再评估后续表达式。 COALESCE 和 NVL 函数是同义词。 返回值 第一个非 NULL 表达式 如果所有表达式都评估为 NULL,则返回 NULL 返回类型 第一个非

    2024年02月09日
    浏览(29)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包