資料倉庫-Hive

1. 資料倉庫

1.1. 基本概念

英文名稱為Data Warehouse，可簡寫為DW或DWH。資料倉庫的目的是建構面向分析的內建化資料環境，為企業提供決策支援（Decision Support）。

資料倉庫是存資料的，企業的各種資料往裡面存，主要目的是為了分析有效資料，後續會基于它産出供分析挖掘的資料，或者資料應用需要的資料，如企業的分析性報告和各類報表等。

可以了解為：

面向分析的存儲系統

。

1.2. 主要特征

資料倉庫是面向主題的（Subject-Oriented ）、內建的（Integrated）、非易失的（Non-Volatile）和時變的（Time-Variant ）資料集合，用以支援管理決策。

1.2.1. 面向主題

資料倉庫是面向主題的,資料倉庫通過一個個主題域将多個業務系統的資料加載到一起，為了各個主題（如：使用者、訂單、商品等）進行分析而建，操作型資料庫是為了支撐各種業務而建立。

1.2.2. 內建性

資料倉庫會将不同源資料庫中的資料彙總到一起,資料倉庫中的綜合資料不能從原有的資料庫系統直接得到。是以在資料進入資料倉庫之前，必然要經過統一與整合，這一步是資料倉庫建設中最關鍵、最複雜的一步(ETL)，要統一源資料中所有沖突之處，如字段的同名異義、異名同義、機關不統一、字長不一緻，等等。

1.2.3. 非易失性

操作型資料庫主要服務于日常的業務操作，使得資料庫需要不斷地對資料實時更新，以便迅速獲得目前最新資料，不至于影響正常的業務運作。

在資料倉庫中隻要儲存過去的業務資料，不需要每一筆業務都實時更新資料倉庫，而是根據商業需要每隔一段時間把一批較新的資料導入資料倉庫。

資料倉庫的資料反映的是一段相當長的時間内曆史資料的内容，是不同時點的資料庫的集合，以及基于這些快照進行統計、綜合和重組的導出資料。資料倉庫中的資料一般僅執行查詢操作，很少會有删除和更新。但是需定期加載和重新整理資料。

1.2.4. 時變性

資料倉庫包含各種粒度的曆史資料。資料倉庫中的資料可能與某個特定日期、星期、月份、季度或者年份有關。資料倉庫的目的是通過分析企業過去一段時間業務的經營狀況，挖掘其中隐藏的模式。雖然資料倉庫的使用者不能修改資料，但并不是說資料倉庫的資料是永遠不變的。分析的結果隻能反映過去的情況，當業務變化後，挖掘出的模式會失去時效性。是以資料倉庫的資料需要定時更新，以适應決策的需要。

1.3. 資料庫與資料倉庫的差別

資料庫與資料倉庫的差別實際講的是

OLTP

與

OLAP

的差別。

操作型處理，叫聯機事務處理 OLTP（On-Line Transaction Processing，），也可以稱面向交易的處理系統，它是針對具體業務在資料庫聯機的日常操作，通常對少數記錄進行查詢、修改。使用者較為關心操作的響應時間、資料的安全性、完整性和并發支援的使用者數等問題。傳統的資料庫系統作為資料管理的主要手段，主要用于操作型處理。

分析型處理，叫聯機分析處理 OLAP（On-Line Analytical Processing）一般針對某些主題的曆史資料進行分析，支援管理決策。

首先要明白，資料倉庫的出現，并不是要取代資料庫。

資料庫是面向事務的設計，資料倉庫是面向主題設計的。
資料庫一般存儲業務資料，資料倉庫存儲的一般是曆史資料。
資料庫設計是盡量避免備援，一般針對某一業務應用進行設計，比如一張簡單的User表，記錄使用者名、密碼等簡單資料即可，符合業務應用，但是不符合分析。資料倉庫在設計是有意引入備援，依照分析需求，分析次元、分析名額進行設計。
資料庫是為捕獲資料而設計，資料倉庫是為分析資料而設計。

資料倉庫，是在資料庫已經大量存在的情況下，為了進一步挖掘資料資源、為了決策需要而産生的，它決不是所謂的“大型資料庫”。

1.4. 數倉的分層架構

按照資料流入流出的過程，資料倉庫架構可分為三層——源資料、資料倉庫、資料應用。

hive講義資料倉庫-Hive

資料倉庫的資料來源于不同的源資料，并提供多樣的資料應用，資料自下而上流入資料倉庫後向上層開放應用，而資料倉庫隻是中間內建化資料管理的一個平台。

源資料層（ODS） ：此層資料無任何更改，直接沿用外圍系統資料結構和資料，不對外開放；為臨時存儲層，是接口資料的臨時存儲區域，為後一步的資料處理做準備。
資料倉庫層（DW） ：也稱為細節層，DW層的資料應該是一緻的、準确的、幹淨的資料，即對源系統資料進行了清洗（去除了雜質）後的資料。
資料應用層（DA或APP） ：前端應用直接讀取的資料源；根據報表、專題分析需求而計算生成的資料。

資料倉庫從各資料源擷取資料及在資料倉庫内的資料轉換和流動都可以認為是ETL（抽取Extra, 轉化Transfer, 裝載Load）的過程，ETL是資料倉庫的流水線，也可以認為是資料倉庫的血液，它維系着資料倉庫中資料的新陳代謝，而資料倉庫日常的管理和維護工作的大部分精力就是保持ETL的正常和穩定。

為什麼要對資料倉庫分層？

用空間換時間，通過大量的預處理來提升應用系統的使用者體驗（效率），是以資料倉庫會存在大量備援的資料；不分層的話，如果源業務系統的業務規則發生變化将會影響整個資料清洗過程，工作量巨大。

通過資料分層管理可以簡化資料清洗的過程，因為把原來一步的工作分到了多個步驟去完成，相當于把一個複雜的工作拆成了多個簡單的工作，把一個大的黑盒變成了一個白盒，每一層的處理邏輯都相對簡單和容易了解，這樣我們比較容易保證每一個步驟的正确性，當資料發生錯誤的時候，往往我們隻需要局部調整某個步驟即可。

1.5. 數倉的中繼資料管理

中繼資料（Meta Date），主要記錄資料倉庫中模型的定義、各層級間的映射關系、監控資料倉庫的資料狀态及ETL的任務運作狀态。一般會通過中繼資料資料庫（Metadata Repository）來統一地存儲和管理中繼資料，其主要目的是使資料倉庫的設計、部署、操作和管理能達成協同和一緻。

中繼資料是資料倉庫管理系統的重要組成部分，中繼資料管理是企業級資料倉庫中的關鍵元件，貫穿資料倉庫建構的整個過程，直接影響着資料倉庫的建構、使用和維護。

