一、Hive基礎

Hive是一個SQL解析引擎，將SQL語句轉譯成MR Job，然後在Hadoop平臺上執行，達到快速開發的目的。

在沒有hive之前，對HDFS上的檔案或HBase中的表進行查詢時，要手工寫一堆MapReduce程式碼，只能由懂MapReduce的程式設計師才能搞定，耗時耗力。為解決這個問題，FaceBook實現並開源Hive，解決海量結構化日誌查詢。

Hive是資料倉儲，和傳統的資料庫有一定的區別。hive中的表是純邏輯表，即表的後設資料。本質就是Hadoop的目錄/檔案，達到了後設資料與資料儲存分離的目的。Hive本身不儲存資料，它完全依賴HDFS和MapReduce。

– Hive的內容是讀多寫少，不支援對資料的改寫和刪除，只能對資料進行批量地匯入匯出。

【讀時模式：hive只有讀的時候才會檢查資料，解析欄位和schema（schema是資料結構的表達）。優點是寫資料非常快。】

【寫時模式：寫的比較慢，它寫的過程中是需要去建立索引，壓縮、資料一致性、欄位檢查等等，但讀的時候會很快。】

二、Hive概念

Hive是基於Hadoop的一個資料倉儲工具，可以將結構化的資料檔案對映成一張表，並提供類SQL查詢功能（HQL），處理的資料儲存在HDFS，用MapReduce實現底層的資料分析，適合離線處理，執行程式執行在YARN。本質是：將HQL轉換成MapReduce程式。下面介紹幾個關鍵元件。

1.使用者介面: Client

使用者介面用來連線hive服務。其中CLI(hive shell)、JDBC/ODBC(java訪問hive)比較常用，而WEBUI(瀏覽器訪問hive)只能做資料查詢，因此使用較少。

1）cli

命令列模式方式最為常用，將在後面詳細介紹。

2）webui

# hive --service hwi #啟動webui服務，通過http://192.168.101.10:9999/hwi可檢視，但是隻能做資料查詢用，不常用

為了使上述命令能夠正常使用，首先進行2個步驟。第一步先在hive-site.xml中新增如下的配置。

<property> 
    <name>hive.hwi.listen.host</name>
    <value>0.0.0.0</value>
  </property>
  <property>
    <name>hive.hwi.listen.port</name>
    <value>9999</value>
  </property>
  <property>
    <name>hive.hwi.war.file</name>
    <value>lib/hive-hwi-1.1.0.war</value>
  </property>

[root@master lib]# cp /usr/local/src/jdk1.8.0_144/lib/tools.jar . #第二步將tools.jar拷貝到hive的lib目錄下，完成上述2步,即可正常使用啟動命令

3）hiveserver

以JDBC/ODBC的程式登入到hive中運算元據，必須選用遠端服務啟動方式。

2.後設資料: Metastore

hive的後設資料包括：表名、表所屬的資料庫（預設是default）、表的擁有者、列/分割槽欄位、表的型別（是否是外部表）、表的資料所在目錄等；預設儲存在自帶的derby資料庫中，推薦使用採用MySQL儲存Metastore。

3.底層架構：Hadoop

使用HDFS儲存表格資料，使用MapReduce進行計算。

4.驅動器: Driver

包含：解析器、編譯器、優化器、執行器。

解析器：將SQL字串轉換成抽象語法樹(AST)，對AST進行語法分析，比如表是否存在、欄位是否存在、SQL語義是否有誤（比如select中被判定為聚合的欄位在group by中是否有出現）；

編譯器：將AST編譯生成邏輯執行計劃；

優化器：對邏輯執行計劃進行優化；

執行器：把邏輯執行計劃轉換成可以執行的物理計劃。對於Hive來說，就是MR/TEZ/Spark；

【AST(abstract syntax tree): 是原始碼的抽象語法結構的樹狀表示，樹上的每個節點都表示原始碼中的一種結構，之所以說是抽象的，是因為抽象語法樹並不會表示出真實語法出現的每一個細節，比如說，巢狀括號被隱含在樹的結構中，並沒有以節點的形式呈現。抽象語法樹並不依賴於源語言的語法，也就是說語法分析階段所採用的上下文無文文法，因為在寫文法時，經常會對文法進行等價的轉換（消除左遞迴，回溯，二義性等），這樣會給文法分析引入一些多餘的成分，對後續階段造成不利影響，甚至會使合個階段變得混亂。因些，很多編譯器經常要獨立地構造語法分析樹，為前端、後端建立一個清晰的介面。】

