Hive SQL的能力

Hive's SQL提供了基本SQL操作。這些操作作用于表和分區(qū)，具有以下能力：

• 可使用WHERE過濾表中的行
• 可使用SELECT選擇表中指定的列
• 可進(jìn)行表連接
• 可使用group by進(jìn)行聚合
• 可下載表內(nèi)容到本地目錄（如nfs）
• 可將查詢結(jié)果存儲(chǔ)到hadoop dfs目錄中
• 可管理表和分區(qū)（create, drop和alter）
• 可對(duì)自定義的map/reduce作業(yè)插入自定義的腳本

Hive SQL的使用和示例

創(chuàng)建，顯示，修改和刪除表

創(chuàng)建，顯示，修改和刪除表的詳細(xì)信息，請(qǐng)參見Hive Data Definition Language。

創(chuàng)建表
創(chuàng)建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;

在這里示例中可以看到，表的列都指定了相應(yīng)的類型?？梢栽诹泻捅砩咸砑幼⑨?。另外，PARTITIONED BY語句定義了表的分區(qū)，分區(qū)與數(shù)據(jù)列不同，而且和數(shù)據(jù)不存儲(chǔ)在一起。創(chuàng)建出來的page_view表，數(shù)據(jù)的字段分隔符是ASCII 001(ctrl-A)，行分隔符是換行符。

字段分隔符可以用參數(shù)指定：

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分隔符。

建議在表中的某些列上進(jìn)行分桶，這樣可以在數(shù)據(jù)集上進(jìn)行有效的抽樣查詢。不使用桶，隨機(jī)抽樣還是可以執(zhí)行，但是效率會(huì)變低因?yàn)楸仨殥呙枵麄€(gè)表。下面的示例說明了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;

上面的示例，表會(huì)根據(jù)userid的哈希值將數(shù)據(jù)劃分到32個(gè)桶中。每個(gè)桶中的數(shù)據(jù)會(huì)按照viewTime的升序進(jìn)行存儲(chǔ)。這樣組織數(shù)據(jù)后，可以有效地抽樣某個(gè)桶中的數(shù)據(jù)。使用排序?qū)傩?，?nèi)部操作能夠更好地利用數(shù)據(jù)結(jié)構(gòu)，實(shí)現(xiàn)更高效的查詢。

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;

上面這個(gè)示例中，用類似類型定義的方式指定了表的列?？梢栽诹泻捅砩咸砑幼⑨?。另外，PARTITIONED BY語句定義了表的分區(qū)，分區(qū)與數(shù)據(jù)列不同，而且和數(shù)據(jù)不存儲(chǔ)在一起。CLUSTERED BY語句指定了對(duì)哪些列進(jìn)行分桶以及桶的數(shù)量。ROW FORMAT DELIMITED指定了行如何存儲(chǔ)在Hive表中。在次基礎(chǔ)上，指定了字段如何結(jié)尾，集合中的元素如何結(jié)尾，map中的keys如何結(jié)尾。STORED AS SEQUENCEFILE指定了數(shù)據(jù)會(huì)在hdfs上以二進(jìn)制存儲(chǔ)。上面示例中，ROW FORMAT和STORED AS的值是系統(tǒng)默認(rèn)值。

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

瀏覽表和分區(qū)

SHOW TABLES;

列出數(shù)據(jù)倉庫中的表；可能有很多表，超出你要查看的范圍。

SHOW TABLES 'page.*';

列出前綴是page的表。匹配模式遵循Java正則表達(dá)式語法。

SHOW PARTITIONS page_view;

列出表的分區(qū)。如果表沒有分區(qū)會(huì)拋出錯(cuò)誤。

DESCRIBE page_view;

列出表的行及其類型。

DESCRIBE EXTENDED page_view;

列出表的列和所有其它屬性。這條語句會(huì)輸出很多信息，而且格式并不友好，通常用于調(diào)試。

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

列出分區(qū)的列和所有其它屬性。這條語句會(huì)輸出很多信息，而且格式并不友好，通常用于調(diào)試。

修改表
重命名已存在的表。如果表的新名字已經(jīng)存在會(huì)報(bào)錯(cuò)：

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

注意對(duì)表模式的修改（如添加列），如果這是一個(gè)分過區(qū)的表，會(huì)保留舊分區(qū)的模式。訪問新列并且在糾紛去上運(yùn)行的所有查詢會(huì)隱式地返回一個(gè)空值或者新列指定的默認(rèn)值。

之后的版本，如果在指定分區(qū)的配置中找不到新列，會(huì)返回一些特定值，不會(huì)拋出錯(cuò)誤。

刪除表和分區(qū)
刪除表的命令如下：

DROP TABLE pv_users;

刪除分區(qū)命令如下：

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

• 注意表或者分區(qū)中的數(shù)據(jù)會(huì)被刪除，而且可能無法恢復(fù)。