Hive筆記整理（二）

[TOC]

Hive筆記整理（二）

Hive中表的分類

managed_table—受控表、管理表、內部表

表中的數據的生命周期/存在與否，受到了表結構的影響，當表結構被刪除的，表中的數據隨之一并被刪除。
默認創建的表就是這種表。

可以在cli中通過desc extended tableName來查看表的詳細信息，當然也可以在MySQL中hive的元數據信息表TBLS中查看。

external_table—外部表

表中的數據的生命周期/存在與否，不受到了表結構的影響，當表結構被刪除的，表中對應數據依然存在。
這相當于只是表對相應數據的引用。

創建外部表：

create external table t6_external(
    id int
);

增加數據：
alter table t6_external set location "/input/hive/hive-t6.txt";

還可以在創建外部表的時候就可以指定相應數據
create external table t6_external_1(
    id int
) location "/input/hive/hive-t6.txt";
上述hql報錯：
MetaException(message:hdfs://ns1/input/hive/hive-t6.txt is not a directory or unable to create one
意思是說在創建表的時候指定的數據，不期望為一個具體文件，而是一個目錄

create external table t6_external_1(
    id int
) location "/input/hive/";

當使用外部表時，是不允許刪除操作的，但是可以添加數據，并且這樣做也會影響到hdfs中引用的文本數據。

內部表和外部表的簡單用途區別：

當考慮到數據的安全性的時候，或者數據被多部門協調使用的，一般用到外部表。
當考慮到hive和其它框架(比如hbase)進行協調集成的時候，一般用到外部表。

可以對內部表和外部表進行互相轉換：

外--->內部
    alter table t6_external set tblproperties("EXTERNAL"="FALSE");
內部表---->外部表
    alter table t2 set tblproperties("EXTERNAL"="TRUE");

持久表和臨時表

以上的表都是持久表，表的存在和會話沒有任何關系。

臨時表：

在一次會話session中創建的臨時存在的表，當會話斷開的時候，該表所有數據(包括元數據)都會消失，表數據是臨時存儲在內存中，(實際上，創建臨時表后，在hdfs中會創建/tmp/hive目錄來保存臨時文件，但只要退出會話，數據就會馬上刪除)
在元數據庫中沒有顯示。
這種臨時表通常就做臨時的數據存儲或交換
臨時表的特點
    不能是分區表
創建臨時表非常簡單，和外部表一樣，將external關鍵字替換成temporary就可以了

功能表

分區表

假如up_web_log表的結構如下：

user/hive/warehouse/up_web_log/
            web_log_2017-03-09.log
            web_log_2017-03-10.log
            web_log_2017-03-11.log
            web_log_2017-03-12.log
            ....
            web_log_2018-03-12.log

對該表的結構解釋如下：

該表存放的是web日志，在hive中，一個表就是hdfs中的一個目錄，web日志的保存統計是按天進行的，所以每天結束后
都會將日志數據加載到hive中，所以可以看到up_web_log目錄下有多個文件，但是對于hive來說，這些日志數據都是
屬于up_web_log這個表的，顯然，隨著時間的推移，這張表的數據會越來越多。

該表存在的問題：

原先的是一張大表，這張表下面存放有若干數據，要想查看其中某一天的數據，
只能首先在表中定義一個日期字段(比如：dt)，然后再在查詢的時候添加過濾字段where dt="2017-03-12"
如此才能求出相應結果，但是有個問題，這種方式需要加載該表下面所有的文件中的數據，造成在內存中加載了
大量的不相關的數據，造成了我們hql運行效率低下。

那么如何對該表進行優化呢？

要想對這個問題進行優化，我們可以依據hive表的特性，其實在管理的是一個文件夾，也就是說，
通過表能夠定位到一個hdfs上面的目錄，我們就可以在該表/目錄的基礎之上再來創建一/多級子目錄，
來完成對該大表的一個劃/拆分，我們通過某種特征標識，比如子文件夾名稱datadate=2017-03-09...
以后再來查詢其中一天的數據的時候，只需要定位到該子文件夾，即可類似加載一張表數據一樣，加載
該子文件夾下面的所有的數據。這樣就不會再去全量加載該大表下面所有的數據，只加載了其中的一部分，
減少了內存數據量，提高了hql運行效率。

我們把這種技術稱之為，表的分區，這種表稱之為分區表。把這個子文件夾稱之為分區表的分區。

分區表的組成說明如下：

分區有兩部分組成，分區字段和具體的分區值組成，中間使用“=”連接，分區字段在整個表中的地位
就相當于一個字段，要想查詢某一分區下面的數據，如下操作 where datadate="2017-03-09"
hdfs中關于該表的存儲結構為：
user/hive/warehouse/up_web_log/
            /datadate=2017-03-09
                web_log_2017-03-09.log
            /datadate=2017-03-10    
                web_log_2017-03-10.log
            /datadate=2017-03-11
                web_log_2017-03-11.log
            /datadate=2017-03-12  
                web_log_2017-03-12.log
                ....
                web_log_2018-03-12.log

創建一張分區表：

create table t7_partition (
    id int
) partitioned by (dt date comment "date partition field");

load data local inpath '/opt/data/hive/hive-t6.txt' into table t7_partition;
FAILED: SemanticException [Error 10062]: Need to specify partition columns because the destination table is partitioned
不能直接向分區表加載數據，必須在加載數據之前明確加載到哪一個分區中，也就是子文件夾中。

分區表的DDL：

創建一個分區：
    alter table t7_partition add partition(dt="2017-03-10");
查看分區列表：
    show partitions t7_partition;
刪除一個分區：
    alter table t7_partition drop partition(dt="2017-03-10");

增加數據：

向指定分區中增加數據：
    load data local inpath '/opt/data/hive/hive-t6.txt' into table t7_partition partition (dt="2017-03-10");
這種方式，會自動創建分區

有多個分區字段的情況：

統計學校，每年，每個學科的招生，就業的情況/每年就業情況
create table t7_partition_1 (
    id int
) partitioned by (year int, school string); 

添加數據：
    load data local inpath '/opt/data/hive/hive-t6.txt' into table t7_partition_1 partition(year=2015, school='python');

桶表

分區表存在的問題：

因為分區表還有可能造成某些分區數據非常大，某些則非常小，造成查詢不均勻，這不是我們所預期，
就需要使用一種技術，對這些表進行相對均勻的打散，把這種技術稱之為分桶，分桶之后的表稱之為桶表。

創建一張分桶表：

create table t8_bucket(
    id int
) clustered by(id) into 3 buckets;

向分桶表增加數據：

只能從表的表進行轉換，不能使用上面的load這種方式（不會對數據進行拆分）
insert into t8_bucket select * from t7_partition_1 where year=2016 and school="mysql";
FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different 't8_bucket': 
Table insclause-0 has 1 columns, but query has 3 columns.
我們的桶表中只有一個字段，但是分區表中有3個字段，所以在使用insert into 的方式導入數據的時候，
一定要注意前后字段個數必須保持一致。
insert into t8_bucket select id from t7_partition_1 where year=2016 and school="mysql";

增加數據后，查看表中的數據：
> select * from t8_bucket;
OK
6
3
4
1
5
2
Time taken: 0.08 seconds, Fetched: 6 row(s)
可以看到，數據的順序跟原來不同，那是因為數據分成了3份，使用的分桶算法為哈希算法，如下：
6%3 = 0, 3%3 = 0，放在第1個桶
4%3 = 1, 2%3 = 1，放在第2個桶
5%3 = 2, 2%3 = 2，放在第3個桶

查看hdfs中表t8_bucket的結構：
hive (mydb1)> dfs -ls /user/hive/warehouse/mydb1.db/t8_bucket;
Found 3 items
-rwxr-xr-x   3 uplooking supergroup          4 2018-03-09 23:00 /user/hive/warehouse/mydb1.db/t8_bucket/000000_0
-rwxr-xr-x   3 uplooking supergroup          4 2018-03-09 23:00 /user/hive/warehouse/mydb1.db/t8_bucket/000001_0
-rwxr-xr-x   3 uplooking supergroup          4 2018-03-09 23:00 /user/hive/warehouse/mydb1.db/t8_bucket/000002_0
可以看到，數據被分別保存到t8_bucket的3個不同的子目錄中。

注意：操作分桶表的時候，本地模式不起作用。

數據的加載和導出

[]==>可選，<> ==>必須

加載

load

load data [local] inpath 'path'  [overwrite] into table [partition_psc];
local：
    有==>從linux本地加載數據
    無==>從hdfs加載數據，相當于執行mv操作(無指的是沒有local參數時，而不是本地中沒有這個文件)
overwrite
    有==>覆蓋掉表中原來的數據
    無==>在原來的基礎上追加新的數據

從其他表加載

insert <overwrite|into> [table(當前面參數為overwrite時必須加table)] t_des select [...] from t_src [...];
    overwrite
        有==>覆蓋掉表中原來的數據
        無==>在原來的基礎上追加新的數據   
==>會轉化成為MR執行

需要注意的地方：t_des中列要和select [...] from t_src這里面的[...]一一對應起來。
當選擇的參數為overwrite時，后面必須要加table，如：
insert overwrite table test select * from t8_bucket;

創建表的時候加載

create table t_des as select [...] from t_src [...];

這樣會創建一張表，表結構為select [...] from t_src中的[...]
eg.create temporary table tmp as select distinct(id) from t8_bucket;

動態分區的加載

快速復制表結構：

create table t_d_partition like t_partition_1;

hive (default)> show partitions t_partition_1;
OK
partition
year=2015/class=bigdata
year=2015/class=linux
year=2016/class=bigdata
year=2016/class=linux

要將2016的數據都到入到t_d_partition的相關的分區中：

insert into table t_d_partition partition(class, year=2016) select id, name, class from t_partition_1 where year=2016;

要將t_partition_1中所有數據都到入到t_d_partition的相關的分區中：

insert overwrite table t_d_partition partition(year, class) select id, name, year, class from t_partition_1;

(我操作時出現的提示：
            FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set                      shive.exec.dynamic.partition.mode=nonstrict
            )

其它問題：

從hdfs上面刪除的數據，并沒有刪除表結構，我們show partitions t_d_partition;是從metastore中查詢出來的內容，如果你是手動刪除的hdfs上面數據，它的元數據信息依然在。

insert into t10_p_1 partition(year=2016, class) select * from t_partition_1;
FAILED: SemanticException [Error 10094]: Line 1:30 Dynamic partition cannot be the parent of a static partition 'professional'
動態分區不能成為靜態分區的父目錄
需要將hive.exec.dynamic.partition.mode設置為nonstrict
<property>
    <name>hive.exec.max.dynamic.partitions</name>
    <value>1000</value>
    <description>Maximum number of dynamic partitions allowed to be created in total.</description>
</property>

import導入hdfs上的數據：

import table stu from '/data/stu';

目前測試時會出現下面的錯誤：
hive (mydb1)> import table test from '/input/hive/';
FAILED: SemanticException [Error 10027]: Invalid path
hive (mydb1)> import table test from 'hdfs://input/hive/';
FAILED: SemanticException [Error 10324]: Import Semantic Analyzer Error
hive (mydb1)> import table test from 'hdfs://ns1/input/hive/';
FAILED: SemanticException [Error 10027]: Invalid path

導出

1.
hadoop fs -cp src_uri dest_uri
(hdfs dfs -cp src_uri dest_uri)

2.
hive> export table tblName to 'hdfs_uri';
導出到的hdfs目錄必須是一個空目錄，如果不存在時，則會自動創建該目錄。
這種方式同時會將元數據信息導出。

3.
insert overwrite [local] directory 'linux_fs_path' select ...from... where ...; 
如果不加local，則數據會導出到hdfs，否則會導出到linux文件系統
不管哪一種方式，如果目錄不存在，則會自動創建，如果存在，則會覆蓋。