一Hive 庫(kù)的基本操作

1.1 建庫(kù)

1.默認(rèn)路徑是/user/hive/warehouse

例如 我輸入命令 create database text1

則text1出現(xiàn)在?warehouse目錄下

?2.指定位置創(chuàng)建數(shù)據(jù)庫(kù)

create database text2 location '/bigdata29/bigdata29db'

后面的路徑是hdfs的路徑
?

?3.最終寫法

加上if not exists 可以判斷該數(shù)據(jù)庫(kù)存不存在

create database if not exists bigdata29_test1 location '/bigdata29/huangdadadb';

1.2 修改數(shù)據(jù)庫(kù)

1.一般創(chuàng)建好的數(shù)據(jù)庫(kù)都不會(huì)去修改數(shù)據(jù)庫(kù)，如果要修改數(shù)據(jù)庫(kù)也是修改創(chuàng)建的時(shí)間

alter database dept set dbproperties('createtime'='20220531');

1.3 數(shù)據(jù)庫(kù)詳細(xì)信息

1.3.1 顯示數(shù)據(jù)庫(kù)

1.show databases;

1.3.2?可以通過(guò)like進(jìn)行過(guò)濾

show databases like 't*';

1.3.3 查看詳情

desc database 數(shù)據(jù)庫(kù)名;

1.3.4 切換數(shù)據(jù)庫(kù)

use 數(shù)據(jù)庫(kù)名;

1.3.5 刪除數(shù)據(jù)庫(kù)

drop database if exists 數(shù)據(jù)庫(kù)名;

如果數(shù)據(jù)庫(kù)不為空，使用cascade命令進(jìn)行強(qiáng)制刪除。報(bào)錯(cuò)信息如下FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)

drop database if exists 數(shù)據(jù)庫(kù)名?cascade;

二 Hive的數(shù)據(jù)類型

2.1 基本數(shù)據(jù)類型

?2.2 復(fù)雜數(shù)據(jù)類型

?三 Hive 表的基本操作

3.1 默認(rèn)建表

1.簡(jiǎn)單數(shù)據(jù)

