實(shí)訓(xùn)10 數(shù)據(jù)定義操作DDL

實(shí)訓(xùn)5.2數(shù)據(jù)定義操作DDL1.實(shí)訓(xùn)目的通過(guò)本實(shí)訓(xùn)，理解HiveQL與SQL的區(qū)別，理解DDL的概念以及實(shí)際操作。2.實(shí)訓(xùn)內(nèi)容本實(shí)訓(xùn)包含HiveQL與SQL的語(yǔ)法上的7種重要對(duì)比，用法上的4種不同；分別進(jìn)行了數(shù)據(jù)庫(kù)的DDL操作和表的DDL操作。3.實(shí)訓(xùn)要求以小組為單元進(jìn)行實(shí)訓(xùn)，每小組5人，小組自主協(xié)商選一位組長(zhǎng)，由組長(zhǎng)安排和分配實(shí)訓(xùn)任務(wù)，注意對(duì)比HiveQL與SQL的區(qū)別，避免在使用的時(shí)候出現(xiàn)語(yǔ)法錯(cuò)誤。4.準(zhǔn)備知識(shí)（1）HiveQL與SQL的區(qū)別Hive的查詢語(yǔ)言是HiveQL，HQL支持SQL-92標(biāo)準(zhǔn)，所以和SQL非常相似。但由于Hive是基于Hadoop，而SQL通常是基于關(guān)系型數(shù)據(jù)庫(kù)，這使得HQL與SQL相比有一些區(qū)別和局限。（2）語(yǔ)法的區(qū)別①兩表內(nèi)聯(lián)：SQL中：select*fromduala,dualbwherea.Key=b.Key;Hive中應(yīng)為：select*fromdualajoindualbona.Key=b.Key;而不是傳統(tǒng)的格式：selectt1.a1asc1,t2.b1asc2fromt1,t2wheret1.a2=t2.b2;②分號(hào)字符分號(hào)是SQL語(yǔ)句和HiveQL的結(jié)束標(biāo)記，但HiveQL中，對(duì)分號(hào)有個(gè)別區(qū)別，例如：selectconcat(Key,concat(';',Key))fromdual;SQL可以執(zhí)行，但是HiveQL會(huì)報(bào)錯(cuò)，因用下面語(yǔ)句：selectconcat(Key,concat('\073',Key))fromdual;③IS[NOT]NULLSQL中null代表空值,但在HiveQL中String類型的字段若是空(empty)字符串,即長(zhǎng)度為0,那么對(duì)它進(jìn)行ISNULL的判斷結(jié)果是False，即非空。④Hive不支持將數(shù)據(jù)插入現(xiàn)有的表或分區(qū)中，僅支持覆蓋重寫(xiě)整個(gè)表⑤Hive不支持INSERTINTO表Values(),UPDATE,DELETE操作⑥Hive支持嵌入Mapreduce程序，來(lái)處理復(fù)雜的邏輯⑦Hive支持將轉(zhuǎn)換后的數(shù)據(jù)直接寫(xiě)入不同的表，還能寫(xiě)入分區(qū)、HDFS和本地目錄（3）用法上的區(qū)別①HiveQL不支持行級(jí)別的增、改、刪，所有數(shù)據(jù)在加載時(shí)就已經(jīng)確定，不可更改②HiveQL支持基本數(shù)據(jù)類型和復(fù)雜數(shù)據(jù)類型，而SQL只支持基本數(shù)據(jù)類型③HiveQL不支持事務(wù)④支持分區(qū)存儲(chǔ)【實(shí)訓(xùn)步驟】1.DDL操作1）DatabaseDDL操作創(chuàng)建數(shù)據(jù)庫(kù)test默認(rèn)對(duì)應(yīng)的HDFS目錄為：/user/hive/warehouse的文件夾，命名格式：數(shù)據(jù)庫(kù)名.dbcreatedatabasetest;可以使用另外一個(gè)終端查看到HDFS上創(chuàng)建了相應(yīng)的文件夾。如圖5-14所示。圖5-14創(chuàng)建test.db數(shù)據(jù)庫(kù)如果想要新建test1數(shù)據(jù)庫(kù)，如果存在則不創(chuàng)建，不存在再創(chuàng)建可用下面命令：createdatabaseifnotexiststest1;在指定路徑下創(chuàng)建數(shù)據(jù)庫(kù)（mydb：數(shù)據(jù)庫(kù)名稱）createdatabasetest2location'/myhive/mydb';可以看到在指定的路徑創(chuàng)建了數(shù)據(jù)庫(kù)。如圖5-15所示。圖5-15創(chuàng)建mydb數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)，并為數(shù)據(jù)庫(kù)添加描述信息：createdatabasetest3comment'mytestdb'withdbproperties('creator'='lisi','date'='2023-10-1');此時(shí)，可以在MySQL中查詢所創(chuàng)建的數(shù)據(jù)庫(kù)以及相關(guān)的描述信息。如圖5-16所示。在MySQL中執(zhí)行：select*fromDBS;select*fromDATABASE_PARAMS;圖5-16查看所創(chuàng)建的數(shù)據(jù)庫(kù)查詢目前所有的數(shù)據(jù)庫(kù)showdatabases;查詢結(jié)果如圖5-17所示。圖5-17查詢所有數(shù)據(jù)庫(kù)查看以test開(kāi)頭的數(shù)據(jù)庫(kù)（前面實(shí)訓(xùn)有提到）showdatabaseslike'test*';查詢結(jié)果如圖5-18所示。圖5-18test開(kāi)頭的數(shù)據(jù)庫(kù)查看數(shù)據(jù)庫(kù)的詳細(xì)信息descdatabasetest3;查詢結(jié)果如圖5-19所示。圖5-19test3數(shù)據(jù)庫(kù)詳細(xì)信息在上面的基礎(chǔ)上如果想查看數(shù)據(jù)庫(kù)更詳細(xì)的信息，可以加個(gè)參數(shù)extendeddescdatabaseextendedtest3;查詢結(jié)果如圖5-20所示。圖5-20test3數(shù)據(jù)庫(kù)更詳細(xì)信息切換數(shù)據(jù)庫(kù)，如果不指定數(shù)據(jù)庫(kù)，那么默認(rèn)使用的就是default，現(xiàn)在我們使用test3數(shù)據(jù)庫(kù)usetest3;修改數(shù)據(jù)庫(kù)的modifieralterdatabasetest3setdbproperties('modifier'='teachershao');執(zhí)行結(jié)束后再重新查看詳情，發(fā)現(xiàn)已經(jīng)有相應(yīng)的結(jié)果。descdatabaseextendedtest3;效果如圖5-21所示。圖5-21修改并查看modifier屬性信息刪除數(shù)據(jù)庫(kù)test3dropdatabaseifexiststest3;刪除后續(xù)以及沒(méi)有test3數(shù)據(jù)庫(kù)，結(jié)果如圖5-22所示。圖5-22刪除test3數(shù)據(jù)庫(kù)刪除時(shí)如果報(bào)如下錯(cuò)誤：InvalidOperationException(message:Databasetest3isnotempty.Oneormoretablesexist.)如果數(shù)據(jù)庫(kù)中有0或多個(gè)表時(shí)，不能直接刪除，需要先刪除表再刪除數(shù)據(jù)庫(kù)。如果想要?jiǎng)h除含有表的數(shù)據(jù)庫(kù)，在刪除時(shí)加上cascade，表示級(jí)聯(lián)刪除（慎用）， 可以使用如下命令：dropdatabaseifexiststest3cascade;②TableDDL操作創(chuàng)建表usetest2;createtableemp(empnoint,enamestring,jobstring,mgrint,hiredatestring,saldouble,commdouble,deptnoint)rowformatdelimitedfieldsterminatedby',';創(chuàng)建結(jié)果如圖5-23所示。圖5-23創(chuàng)建表emp新開(kāi)一個(gè)終端，準(zhǔn)備數(shù)據(jù)：vim/root/datas/emp.txt7369,SMITH,CLERK,7902,1980-12-17,800.00,207499,ALLEN,SALESMAN,7698,1981-2-20,1600.00,300.00,307521,WARD,SALESMAN,7698,1981-2-22,1250.00,500.00,307566,JONES,MANAGER,7839,1981-4-2,2975.00,207654,MARTIN,SALESMAN,7698,1981-9-28,1250.00,1400.00,307698,BLAKE,MANAGER,7839,1981-5-1,2850.00,307782,CLARK,MANAGER,7839,1981-6-9,2450.00,107788,SCOTT,ANALYST,7566,1987-4-19,3000.00,207839,KING,PRESIDENT,1981-11-17,5000.00,107844,TURNER,SALESMAN,7698,1981-9-28,1500.00,0.00,307876,ADAMS,CLERK,7788,1987-5-23,1100.00,207900,JAMES,CLERK,7698,1981-12-3,950.00,307902,FORD,ANALYST,7566,1981-12-3,3000.00,207934,MILLER,CLERK,7782,1982-1-23,1300.00,108888,HIVE,PROGRAM,7839,1988-1-23,10300.00返回hive，執(zhí)行以下命令將數(shù)據(jù)導(dǎo)入到hive中：loaddatalocalinpath'/root/datas/emp.txt'overwriteintotableemp;導(dǎo)入結(jié)果如圖5-24所示。圖5-24向emp表中導(dǎo)入數(shù)據(jù)復(fù)制表（只拷貝表結(jié)構(gòu)，沒(méi)有數(shù)據(jù)）createtableemp2likeemp;select*fromemp2;復(fù)制結(jié)果如圖5-25所示。圖5-25查詢表結(jié)果復(fù)制表（拷貝表結(jié)構(gòu)及數(shù)據(jù)）本操作會(huì)執(zhí)行MapReduce作業(yè)，所以需要先啟動(dòng)YARNcreatetableemp3asselect*fromemp;注意：由于實(shí)訓(xùn)環(huán)境內(nèi)存資源比較緊缺，如果實(shí)訓(xùn)出錯(cuò)，則可以使用本地模式執(zhí)行。先使用下面命令設(shè)置Hive成本地模式再執(zhí)行創(chuàng)建語(yǔ)句。效果如圖5-26所示。sethive.exec.mode.local.auto=true;圖5-26拷貝表結(jié)構(gòu)及數(shù)據(jù)執(zhí)行流程select*fromemp3;查看emp3表中的信息如圖5-27所示。圖5-27emp3表中的信息創(chuàng)建表一張emp_copy表，要求包含emp中的這三列：empno、ename、jobcreatetableemp_copyasselectempno,ename,jobfromemp;showtables'emp*';創(chuàng)建結(jié)果如圖5-28所示。圖5-28創(chuàng)建emp_copy表查看表結(jié)構(gòu)descemp; 操作結(jié)果如圖5-29所示。圖5-29查看emp表結(jié)構(gòu)查看表結(jié)構(gòu)的詳細(xì)信息descextendedemp; 查看結(jié)果如圖5-30所示。圖5-30查看emp表的詳細(xì)信息上面查看到的表結(jié)構(gòu)的詳細(xì)信息格式較亂，可使用formatted格式化查看descformattedemp; 查看結(jié)果如圖5-31所示。圖5-31格式化查看emp表查看表的創(chuàng)建語(yǔ)句showcreatetableemp;修改表名語(yǔ)法：ALTERTABLEtable_nameRENAMETOnew_table_name;修改表名emp2為emp_bak。效果如圖5-32所示。altertableemp2renametoemp_