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

hive  

view
 plain copy

create database mydb;  

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

view
 plain copy

create database if not exists mydb;  

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

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

view
 plain copy

describe database mydb;  

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

view
 plain copy

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

create database mydb;  

view
 plain copy

use mydb;  

show tables;  

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

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

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

view
 plain copy

時間             ip             螢幕寬 螢幕高   搜尋詞  上一個搜尋詞  

20161220155840  192.168.1.179   1366    768     HTML  

20161220155843  192.168.1.179   1366    768     Sqoop   HTML  

20161220155911  192.168.1.179   1366    768     SparkR  Sqoop  

20161220155914  192.168.1.155   1600    900     hadoop  

20161220155928  192.168.1.155   1600    900     sqoop   hahahhaha  

... ...

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

view
 plain copy

use mydb;  

view
 plain copy

create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  

row format delimited fields terminated by '\t';  

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

view
 plain copy

show tables;  

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

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

view
 plain copy

use mydb;  

create external table outlog (dt string, ip string, screenx int, screeny int, word string, preword string)  

row format delimited fields terminated by '\t';  

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

view
 plain copy

show tables;  

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

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

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

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

view
 plain copy

hadoop fs -mkdir -p /logs/in  

hadoop fs -mkdir -p /logs/out  

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

view
 plain copy

hadoop fs -put /data/mydata/solog /logs/in  

hadoop fs -put /data/mydata/solog /logs/out  

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

載入資料到內部表

view
 plain copy

load data inpath '/logs/in/solog' into table inlog;  

載入資料到外部表

view
 plain copy

load data inpath '/logs/out/solog' into table outlog;  

檢視hdfs檔案系統的變化

view
 plain copy

hive>dfs -lsr /;  

lsr: DEPRECATED: Please use 'ls -R' instead.  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:46 /logs  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /logs/in  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /logs/out  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:09 /mydata  

-rw-r--r--   1 zhangyu supergroup       5171 2016-12-20 10:09 /mydata/solog  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:10 /myout  

drwx-wx-wx   - zhangyu supergroup          0 2016-12-21 02:00 /tmp  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user/hive  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse/mydb.db  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /user/hive/warehouse/mydb.db/inlog  

-rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/inlog/solog  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /user/hive/warehouse/mydb.db/outlog  

-rwxr-xr-x   1 zhangyu supergroup       5171 2016-12-21 06:46 /user/hive/warehouse/mydb.db/outlog/solog

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

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

view
 plain copy

drop table inlog;  

view
 plain copy

drop table outlog;  

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

view
 plain copy

hive>dfs -lsr /;  

lsr: DEPRECATED: Please use 'ls -R' instead.  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:46 /logs  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:48 /logs/in  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /logs/out  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:09 /mydata  

-rw-r--r--   1 zhangyu supergroup       5171 2016-12-20 10:09 /mydata/solog  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-20 10:10 /myout  

drwx-wx-wx   - zhangyu supergroup          0 2016-12-21 02:00 /tmp  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:43 /user/hive  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:44 /user/hive/warehouse  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:50 /user/hive/warehouse/mydb.db  

drwxr-xr-x   - zhangyu supergroup          0 2016-12-21 06:49 /user/hive/warehouse/mydb.db/outlog  

-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

create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  

row format delimited fields terminated by '\t';  

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

view
 plain copy

alter table inlog add columns(osname string,osversion string);  

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

view
 plain copy

desc inlog;  

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

view
 plain copy

alter table inlog rename to tmp1;  

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

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

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

答案：

view
 plain copy

create table tmp2 like tmp1;  

建立完成並檢視

三、向Hive表匯入資料

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

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

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

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

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

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

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

view
 plain copy

create table inlog (dt string, ip string, screenx int, screeny int, word string, preword string)  

row format delimited fields terminated by '\t'  

stored as textfile;  

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

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

執行命令並檢視

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

view
 plain copy

load data local inpath '/data/mydata/solog' into table inlog;  

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

view
 plain copy

select * from inlog limit 10;  

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

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

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

view
 plain copy

hadoop fs -mkdir -p /logs/in  

view
 plain copy

hadoop fs -put /data/mydata/solog /logs/in  

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

view
 plain copy

create table inlog2 like inlog;  

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

view
 plain copy

load data inpath '/logs/in/solog' into table inlog2;  

匯入成功,查詢一下

view
 plain copy

select * from inlog2 limit 10;  

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

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

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

