# 复杂类型
DataArts Fabric SQL支持从iceberg表和parquet文件中读写复杂类型列，orc文件仅支持读，仅支持struct和array类型及其嵌套类型。
#### 复杂类型DDL
当前DDL仅支持STRUCT和ARRAY类型。
定义语法如下：
- ARRAY：定义语法为ARRAY\<data_type\>。
- STRUCT：定义语法为STRUCT\<col_name : data_type, ...\>。
其中data_type表示数据类型，可以是基础类型和复杂类型，基础类型请参见[与LakeFormation数据类型映射关系](https://support.huaweicloud.com/devg-fabric/dataartsfabric_sql_04_0190.html)。
DDL示例：
```
-- 创建包含复杂类型的managed表
CREATE TABLE test_managed_table (
    name TEXT,
    address STRUCT<zip:INT, city:TEXT>,
    tags ARRAY<TEXT>,
    logs ARRAY<STRUCT<term:TEXT, time:TIMESTAMP>>
) store as parquet;
-- 创建包含复杂类型的external表
CREATE EXTERNAL TABLE test_external_table (
    name TEXT,
    address STRUCT<zip:INT, city:TEXT>,
    tags ARRAY<TEXT>,
    logs ARRAY<STRUCT<term:TEXT, time:TIMESTAMP>>
) store as orc location 'obs://test/test1';
-- 建表后，可查看LakeFormation完整定义
DESCRIBE test_managed_table ;
DESCRIBE test_external_table ;
```
#### 复杂类型DML
- Struct类型数据构造方式通过ROW(x,x,x,...)，例如：ROW(1,2,3,..)；struct内列数超过1列时，row可省略，如：(1,2,3,...)。
- Array类型数据构造方式通过ARRAY\[x,x,x,..\]，例如：ARRAY\[1,2,3,...\]。
- 嵌套类型按照上述规则依次构造即可，如struct\<a:array\<int\>, b:array\<int\>\>类型对应的数据结构为：ROW(ARRAY\[1,2\], ARRAY\[3,4\])。
DML示例：
```
-- 插入数据
INSERT INTO test_managed_table VALUES('Alice', (10001,'New York'), array['friend','colleague'], array[('login','2024-01-01 00:00:00'),('login','2024-02-01 00:00:00')]);
INSERT INTO test_managed_table SELECT 'Bob', (90001,'Los Angeles')::STRUCT<zip:INT, city:TEXT>, array['family'], array[('purchase','2024-02-15 00:00:00'),('purchase','2024-02-10 00:00:00')]::ARRAY<STRUCT<term:TEXT, time:TIMESTAMP>>;
INSERT INTO test_managed_table SELECT 'Charlie', (60601,'Chicago')::STRUCT<zip:INT, city:TEXT>, array['friend','hobby'], array[NULL]::ARRAY<STRUCT<term:TEXT, time:TIMESTAMP>>;
-- 查看数据
SELECT * FROM test_managed_table ORDER BY name ;
  name   |        address        |        tags        |                                         logs                                          
---------+-----------------------+--------------------+---------------------------------------------------------------------------------------
 Alice   | (10001,"New York")    | {friend,colleague} | {"(login,\"Mon Jan 01 00:00:00 2024\")","(login,\"Thu Feb 01 00:00:00 2024\")"}
 Bob     | (90001,"Los Angeles") | {family}           | {"(purchase,\"Thu Feb 15 00:00:00 2024\")","(purchase,\"Sat Feb 10 00:00:00 2024\")"}
 Charlie | (60601,Chicago)       | {friend,hobby}     | {NULL}
(3 rows)
```
#### 复杂类型DQL
- Struct类型支持点查的方式直接获取内部元素，点查时表列名需用括号"()"包裹。
- Array的使用与一般数组类似，通过下标指定返回某个元素，需要注意的是下标起始位置索引为1。
DQL示例：
```
-- 查询复杂类型数据
SELECT * FROM test_managed_table;
  name   |        address        |        tags        |                                         logs                                          
---------+-----------------------+--------------------+---------------------------------------------------------------------------------------
 Alice   | (10001,"New York")    | {friend,colleague} | {"(login,\"Mon Jan 01 00:00:00 2024\")","(login,\"Thu Feb 01 00:00:00 2024\")"}
 Bob     | (90001,"Los Angeles") | {family}           | {"(purchase,\"Thu Feb 15 00:00:00 2024\")","(purchase,\"Sat Feb 10 00:00:00 2024\")"}
 Charlie | (60601,Chicago)       | {friend,hobby}     | {NULL}
(3 rows)
-- 提取struct元素
SELECT (address).city, (address).zip FROM test_managed_table;
    city     |  zip  
-------------+-------
 Los Angeles | 90001
 New York    | 10001
 Chicago     | 60601
(3 rows)
-- 提取array元素
SELECT tags[1], tags[2] FROM test_managed_table;
  tags   |   tags   
---------+----------
  friend | colleague 
  family |   
  friend | hobby  
(3 rows)
```
#### 复杂类型转jsonb
当前复杂类型包括STRUCT和ARRAY支持通过JSON的方式读取，需设置GUC参数：
```
SET ENABLE_COMPLEX_TYPE=OFF;
```
用户在读取数据以后需要显式转换为对应的数据类型。
DQL示例（jsonb完整操作符和函数说明请参见[JSON/JSONB函数和操作符](https://support.huaweicloud.com/devg-fabric/dataartsfabric_sql_04_0224.html)）：
```
--  -> 通过键或索引提取 JSON 对象或数组的元素
SELECT name, address, tags, logs->0->'term' FROM test_managed_table;
  name   |                address                |          tags           |  ?column?  
---------+---------------------------------------+-------------------------+------------
 Alice   | {"zip": 10001, "city": "New York"}    | ["friend", "colleague"] | "login"
 Bob     | {"zip": 90001, "city": "Los Angeles"} | ["family"]              | "purchase"
 Charlie | {"zip": 60601, "city": "Chicago"}     | ["friend", "hobby"]     | 
(3 rows)
--  ->> 提取元素并转为 text
SELECT name, address FROM test_managed_table WHERE address->>'zip' = '10001';
 name  |              address               
-------+------------------------------------
 Alice | {"zip": 10001, "city": "New York"}
(1 row)
--  @> 数组包含元素，左侧数组包含右侧数组内全部元素
SELECT name, tags FROM test_managed_table WHERE tags @> '["friend"]'::jsonb;
  name   |          tags           
---------+-------------------------
 Alice   | ["friend", "colleague"]
 Charlie | ["friend", "hobby"]
(2 rows)
--  类型显示转换，先使用 ->> 获取为字符，再进行类型转换
SELECT (logs->0->>'time')::TIMESTAMP FROM test_managed_table;
      timestamp      
---------------------
 2024-01-01 00:00:00
 2024-02-15 00:00:00
(3 rows)
```