大數(shù)據(jù)Hadoop Hive sql語法詳解無標題文章

時間：2017-08-16 19:36:53???來源：CSDN

Hive 是基于Hadoop 構建的一套數(shù)據(jù)倉庫分析系統(tǒng)，它提供了豐富的SQL查詢方式來分析存儲在Hadoop 分布式文件系統(tǒng)中的數(shù)據(jù)，可以將結構

化的數(shù)據(jù)文件映射為一張數(shù)據(jù)庫表照弥，并提供完整的SQL查詢功能守屉，可以將SQL語句轉(zhuǎn)換為MapReduce任務進行運行隅忿，通過自己的SQL 去查詢分析需

要的內(nèi)容膊爪，這套SQL 簡稱Hive SQL入客，使不熟悉mapreduce 的用戶很方便的利用SQL 語言查詢管毙，匯總腿椎，分析數(shù)據(jù)。而mapreduce開發(fā)人員可以把

己寫的mapper 和reducer 作為插件來支持Hive 做更復雜的數(shù)據(jù)分析夭咬。

它與關系型數(shù)據(jù)庫的SQL 略有不同啃炸，但支持了絕大多數(shù)的語句如DDL、DML 以及常見的聚合函數(shù)卓舵、連接查詢南用、條件查詢。HIVE不適合用于聯(lián)機

online)事務處理边器，也不提供實時查詢功能训枢。它最適合應用在基于大量不可變數(shù)據(jù)的批處理作業(yè)。

HIVE的特點：可伸縮（在Hadoop的集群上動態(tài)的添加設備）忘巧，可擴展恒界，容錯，輸入格式的松散耦合砚嘴。

Hive 的官方文檔中對查詢語言有了很詳細的描述十酣，請參考：http://wiki.apache.org/hadoop/Hive/LanguageManual ，本文的內(nèi)容大部分翻譯自該頁面际长，期間加入了一些在使用過程中需要注意到的事項耸采。

1. ?DDL 操作

DDL

?建表

?刪除表

?修改表結構

?創(chuàng)建／刪除視圖

?創(chuàng)建數(shù)據(jù)庫

?顯示命令

建表：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

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

[COMMENT table_comment]

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

