Hive筆記整理（一）

[TOC]

Hive筆記整理（一）

Hive

Hive由facebook貢獻給Apache，是一款建立在Hadoop之上的數據倉庫的基礎框架。

數據倉庫

特點——關于存放在數據倉庫中的數據的說明：
    是能夠為企業的各個級別的決策提供數據支撐的數據
其實說白了，就是一個存放數據的倉庫

數據庫和數據倉庫之間的區別

現代數據倉庫，是構建在數據庫之上的，使用數據庫作為載體存放數據。
            數據倉庫著重強調的是存放的歷史數據，數據庫著重強調的是存放在線的數據。
            數據倉庫著重強調的是OLAP的操作，數據庫著重強調的是OLTP的操作
                OLAP：Online Analysis Processing 聯機分析處理--->對數據進行分析查詢select、load
                OLTP：Online Transcation Processing 聯機事務處理--->對數據進行事務性操作update delete
            數據倉庫操作的都是歷史數據，數據庫操作的幾乎都是在線交易數據

ETL---是用來構建我們一個數據倉庫的概念

E(Extract 提取) 獲取數據的過程，就稱之為提取，采集
T(Transform 轉化) 對進入倉庫的數據進行分類、清洗
L(Load 加載) 數據進入倉庫的過程就是Load

BI（Business Intelligence）

Hive是一款SQL的解析引擎，能夠將HQL轉移成為MR在hadoop計算hdfs上面的數據。

HIve的概述

存儲結構

Hive的數據存儲基于Hadoop HDFS
Hive沒有專門的數據存儲格式
存儲結構主要包括：數據庫、文件、表、視圖、索引
Hive默認可以直接加載文本文件（TextFile），還支持SequenceFile、RCFile 、ORCFile、Parquet
創建表時，指定Hive數據的列分隔符與行分隔符，Hive即可解析數據

系統架構

用戶接口：包括 CLI，JDBC/ODBC，WebUI
元數據存儲：通常是存儲在關系數據庫如 mysql, derby等等中
Driver：解釋器、編譯器、優化器、執行器
Hadoop：用 HDFS 進行存儲，利用 MapReduce 進行計算

Hive的安裝

三個前提

JDK
HADOOP
MySQL
也就是確保上面三步都安裝完成后再安裝Hive

第一步：安裝MySQL(離線)

操作目錄：/home/uplooking/soft --->安裝包所在目錄
1°、查詢linux中已有的mysql依賴包
    [uplooking@uplooking01 ~]$ rpm -qa | grep mysql mysql-libs-5.1.71-1.el6.x86_64

2°、刪除linux中已有的mysql依賴包
    [uplooking@uplooking01 ~]$ sudo rpm -e --nodeps `rpm -qa | grep mysql`

3°、安裝服務端和客戶端
    [uplooking@uplooking01 ~]$ sudo rpm -ivh soft/MySQL-server-5.5.45-1.linux2.6.x86_64.rpm
    [uplooking@uplooking01 ~]$ sudo rpm -ivh soft/MySQL-client-5.5.45-1.linux2.6.x86_64.rpm

4°、啟動mysql server服務
    [uplooking@uplooking01 ~]$ sudo service mysql start(注意：離線安裝后mysql的服務名稱為mysql，在線安裝后的服務名稱為msyqld)

5°、加入到開機啟動項
    [uplooking@uplooking01 ~]$ sudo chkconfig mysql on

6°、進行用戶名密碼設置
    [uplooking@uplooking01 ~]$ sudo /usr/bin/mysql_secure_installation

7°、對遠程可訪問的機器進行授權
    uplooking@uplooking01 ~]$ mysql -huplooking01 -uroot -puplooking
    ERROR 1130 (HY000): Host 'uplooking01' is not allowed to connect to this MySQL server
    在mysql服務器登錄：mysql -uroot -puplooking
    執行以下語句：
    mysql> grant all privileges on *.* to 'root'@'%' identified by 'uplooking';
    mysql> flush privileges;

第二步：安裝Hive

1°、解壓Hive文件：
    進入$HIVE_HOME/conf/修改文件
    cp  hive-env.sh.template  hive-env.sh
    cp  hive-default.xml.template  hive-site.xml

