Hive SQL的能力

Hive's SQL提供了基本SQL操作。这些操作作用于表和分区，具有以下能力：

• 可使用WHERE过滤表中的行
• 可使用SELECT选择表中指定的列
• 可进行表连接
• 可使用group by进行聚合
• 可下载表内容到本地目录（如nfs）
• 可将查询结果存储到hadoop dfs目录中
• 可管理表和分区（create, drop和alter）
• 可对自定义的map/reduce作业插入自定义的脚本

Hive SQL的使用和示例

创建，显示，修改和删除表

创建，显示，修改和删除表的详细信息，请参见Hive Data Definition Language。

创建表
创建page_view表：

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)
STORED AS SEQUENCEFILE;

在这里示例中可以看到，表的列都指定了相应的类型。可以在列和表上添加注释。另外，PARTITIONED BY语句定义了表的分区，分区与数据列不同，而且和数据不存储在一起。创建出来的page_view表，数据的字段分隔符是ASCII 001(ctrl-A)，行分隔符是换行符。

字段分隔符可以用参数指定：

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 '1'
STORED AS SEQUENCEFILE;

行分隔符目前不能修改，它是Hadoop分隔符。

建议在表中的某些列上进行分桶，这样可以在数据集上进行有效的抽样查询。不使用桶，随机抽样还是可以执行，但是效率会变低因为必须扫描整个表。下面的示例说明了page_view表在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 '1'
        COLLECTION ITEMS TERMINATED BY '2'
        MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

上面的示例，表会根据userid的哈希值将数据划分到32个桶中。每个桶中的数据会按照viewTime的升序进行存储。这样组织数据后，可以有效地抽样某个桶中的数据。使用排序属性，内部操作能够更好地利用数据结构，实现更高效的查询。

CREATE TABLE page_view(viewTime INT, userid BIGINT,
                page_url STRING, referrer_url STRING,
                friends ARRAY<BIGINT>, properties MAP<STRING, 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 '1'
        COLLECTION ITEMS TERMINATED BY '2'
        MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

上面这个示例中，用类似类型定义的方式指定了表的列。可以在列和表上添加注释。另外，PARTITIONED BY语句定义了表的分区，分区与数据列不同，而且和数据不存储在一起。CLUSTERED BY语句指定了对哪些列进行分桶以及桶的数量。ROW FORMAT DELIMITED指定了行如何存储在Hive表中。在次基础上，指定了字段如何结尾，集合中的元素如何结尾，map中的keys如何结尾。STORED AS SEQUENCEFILE指定了数据会在hdfs上以二进制存储。上面示例中，ROW FORMAT和STORED AS的值是系统默认值。

表名和列是大小写敏感的。

浏览表和分区

SHOW TABLES;

列出数据仓库中的表；可能有很多表，超出你要查看的范围。

SHOW TABLES 'page.*';

列出前缀是page的表。匹配模式遵循Java正则表达式语法。

SHOW PARTITIONS page_view;

列出表的分区。如果表没有分区会抛出错误。

DESCRIBE page_view;

列出表的行及其类型。

DESCRIBE EXTENDED page_view;

列出表的列和所有其它属性。这条语句会输出很多信息，而且格式并不友好，通常用于调试。

DESCRIBE EXTENDED page_view PARTITION (ds='2008-08-08');

列出分区的列和所有其它属性。这条语句会输出很多信息，而且格式并不友好，通常用于调试。

修改表
重命名已存在的表。如果表的新名字已经存在会报错：

ALTER TABLE old_table_name RENAME TO new_table_name;

重命名已存在表的列。要确保使用相同的列类型，之前存在的列也要列出来：

ALTER TABLE old_table_name REPLACE COLUMNS (col1 TYPE, ...);

在已存在的表中添加列：

ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');

注意对表模式的修改（如添加列），如果这是一个分过区的表，会保留旧分区的模式。访问新列并且在纠纷去上运行的所有查询会隐式地返回一个空值或者新列指定的默认值。

之后的版本，如果在指定分区的配置中找不到新列，会返回一些特定值，不会抛出错误。

删除表和分区
删除表的命令如下：

DROP TABLE pv_users;

删除分区命令如下：

ALTER TABLE pv_users DROP PARTITION (ds='2008-08-08')

• 注意表或者分区中的数据会被删除，而且可能无法恢复。

---