[CLUSTERED BY (col_name, col_name, ...)

[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

[ROW FORMAT row_format]

[STORED AS file_format]

[LOCATION hdfs_path]

?CREATE TABLE 創(chuàng)建一個指定名字的表。如果相同名字的表已經(jīng)存在工育，則拋出異常虾宇；用戶可以用 IF NOT EXIST 選項來忽略這個異常

?EXTERNAL 關鍵字可以讓用戶創(chuàng)建一個外部表，在建表的同時指定一個指向?qū)嶋H數(shù)據(jù)的路徑（LOCATION）

?LIKE 允許用戶復制現(xiàn)有的表結構如绸，但是不復制數(shù)據(jù)

?COMMENT可以為表與字段增加描述

?ROW FORMAT

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

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe嘱朽。如果沒有指定 ROW FORMAT 或者 ROW FORMAT

DELIMITED，將會使用自帶的 SerDe怔接。在建表的時候搪泳，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe扼脐，Hive

通過 SerDe 確定表的具體的列的數(shù)據(jù)岸军。

?STORED AS

SEQUENCEFILE

| TEXTFILE

| RCFILE

| INPUTFORMAT input_format_classname OUTPUTFORMAT???????????? output_format_classname

如果文件數(shù)據(jù)是純文本，可以使用 STORED AS TEXTFILE瓦侮。如果數(shù)據(jù)需要壓縮艰赞，使用 STORED AS SEQUENCE 。

創(chuàng)建簡單表：

hive> CREATE TABLE pokes (foo INT, bar STRING);

創(chuàng)建外部表：

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

建分區(qū)表

CREATE TABLE par_table(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(date STRING, pos STRING)

ROW FORMAT DELIMITED ‘\t’

FIELDS TERMINATED BY '\n'

STORED AS SEQUENCEFILE;

建Bucket表

CREATE TABLE par_table(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(date STRING, pos STRING)

CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

ROW FORMAT DELIMITED ‘\t’

FIELDS TERMINATED BY '\n'

STORED AS SEQUENCEFILE;

創(chuàng)建表并創(chuàng)建索引字段ds

hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

復制一個空表

CREATE TABLE empty_key_value_store

LIKE key_value_store;

例子

create table ?user_info (user_id int, cid string, ckid string, username string)

row format delimited

fields terminated by '\t'

lines terminated by '\n';

導入數(shù)據(jù)表的數(shù)據(jù)格式是：字段之間是tab鍵分割肚吏，行之間是斷行猖毫。

及要我們的文件內(nèi)容格式：

100636 ?100890 ?c5c86f4cddc15eb7 ? ? ? ?yyyvybtvt

100612 ?100865 ?97cc70d411c18b6f ? ? ? ?gyvcycy

100078 ?100087 ?ecd6026a15ffddf5 ? ? ? ?qa000100

顯示所有表：

hive> SHOW TABLES;

按正條件（正則表達式）顯示表，

hive> SHOW TABLES '.*s';

修改表結構

?增加分區(qū)须喂、刪除分區(qū)

?重命名表

?修改列的名字吁断、類型、位置坞生、注釋

?增加/更新列

?增加表的元數(shù)據(jù)信息

表添加一列：

hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);

添加一列并增加列字段注釋

hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

更改表名：

hive> ALTER TABLE events RENAME TO 3koobecaf;

刪除列：

hive> DROP TABLE pokes;

增加仔役、刪除分區(qū)

?增加

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

partition_spec:

: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

?刪除

ALTER TABLE table_name DROP partition_spec, partition_spec,...

重命名表

?ALTER TABLE table_name RENAME TO new_table_name

修改列的名字、類型是己、位置又兵、注釋：

?ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

?這個命令可以允許改變列名、數(shù)據(jù)類型卒废、注釋沛厨、列位置或者它們的任意組合

表添加一列：

hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);

添加一列并增加列字段注釋

hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

增加/更新列

?ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

??ADD是代表新增一字段，字段位置在所有列后面(partition列前)

REPLACE則是表示替換表中所有字段摔认。

增加表的元數(shù)據(jù)信息

?ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:

:[property_name = property_value…..]

?用戶可以用這個命令向表中增加metadata

改變表文件格式與組織

?ALTER TABLE table_name SET FILEFORMAT file_format

?ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS

?這個命令修改了表的物理存儲屬性

創(chuàng)建／刪除視圖

?CREATE

VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment],

...) ][COMMENT view_comment][TBLPROPERTIES (property_name =

property_value, ...)]?AS SELECT

?增加視圖

?如果沒有提供表名逆皮，視圖列的名字將由定義的SELECT表達式自動生成

?如果修改基本表的屬性，視圖中不會體現(xiàn)参袱，無效查詢將會失敗

?視圖是只讀的电谣，不能用LOAD/INSERT/ALTER

?DROP VIEW view_name

?刪除視圖

創(chuàng)建數(shù)據(jù)庫

?CREATE DATABASE name

顯示命令

?show tables;

?show databases;

?show partitions ;

?show functions

?describe extended table_name dot col_name

2. ?DML 操作:元數(shù)據(jù)存儲

hive不支持用insert語句一條一條的進行插入操作，也不支持update操作抹蚀。數(shù)據(jù)是以load的方式加載到建立好的表中剿牺。數(shù)據(jù)一旦導入就不可以修改。

DML包括：INSERT插入环壤、UPDATE更新晒来、DELETE刪除

?向數(shù)據(jù)表內(nèi)加載文件

?將查詢結果插入到Hive表中

?0.8新特性 insert into

向數(shù)據(jù)表內(nèi)加載文件

?LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

?Load 操作只是單純的復制/移動操作，將數(shù)據(jù)文件移動到 Hive 表對應的位置郑现。

?filepath

?相對路徑湃崩，例如：project/data1

?絕對路徑，例如： /user/hive/project/data1

?包含模式的完整 URI懂酱，例如：hdfs://namenode:9000/user/hive/project/data1

例如：

hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

加載本地數(shù)據(jù)竹习，同時給定分區(qū)信息

?加載的目標可以是一個表或者分區(qū)。如果表包含分區(qū)列牺，必須指定每一個分區(qū)的分區(qū)名