建構資料倉庫的主要步驟之一是ETL。這時中繼資料将發揮重要的作用，它定義了源資料系統到資料倉庫的映射、資料轉換的規則、資料倉庫的邏輯結構、資料更新的規則、資料導入曆史記錄以及裝載周期等相關内容。資料抽取和轉換的專家以及資料倉庫管理者正是通過中繼資料高效地建構資料倉庫。
使用者在使用資料倉庫時，通過中繼資料通路資料，明确資料項的含義以及定制報表。
資料倉庫的規模及其複雜性離不開正确的中繼資料管理，包括增加或移除外部資料源，改變資料清洗方法，控制出錯的查詢以及安排備份等。

hive講義資料倉庫-Hive

中繼資料可分為技術中繼資料和業務中繼資料。技術中繼資料為開發和管理資料倉庫的IT 人員使用，它描述了與資料倉庫開發、管理和維護相關的資料，包括資料源資訊、資料轉換描述、資料倉庫模型、資料清洗與更新規則、資料映射和通路權限等。而業務中繼資料為管理層和業務分析人員服務，從業務角度描述資料，包括商務術語、資料倉庫中有什麼資料、資料的位置和資料的可用性等，幫助業務人員更好地了解資料倉庫中哪些資料是可用的以及如何使用。

由上可見，中繼資料不僅定義了資料倉庫中資料的模式、來源、抽取和轉換規則等，而且是整個資料倉庫系統運作的基礎，中繼資料把資料倉庫系統中各個松散的元件聯系起來，組成了一個有機的整體。

2. Hive 的基本概念

2.1. Hive 簡介

什麼是 Hive

Hive是基于Hadoop的一個資料倉庫工具，可以将結構化的資料檔案映射為一張資料庫表，并提供類SQL查詢功能。

其本質是将SQL轉換為MapReduce的任務進行運算，底層由HDFS來提供資料的存儲，說白了hive可以了解為一個将SQL轉換為MapReduce的任務的工具，甚至更進一步可以說hive就是一個MapReduce的用戶端

為什麼使用 Hive

采用類SQL文法去操作資料，提供快速開發的能力。
避免了去寫MapReduce，減少開發人員的學習成本。
功能擴充很友善。

2.2. Hive 架構

hive講義資料倉庫-Hive

使用者接口：包括CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)為shell指令行；JDBC/ODBC是Hive的JAVA實作，與傳統資料庫JDBC類似；WebGUI是通過浏覽器通路Hive。
中繼資料存儲：通常是存儲在關系資料庫如mysql/derby中。Hive 将中繼資料存儲在資料庫中。Hive 中的中繼資料包括表的名字，表的列和分區及其屬性，表的屬性（是否為外部表等），表的資料所在目錄等。
解釋器、編譯器、優化器、執行器: 完成HQL 查詢語句從詞法分析、文法分析、編譯、優化以及查詢計劃的生成。生成的查詢計劃存儲在HDFS 中，并在随後有MapReduce 調用執行。

2.3. Hive 與 Hadoop 的關系

Hive利用HDFS存儲資料，利用MapReduce查詢分析資料

hive講義資料倉庫-Hive

2.4. Hive與傳統資料庫對比

hive用于海量資料的離線資料分析

hive講義資料倉庫-Hive

總結：hive具有sql資料庫的外表，但應用場景完全不同，hive隻适合用來做批量資料統計分析

2.5. Hive 的安裝

這裡我們選用hive的版本是2.1.1

下載下傳位址為：

http://archive.apache.org/dist/hive/hive-2.1.1/apache-hive-2.1.1-bin.tar.gz

下載下傳之後，将我們的安裝包上傳到第三台機器的/export/softwares目錄下面去

第一步：上傳并解壓安裝包

将我們的hive的安裝包上傳到第三台伺服器的/export/softwares路徑下，然後進行解壓

cd /export/softwares/
tar -zxvf apache-hive-2.1.1-bin.tar.gz -C ../servers/

第二步：安裝mysql

第一步：線上安裝mysql相關的軟體包

yum install mysql mysql-server mysql-devel

第二步：啟動mysql的服務

/etc/init.d/mysqld start

第三步：通過mysql安裝自帶腳本進行設定

/usr/bin/mysql_secure_installation

第四步：進入mysql的用戶端然後進行授權

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

flush privileges;

第三步：修改hive的配置檔案

修改hive-env.sh

cd /export/servers/apache-hive-2.1.1-bin/conf
cp hive-env.sh.template hive-env.sh

HADOOP_HOME=/export/servers/hadoop-2.7.5
export HIVE_CONF_DIR=/export/servers/apache-hive-2.1.1-bin/conf

修改hive-site.xml

cd /export/servers/apache-hive-2.1.1-bin/conf
vim hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl" target="_blank" rel="external nofollow" ?>
<configuration>
<property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>root</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>123456</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://node03:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
  </property>
  <property>
      <name>hive.metastore.schema.verification</name>
      <value>false</value>
  </property>
  <property>
    <name>datanucleus.schema.autoCreateAll</name>
    <value>true</value>
 </property>
 <property>
		<name>hive.server2.thrift.bind.host</name>
		<value>node03</value>
   </property>
</configuration>

第四步：添加mysql的連接配接驅動包到hive的lib目錄下

hive使用mysql作為中繼資料存儲，必然需要連接配接mysql資料庫，是以我們添加一個mysql的連接配接驅動包到hive的安裝目錄下，然後就可以準備啟動hive了

将我們準備好的mysql-connector-java-5.1.38.jar 這個jar包直接上傳到

/export/servers/apache-hive-2.1.1-bin/lib

這個目錄下即可

至此，hive的安裝部署已經完成，接下來我們來看下hive的三種互動方式

第五步：配置hive的環境變量

node03伺服器執行以下指令配置hive的環境變量

sudo vim /etc/profile

export HIVE_HOME=/export/servers/apache-hive-2.1.1-bin
export PATH=:$HIVE_HOME/bin:$PATH

2.6. Hive 的互動方式

第一種互動方式 `bin/hive`

cd /export/servers/apache-hive-2.1.1-bin/
bin/hive

建立一個資料庫

第二種互動方式： `使用sql語句或者sql腳本進行互動`

不進入hive的用戶端直接執行hive的hql語句

cd /export/servers/apache-hive-2.1.1-bin
bin/hive -e "create database if not exists mytest;"

或者我們可以将我們的hql語句寫成一個sql腳本然後執行

cd /export/servers
vim  hive.sql

create database if not exists mytest;
use mytest;
create table stu(id int,name string);

通過hive -f 來執行我們的sql腳本

bin/hive -f /export/servers/hive.sql

3. Hive 的基本操作

###3.1 資料庫操作

####3.1.1 建立資料庫

create database if not exists myhive;
use  myhive;

說明：hive的表存放位置模式是由hive-site.xml當中的一個屬性指定的

<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>

####3.1.2 建立資料庫并指定位置

####3.1.3 設定資料庫鍵值對資訊

資料庫可以有一些描述性的鍵值對資訊，在建立時添加：

檢視資料庫的鍵值對資訊：

修改資料庫的鍵值對資訊：

