2021年大数据Hive（三）：手把手教你如何吃透Hive数据库和表操作（学会秒变数仓大佬）
全網(wǎng)最詳細(xì)的Hive文章系列，強(qiáng)烈建議收藏加關(guān)注！
后面更新文章都會(huì)列出歷史文章目錄，幫助大家回顧知識(shí)重點(diǎn)。
目錄
系列歷史文章
前言
Hive數(shù)據(jù)庫(kù)和表操作
一、數(shù)據(jù)庫(kù)操作
1、創(chuàng)建數(shù)據(jù)庫(kù)
2、創(chuàng)建數(shù)據(jù)庫(kù)并指定hdfs存儲(chǔ)位置
3、查看數(shù)據(jù)庫(kù)詳細(xì)信息
4、刪除數(shù)據(jù)庫(kù)
二、數(shù)據(jù)庫(kù)表操作
1、創(chuàng)建數(shù)據(jù)庫(kù)表語(yǔ)法
2、Hive建表時(shí)候的字段類型
3、內(nèi)部表操作
4、外部表操作
5、復(fù)雜類型操作
6、分區(qū)表
7、分桶表
8、修改表
9、hive表中加載數(shù)據(jù)
10、hive表中的數(shù)據(jù)導(dǎo)出
系列歷史文章
2021年大數(shù)據(jù)Hive（十二）：Hive綜合案例！！！
2021年大數(shù)據(jù)Hive（十一）：Hive調(diào)優(yōu)
2021年大數(shù)據(jù)Hive（十）：Hive的數(shù)據(jù)存儲(chǔ)格式
2021年大數(shù)據(jù)Hive（九）：Hive的數(shù)據(jù)壓縮
2021年大數(shù)據(jù)Hive（八）：Hive自定義函數(shù)
2021年大數(shù)據(jù)Hive（七）：Hive的開窗函數(shù)
2021年大數(shù)據(jù)Hive（六）：Hive的表生成函數(shù)
2021年大數(shù)據(jù)Hive（五）：Hive的內(nèi)置函數(shù)（數(shù)學(xué)、字符串、日期、條件、轉(zhuǎn)換、行轉(zhuǎn)列）
2021年大數(shù)據(jù)Hive（四）：Hive查詢語(yǔ)法
2021年大數(shù)據(jù)Hive（三）：手把手教你如何吃透Hive數(shù)據(jù)庫(kù)和表操作（學(xué)會(huì)秒變數(shù)倉(cāng)大佬）
2021年大數(shù)據(jù)Hive（二）：Hive的三種安裝模式和MySQL搭配使用
2021年大數(shù)據(jù)Hive（一）：Hive基本概念
前言
?2021大數(shù)據(jù)領(lǐng)域優(yōu)質(zhì)創(chuàng)作博客，帶你從入門到精通，該博客每天更新，逐漸完善大數(shù)據(jù)各個(gè)知識(shí)體系的文章，幫助大家更高效學(xué)習(xí)。
有對(duì)大數(shù)據(jù)感興趣的可以關(guān)注微信公眾號(hào)：三幫大數(shù)據(jù)
Hive數(shù)據(jù)庫(kù)和表操作
一、數(shù)據(jù)庫(kù)操作
1、創(chuàng)建數(shù)據(jù)庫(kù)
create?database if?not?exists?myhive;use??myhive;說(shuō)明：hive的表存放位置模式是由hive-site.xml當(dāng)中的一個(gè)屬性指定的
<name>hive.metastore.warehouse.dir</name><value>/user/hive/warehouse</value>2、創(chuàng)建數(shù)據(jù)庫(kù)并指定hdfs存儲(chǔ)位置
create?database myhive2 location '/myhive2';3、查看數(shù)據(jù)庫(kù)詳細(xì)信息
查看數(shù)據(jù)庫(kù)基本信息
desc??database ?myhive;4、刪除數(shù)據(jù)庫(kù)
刪除一個(gè)空數(shù)據(jù)庫(kù)，如果數(shù)據(jù)庫(kù)下面有數(shù)據(jù)表，那么就會(huì)報(bào)錯(cuò)
drop??database ?myhive;強(qiáng)制刪除數(shù)據(jù)庫(kù)，包含數(shù)據(jù)庫(kù)下面的表一起刪除
drop??database ?myhive2 ?cascade;?二、數(shù)據(jù)庫(kù)表操作
1、創(chuàng)建數(shù)據(jù)庫(kù)表語(yǔ)法
CREATE?[EXTERNAL]?TABLE?[IF?NOT?EXISTS]?table_name[(col_name?data_type [COMMENT?col_comment],?...)]?[COMMENT?table_comment]?[PARTITIONED BY?(col_name?data_type [COMMENT?col_comment],?...)]?[CLUSTERED BY?(col_name,?col_name,?...)?[SORTED BY?(col_name?[ASC|DESC],?...)]?INTO?num_buckets BUCKETS]?[ROW?FORMAT?row_format]?[STORED AS?file_format]?[LOCATION hdfs_path]說(shuō)明：
1、CREATE TABLE?創(chuàng)建一個(gè)指定名字的表。如果相同名字的表已經(jīng)存在，則拋出異常；用戶可以用 IF NOT EXISTS 選項(xiàng)來(lái)忽略這個(gè)異常。
2、EXTERNAL?關(guān)鍵字可以讓用戶創(chuàng)建一個(gè)外部表，在建表的同時(shí)指定一個(gè)指向?qū)嶋H數(shù)據(jù)的路徑（LOCATION），Hive 創(chuàng)建內(nèi)部表時(shí)，會(huì)將數(shù)據(jù)移動(dòng)到數(shù)據(jù)倉(cāng)庫(kù)指向的路徑；若創(chuàng)建外部表，僅記錄數(shù)據(jù)所在的路徑，不對(duì)數(shù)據(jù)的位置做任何改變。在刪除表的時(shí)候，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會(huì)被一起刪除，而外部表只刪除元數(shù)據(jù)，不刪除數(shù)據(jù)。
3、LIKE?允許用戶復(fù)制現(xiàn)有的表結(jié)構(gòu)，但是不復(fù)制數(shù)據(jù)。
4、ROW FORMAT DELIMITED 可用來(lái)指定行分隔符
5、STORED AS??SEQUENCEFILE|TEXTFILE|RCFILE 來(lái)指定該表數(shù)據(jù)的存儲(chǔ)格式，hive中，表的默認(rèn)存儲(chǔ)格式為TextFile。
6、CLUSTERED BY? 對(duì)于每一個(gè)表（table）進(jìn)行分桶(MapReuce中的分區(qū)），桶是更為細(xì)粒度的數(shù)據(jù)范圍劃分。Hive也是 針對(duì)某一列進(jìn)行桶的組織。Hive采用對(duì)列值哈希，然后除以桶的個(gè)數(shù)求余的方式?jīng)Q定該條記錄存放在哪個(gè)桶當(dāng)中。
7、LOCATION? 指定表在HDFS上的存儲(chǔ)位置。
2、Hive建表時(shí)候的字段類型
| 分類 | 類型 | 描述 | 字面量示例 |
| 原始類型 | BOOLEAN | true/false | TRUE |
| TINYINT | 1字節(jié)的有符號(hào)整數(shù) -128~127 | 1Y | |
| SMALLINT | 2個(gè)字節(jié)的有符號(hào)整數(shù)，-32768~32767 | 1S | |
| INT | 4個(gè)字節(jié)的帶符號(hào)整數(shù)(-2147483648~2147483647) | 1 | |
| BIGINT | 8字節(jié)帶符號(hào)整數(shù) | 1L | |
| FLOAT | 4字節(jié)單精度浮點(diǎn)數(shù)1.0 | ||
| DOUBLE | 8字節(jié)雙精度浮點(diǎn)數(shù) | 1.0 | |
| DEICIMAL | 任意精度的帶符號(hào)小數(shù) | 1.0 | |
| STRING | 字符串，變長(zhǎng) | “a”,’b’ | |
| VARCHAR | 變長(zhǎng)字符串 | “a”,’b’ | |
| CHAR | 固定長(zhǎng)度字符串 | “a”,’b’ | |
| BINARY | 字節(jié)數(shù)組 | 無(wú)法表示 | |
| TIMESTAMP | 時(shí)間戳，毫秒值精度 | 122327493795 | |
| DATE | 日期 | ‘2016-03-29’ | |
| Time | ?時(shí)分秒 | ‘12:35:46’ | |
| DateTime | 年月日 時(shí)分秒 | ||
| 復(fù)雜類型 | ARRAY | 有序的的同類型的集合 | ???["beijing","shanghai","tianjin","hangzhou"] |
| MAP | key-value,key必須為原始類型，value可以任意類型 | {"數(shù)學(xué)":80,"語(yǔ)文":89,"英語(yǔ)":95} | |
| STRUCT | 字段集合,類型可以不同 | struct(‘1’,1,1.0) |
3、內(nèi)部表操作
未被external修飾的是內(nèi)部表（managed table）,內(nèi)部表又稱管理表,內(nèi)部表數(shù)據(jù)存儲(chǔ)的位置由hive.metastore.warehouse.dir參數(shù)決定（默認(rèn)：/user/hive/warehouse），刪除內(nèi)部表會(huì)直接刪除元數(shù)據(jù)（metadata）及存儲(chǔ)數(shù)據(jù)，因此內(nèi)部表不適合和其他工具共享數(shù)據(jù)。
1、hive建表初體驗(yàn)
create?database myhive;use?myhive;create?table?stu(id int,name string);insert?into?stu values?(1,"zhangsan");select?*?from?stu;???????2、創(chuàng)建表并指定字段之間的分隔符
create??table?if?not?exists?stu3(id int?,name string)?row?format?delimited fields terminated by?'\t';???????3、根據(jù)查詢結(jié)果創(chuàng)建表
create?table?stu3 as?select?*?from?stu2;???????4、根據(jù)已經(jīng)存在的表結(jié)構(gòu)創(chuàng)建表
create?table?stu4 like?stu2;???????5、查詢表的類型
desc?formatted ?stu2;??????????????6、刪除表
drop?table?stu2;查看數(shù)據(jù)庫(kù)和HDFS，發(fā)現(xiàn)刪除內(nèi)部表之后，所有的內(nèi)容全部刪除
4、外部表操作
在創(chuàng)建表的時(shí)候可以指定external關(guān)鍵字創(chuàng)建外部表,外部表對(duì)應(yīng)的文件存儲(chǔ)在location指定的hdfs目錄下,向該目錄添加新文件的同時(shí)，該表也會(huì)讀取到該文件(當(dāng)然文件格式必須跟表定義的一致)。
外部表因?yàn)槭侵付ㄆ渌膆dfs路徑的數(shù)據(jù)加載到表當(dāng)中來(lái)，所以hive表會(huì)認(rèn)為自己不完全獨(dú)占這份數(shù)據(jù)，所以刪除hive外部表的時(shí)候，數(shù)據(jù)仍然存放在hdfs當(dāng)中，不會(huì)刪掉。
1、數(shù)據(jù)裝載載命令Load
Load命令用于將外部數(shù)據(jù)加載到Hive表中
語(yǔ)法:
load data?[local]?inpath '/export/data/datas/student.txt'?[overwrite] |?into?table?student [partition?(partcol1=val1,…)];參數(shù):
- load data:表示加載數(shù)據(jù)
- local:表示從本地加載數(shù)據(jù)到hive表；否則從HDFS加載數(shù)據(jù)到hive表
- inpath:表示加載數(shù)據(jù)的路徑
- overwrite:表示覆蓋表中已有數(shù)據(jù)，否則表示追加
- into table:表示加載到哪張表
- student:表示具體的表
- partition:表示上傳到指定分區(qū)
???????2、操作案例
分別創(chuàng)建老師與學(xué)生表外部表，并向表中加載數(shù)據(jù)
源數(shù)據(jù)如下:
student.txt
01 趙雷 1990-01-01 男
02 錢電 1990-12-21 男
03 孫風(fēng) 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吳蘭 1992-03-01 女
07 鄭竹 1989-07-01 女
08 王菊 1990-01-20 女
teacher.txt???????
01 張三
02 李四
03 王五
- 創(chuàng)建老師表：
create?external?table?teacher (tid string,tname string)?row?format?delimited fields terminated by?'\t';- 創(chuàng)建學(xué)生表：
create?external?table?student (sid string,sname string,sbirth string ,?ssex string )?row?format?delimited fields terminated by?'\t';- 從本地文件系統(tǒng)向表中加載數(shù)據(jù)
load data?local?inpath '/export/data/hivedatas/student.txt'?into?table?student;- 加載數(shù)據(jù)并覆蓋已有數(shù)據(jù)
load data?local?inpath '/export/data/hivedatas/student.txt'?overwrite ?into?table?student;- 從hdfs文件系統(tǒng)向表中加載數(shù)據(jù)
其實(shí)就是一個(gè)移動(dòng)文件的操作
需要提前將數(shù)據(jù)上傳到hdfs文件系統(tǒng)，
hadoop fs -mkdir -p /hivedatascd /export/data/hivedatashadoop fs -put teacher.csv?/hivedatas/load data?inpath '/hivedatas/teacher.csv'?into?table?teacher;注意,如果刪掉teacher表，hdfs的數(shù)據(jù)仍然存在，并且重新創(chuàng)建表之后，表中就直接存在數(shù)據(jù)了,因?yàn)槲覀兊膕tudent表使用的是外部表，drop table之后，表當(dāng)中的數(shù)據(jù)依然保留在hdfs上面了
5、復(fù)雜類型操作
1、Array類型
Array是數(shù)組類型，Array中存放相同類型的數(shù)據(jù)
源數(shù)據(jù): ?
說(shuō)明:name與locations之間制表符分隔，locations中元素之間逗號(hào)分隔
zhangsan ??beijing,shanghai,tianjin,hangzhou
wangwu ?? changchun,chengdu,wuhan,beijin
建表語(yǔ)句
create external?table?hive_array(name string,?work_locations array<string>)row?format?delimited fields terminated by?'\t'collection items terminated by ?',';導(dǎo)入數(shù)據(jù)（從本地導(dǎo)入，同樣支持從HDFS導(dǎo)入）
load data?local?inpath '/export/data/hivedatas/work_locations.txt'?overwrite into?table?hive_array;常用查詢：
-- 查詢所有數(shù)據(jù)select?*?from?hive_array;-- 查詢loction數(shù)組中第一個(gè)元素select?name,?work_locations[0]?location from?hive_array;-- 查詢location數(shù)組中元素的個(gè)數(shù)select?name,?size(work_locations)?location from?hive_array;-- 查詢location數(shù)組中包含tianjin的信息select?*?from?hive_array where?array_contains(work_locations,'tianjin');????????6、分區(qū)表
分區(qū)不是獨(dú)立的表模型,要和內(nèi)部表或者外部表結(jié)合:
??內(nèi)部分區(qū)表
??外部分區(qū)表
??????????????1、基本操作
在大數(shù)據(jù)中，最常用的一種思想就是分治，分區(qū)表實(shí)際就是對(duì)應(yīng)hdfs文件系統(tǒng)上的的獨(dú)立的文件夾，該文件夾下是該分區(qū)所有數(shù)據(jù)文件。
分區(qū)可以理解為分類，通過(guò)分類把不同類型的數(shù)據(jù)放到不同的目錄下。
分類的標(biāo)準(zhǔn)就是分區(qū)字段，可以一個(gè)，也可以多個(gè)。
分區(qū)表的意義在于優(yōu)化查詢。查詢時(shí)盡量利用分區(qū)字段。如果不使用分區(qū)字段，就會(huì)全部掃描。
在查詢是通過(guò)where子句查詢來(lái)指定所需的分區(qū)。
在hive中，分區(qū)就是分文件夾
創(chuàng)建分區(qū)表語(yǔ)法
create?table?score(sid string,cid string,?sscore int)?partitioned by?(month?string)?row?format?delimited fields terminated by?'\t';創(chuàng)建一個(gè)表帶多個(gè)分區(qū)
create?table?score2 (sid string,cid string,?sscore int)?partitioned by?(year?string,month?string,day?string)?row?format?delimited fields terminated by?'\t';加載數(shù)據(jù)到分區(qū)表中
load data?local?inpath '/export/data/hivedatas/score.csv'?into?table?score partition?(month='202006');加載數(shù)據(jù)到一個(gè)多分區(qū)的表中去
load data?local?inpath '/export/data/hivedatas/score.csv'?into?table?score2 partition(year='2020',month='06',day='01');多分區(qū)聯(lián)合查詢使用union ?all來(lái)實(shí)現(xiàn)
select?*?from?score where?month?=?'202006'?union?all?select?*?from?score where?month?=?'202007';查看分區(qū)
show ?partitions ?score;添加一個(gè)分區(qū)
alter?table?score?add?partition(month='202008');同時(shí)添加多個(gè)分區(qū)
alter?table?score add?partition(month='202009')?partition(month?=?'202010');注意：添加分區(qū)之后就可以在hdfs文件系統(tǒng)當(dāng)中看到表下面多了一個(gè)文件夾
刪除分區(qū)
alter?table?score drop?partition(month?=?'202010');???????7、分桶表
分桶就是將數(shù)據(jù)劃分到不同的文件，其實(shí)就是MapReduce的分區(qū)
??????????????1、基本操作
將數(shù)據(jù)按照指定的字段進(jìn)行分成多個(gè)桶中去，說(shuō)白了就是將數(shù)據(jù)按照字段進(jìn)行劃分，可以將數(shù)據(jù)按照字段劃分到多個(gè)文件當(dāng)中去
開啟hive的桶表功能(如果執(zhí)行該命令報(bào)錯(cuò)，表示這個(gè)版本的Hive已經(jīng)自動(dòng)開啟了分桶功能，則直接進(jìn)行下一步)
set?hive.enforce.bucketing=true;設(shè)置reduce的個(gè)數(shù)
set?mapreduce.job.reduces=3;?創(chuàng)建分桶表
create?table?course (cid string,c_name string,tid string)?clustered by(cid)?into?3?buckets row?format?delimited fields terminated by?'\t';桶表的數(shù)據(jù)加載，由于桶表的數(shù)據(jù)加載通過(guò)hdfs ?dfs ?-put文件或者通過(guò)load ?data均不好使，只能通過(guò)insert ?overwrite
創(chuàng)建普通表，并通過(guò)insert ?overwrite的方式將普通表的數(shù)據(jù)通過(guò)查詢的方式加載到桶表當(dāng)中去
創(chuàng)建普通表：
create?table?course_common (cid string,c_name string,tid string)?row?format?delimited fields terminated by?'\t';普通表中加載數(shù)據(jù)
load data?local?inpath '/export/data/hivedatas/course.csv'?into?table?course_common;通過(guò)insert ?overwrite給桶表中加載數(shù)據(jù)
insert?overwrite table?course select?*?from?course_common cluster?by(cid);8、修改表
1、表重命名
基本語(yǔ)法：
alter??table??old_table_name ?rename??to??new_table_name;-- 把表score3修改成score4alter?table?score3 rename?to?score4;???????2、增加/修改列信息
-- 1:查詢表結(jié)構(gòu)desc?score4;-- 2:添加列alter?table?score4 add?columns (mycol string,?mysco string);-- 3:查詢表結(jié)構(gòu)desc?score4;-- 4:更新列alter?table?score4 change column?mysco mysconew int;-- 5:查詢表結(jié)構(gòu)desc?score4;??????????????3、刪除表
drop?table?score4;???????4、清空表數(shù)據(jù)
只能清空管理表，也就是內(nèi)部表
truncate?table?score4;9、hive表中加載數(shù)據(jù)
1、直接向分區(qū)表中插入數(shù)據(jù)
通過(guò)insert into方式加載數(shù)據(jù)
create?table?score3 like?score;insert?into?table?score3 partition(month?='202007')?values?('001','002',100);通過(guò)查詢方式加載數(shù)據(jù)
create?table?score4 like?score;insert?overwrite table?score4 partition(month?=?'202006')?select?sid,cid,sscore from?score;???????2、通過(guò)查詢插入數(shù)據(jù)
通過(guò)load方式加載數(shù)據(jù)
create?table?score5 like?score;load data?local?inpath '/export/data/hivedatas/score.csv'?overwrite into?table?score5 partition(month='202006');- ???????多插入模式
常用于實(shí)際生產(chǎn)環(huán)境當(dāng)中，將一張表拆開成兩部分或者多部分
給score表加載數(shù)據(jù)
load data?local?inpath '/export/data/hivedatas/score.csv'?overwrite into?table?score partition(month='202006');創(chuàng)建第一部分表：
create?table?score_first(?sid string,cid ?string)?partitioned by?(month?string)?row?format?delimited fields terminated by?'\t'?;創(chuàng)建第二部分表：
create?table?score_second(cid string,sscore int)?partitioned by?(month?string)?row?format?delimited fields terminated by?'\t';分別給第一部分與第二部分表加載數(shù)據(jù)
from?score insert?overwrite table?score_first partition(month='202006')?select?sid,cid insert?overwrite table?score_second partition(month?=?'202006')??select?cid,sscore;- ???????查詢語(yǔ)句中創(chuàng)建表并加載數(shù)據(jù)（as?select）
將查詢的結(jié)果保存到一張表當(dāng)中去
create?table?score5 as?select?*?from?score;- ???????創(chuàng)建表時(shí)通過(guò)location指定加載數(shù)據(jù)路徑
1、創(chuàng)建表，并指定在hdfs上的位置
create?external?table?score6 (sid string,cid string,sscore int)?row?format?delimited fields terminated by?'\t'?location '/myscore6';2、上傳數(shù)據(jù)到hdfs上
hadoop fs -mkdir -p /myscore6hadoop fs -put score.csv/myscore6;3、查詢數(shù)據(jù)
select?*?from?score6;???????10、hive表中的數(shù)據(jù)導(dǎo)出
將hive表中的數(shù)據(jù)導(dǎo)出到其他任意目錄，例如linux本地磁盤，例如hdfs，例如mysql等等
??????????????1、insert導(dǎo)出
1）將查詢的結(jié)果導(dǎo)出到本地
insert?overwrite local?directory '/export/data/exporthive'?select?*?from?score;2）將查詢的結(jié)果格式化導(dǎo)出到本地
insert?overwrite local?directory '/export/data/exporthive'?row?format?delimited fields terminated by?'\t'?collection items terminated by?'#'?select?*?from?student;3）將查詢的結(jié)果導(dǎo)出到HDFS上(沒有l(wèi)ocal)
insert?overwrite directory '/exporthive'?row?format?delimited fields terminated by?'\t'??select?*?from?score;??????????????2、hive shell 命令導(dǎo)出
基本語(yǔ)法：（hive -f/-e 執(zhí)行語(yǔ)句或者腳本 > file）
bin/hive -e "select * from myhive.score;"?>?/export/data/exporthive/score.txt??????????????3、export導(dǎo)出到HDFS上
export table?score to?'/export/exporthive/score';???????4、sqoop導(dǎo)出
由于篇幅有限，在項(xiàng)目實(shí)戰(zhàn)的系列文章詳細(xì)介紹
- 📢博客主頁(yè)：https://lansonli.blog.csdn.net
- 📢歡迎點(diǎn)贊 👍 收藏 ?留言 📝 如有錯(cuò)誤敬請(qǐng)指正！
- 📢本文由 Lansonli 原創(chuàng)，首發(fā)于 CSDN博客🙉
- 📢大數(shù)據(jù)系列文章會(huì)每天更新，停下休息的時(shí)候不要忘了別人還在奔跑，希望大家抓緊時(shí)間學(xué)習(xí)，全力奔赴更美好的生活?
總結(jié)
以上是生活随笔為你收集整理的2021年大数据Hive（三）：手把手教你如何吃透Hive数据库和表操作（学会秒变数仓大佬）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 2021年大数据Hive（二）：Hive
- 下一篇： 2021年大数据Hive（四）：Hive