create table IF NOT EXISTS 表名
(

數(shù)據(jù)名 數(shù)據(jù)類型
? ? id bigint,??
? ? name string,
? ? age int,
? ? gender string,
? ? clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';? --> 以逗號(hào)做為分隔符

2.復(fù)雜數(shù)據(jù)

create table IF NOT EXISTS t_person(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string ,city:string>
)
row format delimited fields terminated by ',' -- 列與列之間的分隔符
collection items terminated by '_' -- 元素與元素之間分隔符
map keys terminated by ':' -- Map數(shù)據(jù)類型鍵與值之間的分隔符
lines terminated by '\n'; ?-- 行與行之間的換行符

3.2 指定存儲(chǔ)格式

3.2.1 相關(guān)文件格式

平時(shí)學(xué)習(xí)用TextFile，工作使用orc

3.2.2建表語(yǔ)法

create table IF NOT EXISTS 表名
(
? ? id bigint,
? ? name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORC -->存儲(chǔ)文件的格式

3.3 查詢結(jié)果作為表

1.表沒有的情況下

create table 表名 as 查詢語(yǔ)句；

2.有表的情況下 添加數(shù)據(jù)

insert into 表名 查詢語(yǔ)句；

?3.4 模仿其他表的結(jié)構(gòu)

create table 表名 like 已經(jīng)存在的表名；

3.5 顯示表

show tables;
show tables like 'u*';
desc t_person;
desc formatted students; // 更加詳細(xì)

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

3.6.1 使用hdfs的put或者cp命令

1.將linux文件使用 put的命令放在?hive表對(duì)應(yīng)的HDFS目錄下

例如：

hadoop fs -put ./students.csv /bigdata29/bigdata29db/students/

?2.如果hdfs中 有數(shù)據(jù)的文件，那么將這個(gè)文件復(fù)制一份到 hive表對(duì)應(yīng)的hdfs目錄下

例如：

hadoop fs -cp /bigdata29/students.csv?/bigdata29/bigdata29db/students/

3.6.2 location 或者是load data

1.location 這個(gè)是創(chuàng)建表的時(shí)候后面加的數(shù)據(jù)。(先有數(shù)據(jù)后有表)

例如?create table IF NOT EXISTS students3
(
? ? id bigint,
? ? name string,
? ? age int,
? ? gender string,
? ? clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata29/shuju/';

主要保證data目錄下只能有一個(gè)數(shù)據(jù)?

這個(gè)表存儲(chǔ)在shuju目錄下

?2.location 這個(gè)是先創(chuàng)建表的時(shí)候后面添加數(shù)據(jù)。(先有表后有數(shù)據(jù))

create table IF NOT EXISTS students4(id bigint,name string,age int,gender string,clazz string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata29/aaa/bbb/ccc';

先在/bigdata29/aaa/bbb/ccc這個(gè)目錄下創(chuàng)建一個(gè)表 再將數(shù)據(jù)放到這個(gè)目錄下

?3.load data 這個(gè)是移動(dòng)hdfs中文件

先創(chuàng)建一個(gè)新表 不加location的話默認(rèn)在創(chuàng)建數(shù)據(jù)庫(kù)的那個(gè)目錄下

create table IF NOT EXISTS students5
(
? ? id bigint,
? ? name string,
? ? age int,
? ? gender string,
? ? clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

再輸入?load data inpath '/bigdata29/data/students.csv' into table students5;即可

但是bigdata29/data/students.csv文件沒有了

4.load data local 是上傳Linux的文件

create table IF NOT EXISTS students6
(
? ? id bigint,
? ? name string,
? ? age int,
? ? gender string,
? ? clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

再輸入 load data local inpath '/usr/local/soft/bigdata29/students.csv'? into table students5；

這里的文件不會(huì)消失

into 改成 overwrite就是覆蓋

3.6.2 查詢語(yǔ)句加載數(shù)據(jù)

1.表沒有的情況下

create table 表名 as 查詢語(yǔ)句；

2.有表的情況下 添加數(shù)據(jù)

insert into 表名 查詢語(yǔ)句；

3.6.3普通的插入

insert into 表名 values ('數(shù)據(jù)');

3.7 修改表

3.7.1 添加列

alter table 表名 add columns (數(shù)據(jù)名 數(shù)據(jù)類型);

3.7.2修改列名或者數(shù)據(jù)類型

alter table 表名 change 之前的數(shù)據(jù)名 更改過(guò)后的數(shù)據(jù)名?數(shù)據(jù)類型;

四 內(nèi)外部表

4.1內(nèi)部表

1.我們默認(rèn)創(chuàng)建的就是內(nèi)部表

2.當(dāng)設(shè)置表路徑的時(shí)候，如果直接指向一個(gè)已有的路徑,可以直接去使用文件夾中的數(shù)據(jù)

當(dāng)load數(shù)據(jù)的時(shí)候，就會(huì)將數(shù)據(jù)文件存放到表對(duì)應(yīng)的文件夾中

而且數(shù)據(jù)一旦被load，就不能被修改

我們查詢數(shù)據(jù)也是查詢文件中的文件,這些數(shù)據(jù)最終都會(huì)存放到HDFS

當(dāng)我們刪除表的時(shí)候，表對(duì)應(yīng)的文件夾會(huì)被刪除，同時(shí)數(shù)據(jù)也會(huì)被刪除

4.2外部表

1.創(chuàng)建表的時(shí)候加個(gè)關(guān)鍵字

create external table 表名

2.一般用的外部表比較多

3.外部表因?yàn)槭侵付ㄆ渌膆dfs路徑的數(shù)據(jù)加載到表中來(lái)，所以hive會(huì)認(rèn)為自己不完全獨(dú)占這份數(shù)據(jù)

4.刪除hive表的時(shí)候，數(shù)據(jù)仍然保存在hdfs中，不會(huì)刪除。

五 導(dǎo)出數(shù)據(jù)

5.1 放入Linux中

1.insert overwrite local directory 'Linux路徑' 查詢語(yǔ)句;

例如：?

insert overwrite local directory '/usr/local/soft/bigdata29/person_data' select * from t_person;

2.按照指定的方式將數(shù)據(jù)輸出到本地

insert overwrite local directory '/usr/local/soft/shujia/person'?
ROW FORMAT DELIMITED fields terminated by ','?
collection items terminated by '-'?
map keys terminated by ':'?
lines terminated by '\n'?
select * from t_person;

5.2? 放入hdfs中

1.insert overwrite directory 'hdfs路徑' 查詢語(yǔ)句;

2.export table t_person to 'hdfs路徑';，但是這個(gè)路徑提前存在

六 Hive的分區(qū)

6.1靜態(tài)分區(qū) SP

借助于物理的文件夾分區(qū)，實(shí)現(xiàn)快速檢索的目的。

一般對(duì)于查詢比較頻繁的列設(shè)置為分區(qū)列。

分區(qū)查詢的時(shí)候直接把對(duì)應(yīng)分區(qū)中所有數(shù)據(jù)放到對(duì)應(yīng)的文件夾中。

6.1.1 單分區(qū)

1.創(chuàng)建表語(yǔ)法：

CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int)
row format delimited fields terminated by ',';

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

先數(shù)據(jù)在Linux創(chuàng)建文件，再上傳到hdfs的表目錄下

load data local inpath '/usr/local/soft/bigdata29/grade2.txt' into table t_student partition(grade=2);

3.加載數(shù)據(jù)的時(shí)候一定要把分區(qū)要確定好，不能不是這個(gè)分區(qū)的內(nèi)容加入到這個(gè)分區(qū)里面

6.1.2 多分區(qū)

1.創(chuàng)建表語(yǔ)法

CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int)
row format delimited fields terminated by ',';

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

在Linux創(chuàng)建數(shù)據(jù)文件，再上傳到hdfs的表目錄下

load data local inpath '/usr/local/soft/bigdata29/grade22.txt' into table t_teacher partition(grade=2,clazz=2);

3.分區(qū)的字段不能少于2個(gè)，比如說(shuō)男生女生就沒必要分區(qū)了

6.1.3 查看分區(qū)

show partitions 表名r;

6.1.4 添加分區(qū)

?alter table 表名 add partition (字段名);

alter table 表名 add partition (字段名) location '指定數(shù)據(jù)文件的路徑';

例如：

alter table t_teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/bigdata29.db/t_teacher/grade=3/clazz=1';

6.1.5 刪除分區(qū)

alter table 表名 drop partition (字段名);

6.2 動(dòng)態(tài)分區(qū)

• 動(dòng)態(tài)分區(qū)（DP）dynamic partition

• 靜態(tài)分區(qū)與動(dòng)態(tài)分區(qū)的主要區(qū)別在于靜態(tài)分區(qū)是手動(dòng)指定，而動(dòng)態(tài)分區(qū)是通過(guò)數(shù)據(jù)來(lái)進(jìn)行判斷。

• 詳細(xì)來(lái)說(shuō)，靜態(tài)分區(qū)的列是在編譯時(shí)期通過(guò)用戶傳遞來(lái)決定的；動(dòng)態(tài)分區(qū)只有在SQL執(zhí)行時(shí)才能決定。

6.2.1 開啟動(dòng)態(tài)分區(qū)

1.先創(chuàng)建分區(qū)表

CREATE TABLE IF NOT EXISTS t_student_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';

2.創(chuàng)建原始數(shù)據(jù)表(外部)

CREATE EXTERNAL TABLE IF NOT EXISTS t_student_e (
sno int,
sname string,
grade int,
clazz int
)?
row format delimited fields terminated by ','
location "/bigdata29/teachers";

3.在本地創(chuàng)建文件 然后將數(shù)據(jù)導(dǎo)入t_student_e表中

4.insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;

6.3 分區(qū)的優(yōu)缺點(diǎn)

1.優(yōu)點(diǎn)：

避免全盤掃描，加快查詢速度

2.缺點(diǎn)

可能產(chǎn)生大量小文件

數(shù)據(jù)傾斜問題

七 Hive分桶

7.1 概念

數(shù)據(jù)分桶的適用場(chǎng)景： 分區(qū)提供了一個(gè)隔離數(shù)據(jù)和優(yōu)化查詢的便利方式，不過(guò)并非所有的數(shù)據(jù)都可形成合理的分區(qū)，尤其是需要確定合適大小的分區(qū)劃分方式 不合理的數(shù)據(jù)分區(qū)劃分方式可能導(dǎo)致有的分區(qū)數(shù)據(jù)過(guò)多，而某些分區(qū)沒有什么數(shù)據(jù)的尷尬情況 分桶是將數(shù)據(jù)集分解為更容易管理的若干部分的另一種技術(shù)。 分桶就是將數(shù)據(jù)按照字段進(jìn)行劃分，可以將數(shù)據(jù)按照字段劃分到多個(gè)文件當(dāng)中去。(都各不相同)

7.2 原理

Hive采用對(duì)列值哈希，然后除以桶的個(gè)數(shù)求余的方式?jīng)Q定該條記錄存放在哪個(gè)桶當(dāng)中。

• bucket num = hash_function(bucketing_column) mod num_buckets

• 列的值做哈希取余 決定數(shù)據(jù)應(yīng)該存儲(chǔ)到哪個(gè)桶

7.3 作用

方便抽樣

使取樣（sampling）更高效。在處理大規(guī)模數(shù)據(jù)集時(shí)，在開發(fā)和修改查詢的階段，如果能在數(shù)據(jù)集的一小部分?jǐn)?shù)據(jù)上試運(yùn)行查詢，會(huì)帶來(lái)很多方便