####3.1.4 檢視資料庫更多詳細資訊

####3.1.5 删除資料庫

删除一個空資料庫，如果資料庫下面有資料表，那麼就會報錯

強制删除資料庫，包含資料庫下面的表一起删除

3.2 資料庫表操作

####3.2.1 建立表的文法:

create [external] table [if not exists] table_name (
col_name data_type [comment '字段描述資訊']
col_name data_type [comment '字段描述資訊'])
[comment '表的描述資訊']
[partitioned by (col_name data_type,...)]
[clustered by (col_name,col_name,...)]
[sorted by (col_name [asc|desc],...) into num_buckets buckets]
[row format row_format]
[storted as ....]
[location '指定表的路徑']

說明：

create table

建立一個指定名字的表。如果相同名字的表已經存在，則抛出異常；使用者可以用 IF NOT EXISTS 選項來忽略這個異常。

external

可以讓使用者建立一個外部表，在建表的同時指定一個指向實際資料的路徑（LOCATION），Hive 建立内部表時，會将資料移動到資料倉庫指向的路徑；若建立外部表，僅記錄資料所在的路徑，不對資料的位置做任何改變。在删除表的時候，内部表的中繼資料和資料會被一起删除，而外部表隻删除中繼資料，不删除資料。

comment

表示注釋,預設不能使用中文

partitioned by

表示使用表分區,一個表可以擁有一個或者多個分區，每一個分區單獨存在一個目錄下 .

clustered by

對于每一個表分檔案， Hive可以進一步組織成桶，也就是說桶是更為細粒度的資料範圍劃分。Hive也是針對某一列進行桶的組織。
sorted by

指定排序字段和排序規則
row format

指定表檔案字段分隔符

storted as指定表檔案的存儲格式, 常用格式:SEQUENCEFILE, TEXTFILE, RCFILE,如果檔案資料是純文字，可以使用 STORED AS TEXTFILE。如果資料需要壓縮，使用 storted as SEQUENCEFILE。
location

指定表檔案的存儲路徑

####3.2.2 内部表的操作

建立表時,如果沒有使用external關鍵字,則該表是内部表（managed table）

Hive建表字段類型

分類	類型	描述	字面量示例
原始類型	BOOLEAN	true/false	TRUE
TINYINT	1位元組的有符号整數, -128~127	1Y
SMALLINT	2個位元組的有符号整數，-32768~32767	1S
INT	4個位元組的帶符号整數	1
BIGINT	8位元組帶符号整數	1L
FLOAT	4位元組單精度浮點數	1.0
DOUBLE	8位元組雙精度浮點數	1.0
DEICIMAL	任意精度的帶符号小數	1.0
STRING	字元串，變長	“a”,’b’
VARCHAR	變長字元串	“a”,’b’
CHAR	固定長度字元串	“a”,’b’
BINARY	位元組數組	無法表示
TIMESTAMP	時間戳，毫秒值精度	122327493795
DATE	日期	‘2016-03-29’
INTERVAL	時間頻率間隔
複雜類型	ARRAY	有序的的同類型的集合	array(1,2)
MAP	key-value,key必須為原始類型，value可以任意類型	map(‘a’,1,’b’,2)
STRUCT	字段集合,類型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
UNION	在有限取值範圍内的一個值	create_union(1,’a’,63)

建表入門:

use myhive;
create table stu(id int,name string);
insert into stu values (1,"zhangsan");  #插入資料
select * from stu;

建立表并指定字段之間的分隔符

建立表并指定表檔案的存放路徑

根據查詢結果建立表

根據已經存在的表結建構立表

查詢表的詳細資訊

. 删除表

####3.2.3 外部表的操作

外部表說明

外部表因為是指定其他的hdfs路徑的資料加載到表當中來，是以hive表會認為自己不完全獨占這份資料，是以删除hive表的時候，資料仍然存放在hdfs當中，不會删掉.

内部表和外部表的使用場景

每天将收集到的網站日志定期流入HDFS文本檔案。在外部表（原始日志表）的基礎上做大量的統計分析，用到的中間表、結果表使用内部表存儲，資料通過SELECT+INSERT進入内部表。

操作案例

分别建立老師與學生表外部表，并向表中加載資料

建立老師表

建立學生表

加載資料

加載資料并覆寫已有資料

從hdfs檔案系統向表中加載資料（需要提前将資料上傳到hdfs檔案系統）

cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put techer.csv /hivedatas/
load data inpath '/hivedatas/techer.csv' into table teacher;

####3.2.4 分區表的操作

在大資料中，最常用的一種思想就是分治，我們可以把大的檔案切割劃分成一個個的小的檔案，這樣每次操作一個小的檔案就會很容易了，同樣的道理，在hive當中也是支援這種思想的，就是我們可以把大的資料，按照每月，或者天進行切分成一個個的小的檔案,存放在不同的檔案夾中.

建立分區表文法

建立一個表帶多個分區

加載資料到分區表中

加載資料到多分區表中

多分區表聯合查詢(使用

union all

)

檢視分區

添加一個分區

删除分區

####3.2.5 分區表綜合練習

需求描述

：

現在有一個檔案score.csv檔案，存放在叢集的這個目錄下/scoredatas/month=201806，這個檔案每天都會生成，存放到對應的日期檔案夾下面去，檔案别人也需要公用，不能移動。需求，建立hive對應的表，并将資料加載到表中，進行資料統計分析，且删除表之後，資料不能删除

資料準備

：

hdfs dfs -mkdir -p /scoredatas/month=201806
hdfs dfs -put score.csv /scoredatas/month=201806/

建立外部分區表，并指定檔案資料存放目錄

進行表的修複

(建立表與資料檔案之間的一個關系映射)

####3.2.6 分桶表操作

分桶，就是将資料按照指定的字段進行劃分到多個檔案當中去,分桶就是MapReduce中的分區.

開啟 Hive 的分桶功能

設定 Reduce 個數

建立分桶表

桶表的資料加載，由于通标的資料加載通過hdfs dfs -put檔案或者通過load data均不好使，隻能通過insert overwrite

建立普通表，并通過insert overwriter的方式将普通表的資料通過查詢的方式加載到桶表當中去

建立普通表

普通表中加載資料

通過insert overwrite給桶表中加載資料

###3.3 修改表結構

重命名:

把表score4修改成score5

增加/修改列資訊:

查詢表結構

添加列

更新列

删除表

1.8. hive表中加載資料

直接向分區表中插入資料

create table score3 like score;

insert into table score3 partition(month ='201807') values ('001','002','100');

通過查詢插入資料

通過load方式加載資料

通過查詢方式加載資料

create table score4 like score;
insert overwrite table score4 partition(month = '201806') select s_id,c_id,s_score from score;

4. Hive 查詢文法

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

