Hive資料庫及表的基本操作

小鴨子_嘎嘎發表於2017-01-03
Hive資料庫

實驗目的

1、瞭解Hive的基本資料型別

2、瞭解Hive DDL、DML的基本操作

3、瞭解Hive資料匯入匯出的基本用法

實驗環境

1、Linux Ubuntu 14.04

2、hadoop-2.6.0-cdh5.4.5

3、hadoop-2.6.0-eclipse-cdh5.4.5.jar

4、mysql

5、hive-1.1.0-cdh5.4.5

實驗內容

在/data/mydata/solog檔案中，儲存有某搜尋引擎網站，搜尋日誌資料。根據要求完成實驗。

實驗步驟

一、資料倉儲的操作

1、啟動hive命令列模式，並在hive中建立一個資料倉儲，名為mydb

view plain copy
  1. hive  
view plain copy
  1. create database mydb;  

上述命令可以建立了一個mydb資料倉儲。但是這條語句可以更進一步的優化，我們可以加上if not exists

view plain copy
  1. create database if not exists mydb;  

加入if not exists的意思是如果沒有mydb庫就建立，如果已有就不再建立。

2、檢視資料倉儲mydb的描述資訊和檔案目錄位置路徑資訊

view plain copy
  1. describe database mydb;  

3、刪除名為mydb的資料倉儲

view plain copy
  1. drop database if exists mydb;  
二、關於表的操作

Hive的資料表分為兩種，內部表和外部表。

內部表：Hive 建立並通過 load data inpath 進資料庫的表，這種表可以理解為資料和表結構都儲存在一起的資料表。當通過 drop table table_name 刪除後設資料中表結構的同時，表中的資料也同樣會從 HDFS 中被刪除。

外部表：在表結構建立以前，資料已經存在，通過建立表結構，將資料對映到表的結構中。當進行 drop table table_name 操作的時候，Hive 僅僅刪除後設資料的表結構，而不刪除資料，所以，相比內部表，外部表可以更放心大膽地使用。

（簡單來說，如果刪除內部表會將資料一併刪除，易丟失資料，危險！而刪除外部表，只會將表結構刪除，資料並沒有刪，生產中常使用外部表）

下面詳細介紹對錶操作的命令及使用方法

1、再次建立名為mydb的資料倉儲。檢視mydb資料庫倉庫中，都有哪些表。

view plain copy
  1. create database mydb;  
view plain copy
  1. use mydb;  
  2. show tables;  

2、建立一個名為inlog的內部表，用於儲存/data/mydata/solog檔案中的資料。

先來介紹一下solog這個文字檔案的資料結構。solog中用於儲存我們的搜尋引擎產生的日誌。

主要包括欄位為時間(dt)，ip (ip)， 螢幕寬(screenx)，螢幕高(screeny)， 搜尋詞(word)，上一個搜尋詞(preword)

view plain copy
  1. 時間             ip             螢幕寬 螢幕高   搜尋詞  上一個搜尋詞  
  2. 20161220155840  192.168.1.179   1366    768     HTML  
  3. 20161220155843  192.168.1.179   1366    768     Sqoop   HTML  
  4. 20161220155911  192.168.1.179   1366    768     SparkR  Sqoop  
  5. 20161220155914  192.168.1.155   1600    900     hadoop  
  6. 20161220155928  192.168.1.155   1600    900     sqoop   hahahhaha  
  7. ... ...  

在資料庫倉庫mydb中，建立名為inlog的內部表

view plain copy
  1. use mydb;  
view plain copy
  1. create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
  2. row format delimited fields terminated by '\t';  

建立完畢，再使用show tables檢視一下建立的表的資訊；

view plain copy
  1. show tables;  

建立表與建立庫一樣，名稱不能重複，解決方法是加入if not exists

3、建立一個外部表，命名為outlog，同樣用於儲存solog檔案中的資料。

view plain copy
  1. use mydb;  
  2. create external table outlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
  3. row format delimited fields terminated by '\t';  

建立完畢，再使用show tables檢視一下建立的表的資訊；

view plain copy
  1. show tables;  

建立外部表同樣可以加入if not exists，進行判斷，如果表已存在，則不執行建立操作。

另外，通過對比語句可以看出，外部表較內部表而言，只是在create後加了一個external。這個是我們目前可以看到的現象。

4、下面來看一下兩個表的區別。

剛才我們建立了一個內部表inlog，一個外部表outlog，在HDFS檔案系統，建立兩個目錄

view plain copy
  1. hadoop fs -mkdir -p /logs/in  
  2. hadoop fs -mkdir -p /logs/out  

