1 Hive 基本概念

1.1 什么是 Hive？

Hive：由 Facebook 開源用于解決海量結(jié)構(gòu)化日志的數(shù)據(jù)統(tǒng)計。

Hive 是基于 Hadoop 的一個數(shù)據(jù)倉庫工具，可以將結(jié)構(gòu)化的數(shù)據(jù)文件映射為一張表，并提供類 SQL 查詢功能。

image

• Hive 處理的數(shù)據(jù)存儲在 HDFS
• Hive 分析數(shù)據(jù)底層的實現(xiàn)是 MapReduce
• 執(zhí)行程序運行在 Yarn 上

1.2 Hive 的優(yōu)缺點

優(yōu)點：

• 操作接口采用類 SQL 語法，提供快速開發(fā)的能力
• 避免了去寫 MapReduce，減少開發(fā)人員的學(xué)習(xí)成本
• Hive 的執(zhí)行延遲比較高，因此 Hive 常用于數(shù)據(jù)分析，對實時性要求不高的場合
• Hive 優(yōu)勢在于處理大數(shù)據(jù)，對于處理小數(shù)據(jù)沒有優(yōu)勢，因為 Hive 的執(zhí)行延遲比較高
• Hive 支持用戶自定義函數(shù)，用戶可以根據(jù)自己的需求來實現(xiàn)自己的函數(shù)

缺點：

• Hive 的 HQL 表達(dá)能力有限
  • 迭代式算法無法表達(dá)
    • 數(shù)據(jù)挖掘方面不擅長，由于 MapReduce 數(shù)據(jù)處理流程的限制，效率更高的算法卻無法實現(xiàn)
• Hive的效率比較低
  • Hive 自動生成的 MapReduce 作業(yè)，通常情況下不夠智能化
  • Hive 調(diào)優(yōu)比較困難，粒度較粗

1.3 Hive架構(gòu)原理

image

1、用戶接口：Client

• CLI
• JDBC

2、元數(shù)據(jù)：Metastore

• 包括表名、表所屬的數(shù)據(jù)庫（默認(rèn)是 default）、表的擁有者、列/分區(qū)字段、表的類型（是否是外部表）、表的數(shù)據(jù)所在目錄等
• 默認(rèn)存儲在自帶的 derby 數(shù)據(jù)庫中，推薦使用 MySQL 存儲 Metastore

3、Hadoop

• 使用 HDFS 進(jìn)行存儲，使用 MapReduce 進(jìn)行計算。

4、驅(qū)動器

• 解析器（SQL Parser）：將 SQL 字符串轉(zhuǎn)換成抽象語法樹 AST，對 AST 進(jìn)行語法分析，比如表是否存在、字段是否存在、SQL語義是否有誤
• 編譯器（Physical Plan）：將 AST 編譯生成邏輯執(zhí)行計劃
• 優(yōu)化器（Query Optimizer）：對邏輯執(zhí)行計劃進(jìn)行優(yōu)化
• 執(zhí)行器（Execution）：把邏輯執(zhí)行計劃轉(zhuǎn)換成可以運行的物理計劃，對于 Hive 來說，就是 MR/Spark

image

Hive 通過給用戶提供的一系列交互接口，接收到用戶的指令(SQL)，使用自己的 Driver，結(jié)合元數(shù)據(jù)(MetaStore)，將這些指令翻譯成 MapReduce，提交到 Hadoop 中執(zhí)行，最后，將執(zhí)行返回的結(jié)果輸出到用戶交互接口。

1.4 Hive VS 數(shù)據(jù)庫

• 由于 SQL 被廣泛的應(yīng)用在數(shù)據(jù)庫中，因此，專門針對 Hive 的特性設(shè)計了類 SQL 的查詢語言 HQL
• Hive 中的數(shù)據(jù)存儲到 HDFS 中，而數(shù)據(jù)庫則可以將數(shù)據(jù)保存在塊設(shè)備或者本地文件系統(tǒng)中
• Hive 中不建議對數(shù)據(jù)的改寫，所有的數(shù)據(jù)都是在加載的時候確定好的，數(shù)據(jù)庫支持更新操作
• Hive 的查詢執(zhí)行由 MapReduce 來實現(xiàn)，數(shù)據(jù)庫有自己的執(zhí)行引擎
• Hive 在查詢數(shù)據(jù)的時候，由于沒有索引，需要掃描整個表，因此延遲較高，另外一個導(dǎo)致 Hive 執(zhí)行延遲高的因素是 MapReduce 框架，由于MapReduce 本身具有較高的延遲，因此在利用 MapReduce 執(zhí)行 Hive 查詢時，也會有較高的延遲，相對的，數(shù)據(jù)庫的執(zhí)行延遲較低，當(dāng)然，這個低是有條件的，即數(shù)據(jù)規(guī)模較小，當(dāng)數(shù)據(jù)規(guī)模大到超過數(shù)據(jù)庫的處理能力的時候，Hive 的并行計算顯然能體現(xiàn)出優(yōu)勢
• Hive 的擴展性高，數(shù)據(jù)庫的可擴展性較低
• Hive 建立在集群上并可以利用 MapReduce 進(jìn)行并行計算，因此可以支持很大規(guī)模的數(shù)據(jù)，對應(yīng)的，數(shù)據(jù)庫可以支持的數(shù)據(jù)規(guī)模較小

