1:表的重命名
alter table log_message rename to logmsgs;
2:新增加表分区
alter table log_message add if not exists
PARTITION (year='2013',month='10',day='11') LOCATION '/user/hadoop/warehouse/table_name';
ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1'; //示例
ALTER TABLE table_name ADD IF NOT EXISTS
PARTITION (dt='20130101') LOCATION '/user/hadoop/warehouse/table_name'; //一次添加一个分区
ALTER TABLE page_view ADD
PARTITION (dt='2008-08-07', country='us') location '/path/to/us/part080807'
PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809'; //一次添加多个分区
3:高效的移动位置来修改某个分区的路径
alter table log_message PARTITION (year='2013',month='10',day='11')
set LOCATION 's3n://ourbucket/2013/10/11';
4删除分区
alter table log_message drop if exists PARTITION (year='2013',month='10',day='11')
ALTER TABLE login DROP IF EXISTS PARTITION (dt='2008-08-08');
ALTER TABLE page_view DROP IF EXISTS PARTITION (dt='2008-08-08', country='us');
5：修改分区
ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new location";
ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');
6： 动态分区
insert overwrite table employees
partition(country,state)
select ...,se.cnty,se.st from staged_employees se;
7:动态分区 和静态分区相结合：但是静态分区必须放在动态分区键的之前
insert overwrite table employees
partition(country='US',state)
select ...,se.cnty,se.st from staged_employees se where se.cnty='US';
8：单个查询语句中，创建表并加载数据
create table ca_employees as
select name,salary,address from employess se where se.state='CA';
9: 导出数据
9.1: 不指定分隔符
insert overwrite local directory '/home/admin/xxg/qz/acc/'
select * from employee;
9.2:指定分隔符
insert overwrite local directory '/home/admin/xxg/qz/acc/'
row format delimited
fields terminated by '\t'
select * from paper_count ;
select count(distinct userid) from qz_count where doQuesCount>=500 order by doQuesCount desc ;
select distinct userid from qz_count where doQuesCount>=500
group by userid limit 100;