hive 导入数据

1、load data

load data local inpath "/home/hadoop/userinfo.txt" into table userinfo;

load data inpath "/mysql/syslog2/part-m-00000" into table syslog;

2、insert

hive> insert into userinfo2 values(25,'test_user');

insert into table weather_list  select year,data from weather_data;

insert overwritetable weather_list  select year,data from weather_data;

insert into table weather_list partition(createtime='2012-01-01',area='gz') select year,data from weather_data;


insert overwritetable table weather_list partition(createtime='2012-01-01',area='gz') select year,data from weather_data;

#动态分区
insert into table weather_list partition(createtime='2012-01-02',area) select year,data,'gz' from weather_data;

hive> insert into table weather_list partition(createtime,area) select year,data,'2013-01-01','gz' from weather_data;
FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

hive> set hive.exec.dynamic.partition.mode=nonstrict;
hive> insert into table weather_list partition(createtime,area) select year,data,'2013-01-01','gz' from weather_data;

3、多表插入，在单表插入的时候，from子句可以放在前面，这个特性在多表插入的时候被应用

#建立三张表，分别按年份存最大，最小，计数
hive> create table weather_max(year int,mdata int) row format delimited fields terminated by ',';
OK
Time taken: 0.519 seconds
hive> create table weather_min(year int,mdata int) row format delimited fields terminated by ',';
OK
Time taken: 0.471 seconds
hive> create table weather_count(year int,cdata int) row format delimited fields terminated by ',';
OK
Time taken: 0.258 seconds

hive> from weather_data
    > insert into weather_max select year,max(data) group by year
    > insert into weather_min select year,min(data) group by year
    > insert into weather_count select year,count(1) group by year;

4、使用CTAS创建表并导入数据，create table ... as select ...

hive> create table userinfo2 as select userid,username from userinfo;