更新时间:2023-11-03 GMT+08:00

trans_array

trans_array函数用于将一行数据转为多行的UDTF,将列中存储的以固定分隔符格式分隔的数组转为多行。

使用限制

  • 所有作为key的列必须位于在前面,而要转置的列必须放在后面。
  • 在一个select中只能有一个UDTF,不可以再出现其他的列。
  • 不可以与group by、cluster by、distribute by、sort by一起使用。

命令格式

trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,...,<col1>, <col2>)

参数说明

表1 参数说明

参数

是否必选

参数类型

说明

num_keys

BIGINT类型。

BIGINT类型常量,值必须>=0。在转为多行时作为转置key的列的个数。

separator

STRING类型。

STRING类型常量,用于将字符串拆分成多个元素的分隔符。为空时返回报错。

keys

STRING类型。

转置时作为key的列, 个数由num_keys指定。如果num_keys指定所有的列都作为key(即num_keys等于所有列的个数),则只返回一行。

cols

STRING类型。

要转为行的数组,keys之后的所有列视为要转置的数组,必须为STRING类型。

返回值说明

参数的数据类型。

  • 返回转置后的行,新的列名由as指定。
  • 作为key的列类型保持不变,其余所有的列是STRING类型。
  • 拆分成的行数以个数多的数组为准,不足的补NULL。

示例代码

为便于理解函数的使用方法,本文为您提供源数据,基于源数据提供函数相关示例。创建表salary,并添加数据,命令示例如下:
CREATE EXTERNAL TABLE salary (
dept_id STRING, -- 部⻔
user_id string, -- 员⼯ID
sal INT -- 薪⽔
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
stored as textfile;

添加数据如下:

d1,user1/user4,1000/6000
d1,user2/user5,2000/7000
d1,user3/user6,3000
d2,user4/user7,4000
d2,user5/user8,5000/8000

执行SQL

select trans_array(1, "/", dept_id, user_id, sal) as (dept_id, user_id, sal) from salary; 

返回结果如下:

d1,user1,1000
d1,user4,6000
d1,user2,2000
d1,user5,7000
d1,user3,3000
d1,user6,NULL
d2,user4,4000
d2,user7,NULL
d2,user5,5000
d2,user8,8000