order by 會對輸入做全局排序，是以隻有一個reducer，會導緻當輸入規模較大時，需要較長的計算時間。
sort by不是全局排序，其在資料進入reducer前完成排序。是以，如果用sort by進行排序，并且設定mapred.reduce.tasks>1，則sort by隻保證每個reducer的輸出有序，不保證全局有序。
distribute by(字段)根據指定的字段将資料分到不同的reducer，且分發算法是hash散列。
cluster by(字段) 除了具有distribute by的功能外，還會對該字段進行排序.

是以，如果distribute 和sort字段是同一個時，此時，

cluster by = distribute by + sort by

4.2. 查詢文法

全表查詢

選擇特定列

列别名

1）重命名一個列。

2）便于計算。

3）緊跟列名，也可以在列名和别名之間加入關鍵字‘AS’

4.3. 常用函數

求總行數（count）

求分數的最大值（max）

求分數的最小值（min）

求分數的總和（sum）

求分數的平均值（avg）

4.4. LIMIT語句

典型的查詢會傳回多行資料。LIMIT子句用于限制傳回的行數。

4.5. WHERE語句

使用WHERE 子句，将不滿足條件的行過濾掉。
WHERE 子句緊随 FROM 子句。
案例實操

查詢出分數大于60的資料

比較運算符

操作符	支援的資料類型	描述
A=B	基本資料類型	如果A等于B則傳回TRUE，反之傳回FALSE
A<=>B	基本資料類型	如果A和B都為NULL，則傳回TRUE，其他的和等号（=）操作符的結果一緻，如果任一為NULL則結果為NULL
A<>B, A!=B	基本資料類型	A或者B為NULL則傳回NULL；如果A不等于B，則傳回TRUE，反之傳回FALSE
A<B	基本資料類型	A或者B為NULL，則傳回NULL；如果A小于B，則傳回TRUE，反之傳回FALSE
A<=B	基本資料類型	A或者B為NULL，則傳回NULL；如果A小于等于B，則傳回TRUE，反之傳回FALSE
A>B	基本資料類型	A或者B為NULL，則傳回NULL；如果A大于B，則傳回TRUE，反之傳回FALSE
A>=B	基本資料類型	A或者B為NULL，則傳回NULL；如果A大于等于B，則傳回TRUE，反之傳回FALSE
A [NOT] BETWEEN B AND C	基本資料類型	如果A，B或者C任一為NULL，則結果為NULL。如果A的值大于等于B而且小于或等于C，則結果為TRUE，反之為FALSE。如果使用NOT關鍵字則可達到相反的效果。
A IS NULL	所有資料類型	如果A等于NULL，則傳回TRUE，反之傳回FALSE
A IS NOT NULL	所有資料類型	如果A不等于NULL，則傳回TRUE，反之傳回FALSE
IN(數值1, 數值2)	所有資料類型	使用 IN運算顯示清單中的值
A [NOT] LIKE B	STRING 類型	B是一個SQL下的簡單正規表達式，如果A與其比對的話，則傳回TRUE；反之傳回FALSE。B的表達式說明如下：‘x%’表示A必須以字母‘x’開頭，‘%x’表示A必須以字母’x’結尾，而‘%x%’表示A包含有字母’x’,可以位于開頭，結尾或者字元串中間。如果使用NOT關鍵字則可達到相反的效果。
A RLIKE B, A REGEXP B	STRING	類型 B是一個正規表達式，如果A與其比對，則傳回TRUE；反之傳回FALSE。比對使用的是JDK中的正規表達式接口實作的，因為正則也依據其中的規則。例如，正規表達式必須和整個字元串A相比對，而不是隻需與其字元串比對。

查詢分數等于80的所有的資料

查詢分數在80到100的所有資料

查詢成績為空的所有資料

查詢成績是80和90的資料

4.6. LIKE 和 RLIKE

使用LIKE運算選擇類似的值
選擇條件可以包含字元或數字:

% 代表零個或多個字元(任意個字元)。
_ 代表一個字元。

RLIKE子句是Hive中這個功能的一個擴充，其可以通過Java的正規表達式這個更強大的語言來指定比對條件。
案例實操
1. 查找以8開頭的所有成績
1. 查找第二個數值為9的所有成績資料
1. 查找s_id中含1的資料

4.7. 邏輯運算符

操作符	含義
AND	邏輯并
OR	邏輯或
NOT	邏輯否

查詢成績大于80，并且s_id是01的資料

查詢成績大于80，或者s_id 是01的數

查詢s_id 不是 01和02的學生

4.8. 分組

GROUP BY 語句

GROUP BY語句通常會和聚合函數一起使用，按照一個或者多個列隊結果進行分組，然後對每個組執行聚合操作。

案例實操：

計算每個學生的平均分數

計算每個學生最高成績

HAVING 語句

having與where不同點
1. where針對表中的列發揮作用，查詢資料；having針對查詢結果中的列發揮作用，篩選資料。
2. where後面不能寫分組函數，而having後面可以使用分組函數。
3. having隻用于group by分組統計語句。
案例實操：
- 求每個學生的平均分數
- 求每個學生平均分數大于85的人

4.9. JOIN 語句

4.9.1. 等值 JOIN

Hive支援通常的SQL JOIN語句，但是隻支援等值連接配接，不支援非等值連接配接。

案例操作: 查詢分數對應的姓名

4.9.2. 表的别名

好處
- 使用别名可以簡化查詢。
- 使用表名字首可以提高執行效率。
案例實操
- 合并老師與課程表

4.9.3. 内連接配接

内連接配接：隻有進行連接配接的兩個表中都存在與連接配接條件相比對的資料才會被保留下來。

4.9.4. 左外連接配接

左外連接配接：JOIN操作符左邊表中符合WHERE子句的所有記錄将會被傳回。

查詢老師對應的課程

4.9.5. 右外連接配接

右外連接配接：JOIN操作符右邊表中符合WHERE子句的所有記錄将會被傳回。

4.9.6. 多表連接配接

注意：連接配接 n個表，至少需要n-1個連接配接條件。例如：連接配接三個表，至少需要兩個連接配接條件。

多表連接配接查詢，查詢老師對應的課程，以及對應的分數，對應的學生

select * from teacher t
left join course c
on t.t_id = c.t_id
left join score s
on s.c_id = c.c_id
left join student stu
on s.s_id = stu.s_id;

大多數情況下，Hive會對每對JOIN連接配接對象啟動一個MapReduce任務。本例中會首先啟動一個MapReduce job對表techer和表course進行連接配接操作，然後會再啟動一個MapReduce job将第一個MapReduce job的輸出和表score;進行連接配接操作。

4.10. 排序

4.10.1. 全局排序

Order By：全局排序，一個reduce

使用 ORDER BY 子句排序

ASC（ascend）: 升序（預設）

DESC（descend）: 降序
ORDER BY 子句在SELECT語句的結尾。
案例實操
1. 查詢學生的成績，并按照分數降序排列
1. 查詢學生的成績，并按照分數升序排列

4.10.2. 按照别名排序

按照分數的平均值排序

4.10.3. 多個列排序

按照學生id和平均成績進行排序

