Hive学习笔记02.txt

女爷i 2022-08-05 05:22 167阅读 0赞

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;