Hive數(shù)據(jù)倉庫如何使用

Hive是一個數(shù)據(jù)倉庫基礎工具在Hadoop中用來處理結構化數(shù)據(jù)。它架構在Hadoop之上，總歸為大數(shù)據(jù)，并使得查詢和分析方便。并提供簡單的sql查詢功能，可以將sql語句轉換為MapReduce任務進行運行。

Hive表類型測試

內(nèi)部表

數(shù)據(jù)準備，先在HDFS上準備文本文件，逗號分割，并上傳到/test目錄，然后在Hive里創(chuàng)建表，表名和文件名要相同。

$ cat /tmp/table_test.csv
1,user1,1000
2,user2,2000
3,user3,3000
4,user4,4000
5,user5,5000

Hive創(chuàng)建表

hive> CREATE TABLE table_test (
 id int,
 name string,
 value INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

前半部分跟我們使用SQL語法差不多，后面的設置表示我們以’,’為分隔符導入數(shù)據(jù)。

Hive加載HDFS數(shù)據(jù)

$ hive -e 'load data local inpath '/tmp/table_test.csv' into table db_test.table_test'Loading data to table db_test.table_test
OK
Time taken: 0.148 seconds

同一個文件可以多次加載（追加數(shù)據(jù)），同時會在HDFS數(shù)據(jù)目錄下多生成一個文件。另外這里加載數(shù)據(jù)local關鍵字表示我們從本地文件加載，如果不加local表示從HDFS中加載數(shù)據(jù)。

Hive查看數(shù)據(jù)

hive> select * from table_test;
OK
1       user1   1000
2       user2   2000
3       user3   3000
4       user4   4000
5       user5   5000
Time taken: 0.058 seconds, Fetched: 5 row(s)

你也可以使用select id from table_test，但是注意在Hive中除了select * from table之外可以使用全表掃描之外，其余任何查詢都需要走MapRedure。

查看HDFS數(shù)據(jù)文件

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /user/hive/warehouse/db_test.db/table_test/
Found 1 items
-rwxrwxrwx   2 root supergroup         65 2017-06-15 22:27 /user/hive/warehouse/db_test.db/table_test/table_test.csv

注意文件權限屬主為root，這是因為我是在root用戶下進入hive的，一般在Hadoop用戶下進入hive命令行進行創(chuàng)建表。

從HDFS加載數(shù)據(jù)到Hive,先上傳數(shù)據(jù)到HDFS集群中

[hadoop@hadoop-nn ~]$ hdfs dfs -mkdir /test[hadoop@hadoop-nn ~]$ hdfs dfs -put /tmp/table_test.csv /test/table_test.csv

創(chuàng)建表

[hadoop@hadoop-nn ~]$ hive
hive> CREATE TABLE hdfs_table (
 id int,
 name string,
 value INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

加載數(shù)據(jù)

hive> LOAD DATA INPATH '/test/table_test.csv' OVERWRITE INTO TABLE db_test.hdfs_table;
Loading data to table db_test.hdfs_table
OK
Time taken: 0.343 seconds
hive> select * from db_test.hdfs_table;
OK
1       user1   1000
2       user2   2000
3       user3   3000
4       user4   4000
5       user5   5000
Time taken: 0.757 seconds, Fetched: 5 row(s)

注意，如果從HDFS加載數(shù)據(jù)到Hive后，原有的HDFS的數(shù)據(jù)文件就不會存在了。

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /test/table_test.csv
ls: `/test/table_test.csv': No such file or directory

查看HDFS數(shù)據(jù)文件

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /user/hive/warehouse/db_test.db/hdfs_table/
Found 1 items
-rwxrwxrwx   2 hadoop supergroup         65 2017-06-15 22:54 /user/hive/warehouse/db_test.db/hdfs_table/table_test.csv

再次上傳一個文件到對應表的目錄（/user/hive/warehouse/db_test.db/hdfs_table）下

[hadoop@hadoop-nn ~]$ cat /tmp/table_test.csv
6,user6,6000

[hadoop@hadoop-nn ~]$ hdfs dfs -put /tmp/table_test.csv /user/hive/warehouse/db_test.db/hdfs_table/table_test_20170616.csv

再次查看Hive表

hive> select * from db_test.hdfs_table;
OK
1       user1   1000
2       user2   2000
3       user3   3000
4       user4   4000
5       user5   5000
6       user6   6000
Time taken: 0.053 seconds, Fetched: 6 row(s)

可以看到，我們追加的一個表信息也顯示出來了。

分區(qū)表

創(chuàng)建分區(qū)表時，需要給定一個分區(qū)字段，這個分區(qū)字段可以是已經(jīng)存在的，也可以是不存在（如果不存在創(chuàng)建表時會自動添加）。Hive分區(qū)概念跟MySQL分區(qū)差不多。下面創(chuàng)建一個以月為分區(qū)的分區(qū)表。

CREATE TABLE par_table (
 id int,
 name string,
 value INT
) partitioned by (day int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

查看表信息

hive> desc par_table;
OK
id                      int                                        
name                    string                                      
value                   int                                        
day                     int                                        
               # Partition Information          # col_name              data_type               comment                           
day                     int                                        
Time taken: 0.023 seconds, Fetched: 9 row(s)

加載數(shù)據(jù)到Hive分區(qū)表中，需要指定對應的分區(qū)表進行數(shù)據(jù)加載

hive> LOAD DATA LOCAL INPATH '/tmp/table_test.csv' OVERWRITE INTO TABLE db_test.par_table PARTITION (day='22');
Loading data to table db_test.par_table partition (day=22)
OK
Time taken: 0.267 seconds

hive> LOAD DATA LOCAL INPATH '/tmp/table_test.csv' OVERWRITE INTO TABLE db_test.par_table PARTITION (day='23');
Loading data to table db_test.par_table partition (day=23)
OK
Time taken: 0.216 seconds

查看HDFS數(shù)據(jù)文件展示樣式

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /user/hive/warehouse/db_test.db/par_table/
Found 1 items
drwxrwxrwx   - hadoop supergroup          0 2017-06-16 01:12 /user/hive/warehouse/db_test.db/par_table/day=22
drwxrwxrwx   - hadoop supergroup          0 2017-06-16 01:12 /user/hive/warehouse/db_test.db/par_table/day=23

可以看到多了對應的分區(qū)目錄了。

查詢數(shù)據(jù)，查詢時有點不太一樣，如果給定一個where條件指定分區(qū)字段（也就是根據(jù)查詢字段來進行分區(qū)），這樣就只會查詢這個分區(qū)的內(nèi)容，不需要加載所有表。如果查詢字段不是分區(qū)字段，那么就需要掃描所有的分區(qū)了。如下兩個示例：

hive> select * from db_test.par_table;
OK
6       user6   6000    22
6       user6   6000    23
Time taken: 0.054 seconds, Fetched: 2 row(s)

hive> select * from db_test.par_table where day=22;
OK
6       user6   6000    22
Time taken: 0.068 seconds, Fetched: 1 row(s)

外部表

Hive支持外部表，外部表跟內(nèi)部表和分區(qū)表不同。只需要在HDFS中有了對應的文件，然后在Hive就可以創(chuàng)建一個表并指定對應的目錄就可以直接查數(shù)據(jù)了，而不需要執(zhí)行數(shù)據(jù)加載任務。下面來測試看看：

先在HDFS中創(chuàng)建目錄和上傳文件：

[hadoop@hadoop-nn ~]$ hdfs dfs -mkdir -p /hive/external
[hadoop@hadoop-nn ~]$ hdfs dfs -put /tmp/table_test.csv /hive/external/ext_table.csv

然后在Hive中直接創(chuàng)建表：

CREATE EXTERNAL TABLE ext_table (
 id int,
 name string,
 value INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/hive/external';

此時，直接查詢此表，不需要加載數(shù)據(jù)了

hive> select * from ext_table;
OK
6       user6   6000
Time taken: 0.042 seconds, Fetched: 1 row(s)

Hive還支持桶表，這里就不說了，很少用，有興趣自行查看資料。

最后來一個MapReduce處理Hive的過程

hive> select count(*) from table_test;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = hadoop_20170616021047_9c0dc1bf-383f-49ad-83e2-e2e5dfdcb20c
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
 set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
 set hive.exec.reducers.max=
In order to set a constant number of reducers:
 set mapreduce.job.reduces=
Starting Job = job_1497424827481_0004, Tracking URL = http://master:8088/proxy/application_1497424827481_0004/
Kill Command = /usr/local/hadoop/bin/hadoop job  -kill job_1497424827481_0004
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2017-06-16 02:10:52,914 Stage-1 map = 0%,  reduce = 0%
2017-06-16 02:10:57,062 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.11 sec
2017-06-16 02:11:02,204 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.53 sec
MapReduce Total cumulative CPU time: 2 seconds 530 msec
Ended Job = job_1497424827481_0004
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.53 sec   HDFS Read: 7980 HDFS Write: 102 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 530 msec
OK
10
Time taken: 15.254 seconds, Fetched: 1 row(s)

可以好好看一下處理過程，由于是測試環(huán)境所以MP時間很久。

視圖

另外Hive也支持視圖，使用非常簡單，如下配置：

hive> create view view_test as select * from table_test;
OK
Time taken: 0.054 seconds

hive> select * from view_test;
OK
d1      user1   1000
d1      user2   2000
d1      user3   3000
d2      user4   4000
d2      user5   5000
Time taken: 0.057 seconds, Fetched: 5 row(s)

Hive元數(shù)據(jù)信息

然后我們來查看一下Hive元數(shù)據(jù)表信息，在MySQL的hive庫下的DBS表中存儲Hive創(chuàng)建的庫信息：

mysql> select * from DBS;
+-------+-----------------------+---------------------------------------------------+---------+------------+------------+
| DB_ID | DESC                  | DB_LOCATION_URI                                   | NAME    | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+---------------------------------------------------+---------+------------+------------+
|     1 | Default Hive database | hdfs://master:8020/user/hive/warehouse            | default | public     | ROLE       |
|     6 | NULL                  | hdfs://master:8020/user/hive/warehouse/db_test.db | db_test | hadoop     | USER       |
+-------+-----------------------+---------------------------------------------------+---------+------------+------------+
2 rows in set (0.00 sec)

DB_ID：庫ID，具有唯一性。

DESC：庫描述信息。

DB_LOCATION_URI：庫在HDFS的URI地址。

NAME：庫名稱。

OWNER_NAME：庫的所有者，用什么系統(tǒng)用戶登錄Hive創(chuàng)建的，其所有者就是誰，一般要在Hadoop用戶下登錄Hive。

OWNER_TYPE：庫的所有者類型。

在hive庫下的TBLS表中存儲我們創(chuàng)建的表的元數(shù)據(jù)信息：
mysql> select * from TBLS;
+--------+-------------+-------+------------------+--------+-----------+-------+------------+----------------+--------------------+--------------------+
| TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER  | RETENTION | SD_ID | TBL_NAME   | TBL_TYPE       | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT |
+--------+-------------+-------+------------------+--------+-----------+-------+------------+----------------+--------------------+--------------------+
|     11 |  1497579800 |     6 |                0 | root   |         0 |    11 | table_test | MANAGED_TABLE  | NULL               | NULL               |
|     16 |  1497581548 |     6 |                0 | hadoop |         0 |    16 | hdfs_table | MANAGED_TABLE  | NULL               | NULL               |
|     26 |  1497584489 |     6 |                0 | hadoop |         0 |    26 | par_table  | MANAGED_TABLE  | NULL               | NULL               |
|     28 |  1497591914 |     6 |                0 | hadoop |         0 |    31 | ext_table  | EXTERNAL_TABLE | NULL               | NULL               |
+--------+-------------+-------+------------------+--------+-----------+-------+------------+----------------+--------------------+--------------------+
4 rows in set (0.00 sec)

解釋幾個重要參數(shù)：

TBL_ID：表ID，具有唯一性。

CREATE_TIME：表創(chuàng)建時間。

DB_ID：所屬庫的ID。

LAST_ACCESS_TIME：最后一次訪問時間。

OWNER：表的所有者，用什么系統(tǒng)用戶登錄Hive創(chuàng)建的，其所有者就是誰，一般要在Hadoop用戶下登錄Hive。

TBL_NAME：表名稱。

TBL_TYPE：表類型，MANAGED_TABLE表示受托管的表（如內(nèi)部表、分區(qū)表、桶表），EXTERNAL_TABLE表示外部表，兩個有個很大的區(qū)別就是受托管的表，當你執(zhí)行DROP TABLE動作時，會把Hive元數(shù)據(jù)信息連同HDFS數(shù)據(jù)也一同刪除。而外部表執(zhí)行DROP TABLE時不會刪除HDFS的數(shù)據(jù)，只是把元數(shù)據(jù)信息刪除了。