11.3　Hive QL详解

11.3　Hive QL详解
- 11.3.1　数据定义（DDL）操作

11.3.1　数据定义（DDL）操作

1.创建表

下面是在Hive中创建表（CREATE）的语法：

CREATE[EXTERNAL]TABLE[IF NOT EXISTS]table_name

[（col_name data_type[COMMENT col_comment]，……）]

[COMMENT table_comment]

[PARTITIONED BY（col_name data_type[col_comment]，col_name data_type[COMMENT

col_comment]，……）]

[CLUSTERED BY（colname, col_name，……）[SORTED BY（col_name，……）]INTO num

buckets BUCKETS]

[ROW FORMAT row_format]

[STORED AS file_format]

[LOCATION hdfs_path]

[AS select_statement]（Note：this feature is only available on the latest trunk

or versions higher than 0.4.0.）

CREATE[EXTERNAL]TABLE[IF NOT EXISTS]table_name

LIKE existing_table_name

[LOCATION hdfs_path]

data_type

：primitive_type

|array_type

|map_type

primitive_type

：TINYINT

|SMALLINT

|INT

|BIGINT

|BOOLEAN

|FLOAT

|DOUBLE

|STRING

array_type

：ARRAY＜primitive_type＞

map_type

：MAP＜primitive_type, primitive_type＞

row_format

：DELIMITED[FIELDS TERMINATED BY char][COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char]

|SERDE serdename[WITH SERDEPROPERTIES property_name=property_value, property

name=property_value，……]

file_format：

：SEQUENCEFILE

|TEXTFILE

|INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

下面进行相关的说明。

CREATE TABLE，创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常，用户可以用IF NOT EXIST选项来忽略这个异常。

EXTERNAL关键字，创建一个外部表，在创建表的同时指定一个指向实际数据的路径（LOCATION）。在Hive中创建内部表时，会将数据移动到数据仓库指向的路径；在创建外部表时，仅记录数据所在的路径，不对数据的位置做任何改变。当删除表时，内部表的元数据和数据会一起被删除，而在删除外部表时只删除元数据，不删除数据。

LIKE格式修饰的CREATE TABLE命令允许复制一个已存在表的定义，而不复制它的数据内容。

这里还需要说明的是，用户可以使用自定制的SerDe或自带的SerDe创建表。SerDe是Serialize/Deserilize的简称，用于序列化和反序列化。在Hive中，序列化和反序列化即在key/value和hive table的每个列值之间的转化。如果没有指定ROW FORMAT或ROW FORMAT DELIMITE-D，创建表就使用自带的SerDe。如果使用自带的SerDe，则必须指定字段列表。关于字段类型，可参考用户指南的类型部分。定制的SerDe字段列表可以是指定的，但是Hive将通过查询SerDe决定实际的字段列表。

如果需要将数据存储为纯文本文件，那么要使用STORED AS TEXTFILE。如果数据需要压缩，则要使用STORED AS SEQUENCEFILE。INPUTFORMAT和OUTPUTFORMAT定义一个与InputFormat和OutputFormat类相对应的名字作为一个字符串，例如，将“org.apache.hadoop.hive.contrib.fileformat.base64”定义为“Base64TextInputFormat”。

Hive还支持建立带有分区（Partition）的表。有分区的表可以在创建的时候使用PARTITIONED BY语句。一个表可以拥有一个或多个分区，每个分区单独存在于一个目录下。而且，表和分区都可以对某个列进行CLUSTERED BY操作，将若干个列放入一个桶（Bucket）中。也可以利用SORT BY列来存储数据，以提高查询性能。

表名和列名不区分大小写，但SerDe和属性名是区分大小写的。表和列的注释分别是以单引号表示的字符串。

下面通过一组例子来对CREATE命令进行介绍，以加深用户的理解。

例1：创建普通表

下面代码将创建page_view表，该表包括viewTime、userid、page_url、referrer_url和ip列。

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：添加表分区

下面代码将创建page_view表，该表所包含字段与例1中page_view表相同。此外，通过Partition语句为该表建立分区，并用制表符来区分同一行中的不同字段。

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：添加聚类存储

下面代码将创建page_view表，该表所包含字段与例1中page_view表相同。在page_view表分区的基础上增加了聚类存储：将列按照userid进行分区并划分到不同的桶中，按照viewTime值的大小进行排序存储。这样的组织结构允许用户通过userid属性高效地对集群列进行采样。

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

FIELDS TERMINATED BY'\001'

COLLECTION ITEMS TERMINATED BY'\002'

MAP KEYS TERMINATED BY'\003'

STORED AS SEQUENCEFILE；

例4：指定存储路径

到目前为止，在所有例子中，数据都默认存储在HDFS的＜hive.metastore.warehouse.dir＞/＜table＞目录中，它在Hive配置的文件hive-site.xml中设定。我们可以通过Location为表指定新的存储位置，如下所示：