2 Hive 安裝

2.1 Hive 安裝部署

1、解壓 apache-hive-1.2.1-bin.tar.gz 到 /opt/module/ 目錄下面

2、修改 apache-hive-1.2.1-bin.tar.gz 的名稱為 hive

3、修改 /opt/module/hive/conf 目錄下的 hive-env.sh.template 名稱為 hive-env.sh

4、配置 hive-env.sh

# 配置HADOOP_HOME路徑
export HADOOP_HOME=/opt/module/hadoop-2.7.2
# 配置HIVE_CONF_DIR路徑
export HIVE_CONF_DIR=/opt/module/hive/conf

5、啟動 hdfs、yarn

6、在 HDFS 上創(chuàng)建 /tmp 和 /user/hive/warehouse 兩個目錄并修改他們的同組權(quán)限可寫

[djm@hadoop102 hadoop-2.7.2]$ bin/hadoop fs -mkdir /tmp
[djm@hadoop102 hadoop-2.7.2]$ bin/hadoop fs -mkdir -p /user/hive/warehouse
[djm@hadoop102 hadoop-2.7.2]$ bin/hadoop fs -chmod g+w /tmp
[djm@hadoop102 hadoop-2.7.2]$ bin/hadoop fs -chmod g+w /user/hive/warehouse

7、啟動 Hive

[djm@hadoop102 hive]$ bin/hive

2.2 將本地文件導(dǎo)入到 Hive

1、在 /opt/module/ 目錄下創(chuàng)建 datas

2、在 datas 目錄下創(chuàng)建 student.txt 并添加如下內(nèi)容

1001    zhangshan
1002    lishi
1003    zhaoliu

3、創(chuàng)建 student 表, 并聲明列分隔符

hive> create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

4、加載 /opt/module/datas/student.txt 文件到 student 數(shù)據(jù)表中

hive> load data local inpath '/opt/module/datas/student.txt' into table student;

5、再打開一個客戶端窗口啟動 hive，會產(chǎn)生java.sql.SQLException 異常：

• Metastore 默認(rèn)存儲在自帶的 derby 數(shù)據(jù)庫中，推薦使用 MySQL 存儲 Metastore

6、其他字段解釋

• row format delimited fields terminated by ',' --列分隔符
• collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符（數(shù)據(jù)分割符號）
• map keys terminated by ':' --MAP 中的 key 與 value 的分隔符
• lines terminated by '\n'; --行分隔符

2.3 安裝 MariaDB

1、查看是否現(xiàn)有的包

rpm -qa | grep mariadb

2、如果有，刪之

rpm -e --nodeps 包名

3、安裝 MariaDB

yum -y install mariadb mariadb-server

4、啟動 MariaDB

systemctl start mariadb

5、進(jìn)行 MariaDB 的相關(guān)簡單配置

mysql_secure_installation

首先是設(shè)置密碼，會提示先輸入密碼
Enter current password for root (enter for none):<–初次運行直接回車

設(shè)置密碼
Set root password? [Y/n] <– 是否設(shè)置root用戶密碼，輸入y并回車或直接回車
New password: <– 設(shè)置root用戶的密碼
Re-enter new password: <– 再輸入一次你設(shè)置的密碼

其他配置
Remove anonymous users? [Y/n] <– 是否刪除匿名用戶，回車
Disallow root login remotely? [Y/n] <–是否禁止root遠(yuǎn)程登錄,回車
Remove test database and access to it? [Y/n] <– 是否刪除test數(shù)據(jù)庫，回車
Reload privilege tables now? [Y/n] <– 是否重新加載權(quán)限表，回車

2.4 Hive 元數(shù)據(jù)配置到 MySQL

1、驅(qū)動拷貝：拷貝 /opt/software 目錄下的 mysql-connector-java-5.1.27-bin.jar 到 /opt/module/hive/lib/

2、配置 Metastore 到 MySQL：在 /opt/module/hive/conf 目錄下創(chuàng)建 hive-site.xml 并編輯

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://hadoop102:3306/metastore?createDatabaseIfNotExist=true</value>
        <description>JDBC connect string for a JDBC metastore</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>Driver class name for a JDBC metastore</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
        <description>username to use against metastore database</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>000000</value>
        <description>password to use against metastore database</description>
    </property>
</configuration>

2.4 HiveJDBC 訪問

1、啟動 hiveserver2 服務(wù)

[djm@hadoop102 hive]$ bin/hiveserver2

2、啟動 beeline

[djm@hadoop102 hive]$ bin/beeline
Beeline version 1.2.1 by Apache Hive
beeline>

3、連接 hiveserver2

