Hadoop大資料實戰系列文章之Hive

hive是基於Hadoop的一個資料倉儲工具，可以將結構化的資料檔案對映為一張資料庫表，並提供完整的 sql 查詢功能，Hive 定義了簡單的類 SQL 查詢語言，稱為 HQL，它允許熟悉 SQL 的使用者查詢資料可以將 sql語句轉換為MapReduce任務進行執行，不必開發專門的 MapReduce。畢竟會寫 SQL 的人比寫 JAVA 的人多，這樣可以讓一大批運營人員直接獲取海量資料。在資料倉儲建設中，HIVE 靈活易用且易於維護，十分適合資料倉儲的統計分析。

本章內容：

1) Hive 簡介

2) Hive 基礎組成

3) Hive 執行流程

4) Hive 基礎操作

1. Hive 基礎 原理

hive 是建設在 Hadoop 之上，Hive 包括如下元件：CLI （command line interface）、

JDBC/ODBC、Thrift Server、WEB GUI、MetaStore 和 Driver(Complier、Optimizer 和

Executor)。

 

1) Driver 元件：包括 Complier、Optimizer 和 Executor，它的作用是將我們寫的HiveQL（類 SQL）語句進行解析、編譯優化，生成執行計劃，然後呼叫底層的MapReduce 計算框架。

2) Metastore 元件：後設資料服務元件儲存hive 的後設資料，hive的後設資料儲存在關聯式資料庫裡，hive 支援的關聯式資料庫有 derby、mysql。Hive 還支援把 metastore服務安裝到遠端的伺服器叢集裡，從而解耦 hive 服務和metastore 服務。

3) Thrift 服務：thrift 是 facebook 開發的一個軟體框架，它用來進行可擴充套件且跨語言的服務的開發，hive 整合了該服務，能讓不同的程式語言呼叫 hive 的介面。

4) CLI：command line interface，命令列介面。

5) Thrift 客戶端： hive 架構的許多客戶端介面是建立在 thrift 客戶端之上，包括JDBC 和 ODBC 介面。

6) WEBGUI：hive 客戶端提供了一種通過網頁的方式訪問 hive 所提供的服務。使用者介面主要有三個：CLI，Client 和 WUI。其中最常用的是 CLI，公司內可通過堡壘機連線 ssh hdp_lbg_ectech@10.126.101.7，直接輸入 hive，就可連線到 HiveServer。

Hive 的 metastore元件是 hive 後設資料集中存放地。Metastore元件包括兩個部分：metastore 服務和後臺資料的儲存。後臺資料儲存的介質就是關聯式資料庫，例如 hive 預設的嵌入式磁碟資料庫 derby，還有 mysql 資料庫。Metastore服務是建立在後臺資料儲存介質之上，並且可以和hive服務進行互動的服務元件，預設情況下，metastore服務和hive服務是安裝在一起的，執行在同一個程式當中。我也可以把metastore服務從hive服務裡剝離出來，metastore 獨立安裝在一個叢集裡，hive 遠端呼叫 metastore 服務，這樣我們可以把後設資料這一層放到防火牆之後，客戶端訪問 hive 服務，就可以連線到後設資料這一層，從而提供了更好的管理性和安全保障。使用遠端的 metastore服務，可以讓 metastore服務和 hive 服務執行在不同的程式裡，這樣也保證了 hive 的穩定性，提升了 hive 服務的效率。

對於資料儲存，Hive 沒有專門的資料儲存格式，可以非常自由的組織 Hive 中的表，只需要在建立表的時候告訴Hive資料中的列分隔符和行分隔符，Hive就可以解析資料。Hive中所有的資料都儲存在 HDFS 中，儲存結構主要包括資料庫、檔案、表和檢視。Hive 中包含以下資料模型：Table 內部表，External Table 外部表，Partition 分割槽，Bucket 桶。Hive預設可以直接載入文字檔案，還支援 sequence file 、RCFile。

Hive 的資料模型介紹如下：

1) Hive 資料庫

類似傳統資料庫的 DataBase，例如 hive >create database test_database;

2) 內部表

Hive 的內部表與資料庫中的表在概念上是類似。每一個 Table 在 Hive 中都有一個相應 的 目 錄 存 儲 數 據 。 例 如 一 個 表 hive_test ， 它 在 HDFS 中 的 路 徑 為/home/hdp_lbg_ectech/warehouse/hdp_lbg_ectech_bdw.db/hive_test ， 其 中/home/hdp_lbg_ectech/warehouse 是 在 hive-site.xml 中 由${hive.metastore.warehouse.dir}指定的資料倉儲的目錄，所有的 Table 資料（不包括外部表）都儲存在這個目錄中。刪除表時，後設資料與資料都會被刪除。

建表語句示例：