5.hive優點

hive操作介面採用類SQL語法，提供快速開發的能力(簡單、容易上手)；避免了去寫MapReduce，減少開發人員的學習成本；統一的後設資料管理，可與impala/spark等共享後設資料；易擴充套件(HDFS+MapReduce：可以擴充套件叢集規模；支援自定義函式)等等。

Hive的執行延遲比較高，因此hive常用於資料分析的，對實時性要求不高的場合；Hive優勢在於處理大資料，對於處理小資料沒有優勢，因為Hive的執行延遲比較高。

三、Hive-1.1.0的安裝

1.解壓hive-1.1.0-cdh5.7.0.tar.gz

tar -zxvf hive-1.1.0-cdh5.7.0.tar.gz 

2.安裝mysql

將metastore存放在mysql中，能使得多個機器都能訪問metastore，開啟多個hive視窗，如果後設資料在預設的derby中，只能開啟一個視窗。

[root@master ~]# rpm -qa | grep mysql 首先檢視是否安裝了mysql，有的話，如果版本不對，先解除安裝，centos7安裝的是MariaDB，需要替換掉

# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
# rpm -ivh mysql-community-release-el7-5.noarch.rpm #安裝後mysql將會替換centos7中的MariaDB
# yum install mysql-community-server
# service mysqld restart

初次安裝mysql，root賬戶沒有密碼，可以直接登入。

# mysql -u root 

設定密碼

mysql> use mysql; #首先要切換到mysql的資料庫。
mysql> select host,user,password from user;#首先檢視目前的使用者和密碼資訊。
mysql> set password for'root'@'master' =password('123456');#使master能夠登入hive，密碼為123456
mysql> set password for'root'@'localhost' =password('123456');#將本地登入也採用相同的密碼
mysql> exit；

mysql> update user set password=password('123456') where user='root' and host='localhost';也可以採用這條命令進行密碼設定

注：可以讓使用者進行遠端登入，如下設定。

mysql> GRANT ALL PRIVILEGES ON . TO ‘user’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION; 
mysql> flush privileges;
mysql> exit;
設定使用者名稱為：user，密碼為：password的賬號可能通過任意一臺機器訪問資料庫。

3.配置檔案

1）hive-site.xml，連線mysql

<configuration>
<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://master:3306/hivemetastore?createDatabaseIfNotExist=true</value>#設定hive後設資料的名稱
</property>
<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>#配置驅動
</property>
<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
</property>#配置使用者
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>123456</value>#配置密碼
</property>
</configuration>

將mysql-connector-java-5.1.27-bin.jar拷貝到hive的lib目錄下。

為了在使用hive時，能夠知道當前使用的database是哪個，可以繼續新增配置資訊如下。

<property>
    <name>hive.cli.print.header</name>
    <value>true</value>
</property>
<property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
</property>

2）hive-en.sh

HADOOP_HOME=/usr/local/src/hadoop-2.6.0-cdh5.7.0

4.配置系統環境變數

export HIVE_HOME=/usr/local/src/hive-1.1.0-cdh5.7.0
export PATH=$PATH:$HIVE_HOME/bin

5.在hdfs上建立目錄

首先確保HDFS正常執行，之後進行命令。hive的後設資料預設是存在hdfs上。

$ bin/hadoop fs -mkdir /tmp
$ bin/hadoop fs -p -mkdir /user/hive/warehouse
* 修改目錄許可權
$ bin/hadoop fs -chmod g+w /tmp
$ bin/hadoop fs -chmod g+w /user/hive/warehouse

這時候在輸入hive即可啟動hive。同時使用 mysql -uroot -p123456登入資料庫，在檢視databases時，能夠看到一個叫做hivemetastore的後設資料。

6.修改Hive日誌資訊

$ mv hive-log4j.properties.template hive-log4j.properties

修改其中一條：

hive.log.dir=/usr/local/src/hive-1.1.0-cdh5.7.0/logs

四、Hive基礎命令

1.bin/hive

# bin/hive -help 檢視使用命令
# bin/hive --hiveconf<property=value>   可以在未進入hive時，直接進行相應的配置
# bin/hive -e <quoted-query-string>     直接在未進入hive時，進行hive的操作。
   例如：bin/hive-e "select * from db_hive.student ;"

