解密 Doris 复杂数据类型 ARRAY

本帖最后由 levycui 于 2022-7-27 21:39 编辑
问题导读：
1、ARRAY基础用法有哪些？
2、ARRAY如何进行数据导入？
3、ARRAY有哪些重要函数？
4、ARRAY列转行如何实现？

关于 ARRAY类型

复杂数据类型通常来说是现有类型的组合，一般都有直接访问和处理组合内数据的能力。常见的复杂类型有：ARRAY、MAP、STRUCT等。

Doris 复杂数据类型 ARRAY 已经可以在 master 分支先行体验，预计 1.2 版本发布。本文主要目的是介绍 Doris 复杂类型 ARRAY 的基本用法，方便有需要的小伙伴们进行体验。

ARRAY 基础用法

开启 ARRAY 开关

想要体验 ARRAY ，需要打开 ARRAY 类型开关 (enable_array_type)

sql> set enable_array_type=true;
复制代码

第一步创建带 ARRAY 列的表

语法为 ARRAY<T> ，T 为 ARRAY 的子类型。当前 ARRAY 支持的子类型有：BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, CHAR, VARCHAR, STRING

注：当前 ARRAY 仅支持 DUPLICATE KEY 数据模型上使用。

sql> CREATE TABLE `array_test` (
`id` INT NULL,
`c_array` ARRAY<INT> NULL
) ENGINE=OLAP
DUPLICATE KEY(`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 5
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
复制代码

第二步导入 ARRAY 数据

ARRAY 列可以为 NULL、为空， ARRAY 内部也可以包含 NULL 元素

sql> INSERT INTO `array_test` VALUES(1, [1, 2, 3]), (2, [4, NULL, 5, NULL]), (3, []), (4, NULL);
复制代码

第三步查看 ARRAY 数据

sql> SELECT * FROM `array_test` ORDER BY `id`;
+------+--------------------+
|  id  | c_array     
+------+--------------------+
|   1  | [1, 2, 3]    
|   2  | [4, NULL, 5, NULL]  
|   3  | []       
|   4  | NULL         
+------+--------------------+
复制代码

ARRAY 数据导入

当前 ARRAY 支持 Json、Parquet、Orc、Csv 格式的数据导入。下面以 Json 格式为例，介绍一下 ARRAY数据的导入:

第一步数据准备

本地准备 Json 格式数据，带上 ARRAY 列：

[{"id": 1, "c_array": [1,2,3]}, {"id": 2, "c_array": [4,5]}]
复制代码

第二步数据导入

通过 stream load 方式将准备好的 json 测试数据导入到 Doris：

curl --location-trusted -u $user:$pwd -H "strip_outer_array: true" -H "format: json" -T local_json_input.json -XPUT http://127.0.0.1:$port/api/$db/$table/_stream_load
{
    "TxnId": 13021,
    "Label": "1f83c4a1-43ad-49d6-8134-5d40f3fc35c3",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 2,
    "NumberLoadedRows": 2,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 61,
    "LoadTimeMs": 36,
    "BeginTxnTimeMs": 0,
    "StreamLoadPutTimeMs": 2,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 16,
    "CommitAndPublishTimeMs": 14
}
复制代码

第三步查看导入结果

查看导入的结果，发现 ARRAY 数据已经导入成功：

sql> SELECT * FROM `array_test`;
+------+-----------+
|  id  | c_array    
+------+-----------+
|   1  | [1, 2, 3]    
|   2  | [4, 5]        
+------+-----------+
复制代码

ARRAY 相关函数介绍

复杂类型的优势在于可以直接访问和处理内部元素，Doris 提供了丰富的 ARRAY 处理函数。由于 Doris 用户跟 Hive 及 Spark 用户重叠度较高，Doris ARRAY 相关函数语法，跟 Hive 及Spark 语法相似度较高。

当前 ARRAY 支持的相关函数汇总如下表，更详细的语义及使用，可以在 Doris 手册中搜索对应函数。

注：ARRAY 相关函数，仅支持在向量化引擎中使用（set enable_vectorized_engine=true 打开）

ARRAY 行列转换举例

当我们在进行数据分析时，行转列、列转行的需求非常常见。下面分别举例看看 Doris 中 ARRAY 如何进行行列转换。

ARRAY 列转行

例如，有一张记录某人曾经任职过的公司的表 (array_demo_col)

sql> SELECT * FROM `array_demo_col` ORDER BY `name`;
+----------+--------------------------------------+
|  name    | companies                         
+----------+--------------------------------------+
|  lisi    | ['companyB', 'companyT', 'companyD']
| zhangsan | ['companyA', 'companyT']        
+----------+--------------------------------------+
复制代码

现在想要查看，哪些人曾经在 companyB 公司工作过，我们就可以通过 explode 列转行，然后针对性进行过滤来实现：

sql> SELECT `name` FROM `array_demo_col` LATERAL VIEW explode(`companies`) comTable AS company WHERE company='companyB';
+------+
| name 
+------+
| lisi   
+------+
复制代码

ARRAY行转列

继续以上面的表 (array_demo_col) 为例，如果我们想要按公司进行分组，曾经在该公司工作过的同事放到 ARRAY 列中。这时，我们就可以使用 collect_list 来进行行转列，具体示例SQL及效果如下：

sql> SELECT `company`,collect_list(`name`) AS names
FROM
    (SELECT `name`,`company`
     FROM `array_demo_col` LATERAL VIEW explode(`companies`) comTable AS company) AS array_demo_row
GROUP BY `company` ORDER BY `company`;
+----------+----------------------+
| company  | names             
+----------+----------------------+
| companyA | ['zhangsan']      
| companyB | ['lisi']                
| companyD | ['lisi']            
| companyT | ['lisi', 'zhangsan'] 
+----------+----------------------+
复制代码