DDL：
庫操作：
創建庫：
create database 庫名；
create database if not exists 庫名；
查看庫的列表：
show databases;
show databases like 's*'; 查看以s開頭的數據庫；
使用庫:
use database;
查看正在使用的庫：
select current_database(); --是一個函數
查看庫信息：
desc database 庫名；
刪除庫：
drop database 庫名； 只能刪除空庫，裡面沒有表的
drop database if exists 庫名；
drop database 庫名 cascade; 刪除庫 非空庫
drop database 庫名 restrict; 默認情況下的刪除庫的操作
修改庫：基本不用
if not exists
if exists 避免錯誤 同樣適用於表的操作
表的操作：
創建表
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]
1) [EXTERNAL] 關鍵字：是否創建外部表 不加創建的是內部表 加上創建的是外部表
2) [PARTITIONED BY （col_name data_type [COMMENT col_comment],...]
PARTITIONED BY:指定分區字段
注意：分區字段一定不是建表字段中的某一個或多個 是一個全新的字段
3）[CLUSTERED BY (col_name,col_name,...) 指定分桶字段
[SORTED BY (col_name [ASC|DESC],...)] INTO num_buckets BUCKETS] 指定排序字段
注意：分桶字段一定是建表語句中的某一個字段或多個字段
排序規則指定的是在桶一個分桶內的排序規則
INTO num_buckets BUCKETS 指定分桶個數
4)[ROW FORMAT row_format] 指定分隔符
delimited fields terminated by '' 指定列分隔符
lines terminated by '' 指定行分隔符
5）[STORED as file_format] 指定最終表數據的存儲格式
textfile 文本格式 默認的格式（默認）
rcfile 行列結合各式
parquet 壓縮格式
6）[LOCATION hdfs_path] 指定最終表數據的存儲格式
不指定這個默認在我們配置的路徑下存儲 /user/hive/warehouse
指定了則存儲在指定的路徑下
建表
1）內部表
create table if not exists manage_table
(id int
,age int
)
row format delimited fields terminated by ','
;
2)外部表
create external table if not exists external_table
(id int
,age int
,name string
)
row format delimited fields terminated by ','
;
3)分區表
create table if not exists ptn_table
(id int
,age int
)
partitioned by (city string)
row format delimited fields terminated by ','
;
分區表中的數據存儲在不同的目錄下
分區表在添加數據之前先添加分區
alter table pnt_table add if not exists partition(city='beijing');
alter table pnt_table add if not exists partition(city='wuhan');
add partition 添加分區
4）分桶表 ------hadoop中的分區的概念 默認分區按照hash分區
create table if not exists buk_table
(id int
,age int
,name string
)
clustered by (id)
sorted by (age desc)
into 4 buckets
row format delimited fields terminated by ','
;
clustered by (id)：相當於指定map端發出的key id.hashCode()
into 4 buckets: 相當於指定numreducetasks id.hashCode() %4 :對id取hashcode值%4
分桶表的作用：
(1）獲得更高的查詢處理效率。桶為表加上了額外的結構，Hive在處理有些查詢時候利用這個結構。具體而言，連接兩個在（包含連接列的）相同列上劃分了桶的表，可以使用Map端連接（Map join）高效的實現。比如Join操作。對於Join操作兩個表有一個相同的列，如果對這兩個表都進行了桶操作。那麼將保存相同列值得桶進行Join操作就可以，可以大大減少Join的數據量。
(2）使取樣（Sampling）更高效。在處理大規模數據集時，在開發和修改查詢的階段，如果能在數據集的 一小部分數據上試運行查詢，會帶來很多方便。
5）複製表 like關鍵字
create table copy_table01 like external_table;
在複製表的時候複製出來的表到底是內部表還是外部表跟你在複製的時候是否指定了external關鍵字有關
注意：複製表 只能複製表結構並不能複製表數據
6） 創建查詢表
create table as select...from table;
7）查看錶
show tables;
show tables in 庫名； 查看某個庫的表
show tables like 's*'
查看錶信息
desc 表名：顯示錶的字段信息
desc formatted 表名：格式化顯示錶的詳細信息
desc extended 表名：顯示錶的詳細信息
8）刪除表
drop table if exists 表名
清空表
truncate table 表名；
僅僅是清空表中的數據，表結構仍然保留
9）修改表 alter
修改表名字
alter table 表名 rename to 新表名
修改表字段信息
(1）添加字段
alter table 表名 add columns();
例如：alter table copy_table01 add columns(sex string,department string);
所有的新的字段一定要指定類型
(2）修改字段 修改字段類型
alter table 表名 change 原始字段 新字段 新字段類型
例如：alter table copy_table01 change age age01 string
alter table copy_table01 change sex sex int 類型轉換錯誤
注意：在進行修改表字段定義的時候一定要注意表字段類型之間的匹配，只能小類型轉大類型
string類型相對於數值類型屬於大類型
但是hive1.2.2中沒有限制 各種類型之間都可以相互修改
3)替換列 （替換表的所有字段為新的字段） 不常用
alter table copy_table01 replace columns(id int,name string)
修改表分區信息
(1）添加分區
alter table ptn_tbale add if not exists partition (city ='beijing')
添加分區的過程中還可以指定分區的存儲路徑
alter table ptn_tbale add if not exists partition (city ='shenzhen')
lacation '/user/shenzhen'
添加分區的時候指定路徑則分區存儲在指定路徑下，沒有指定則存在默認路徑下
(2）修改分區 修改分區的存儲路徑
alter table ptn_tbale set partition(city='shenzhen') lacation '/user/shenzhen'
(3）刪除分區
alter table ptn_tbale drop if exists partition (city ='beijing')
10）其他輔助命令
查看建表語句：
show create table 表名；
查看hive函數列表
show functions ；
查看錶的分區信息 一定針對分區表
show partitions table_name;
show partitions table_name partition(city='beijing');
DML
1.數據加載：load
語法：
load database
hdfs: inpath
本地： local inpath
'/home/hadoop' into table 表名
1）從本地加載數據
load data local inpath '/home/hadoop/student.txt' into table student_base ;
從本地加載數據相當於複製的工作 將本地的文件複製到hive的默認路徑下
2）從hdfs加載
hive 原始的數據就存在hdfs上 如果在建一個內部表 複製？移動？
load data inpath '/student.txt' into table student_base ;
同在hdfs上的數據有必要存儲兩份嗎？沒有 所以這個操作是移動的操作
一般情況下像這種情況應該建內部表還是外部表？
如果是內部表？刪除數據的時候數據和元數據會一併刪除
如果是外部表刪除數據的時候元數據會被刪除，而原始數據能暴力流
這種情況（hdfs上原始存儲的數據（公共數據）建外部表
數據加載成功之後，發現在/user/myhive/bd1803/bd1803.db/student_base目錄下有兩個文件
第一次加載從本地加載的 文件student.txt
第一次加載的時候從hdfs加載的 文件student.txt
這兩個文件在加載的時候都會被加載到hive的指定目錄下
由於在hdfs上相同的目錄下不能有兩個同名的文件，這時候會將第二次上傳的數據重命名為student_copy01.txt
2,insert 方式：
1）單條數據插入：
insert into table student_base values(1,'zs','f',24,'CS');
會先建立一個臨時表，然後將臨時表中的數據拷貝到指定的表中
效率比較低，會出現很多小文件
2）多條數據插入:可以從一個表中查詢 將查詢結果全部插入到指定的表中 單重插入
insert into table student_base select * from stu where age>20;
一次只能插入到一個表中
3）多重插入：可以一次插入多個表 但是對基表值掃描一次 優點：減少基表的掃描次數,提升插入性能
from 表名 --要掃描的表
insert into table 表1 select ...
insert into tbale 表2 select ...
例如: stu01:大於等於24歲
stu02:小於24歲
from stu
insert into table stu01 select * where age>=24
insert into table stu02 select * where age<24;
分區表的數據插入：
create table if not exists stu_ptn(id int,name string,sex string,department string)
partitioned by (age int)
row format delimited fields terminated by ','
;
這種方式加載數據的時候一定先添加分區
alter table stu_ptn add partition (age=18);
1)load
向分區表中加載數據一定指定加載到哪個分區中
load data local inpath '/home/hadoop/student.txt' into table stu_ptn partition(age=18);
一般情況下分區表的數據加載不使用load方式， load方式不會進行數據監測
2）insert方式
insert into table stu_ptn partition(age=18) values(,,,,,,) 一般也不建議
多條數據插入
insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=18;
正常情況下分區肯定有多個按年齡進行分區
數據加載時候這種方式可以嗎？
分區比較少的時候：
alter table stu_ptn add partition (age=18);
alter table stu_ptn add partition (age=19);
alter table stu_ptn add partition (age=20);
from stu
insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=18;
insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=19;
insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=20;
靜態分區：在向分區表中添加數據之前手動的添加一個指定分區
分區個數不確定 添加分區的時候怎麼添加？上面的方式不使用 要使用動態分區
動態分區：可以根據你的數據自動進行分區 不需要提前添加分區
create table if not exists stu_ptn01(id int,name string,sex string,department string)
partitioned by (age int)
row format delimited fields terminated by ','
;
insert into table stu_ptn partition(age) select id,name,age,sex,department from stu; 錯誤
insert into table stu_ptn partition(age) select id,name,sex,department，age from stu; 正確
動態分區在查詢插入的時候動態分區字段必須放在最後一個字段，否則會將最後一個字段默認為你的分區字段
動態分區需要設置：
set hive.exec.dynamic.partitoin.mode=nostrict
查詢屬性值：
set hive.exec.dynamic.partitoin.mode;
hive.exec.dynamic.partitoin.mode=nostrict
hive.exec.dynamic.partitoin.mode=strict 嚴格模式 必須手動添加一個靜態分區
set hive.exec.dynamic.partitoin.mode=nostrict
hive1.2版本中以下需要設置
set hive.exec.dynamic.partitoin=true； --開啟動態分區
set hive.exec.dynamic.partitoin.mode=nostrict；
多個分區字段的時候：
create table if not exists stu_ptn02(id int,name string,sex string)
partitioned by (department string,age int)
row format delimited fields terminated by ','
;
insert into table stu_ptn02 partition(department,age)
select id,name,sex,department,age from stu;
多字段分區的時候目錄結構按照分區字段順序進行劃分的
hive1.2中在進行動態分區的時候如果是多字段分區必須手動指定第一級的分區
分區表使用的時候分區字段可以看做一個普通字段
select * from stu_ptn02 where age=18;
只是在建表和添加數據的時候不一樣
分桶表的數據插入
create table if not exists stu_bak(id int,name string,sex string,age int,departmrnt string)
clustered by (age) sorted by (sex) into 3 buckets
row format delimited fields terminated by ',';
1)load方式
load data local inpath '/home/hadoop/student.txt' into table stu_bak
load方式不能向分通表中添加數據
2）insert 方式
insert into table stu_bak select * from stu;
好了，我要接著專心的去做需求，改bug了！！！
閱讀更多 大數據之路 的文章