# bin/hive -f <filename> ，這個filename就是執行hive命令的指令碼
   例如：# touch hivef.sql，加入如下的內容：select* from db_hive.student ;
   # bin/hive -f /opt/datas/hivef.sql
   # bin/hive -f /opt/datas/hivef.sql > /opt/datas/hivef-res.txt 輸出結果

* bin/hive -i <filename>，與使用者udf相互使用

進入hive命令列模式下：

hive(default)> dfs -ls / ; 在hive cli命令視窗中如何檢視hdfs檔案系統

hive(default)> !ls /opt/datas;在hive cli命令視窗中如何檢視本地檔案系統

hive > set ;檢視當前所有的配置資訊
hive (db_hive)> set system:user.name ;檢視某個屬性的配置
hive (db_hive)> set system:user.name=beifeng ;為某個屬性進行配置，此種方式，設定屬性的值，僅僅在當前會話session生效

注：以bin/hive --hiveconf hive.root.logger=INFO,console啟動hive，可以將日誌資訊直接輸出到控制檯

2.資料庫的操作

建立資料庫的幾種寫法：

create database db_hive_01 ;
create database if not exists db_hive_01 ;標準寫法
create database if not exists db_hive_01 location /user/hive/warehouse/db_hive_01.db' ;

# hadoop dfs -ls /user/hive/warehouse，可以看到hdfs上建立了db_hive_01資料庫。

檢視建立的資料庫資訊：

desc database db_hive_01 ;
desc database extended db_hive_01 ;更詳細

刪除資料庫：

drop database db_hive_03 ;
drop database db_hive_03 cascade;級聯刪除
drop database if exists db_hive_03 ;標準寫法

3.表的操作

hive> create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
hive> load data local inpath '/opt/datas/student.txt' into table db_hive.student;
hive> desc extended student;檢視完整的表資訊
hive> desc formatted student;檢視格式良好的資訊
hive> show functions;檢視hive的操作
hive> desc functions 具體函式名稱;
hive> desc extened functions 函式名稱; 更詳細的操作檢視

alter table dept_like rename to dept_like_rename ;改名
drop table if exists dept_like_rename ; 刪除表

truncate table dept_cats ;清除表dept_cats的資料

4.建立表的幾種方式

方式一：

create table IF NOT EXISTS default.bf_log_20150913( #前面加上default是為了指明表建立在該資料庫下
ip string COMMENT 'remote ip address' ,//引數介紹
user string ,
req_url string COMMENT 'user request url')
COMMENT 'Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE ;
load data local inpath '/opt/datas/bf-log.txt' into tabledefault.bf_log_20150913;

方式二：

create table IF NOT EXISTS table2 as select ip,req_url from table2;

通過方式一建立的表格資訊，來建立新的表格，相當於複製了表格的部分或者全部的資訊，這時候新建立的表就會有資料。

方式三：

create table if not exists default.dept_like like default.dept ;只是建立了結構，並沒有資料，這個表結構和default.dept一樣

5.匯出hive資料

方式一：輸出到本地

insert overwrite local directory '/opt/datas/hive_exp_emp' select * from default.emp ;

在本地目錄/opt/datas/下會建立一個hive_exp_emp資料夾，資料夾下存有匯出的資料。還可以按照下面方式指定分割符。

insert overwrite local directory '/opt/datas/hive_exp_emp'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMSTERMINATED BY '\n'
select * from default.emp ;修改輸出格式

方式二：採用-e方式

bin/hive -e "select * from default.emp ;" >/opt/datas/exp_res.txt

方式三：輸出到hdfs系統

insert overwrite directory '/user/hive/hive_exp_emp' select* from default.emp; 放在檔案系統上

五、Hive表的管理

hive表的本質就是Hadoop的目錄/檔案，實際的資料儲存在hdfs上，hive只是操作這些資料。

1.外部表

管理表（內部表），刪除表時，會刪除表資料以及後設資料，不特別指定時，預設都是內部表。

託管表（外部表），用的多，刪除時候，只會刪除後設資料，不會刪除表資料。

dfs -put /opt/emp.txt /user/hive/warehouse/emp_ext2  #首先將一個日誌檔案上傳到dfs系統中emp_ext2目錄下

create EXTERNAL table IF NOTEXISTS default.emp_ext2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location'/user/hive/warehouse/emp_ext2';必須要指定路徑，否則載入不了資料

這時候建立的default.emp_ext2會自動載入剛剛上傳的emp_ext2表資料。如果在/user/hive/warehouse/emp_ext2這個路徑下面，上傳了多個日誌檔案，那麼建立的emp_ext2表會將這些資料全部載入，不論格式是否正確（錯誤的格式在表中會顯示為null）。