4.10.4. 每個MapReduce内部排序（Sort By）局部排序

Sort By：每個MapReduce内部進行排序，對全局結果集來說不是排序。

設定reduce個數

檢視設定reduce個數

查詢成績按照成績降序排列

将查詢結果導入到檔案中（按照成績降序排列）

4.10.5. 分區排序（DISTRIBUTE BY）

Distribute By：類似MR中partition，進行分區，結合sort by使用。

注意，Hive要求DISTRIBUTE BY語句要寫在SORT BY語句之前。

對于distribute by進行測試，一定要配置設定多reduce進行處理，否則無法看到distribute by的效果。

案例實操：先按照學生id進行分區，再按照學生成績進行排序。

設定reduce的個數，将我們對應的s_id劃分到對應的reduce當中去

通過distribute by 進行資料的分區

4.10.6. CLUSTER BY

當distribute by和sort by字段相同時，可以使用cluster by方式。

cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序隻能是倒序排序，不能指定排序規則為ASC或者DESC。

以下兩種寫法等價

select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;

5.Hive Shell參數

5.1 Hive指令行

文法結構

說明：

1、 -i 從檔案初始化HQL。

2、

-e從指令行執行指定的HQL

3、

-f 執行HQL腳本

4、 -v 輸出執行的HQL語句到控制台

5、 -p connect to Hive Server on port number

6、 -hiveconf x=y Use this to set hive/hadoop configuration variables. 設定hive運作時候的參數配置

5.2 Hive參數配置方式

開發Hive應用時，不可避免地需要設定Hive的參數。設定Hive的參數可以調優HQL代碼的執行效率，或幫助定位問題。

對于一般參數，有以下三種設定方式：

配置檔案
指令行參數
參數聲明

配置檔案

：Hive的配置檔案包括

使用者自定義配置檔案：$HIVE_CONF_DIR/hive-site.xml
預設配置檔案： $HIVE_CONF_DIR/hive-default.xml

使用者自定義配置會覆寫預設配置。

另外，Hive也會讀入Hadoop的配置，因為Hive是作為Hadoop的用戶端啟動的，Hive的配置會覆寫Hadoop的配置。

配置檔案的設定對本機啟動的所有Hive程序都有效。

指令行參數：

啟動Hive（用戶端或Server方式）時，可以在指令行添加-hiveconf param=value來設定參數，例如：

bin/hive -hiveconf hive.root.logger=INFO,console

這一設定對本次啟動的Session（對于Server方式啟動，則是所有請求的Sessions）有效。

參數聲明

：可以在HQL中使用SET關鍵字設定參數，例如：

這一設定的作用域也是session級的。

上述三種設定方式的優先級依次遞增。即參數聲明覆寫指令行參數，指令行參數覆寫配置檔案設定。注意某些系統級的參數，例如log4j相關的設定，必須用前兩種方式設定，因為那些參數的讀取在Session建立以前已經完成了。

參數聲明 > 指令行參數 > 配置檔案參數（hive）

6. Hive 函數

6.1. 内置函數

内容較多，見《Hive官方文檔》

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

檢視系統自帶的函數
顯示自帶的函數的用法
詳細顯示自帶的函數的用法

4:常用内置函數