beeline> !connect jdbc:hive2://hadoop102:10000（回車）
Connecting to jdbc:hive2://hadoop102:10000
Enter username for jdbc:hive2://hadoop102:10000: djm（回車）
Enter password for jdbc:hive2://hadoop102:10000: （直接回車）
Connected to: Apache Hive (version 1.2.1)
Driver: Hive JDBC (version 1.2.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hadoop102:10000>

2.5 Hive 常見屬性配置

2.5.1 Hive 數(shù)據(jù)倉庫位置配置

Default 數(shù)據(jù)倉庫的最原始位置是在 hdfs 上的：/user/hive/warehouse 路徑下

在倉庫目錄下，沒有對默認(rèn)的數(shù)據(jù)庫 default 創(chuàng)建文件夾，如果某張表屬于 default 數(shù)據(jù)庫，直接在數(shù)據(jù)倉庫目錄下創(chuàng)建一個文件夾

修改 default 數(shù)據(jù)倉庫原始位置，在 hive-site.xml 配置，添加如下配置：

<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
</property>

配置同組用戶有執(zhí)行權(quán)限

bin/hdfs dfs -chmod g+w /user/hive/warehouse

2.5.2 查詢后信息顯示配置

在 hive-site.xml 文件中添加如下配置信息，就可以實現(xiàn)顯示當(dāng)前數(shù)據(jù)庫，以及查詢表的頭信息配置

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

2.5.3 Hive 運行日志信息配置

Hive 的 log 默認(rèn)存放在 /tmp/djm/hive.log 目錄下

修改 hive 的 log 存放日志到 /opt/module/hive/logs

• 修改 conf/hive-log4j.properties.template 文件名稱為 hive-log4j.properties

• 在 hive-log4j.properties 文件中修改 log 存放位置

  hive.log.dir=/opt/module/hive/logs
  

2.5.4 參數(shù)配置方式

查看當(dāng)前所有的配置信息

hive>set;

參數(shù)的配置三種方式

1、配置文件方式

默認(rèn)配置文件：hive-default.xml

用戶自定義配置文件：hive-site.xml

注意：用戶自定義配置會覆蓋默認(rèn)配置，另外，Hive 也會讀入 Hadoop 的配置，因為 Hive 是作為 Hadoop 的客戶端啟動的，Hive 的配置會覆蓋 Hadoop 的配置，配置文件的設(shè)定對本機啟動的所有 Hive 進(jìn)程都有效

2、命令行參數(shù)方式

啟動 Hive 時，可以在命令行添加 -hiveconf param=value 來設(shè)定參數(shù)

例如：

[djm@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;

注意：僅對本次 hive 啟動有效

查看參數(shù)設(shè)置：

set mapred.reduce.tasks;

3、參數(shù)聲明方式

可以在 HQL 中使用 SET 關(guān)鍵字設(shè)定參數(shù)

例如：

set mapred.reduce.tasks=100;

注意：僅對本次 hive 啟動有效

上述三種設(shè)定方式的優(yōu)先級依次遞增，即配置文件<命令行參數(shù)<參數(shù)聲明。注意某些系統(tǒng)級的參數(shù)，例如 log4j 相關(guān)的設(shè)定，必須用前兩種方式設(shè)定，因為那些參數(shù)的讀取在會話建立以前已經(jīng)完成了

3 Hive 數(shù)據(jù)類型

3.1 基本數(shù)據(jù)類型

對于 Hive 的 String 類型相當(dāng)于數(shù)據(jù)庫的 varchar 類型，該類型是一個可變的字符串，不過它不能聲明其中最多能存儲多少個字符，理論上它可以存儲 2GB 的字符數(shù)

3.2 集合數(shù)據(jù)類型

3.3 類型轉(zhuǎn)化

Hive 的原子數(shù)據(jù)類型是可以進(jìn)行隱式轉(zhuǎn)換的，類似于 Java 的類型轉(zhuǎn)換，例如某表達(dá)式使用 INT 類型，TINYINT 會自動轉(zhuǎn)換為 INT 類型，但是 Hive 不會進(jìn)行反向轉(zhuǎn)化，例如，某表達(dá)式使用 TINYINT 類型，INT 不會自動轉(zhuǎn)換為 TINYINT 類型，它會返回錯誤，除非使用 CAST 操作

轉(zhuǎn)換規(guī)則：

• 任何整數(shù)類型都可以隱式地轉(zhuǎn)換為一個范圍更廣的范圍
• 所有整數(shù)類型、FLOAT 和 STRING 類型都可以隱式地轉(zhuǎn)換成 DOUBLE
• TINYINT、SMALLINT、INT 都可以轉(zhuǎn)換為 FLOAT
• BOOLEAN 類型不可以轉(zhuǎn)換為任何其它的類型

4 DDL

4.1 創(chuàng)建數(shù)據(jù)庫

格式：

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

4.2 查詢數(shù)據(jù)庫

顯示所有數(shù)據(jù)庫

show databases;

過濾顯示查詢的數(shù)據(jù)庫

show databases like 'db_hive*';

查看數(shù)據(jù)庫信息

desc database db_hive;

查看數(shù)據(jù)庫詳情

desc database extended db_hive;

切換數(shù)據(jù)庫

use db_hive;

4.3 修改數(shù)據(jù)庫

格式：

ALTER DATABASE database_name 
SET DBPROPERTIES (property_name=property_value, ...);

4.4 刪除數(shù)據(jù)庫

格式：

DROP database_name
[IF NOT EXISTS] 
[CASCADE];

4.5 創(chuàng)建表

格式：

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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

字段解釋說明：

• EXTERNAL 表示該表為外部表，在建表的同時可以指定一個指向?qū)嶋H數(shù)據(jù)的路徑，在刪除表的時候，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會被一起刪除，而外部表只刪除元數(shù)據(jù)，不刪除數(shù)據(jù)
• PARTITIONED BY 表示該表為分區(qū)表，可以理解成 MySQL 的索引
• CLUSTERED BY 表示該表啊為分桶表
• SORTED BY不常用，對桶中的一個或多個列另外排序
• ROW FORMAT 行對象的序列與反序列化：
  • 用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe，如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe，在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 確定表的具體的列的數(shù)據(jù)
• STORED AS 指定存儲文件類型：
  • TEXTFILE 文本
  • SEQUENCEFILE 二進(jìn)制序列文件
  • RCFILE 列式存儲格式文件
• LOCATION 指定在 HDFS 上的存儲位置
• AS 后跟查詢語句，根據(jù)查詢結(jié)果創(chuàng)建表
• LIKE 允許用戶復(fù)制現(xiàn)有的表結(jié)構(gòu)，但是不復(fù)制數(shù)據(jù)

4.5.1 管理表

默認(rèn)創(chuàng)建的表都是所謂的管理表，有時也被稱為內(nèi)部表，因為這種表，Hive會控制著數(shù)據(jù)的生命周期，Hive 默認(rèn)情況下會將這些表的數(shù)據(jù)存儲在由配置項 hive.metastore.warehouse.dir 所定義的目錄的子目錄下，當(dāng)我們刪除一個管理表時，Hive 也會刪除這個表中數(shù)據(jù)，管理表不適合和其他工具共享數(shù)據(jù)。

4.5.2 外部表

因為表是外部表，所以 Hive 并非認(rèn)為其完全擁有這份數(shù)據(jù)。刪除該表并不會刪除掉這份數(shù)據(jù)，不過描述表的元數(shù)據(jù)信息會被刪除掉

4.5.3 管理表與外部表的互相轉(zhuǎn)換

查詢表的類型

desc formatted student;

修改內(nèi)部表 student 為外部表

alter table student set tblproperties('EXTERNAL'='TRUE');

查詢表的類型

desc formatted student;

修改外部表 student 為內(nèi)部表

alter table student set tblproperties('EXTERNAL'='FALSE');

查詢表的類型

desc formatted student;

4.6 分區(qū)表

創(chuàng)建分區(qū)表

hive (default)> create table dept_partition(
  deptno int, dname string, loc string
) partitioned by (month string) row format delimited fields terminated by '\t';

加載分區(qū)數(shù)據(jù)到分區(qū)表中

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201707’);