然後將Linux上/data/mydata/solog檔案，分別上傳到HDFS的/logs/in和/logs/out目錄下。

view plain copy
  1. hadoop fs -put /data/mydata/solog /logs/in  
  2. hadoop fs -put /data/mydata/solog /logs/out  

下面分別將兩個資料，載入到兩張表中。

載入資料到內部表

view plain copy
  1. load data inpath '/logs/in/solog' into table inlog;  

載入資料到外部表

view plain copy
  1. load data inpath '/logs/out/solog' into table outlog;  

檢視hdfs檔案系統的變化

view plain copy
  1. hive>dfs -lsr /;  
  2. lsr: DEPRECATED: Please use 'ls -R' instead.  
  3. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:46 /logs  
  4. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /logs/in  
  5. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /logs/out  
  6. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:09 /mydata  
  7. -rw-r--r--   1 zhangyu supergroup       5171 2016-12-20 10:09 /mydata/solog  
  8. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:10 /myout  
  9. drwx-wx-wx   - zhangyu supergroup          0 2016-12-21 02:00 /tmp  
  10. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user  
  11. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user/hive  
  12. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse  
  13. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse/mydb.db  
  14. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /user/hive/warehouse/mydb.db/inlog  
  15. -rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/inlog/solog  
  16. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /user/hive/warehouse/mydb.db/outlog  
  17. -rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/outlog/solog  

這裡可以看到一個現象，就是使用load載入資料的時候，hive會將原始資料，進行遷移，放到表對應的目錄下。

下面執行刪除內部表，以及刪除外部表的操作。

view plain copy
  1. drop table inlog;  
view plain copy
  1. drop table outlog;  

再來檢視hdfs檔案系統的變化。

view plain copy
  1. hive>dfs -lsr /;  
  2. lsr: DEPRECATED: Please use 'ls -R' instead.  
  3. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:46 /logs  
  4. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /logs/in  
  5. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /logs/out  
  6. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:09 /mydata  
  7. -rw-r--r--   1 zhangyu supergroup       5171 2016-12-20 10:09 /mydata/solog  
  8. drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:10 /myout  
  9. drwx-wx-wx   - zhangyu supergroup          0 2016-12-21 02:00 /tmp  
  10. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user  
  11. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user/hive  
  12. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse  
  13. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:50 /user/hive/warehouse/mydb.db  
  14. drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /user/hive/warehouse/mydb.db/outlog  
  15. -rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/outlog/solog  

執行完刪除之後，在hive資料倉儲下，儲存資料的目錄/user/hive/warehouse/mydb.db下，inlog表已經被刪除。而outlog目錄以及檔案還在。

使用show tables檢視當前都有哪些表，可以看到資料倉儲中，剛才我們建立的表已經被刪除。

資料表在刪除的時候，內部表會連資料一起刪除，而外部表只刪除表結構，資料還是保留的

5、修改表的表結構。再次建立內部表inlog

view plain copy
  1. create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
  2. row format delimited fields terminated by '\t';  

修改inlog的表結構，新增兩列osname,osversion，用於儲存訪問者的作業系統名稱、作業系統版本。

view plain copy
  1. alter table inlog add columns(osname string,osversion string);  

使用desc命令檢視一下加完欄位後的inlog表的表結構

view plain copy
  1. desc inlog;  

6、 修改inlog表的表名。把 inlog 表重新命名為 tmp1。

view plain copy
  1. alter table inlog rename to tmp1;  

可以使用show tables再次檢視錶名的變化。

這個命令可以讓使用者為表更名，資料所在的位置和分割槽名並不改變。換而言之，舊的表名並未“釋放” ，對舊錶的更改會改變新表的資料。

7、建立與已知表相同結構的表。建立一個與tmp1表結構相同的表，表名為tmp2，這裡要用到 LIKE 關鍵字。

答案：

view plain copy
  1. create table tmp2 like tmp1;  

建立完成並檢視

三、向Hive表匯入資料

向Hive中匯入資料，主要有4中方式，分別為：

①從本地檔案系統中匯入資料到Hive表；

②從HDFS上匯入資料到Hive表；

③從別的表中查詢出相應的資料並匯入到Hive表中；

④在建立表的時候通過從別的表中查詢出相應的記錄並插入到所建立的表中

1、從本地檔案系統中匯入資料到Hive表

在hive中的mydb資料倉儲中，再次建立一個內部表名為inlog，字元型別為string,以“\t”為分隔符

view plain copy
  1. create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  
  2. row format delimited fields terminated by '\t'  
  3. stored as textfile;  

[row format delimited]關鍵字，是用來設定建立的表在載入資料的時候，支援的列分隔符。