?filepath 可以引用一個文件（這種情況下整陌，Hive 會將文件移動到表所對應的目錄中）或者是一個目錄（在這種情況下，Hive 會將目錄中的所有文件移動至表所對應的目錄中）

LOCAL關鍵字

?指定了LOCAL瞎领，即本地

?load 命令會去查找本地文件系統(tǒng)中的 filepath泌辫。如果發(fā)現(xiàn)是相對路徑，則路徑會被解釋為相對于當前用戶的當前路徑九默。用戶也可以為本地文件指定一個完整的 URI震放，比如：file:///user/hive/project/data1.

?load 命令會將 filepath 中的文件復制到目標文件系統(tǒng)中。目標文件系統(tǒng)由表的位置屬性決定驼修。被復制的數(shù)據(jù)文件移動到表的數(shù)據(jù)對應的位置

例如：加載本地數(shù)據(jù)殿遂，同時給定分區(qū)信息：

hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

??沒有指定LOCAL

如果 filepath 指向的是一個完整的 URI诈铛，hive 會直接使用這個 URI。否則

?如果沒有指定 schema 或者 authority墨礁，Hive 會使用在 hadoop 配置文件中定義的 schema 和 authority幢竹，fs.default.name 指定了 Namenode 的 URI

?如果路徑不是絕對的，Hive 相對于 /user/ 進行解釋焕毫。 Hive 會將 filepath 中指定的文件內(nèi)容移動到 table （或者 partition）所指定的路徑中

加載DFS數(shù)據(jù) ，同時給定分區(qū)信息：

hive> LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

The above command will load data from an HDFS file/directory to the table. Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous.

OVERWRITE

?指定了OVERWRITE

?目標表（或者分區(qū)）中的內(nèi)容（如果有）會被刪除驶乾，然后再將 filepath 指向的文件/目錄中的內(nèi)容添加到表/分區(qū)中邑飒。

?如果目標表（分區(qū)）已經(jīng)有一個文件，并且文件名和 filepath 中的文件名沖突级乐，那么現(xiàn)有的文件會被新文件所替代疙咸。

將查詢結果插入Hive表

?將查詢結果插入Hive表

?將查詢結果寫入HDFS文件系統(tǒng)

?基本模式

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

?多插入模式

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...

?自動分區(qū)模式

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

將查詢結果寫入HDFS文件系統(tǒng)

?INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

?數(shù)據(jù)寫入文件系統(tǒng)時進行文本序列化，且每列用^A 來區(qū)分唇牧，\n換行

INSERTINTO

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

3. ?DQL 操作:數(shù)據(jù)查詢SQL

SQL操作

?基本的Select 操作

?基于Partition的查詢

?Join

