Hive 新建含数组、字典的表 然后填充数据

新建表脚本

create table if not exists hi_tuser_array_map_txt
(
    Fguid string,
    FID bigint,
    FUserName string,
    FParentID int,
    FCompanyID int,
    FStatus tinyint,
    ts bigint,
    FAddTimeDate string,

    dt array<string>,
    other map<string,int>
)
comment 'Hive--tuser_array_map_txt' 
row format delimited 
fields terminated by '\t' 
collection items terminated by ',' 
map keys terminated by ':' 
lines terminated by '\n'
stored as textFile;

 

执行新建表

hive> create table if not exists hi_tuser_array_map_txt(Fguid string,FID bigint,FUserName string,FParentID int,FCompanyID int,FStatus tinyint,ts bigint,FAddTimeDate string,dt array<string>,other map<string,int>)
    > comment 'Hive--tuser_array_map_txt' row format delimited fields terminated by '\t' collection items terminated by ',' map keys terminated by ':' lines terminated by '\n' stored as textFile; 
OK
Time taken: 0.917 seconds
hive> 

文本数据格式

[root@centos02 opt]# cat /opt/Files/txt/tuser_array_map_txt.txt

a730eb2b-d2a1-11e9-a80c-5254003d609c    3067    admin    0    3067    1    1389628800    2014-01-14 00:00:00    2014-01-14,00:00:00    127.0.0.1:18
a730f0c3-d2a1-11e9-a80c-5254003d609c    36052    f004    0    36052    1    1409537734    2014-09-01 10:15:34    2014-09-01,10:15:34    127.0.0.1:3
a730f213-d2a1-11e9-a80c-5254003d609c    36198    admin9    3067    3067    1    1409569578    2014-09-01 19:06:18    2014-09-01,19:06:18    127.0.0.1:63

填充数据

hive> load data local inpath '/opt/Files/txt/tuser_array_map_txt.txt' overwrite into table hi_tuser_array_map_txt;
Loading data to table cashmid.hi_tuser_array_map_txt
OK
Time taken: 1.484 seconds
hive> 

 

查看数据

hive> select FID,FUserName,FParentID,FCompanyID,FStatus,ts,FAddTimeDate,dt,other  from hi_tuser_array_map_txt limit 10;
OK
3067    admin    0    3067    1    1389628800    2014-01-14 00:00:00    ["2014-01-14","00:00:00"]    {"127.0.0.1":18}
36052    f004    0    36052    1    1409537734    2014-09-01 10:15:34    ["2014-09-01","10:15:34"]    {"127.0.0.1":3}
36198    admin9    3067    3067    1    1409569578    2014-09-01 19:06:18    ["2014-09-01","19:06:18"]    {"127.0.0.1":63}
Time taken: 0.352 seconds, Fetched: 3 row(s)
hive>