dorisdb二维数组的结构及查询

　　业务场景：想把java中的List<List<String，String>>类似结构的数据存储到dorisdb，其实楼主也可以遍历后存成多条，但是呢会带来一个问题，原本我只需要一条数据就能把这个数据存储下来，现在需要扩展成十条甚至是二十条，如果对于庞大的访问量来讲，dorisdb的内存空间远远不够，还好dorisdb提供了二维数组结构。

-- 最新建表语句
CREATE TABLE `t_test` (

`route_start` DATETIME NOT NULL COMMENT "鉴权请求发起时间",

`account` VARCHAR(32) NOT NULL COMMENT "账户",
`content_id` VARCHAR(64) NOT NULL COMMENT "内容id",
`product_code` ARRAY<ARRAY<VARCHAR(64)>> NOT NULL COMMENT "产品编码",
`create_time` DATE NOT NULL COMMENT "创建时间",
`total_time` INT(16)
) ENGINE=OLAP
DUPLICATE KEY( `route_start`,`account`,`content_id`)
PARTITION BY RANGE(`route_start`)
(
PARTITION p2021_52 VALUES [('0000-01-01'), ('2021-12-22')),
PARTITION p2021_53 VALUES [('2021-12-27'), ('2022-01-03')),
PARTITION p2022_02 VALUES [('2022-01-03'), ('2022-01-10')),
PARTITION p2022_03 VALUES [('2022-01-10'), ('2022-01-17'))
)
DISTRIBUTED BY HASH(`create_time`) BUCKETS 8
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "week",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-65535",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "8",
"dynamic_partition.start_day_of_week" = "1",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);

需要注意的地方：ARRAY<ARRAY<VARCHAR(64)>>的字段不能作为DUPLICATE KEY

 

接下来就是查询的时候怎么用的问题，还好dorisdb提供了一个行转列的函数unnest

select t1.*,t2.product_name from (
SELECT account,unnest,unnest[1] product_code,unnest[2] result

FROM t_unity_auth_test3 t,unnest(product_code)
WHERE account= 'MAC00000000' AND unnest[1] = 'pro00000073'

) t1 left join t_product t2 on t1.product_code = t2.product_code

二维数组结构:[['a3','2'],['b1','0'],['a2','1'],['a1','1']]

dorisdb提供了访问二维数组的方式：下标从1开始，这个和Java的下标有些不一样：product_code[1] 就是 ['a3','2']   product_code[2] 就是 ['b1','0']

product_code[1] [0] = 'a3'  product_code[1] [1] = '2'