一Hive 庫的基本操作

1.1 建庫

1.默認路徑是/user/hive/warehouse

例如 我輸入命令 create database text1

則text1出現在?warehouse目錄下

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

create database text2 location '/bigdata29/bigdata29db'

后面的路徑是hdfs的路徑
?

?3.最終寫法

加上if not exists 可以判斷該數據庫存不存在

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

1.2 修改數據庫

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

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

1.3 數據庫詳細信息

1.3.1 顯示數據庫

1.show databases;

1.3.2?可以通過like進行過濾

show databases like 't*';

1.3.3 查看詳情

desc database 數據庫名;

1.3.4 切換數據庫

use 數據庫名;

1.3.5 刪除數據庫

drop database if exists 數據庫名;

如果數據庫不為空，使用cascade命令進行強制刪除。報錯信息如下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 數據庫名?cascade;

二 Hive的數據類型

2.1 基本數據類型

?2.2 復雜數據類型

?三 Hive 表的基本操作

3.1 默認建表

1.簡單數據

create table IF NOT EXISTS 表名
(

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

2.復雜數據

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數據類型鍵與值之間的分隔符
lines terminated by '\n'; ?-- 行與行之間的換行符

3.2 指定存儲格式

3.2.1 相關文件格式

平時學習用TextFile，工作使用orc

3.2.2建表語法

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

3.3 查詢結果作為表

1.表沒有的情況下

create table 表名 as 查詢語句；

2.有表的情況下 添加數據

insert into 表名 查詢語句；

?3.4 模仿其他表的結構

create table 表名 like 已經存在的表名；

3.5 顯示表

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

3.6 加載數據

3.6.1 使用hdfs的put或者cp命令

1.將linux文件使用 put的命令放在?hive表對應的HDFS目錄下

例如：

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

?2.如果hdfs中 有數據的文件，那么將這個文件復制一份到 hive表對應的hdfs目錄下

例如：

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

3.6.2 location 或者是load data

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

例如?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目錄下只能有一個數據?

這個表存儲在shuju目錄下

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

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這個目錄下創(chuàng)建一個表 再將數據放到這個目錄下

?3.load data 這個是移動hdfs中文件

先創(chuàng)建一個新表 不加location的話默認在創(chuàng)建數據庫的那個目錄下

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；

這里的文件不會消失

into 改成 overwrite就是覆蓋

3.6.2 查詢語句加載數據

1.表沒有的情況下

create table 表名 as 查詢語句；

2.有表的情況下 添加數據

insert into 表名 查詢語句；

3.6.3普通的插入

insert into 表名 values ('數據');

3.7 修改表

3.7.1 添加列

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

3.7.2修改列名或者數據類型

alter table 表名 change 之前的數據名 更改過后的數據名?數據類型;

四 內外部表

4.1內部表

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

2.當設置表路徑的時候，如果直接指向一個已有的路徑,可以直接去使用文件夾中的數據

當load數據的時候，就會將數據文件存放到表對應的文件夾中

而且數據一旦被load，就不能被修改

我們查詢數據也是查詢文件中的文件,這些數據最終都會存放到HDFS

當我們刪除表的時候，表對應的文件夾會被刪除，同時數據也會被刪除

4.2外部表

1.創(chuàng)建表的時候加個關鍵字

create external table 表名

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

3.外部表因為是指定其他的hdfs路徑的數據加載到表中來，所以hive會認為自己不完全獨占這份數據

4.刪除hive表的時候，數據仍然保存在hdfs中，不會刪除。

五 導出數據

5.1 放入Linux中

1.insert overwrite local directory 'Linux路徑' 查詢語句;

例如：?

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

2.按照指定的方式將數據輸出到本地

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路徑' 查詢語句;

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

六 Hive的分區(qū)

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

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

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

分區(qū)查詢的時候直接把對應分區(qū)中所有數據放到對應的文件夾中。

6.1.1 單分區(qū)

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

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

2.加載數據

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

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

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

6.1.2 多分區(qū)

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

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

2.加載數據

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

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

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

6.1.3 查看分區(qū)

show partitions 表名r;

6.1.4 添加分區(qū)

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

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

例如：

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 動態(tài)分區(qū)

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

• 靜態(tài)分區(qū)與動態(tài)分區(qū)的主要區(qū)別在于靜態(tài)分區(qū)是手動指定，而動態(tài)分區(qū)是通過數據來進行判斷。

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

6.2.1 開啟動態(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)建原始數據表(外部)

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)建文件 然后將數據導入t_student_e表中

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

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

1.優(yōu)點：

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

2.缺點

可能產生大量小文件

數據傾斜問題

七 Hive分桶

7.1 概念

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

7.2 原理

Hive采用對列值哈希，然后除以桶的個數求余的方式決定該條記錄存放在哪個桶當中。

• bucket num = hash_function(bucketing_column) mod num_buckets

• 列的值做哈希取余 決定數據應該存儲到哪個桶

7.3 作用

方便抽樣

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

提高join查詢效率

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

7.4 實例

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

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

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

3.加載數據到普通的表中

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對表中的列名分桶，后面的4表示分成4個

5.將數據加載到分桶表中

insert into psn_bucket select * from person;

后面是普通表的查詢語句

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

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

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

3.分區(qū)的依據是通過指定分區(qū)的字段的值來進行分區(qū)的，分桶是指定分桶的字段的哈希值除以桶的個數取余來進行分桶的

八 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 {//加載驅動Class.forName("org.apache.hive.jdbc.HiveDriver");//創(chuàng)建與hive的連接對象Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/bigdata29");//創(chuàng)建操作hive的對象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();}
}