```bash
Hive:collect_set转为数组并去重,concat_ws将数组用逗号间隔连接成字符串
select
user_id,
concat_ws(',', collect_set(order_id)) as order_ids
from test_table
where 1=1
group by user_id ;
Presto:array_agg转为数组,array_distinct去重,array_join将数组用逗号间隔连接成字符串
select
user_id,
array_join(array_distinct(array_agg(order_id)), ',') as order_ids
from test_table
where 1=1
group by user_id ;
hive to_date('2021-12-13 10:36:28') 结果 2021-12-13
presto substr('2021-12-13 10:36:28',1,10) 结果 2021-12-13
hive nvl()
presto coalesce()
hive cast(字段 as string)
presto cast(字段 as varchar)
hive get_json_object()
presto json_array_get() 和 json_extract()
json_array_get()取出jsonArray的第一个元素
select
json_array_get(xjson,0)
from
test_table
json_extract 和 hive中的get_json_object类似
select
json_extract('{"mid":"1001","alternate":"1"}', '$.alternate')
from
test_table
hive show partitions from table_name
presto
select max(element_at(split("$path",'/'),9)) AS part from table_name