Hive DDL數(shù)據(jù)定義

目錄
一.DDL數(shù)據(jù)定義
????1.1 創(chuàng)建數(shù)據(jù)庫
????1.2 修改數(shù)據(jù)庫
????1.3 查詢數(shù)據(jù)庫
????1.4 刪除數(shù)據(jù)庫
????1.5 創(chuàng)建表
????1.6 分區(qū)表
????1.7 修改表
????1.8 刪除表

一.DDL數(shù)據(jù)定義

1.1 創(chuàng)建數(shù)據(jù)庫

(1)創(chuàng)建一個數(shù)據(jù)庫，數(shù)據(jù)庫在HDFS上的默認存儲路徑是/user/hive/warehouse/*.db痕貌。

hive (default)> create database db_hive;

可能出現(xiàn)的報錯：

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.)

解決辦法：https://blog.csdn.net/mo_ing/article/details/81219533

個人解決版本：使用mysql-connector-java-5.1.27.jar话速，而不是mysql-connector-java-5.1.18.jar

(2).避免要創(chuàng)建的數(shù)據(jù)庫已經(jīng)存在錯誤，增加if not exists判斷芯侥。（標準寫法）

hive> create database db_hive;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists

標準寫法：

hive (default)> create database if not exists db_hive;

(3)創(chuàng)建一個數(shù)據(jù)庫，指定數(shù)據(jù)庫在HDFS上存放的位置

hive (default)> create database db_hive2 location '/db_hive2.db';

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

????用戶可以使用ALTER DATABASE命令為某個數(shù)據(jù)庫的DBPROPERTIES設(shè)置鍵-值對屬性值，來描述這個數(shù)據(jù)庫的屬性信息柱查。數(shù)據(jù)庫的其他元數(shù)據(jù)信息都是不可更改的廓俭，包括數(shù)據(jù)庫名和數(shù)據(jù)庫所在的目錄位置。

hive (default)> alter database db_hive set dbproperties('createtime'='20190506');

在mysql中查看修改結(jié)果

hive> desc database extended db_hive;

1.3 查詢數(shù)據(jù)庫

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

(1)顯示數(shù)據(jù)庫

hive> show databases;

(2)過濾顯示查詢的數(shù)據(jù)庫

hive> show databases like 'db_hive*';
1.3.2 查看數(shù)據(jù)庫詳情

(1)顯示數(shù)據(jù)庫信息

hive> desc database db_hive;

(2)顯示數(shù)據(jù)庫詳細信息唉工，extended

hive> desc database extended db_hive;
1.3.3 切換當前數(shù)據(jù)庫
hive (default)> use db_hive;
image.png

1.4 刪除數(shù)據(jù)庫

(1)刪除空數(shù)據(jù)庫

hive>drop database db_hive2;

(2)如果刪除的數(shù)據(jù)庫不存在研乒，最好采用 if exists判斷數(shù)據(jù)庫是否存在

hive> drop database db_hive2;

正確做法：

hive> drop database if exists db_hive2;

(3)如果數(shù)據(jù)庫不為空，可以采用cascade命令淋硝，強制刪除

hive> drop database db_hive;

正確做法：

hive> drop database db_hive cascade;

1.5 創(chuàng)建表

1.建表語法
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]
2.字段解釋說明：

(1)CREATE TABLE 創(chuàng)建一個指定名字的表雹熬。如果相同名字的表已經(jīng)存在，則拋出異常谣膳；用戶可以用 IF NOT EXISTS 選項來忽略這個異常竿报。

(2)EXTERNAL關(guān)鍵字可以讓用戶創(chuàng)建一個外部表，在建表的同時指定一個指向?qū)嶋H數(shù)據(jù)的路徑（LOCATION）继谚，Hive創(chuàng)建內(nèi)部表時烈菌，會將數(shù)據(jù)移動到數(shù)據(jù)倉庫指向的路徑；若創(chuàng)建外部表花履，僅記錄數(shù)據(jù)所在的路徑芽世，不對數(shù)據(jù)的位置做任何改變。在刪除表的時候诡壁，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會被一起刪除济瓢，而外部表只刪除元數(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, ...)]

????用戶在建表的時候可以自定義SerDe或者使用自帶的SerDe。如果沒有指定ROW FORMAT 或者ROW FORMAT DELIMITED纽帖，將會使用自帶的SerDe宠漩。在建表的時候，用戶還需要為表指定列懊直，用戶在指定表的列的同時也會指定自定義的SerDe扒吁，Hive通過SerDe確定表的具體的列的數(shù)據(jù)。

(8)STORED AS指定存儲文件類型

????常用的存儲文件類型：SEQUENCEFILE(二進制序列文件)室囊、TEXTFILE(文本)雕崩、RCFILE(列式存儲格式文件)
????如果文件數(shù)據(jù)是純文本，可以使用STORED AS TEXTFILE融撞。如果數(shù)據(jù)需要壓縮盼铁，使用 STORED AS SEQUENCEFILE。

(9)LOCATION ：指定表在HDFS上的存儲位置尝偎。

(10)LIKE允許用戶復制現(xiàn)有的表結(jié)構(gòu)饶火，但是不復制數(shù)據(jù)鹏控。

1.5.1 管理表
1.理論

????默認創(chuàng)建的表都是所謂的管理表，有時也被稱為內(nèi)部表肤寝。因為這種表当辐，Hive會（或多或少地）控制著數(shù)據(jù)的生命周期。Hive默認情況下會將這些表的數(shù)據(jù)存儲在由配置項hive.metastore.warehouse.dir(例如鲤看，/user/hive/warehouse)所定義的目錄的子目錄下缘揪。 當我們刪除一個管理表時，Hive也會刪除這個表中數(shù)據(jù)义桂。管理表不適合和其他工具共享數(shù)據(jù)找筝。

2.案例實操

(1)普通創(chuàng)建表

create table if not exists student2(

id int, name string

)

row format delimited fields terminated by '\t'

stored as textfile

location '/user/hive/warehouse/student2';

(2)根據(jù)查詢結(jié)果創(chuàng)建表（查詢的結(jié)果會添加到新創(chuàng)建的表中）

create table if not exists student3
as select id from student;

(3)根據(jù)已經(jīng)存在的表結(jié)構(gòu)創(chuàng)建表

create table if not exists student4 like student;

(4)查詢表的類型

hive (default)> desc formatted student4;
1.5.2 外部表
1.理論

????因為表是外部表，所以Hive并非認為其完全擁有這份數(shù)據(jù)慷吊。刪除該表并不會刪除掉這份數(shù)據(jù)袖裕，不過描述表的元數(shù)據(jù)信息會被刪除掉。

2.管理表和外部表的使用場景：

????每天將收集到的網(wǎng)站日志定期流入HDFS文本文件罢浇。在外部表（原始日志表）的基礎(chǔ)上做大量的統(tǒng)計分析陆赋，用到的中間表、結(jié)果表使用內(nèi)部表存儲嚷闭，數(shù)據(jù)通過SELECT+INSERT進入內(nèi)部表攒岛。

3.案例實操

分別創(chuàng)建部門和員工外部表，并向表中導入數(shù)據(jù)胞锰。

(1)原始數(shù)據(jù)

(2)建表語句

創(chuàng)建部門表

create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

創(chuàng)建員工表

create external table if not exists default.emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

(3)查看創(chuàng)建的表

hive (default)> show tables;
查看創(chuàng)建的表

(4)向外部表中導入數(shù)據(jù)
導入數(shù)據(jù)

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept;

hive (default)> load data local inpath '/opt/module/datas/emp.txt' into table default.emp;

查詢結(jié)果

hive (default)> select * from emp;

hive (default)> select * from dept;

(5)查看表格式化數(shù)據(jù)

hive (default)> desc formatted dept;

1.6 分區(qū)表

????分區(qū)表實際上就是對應一個HDFS文件系統(tǒng)上的獨立的文件夾灾锯，該文件夾下是該分區(qū)所有的數(shù)據(jù)文件。Hive中的分區(qū)就是分目錄嗅榕，把一個大的數(shù)據(jù)集根據(jù)業(yè)務(wù)需要分割成小的數(shù)據(jù)集顺饮。在查詢時通過WHERE子句中的表達式選擇查詢所需要的指定的分區(qū)，這樣的查詢效率會提高很多凌那。

1.6.1 分區(qū)表基本操作

(1)引入分區(qū)表（需要根據(jù)日期對日志進行管理）

/user/hive/warehouse/log_partition/20190702/20190702.log

/user/hive/warehouse/log_partition/20190703/20190703.log

/user/hive/warehouse/log_partition/20190704/20190704.log

(2)創(chuàng)建分區(qū)表語法

hive (default)> create table dept_partition(
               deptno int, dname string, loc string
               )
               partitioned by (month string)
               row format delimited fields terminated by '\t';

(3)加載數(shù)據(jù)到分區(qū)表中

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201909');

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201908');

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201907');

4）查詢分區(qū)表中數(shù)據(jù)
單分區(qū)查詢

hive (default)> select * from dept_partition where month='201909';

多分區(qū)聯(lián)合查詢

hive (default)> select * from dept_partition where month='201909'
              union
              select * from dept_partition where month='201908'
              union
              select * from dept_partition where month='201907';

(5)增加分區(qū)

創(chuàng)建單個分區(qū)

hive (default)> alter table dept_partition add partition(month='201906') ;

同時創(chuàng)建多個分區(qū)

hive (default)>  alter table dept_partition add partition(month='201905') partition(month='201904');

注：增加多個分區(qū)之間用空格" "隔開兼雄，刪除多個分區(qū)用","隔開

(6)刪除分區(qū)

刪除單個分區(qū)

hive (default)> alter table dept_partition drop partition (month='201904');

同時刪除多個分區(qū)

hive (default)> alter table dept_partition drop partition (month='201905'), partition (month='201906');

(7)查看分區(qū)表有多少分區(qū)

hive> show partitions dept_partition;

(8)查看分區(qū)表結(jié)構(gòu)

hive> desc formatted dept_partition;
1.6.2 分區(qū)表注意事項
1.創(chuàng)建二級分區(qū)表
hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';
2.正常的加載數(shù)據(jù)

(1)加載數(shù)據(jù)到二級分區(qū)表中

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201909', day='13');

(2)查詢分區(qū)數(shù)據(jù)

hive (default)> select * from dept_partition2 where month='201909' and day='13';
3.把數(shù)據(jù)直接上傳到分區(qū)目錄上，讓分區(qū)表和數(shù)據(jù)產(chǎn)生關(guān)聯(lián)的兩種方式

(1)方式一：上傳數(shù)據(jù)后修復
上傳數(shù)據(jù)

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201909/day=12;

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201909/day=12;

查詢數(shù)據(jù)（查詢不到剛上傳的數(shù)據(jù)）

hive (default)> select * from dept_partition2 where month='201909' and day='12';

執(zhí)行修復命令

hive>msck repair table dept_partition2;

再次查詢數(shù)據(jù)

hive (default)> select * from dept_partition2 where month='201909' and day='12';

(2)方式二：上傳數(shù)據(jù)后添加分區(qū)
上傳數(shù)據(jù)

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201909/day=11;

hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201909/day=11;

執(zhí)行添加分區(qū)

hive (default)> alter table dept_partition2 add partition(month='201909', day='11');

查詢數(shù)據(jù)

hive (default)> select * from dept_partition2 where month='201909' and day='11';

(3)方式三：上傳數(shù)據(jù)后load數(shù)據(jù)到分區(qū)

創(chuàng)建目錄

hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201909/day=10;

上傳數(shù)據(jù)

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201909',day='10');

查詢數(shù)據(jù)

hive (default)> select * from dept_partition2 where month='201909' and day='10';

1.7 修改表

1.7.1 重命名表
1.語法
ALTER TABLE table_name RENAME TO new_table_name
2.實操案例
hive (default)> alter table dept_partition2 rename to dept_partition4;
1.7.2 增加和刪除表分區(qū)

詳見1.6.1分區(qū)表基本操作帽蝶。同上

1.7.3 增加/修改/替換列信息
1.語法

更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替換列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

注：ADD是代表新增一字段赦肋，字段位置在所有列后面(partition列前)，REPLACE則是表示替換表中所有字段励稳。

2.實操案例

(1)查詢表結(jié)構(gòu)

hive> desc dept_partition;

(2)添加列

hive (default)> alter table dept_partition add columns(deptdesc string);

(3)查詢表結(jié)構(gòu)

hive>desc dept_partition;

(4)更新列

hive (default)> alter table dept_partition change column deptdesc desc int;

(5)查詢表結(jié)構(gòu)

hive>desc dept_partition;

(6)替換列

hive (default)> alter table dept_partition replace columns(deptno string, dname string, loc string);

(7)查詢表結(jié)構(gòu)

hive>desc dept_partition;

1.8 刪除表

hive (default)> drop table dept_partition;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末佃乘，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子驹尼，更是在濱河造成了極大的恐慌趣避，老刑警劉巖，帶你破解...
    沈念sama閱讀 216,324評論 6贊 498
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件新翎，死亡現(xiàn)場離奇詭異程帕，居然都是意外死亡住练，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,356評論 3贊 392
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門骆捧，熙熙樓的掌柜王于貴愁眉苦臉地迎上來澎羞，“玉大人，你說我怎么就攤上這事敛苇。” “怎么了顺呕？”我有些...
    開封第一講書人閱讀 162,328評論 0贊 353
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵枫攀，是天一觀的道長。 經(jīng)常有香客問我株茶，道長来涨，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 58,147評論 1贊 292
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任启盛，我火速辦了婚禮蹦掐，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘僵闯。我一直安慰自己卧抗，他們只是感情好，可當我...
    茶點故事閱讀 67,160評論 6贊 388
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布鳖粟。 她就那樣靜靜地躺著社裆，像睡著了一般。 火紅的嫁衣襯著肌膚如雪向图。 梳的紋絲不亂的頭發(fā)上泳秀，一...
    開封第一講書人閱讀 51,115評論 1贊 296
  • 城市分裂傳說
    那天，我揣著相機與錄音榄攀，去河邊找鬼嗜傅。 笑死，一個胖子當著我的面吹牛檩赢，可吹牛的內(nèi)容都是我干的吕嘀。 我是一名探鬼主播，決...
    沈念sama閱讀 40,025評論 3贊 417
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼漠畜，長吁一口氣：“原來是場噩夢啊……” “哼币他！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起憔狞，我...
    開封第一講書人閱讀 38,867評論 0贊 274
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤蝴悉，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后瘾敢，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體拍冠，經(jīng)...
    沈念sama閱讀 45,307評論 1贊 310
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡尿这，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,528評論 2贊 332
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了庆杜。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片射众。...
    茶點故事閱讀 39,688評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖晃财，靈堂內(nèi)的尸體忽然破棺而出叨橱，到底是詐尸還是另有隱情，我是刑警寧澤断盛，帶...
    沈念sama閱讀 35,409評論 5贊 343
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布罗洗，位于F島的核電站，受9級特大地震影響钢猛，放射性物質(zhì)發(fā)生泄漏伙菜。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 41,001評論 3贊 325
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一命迈、第九天 我趴在偏房一處隱蔽的房頂上張望贩绕。 院中可真熱鬧，春花似錦壶愤、人聲如沸淑倾。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,657評論 0贊 22
  • 一樁弒父案公你，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽踊淳。三九已至，卻和暖如春陕靠，著一層夾襖步出監(jiān)牢的瞬間迂尝，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,811評論 1贊 268
  • 情欲美人皮
    我被黑心中介騙來泰國打工剪芥， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留垄开，地道東北人。 一個月前我還...
    沈念sama閱讀 47,685評論 2贊 368
  • 代替公主和親
    正文 我出身青樓税肪，卻偏偏與公主長得像溉躲，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子益兄，可洞房花燭夜當晚...
    茶點故事閱讀 44,573評論 2贊 353

推薦閱讀更多精彩內(nèi)容

  • Zookeeper用于集群主備切換锻梳。 YARN讓集群具備更好的擴展性。 Spark沒有存儲能力净捅。 Spark的Ma...
    Yobhel閱讀 7,267評論 0贊 34
  • ORA-00001: 違反唯一約束條件 (.) 錯誤說明：當在唯一索引所對應的列上鍵入重復值時疑枯，會觸發(fā)此異常。 O...
    我想起個好名字閱讀 5,306評論 0贊 9
  • 安裝 元素數(shù)據(jù)存儲選擇 默認使用derby數(shù)據(jù)庫蛔六，不能夠多個用戶同時使用荆永，多用于測試使用MySQL數(shù)據(jù)庫存儲元數(shù)據(jù)...
    BlackChen閱讀 434評論 0贊 1
  • hive簡介 解釋一：Hive是一個數(shù)據(jù)倉庫基礎(chǔ)工具在Hadoop中用來處理結(jié)構(gòu)化數(shù)據(jù)废亭。它架構(gòu)在Hadoop之上，...
    卡卡xx閱讀 6,319評論 0贊 4