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

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