hive常用命令

1.建表
create table if not exists sdm_risk_flow(ID string, STATES string, ENABLED string, EVENT_ID string)
partitioned by (dt string) row format delimited fields terminated by “\t ” STORED AS ORC

⚠️这里是反引号：英文状态下 esc键下面的那个键，或者不加引号，直接写字段名称

create table test_ds  
(  
  id int comment '用户ID',  
  name string comment '用户名称'  
)  
comment '测试分区表'  
partitioned by(ds string comment '时间分区字段')  
clustered by(id) sorted by(name) into 32 buckets  
row format delimited   
fields terminated by '\t'  
stored as rcfile;  
//location '/tmp/test_location';  
这里表示将id按照name进行排序，聚类汇总，然后分区划分到32个散列桶中
其中/tmp/test_location可不必先创建

2.查询

select * from tablename where ....

3.插入数据

insert into table(fieldName..) values(fieldValues...)

4.切换数据库

use databaseName

5.显示所有数据库

show databases

6.显示某数据库下所有的表

 show tables

7.删除表

drop table

8.删除数据库（有数据的情况下）

drop database cascade;

9.显示表的分区

show partitions db_name.table_name

10.查询某个分区下的数据

select table_coulm from table_name where partition_name = '2014-02-25’;

11.添加分区

 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/dt=20130101'; //一次添加一个分区

 ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'  
 PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';  //一次添加多个分区

12.删除分区

 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’);

13.修改分区

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’);

14.添加列

ALTER TABLE table_name ADD COLUMNS (col_name STRING);  //在所有存在的列后面，但是在分区列之前添加一列