view
 plain copy

create table inlog3 like inlog;  

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

view
 plain copy

insert into table inlog3 select * from inlog;  

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

view
 plain copy

insert overwrite table inlog3 select * from inlog;  

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

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

view
 plain copy

create table inlog5 as select * from inlog;  

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

view
 plain copy

select * from inlog5 limit 10;  

四、將hive中的資料匯出

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

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

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

view
 plain copy

mkdir -p /data/myout  

開始匯出資料

view
 plain copy

insert overwrite local directory '/data/myout/1' select * from inlog;  

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

view
 plain copy

cd /data/myout/1       (為目錄，目錄下有000000_0檔案 ）  

view
 plain copy

cat  000000_0  （發現列之間沒有分割 ）  

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

view
 plain copy

insert overwrite local directory '/data/myout/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

hadoop fs -mkdir /myout/1  

開始匯出資料

view
 plain copy

insert overwrite directory '/myout/1'  

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

hadoop fs -cat /myout/1/000000_0  

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

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

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

view
 plain copy

create table inlog6 (dt string, ip string, screenx int, screeny int, word string, preword string)  

row format delimited fields terminated by '\t';  

view
 plain copy

show tables;  

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

view
 plain copy

insert into table inlog6 select * from inlog;  

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

view
 plain copy

select * from inlog6;  

提交報告暫時儲存

Hive表的基本操作
2021-01-10
Hive
資料庫的基本操作
2024-12-09
資料庫
基本 SQL 之資料庫及表管理
2019-02-19
SQL資料庫
MySQL資料表的基本操作
2018-04-28
MySql
資料庫基本操作
2024-08-15
資料庫
MySQL入門系列：資料庫和表的基本操作
2019-03-07
MySql資料庫
資料庫表的基本操作和編碼格式設定
2020-10-03
資料庫
Oracle資料庫管理——表資料庫高水位及shrink操作
2020-03-03
Oracle資料庫
Laravel 資料庫基本操作
2021-05-27
Laravel資料庫
postgresql 資料庫基本操作
2018-06-19
SQL資料庫
【Falsk 使用資料庫】---- 資料庫基本操作
2020-12-20
資料庫
MongoDB資料庫的基本操作梳理
2019-04-13
MongoDB資料庫
MySQL—-MySQL資料庫入門—-第二章資料庫和表的基本操作
2019-02-20
MySql資料庫
Hive的基本操作用法
2024-11-10
Hive
EntityFramework Core筆記：表結構及資料基本操作（2）
2018-05-27
Framework筆記
資料庫基本操作術語
2024-08-28
資料庫
mysql資料庫基本操作（五）
2019-05-22
MySql資料庫
mysql資料庫基本操作（三）
2019-05-21
MySql資料庫
mysql資料庫基本操作（四)
2019-05-21
MySql資料庫
02、MySQL—資料庫基本操作
2019-06-29
MySql資料庫
mysql資料庫基本操作（六）
2019-05-27
MySql資料庫
SQL—對資料表內容的基本操作
2018-05-14
SQL
時序資料庫之InfluxDB的基本操作
2022-03-26
資料庫UX
Hive學習之基本操作
2018-11-30
Hive
python+資料庫（三）用python對資料庫基本操作
2018-11-08
Python資料庫
值得白嫖的資料庫常用操作語句彙總（資料庫、資料表、資料操作）
2020-09-19
資料庫
Oracle dos連線資料庫基本操作
2020-04-04
Oracle資料庫
Python3資料庫操作基本類
2019-02-11
Python資料庫
2.資料庫Mysql--------基本操作
2018-06-21
資料庫MySql
[hive]hive資料模型中四種表
2018-08-14
Hive模型
大資料4.2 -- hive資料庫
2018-04-03
大資料Hive資料庫
hadoop之旅8-centerOS7 : Hive的基本操作
2018-11-04
HadoopROSHive
Spark操作Hive分割槽表
2018-12-07
SparkHive
Redis資料基本操作
2018-11-16
Redis
MySQL資料基本操作
2018-04-21
MySql
查詢資料庫表及表欄位
2024-12-05
資料庫
利用MySQL原資料資訊批量轉換指定庫資料表生成Hive建表語句
2021-08-09
MySqlHive
資料庫的基本資訊，都在這幾張表裡了
2022-03-16
資料庫
Oracle 操作表結構基本語法及示例
2018-07-12
Oracle