hive分区表

内部表和外部表

内部表:create table    ,copy数据到warehouse,删除表时数据也会删除

外部表:create external table ,不copy数据到warehouse,删除表时数据不会删除

 

表的分区

分区的好处:如果不建立分区的话,则会全表扫描

数据通过目录划分分区,分区字段是特殊字段

目录结构:/pub/{dt}/{customer_id}/

添加分区:ALTER TABLE fs ADD PATITION(dt='20150305',customer_id) location '/pub/20150315/0001'

使用分区进行查询:

select * from fc where dt='20150315' and customer_id='0001'

日期字段(字符串类型)可以进行比较:t.dt>'20150413'

 

分区表的创建:

create table hive_patition(name string,salary float,gender string,level string)

partitioned by(dt string,dep string)

row format delimited fields treminated by ','

stored as textfile;

 

desc hive_partition;

show partitions hive_partition;//查看分区表的分区信息

导入数据:

load data local inpath '/opt/20150402/party.log' into table hive_partition partition(dt='2015-04015',dep='dev')

 

修改表增加分区:

alter table hive_partition add partition(dt='2015-04-01',dep='dev1') location '/pub/20150401/0001' partition(dt='2015-04-02',dep='dev2') location '/pub/20150402/0001'

修改表删除分区:

alter table hive_partition drop partition(dt='2015-04-02',dep='dev2')

修改表重命名表:

alter table hive_partition rename to partition_test

修改表中的字段:

alter table partition_test change name userName

添加字段:

alter table partition_test add columns(testColumn string)

 

嵌套查询:

from (select name,salary from partition_table) e select e.name,e.salary where e.salary>10000;

case when then语句

select name,salary,case when salary>1 and salary<5000 then 'L1' when salary>=5000 and salary<10000 then 'L2' when salary>=10000 and salary<150000 then 'L3' when salary>10000 then 'L4' else 'L0' end as salaryLevel from

partition_table;

 

having的用法:

select gender,sum(salary) as totalSalary from partition_table group by gender having totalSalary>50000;

 

 

相关推荐