3.1 基本的Select操作

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[?? CLUSTER BY col_list

| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

?使用ALL和DISTINCT選項區(qū)分對重復記錄的處理罕扎。默認是ALL，表示查詢所有記錄丐重。DISTINCT表示去掉重復的記錄

?Where 條件

?類似我們傳統(tǒng)SQL的where 條件

?目前支持 AND,OR ,0.9版本支持between

?IN, NOT IN

?不支持EXIST ,NOT EXIST

ORDER BY與SORT BY的不同

?ORDER BY 全局排序腔召，只有一個Reduce任務

?SORT BY 只在本機做排序

Limit

?Limit 可以限制查詢的記錄數(shù)

SELECT * FROM t1 LIMIT 5

?實現(xiàn)Top k 查詢

?下面的查詢語句查詢銷售記錄最大的 5 個銷售代表。

SET mapred.reduce.tasks = 1

SELECT * FROM test SORT BY amount DESC LIMIT 5

?REGEX Column Specification

SELECT 語句可以使用正則表達式做列選擇扮惦，下面的語句查詢除了 ds 和 hr 之外的所有列：

SELECT `(ds|hr)?+.+` FROM test

例如

按先件查詢

hive> SELECT a.foo FROM invites a WHERE a.ds='';

將查詢數(shù)據(jù)輸出至目錄：

hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='';

將查詢結果輸出至本地目錄：

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

選擇所有列到本地目錄：

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;

hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;

hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE a.ds='';

hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;

hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

將一個表的統(tǒng)計結果插入另一個表中：

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;

hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo > 0 GROUP BY a.bar;

JOIN

hive> FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events SELECT t1.bar, t1.foo, t2.foo;

將多表數(shù)據(jù)插入到同一表中：

FROM src

INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100

INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200

INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;

將文件流直接插入文件：

hive> FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-09';

This streams the data in the map phase through the script /bin/cat (like hadoop streaming). Similarly - streaming can be used on the reduce side (please see the Hive Tutorial or examples)

3.2 基于Partition的查詢

?一般 SELECT 查詢會掃描整個表臀蛛，使用 PARTITIONED BY 子句建表，查詢就可以利用分區(qū)剪枝（input pruning）的特性

?Hive 當前的實現(xiàn)是崖蜜，只有分區(qū)斷言出現(xiàn)在離 FROM 子句最近的那個WHERE 子句中浊仆，才會啟用分區(qū)剪枝

3.3 Join

Syntax

join_table:

table_reference JOIN table_factor [join_condition]

| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition

| table_reference LEFT SEMI JOIN table_reference join_condition

table_reference:

table_factor

| join_table

table_factor:

tbl_name [alias]

| table_subquery alias

| ( table_references )

join_condition:

ON equality_expression ( AND equality_expression )*

equality_expression:

expression = expression

?Hive 只支持等值連接（equality joins）、外連接（outer joins）和（left semi joins）豫领。Hive 不支持所有非等值的連接抡柿，因為非等值連接非常難轉(zhuǎn)化到 map/reduce 任務

?LEFT，RIGHT和FULL OUTER關鍵字用于處理join中空記錄的情況

?LEFT SEMI JOIN 是 IN/EXISTS 子查詢的一種更高效的實現(xiàn)

?join 時等恐，每次 map/reduce 任務的邏輯是這樣的：reducer 會緩存 join 序列中除了最后一個表的所有表的記錄洲劣，再通過最后一個表將結果序列化到文件系統(tǒng)

?實踐中，應該把最大的那個表寫在最后

join查詢時课蔬，需要注意幾個關鍵點

?只支持等值join

?SELECT a.* FROM a JOIN b ON (a.id = b.id)

?SELECT a.* FROM a JOIN b

ON (a.id = b.id AND a.department = b.department)

?可以 join 多于 2 個表囱稽，例如

SELECT a.val, b.val, c.val FROM a JOIN b

ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

?如果join中多個表的 join key 是同一個，則 join 會被轉(zhuǎn)化為單個 map/reduce 任務

LEFT二跋，RIGHT和FULL OUTER

?例子

?SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

?如果你想限制 join 的輸出战惊，應該在 WHERE 子句中寫過濾條件——或是在 join 子句中寫

?容易混淆的問題是表分區(qū)的情況

??SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)

WHERE a.ds='2010-07-07' AND b.ds='2010-07-07‘

?如果 d 表中找不到對應 c 表的記錄，d 表的所有列都會列出 NULL扎即，包括 ds 列吞获。也就是說况凉，join 會過濾 d 表中不能找到匹配 c 表 join key 的所有記錄。這樣的話衫哥，LEFT OUTER 就使得查詢結果與 WHERE 子句無關

?解決辦法

?SELECT c.val, d.val FROM c LEFT OUTER JOIN d

ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

LEFT SEMI JOIN

?LEFT SEMI JOIN 的限制是茎刚， JOIN 子句中右邊的表只能在 ON 子句中設置過濾條件，在 WHERE 子句撤逢、SELECT 子句或其他地方過濾都不行

?SELECT a.key, a.value

FROM a

WHERE a.key in

(SELECT b.key

FROM B);

可以被重寫為：

SELECT a.key, a.val

FROM a LEFT SEMI JOIN b on (a.key = b.key)

UNION ALL

?用來合并多個select的查詢結果，需要保證select中字段須一致

?select_statement UNION ALL select_statement UNION ALL select_statement ...

4. ?從SQL到HiveQL應轉(zhuǎn)變的習慣

1粮坞、Hive不支持等值連接

?SQL中對兩表內(nèi)聯(lián)可以寫成：

