参考链接：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

1.数据定义语言（DDL）

• 创建、修改、删除数据库

创建数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (property_name=property_value, …)];

语法解释：

• 数据库注释：COMMENT
• 在hdfs上的路径： LOCATION
• 使用Key-value的方式为数据库注释

使用实例：

create database lesson1test comment ‘lesson1test’ with dbproperties(‘user’=’banana’);

修改数据库的key-value注释

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);

— (Note: SCHEMA added in Hive 0.14.0)

修改数据库的用户

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

— (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

使用实例

alter database lesson1test set dbproperties(‘user’=’lighter’);

alter database lesson1test set owner user hdfs;

drop database if exists lesson1test;

• 创建、截断、删除表（带分区）

创建表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

[(col_name data_type [COMMENT col_comment], …)]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)]

[

[ROW FORMAT row_format]

[STORED AS file_format]

| STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPERTIES (…)]  — (Note: Available in Hive 0.6.0 and later)

]

[LOCATION hdfs_path]

语法解释：

• 是否是外部表： EXTERNAL
• 表格注释： COMMENT
• 定义分区信息：PARTITIONED BY
• 行格式化方式（1.决定了是否使用hive自带的序列化函数2.可用于指定属性分隔符）： ROW FORMAT

创建表使用实例：

CREATE TABLE goods_price(

id INT,

name STRING,

price FLOAT

)

COMMENT ‘Goods table’

PARTITIONED BY(supplier STRING, class_name STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘,’

STORED AS TEXTFILE;

复制表结构：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

LIKE existing_table_or_view_name

[LOCATION hdfs_path];

使用实例：

create table if not exists copy_table like lesson1.goods_price;

截断表（保留表结构，删除数据）：

TRUNCATE TABLE table_name [PARTITION partition_spec];

partition_spec:

(partition_column = partition_col_value, partition_column = partition_col_value, …)

使用实例（可指定一个partition_column，也可都指定）：

truncate table copy_table partition (supplier=’yihaodian’,class_name=’shupian’);

truncate table copy_table partition (supplier=’yihaodian’);

删除表（任何删除动作都会删除相关的元数据）：

DROP TABLE [IF EXISTS] table_name [PURGE];     — (Note: PURGE available in Hive 0.14.0 and later)

语法解释：

不指定PURGE，则被删除数据被移到.Trash/Current。

使用PURGE则直接删除（不可恢复）。

使用实例：

drop table if exists copy_table;

• 修改表、分区、列

修改表名（保留表结构，删除数据）：

ALTER TABLE table_name RENAME TO new_table_name;

使用实例：

alter table goods_price rename to new_goods_price;

增加表分区（未修改表结构，只是多了一个分区值）：

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec

[LOCATION ‘location1’] partition_spec [LOCATION ‘location2′] …;

partition_spec:

: (partition_column = partition_col_value, partition_column = partition_col_value, …)

使用实例：

alter table new_goods_price add if not exists partition (supplier=’taobao’,class_name=’shupian’);

alter table new_goods_price add if not exists partition (supplier=’jingdong’,class_name=’shupian’) location ‘/user/hive/warehouse/lesson1.db/goods_price/supplier=jingdong/class_name=shupian’;

表知识扩展——内部表和外部表

表类型	语句	是否将数据拷贝到warehouse	删除表是否删除数据
内部表	create table;	是	是
外部表	create external table;	否	否

Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径。

内部表不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除， 而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。

表知识扩展——表分区

意义：避免全表扫描。Where语句采用分区限定的话，表查询会限制在分区内进行，提高效率和准确率。

• 数据通过存储目录划分分区。
• 指定多个分区时，hdfs上存储目录按照分区创建顺序形成树形结构。
• 分区字段的选择：时间段、不同服务的数据、IP段等等。

两个分区时的二级树形存储目录（先supplier后class_name），由下图可见supplier目录是class_name的上级目录。

2.数据操作语言（DML）

• 文件数据导入表

命令：

LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

语法解释：

• 指定数据文件目录： INPATH
• 覆盖原有数据： OVERWRITE
• 指定分区（顺序无先后，但需要明确指定每一个分区）

partition(supplier=’yihaodian’,class_name=’niunai’);

使用实例：

load data local inpath ‘/tmp/20150313/niunai’ into table goods_price partition(supplier=’yihaodian’,class_name=’niunai’);

• 查询结果数据插入表

命令（增加数据）：

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement;

使用实例：

insert into table copy_table partition(class_name=’niunai’,supplier=’yihaodian’) select id,name,price from goods_price;

命令（覆盖原有数据）：

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …) [IF NOT EXISTS]] select_statement1 FROM from_statement;

使用实例：

insert overwrite table copy_table partition(class_name=’niunai’,supplier=’yihaodian’) select id,name,price from overwrite_table;

• UPDATE&DELETE

命令：

UPDATE tablename SET column = value [, column = value …] [WHERE expression]

DELETE FROM tablename [WHERE expression]

使用实例：

Hive 0.14支持，目前最新发行版为Hive 0.13