查詢分區(qū)內(nèi)數(shù)據(jù)

# 單分區(qū)查詢
select * from dept_partition where month='201709';
# 多分區(qū)查詢
select * from dept_partition where month='201709'
  union select * from dept_partition where month='201708' 
  union select * from dept_partition where month='201707';

增加分區(qū)

# 增加單分區(qū)
alter table dept_partition add partition(month='201706');
# 增加多分區(qū)
alter table dept_partition add partition(month='201706') partition(month='201705');

刪除分區(qū)

# 刪除單分區(qū)
alter table dept_partition drop partition(month='201706');
# 刪除多分區(qū)
alter table dept_partition drop partition(month='201706') partition(month='201705');

查看分區(qū)表有多少分區(qū)

hive> show partitions dept_partition;

查看分區(qū)表結(jié)構(gòu)

desc formatted dept_partition;

創(chuàng)建二級分區(qū)并導(dǎo)入

# 創(chuàng)建二級分區(qū)
create table dept_partition2(
  deptno int, dname string, loc string
) partitioned by (month string, day string) row format delimited fields terminated by '\t';
# 加載數(shù)據(jù)
load data local inpath '/opt/module/datas/dept.txt' 
into table default.dept_partition2 partition(month='201709', day='13');
# 查詢分區(qū)數(shù)據(jù)
select * from dept_partition2 where month='201709' and day='13';

把數(shù)據(jù)直接上傳到分區(qū)目錄上，怎么讓分區(qū)表和數(shù)據(jù)產(chǎn)生關(guān)聯(lián)？

1、執(zhí)行分區(qū)修復(fù)命令

msck repair table dept_partition2;

2、上傳數(shù)據(jù)后添加分區(qū)

alter table dept_partition2 add partition(month='201709', day='11');

3、創(chuàng)建文件夾后 load 數(shù)據(jù)到分區(qū)

load data local inpath '/opt/module/datas/dept.txt' 
into table dept_partition2 partition(month='201709',day='10');

4.7 修改表

重命名表

格式：

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 則是表示替換表中所有字段

4.8 刪除表

格式：

DROP TABLE table_name;

5 DML

5.1 數(shù)據(jù)導(dǎo)入

5.1.1 Load 方式

格式：

load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

