Hive QL详解

<一>数据定义(DML)操作

1、创建普通表

create table page_view 
(viewtime int,
userid bigint,
page_url string,
referrer_url string,
ip STRING comment 'IP Address of the User')
comment 'This is the page view table' ;

2、添加表分区

create table page_view 
(viewtime int,
userid bigint,
page_url string,
referrer_url string,
ip STRING comment 'IP Address of the User')
comment 'This is the page view table' 
partitioned by (dt string , country string)
row format delimited    
fields terminated by '\001'
stored as sequencefile;

3、添加聚类存储

create table page_view 
(viewtime int,
userid bigint,
page_url string,
referrer_url string,
ip STRING comment 'IP Address of the User')
comment 'This is the page view table' 
partitioned by (dt string , country string)
clustered by(userid) sorted by(viewTime) into 32 buckets
row format delimited    --delimite限制
fields terminated by '\001'
collection items terminated by '\002'
map keys terminated by '\003'
stored as sequencefile;

4、指定存储路径

create table page_view 
(viewtime int,
userid bigint,
page_url string,
referrer_url string,
ip STRING comment 'IP Address of the User')
comment 'This is the page view table' 
row format delimited 
fields terminated by '\054'
stored as textfile
location '<hdfs_location>';  

5、重命名表

alter table   tablename  rename to new_table_name ;

6、改变列名字/类型/位置/注释

create table test (a int , b int , c int);
alter table test change a a1 int ;
alter table test change a a1 string after b ;
alter table test change b b1 int first ;

注意：列的改变只会修改hive的元数据，不会修改实际数据。用户应该确保元数据定义和实际数据结构的一致性。

7、增加/更新列

alter table test  add|replace 
columns (d int comment 'new columns');

注：replace，删除当前的列，增加新的列。只有使用native的SerDE时才可以这么做。

8、增加表属性

alter table table_name set tblproperties table_properties
table_properties:
:(property_name = property_value , property_name = property_value)

9、增加SerDe属性

alter table table_name 
set serde serde_class_name
[with serdeproperties serde_properties]

alter table table_name
set serdeproperties serde_properties

ser_properties:
:(property_name =property_value , property_name = property_value)

注：Hive 为了序列化和反序列化，将会初始化SerDe属性。

10、改变文件格式和组织

alter table table_name set fileformat file_format ;
alter table table_name clustered by (col_name,col_name,...)
[sorted by (col_name,col_name,...)] into num_buckets buckets

11、增加分区

alter table page_view add 
partition (dt='2010-08-08',country='us')
location '/path/to/us/part080808'
partition (dt='2010-08-09',country='us')
location '/path/to/us/part080809';

12、删除分区

alter table page_view drop
partition (dt='2010-08-08',country='us');

13、创建视图

create view [if not exists] view 
[(column_name [comment column_comment],...)]
[comment view_coment]
as select ... ;

14、创建/删除函数

create temporary funcation funcation_name as class_name;
drop  temporary funcation_name ;

<二>数据操作(DML)

插入数据：load data local inpath '/iflytek/data/test.txt' into table test ;

其他的可以参考：http://blog.csdn.net/lifuxiangcaohui/article/details/40588929

<三>数据查询（DCL）

与标准的SQL类似。