總目錄:http://www.reibang.com/p/e406a9bc93a9
Hadoop - 子目錄：http://www.reibang.com/p/9428e443b7fd
什么是DDL
DDL--數(shù)據(jù)定義語言，用來定義存儲(chǔ)數(shù)據(jù)的概念，例如庫，表等德玫。
數(shù)據(jù)庫的增刪改查
- 增
create database 數(shù)據(jù)庫名;
增加
hdfs
如果要在建庫時(shí)判斷是否重名就要加if not exists
同時(shí)也可以指定庫存在的位置：
create database 庫名 location '絕對(duì)路徑';
-
查
show databases;
示例
還有模糊查詢show databases like 'hive*';
示例
查詢數(shù)據(jù)庫信息
desc databases 庫名;
示例
查詢數(shù)據(jù)庫詳細(xì)信息
desc databases extended 庫名;
注：因?yàn)橐粋€(gè)庫只有這些信息前翎，所以詳細(xì)信息和普通信息差不多豁护，在表中可以看出來差別。 -
改
這里的改指的是添加數(shù)據(jù)庫的信息（已有的信息不能修改）
alter database 庫名 set dbproperties(key=value);
修改完后姜性，要使用查詢?cè)敿?xì)信息查詢冬筒。
示例 -
刪
drop database hive_1;
示例
可以使用if exists關(guān)鍵字判斷這個(gè)庫是否存在恐锣。
drop database if exists hive_1;
不存在也不報(bào)錯(cuò)
示例
如果數(shù)據(jù)庫不為空茅主，就需要使用cascade命令舞痰。
建個(gè)表
兩次刪除比較
建表語句
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）CREATE TABLE 創(chuàng)建一個(gè)指定名字的表。如果相同名字的表已經(jīng)存在诀姚，則拋出
異常响牛；用戶可以用 IF NOT EXISTS 選項(xiàng)來忽略這個(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ù)倉庫指向的路
徑糯笙；若創(chuàng)建外部表贬丛，僅記錄數(shù)據(jù)所在的路徑，不對(duì)數(shù)據(jù)的位置做任何改變给涕。在刪除表的
時(shí)候豺憔，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會(huì)被一起刪除，而外部表只刪除元數(shù)據(jù)够庙，不刪除數(shù)據(jù)恭应。
（3）COMMENT：為表和列添加注釋。
（4）PARTITIONED BY 創(chuàng)建分區(qū)表
（5）CLUSTERED BY 創(chuàng)建分桶表 （6）SORTED BY 不常用
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]
用戶在建表的時(shí)候可以自定義 SerDe 或者使用自帶的 SerDe耘眨。如果沒有指定 ROW
FORMAT 或者 ROW FORMAT DELIMITED昼榛，將會(huì)使用自帶的 SerDe。在建表的時(shí)候剔难，用戶
還需要為表指定列胆屿，用戶在指定表的列的同時(shí)也會(huì)指定自定義的 SerDe奥喻，Hive 通過 SerDe
確定表的具體的列的數(shù)據(jù)。
SerDe 是 Serialize/Deserilize 的簡稱莺掠，目的是用于序列化和反序列化衫嵌。
（8）STORED AS 指定存儲(chǔ)文件類型
常用的存儲(chǔ)文件類型：SEQUENCEFILE（二進(jìn)制序列文件）、TEXTFILE（文本）彻秆、RCFILE（列式存儲(chǔ)格式文件）
如果文件數(shù)據(jù)是純文本楔绞，可以使用 STORED AS TEXTFILE。如果數(shù)據(jù)需要壓縮唇兑，使用 STORED AS SEQUENCEFILE酒朵。
（9）LOCATION ：指定表在 HDFS 上的存儲(chǔ)位置。
（10）LIKE 允許用戶復(fù)制現(xiàn)有的表結(jié)構(gòu)扎附，但是不復(fù)制數(shù)據(jù)蔫耽。
內(nèi)外部表
-
什么是內(nèi)部表
管理表，是指hive能夠掌握著數(shù)據(jù)生產(chǎn)周期的表留夜，hive會(huì)將其存儲(chǔ)在自己的存儲(chǔ)目錄hive.metastore.warehouse.dir下匙铡。hive刪除這個(gè)表時(shí)，會(huì)將數(shù)據(jù)一起刪除碍粥，所以管理表不適合與其他工具共享數(shù)據(jù)鳖眼。
hdfs
stu表中有兩個(gè)數(shù)據(jù)塊，現(xiàn)在我們把stu表刪除嚼摩。
drop table 表名;
刪除
hdfs
hdfs上的stu目錄就直接被刪掉了钦讳，相對(duì)應(yīng)的數(shù)據(jù)也沒了。 -
外部表
外部表就可以理解成是簡單外鏈上的數(shù)據(jù)枕面，在hive上刪除外部表只會(huì)刪除該表的元數(shù)據(jù)不會(huì)影響原數(shù)據(jù)愿卒。
創(chuàng)建外部表
create external table if not exists 表名(...)...;
創(chuàng)建部門表
創(chuàng)建員工表
然后導(dǎo)入數(shù)據(jù)
數(shù)據(jù)源可以去我的公眾號(hào)hx_bigdata回復(fù)1435獲得。
導(dǎo)入數(shù)據(jù)源
查看表類型
desc formatted 表名;
示例 -
類型轉(zhuǎn)換
將外部表轉(zhuǎn)換為內(nèi)部表
alter table 表名 set tblproperties('EXTERNAL'='FALSE');
示例
將內(nèi)部表轉(zhuǎn)換為外部表
alter table 表名 set tblproperties('EXTERNAL'='TRUE');
('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')為固定寫法潮秘，必須大寫琼开。
- 使用場景舉例
每天將收集到的網(wǎng)站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基礎(chǔ)上做大量的統(tǒng)計(jì)分析枕荞，用到的中間表柜候、結(jié)果表使用內(nèi)部表存儲(chǔ)，數(shù)據(jù)通過 SELECT+INSERT進(jìn)入內(nèi)部表买猖。
分區(qū)表
分區(qū)表實(shí)際上就是對(duì)應(yīng)一個(gè) HDFS 文件系統(tǒng)上的獨(dú)立的文件夾改橘，該文件夾下是該分區(qū)所有的數(shù)據(jù)文件。
Hive 中的分區(qū)就是分目錄玉控，把一個(gè)大的數(shù)據(jù)集根據(jù)業(yè)務(wù)需要分割成小的數(shù)據(jù)集飞主。
在查詢時(shí)通過 WHERE 子句中的表達(dá)式選擇查詢所需要的指定的分區(qū)，這樣的查詢效率會(huì)提高很多。
分區(qū)表的簡單實(shí)用
創(chuàng)建一個(gè)分區(qū)表
指定分區(qū)字段碌识，然后導(dǎo)入數(shù)據(jù)時(shí)按照分區(qū)字段來分碾篡。
查看表數(shù)據(jù)。
查看hdfs上的數(shù)據(jù)筏餐。
類型是d开泽，表示他們是目錄。而三個(gè)目錄的名字是他們各自的分區(qū)魁瞪。
在數(shù)據(jù)庫中的元數(shù)據(jù)穆律。
數(shù)據(jù)庫中存放了三條記錄，他們對(duì)應(yīng)的表ID相同导俘，但是分區(qū)ID不同峦耘。
分區(qū)表的基礎(chǔ)操作
-
增加分區(qū)
分區(qū)不止可以通過導(dǎo)入數(shù)據(jù)創(chuàng)建，還可以使用語句來創(chuàng)建旅薄。
alter table 表名 add partition(分區(qū)字段=分區(qū)名) ;
示例
hdfs
注：可以同時(shí)創(chuàng)建多個(gè)分區(qū)：
alter table 表名 add partition(分區(qū)字段=分區(qū)名1) partition(分區(qū)字段=分區(qū)名2) ; -
刪除分區(qū)
alter table 表名 drop partition(分區(qū)字段=分區(qū)名) ;
示例
hdfs
注：可以同時(shí)刪除多個(gè)分區(qū)
alter table 表名 drop partition(分區(qū)字段=分區(qū)名1) partition(分區(qū)字段=分區(qū)名2) ; -
查看分區(qū)數(shù)量
show partitions 表名;
分區(qū)數(shù)量
- 查看表結(jié)構(gòu)
desc formatted 表名;
表結(jié)構(gòu)
分區(qū)表的注意事項(xiàng)
-
創(chuàng)建二級(jí)分區(qū)
不止可以創(chuàng)建二級(jí)分區(qū)辅髓，也可以創(chuàng)建三級(jí)，四級(jí)等等少梁。
create table 表名(字段名1洛口，字段類型1...) partitioned by (一級(jí)分區(qū) 一級(jí)分區(qū)類型，二級(jí)分區(qū) 二級(jí)分區(qū)類型...) row format delimited fields terminated by '\t';
示例 -
導(dǎo)入數(shù)據(jù)到二級(jí)分區(qū)
load data local inpath '/usr/hive_data/dept.txt' into table default.dept_partition2 partition(month="202004",day="05");
示例
hdfs
在一級(jí)分區(qū)下凯沪，有一個(gè)二級(jí)分區(qū)目錄第焰。
- 讓分區(qū)表與數(shù)據(jù)產(chǎn)生關(guān)聯(lián)
方式一：上傳數(shù)據(jù)后修復(fù)
上傳數(shù)據(jù)
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202004/day=03;
hive (default)> dfs -put /usr/hive_data/dept.txt /user/hive/warehouse/dept_partition2/month=202004/day=03;
查詢數(shù)據(jù)（查詢不到剛上傳的數(shù)據(jù)）
hive (default)> select * from dept_partition2 where month='202004' and day='03';
執(zhí)行修復(fù)命令
hive (default)> msck repair table dept_partition2;
再次查詢數(shù)據(jù)
hive (default)> select * from dept_partition2 where month='202004' and day='03';
方式二：上傳數(shù)據(jù)后添加分區(qū)
上傳數(shù)據(jù)
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202004/day=02;
hive (default)> dfs -put /usr/hive_data/dept.txt /user/hive/warehouse/dept_partition2/month=202004/day=02;
執(zhí)行添加分區(qū)
hive (default)> alter table dept_partition2 add partition(month='202004',day='02');
查詢數(shù)據(jù)
hive (default)> select * from dept_partition2 where month='202004' and day='02';
方式三：創(chuàng)建文件夾后 load 數(shù)據(jù)到分區(qū)
創(chuàng)建目錄
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=202004/day=01;
上傳數(shù)據(jù)
hive (default)> load data local inpath '/usr/hive_data/dept.txt' into table dept_partition2 partition(month='202004',day='01');
查詢數(shù)據(jù)
hive (default)> select * from dept_partition2 where month='202004' and day='01';
表的修改
重命名表
alter table 舊表名 rename to 新表名
修改表結(jié)構(gòu)
-
增加一列
alter table 表名 add columns(新增字段名 字段類型);
例子 -
修改一列類型
alter table 表名 change column 字段名 desc 修改后的字段類型;
例子
表的刪除
drop table 表名;