• load data 表示加載數(shù)據(jù)
• local 表示從本地加載數(shù)據(jù)到hive表；否則從HDFS加載數(shù)據(jù)到hive表
• inpath 表示加載數(shù)據(jù)的路徑
• overwrite 表示覆蓋表中已有數(shù)據(jù)，否則表示追加
• into table 表示加載到哪張表
• student 表示具體的表
• partition 表示上傳到指定分區(qū)

5.1.2 Insert 方式

insert overwrite table student1 partition(month = '201708') 
select id, name from student where month = '201709';

5.1.3 As Select 方式

create table if not exists student2 as select id, name from student;

5.1.4 Import 方式

import table student3 partition(month='201709') from '/user/hive/warehouse/export/student';

5.1.5 創(chuàng)建表時通過 Location 指定加載數(shù)據(jù)路徑

create external table if not exists student4(
  id int, name string 
) row format delimited fields terminated by '\t' location '/student;

5.2 數(shù)據(jù)導(dǎo)出

5.2.1 Insert 導(dǎo)出

將查詢結(jié)果導(dǎo)出到本地

insert overwrite local directory '/opt/module/datas/export/student' 
select * from student;

將查詢結(jié)果格式化到本地

insert overwrite local directory '/opt/module/datas/export/student' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

將查詢結(jié)果導(dǎo)出到 HDFS

insert overwrite directory '/user/atguigu/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

5.2.2 Hive Shell 命令導(dǎo)出

bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student.txt;

5.2.3 Export 導(dǎo)出到 HDFS

export table default.student to '/user/hive/warehouse/export/student';

5.3 清除表數(shù)據(jù)

truncate table student;

6 查詢

6.1 基本查詢

全表查詢

select * from emp;

特定列查詢

select empno, ename from emp;

列別名

select empno as id, ename as name from emp;

limit

select * from emp limit 5;

6.2 Where

# 查詢出薪水等于5000的所有員工
select * from emp where sal = 5000;
# 查詢出薪水在500到1000的員工
select * from emp where sal between 500 and 1000;
# 查詢comm為空的所有員工信息
select * from emp where comm is null;
# 查詢工資是1500或5000的員工信息
select * from emp where sal in (1500, 5000);
# 查找以2開頭薪水的員工信息
select * from emp where sal like '2%';
# 查找第二個數(shù)值為2的薪水的員工信息
select * from emp where sal like '_2%';
# 查找薪水中含有2的員工信息
select * from emp where sal rlike '[2]';
# 查詢薪水大于1000，部門是30
select * from emp where sal > 1000 and deptno = 30;
# 查詢薪水大于1000，或者部門是30
select * from emp where sal > 1000 or deptno = 30;
# 查詢除了20部門和30部門以外的員工信息
select * from emp where deptno not in (20, 30);

6.3 分組

GROUP BY 語句通常會和聚合函數(shù)一起使用，按照一個或者多個列隊結(jié)果進(jìn)行分組，然后對每個組執(zhí)行聚合操作

# 計算emp表每個部門的平均工資
select deptno, avg(sal) from emp group by deptno;
# 計算emp每個部門中每個崗位的最高薪水

Having 只用于 GROUP BY 分組統(tǒng)計語句

# 求每個部門的平均薪水大于2000的部門
select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal > 2000;

6.4 Join

等值連接

• 根據(jù)員工表和部門表中的部門編號相等，查詢員工編號、員工名稱和部門名稱

  select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
  

內(nèi)連接

• 只有進(jìn)行連接的兩個表中都存在與連接條件相匹配的數(shù)據(jù)才會被保留下來

  select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
  

左外連接

• JOIN 操作符左邊表中符合 WHERE 子句的所有記錄將會被返回

  select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
  

右外連接

• JOIN操作符右邊表中符合WHERE子句的所有記錄將會被返回

  select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
  

滿外連接

• 將會返回所有表中符合 WHERE 語句條件的所有記錄，如果任一表的指定字段沒有符合條件的值的話，那么就使用 NULL值替代

  select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
  

多表連接

• Hive 會對每對 JOIN 連接對象啟動一個 MapReduce 任務(wù)

  select e.ename, d.dname, l.loc_name from emp e join dept d on d.deptno = e.deptno join location l on d.loc = l.loc;
  

6.5 排序

Order By

• 全局排序，只有一個 Reduce

  # 查詢員工信息按工資升序排列
  select * from emp order by sal;
  # 查詢員工信息按工資降序排列
  select * from emp order by sal desc;
  

Sort By

• 內(nèi)部排序，對于大規(guī)模的數(shù)據(jù)集 Order By 的效率非常低，在很多情況下，并不需要全局排序，此時可以使用 Sort By

• Sort by 為每個 Reduce 產(chǎn)生一個排序文件，每個 Reducer 內(nèi)部進(jìn)行排序，對全局結(jié)果集來說不是排序

  # 按照部門編號降序排序
  select * from emp sort by deptno desc;
  

Distribute By

• 分區(qū)排序，在有些情況下，我們需要控制某個特定行應(yīng)該到哪個 Reducer ，通常是為了進(jìn)行后續(xù)的聚集操作，Distribute By 類似 MR 中 Partition，進(jìn)行分區(qū)，結(jié)合 Sort By 使用