CREATE EXTERNAL TABLE hdp_lbg_ectech_bdw.hive_test

(`userid` string COMMENT'')

ROW FORMAT DELIMITED FIELDS TERMINATED BY'\001';

load data inpath ‘/home/hdp_lbg_ectech/resultdata/test.txt’overwrite into

table hive_test;

3) 外部表

外部表指向已經在 HDFS 中存在的資料，可以建立分割槽。它和內部表在後設資料的組織上是相同的，而實際資料的儲存則有較大的差異。內部表在載入資料的過程中，實際資料會被移動到資料倉儲目錄中。刪除表時，表中的資料和後設資料將會被同時刪除。而外部表只有一個過程，載入資料和建立表同時完成（CREATE EXTERNAL TABLE ……LOCATION），實際資料是儲存在 LOCATION 後面指定的 HDFS 路徑中，並不會移動到資料倉儲目錄中。當刪除一個外部表時，僅刪除該表的後設資料，而實際外部目錄的資料不會被刪除，推薦使用這種模式。

4) 分割槽

Partition相當於資料庫中的列的索引，但是Hive組織方式和資料庫中的很不相同。在Hive 中，表中的一個分割槽對應於表下的一個目錄，所有的分割槽資料都儲存在對應的目錄中。一般是按時間、地區、類目來分割槽，便於區域性查詢，避免掃描整個資料來源。

5) 桶

Buckets 是將表的列通過 Hash 演算法進一步分解成不同的檔案儲存。它對指定列計算hash，根據 hash 值切分資料，目的是為了並行，每一個 Bucket 對應一個檔案。例如將userid 列分散至 32 個 bucket，首先對 userid 列的值計算 hash，對應 hash 值為 0 的HDFS目錄為/home/hdp_lbg_ectech/resultdata/part-00000；hash值為20的HDFS目錄為/home/hdp_lbg_ectech/resultdata/part-00020。

6) Hive 的檢視

檢視與傳統資料庫的檢視類似。目前只有邏輯檢視，沒有物化檢視；檢視只能查詢，不能 Load/Insert/Update/Delete 資料；檢視在建立時候，只是儲存了一份後設資料，當查詢檢視的時候，才開始執行檢視對應的那些子查詢；

2. Hive 基礎操作

1) 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 。

例子 1：建立簡單表

CREATE TABLE pokes (foo INT, bar STRING);

例子 2：建立外部表

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

例子 3：建立分割槽表

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;

例子 4：建立 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;

例子 5：建立表並建立索引欄位 ds

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

例子 6：複製一個空表

CREATE TABLE empty_key_value_store

LIKE key_value_store;

例子 7：顯示所有表

SHOW TABLES;

例子 8：按正則條件（正規表示式）顯示錶

SHOW TABLES '.*s';

例子 9：表新增一列

ALTER TABLE pokes ADD COLUMNS (new_col INT);

例子 10：新增一列並增加列欄位註釋

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

例子 11：更改表名

ALTER TABLE events RENAME TO 3koobecaf;

例子 12：刪除列

DROP TABLE pokes;

例子 13：增加、刪除分割槽

增加：

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

例子 14：重新命名錶

ALTER TABLE table_name RENAME TO new_table_name

例子 15：修改列的名字、型別、位置、註釋

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type

[COMMENT col_comment] [FIRST|AFTER column_name]

這個命令可以允許改變列名、資料型別、註釋、列位置或者它們的任意組合

例子 16：建立／刪除檢視

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

刪除檢視

例子 17：建立資料庫

CREATE DATABASE name

例子 18：顯示命令

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 表中

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

例子 1：向資料表內載入檔案

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

例子 2：載入本地資料，同時給定分割槽資訊

LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites

PARTITION (ds='2008-08-15');

例子 3：載入本地資料，同時給定分割槽資訊

LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE invites PARTITION

(ds='2008-08-15');

例子 4：將查詢結果插入Hive 表

基本模式：

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

例子 3：將查詢結果寫入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 換行

例子 3：INSERT INTO

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

select_statement1 FROM from_statement

3) DQL 操作：資料查詢 SQL

DQL 包括：

 基本的 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 可以限制查詢的記錄數

例子 1：按先件查詢

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

例子 2：將查詢資料輸出至目錄

INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE

a.ds='<DATE>';

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

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

例子 4：選擇所有列到本地目錄

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

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

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

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

INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE

a.ds='<DATE>';

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

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

例子 5：將一個表的統計結果插入另一個表中

INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM invites a WHERE a.foo >

0 GROUP BY a.bar;

FROM pokes t1 JOIN invites t2 ON (t1.bar = t2.bar) INSERT OVERWRITE TABLE events

SELECT t1.bar, t1.foo, t2.foo;

例子 6：將多表資料插入到同一表中

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;

例子 7：將檔案流直接插入檔案

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