需要注意的是，如果在該目錄下也建立了一個管理表，管理表也可以讀取到該目錄下的資料檔案，管理表刪除了將會把資料也刪除掉，導致外部表也讀取不到資料。外部表會顯示空。

2.分割槽表

在Hive 中，表中的一個 Partition 對應表下的一個目錄，所有的 Partition 的資料都儲存在對應的目錄中。

– 例如：pvs 表中包含 ds 和 city 兩個 Partition，則

    – 對應於 ds = 20090801,ctry = US 的 HDFS 子目錄為：/wh/pvs/ds=20090801/ctry=US；

    – 對應於 ds = 20090801,ctry = CA 的 HDFS 子目錄為；/wh/pvs/ds=20090801/ctry=CA

partition將資料按照一定的規格和條件進行管理，目的是為了輔助查詢，縮小查詢範圍，加快資料的檢索速度，是個非常重要的概念。

create EXTERNAL table IF NOT EXISTS default.emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)partitioned by (month string,day string) #這裡要注意分割槽的欄位在建立表中並不存在，但是用desc檢視錶結構，可以看到多了分割槽欄位
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

hive (default)> desc emp_partition;
OK
col_name	data_type	comment
empno               	int                 	                    
ename               	string              	                    
job                 	string              	                    
mgr                 	int                 	                    
hiredate            	string              	                    
sal                 	double              	                    
comm                	double              	                    
deptno              	int                 	                    
month               	string              	                    
day                 	string              	                    
	 	 
# Partition Information	 	 
# col_name            	data_type           	comment             
	 	 
month               	string              	                    
day                 	string 

load data local inpath '/opt/datas/emp.txt' into table default.emp_partition partition(month='201509',day='13') ;載入資料，必須要指明分割槽，這個時候在hdfs上就可以看到相應的資料夾。
select * from emp_partition where month = '201509' and day ='13' ;查詢的時候也必須指明具體查的是哪個分割槽。

3.分割槽表注意問題

create table IF NOT EXISTS default.dept_part(
deptno int,
dname string,
loc string
)partitioned by (day string)
ROW FORMAT DELIMITED FIELDSTERMINATED BY '\t';

dfs -mkdir -p/user/hive/warehouse/dept_part/day=20150913 ;
dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20150913 ;

建立表時，並沒有指明該表時分割槽表，如果通過手動方式建立分割槽，hive的後設資料並不知道有這個分割槽的存在，所以該表格只是普通的表格，如果要將該表格轉換為分割槽表，需要進行修復操作。

hive (default)>msck repair table dept_part ;
hive (default)>alter table dept_part add partition(day='20150913');

show partitions dept_part ;查詢分割槽的狀態

4.bucket桶表

Bucket類似於分割槽表，如果想存完整的資料又不想突破一張表的一個能力範圍，可以把一張表拆分成多個表，這個通常叫分庫。

主要目的是為了優化查詢。例如兩個大表都是通過userid來分桶，當做join的時候（自動啟用map端的map-side join），只要命中了第一張表的分桶ID，那麼就可以直接定位到第二張表分桶的ID，提高查詢的速度。第二個優點就是方便取樣。

hive中table可以拆分成partition，partition可以通過‘CLUSTERED BY’進一步分成bucket，bucket中的資料可以通過‘SORT BY’排序。

'set hive.enforce.bucketing = true' 首先開啟桶表功能

create external table rating_table_b
(userId INT,
movieId STRING,
rating STRING
)
clustered by (userId) into 32 buckets;

查詢表的結構：
desc formatted rating_table_b;

灌入輸入：
set hive.enforce.bucketing = true;
from rating_table
insert overwrite table rating_table_b
select userid, movieid, rating; #在表格的目錄下，將會自動生成32個桶表

• 檢視sampling資料：

– hive> select * from student tablesample(bucket 1 out of 2 on id);

– table sample是抽樣語句，語法：TABLESAMPLE( BUCKET x  OUT  OF  y )

– y必須是table總bucket數的倍數或者因子。

hive根據y的大小，決定抽樣的比例。例如，table總共分了64份，當y=32時，抽取(64/32=)2個bucket的資料，當y=128時，抽取(64/128=)1/2個bucket的資料。 x表示從哪個bucket開始抽取。例如，table總bucket數為32，tablesample(bucket 3 out of 16)，表示總共抽取（32/16=）2個bucket的資料，分別為第3個bucket和第（3+16=）19個bucket的資料。

