数仓中presto解析varchar类型Array＜Map＞数据方案1

Jeffery__x

已于 2022-03-22 10:56:13 修改

阅读量5.4k

点赞数

分类专栏：问题解决方案文章标签： presto

于 2020-02-26 16:48:22 首次发布

本文链接：https://blog.csdn.net/qq_36402330/article/details/104519689

版权

问题解决方案专栏收录该内容

1 篇文章 0 订阅

订阅专栏

数仓中presto解析varchar类型Array格式数据方案1

源数据：[{“accountSubject”:“10128”,“amount”:500000},{“accountSubject”:“10129”,“amount”:3000000}]

痛点：：原因众多，包括但不限于，数据从业务数据库中同步到数仓，大部分统一使用string类型，导致Array等结构化数据被以string类型的方式导入到数仓中，导致很多函数需要通过构建数据之后才能使用

背景：：presto较hive而言相对较麻烦，但是公司并不会开放hive引擎供外日常查询需求，解决方案1:
使用正则表达式构建数据，借用json_extract()函数获取指定value

实现逻辑：：regexp_extract(value, ‘(?<=[).*(?=])’) 提取外层括号内的json数据，使用CROSS JOIN UNNEST(“regexp_split”(CAST(“a” AS VARCHAR), ‘(?<=})\S(?={)’)) t (aa) 将数据通过’,'拆分，列转行的形式，将数据构建成json串，使用json_extract()函数获取指定value

代码展示：SELECT order_id, cast(json_extract(aa, '$.accountSubject') as bigint) FROM ( SELECT * FROM ( SELECT order_id, regexp_extract(refund_expenses, '(?<=\[).*(?=\])') a FROM guazi_dw_dwb.dwb_partner_invest_invest_order_refund_flow_day WHERE dt = '${date_y_m_d}' ) t CROSS JOIN UNNEST("regexp_split"(CAST("a" AS VARCHAR), '(?<=})\S+(?={)')) t (aa) ) t