提高join查詢效率

獲得更高的查詢處理效率。桶為表加上了額外的結(jié)構(gòu)，Hive 在處理有些查詢時(shí)能利用這個(gè)結(jié)構(gòu)。具體而言，連接兩個(gè)在（包含連接列的）相同列上劃分了桶的表，可以使用 Map 端連接 （Map-side join）高效的實(shí)現(xiàn)。比如JOIN操作。對(duì)于JOIN操作兩個(gè)表有一個(gè)相同的列，如果對(duì)這兩個(gè)表都進(jìn)行了桶操作。那么將保存相同列值的桶進(jìn)行JOIN操作就可以，可以大大較少JOIN的數(shù)據(jù)量。

7.4 實(shí)例

1.先創(chuàng)建數(shù)據(jù)

2.創(chuàng)建普通的表

create table person
(
id int,
name string,
age int
)
row format delimited
fields terminated by ',';

3.加載數(shù)據(jù)到普通的表中

4.創(chuàng)建分桶表

create table psn_bucket
(
id int,
name string,
age int
)
clustered by(age) into 4 buckets
row format delimited fields terminated by ',';

這里使用clustered by對(duì)表中的列名分桶，后面的4表示分成4個(gè)

5.將數(shù)據(jù)加載到分桶表中

insert into psn_bucket select * from person;