#字元串連接配接函數： concat 
  select concat('abc','def’,'gh');
#帶分隔符字元串連接配接函數： concat_ws 
  select concat_ws(',','abc','def','gh');
#cast類型轉換
  select cast(1.5 as int);
#get_json_object(json 解析函數，用來處理json，必須是json格式)
   select get_json_object('{"name":"jack","age":"20"}','$.name');
#URL解析函數
   select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');
#explode：把map集合中每個鍵值對或數組中的每個元素都單獨生成一行的形式

6.2. 自定義函數

####6.2.1 概述:

Hive 自帶了一些函數，比如：max/min等，當Hive提供的内置函數無法滿足你的業務處理需要時，此時就可以考慮使用使用者自定義函數(UDF).
根據使用者自定義函數類别分為以下三種：
1. UDF（User-Defined-Function）
  - 一進一出
2. UDAF（User-Defined Aggregation Function）
  - 聚集函數，多進一出
  - 類似于： count / max / min
3. UDTF（User-Defined Table-Generating Functions）
  - 一進多出
  - 如 lateral view explore()
程式設計步驟：
1. 繼承org.apache.hadoop.hive.ql.UDF
2. 需要實作evaluate函數；evaluate函數支援重載；
注意事項
1. UDF必須要有傳回類型，可以傳回null，但是傳回類型不能為void；
2. UDF中常用Text/LongWritable等類型，不推薦使用java類型；

####6.2.2 UDF 開發執行個體

Step 1 建立 Maven 工程

<dependencies>
    <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>2.7.5</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>2.7.5</version>
    </dependency>
</dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>

Step 2 開發 Java 類內建 UDF

public class MyUDF  extends UDF{
    public Text evaluate(final Text str){
        String tmp_str = str.toString();
        if(str != null && !tmp_str.equals("")){
          String str_ret =   tmp_str.substring(0, 1).toUpperCase() + tmp_str.substring(1);
          return  new Text(str_ret);
        }
        return  new Text("");
    }
}

Step 3 項目打包，并上傳到hive的lib目錄下

[外鍊圖檔轉存失敗,源站可能有防盜鍊機制,建議将圖檔儲存下來直接上傳(img-LFxmMz8o-1596119899547)(http://ppw6n93dt.bkt.clouddn.com/8dda7bfdfab0655e99a3c3b17afc422e.png)]

Step 4 添加jar包

重命名我們的jar包名稱

cd /export/servers/apache-hive-2.7.5-bin/lib
mv original-day_10_hive_udf-1.0-SNAPSHOT.jar my_upper.jar

hive的用戶端添加我們的jar包

Step 5 設定函數與我們的自定義函數關聯

Step 6 使用自定義函數

7.hive的資料壓縮

在實際工作當中，hive當中處理的資料，一般都需要經過壓縮，前期我們在學習hadoop的時候，已經配置過hadoop的壓縮，我們這裡的hive也是一樣的可以使用壓縮來節省我們的MR處理的網絡帶寬

7.1 MR支援的壓縮編碼

壓縮格式	工具	算法	檔案擴充名	是否可切分
DEFAULT	無	DEFAULT	.deflate	否
Gzip	gzip	DEFAULT	.gz	否
bzip2	bzip2	bzip2	.bz2	是
LZO	lzop	LZO	.lzo	否
LZ4	無	LZ4	.lz4	否
Snappy	無	Snappy	.snappy	否

為了支援多種壓縮/解壓縮算法，Hadoop引入了編碼/解碼器，如下表所示

壓縮格式	對應的編碼/解碼器
DEFLATE	org.apache.hadoop.io.compress.DefaultCodec
gzip	org.apache.hadoop.io.compress.GzipCodec
bzip2	org.apache.hadoop.io.compress.BZip2Codec
LZO	com.hadoop.compression.lzo.LzopCodec
LZ4	org.apache.hadoop.io.compress.Lz4Codec
Snappy	org.apache.hadoop.io.compress.SnappyCodec

壓縮性能的比較

壓縮算法	原始檔案大小	壓縮檔案大小	壓縮速度	解壓速度
gzip	8.3GB	1.8GB	17.5MB/s	58MB/s
bzip2	8.3GB	1.1GB	2.4MB/s	9.5MB/s
LZO	8.3GB	2.9GB	49.3MB/s	74.6MB/s

http://google.github.io/snappy/

On a single core of a Core i7 processor in 64-bit mode, Snappy compresses at about

250 MB/sec

or more and decompresses at about

500 MB/se

c or more.

7.2 壓縮配置參數

要在Hadoop中啟用壓縮，可以配置如下參數（mapred-site.xml檔案中）：

參數	預設值	階段	建議
io.compression.codecs （在core-site.xml中配置）	org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec,org.apache.hadoop.io.compress.Lz4Codec	輸入壓縮	Hadoop使用檔案擴充名判斷是否支援某種編解碼器
mapreduce.map.output.compress	false	mapper輸出	這個參數設為true啟用壓縮
mapreduce.map.output.compress.codec	org.apache.hadoop.io.compress.DefaultCodec	mapper輸出	使用LZO、LZ4或snappy編解碼器在此階段壓縮資料
mapreduce.output.fileoutputformat.compress	false	reducer輸出	這個參數設為true啟用壓縮
mapreduce.output.fileoutputformat.compress.codec	org.apache.hadoop.io.compress. DefaultCodec	reducer輸出	使用标準工具或者編解碼器，如gzip和bzip2
mapreduce.output.fileoutputformat.compress.type	RECORD	reducer輸出	SequenceFile輸出使用的壓縮類型：NONE和BLOCK

7.3 開啟Map輸出階段壓縮

開啟map輸出階段壓縮可以減少job中map和Reduce task間資料傳輸量。具體配置如下：

案例實操：

1）開啟hive中間傳輸資料壓縮功能

2）開啟mapreduce中map輸出壓縮功能

3）設定mapreduce中map輸出資料的壓縮方式

4）執行查詢語句

7.4 開啟Reduce輸出階段壓縮

當Hive将輸出寫入到表中時，輸出内容同樣可以進行壓縮。屬性hive.exec.compress.output控制着這個功能。使用者可能需要保持預設設定檔案中的預設值false，這樣預設的輸出就是非壓縮的純文字檔案了。使用者可以通過在查詢語句或執行腳本中設定這個值為true，來開啟輸出結果壓縮功能。

案例實操：

1）開啟hive最終輸出資料壓縮功能

2）開啟mapreduce最終輸出資料壓縮

3）設定mapreduce最終資料輸出壓縮方式

4）設定mapreduce最終資料輸出壓縮為塊壓縮

5）測試一下輸出結果是否是壓縮檔案

8.hive的資料存儲格式

Hive支援的存儲數的格式主要有：TEXTFILE（行式存儲）、SEQUENCEFILE(行式存儲)、ORC（列式存儲）、PARQUET（列式存儲）。

8.1 列式存儲和行式存儲

hive講義資料倉庫-Hive

上圖左邊為邏輯表，右邊第一個為行式存儲，第二個為列式存儲。

行存儲的特點：查詢滿足條件的一整行資料的時候，列存儲則需要去每個聚集的字段找到對應的每個列的值，行存儲隻需要找到其中一個值，其餘的值都在相鄰地方，是以此時行存儲查詢的速度更快。

列存儲的特點：因為每個字段的資料聚集存儲，在查詢隻需要少數幾個字段的時候，能大大減少讀取的資料量；每個字段的資料類型一定是相同的，列式存儲可以針對性的設計更好的設計壓縮算法。

TEXTFILE和SEQUENCEFILE的存儲格式都是基于行存儲的；

ORC和PARQUET是基于列式存儲的。

###8.2 常用資料存儲格式

TEXTFILE格式

預設格式，資料不做壓縮，磁盤開銷大，資料解析開銷大。可結合Gzip、Bzip2使用.

ORC格式

Orc (Optimized Row Columnar)是hive 0.11版裡引入的新的存儲格式。

可以看到每個Orc檔案由1個或多個stripe組成，每個stripe250MB大小，每個Stripe裡有三部分組成，分别是Index Data,Row Data,Stripe Footer：

indexData：某些列的索引資料
rowData :真正的資料存儲
StripFooter：stripe的中繼資料資訊

hive講義資料倉庫-Hive

PARQUET格式

Parquet是面向分析型業務的列式存儲格式，由Twitter和Cloudera合作開發，

Parquet檔案是以二進制方式存儲的，是以是不可以直接讀取的，檔案中包括該檔案的資料和中繼資料，是以Parquet格式檔案是自解析的。

通常情況下，在存儲Parquet資料的時候會按照Block大小設定行組的大小，由于一般情況下每一個Mapper任務處理資料的最小機關是一個Block，這樣可以把每一個行組由一個Mapper任務處理，增大任務執行并行度。Parquet檔案的格式如下圖所示。

hive講義資料倉庫-Hive

##**9. **檔案存儲格式與資料壓縮結合

###9.1 壓縮比和查詢速度對比

1）TextFile

（1）建立表，存儲資料格式為TEXTFILE

create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;

（2）向表中加載資料

（3）檢視表中資料大小

2）ORC

（1）建立表，存儲資料格式為ORC

create table log_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc ;

（2）向表中加載資料

（3）檢視表中資料大小

3）Parquet

（1）建立表，存儲資料格式為parquet

create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET ;

（2）向表中加載資料

（3）檢視表中資料大小

存儲檔案的壓縮比總結：

ORC > Parquet > textFile

4)存儲檔案的查詢速度測試：

1）TextFile

hive (default)> select count(*) from log_text;

Time taken: 21.54 seconds, Fetched: 1 row(s)

2）ORC

hive (default)> select count(*) from log_orc;

Time taken: 20.867 seconds, Fetched: 1 row(s)

3）Parquet

hive (default)> select count(*) from log_parquet;

Time taken: 22.922 seconds, Fetched: 1 row(s)

存儲檔案的查詢速度總結：

ORC > TextFile > Parquet

###9.2 ORC存儲指定壓縮方式

官網：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC

ORC存儲方式的壓縮：

Key	Default	Notes
orc.compress	`ZLIB`	high level compression (one of NONE, ZLIB, SNAPPY)
orc.compress.size	262,144	number of bytes in each compression chunk
orc.stripe.size	67,108,864	number of bytes in each stripe
orc.row.index.stride	10,000	number of rows between index entries (must be >= 1000)
orc.create.index	true	whether to create row indexes
orc.bloom.filter.columns	“”	comma separated list of column names for which bloom filter should be created
orc.bloom.filter.fpp	0.05	false positive probability for bloom filter (must >0.0 and <1.0)

1）建立一個非壓縮的的ORC存儲方式

（1）建表語句

create table log_orc_none(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="NONE");

（2）插入資料

（3）檢視插入後資料

2）建立一個SNAPPY壓縮的ORC存儲方式

（1）建表語句

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");

（2）插入資料

（3）檢視插入後資料

###9.3 存儲方式和壓縮總結：

在實際的項目開發當中，hive表的資料存儲格式一般選擇：orc或parquet。壓縮方式一般選擇snappy。

10.hive調優

10.1 Fetch抓取

Hive中對某些情況的查詢可以不必使用MapReduce計算。例如：SELECT * FROM score;在這種情況下，Hive可以簡單地讀取score對應的存儲目錄下的檔案，然後輸出查詢結果到控制台。通過設定hive.fetch.task.conversion參數,可以控制查詢語句是否走MapReduce.

案例實操：

1）把hive.fetch.task.conversion設定成none，然後執行查詢語句，都會執行mapreduce程式。

set hive.fetch.task.conversion=none;

select * from score;
select s_score from score;
select s_score from score limit 3;

2）把hive.fetch.task.conversion設定成more，然後執行查詢語句，如下查詢方式都不會執行mapreduce程式。

set hive.fetch.task.conversion=more;

select * from score;
select s_score from score;
select s_score from score limit 3;

###10.2 本地模式

大多數的Hadoop Job是需要Hadoop提供的完整的可擴充性來處理大資料集的。不過，有時Hive的輸入資料量是非常小的。在這種情況下，為查詢觸發執行任務時消耗可能會比實際job的執行時間要多的多。對于大多數這種情況，Hive可以通過本地模式在單台機器上處理所有的任務。對于小資料集，執行時間可以明顯被縮短。

使用者可以通過設定hive.exec.mode.local.auto的值為true，來讓Hive在适當的時候自動啟動這個優化。

案例實操：

1）開啟本地模式，并執行查詢語句

set hive.exec.mode.local.auto=true; 
select * from score cluster by s_id;

2）關閉本地模式，并執行查詢語句

set hive.exec.mode.local.auto=false; 
select * from score cluster by s_id;

###10.3 MapJoin

如果不指定MapJoin或者不符合MapJoin的條件，那麼Hive解析器會在Reduce階段完成join,容易發生資料傾斜。可以用MapJoin把小表全部加載到記憶體在map端進行join，避免reducer處理。

1）開啟MapJoin參數設定：

（1）設定自動選擇Mapjoin

（2）大表小表的門檻值設定（預設25M以下認為是小表）：

###10.4 Group By

預設情況下，Map階段同一Key資料分發給一個reduce，當一個key資料過大時就傾斜了。并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端進行部分聚合，最後在Reduce端得出最終結果。

開啟Map端聚合參數設定

（1）是否在Map端進行聚合，預設為True

（2）在Map端進行聚合操作的條目數目

（3）有資料傾斜的時候進行負載均衡（預設是false）

當選項設定為 true，生成的查詢計劃會有兩個MR Job。

第一個MR Job中，Map的輸出結果會随機分布到Reduce中，每個Reduce做部分聚合操作，并輸出結果，這樣處理的結果是相同的Group By Key有可能被分發到不同的Reduce中，進而達到負載均衡的目的；

第二個MR Job再根據預處理的資料結果按照Group By Key分布到Reduce中（這個過程可以保證相同的Group By Key被分布到同一個Reduce中），最後完成最終的聚合操作。

###10.5 Count(distinct)

資料量小的時候無所謂，資料量大的情況下，由于COUNT DISTINCT操作需要用一個Reduce Task來完成，這一個Reduce需要處理的資料量太大，就會導緻整個Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換：

select count(distinct s_id) from score;
select count(s_id) from (select id from score group by s_id) a;

雖然會多用一個Job來完成，但在資料量大的情況下，這個絕對是值得的。

###10.6 笛卡爾積

盡量避免笛卡爾積，即避免join的時候不加on條件，或者無效的on條件，Hive隻能使用1個reducer來完成笛卡爾積。

###10.7 動态分區調整

往hive分區表中插入資料時，hive提供了一個動态分區功能，其可以基于查詢參數的位置去推斷分區的名稱，進而建立分區。使用Hive的動态分區，需要進行相應的配置。

Hive的動态分區是以第一個表的分區規則，來對應第二個表的分區規則，将第一個表的所有分區，全部拷貝到第二個表中來，第二個表在加載資料的時候，不需要指定分區了，直接用第一個表的分區即可

####10.7.1 開啟動态分區參數設定

（1）開啟動态分區功能（預設true，開啟）

（2）設定為非嚴格模式（動态分區的模式，預設strict，表示必須指定至少一個分區為靜态分區，nonstrict模式表示允許所有的分區字段都可以使用動态分區。）

（3）在所有執行MR的節點上，最大一共可以建立多少個動态分區。

（4）在每個執行MR的節點上，最大可以建立多少個動态分區。該參數需要根據實際的資料來設定。

（5）整個MR Job中，最大可以建立多少個HDFS檔案。

在linux系統當中，每個linux使用者最多可以開啟1024個程序，每一個程序最多可以打開2048個檔案，即持有2048個檔案句柄，下面這個值越大，就可以打開檔案句柄越大

（6）當有空分區生成時，是否抛出異常。一般不需要設定。

####10.7.2 案例操作

需求：将ori中的資料按照時間(如：20111231234568)，插入到目标表ori_partitioned的相應分區中。

（1）準備資料原表

create table ori_partitioned(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) 
PARTITIONED BY (p_time bigint) 
row format delimited fields terminated by '\t';

load data local inpath '/export/servers/hivedatas/small_data' into  table ori_partitioned partition (p_time='20111230000010');

load data local inpath '/export/servers/hivedatas/small_data' into  table ori_partitioned partition (p_time='20111230000011');

（2）建立目标分區表

（3）向目标分區表加載資料

如果按照之前介紹的往指定一個分區中Insert資料，那麼這個需求很不容易實作。這時候就需要使用動态分區來實作。

INSERT overwrite TABLE ori_partitioned_target PARTITION (p_time)
SELECT id, time, uid, keyword, url_rank, click_num, click_url, p_time
FROM ori_partitioned;

注意：在SELECT子句的最後幾個字段，必須對應前面**PARTITION (p_time)**中指定的分區字段，包括順序。

(4)檢視分區

10.8 并行執行

Hive會将一個查詢轉化成一個或者多個階段。這樣的階段可以是MapReduce階段、抽樣階段、合并階段、limit階段。或者Hive執行過程中可能需要的其他階段。預設情況下，Hive一次隻會執行一個階段。不過，某個特定的job可能包含衆多的階段，而這些階段可能并非完全互相依賴的，也就是說有些階段是可以并行執行的，這樣可能使得整個job的執行時間縮短。不過，如果有更多的階段可以并行執行，那麼job可能就越快完成。

通過設定參數hive.exec.parallel值為true，就可以開啟并發執行。不過，在共享叢集中，需要注意下，如果job中并行階段增多，那麼叢集使用率就會增加。

當然，得是在系統資源比較空閑的時候才有優勢，否則，沒資源，并行也起不來。

10.9 嚴格模式

Hive提供了一個嚴格模式，可以防止使用者執行那些可能意向不到的不好的影響的查詢。

通過設定屬性hive.mapred.mode值為預設是非嚴格模式nonstrict 。開啟嚴格模式需要修改hive.mapred.mode值為strict，開啟嚴格模式可以禁止3種類型的查詢。

set hive.mapred.mode = strict; #開啟嚴格模式
set hive.mapred.mode = nostrict; #開啟非嚴格模式

1）

對于分區表，在where語句中必須含有分區字段作為過濾條件來限制範圍，否則不允許執行

。換句話說，就是使用者不允許掃描所有分區。進行這個限制的原因是，通常分區表都擁有非常大的資料集，而且資料增加迅速。沒有進行分區限制的查詢可能會消耗令人不可接受的巨大資源來處理這個表。

2）

對于使用了order by語句的查詢，要求必須使用limit語句

。因為order by為了執行排序過程會将所有的結果資料分發到同一個Reducer中進行處理，強制要求使用者增加這個LIMIT語句可以防止Reducer額外執行很長一段時間。

3）

限制笛卡爾積的查詢

。對關系型資料庫非常了解的使用者可能期望在執行JOIN查詢的時候不使用ON語句而是使用where語句，這樣關系資料庫的執行優化器就可以高效地将WHERE語句轉化成那個ON語句。不幸的是，Hive并不會執行這種優化，是以，如果表足夠大，那麼這個查詢就會出現不可控的情況。

10.10 JVM重用

JVM重用是Hadoop調優參數的内容，其對Hive的性能具有非常大的影響，特别是對于很難避免小檔案的場景或task特别多的場景，這類場景大多數執行時間都很短。

Hadoop的預設配置通常是使用派生JVM來執行map和Reduce任務的。這時JVM的啟動過程可能會造成相當大的開銷，尤其是執行的job包含有成百上千task任務的情況。JVM重用可以使得JVM執行個體在同一個job中重新使用N次。N的值可以在Hadoop的mapred-site.xml檔案中進行配置。通常在10-20之間，具體多少需要根據具體業務場景測試得出。

我們也可以在hive當中通過

這個設定來設定我們的jvm重用

這個功能的缺點是，開啟JVM重用将一直占用使用到的task插槽，以便進行重用，直到任務完成後才能釋放。如果某個“不平衡的”job中有某幾個reduce task執行的時間要比其他Reduce task消耗的時間多的多的話，那麼保留的插槽就會一直空閑着卻無法被其他的job使用，直到所有的task都結束了才會釋放。

10.11 推測執行

在分布式叢集環境下，因為程式Bug（包括Hadoop本身的bug），負載不均衡或者資源分布不均等原因，會造成同一個作業的多個任務之間運作速度不一緻，有些任務的運作速度可能明顯慢于其他任務（比如一個作業的某個任務進度隻有50%，而其他所有任務已經運作完畢），則這些任務會拖慢作業的整體執行進度。為了避免這種情況發生，Hadoop采用了推測執行（Speculative Execution）機制

，它根據一定的法則推測出“拖後腿”的任務，并為這樣的任務啟動一個備份任務，讓該任務與原始任務同時處理同一份資料，并最終選用最先成功運作完成任務的計算結果作為最終結果。

設定開啟推測執行參數：

set mapred.map.tasks.speculative.execution=true
set mapred.reduce.tasks.speculative.execution=true
set hive.mapred.reduce.tasks.speculative.execution=true;

關于調優這些推測執行變量，還很難給一個具體的建議。如果使用者對于運作時的偏差非常敏感的話，那麼可以将這些功能關閉掉。如果使用者因為輸入資料量很大而需要執行長時間的map或者Reduce task的話，那麼啟動推測執行造成的浪費是非常巨大大。

hive講義資料倉庫-Hive

資料倉庫-Hive

1. 資料倉庫

1.1. 基本概念

1.2. 主要特征

1.2.1. 面向主題

1.2.2. 內建性

1.2.3. 非易失性

1.2.4. 時變性

1.3. 資料庫與資料倉庫的差別

1.4. 數倉的分層架構

為什麼要對資料倉庫分層？

1.5. 數倉的中繼資料管理

2. Hive 的基本概念

2.1. Hive 簡介

什麼是 Hive

為什麼使用 Hive

2.2. Hive 架構

2.3. Hive 與 Hadoop 的關系

2.4. Hive與傳統資料庫對比

2.5. Hive 的安裝

第一步：上傳并解壓安裝包

第二步：安裝mysql

第三步：修改hive的配置檔案

第四步：添加mysql的連接配接驅動包到hive的lib目錄下

第五步：配置hive的環境變量

2.6. Hive 的互動方式

第一種互動方式 bin/hive

第二種互動方式： 使用sql語句或者sql腳本進行互動

3. Hive 的基本操作

3.2 資料庫表操作

4. Hive 查詢文法

4.1. SELECT

4.2. 查詢文法

4.3. 常用函數

4.4. LIMIT語句

4.5. WHERE語句

4.6. LIKE 和 RLIKE

4.7. 邏輯運算符

4.8. 分組

GROUP BY 語句

HAVING 語句

4.9. JOIN 語句

4.9.1. 等值 JOIN

4.9.2. 表的别名

4.9.3. 内連接配接

4.9.4. 左外連接配接

4.9.5. 右外連接配接

4.9.6. 多表連接配接

4.10. 排序

4.10.1. 全局排序

4.10.2. 按照别名排序

4.10.3. 多個列排序

4.10.4. 每個MapReduce内部排序（Sort By）局部排序

4.10.5. 分區排序（DISTRIBUTE BY）

4.10.6. CLUSTER BY

5.Hive Shell參數

5.1 Hive指令行

5.2 Hive參數配置方式

6. Hive 函數

6.1. 内置函數

6.2. 自定義函數

Step 1 建立 Maven 工程

Step 2 開發 Java 類內建 UDF

Step 3 項目打包，并上傳到hive的lib目錄下

Step 4 添加jar包

Step 5 設定函數與我們的自定義函數關聯

Step 6 使用自定義函數

7.hive的資料壓縮

**7.1 **MR支援的壓縮編碼

7.2 壓縮配置參數

7.3 開啟Map輸出階段壓縮

7.4 開啟Reduce輸出階段壓縮

8.hive的資料存儲格式

8.1 列式存儲和行式存儲

10.hive調優

10.1 Fetch抓取

10.8 并行執行

10.9 嚴格模式

10.10 JVM重用

第一種互動方式 `bin/hive`

第二種互動方式： `使用sql語句或者sql腳本進行互動`

7.1 MR支援的壓縮編碼