2°、修改$HIVE_HOME/bin的hive-env.sh，增加以下三行
    export JAVA_HOME=/opt/jdk
    export HADOOP_HOME=/home/uplooking/app/hadoop
    export HIVE_HOME=/home/uplooking/app/hive

3°、修改$HIVE_HOME/conf/hive-site.xml
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://uplooking01:3306/hive?createDatabaseIfNotExist=true</value>
    </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>uplooking</value>
    </property>
    <property>
        <name>hive.querylog.location</name>
        <value>/home/uplooking/app/hive/tmp</value>
    </property>
    <property>
        <name>hive.exec.local.scratchdir</name>
        <value>/home/uplooking/app/hive/tmp</value>
    </property>
    <property>
        <name>hive.downloaded.resources.dir</name>
        <value>/home/uplooking/app/hive/tmp</value>
    </property>

4°、拷貝mysql驅動jar包到$HIVE_HOME/lib下面
    [uplooking@uplooking01 hive]$ cp ~/soft/mysql-connector-java-5.1.39.jar lib/

5°、初始化hive元數據倉庫
    該執行目錄$HIVE_HOME/bin
    bin]$ ./schematool -initSchema -dbType mysql -userName root -passWord uplooking

6°、啟動hive
    ./hive

Hive的訪問

有本地運行模式和集群運行模式

本地模式需要打開開關：set hive.exec.mode.local.auto=true
默認是集群模式
本地模式在對sql進行調試，測試的時候經常使用
<property>
    <name>hive.exec.mode.local.auto</name>
    <value>false</value>
    <description>Let Hive determine whether to run in local mode automatically</description>
</property>
<property>
    <name>hive.exec.mode.local.auto.inputbytes.max</name>
    <value>134217728</value>//128M
    <description>When hive.exec.mode.local.auto is true, input bytes should less than this for local mode.</description>
</property>
<property>
    <name>hive.exec.mode.local.auto.input.files.max</name>
    <value>4</value>
    <description>When hive.exec.mode.local.auto is true, the number of tasks should less than this for local mode.</description>
</property>

有CLI、WebGUI、JDBC的三種訪問方式

CLI是最常用，也就是命令行模式

WebGUI需要自己通過hive源碼制作一個war，部署到hive里面，才能夠使用其提供的一個web界面來訪問hive，進行相關操作

JDBC就是進行sql編程，如果hive使用的是MR的計算引擎，則其運行非常之慢，不能作為交互式查詢

hive目前支持三種計算引擎:mr、spark、tez，默認是mr，spark在hive-2.0以后的版本才支持
<property>
    <name>hive.execution.engine</name>
    <value>mr</value>
</property>

Hive基本操作

數據庫操作

創建數據庫：
    create database mydb1;
使用數據庫：
    use mydb1;
顯示當前正在使用的數據庫：
    set hive.cli.print.current.db=true;
顯示數據庫列表：
    show databases;
刪除：
    drop database mydb1;

表操作

表的數據類型，除了string和復合數據類型(array, map, struct)之外，幾乎和mysql一致。

DDL

DDL即數據庫模式定義語言DDL(Data Definition Language)，用于描述數據庫中要存儲的現實世界實體的語言，其實說白了就是數據庫中關于表操作的語句。

表的創建
    create table 表名(
        字段名 字段類型 注解comment, ... , 
        字段名 字段類型 注解comment
    ) comment "";
    eg.
    create table t1(
        id int,
        name string comment "this is t1's column name"
    ) comment "TABLE t1";

增加一列:
    alter table t1 add columns (age int) ;

刪除一列？不支持刪除一列
    alter table t1 drop column age;

替換，曲線完成刪除一列的內容
    alter table t1 replace columns(online string);使用當前列替換掉原有的所有列

工作中，經常使用腳本的方式來對編寫，并執行hql：

hive客戶端：hive> source /opt/data/hive/hive-t1.hql;
linux終端一：/opt/hive/bin/hive -f hive-t1-1.hql
linux終端二：/opt/hive/bin/hive -e "set hive.exec.mode.local.auto=true;select * from test;"
    對上述linux終端的執行方式，我們還可以添加相關參數選項
    /opt/hive/bin/hive -f hive-t1-1.hql --hiveconf hive.exec.mode.local.auto=true
    /opt/hive/bin/hive -e "select * from test;" --hiveconf hive.exec.mode.local.auto=true
