Hive 新建表 填充数据

新建表脚本（字段之间的值以Tab分隔）

create table if not exists hi_tuser_txt
(
    Fguid string,
    FID bigint,
    FUserName string,
    FParentID int,
    FCompanyID int,
    FStatus tinyint,
    ts bigint,
    FAddTimeDate string,    
    FAddDate string,
    FAddTime string,
    FIP string,
    FThreadID smallint 
)
comment 'Hive--tuser_txt' 
row format delimited 
fields terminated by '\t' 
lines terminated by '\n' 
stored as textFile;

 

执行新建表

hive> create table if not exists hi_tuser_txt(Fguid string,FID bigint,FUserName string,FParentID int,FCompanyID int,FStatus tinyint,ts bigint,FAddTimeDate string,FAddDate string,FAddTime string,FIP string,FThreadID smallint )
    > comment 'Hive--tuser_txt' row format delimited fields terminated by '\t' lines terminated by '\n' stored as textFile; 
OK
Time taken: 1.484 seconds
hive>

文本数据格式 

[root@centos02 opt]# cat /opt/Files/txt/tuser_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    25
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    22
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    34
a730f31d-d2a1-11e9-a80c-5254003d609c    36215    clqtest    36052    36052    0    1409572906    2014-09-01 20:01:46    2014-09-01    20:01:46    210.79.115.102    8
a730f40e-d2a1-11e9-a80c-5254003d609c    36414    csh00a    36052    36052    1    1409620995    2014-09-02 09:23:15    2014-09-02    09:23:15    127.0.0.1    36

填充数据

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

 

 

新建表脚本2（字段之间的值以逗号分隔）

create table if not exists hi_tuser2_txt
(
    Fguid string,
    FID bigint,
    FUserName string,
    FParentID int,
    FCompanyID int,
    FStatus tinyint,
    ts bigint,
    FAddTimeDate string,    
    FAddDate string,
    FAddTime string,
    FIP string,
    FThreadID smallint 
)
comment 'Hive--tuser2_txt' 
row format delimited 
fields terminated by ',' 
lines terminated by '\n' 
stored as textFile;

执行脚本

hive> create table if not exists hi_tuser2_txt(Fguid string,FID bigint,FUserName string,FParentID int,FCompanyID int,FStatus tinyint,ts bigint,FAddTimeDate string,FAddDate string,FAddTime string,FIP string,FThreadID smallint)
    > comment 'Hive--tuser2_txt' row format delimited fields terminated by ',' lines terminated by '\n' stored as textFile;
OK
Time taken: 0.815 seconds
hive>

文本数据格式

[root@centos02 opt]# cat /opt/Files/txt/tuser_txt-2.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,25
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,22
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,34
a730f31d-d2a1-11e9-a80c-5254003d609c,36215,clqtest,36052,36052,0,1409572906,2014-09-01 20:01:46,2014-09-01,20:01:46,210.79.115.102,8
a730f40e-d2a1-11e9-a80c-5254003d609c,36414,csh00a,36052,36052,1,1409620995,2014-09-02 09:23:15,2014-09-02,09:23:15,127.0.0.1,36

填充数据 

hive> load data local inpath '/opt/Files/txt/tuser_txt-2.txt' overwrite into table hi_tuser2_txt;
Loading data to table cashmid.hi_tuser2_txt
OK
Time taken: 1.858 seconds
hive> 

 

查看数据库下的所有表

hive> show tables;
OK
hi_tuser2_txt
hi_tuser_txt
Time taken: 0.505 seconds, Fetched: 2 row(s)
hive> 

 

查询数据 

select FID,FUserName,FParentID,FCompanyID,FStatus,ts,FAddTimeDate,FAddDate,FAddTime,FIP,FThreadID  
from hi_tuser_txt 
limit 10 ;