hive基础操作

1:创建普通表

drop table telephonedata；
create table IF NOT EXISTS telephonedata(
telephone string,
upPackageNum int,
updata int,
downPackageNum int,
downdata int
)
row format delimited fields terminated by ‘\t’ stored as textfile;

load data inpath ‘/dtable/telephonedata_log2.log’ overwrite into table telephonedata;

select * from telephonedata limit 1000;

SELECT T.* FROM (SELECT TELEPHONE,SUM(UPPACKAGENUM) UPPACKAGENUM,SUM(UPDATA) UPDATA,SUM(DOWNPACKAGENUM) DOWNPACKAGENUM,SUM(DOWNDATA) DOWNDATA FROM TELEPHONEDATA GROUP BY TELEPHONE) T ORDER BY DOWNDATA DESC;

2:创建外部表

drop table telephoneext；
create external table td_ext(
telephone string,
uppackagenum int,
updata int,
downpackagenum int,
downdata int
)
row format delimited fields terminated by ‘\t’ stored as textfile
location ‘/user/table/telephonedata’;

3:创建分区表

create table test_partition (id int,name string,no int)partitioned by (dt string) row format delimited fields terminated by ‘\t’ stored as textfile ;

load data local inpath ‘/test/partition1.txt’ overwrite into table test_partition partition (dt=’2016-05’);
load data local inpath ‘/test/partition2.txt’ overwrite into table test_partition partition (dt=’2016-06’);

select * from test_partition where dt=’2016-06’;
select count(1) from test_partition where dt=’2016-06’;

4:保存处理后的数据

create table sel_table as
SELECT T.* FROM (SELECT TELEPHONE,SUM(UPPACKAGENUM) UPPACKAGENUM,SUM(UPDATA) UPDATA,SUM(DOWNPACKAGENUM) DOWNPACKAGENUM,SUM(DOWNDATA) DOWNDATA FROM TELEPHONEDATA GROUP BY TELEPHONE) T ORDER BY DOWNDATA DESC;

5:导出到本地文件系统

insert overwrite local directory ‘/test/sel_table’
select * from sel_table;

6:导出到HDFS中

insert overwrite directory ‘/test/sel_table’
select * from sel_table;