5.複合型別

• 資料型別

    – 原生型別：TINYINT、SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、STRING、BINARY（Hive 0.8.0以上才可用）、TIMESTAMP（Hive 0.8.0以上才可用）

    – 複合型別：Arrays：ARRAY<data_type>、Maps:MAP<primitive_type, data_type>、Structs:STRUCT<col_name: data_type[COMMENT col_comment],……>、Union:UNIONTYPE<data_type, data_type,……>

例如：select name ,score[‘數學’]，就可以查詢到數學成績

INSERT OVERWRITE TABLE pv_users 
SELECT pv.pageid, u.age
FROM page_view pv
JOIN user u ON (pv.userid = u.userid);

6.transform

支援多語言，實現類似UDF功能。

1) 欄位拼接

第1種方法：建立AWK指令碼：

]# cat transform.awk
{
   print $1"_"$2
}

hive> add file /root/hive_test_3/transform_test/transform.awk; #新增指令碼

執行拼接功能：

select transform(movieid, title) using "awk -f transform.awk" as (uuu) from movie_table;

第2種方法：建立Python指令碼：

]# cat transform.py
import sys
for line in sys.stdin:
   ss = line.strip().split('\t')
   print '_'.join([ss[0].strip(), ss[1].strip()])

hive> add file/root/hive_test_3/transform_test/transform.py;

執行拼接功能：

hive> select transform(movieid, title)using "python transform.py" as (uuu) from movie_table;

2) wordcount

 首先建立文章表，灌入整篇文章：

hive> CREATE TABLE docs(line STRING);
hive> LOAD DATA INPATH '/The_Man_of_Property.txt' OVERWRITE INTO TABLE docs; #先把檔案放入hdfs

開發mapper程式碼：

]# cat mapper.py
import sys
for line in sys.stdin:
   ss = line.strip().split(' ')
   for word in ss:
       print '%s\t1' % (word)

hive> add file/root/hive_test_3/transform_wc/mapper.py;

測試map功能，輸出應該是“單詞+1”格式：

select transform(line) using "python mapper.py" as word,count from docs cluster by word;

開發reduce：

]# cat red.py
import sys
last_key = None
last_count = 0

for line in sys.stdin:
   ss = line.strip().split('\t')
   if len(ss) != 2:
       continue
   key = ss[0].strip()
   count = ss[1].strip()

   if last_key and last_key != key:
       print '%s\t%d' % (last_key, last_count)
       last_key = key
       last_count = int(count)
   else:
       last_key = key
       last_count += int(count)

hive> add file/root/hive_test_3/transform_wc/red.py;

執行map和reduce功能：

select
transform(wc.word, wc.count) using "python red.py" as w, c
from
(select transform(line) using "python mapper.py" as word,count from docs cluster by word) wc;

把wordcount結果儲存起來，建立一張表：

CREATE TABLE word_count(word STRING, countINT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

再次執行map和reduce功能：

insert overwrite table word_count
select
transform(wc.word, wc.count) using"python red.py" as w, c
from
(select transform(line) using "pythonmapper.py" as word,count from docs cluster by word) wc;

查詢確認結果：

hive> select * from word_count  order by count desc limit 10;

7.hive整合hbase

首先確保hbase正常啟動。

進入hbase shell終端，建立Hbase表：
hbase shell
create 'classes','user'

插入資料：
put 'classes','001','user:name','jack'
put 'classes','001','user:age','20'
put 'classes','002','user:name','liza'
put 'classes','002','user:age','18'

建立Hive表：

]# cat create_hive_hbase.sql       
create external table classes(id int,name string,age int)
STORED BY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping" = ":key,user:name,user:age")
TBLPROPERTIES("hbase.table.name"= "classes");

檢查表結構：

hive> desc formatted classes;

檢查資料：

select * from classes;

 刪除hbase某條記錄：

hbase(main):022:0> delete "classes", '001', 'user:name' #再次檢查hive中表格，會發現相應的欄位變成null

六、資料壓縮

    資料壓縮可以減小本地磁碟儲存空間和儲存到本地所用時間，即IO，同時減少網路IO傳輸壓力。主要的壓縮有以下幾種方式。

1.snappy

要採用snappy壓縮的話，要編譯Hadoop原始碼

1) 安裝sanppy，解壓即可（已經編譯過）