?select * from dual a,dual b where a.key = b.key;

?Hive中應為

?select * from dual a join dual b on a.key = b.key;

而不是傳統(tǒng)的格式：

SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2 WHERE t1.a2 = t2.b2

2蚊荣、分號字符

?分號是SQL語句結束標記，在HiveQL中也是莫杈，但是在HiveQL中互例，對分號的識別沒有那么智慧，例如：

?select concat(key,concat(';',key)) from dual;

?但HiveQL在解析語句時提示：

FAILED: Parse Error: line 0:-1 mismatched input '' expecting ) in function specification

?解決的辦法是筝闹，使用分號的八進制的ASCII碼進行轉(zhuǎn)義媳叨，那么上述語句應寫成：

?select concat(key,concat('\073',key)) from dual;

3、IS [NOT] NULL

?SQL中null代表空值,?值得警惕的是, 在HiveQL中String類型的字段若是空(empty)字符串, 即長度為0, 那么對它進行IS NULL的判斷結果是False.

4关顷、Hive不支持將數(shù)據(jù)插入現(xiàn)有的表或分區(qū)中糊秆，

僅支持覆蓋重寫整個表，示例如下：

INSERT OVERWRITE TABLE t1

SELECT * FROM t2;

4议双、hive不支持INSERT INTO, UPDATE, DELETE操作

這樣的話痘番，就不要很復雜的鎖機制來讀寫數(shù)據(jù)。

INSERT INTO syntax is only available starting in version 0.8平痰。INSERT INTO就是在表或分區(qū)中追加數(shù)據(jù)汞舱。

5、hive支持嵌入mapreduce程序宗雇，來處理復雜的邏輯

如：

FROM (

MAP doctext USING 'python wc_mapper.py' AS (word, cnt)

FROM docs

CLUSTER BY word

) a

REDUCE word, cnt USING 'python wc_reduce.py';

--doctext: 是輸入

--word, cnt: 是map程序的輸出

--CLUSTER BY: 將wordhash后昂芜，又作為reduce程序的輸入

并且map程序、reduce程序可以單獨使用赔蒲，如：

FROM (

FROM session_table

SELECT sessionid, tstamp, data

DISTRIBUTE BY sessionid SORT BY tstamp

) a

REDUCE sessionid, tstamp, data USING 'session_reducer.sh';

--DISTRIBUTE BY: 用于給reduce程序分配行數(shù)據(jù)

6泌神、hive支持將轉(zhuǎn)換后的數(shù)據(jù)直接寫入不同的表，還能寫入分區(qū)嘹履、hdfs和本地目錄腻扇。

這樣能免除多次掃描輸入表的開銷。

FROM t1

INSERT OVERWRITE TABLE t2

SELECT t3.c2, count(1)

FROM t3

WHERE t3.c1 <= 20

GROUP BY t3.c2

INSERT OVERWRITE DIRECTORY '/output_dir'

SELECT t3.c2, avg(t3.c1)

FROM t3

WHERE t3.c1 > 20 AND t3.c1 <= 30

GROUP BY t3.c2

INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'

SELECT t3.c2, sum(t3.c1)

FROM t3

WHERE t3.c1 > 30

GROUP BY t3.c2;

5. ?實際示例

創(chuàng)建一個表

