EXPLODE_SPLIT
描述
explode_split
表函数用于将字符串按照指定分隔符拆分为多个子字符串,并将每个子字符串展开为一行。
需要与 LATERAL VIEW
配合使用,以将嵌套数据结构展开为标准的平面表格式。
explode_split
和 explode_split_outer
区别主要在于空值处理。
语法
EXPLODE_SPLIT(<str>, <delimiter>)
参数
<str>
String 类型,要分隔的字符串。<delimiter>
String 类型,分隔符。
返回值
- 返回由分隔后的字符串组成的列,列类型为 String。
使用说明
<str>
为 NULL 时返回 0 行数据。<str>
为空字符串("")或者无法被拆分时,会返回一行数据。<delimiter>
如果为 NULL,会返回 0 行数据。<delimiter>
如果为空字符串(""),<str>
会被按字节进行拆分(参考:SPLIT_BY_STRING
)。
示例
- 准备数据
create table example(
k1 int
) properties(
"replication_num" = "1"
);
insert into example values(1); - 常规参数
select * from (select 1 as k1) t1 lateral view explode_split("ab,cd,ef", ",") t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | ab |
| 1 | cd |
| 1 | ef |
+------+------+ - 空字符串和无法分隔的情况
select * from (select 1 as k1) t1 lateral view explode_split("", ",") t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | |
+------+------+select * from (select 1 as k1) t1 lateral view explode_split("abc", ",") t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | abc |
+------+------+ - NULL 参数
select * from (select 1 as k1) t1 lateral view explode_split(NULL, ',') t2 as c;
Empty set (0.03 sec)
- 空的分隔符
select * from (select 1 as k1) t1 lateral view explode_split('abc', '') t2 as c;
+------+------+
| k1 | c |
+------+------+
| 1 | a |
| 1 | b |
| 1 | c |
+------+------+ - 分隔符为 NULL
select * from (select 1 as k1) t1 lateral view explode_split('abc', null) t2 as c;
Empty set (0.03 sec)