工作的时候遇到hive 查询拼接url的情况,发现json串里有个uri的集合,这个就麻烦了,研究一下,写出了一个builder,如下:
url_builder as (
select
item_id,
collect_set(
concat(
'http://www.google.com/',
get_json_object(image_list, '$.uri'),
'~args'
)
) as image_urls
from
(
select
item_id,
split(
regexp_replace(
concat(
'{',
regexp_extract(
image_list,
'[^\\[](.+)[^\\]]'
),
'}'
),
'\\}\\,\\{',
'\\}\\|\\|\\{'
),
'\\|\\|'
) as image_array
from
item_table
) as test LATERAL VIEW explode(image_array) Ttable as image_list
group by
item_id
)