CREATE TABLE u_data (

userid INT,

movieid INT,

rating INT,

unixtime STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '/t'

STORED AS TEXTFILE;

下載示例數(shù)據(jù)文件砾嫉，并解壓縮

wgethttp://www.grouplens.org/system/files/ml-data.tar__0.gz

tar xvzf ml-data.tar__0.gz

加載數(shù)據(jù)到表中:

LOAD DATA LOCAL INPATH 'ml-data/u.data'

OVERWRITE INTO TABLE u_data;

統(tǒng)計數(shù)據(jù)總量:

SELECT COUNT(1) FROM u_data;

現(xiàn)在做一些復雜的數(shù)據(jù)分析:

創(chuàng)建一個 weekday_mapper.py: 文件幼苛，作為數(shù)據(jù)按周進行分割

import sys

import datetime

for line in sys.stdin:

line = line.strip()

userid, movieid, rating, unixtime = line.split('/t')

生成數(shù)據(jù)的周信息

weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()

print '/t'.join([userid, movieid, rating, str(weekday)])

使用映射腳本

//創(chuàng)建表，按分割符分割行中的字段值

CREATE TABLE u_data_new (

userid INT,

movieid INT,

rating INT,

weekday INT)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '/t';

//將python文件加載到系統(tǒng)

add FILE weekday_mapper.py;

將數(shù)據(jù)按周進行分割

INSERT OVERWRITE TABLE u_data_new

SELECT

TRANSFORM (userid, movieid, rating, unixtime)

USING 'python weekday_mapper.py'

AS (userid, movieid, rating, weekday)

FROM u_data;

SELECT weekday, COUNT(1)

FROM u_data_new

GROUP BY weekday;

處理Apache Weblog 數(shù)據(jù)

將WEB日志先用正則表達式進行組合焕刮，再按需要的條件進行組合輸入到表中

add jar ../build/contrib/hive_contrib.jar;

CREATE TABLE apachelog (

host STRING,

identity STRING,

user STRING,

time STRING,

request STRING,

status STRING,

size STRING,

referer STRING,

agent STRING)

ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'

WITH SERDEPROPERTIES (

"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^ /"]*|/"[^/"]*/") (-|[0-9]*) (-|[0-9]*)(?: ([^ /"]*|/"[^/"]*/") ([^ /"]*|/"[^/"]*/"))?",

"output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s"

)

STORED AS TEXTFILE;

?著作權歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末舶沿，一起剝皮案震驚了整個濱河市墙杯，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌括荡，老刑警劉巖高镐，帶你破解...
沈念sama閱讀 218,682評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異畸冲，居然都是意外死亡嫉髓，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,277評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門邑闲，熙熙樓的掌柜王于貴愁眉苦臉地迎上來算行，“玉大人，你說我怎么就攤上這事苫耸≈菪希” “怎么了？”我有些...
開封第一講書人閱讀 165,083評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵褪子，是天一觀的道長量淌。經(jīng)常有香客問我，道長嫌褪，這世上最難降的妖魔是什么呀枢？我笑而不...
開封第一講書人閱讀 58,763評論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮渔扎，結果婚禮上硫狞，老公的妹妹穿的比我還像新娘。我一直安慰自己晃痴，他們只是感情好残吩，可當我...
茶點故事閱讀 67,785評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著倘核，像睡著了一般泣侮。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上紧唱，一...
開封第一講書人閱讀 51,624評論 1贊 305
城市分裂傳說
那天活尊，我揣著相機與錄音，去河邊找鬼漏益。笑死蛹锰，一個胖子當著我的面吹牛，可吹牛的內(nèi)容都是我干的绰疤。我是一名探鬼主播铜犬，決...
沈念sama閱讀 40,358評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了癣猾？” 一聲冷哼從身側響起敛劝，我...
開封第一講書人閱讀 39,261評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎纷宇，沒想到半個月后夸盟，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,722評論 1贊 315
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡像捶，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,900評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年上陕，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片作岖。...
茶點故事閱讀 40,030評論 1贊 350
活死人
序言：一個原本活蹦亂跳的男人離奇死亡唆垃，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出痘儡，到底是詐尸還是另有隱情，我是刑警寧澤枢步，帶...
沈念sama閱讀 35,737評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布沉删，位于F島的核電站，受9級特大地震影響醉途，放射性物質(zhì)發(fā)生泄漏矾瑰。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 41,360評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一隘擎、第九天我趴在偏房一處隱蔽的房頂上張望殴穴。院中可真熱鬧，春花似錦货葬、人聲如沸采幌。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,941評論 0贊 22
一樁弒父案震桶，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽休傍。三九已至，卻和暖如春蹲姐，著一層夾襖步出監(jiān)牢的瞬間磨取，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,057評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工柴墩，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留忙厌，地道東北人。一個月前我還...
沈念sama閱讀 48,237評論 3贊 371
代替公主和親
正文我出身青樓江咳，卻偏偏與公主長得像逢净，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 44,976評論 2贊 355

大數(shù)據(jù)Hadoop Hive sql語法詳解無標題文章

推薦閱讀更多精彩內(nèi)容