這樣做，可以非常方便將hive的執行語句，組成成為shell腳本，在linux中調度相關shell計算hive中的數據
這兩個-e和-f不能互換，

eg。
/opt/hive/bin/hive -e "use mydb1; select * from test where line like '%you%';" --hiveconf hive.cli.print.header=true
/opt/hive/bin/hive -f hive-t1-1.hql --hiveconf hive.cli.print.header=true

數據操作

向hive表中導入數據的方式

hive cli：
hive> load data local inpath '/opt/data/hive/hive-t1-1.txt' into table t1;
Linux終端：
$ hdfs dfs -put /opt/data/hive/hive-t1-1.txt /user/hive/warehouse/t1/

我們在導入數據的時候，發現數據沒有解析成功，那是因為自定義的數據，行列有相關的分隔符，并沒有告知當前表如何解析，或者說自定義數據的解析方式和hive表的默認的解析方式不一致。

hive表默認的解析方式----行列的分隔符

默認的行分隔符\n
默認的列分隔符\001 在鍵盤上如何輸入呢ctrl+v ctrl+a

數據進入數據庫表中的模式

讀模式
    將數據加載到表中的時候，對數據的合法性不進行校驗，只有在操作表的時候，才對數據合法性進行校驗，不合法的數據顯示為NULL(比如某一列的數據類型為日期類型，如果load的某條數據是該列不是日期類型，則該條數據的這一列不合法，導入hive之后就會顯示為NULL)
    適合大數據的加載，比如hive
寫模型
    在數據加載到表中的時候，需要對數據的合法性進行校驗，加載到數據庫中的數據，都是合法的數據。
    適合事務性數據庫加載數據，常見的mysql、oracle等都是采用這種模式

自定義分隔符

create table t2 (
    id int,
    name string,
    birthday date,
    online boolean
) row format delimited ---->開啟使用自定義分隔符的標識
fields terminated by '\t'   ---->對每一列分隔符的定義
lines terminated by '\n';   ---->對每一行分隔符的定義，當然可以省略不寫，默認和linux保持一致，同時注意，這兩個順序不能顛倒

Hive表的復合數據類型

array ---->java中的array

create table t3_arr(
    id int,
    name string,
    hobby array<string>
)
row format delimited
fields terminated by '\t';

array的默認分割是\002，在shell中如何敲出來ctrl+v ctrl+b
默認不能滿足需求，需要自定義分隔符

create table t3_arr_1(
    id int,
    name string,
    hobby array<string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','; 

在導入數據時，數據的格式應該如下：
1   香飄葉子    IT,Computer

array的引用，使用arrayName[index],索引從0開始

map---->java中的map

每個人都有學習(語文、數學、體育)成績
create table t4_map(
    id int,
    name string,
    score map<string, float> comment "this is score"
) row format delimited 
fields terminated by '\t'
collection items terminated by ','
map keys terminated by '=';

根據上面的定義，導入的數據格式應該如下：
1   香飄葉子    Chinese=102,Math=121,English=124

map里面的默認的key和value之間的分隔符:\003,在shell里面通過ctrl+v ctrl+c

map具體值的調用格式,列名["屬性"],比如score["chinese"]

struct---->java中的object

id  name    address(province:string, city:string, zip:int)
1   小陳  bj,chaoyang,100002
2   老王  hb,shijiazhuang,052260
3   小何  hn,huaiyang,466000
4   小馬  hlj,harbin,10000

create table t5_struct (
    id int,
    name string,
    address struct<province:string, city:string, zip:int>
) row format delimited 
fields terminated by '\t'
collection items terminated by ',';

調用的格式：列名.屬性，比如address.province

復合數據類型案例

有一張員工表：

id          int
name        string
subordinate array<int>
salary      float
tax         map<string, float>
home_info   struct<province:string, city:string, zip:int>

創建表：

create table t7_emp (
    id          int,
    name        string,
    subordinate array<int>,
    salary      float,
    tax         map<string, float>,
    home_info   struct<province:string, city:string, zip:int>           
);

查詢員工的稅后工資，查詢家鄉為河北的人員：

select id, name, salary * (1 - tax["gs"] - tax["gjj"] - tax["sb"]) sh_salary from t7_emp where home_info.province = "河北";