CREATE EXTERNAL TABLE page_view（viewTime INT, userid BIGINT，

page_url STRING, referrer_url STRING，

ip STRING COMMENT'IP Address of the User'，

country STRING COMMENT'country of origination'）

COMMENT'This is the staging page view table'

ROW FORMAT DELIMITED FIELDS TERMINATED BY'\054'

STORED AS TEXTFILE

LOCATION'＜hdfs_location＞'；

2.修改表语句

ALTER TABLE语句用于改变一个已经存在的表的结构，比如增加列或分区，改变SerDe、添加表和SerDe的属性或重命名表。

（1）重命名表

ALTER TABLE table_name RENAME TO new_table_name

这个命令可以让用户为表更名。数据所在的位置和分区名并不改变。换而言之，旧的表名并未“释放”，对旧表的更改会改变新表的数据。

（2）改变列名字/类型/位置/注释

ALTER TABLE table_name CHANGE[COLUMN]

col_old_name col_new_name column_type

[COMMENT col_comment]

[FIRST|AFTER column_name]

这个命令允许用户修改列的名称、数据类型、注释或位置，例如：

CREATE TABLE test_change（a int, b int, c int）；

ALTER TABLE test_change CHANGE a a1 INT；//将a列的名字改为a1

ALTER TABLE test_change CHANGE a a1 STRING AFTER b；

//将a列的名字改为a1，a列的数据类型改为string，并将它放置在列b之后

修改后，新的表结构为：b int, a1 string, c int。

ALTER TABLE test_change CHANGE b b1 INT FIRST；

//会将b列的名字修改为b1，并将它放在第一列

修改后，新表的结构为：b1 int, a string, c int。

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

（3）增加/更新列

ALTER TABLE table_name ADD|REPLACE

COLUMNS（col_name data_type[COMMENT col_comment]，……）

ADD COLUMNS，允许用户在当前列的末尾、分区列之前增加新的列。REPLACE COLUMNS，删除当前的列，加入新的列。只有在使用native的SerDE（DynamicSerDe或MetadataTypeColumnsetSerDe）时才可以这么做。

（4）增加表属性

ALTER TABLE table_name SET TBLPROPERTIES table_properties

table_properties：

：（property_name=property_value, property_name=property_value，……）

用户可以用这个命令向表中增加元数据，目前last_modified_user、last_modified_time属性都是由Hive自动管理的。用户可以向列表中增加自己的属性，可以使用DESCRIBE EXTENDED TABLE来获得这些信息。

（5）增加SerDe属性

ALTER TABLE table_name

SET SERDE serde_class_name

[WITH SERDEPROPERTIES serde_properties]

ALTER TABLE table_name

SET SERDEPROPERTIES serde_properties

serde_properties：

：（property_name=property_value，

property_name=property_value，……）

这个命令允许用户向SerDe对象增加用户定义的元数据。Hive为了序列化和反序列化数据，将会初始化SerDe属性，并将属性传给表的SerDe。这样，用户可以为自定义的SerDe存储属性。

（6）改变表文件格式和组织

ALTER TABLE table_name SET FILEFORMAT file_format

ALTER TABLE table_name CLUSTERED BY（col_name, col_name，……）

[SORTED BY（col_name，……）]INTO num_buckets BUCKETS

这个命令修改了表的物理存储属性。

注意　这些命令只能修改Hive的元数据，不能重组或格式化现有的数据。用户应该确定实际数据的分布符合元数据的定义。

3.表分区操作语句

Hive在进行数据查询的时候一般会对整个表进行扫描，当表很大时将会消耗很多时间。有时候只需要对表中比较关心的一部分数据进行扫描，因此Hive引入了分区（Partition）的概念。

Hive表分区不同于一般分布式系统中常见的范围分区、哈希分区、一致性分区等概念。Hive的分区相对比较简单，是在Hive的表结构下根据分区的字段设置将数据按目录进行存放。相当于简单的索引功能。

Hive表分区需要在表创建的时候指定模式才能使用。它的字段指定的是虚拟的列，在实际的表中并不存在。在Hive表分区的模式下可以指定多级的结构，相当于对目录进行了嵌套。表模式在创建完成之后使用之前还需要通过ALTER TABLE语句添加具体的分区目录才能使用。

Hive表分区的命令主要包括创建分区、增加分区和删除分区。其中创建分区已经在CREATE语句中进行介绍，下面介绍一下为Hive表增加分区和删除分区命令。

（1）增加分区

ALTER TABLE table_name ADD partition_spec[LOCATION'location1']partition_spec[

LOCATION'location2']……

partition_spec：

：PARTITION（partitioncol=partition_col_value, partition_col=partiton_col

value，……）