2) 編譯haodop 2.x原始碼

         mvn package-Pdist,native -DskipTests -Dtar -Drequire.snappy

編譯好之後會出現

/opt/modules/hadoop-2.5.0-src/target/hadoop-2.5.0/lib/native

3）進入到2.5.0-native-snappy，這是已經編譯好的檔案

4）將裡面的檔案全部拷貝到hadoop的lib/native下

5）檢查一下是否支援壓縮

$ bin/hadoop checknative

15/08/31 23:10:16 INFO bzip2.Bzip2Factory: Successfully loaded &initialized native-bzip2 library system-native

15/08/31 23:10:16 INFO zlib.ZlibFactory: Successfully loaded &initialized native-zlib library

Native library checking:

hadoop: true /opt/modules/hadoop-2.5.0/lib/native/libhadoop.so

zlib:   true /lib64/libz.so.1

snappy: true /opt/modules/hadoop-2.5.0/lib/native/libsnappy.so.1不替換的話，是false

lz4:    true revision:99

bzip2:  true /lib64/libbz2.so.1

2.MapReduce資料壓縮

6）進行測試，對比壓縮前後

bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0.jarwordcount /user/beifeng/mapreduce/wordcount/input /user/beifeng/mapreduce/wordcount/output

bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0.jarwordcount -Dmapreduce.map.output.compress=true-Dmapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec/user/beifeng/mapreduce/wordcount/input/user/beifeng/mapreduce/wordcount/output22

進入8088埠，進入history的configuration，可以看到設定的引數。

3.hive資料壓縮

在hive中設定也是如此：

set -Dmapreduce.map.output.compress=true

set -Dmapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec

七、檔案儲存的格式

hive有如下幾種檔案儲存方式。常用的是ORC和PARQUET。

 | SEQUENCEFILE序列化

  |TEXTFILE    -- (Default, depending on hive.default.fileformatconfiguration)

  |RCFILE      -- (Note: Available in Hive 0.6.0 andlater)

  |ORC         -- (Note: Available in Hive0.11.0 and later)

  |PARQUET     -- (Note: Available in Hive 0.13.0 and later) 列式儲存

  |AVRO        -- (Note: Available in Hive0.14.0 and later)

  |INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

1.textfile

