Hive的基本文法

Point 1：

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 以什麼格式作為Row_format

使用者在建表的時候可以自定義 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 。

1.3 建立簡單表：

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

1.4 建立外部表：

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

1.5 建分區表

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;

1.6 建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  BUCKETS
ROW FORMAT DELIMITED ‘\t’
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;

1.7 建立表并建立索引字段ds

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

1.8 複制一個空表

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

1.9 顯示所有表：

　　hive> SHOW TABLES;

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

　　hive> SHOW TABLES ‘.*s’;

•增加分區、删除分區
•重命名表
•修改列的名字、類型、位置、注釋
•增加/更新列
•增加表的中繼資料資訊

1.21 表添加一列：

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

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

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

1.23 更改表名：

　　hive> ALTER TABLE events RENAME TO 3koobecaf;

1.24 删除列：

　　hive> DROP TABLE pokes;

1.25 增加、删除分區

•增加

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION ‘location1’ ] partition_spec [ LOCATION ‘location2’ ] …

•删除

　　ALTER TABLE table_name DROP partition_spec, partition_spec,…

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

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

1.31 增加表的中繼資料資訊

　　ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:

:[property_name = property_value…..]

•使用者可以用這個指令向表中增加metadata

1.31改變表檔案格式與組織

　　ALTER TABLE table_name SET FILEFORMAT file_format

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

　　這個指令修改了表的實體存儲屬性

1.4 建立／删除視圖

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

•删除視圖

1.5 建立資料庫

　　CREATE DATABASE name

1.6 顯示指令

•show tables;

•show databases;

•show partitions ;

•show functions

•describe extended table_name dot col_name

2、DML 操作:中繼資料存儲

　　hive不支援用insert語句一條一條的進行插入操作，也不支援update操作。資料是以load的方式加載到建立好的表中。資料一旦導入就不可以修改。

DML包括：INSERT插入、UPDATE更新、DELETE删除

•向資料表内加載檔案

•将查詢結果插入到Hive表中

•0.8新特性 insert into

2.1.0 向資料表内加載檔案

•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

例如：

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

2.1.1 加載本地資料，同時給定分區資訊

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

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

LOCAL關鍵字

•指定了LOCAL，即本地

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

•load 指令會将 filepath 中的檔案複制到目标檔案系統中。目标檔案系統由表的位置屬性決定。被複制的資料檔案移動到表的資料對應的位置

例如：加載本地資料，同時給定分區資訊：

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

• 沒有指定LOCAL

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

•如果路徑不是絕對的，Hive 相對于 /user/ 進行解釋。 Hive 會将 filepath 中指定的檔案内容移動到 table （或者 partition）所指定的路徑中

2.1.2 加載DFS資料，同時給定分區資訊：

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

•目标表（或者分區）中的内容（如果有）會被删除，然後再将 filepath 指向的檔案/目錄中的内容添加到表/分區中。

•如果目标表（分區）已經有一個檔案，并且檔案名和 filepath 中的檔案名沖突，那麼現有的檔案會被新檔案所替代。

2.1.3 将查詢結果插入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

2.1.5 将查詢結果寫入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換行

2.1.6 INSERT INTO

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement DQL 操作:資料查詢SQL

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選項區分對重複記錄的處理。預設是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

例如

按先件查詢

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

将查詢資料輸出至目錄：

　　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;

将一個表的統計結果插入另一個表中：

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;

将多表資料插入到同一表中：

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 子句建表，查詢就可以利用分區剪枝（input pruning）的特性

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

3.3 Join

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)

4、從SQL到HiveQL應轉變的習慣

4.1、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;

而不是傳統的格式：

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

4.2、分号字元

•分号是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;

4.3、IS [NOT] NULL

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

4.4、Hive不支援将資料插入現有的表或分區中，

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

INSERT OVERWRITE TABLE t1

SELECT * FROM t2; INSERT OVERWRITE TABLE t1SELECT * FROM t2;

4.5、hive不支援INSERT INTO, UPDATE, DELETE操作

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

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程式配置設定行資料

4.7、hive支援将轉換後的資料直接寫入不同的表，還能寫入分區、hdfs和本地目錄。

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

FROM t1  

INSERT OVERWRITE TABLE t2  

SELECT t3.c2, count()  

FROM t3  

WHERE t3.c1 <=   

GROUP BY t3.c2  



INSERT OVERWRITE DIRECTORY '/output_dir'  

SELECT t3.c2, avg(t3.c1)  

FROM t3  

WHERE t3.c1 >  AND t3.c1 <=   

GROUP BY t3.c2  



INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'  

SELECT t3.c2, sum(t3.c1)  

FROM t3  

WHERE t3.c1 >   

GROUP BY t3.c2;

5、實際示例

5.1 建立一個表

CREATE TABLE u_data (
userid INT,
movieid INT,
rating INT,
unixtime STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t'
STORED AS TEXTFILE;

下載下傳示例資料檔案，并解壓縮

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

tar xvzf ml-data.tar__0.gz

5.2 加載資料到表中:

LOAD DATA LOCAL INPATH 'ml-data/u.data'
OVERWRITE INTO TABLE u_data;

5.3 統計資料總量:

　　SELECT COUNT(1) FROM u_data;

5.4 現在做一些複雜的資料分析:

　　建立一個 weekday_mapper.py: 檔案，作為資料按周進行分割

　import sys
import datetime

for line in sys.stdin:
line = line.strip()
userid, movieid, rating, unixtime = line.split('/t')

5.5 生成資料的周資訊

weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '/t'.join([userid, movieid, rating, str(weekday)])

5.6 使用映射腳本

//建立表，按分割符分割行中的字段值

CREATE TABLE u_data_new (
userid INT,
movieid INT,
rating INT,
weekday INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '/t';

//将python檔案加載到系統

add FILE weekday_mapper.py;

5.7 将資料按周進行分割

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()
FROM u_data_new
GROUP BY weekday;

處理Apache Weblog 資料

将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" = "([^ ]*) ([^ ]*) ([^ ]*) (-|//[[^//]]*//]) ([^ /"]*|/"[^/"]*/") (-|[-]*) (-|[-]*)(?: ([^ /"]*|/"[^/"]*/") ([^ /"]*|/"[^/"]*/"))?",
"output.format.string" = "%$s %$s %$s %$s %$s %$s %$s %$s %$s"
)
STORED AS TEXTFILE;

Hive的基本文法

繼續閱讀

Hive（二）--分區分桶，内部表外部表

大資料高頻面試題之Hive的小檔案合并

世界因大資料而改變

人工智能教育是轉型的新風口？

hive sql通過具體位址解析出行政區劃(省＞市＞區＞縣＞鄉＞鎮＞村)

25張圖詳解 | 大型分布式電商系統架構（二）

Spark的RDD轉換算子-雙value型Spark的RDD轉換算子-雙value型

記一次msyql InnoDB導緻資料庫崩潰，資料庫重新開機失敗的問題

Hive最全常見錯誤及解決方案hive --service metastore &

應用實踐 | 物易雲通基于 Apache Doris 的實時資料倉庫建設業務背景數倉架構演進新架構的優勢系統重點功能新架構的收益問題與經驗寫在最後

Apache Doris 系列：基礎篇-使用BitMap函數精準去重（2）

《Hive權威指南》第八章：HiveQL索引8 HiveQL：索引

2021-09-30一碼在手安全無憂從農田到餐桌，全流程追溯四大子產品，助力客戶實作品牌化

HiveQl語句應用執行個體：WordCount具體步驟如下：

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

2021-2025年中國運動療法（KT）帶行業市場供需與戰略研究報告