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

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

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;

#建立三张表，分别按年份存最大，最小，计数

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;

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