hive中的复杂数据类型数据如何导入(array)

创建hive表

create table temp.dws_search_by_program_set_count_his(
  program_set_id string, 
  click_array array<string>)
row format delimited
fields terminated by ','
collection items terminated by '#'
lines terminated by '\n';

其中click_array 为array类型。

注意：

* 在建表的时候一定要指定row format delimited，我这里指定了列与列质检为逗号，array的元素内容为#

数据格式：

100051130,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051133,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051134,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051136,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051138,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051140,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051157,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051161,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0
100051163,0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0#0

下面来导入数据：

load data local inpath '/home/gold/dws_search_by_program_set_count_his.csv' overwrite into table temp.dws_search_by_program_set_count_his;

效果：

hive中load数据到分区和add partition的区别：

load data的方式需要移动文件路径，如果把文件就放在分区位置，这时候如果用load data的方式，就会报错，需要用add partition的方式

ALTER TABLE dws.dws_device_box_info_his_v2 ADD partition(province_alias='js',dt='20190701') 
location 'hdfs://ycluster-3/user/hive/warehouse/dws.db/dws_device_box_info_his_v2/province_alias=js/dt=20190701';

如果用load data的方式：

load data inpath 'hdfs://ycluster-3/user/hive/warehouse/dws.db/dws_device_box_info_his_v2/province_alias=js/dt=20190701' 
overwrite into table dws.dws_device_box_info_his_v2 partition(province_alias='js',dt='20190701');

就会报错：

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask. Unable to move source hdfs://ycluster-3/user/hive/warehouse/dws.db/dws_device_box_info_his_v2/province_alias=js/dt=20190701 to destination hdfs://ycluster-3/user/hive/warehouse/dws.db/dws_device_box_info_his_v2/province_alias=js/dt=20190701

可以看出，报错是不能移动文件位置，因为文件已经在这个路径下了

总结：
* 如果文件已经在分区的位置，这时候，需要用add partition的方式
* 如果文件不在分区的位置，这时候用load data的方式

具体的可以参考
hive中的复杂类型struct、array、map，这里struct、array、map都有

转载于:https://www.cnblogs.com/goldenSky/p/10728658.html