是否可以更新配置单元中的复杂数据类型?e、 x:Map、数组、结构

oknrviil  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(204)

我想知道:是否有可能更新配置单元中的复杂数据类型?e、 x:使用acid表Map、数组、结构并更新语法?
e、 g.我们有一张table:

CREATE TABLE complex_nested_types_update_array_map (
person_id int,
person_info MAP <STRING, ARRAY <STRING>>)
CLUSTERED BY (person_id) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true");

并插入数据:

insert into table complex_nested_types_update_array_map 
SELECT 1, map('John', array("+44801123311", "+120342234", "+230342234", "+3303422434"));
insert into table complex_nested_types_update_array_map 
SELECT 2, map('Tomas', array("+380342234", "+230342234", "+230342234", "+530342234"));

所以我们的数据在表中:

select * from complex_nested_types_update_array_map order by person_id;

1   {"John":["+44801123311","+120342234","+230342234","+3303422434"]}
2   {"Tomas":["+380342234","+230342234","+230342234","+530342234"]}

是否可以在不覆盖整行的情况下更新特定数组元素?
例如:

UPDATE complex_nested_types_update_array_map SET
person_info = map('AAron', array("edited", "edited", "+230342234", "+3303422434"))
WHERE person_id = 2;

更新数据:

select * from complex_nested_types_update_array_map order by person_id;

1   {"John":["+44801123311","+120342234","+230342234","+3303422434"]}
2   {"AAron":["edited","edited","+230342234","+3303422434"]}

但是我们只能更新数组的[2]或[3]元素吗?

iyfjxgzm

iyfjxgzm1#

使用 insert overwrite 选项。

insert overwrite table complex_nested_types_update_array_map 
select person_id,map('AAron', array("edited", "edited", "+230342234", "+3303422434")) as person_info
from complex_nested_types_update_array_map 
WHERE person_id = 2
union all
select person_id,person_info
from complex_nested_types_update_array_map 
where person_id<>2

相关问题