用户可以用ALTER TABLE ADD PARTITION来对表增加分区。当分区名是字符串时加引号，例如：

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'；

（2）删除分区

ALTER TABLE table_name DROP

partition_spec, partition_spec，……

用户可以用ALTER TABLE DROP PARTITION来删除分区，分区的元数据和数据将被一并删除，例如：

ALTER TABLE page_view

DROP PARTITION（dt='2010-08-08'，country='us'）；

下面我们通过一组例子对分区命令及相关知识进行讲解。

假设我们有一组电影评分数据^[1]，该数据包含以下字段：用户ID、电影ID、电影评分、影片放映城市、影片观看时间。首先，我们使用Hive命令行创建电影评分表，如代码清单11-1所示。

代码清单11-1　创建电影评分表u1_data

create table u1_data（

userid int，

movieid int，

rating int，

city string，

viewTime string）

row format delimited

fields terminated by'\t'

stored as textfile；

该表为普通用户表，字段之间通过制表符“\t”进行分割。通过Hadoop命令可以查看该表的目录结构如下所示：

hadoop fs-ls/user/hive/warehouse/u1_data；

Found 1 items

-rw-r—r—1 hadoop supergroup 2609206 2012-05-17 01：27/user/hive/warehouse/

u1_data/u.data.new

可以看到u1_data标下并没有分区。

下面我们创建带有一个分区的用户观影数据表，如代码清单11-2所示。

代码清单11-2　创建电影评分表u2_data：

create table u2_data（

userid int，

movieid int，

rating int，

city string，

viewTime string）

PARTITIONED BY（dt string）

row format delimited

fields terminated by'\t'

stored as textfile；

在该表中指定了单个表分区模式，即“dt string”，在表刚刚创建的时候我们可以查看该表的目录结构，发现其并没有通过dt对表结构进行分区，如下所示：

hadoop fs-ls/user/hive/warehouse/u2_data；

Found 1 items

drwxr-xr-x-hadoop supergroup 0 2012-05-17 01：33/user/hive/warehouse/u2_data/

下面我们使用该模式对表指定具体分区，如下所示：

alter table u2_data add partition（dt='20110801'）；

此时，无论是否加载数据，该表根目录下将存在dt=20110801分区，如下所示：

hadoop fs-ls/user/hive/warehouse/u2_data；

Found 1 items

drwxr-xr-x-hadoop supergroup 0 2012-05-17 01：33/user/hive/warehouse/u2_data/dt=20110801

这里有两点需要注意：

1）当没有声明表模式的时候不能为表指定具体的分区。若为表u2_data指定city分区，将提示以下错误：

hive＞alter table u2_data add partition（dt='20110901'，city='北京'）；

FAILED：Error in metadata：table is partitioned but partition spec is not specified

or does not fully match table partitioning：{dt=20110901，city=北京}

FAILED：Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

2）分区名不能与表属性名重复，如下所示：

create table u2_data（

userid int，

movieid int，

rating int，

city string，

viewTime string）

PARTITIONED BY（city string）

row format delimited

fields terminated by'\t'

stored as textfile；

FAILED：Error in semantic analysis：Column repeated in partitioning columns

另外，还可以为表创建多个分区，相当于多级索引的功能。以电影评分表为例，我们创建dt string和city string两级分区，如代码清单11-3所示。

代码清单11-3　创建电影评分表u3_data：

create table u3_data（

userid int，

movieid int，

rating int）

PARTITIONED BY（dt string, city string）

row format delimited

fields terminated by'\t'

stored as textfile；

下面，我们使用模式指定一个具体的分区并查看HDFS目录，如下所示：

alter table u3_data add partition（dt='20110801'，city='北京'）；

hadoop fs-ls/user/hive/warehouse/u3_data/dt=20110801；

Found 1 items

drwxr-xr-x-hadoop supergroup 0 2012-05-17 19：27/user/hive/warehouse/

u3_data/dt=20110801/city=北京

对于数据加载操作我们将在11.3.2节数据操作（DML）中进行详细介绍，这里不再赘述。

4.删除表

DROP TABLE table_name

DROP TABLE用于删除表的元数据和数据。如果配置了Trash，那么会将数据删除到Trash/Current目录，元数据将完全丢失。当删除EXTERNAL定义的表时，表中的数据不会从文件系统中删除。

5.创建/删除视图

目前，只有Hive 0.6之后的版本才支持视图。

（1）创建表视图

CREATE VIEW[IF NOT EXISTS]view_name[（column_name[COMMENT column_comment]，……）]

[COMMENT view_comment]

AS SELECT……

CREATE VIEW，以指定的名称创建一个表视图。如果表或视图的名字已经存在，则报错，也可以使用IF NOT EXISTS忽略这个错误。