[stored as textfile]關鍵字是用來設定載入資料的資料型別,預設是TEXTFILE。

執行命令並檢視

將本地檔案系統中的檔案/data/mydata/solog，匯入到inlog表。

view plain copy
  1. load data local inpath '/data/mydata/solog' into table inlog;  

查詢一下，inlog表中的資料，看看是否已經匯入

view plain copy
  1. select * from inlog limit 10;  

2、將HDFS上的資料匯入到hive表；

首先將本地/data/mydata/solog檔案，上傳到HDFS的/logs/in目錄下。

(如果目錄不存在，首先建立/logs/in目錄)

view plain copy
  1. hadoop fs -mkdir -p /logs/in  
view plain copy
  1. hadoop fs -put /data/mydata/solog /logs/in  

在mydb中建立表，名為inlog2。inlog2表與inlog表結構相同

view plain copy
  1. create table inlog2 like inlog;  

接下來將HDFS下/logs/in/solog中的資料，匯入到inlog2表中

view plain copy
  1. load data inpath '/logs/in/solog' into table inlog2;  

匯入成功,查詢一下

view plain copy
  1. select * from inlog2 limit 10;  

通過以上操作，可以看出，從HDFS中資料匯入到hive表，較本地linux資料匯入到hive表中，主要差別是在load data後少了local。

3、從一個表中查詢出資料，並匯入到另一個hive表中

在hive中建立一個inlog3表，inlog3表的表結構和inlog表結構相同。

view plain copy
  1. create table inlog3 like inlog;  

將inlog表中的資料，匯入到inlog3表中。

view plain copy
  1. insert into table inlog3 select * from inlog;  

如果在inlog3中已經存在一部分資料，如果想要從inlog表中進行查詢，並將查詢出的結果覆蓋inlog3表中的資料，可以使用關鍵詞overwrite

view plain copy
  1. insert overwrite table inlog3 select * from inlog;  

4、在建立表的時候通過從別的表中查詢出相應的記錄並插入到所建立的表中

在mydb資料倉儲中，建立表，命名為inlog5，同時，將inlog表中全部的資料，插入到inlog5中。

view plain copy
  1. create table inlog5 as select * from inlog;  

建立並匯入完成，檢視inlog5表中的資料

view plain copy
  1. select * from inlog5 limit 10;  
四、將hive中的資料匯出

將hive中的資料匯出，主要有3種方式：①匯出到本地檔案系統；②匯出到HDFS中；③匯出到Hive的另一個表中。

1、匯出到本地檔案系統；

將hive中的inlog表匯出到Linux本地檔案系統/data/myout/1中

(如果目錄不存在，需建立目錄)

view plain copy
  1. mkdir -p /data/myout  

開始匯出資料

view plain copy
  1. insert overwrite local directory '/data/myout/1' select * from inlog;  

匯出成功，在本地查詢一下

view plain copy
  1. cd /data/myout/1       (為目錄，目錄下有000000_0檔案 ）  
view plain copy
  1. cat  000000_0  （發現列之間沒有分割 ）  

可以使用下面的方式增加分割

view plain copy
  1. insert overwrite local directory '/data/myout/2'  
  2. select dt, concat('\t',ip),concat('\t',screenx),concat('\t',screeny),concat('\t',word),concat('\t',preword) from inlog;  

加完分隔，再次查詢，和匯入資料到Hive不一樣，不能用insert into來將資料匯出

2、將資料匯出到HDFS中

將hive中的inlog表中的資料，匯出到HDFS中的/myout/1裡

(如果目錄不存在，需建立目錄)

view plain copy
  1. hadoop fs -mkdir /myout/1  

開始匯出資料

view plain copy
  1. insert overwrite directory '/myout/1'  
  2. select dt, concat('\t',ip),concat('\t',screenx),concat('\t',screeny),concat('\t',word),concat('\t',preword) from inlog;  

切換到hadoop目錄/myout/1下查詢一下

view plain copy
  1. hadoop fs -cat /myout/1/000000_0  

3、匯出到Hive的另一個表中

將hive中表inlog中的資料，匯出到inlog6表（兩表欄位及字元型別相同）

首先在hive中的mydb資料倉儲下，建立一個表名為inlog6

view plain copy
  1. create table inlog6 (dt string, ip string, screenx int, screeny int, word string, preword string)  
  2. row format delimited fields terminated by '\t';  
view plain copy
  1. show tables;  

然後將inlog中的資料匯出到inlog6中；

view plain copy
  1. insert into table inlog6 select * from inlog;  

匯出完成，檢視一下資料匯出後的inlog6表

view plain copy
  1. select * from inlog6;  

相關文章