• 對于 Distribute By ，一定要分配多 Reducer 進(jìn)行處理

• Distribute By 的分區(qū)規(guī)則是根據(jù)分區(qū)字段的 Hash 值與 Reducer 的個數(shù)進(jìn)行模除后，余數(shù)相同的分到一個區(qū)

• Hive 要求 Distribute By 語句要寫在 Sort By 語句之前

  # 先按照部門編號分區(qū)，再按照員工編號降序排序
  select * from emp distribute by deptno sort by empno desc;
  

Cluster By

• 當(dāng) Distribute By 和 Sort By 字段相同時，可以使用 Cluster By 方式

• Cluster By 除了具有 Distribute By 的功能外還兼具 Sort By 的功能，但是排序只能是升序排序，不能指定排序規(guī)則

  # 先按照部門編號分區(qū)，再按照員工編號升序排列
  select * from emp cluster by deptno;
  

6.6 分桶及抽樣

分區(qū)提供一個隔離數(shù)據(jù)和優(yōu)化查詢的便利方式，不過，并非所有的數(shù)據(jù)集都可形成合理的分區(qū)，對于一張表或者分區(qū)，Hive 可以進(jìn)一步組織成桶，也就是更為細(xì)粒度的數(shù)據(jù)范圍劃分

分桶是將數(shù)據(jù)集分解成更容易管理的若干部分的另一個技術(shù)

分區(qū)針對的是數(shù)據(jù)的存儲路徑；分桶針對的是數(shù)據(jù)文件

設(shè)置 hive.enforce.bucketing 屬性，使得 Hive 能識別桶

set hive.enforce.bucketing = true;

創(chuàng)建分桶表

create table stu_buck (
  id int, name string
) clustered by(id) into 4 buckets row format delimited fields terminated by '\t';

查看表結(jié)構(gòu)

desc formatted stu_buck;

導(dǎo)入數(shù)據(jù)到分桶表中

insert into table stu_buck select id, name from stu;

分桶規(guī)則：

Hive 的分桶采用對分桶字段的值進(jìn)行哈希，然后除以桶的個數(shù)求余的方式?jīng)Q定該條記錄存放在哪個桶當(dāng)中

分桶抽樣查詢

• 對于非常大的數(shù)據(jù)集，有時用戶需要使用的是一個具有代表性的查詢結(jié)果而不是全部結(jié)果，Hive 可以通過對表進(jìn)行抽樣來滿足這個需求

  語法：TABLESAMPLE(BUCKET x OUT OF y)
  y必須是table總bucket數(shù)的倍數(shù)或者因子，y決定抽樣的比例，桶的個數(shù)除以y為抽樣的份數(shù)
  x必須小于y，決定從哪個桶開始，抽取第x、x+y、x+y+y、...份數(shù)據(jù)
  select * from stu_buck tablesample(bucket 1 out of 4 on id);
  

7 函數(shù)

7.1 空字段賦值

函數(shù)說明：

NVL(col, str)：給值為 NULL 的數(shù)據(jù)賦值

# 如果員工的comm為NULL，則用-1代替
select comm, nvl(comm, -1) from emp;

7.2 CASE WHEN

準(zhǔn)備數(shù)據(jù)：

創(chuàng)建表并導(dǎo)入數(shù)據(jù)：

create table emp_sex(
  name string, dept_id string, sex string
) row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;

求各部門男女各有多少人

select 
  dept_id, 
  sum(case sex when '男' then 1 else 0 end) male, 
  sum(case sex when '女' then 1 else 0 end) female 
from emp_sex group by dept_id;

7.3 行轉(zhuǎn)列

相關(guān)函數(shù)說明：

CONCAT(string A/col, string B/col…)：返回輸入字符串連接后的結(jié)果

CONCAT_WS(separator, str1, str2,...)：將每個字符串用指定的字符分開

COLLECT_SET(col)：將某字段的值進(jìn)行去重匯總，產(chǎn)生 array 類型字段

準(zhǔn)備數(shù)據(jù)：

創(chuàng)建表并導(dǎo)入數(shù)據(jù)：

create table person_info(
  name string, 
  constellation string, 
  blood_type string
) row format delimited fields terminated by "\t";
load data local inpath "/opt/module/datas/constellation.txt" into table person_info;

把星座和血型一樣的人歸類到一起

select 
    t.base, concat_ws('|', collect_set(t.name)) name 
from 
    (select name, concat(constellation, ",", blood_type) base from person_info) t 
group by 
    t.base;

7.4 列轉(zhuǎn)行

函數(shù)說明：

EXPLODE(col)：將 array 或者 map 結(jié)構(gòu)拆分成多行

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解釋：用于和 split, explode 等UDTF一起使用，它能夠?qū)⒁涣袛?shù)據(jù)拆成多行數(shù)據(jù)，在此基礎(chǔ)上可以對拆分后的數(shù)據(jù)進(jìn)行聚合

準(zhǔn)備數(shù)據(jù)：