如果没有提供表名，则视图列的名字将由定义的SELECT表达式自动生成；如果SELECT包括像x+y这样的无标量的表达式，则视图列的名字将生成_C0，_C1等形式。当重命名列时，可有选择地提供列注释。注释不会从底层列自动继承。如果定义SELECT表达式的视图是无效的，那么CREATE VIEW语句将失败。

注意　没有关联存储的视图是纯粹的逻辑对象。目前在Hive中不支持物化视图。当一个查询引用一个视图时，可以评估视图的定义并为下一步查询提供记录集合。这是一种概念的描述，实际上，作为查询优化的一部分，Hive可以将视图的定义与查询的定义结合起来，例如从查询到视图使用的过滤器。

在创建视图的同时确定视图的架构，随后再改变基本表（如添加一列）将不会在视图的架构中体现。如果基本表被删除或以不兼容的方式被修改，则该无效视图的查询失败。

视图是只读的，不能用于LOAD/INSERT/ALTER的目标。

视图可能包含ORDER BY和LIMIT子句。如果一个引用了视图的查询也包含了这些子句，那么在执行这些子句时首先要查看视图语句，然后返回结果按视图中语句执行。例如，一个视图v指定返回记录LIMIT为5，执行查询语句：select*from v LIMIT 10，这个查询最多返回5行记录。

以下是创建视图的例子：

CREATE VIEW onion_referrers（url COMMENT'URL of Referring page'）

COMMENT'Referrers to The Onion website'

SELECT DISTINCT referrer_url

FROM page_view

WHERE page_url='http：//www.theonion.com'；

（2）删除表视图

DROP VIEW view_name

DROP VIEW，删除指定视图的元数据。在视图中使用DROP TABLE是错误的，例如：

DROP VIEW onion_referrers；

6.创建/删除函数

（1）创建函数

CREATE TEMPORARY FUNCTION function_name AS class_name

该语句创建了一个由类名实现的函数。在Hive中可以持续使用该函数查询，也可以使用Hive类路径中的任何类。用户可以通过执行ADD FILES语句将函数类添加到类路径，可参阅用户指南CLI部分了解有关在Hive中添加/删除函数的更多信息。使用该语句注册用户定义函数。

（2）删除函数

注销用户定义函数的格式如下：

DROP TEMPORARY FUNCTION function_name

7.展示描述语句

在Hive中，该语句提供一种方法对现有的数据和元数据进行查询。

（1）显示表

SHOW TABLES identifier_with_wildcards

SHOW TABLES列出了所有基表及与给定正则表达式名字相匹配的视图。在正则表达式中，可以使用“”来匹配任意字符，并使用“[]”或“|”来表示选择关系。例如'page_view'、'page_v'、'view|page'，所有这些将匹配'page_view'表。匹配表按字母顺序排列。在元存储中，如果没有找到匹配的表，则不提示错误。

（2）显示分区

SHOW PARTITIONS table_name

SHOW PARTITIONS列出了给定基表中的所有现有分区，分区按字母顺序排列。

（3）显示表/分区扩展

SHOW TABLE EXTENDED[IN|FROM database_name]LIKE identifier_with_wildcards

[PARTITION（partition_desc）]

SHOW TABLE EXTENDED为列出所有给定的匹配正规表达式的表信息。如果分区规范存在，那么用户不能使用正规表达式作为表名。该命令的输出包括基本表信息和文件系统信息，例如，文件总数、文件总大小、最大文件大小、最小文件大小、最新存储时间和最新更新时间。如果分区存在，则它会输出给定分区的文件系统信息，而不是表中的文件系统信息。

作为视图，SHOW TABLE EXTENDED用于检索视图的定义。

（4）显示函数

SHOW FUNCTIONS"a.*"

SHOW FUNCTIONS为列出用户定义和建立所有匹配给定正规表达式的函数。可以为所有函数提供".*"。

（5）描述表/列

DESCRIBE[EXTENDED]table_name[DOT col_name]

DESCRIBE[EXTENDED]table_name[DOT col_name（[DOT field_name]|[DOT'$elem$']|

[DOT'$key$']|[DOT'$value$']）*]

DESCRIBE TABLE为显示列信息，包括给定表的分区。如果指定EXTENDED关键字，则将在序列化形式中显示表的所有元数据。DESCRIBE TABLE通常只用于调试，而不用在平常的使用中。

如果表有复杂的列，可以通过指定数组元素table_name.complex_col_name（和'$elem$'作为数组元素，'$key$'为图的主键，'$value$'为图的属性）来检查该列的属性。对于复杂的列类型，可以使用这些定义进行递归查询。

（6）描述分区

DESCRIBE[EXTENDED]table_name partition_spec

该语句列出了给定分区的元数据，其输出和DESCRIBE TABLE类似。目前，在查询计划准备阶段不能使用这些列信息。

[1]http：//www.grouplens.org/node/73。

11.3 Hive QL详解