后面是普通表的查詢語(yǔ)句

7.5 分桶于分區(qū)的區(qū)別

1.分區(qū)用的是partitioned by 關(guān)鍵字聲明的，分桶是clustered by 和into?buckets 分成幾個(gè)桶的關(guān)鍵字聲明的

2.分區(qū)的在hdfs上面表示的是一個(gè)文件夾，分桶表示的是一個(gè)文件

3.分區(qū)的依據(jù)是通過(guò)指定分區(qū)的字段的值來(lái)進(jìn)行分區(qū)的，分桶是指定分桶的字段的哈希值除以桶的個(gè)數(shù)取余來(lái)進(jìn)行分桶的

八 Idea連接Hive

package com.shujia.jcbc;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class JcbcHive {public static void main(String[] args) throws Exception {//加載驅(qū)動(dòng)Class.forName("org.apache.hive.jdbc.HiveDriver");//創(chuàng)建與hive的連接對(duì)象Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/bigdata29");//創(chuàng)建操作hive的對(duì)象Statement state = conn.createStatement();ResultSet resultSet = state.executeQuery("select * from emp ");while (resultSet.next()){String empno = resultSet.getString(1);String hiredate = resultSet.getString(2);String sal = resultSet.getString(3);String deptno = resultSet.getString(4);System.out.println(empno+", "+hiredate+", "+sal+", "+deptno);}//釋放資源conn.close();}
}