創(chuàng)建表并導(dǎo)入數(shù)據(jù)：

create table movie_info(
    movie string, 
    category array<string>
) row format delimited fields terminated by "\t" collection items terminated by ",";
load data local inpath "/opt/module/datas/movie.txt" into table movie_info;

將電影分類中的數(shù)組數(shù)據(jù)展開

select
    movie,
    item
from
    movie_info lateral view explode(category) t as item;

7.5 開窗

相關(guān)函數(shù)說明：

OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變而變化

CURRENT ROW：當(dāng)前行

n PRECEDING：往前 n 行數(shù)據(jù)

n FOLLOWING：往后 n 行數(shù)據(jù)

UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING 表示到后面的終點

LAG(col,n,default_val)：往前第 n 行數(shù)據(jù)

LEAD(col,n, default_val)：往后第 n 行數(shù)據(jù)

NTILE(n)：把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中，各個組有編號，編號從 1 開始，對于每一行，NTILE 返回此行所屬的組的編號

創(chuàng)建表并導(dǎo)入數(shù)據(jù)

create table business(
  name string, 
  orderdate string,
  cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/datas/business.txt" into table business;

按需求查詢數(shù)據(jù)

# 查詢在2017年4月份購買過的顧客及總?cè)藬?shù)
select name, count(*) over() from business where substring(orderdate, 1, 7) = '2017-04';
# 查詢顧客的購買明細(xì)及月購買總額
select name, orderdate, sum(cost) over(partition by month(orderdate)) from business;
# 上述的場景, 將每個顧客的cost按照日期進(jìn)行累加
select
    name,
    orderdate,
    cost,
    sum(cost) over(partition by name
order by
    orderdate rows between unbounded preceding and current row)
from
    business;
# 查詢每個顧客上次的購買時間
select
    name,
    orderdate,
    cost,
    lag(orderdate, 1) over(partition by name)
from
    business;
# 查詢前20%時間的訂單信息
select
    *
from
    (
    select
        name,
        orderdate,
        ntile(5) over(
        order by orderdate) sorted
    from
        business) t
where
    sorted = 1;

7.6 Rank

函數(shù)說明：

RANK() 排序相同時會重復(fù)，總數(shù)不會變

DENSE_RANK() 排序相同時會重復(fù)，總數(shù)會減少

ROW_NUMBER() 會根據(jù)順序計算

數(shù)據(jù)準(zhǔn)備：

創(chuàng)建表并導(dǎo)入數(shù)據(jù)：

create table score(
  name string,
  subject string, 
  score int
) row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/score.txt' into table score;

計算每門學(xué)科成績排名

select
    name,
    subject,
    score,
    rank() over (partition by subject
order by
    desc)
from
    score;

計算沒門學(xué)科前三名

select
    *
from
    (
    select
        *,
        rank() over (partition by subject
    order by
        score desc) as rk
    from
        score) t
where
    t.rk <= 3;

7.7 自定義函數(shù)

根據(jù)用戶自定義函數(shù)類別分為以下三種：

• UDF（User-Defined-Function）一進(jìn)一出
• UDAF（User-Defined Aggregation Function）聚集函數(shù)，多進(jìn)一出
• UDTF（User-Defined Table-Generating Functions）一進(jìn)多出

步驟：

1、導(dǎo)入依賴

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

2、創(chuàng)建一個類

package com.djm.hive;
import org.apache.hadoop.hive.ql.exec.UDF;

public class Lower extends UDF {

    public String evaluate (final String s) {
        
        if (s == null) {
            return null;
        }
        
        return s.toLowerCase();
    }
}

3、打成 jar 包上傳到服務(wù)器 /opt/module/jars/ 目錄

4、將 jar 包添加到 hive 的 classpath

add jar /opt/module/datas/udf.jar;

5、創(chuàng)建臨時函數(shù)與 Class 關(guān)聯(lián)

create temporary function mylower as "com.djm.hive.Lower";

6、使用自定義函數(shù)

select ename, mylower(ename) lowername from emp;

8 壓縮和存儲

8.1 開啟 Map 輸出階段壓縮

開啟 map 輸出階段壓縮可以減少 job 中 map 和 Reduce task 間數(shù)據(jù)傳輸量

# 開啟hive中間傳輸數(shù)據(jù)壓縮功能
set hive.exec.compress.intermediate=true;
# 開啟mapreduce中map輸出壓縮功能
set mapreduce.map.output.compress=true;
# 設(shè)置mapreduce中map輸出數(shù)據(jù)的壓縮方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

8.2 開啟 Reduce 輸出階段壓縮

當(dāng) Hive 將輸出寫入到表中時，輸出內(nèi)容同樣可以進(jìn)行壓縮

# 開啟hive最終輸出數(shù)據(jù)壓縮功能
set hive.exec.compress.output=true;
# 開啟mapreduce最終輸出數(shù)據(jù)壓縮
set mapreduce.output.fileoutputformat.compress=true;
# 設(shè)置mapreduce最終數(shù)據(jù)輸出壓縮方式
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
# 設(shè)置mapreduce最終數(shù)據(jù)輸出壓縮為塊壓縮
set mapreduce.output.fileoutputformat.compress.type=BLOCK;

8.3 文件存儲格式

8.3.1 列式存儲和行式存儲

image

Hive 支持的存儲數(shù)據(jù)的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET

行存儲的特點：

查詢滿足條件的一整行數(shù)據(jù)的時候，列存儲則需要去每個聚集的字段找到對應(yīng)的每個列的值，行存儲只需要找到其中一個值，其余的值都在相鄰地方，所以此時行存儲查詢的速度更快。

列存儲的特點：

因為每個字段的數(shù)據(jù)聚集存儲，在查詢只需要少數(shù)幾個字段的時候，能大大減少讀取的數(shù)據(jù)量；每個字段的數(shù)據(jù)類型一定是相同的，列式存儲可以針對性的設(shè)計更好的設(shè)計壓縮算法。

8.3.2 TextFile 格式

默認(rèn)格式，數(shù)據(jù)不做壓縮，磁盤開銷大，數(shù)據(jù)解析開銷大，可結(jié)合 Gzip、Bzip2 使用，但使用 Gzip 這種方式，hive 不會對數(shù)據(jù)進(jìn)行切分，從而無法對數(shù)據(jù)進(jìn)行并行操作

8.3.3 Orc 格式

image

Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存儲格式

如圖所示可以看到每個 Orc 文件由 1 個或多個 Stripe 組成，每個 Stripe 一般為 HDFS 的塊大小，每一個 Stripe 包含多條記錄，這些記錄按照列進(jìn)行獨立存儲，對應(yīng)到 Parquet 中的 row group的概念，每個 Stripe 里有三部分組成，分別是 Index Data，Row Data，Stripe Footer：

• Index Data：一個輕量級的 index，默認(rèn)是每隔 1W 行做一個索引，這里做的索引應(yīng)該只是記錄某行的各字段在 Row
  Data 中的 offset

• Row Data：存的是具體的數(shù)據(jù)，先取部分行，然后對這些行按列進(jìn)行存儲。對每個列進(jìn)行了編碼，分成多個 Stream 來存儲

• Stripe Footer：存的是各個 Stream 的類型，長度等信息

每個文件有一個 File Footer，這里面存的是每個 Stripe 的行數(shù)，每個 Column 的數(shù)據(jù)類型信息等；每個文件的尾部是一個PostScript，這里面記錄了整個文件的壓縮類型以及FileFooter的長度信息等，讀取文件時，會 seek 到文件尾部讀PostScript，從里面解析到 File Footer 長度，再讀 FileFooter，從里面解析到各個 Stripe 信息，再讀各個 Stripe，即從后往前讀

8.3.4 Parquet 格式

image

如圖所示一個文件中可以存儲多個行組，文件的首位都是該文件的 Magic Code，用于校驗它是否是一個 Parquet 文件，F(xiàn)ooter length 記錄了文件元數(shù)據(jù)的大小，通過該值和文件長度可以計算出元數(shù)據(jù)的偏移量，文件的元數(shù)據(jù)中包括每一個行組的元數(shù)據(jù)信息和該文件存儲數(shù)據(jù)的 Schema 信息，除了文件中每一個行組的元數(shù)據(jù)，每一頁的開始都會存儲該頁的元數(shù)據(jù)，在Parquet 中，有三種類型的頁：數(shù)據(jù)頁、字典頁和索引頁，數(shù)據(jù)頁用于存儲當(dāng)前行組中該列的值，字典頁存儲該列值的編碼字典，每一個列塊中最多包含一個字典頁，索引頁用來存儲當(dāng)前行組下該列的索引，目前 Parquet 中還不支持索引頁。

Parquet 文件是以二進(jìn)制方式存儲的，所以是不可以直接讀取的，文件中包括該文件的數(shù)據(jù)和元數(shù)據(jù)，因此 Parquet 格式文件是自解析的。

• Row Group：每一個行組包含一定的行數(shù)，在一個 HDFS 文件中至少存儲一個行組，類似于 Orc 的 Stripe 的概念

• Column Chunk：在一個行組中每一列保存在一個列塊中，行組中的所有列連續(xù)的存儲在這個行組文件中，一個列塊中的值都是相同類型的，不同的列塊可能使用不同的算法進(jìn)行壓縮

• Page：每一個列塊劃分為多個頁，一個頁是最小的編碼的單位，在同一個列塊的不同頁可能使用不同的編碼方式

通常情況下，在存儲 ParquetBlockMapperBlock 每一個行組由一個任務(wù)處理，增大任務(wù)執(zhí)行并行度

8.4 存儲和壓縮結(jié)合

查看是否支持 Snappy

hadoop checknative

將編譯好的支持 Snappy 壓縮的 Hadoop 解壓

tar -zxvf hadoop-2.7.2.tar.gz

拷貝 /opt/software/hadoop-2.7.2/lib/native 到 /opt/module/hadoop-2.7.2/lib/native

cp ../native/* /opt/module/hadoop-2.7.2/lib/native/

集群分發(fā)

xsync /opt/module/hadoop-2.7.2/lib/native/

再次查看是否支持 Snapy