create tablepage_views(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED ASTEXTFILE ;
load data localinpath '/opt/datas/page_views.data' into table page_views ;
dfs -du -h /user/hive/warehouse/page_views/ ;
18.1M /user/hive/warehouse/page_views/page_views.data

2.orc

create tablepage_views_orc(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc ;
insertinto table page_views_orc select * from page_views ;企業中生成的方法
dfs -du -h/user/hive/warehouse/page_views_orc/ ;
2.6M /user/hive/warehouse/page_views_orc/000000_0

3.parquet

create tablepage_views_parquet(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET;
insert into tablepage_views_parquet select * from page_views ;
dfs -du -h/user/hive/warehouse/page_views_parquet/ ;
13.1M /user/hive/warehouse/page_views_parquet/000000_0

例子：採用snappy壓縮

create tablepage_views_orc_snappy(
track_timestring,
url string,
session_idstring,
referer string,
ip string,
end_user_idstring,
city_id string
)
ROW FORMATDELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties("orc.compress"="SNAPPY");
insert into tablepage_views_orc_snappy select * from page_views ;
dfs -du -h/user/hive/warehouse/page_views_orc_snappy/ ;有3M多，是正常的，因為預設的bzip壓縮是空間最小的

八、UDF、UDAF、UDTF

UDF、UDAF、UDTF只能用java程式碼編寫，根據具體的業務不同編寫不同的程式碼，以下是UDF的例子，講解怎麼給hive配置自己編寫的函式，具體的內容不展開。

1.配置下pom.xml

                <!-- Hive Client -->
 <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>${hive.version}</version>
 </dependency>
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>${hive.version}</version>
</dependency>

2.配置函式

add jar /opt/datas/hiveudf.jar ;
create temporary function my_lower as "hive.udf.LowerUDF" ;
select ename, my_lower(ename) lowername from emp limit 5 ;

或者

CREATE FUNCTION self_lower AS 'hive.udf.LowerUDF'USING JAR 'hdfs://master:8020/user/hive/jars/hiveudf.jar'; jar包必須在檔案系統上
select ename, self_lower(ename) lowername from emp limit 5 ;

九、Hive優化

hive本質是MapReduce，所以很多情況下，hive優化就是針對MapReduce進行。

1.Map的優化：

– 作業會通過input的目錄產生一個或者多個map任務。 

– Map越多越好嗎？是不是保證每個map處理接近檔案塊的大小？事實上，map太多則會正價啟動成本，map太少又會較小並行度，所以必須合理取值。

– 如何合併小檔案，減少map數？

set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
sethive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

– 如何適當的增加map數？

set mapred.map.tasks=10; 事實上，影響比較小

– Map端聚合hive.map.aggr=true。Mr中的Combiners，提前將結果聚合。

2.Reduce的優化：

– hive.exec.reducers.bytes.per.reducer；reduce任務處理的資料量

reduce的個數= InputFileSize / bytesper reducer

– 調整reduce的個數：

• 設定reduce處理的資料量，設定多少就是多少

setmapred.reduce.tasks=10

select pt,count(1)
from popt_tbaccountcopy_mes
where pt = '2012-07-04' group by pt;

寫成如下，將會導致只有一個reduce執行

select count(1)
from popt_tbaccountcopy_mes
where pt = '2012-07-04';

存在聚合計算的場景：建議開啟group by，防止reduce任務退化成一個。

笛卡爾積：兩個表做join時候，會出現笛卡爾積現象，此時用on替代where。join還有如下的語句優化。

• 一個MR job
    SELECT a.val, b.val, c.val
    FROM a
    JOIN b ON (a.key = b.key1)
    JOIN c ON (a.key = c.key1)
• 生成多個MR job
    SELECT a.val, b.val, c.val
    FROM a
    JOIN b ON (a.key = b.key1)
    JOIN c ON (c.key = b.key1)

3.分割槽裁剪（partition）

Where中的分割槽條件，會提前生效，不必特意做子查詢，直接Join和Group By。

4.map join

 /*+ MAPJOIN(tablelist) */，必須是小表，不要超過1G，或者50萬條記錄，直接放在記憶體中，優化查詢速度。

select /*+ MAPJOIN(A) */ B.userid, A.movieid, B.rating #指定A是小表，不特別指定的話，會按照順序將第一個表放入記憶體中
from movie_table A
join rating_table B
on A.movieid == B.movieid 
limit 10;

5.Union all

union all 將兩個表直接合並。先做union all再做join或group by等操作可以有效減少MR過程，儘管是多個Select，最終只有一個mr，資料沒有去重，所以速度較快。

create table a as select * from movie_table limit 10;
create table b as select * from movie_table limit 10;

測試union all：

select count(1)
from (
    select c.id
    from(
        select movieid as id from a
        union all
        select movieid as id from b
    ) c
group by c.id) d;

測試union：更耗時
select count(1)
from (
    select movieid as id from a
    union
    select movieid as id from b) c;

6.Multi-insert & multi-group by

– 從一份基礎表中按照不同的維度，一次組合出不同的資料

FROM from_statement
      INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1)] select_statement1 group by key1
      INSERT OVERWRITE TABLE tablename2 [PARTITION(partcol2=val2 )] select_statement2 group by key2

7.Automatic merge

– 當檔案大小比閾值小時，hive會啟動一個mr進行合併

hive.merge.mapfiles= true 是否和並 Map 輸出檔案，預設為 True

hive.merge.mapredfiles = false 是否合併 Reduce 輸出檔案，預設為 False

hive.merge.size.per.task = 256*1000*1000 合併檔案的大小

8.Multi-Count Distinct

通常產生兩個MR Job，在第一個JOB中，map的輸出結果會隨機分佈到reduce中，每個reduce做部分的聚合操作，並輸出結果，在第二個job中，對於上面預處理的結果按照group by key分佈到reduce中，最終完成聚合操作。自動完成負載均衡，防止資料傾斜。

set hive.groupby.skewindata=true;

9.表連線順序

如果不特別指明，按照JOIN順序中的最後一個表應該儘量是大表，因為JOIN前一階段生成的資料會存在於Reducer的buffer中，通過stream最後面的表，直接從Reducer的buffer中讀取已經緩衝的中間結果資料（這個中間結果資料可能是JOIN順序中，前面表連線的結果的Key，資料量相對較小，記憶體開銷就小），與後面的大表進行連線時，只需要從buffer中讀取快取的Key，與大表中的指定Key進行連線，速度會更快，也可能避免記憶體緩衝區溢位。

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val
FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key1)；
a表被視為大表

SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a
JOIN b ON a.key = b.key;
MAPJION會把小表全部讀入記憶體中，在map階段直接拿另外一個表的資料和記憶體中表資料做匹配，由於在map是進行了join操作，省去了reduce執行的效率也會高很多。

使用hint的方式啟發JOIN操作

左連線時，左表中出現的JOIN欄位都保留，右表沒有連線上的都為空。

SELECT a.val, b.val
FROM a
LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

SELECT a.val, b.val
FROM a
LEFT OUTER JOIN b
ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

執行順序是，首先完成2表JOIN，然後再通過WHERE條件進行過濾，這樣在JOIN過程中可能會輸出大量結果，再對這些結果進行過濾，比較耗時。可以進行優化，將WHERE條件放在ON後，在JOIN的過程中，就對不滿足條件的記錄進行了預先過濾。

10.並行實行

同步執行hive的多個階段，hive在執行過程，將一個查詢轉化成一個或者多個階段。某個特定的job可能包含眾多的階段，而這些階段可能並非完全相互依賴的，也就是說可以並行執行的，這樣可能使得整個job的執行時間縮短。 

set hive.exec.parallel=true

11.資料傾斜

原因：key分佈不均導致的，人為的建表疏忽，業務資料特點

症狀：任務進度長時間維持在99%（或100%），檢視任務監控頁面，發現只有少量（1個或幾個）reduce子任務未完成。

檢視未完成的子任務，可以看到本地讀寫資料量積累非常大，通常超過10GB可以認定為發生資料傾斜。

傾斜度：平均記錄數超過50w且最大記錄數是超過平均記錄數的4倍。最長時長比平均時長超過4分鐘，且最大時長超過平均時長的2倍。

hive.groupby.skewindata=true #萬能方法

1）資料傾斜-大小表關聯

Hive在進行join時，按照join的key進行分發，而在join左邊的表的資料會首先讀入記憶體，如果左邊表的key相對分散，讀入記憶體的資料會比較小，join任務執行會比較快；而如果左邊的表key比較集中，而這張表的資料量很大，那麼資料傾斜就會比較嚴重，而如果這張表是小表，則還是應該把這張表放在join左邊。

方法：Small_table join big_table。這個可以通過指定大小表來完成。

2）資料傾斜-大大表關聯（通過系統機制）

日誌中有一部分的userid是空或者是0的情況，導致在用user_id進行hash分桶的時候，會將日誌中userid為0或者空的資料分到一起，導致了過大的斜率。

思路：把空值的key變成一個字串加上隨機數，把傾斜的資料分到不同的reduce上，由於null值關聯不上，處理後並不影響最終結果。

方法：過濾語句寫成如下形式。

on case when(x.uid = '-' or x.uid = '0‘ or x.uid is null) then concat('dp_hive_search',rand()) else x.uid end = f.user_id;

3）資料傾斜-大大表關聯（業務削減）

Select * from dw_log t join dw_user t1 on t.user_id=t1.user_id #兩個表都上千萬，跑起來很懸

思路： 當天登陸的使用者其實很少

方法：提前篩選出聚合key

 Select /*+MAPJOIN(t12)*/ *
 from dw_log t11
 join (
    select /*+MAPJOIN(t)*/ t1.*
    from (
          select user_id from dw_log group by user_id 取出使用者id
   ) t
    join dw_user t1
    ont.user_id=t1.user_id
    ) t12 on t11.user_id=t12.user_id

4）資料傾斜-聚合時存在大量特殊值

原因：做countdistinct時，該欄位存在大量值為NULL或空的記錄。

思路：count distinct時，將值為空的情況單獨處理，如果是計算count distinct，可以不用處理，直接過濾，在最後結果中加1。如果還有其他計算，需要進行group by，可以先將值為空的記錄單獨處理，再和其他計算結果進行union

• 方法

select cast(count(distinct (user_id))+1as bigint) as user_cnt
from tab_a
where user_id is not null and user_id<> ''

5）資料傾斜-空間換時間

Select day,count(distinct session_id),count(distinct user_id) from log a group by day;

問題：同一個reduce上進行distinct操作時壓力很大

方法：

select day,
count(case whentype='session' then 1 else null end) as session_cnt,
count(case whentype='user' then 1 else null end) as user_cnt
from (
selectday,session_id,type
from (
select day,session_id,'session'as type
from log
union all #空間換時間
select dayuser_id,'user' as type
from log
)
group by day,session_id,type
) t1
by d