當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

深入浅出学Hive：Hive QL

發布時間：2024/1/17 编程问答 28 豆豆

生活随笔收集整理的這篇文章主要介紹了深入浅出学Hive：Hive QL 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

第一部分：ＤＤＬ

DDL

?建表

?刪除表

?修改表結構

?創建／刪除視圖

?創建數據庫

?顯示命令

建表

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 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用 IF NOT EXIST 選項來忽略這個異常

?EXTERNAL 關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION）

?LIKE 允許用戶復制現有的表結構，但是不復制數據

?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 確定表的具體的列的數據。

?STORED AS

??????????? SEQUENCEFILE

??????????? | TEXTFILE

??????????? | RCFILE????

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

?????? 如果文件數據是純文本，可以使用 STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCE 。

建立外部表

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

建分區表

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;

復制一個空表

CREATE TABLE empty_key_value_store

LIKE key_value_store;

刪除表

DROP TABLE table_name

修改表結構

?增加分區、刪除分區

?重命名表

?修改列的名字、類型、位置、注釋

?增加/更新列

?增加表的元數據信息

增加、刪除分區

?增加

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]

?這個命令可以允許改變列名、數據類型、注釋、列位置或者它們的任意組合

增加/更新列

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

????

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

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

增加表的元數據信息

?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

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

創建／刪除視圖

?CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)]?AS SELECT

?增加視圖

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

?如果修改基本表的屬性，視圖中不會體現，無效查詢將會失敗

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

?DROP VIEW view_name

?刪除視圖

創建數據庫

?CREATE DATABASE name

顯示命令

?show tables;

?show databases;

?show partitions ;

?show functions

?describe extended table_name dot col_name

第二部分：ＤＭＬ

DML

?向數據表內加載文件

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

?0.8新特性 insert into

向數據表內加載文件

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

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

?filepath

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

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

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

向數據表內加載文件

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

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

LOCAL關鍵字

?指定了LOCAL

?load 命令會去查找本地文件系統中的 filepath。如果發現是相對路徑，則路徑會被解釋為相對于當前用戶的當前路徑。用戶也可以為本地文件指定一個完整的 URI，比如：file:///user/hive/project/data1.

?load 命令會將 filepath 中的文件復制到目標文件系統中。目標文件系統由表的位置屬性決定。被復制的數據文件移動到表的數據對應的位置

??沒有指定LOCAL

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

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

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

OVERWRITE

?指定了OVERWRITE

?目標表（或者分區）中的內容（如果有）會被刪除，然后再將 filepath 指向的文件/目錄中的內容添加到表/分區中。

?如果目標表（分區）已經有一個文件，并且文件名和 filepath 中的文件名沖突，那么現有的文件會被新文件所替代。

將查詢結果插入Hive表

?將查詢結果插入Hive表

?將查詢結果寫入HDFS文件系統

?基本模式

???? 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] ...

?自動分區模式

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

將查詢結果寫入HDFS文件系統

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

??????? FROM from_statement

??????? INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

???? [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]

?數據寫入文件系統時進行文本序列化，且每列用^A 來區分，\n換行

INSERT?INTO?

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

第三部分：HiveQL 查詢操作

SQL操作

?基本的Select 操作

?基于Partition的查詢

?Join

基本的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選項區分對重復記錄的處理。默認是ALL，表示查詢所有記錄。DISTINCT表示去掉重復的記錄

?Where 條件

?類似我們傳統SQL的where 條件

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

?IN, NOT IN

?不支持EXIST ,NOT EXIST

ORDER BY與SORT BY的不同

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

?SORT BY 只在本機做排序

Limit

?Limit 可以限制查詢的記錄數

SELECT * FROM t1 LIMIT 5

?實現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

基于Partition的查詢

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

?Hive 當前的實現是，只有分區斷言出現在離 FROM 子句最近的那個WHERE 子句中，才會啟用分區剪枝

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 不支持所有非等值的連接，因為非等值連接非常難轉化到 map/reduce 任務

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

?LEFT SEMI JOIN 是 IN/EXISTS 子查詢的一種更高效的實現

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

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

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 會被轉化為單個 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 子句中寫

?容易混淆的問題是表分區的情況

??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 ...

第四部分：從SQL到HiveQL應該轉變的幾個習慣

Hive不支持等值連接

?SQL中對兩表內聯可以寫成：

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

?Hive中應為

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

分號字符

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

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

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

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

?解決的辦法是，使用分號的八進制的ASCII碼進行轉義，那么上述語句應寫成：

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

IS [NOT] NULL

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

轉載請注明【?http://sishuok.com/forum/blogPost/list/6227.html】

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的深入浅出学Hive：Hive QL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。