數(shù)據(jù)倉庫Hive應用實戰(zhàn)- 課件 模塊4 管理分區(qū)表中數(shù)據(jù)

理解并熟記創(chuàng)建數(shù)據(jù)庫的語法和應用方法理解并熟記修改、刪除數(shù)據(jù)庫的語法和應用方法理解并熟記查詢和切換數(shù)據(jù)庫的語法和應用方法理解并熟記修改數(shù)據(jù)庫存儲位置的語法和應用方法理解并初步建立數(shù)據(jù)庫安全規(guī)范意識大多數(shù)數(shù)據(jù)集根據(jù)業(yè)務情況分解成小的數(shù)據(jù)集，例如以年分解過去每一屆奧林匹克運動會的選手數(shù)據(jù)及獲獎數(shù)據(jù)，以月份分解某企業(yè)的所有銷售數(shù)據(jù)，以日分解某快遞公司的快遞數(shù)據(jù)信息等。為分解的大數(shù)據(jù)集創(chuàng)建分區(qū)表模式以映射到HDFS底層分解的小數(shù)據(jù)是本次的任務目標。本任務以“學生信息系統(tǒng)”項目、“大數(shù)據(jù)商業(yè)智能選址”項目為實操載體，完成分析分區(qū)表實際數(shù)據(jù)格式、使用HiveQL創(chuàng)建分區(qū)表、理順分區(qū)表的分區(qū)與HDFS目錄間關(guān)系、查看分區(qū)信息等學習目標。任務1創(chuàng)建分區(qū)表4.1.1分區(qū)表創(chuàng)建方式任務1創(chuàng)建分區(qū)表CREATE[EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_name(col_namedata_type[COMMENTcol_comment]，...)[COMMENTtable_comment][PARTITIONEDBY(col_namedata_type[COMMENTcol_comment]，...)][CLUSTEREDBY(col_name,col_name,...)[SORTEDBY(col_name[ASC|DESC],…)]INTOnum_bucketsBUCKETS][ROWFORMATrow_format][STOREDASfile_format][TBLPROPERTIYES(property_name=property_value,...)][LOCATIONhdfs_path];4.1.1分區(qū)表創(chuàng)建方式任務1創(chuàng)建分區(qū)表【例4-1】在studentdb中創(chuàng)建分區(qū)表student_in_partition_1，數(shù)據(jù)表字段名定義如下表所示,往分區(qū)表中插入以下數(shù)據(jù)：（'Zhangsan'，92.5，'BigData1901'）、（'Zhouhui'，83.5，'Network1901'），最后查看分區(qū)表的存儲目錄。4.1.1分區(qū)表創(chuàng)建方式任務1創(chuàng)建分區(qū)表第一步，創(chuàng)建分區(qū)表student_in_partition_1：CREATETABLEIFNOTEXISTSstudent_in_partition_1(stnameSTRINGCOMMENT'學生姓名',av_scoreFLOATCOMMENT'平均分')PARTITIONEDBY(classSTRINGCOMMENT'班級');第二步，往分區(qū)表student_in_partition_1中插入兩條數(shù)據(jù)：INSERTINTOTABLEstudent_in_partition_1PARTITION(class='BigData1901')VALUES('Zhangsan',92.5);INSERTINTOTABLEstudent_in_partition_1PARTITION(class='Network1901')VALUES('Zhouhui',83.5);第三步，查看分區(qū)表的存儲目錄：總結(jié)，在表存儲目錄下以分區(qū)為名創(chuàng)建了class=BigData1901和class=Network1901兩個獨立的分區(qū)目錄，且各分區(qū)目錄下存儲著該分區(qū)內(nèi)的數(shù)據(jù)文件。4.1.1分區(qū)表創(chuàng)建方式任務1創(chuàng)建分區(qū)表【例4-2】在studentdb中創(chuàng)建分區(qū)表student_in_partition_2，數(shù)據(jù)表字段名定義如下表所示,往分區(qū)表中插入以下數(shù)據(jù)：（'Luotian',88.1,2018,'BigData1801'）、（'Wangan'，75.5，2018，'BigData1802'）、（'Zhangsan'，83.5，2019，'Network1901'）、（'Liming'，80，2019，'Network1902'），最后查看分區(qū)表的存儲目錄。4.1.1分區(qū)表創(chuàng)建方式任務1創(chuàng)建分區(qū)表第一步，創(chuàng)建多分區(qū)表student_in_partition_2：CREATETABLEIFNOTEXISTSstudent_in_partition_2(stnameSTRINGCOMMENT'學生姓名',av_scoreFLOATCOMMENT'平均分')PARTITIONEDBY(gradeINTCOMMENT'入學年份',classSTRINGCOMMENT'班級');第二步，往分區(qū)表student_in_partition_2中插入四條數(shù)據(jù)：數(shù)據(jù)1：INSERTINTOTABLEstudent_in_partition_2PARTITION(grade=2018,class='BigData1801')VALUES('Luotian',88.1);數(shù)據(jù)2：INSERTINTOTABLEstudent_in_partition_2PARTITION(grade=2018,class='BigData1802')VALUES('Wangan',75.5);數(shù)據(jù)3：INSERTINTOTABLEstudent_in_partition_2PARTITION(grade=2019,class='Network1901')VALUES('Zhangsan',83.5);數(shù)據(jù)4：INSERTINTOTABLEstudent_in_partition_2PARTITION(grade=2019,class='Network1902')VALUES('Liming',80);4.1.1分區(qū)表創(chuàng)建方式任務1創(chuàng)建分區(qū)表第三步，查看多分區(qū)表的存儲目錄：總結(jié)，在表存儲目錄下以grade分區(qū)為父目錄，以class分區(qū)為子目錄創(chuàng)建了兩層四個獨立分區(qū)目錄，且各分區(qū)目錄下存儲著該分區(qū)內(nèi)的數(shù)據(jù)文件。分區(qū)字段一旦創(chuàng)建好，如上例中的grade和class，其就和普通字段一樣。對數(shù)據(jù)進行分區(qū)，最重要的原因是為了更快地查詢。4.1.2分區(qū)表管理方式任務1創(chuàng)建分區(qū)表1、內(nèi)部分區(qū)表分區(qū)表改變了Hive對數(shù)據(jù)存儲的組織方式，提高了查詢速度。Hive中分區(qū)表將會創(chuàng)建好零個或者多個可以反映分區(qū)結(jié)構(gòu)的子目錄，如上例中的表目錄student_in_partition_1下創(chuàng)建了兩個分區(qū)目錄，各分區(qū)目錄存放著對應班級的學生信息，如上圖4-1所示。2、外部分區(qū)表外部表也可以使用分區(qū)，而且這是管理大型生產(chǎn)數(shù)據(jù)集最常見的情況。外部和分區(qū)的結(jié)合不僅給用戶提供了可以和其他工具共享數(shù)據(jù)的方式，同時也可以優(yōu)化查詢性能。由于用戶可以自己定義目錄結(jié)構(gòu)，因此用戶對于目錄結(jié)構(gòu)的使用具有更多的靈活性。4.1.3分區(qū)信息查看方式任務1創(chuàng)建分區(qū)表Hive中可以查看分區(qū)表的分區(qū)信息，其基本語法聲明如下：SHOWPARTITIONS[db_name.]table_name[PARTITION(partition_spec)];partition_spec::(partition_column=partition_col_value,partition_column=partition_col_value,...)4.1.3分區(qū)信息查看方式任務1創(chuàng)建分區(qū)表【例4-3】查看studentdb數(shù)據(jù)庫中分區(qū)表student_in_partition_2的所有分區(qū)信息。4.1.3分區(qū)信息查看方式任務1創(chuàng)建分區(qū)表【例4-4】查看分區(qū)表student_in_partition2中的grade為2018下的所有分區(qū)信息。4.1.3分區(qū)信息查看方式任務1創(chuàng)建分區(qū)表【例4-5】通過DESC命令查詢分區(qū)表student_in_partition_2的表結(jié)構(gòu)信息。4.1.4創(chuàng)建“大數(shù)據(jù)商業(yè)智能選址”ods_site分區(qū)表任務1創(chuàng)建分區(qū)表4.1.4.1設置分區(qū)COMMENT字符編碼登錄MySQL，對指定的元數(shù)據(jù)庫hivedb中相關(guān)表做字符編碼修改。MariaDB[hivedb]>ALTERTABLEPARTITION_KEYSMODIFYcolumnPKEY_COMMENTvarchar(4000)charactersetutf8;4.1.4創(chuàng)建“大數(shù)據(jù)商業(yè)智能選址”ods_site分區(qū)表任務1創(chuàng)建分區(qū)表4.1.4.2創(chuàng)建ods_site分區(qū)表ods_site庫中包括4個分區(qū)表，分別是基站常住人口表ods_resident_pop、基站流動人口表ods_floating_pop、消費信息表ods_consumption和銀行APP明細表ods_bank_app。分區(qū)表創(chuàng)建好之后，發(fā)現(xiàn)分區(qū)字段設置不盡合理，尤其新手初次創(chuàng)建分區(qū)表時由于分析實際數(shù)據(jù)分區(qū)字段時考慮不周全，或者隨著業(yè)務數(shù)據(jù)量迅速增長等需要新增分區(qū)字段、重新指定分區(qū)目錄、重新命名分區(qū)字段名等。任務2修改刪除分區(qū)4.2.1添加分區(qū)為表增加分區(qū)的語法格式：ALTERTABLEtable_nameADD[IFNOTEXISTS]PARTITIONpartition_spec

[LOCATION'location'][,PARTITIONpartition_spec[LOCATION'location'],...];任務2修改刪除分區(qū)4.2.1添加分區(qū)任務2修改刪除分區(qū)【例4-6】為分區(qū)表phy_course_static_partition添加opt_cour為badminton的新分區(qū)，將數(shù)據(jù)phy_course_badminton.txt上傳至HDFS的/hivedata/phy_course_badminton目錄下，并將該數(shù)據(jù)作為新分區(qū)數(shù)據(jù)。4.2.1添加分區(qū)第1步，創(chuàng)建目錄，并上傳數(shù)據(jù)phy_course_badminton.txt到指定目錄下。dfs-mkdir/hivedata/phy_course_badminton;dfs-put/home/hadoop/hivedata/student/phy_course_badminton.txt/hivedata/phy_course_badminton;第2步，為分區(qū)表phy_course_static_partition添加新分區(qū)。ALTERTABLEphy_course_static_partitionADDPARTITION(opt_cour='badminton')LOCATION"/hivedata/phy_course_badminton";第3步，查詢分區(qū)表phy_course_static_partition的分區(qū)信息。任務2修改刪除分區(qū)4.2.1添加分區(qū)第4步，查詢opt_cour為badminton的新分區(qū)信息。4.2.1添加分區(qū)【例4-7】在分區(qū)表phy_course_static_partition的存儲目錄下分別創(chuàng)建opt_cour=volleyball和opt_cour=tennis的兩個分區(qū)子目錄。第1步，在分區(qū)表的存儲目錄下創(chuàng)建兩個子目錄。dfs-mkdir/user/hive/warehouse/studentdb.db/phy_course_static_partition/opt_cour=volleyball;dfs-mkdir/user/hive/warehouse/studentdb.db/phy_course_static_partition/opt_cour=tennis;第2步，查看分區(qū)表分區(qū)信息，沒有變化。第3步，對分區(qū)表執(zhí)行MSCKREPAIRTABLEstudentdb.phy_course_static_partition;第4步，再次查看分區(qū)表分區(qū)信息，分區(qū)發(fā)生變化。任務2修改刪除分區(qū)4.2.2修改分區(qū)路徑修改表的分區(qū)路徑的語法格式：ALTERTABLEtable_namePARTITIONpartition_specSETLOCATION"newlocation";任務2修改刪除分區(qū)4.2.2修改分區(qū)路徑【例4-8】將分區(qū)表phy_course_static_partition中opt_cour為badminton的分區(qū)路徑修改為/user/hive/warehouse/studentdb.db/phy_course_static_partition/badminton。任務2修改刪除分區(qū)4.2.2修改分區(qū)路徑第1步，修改badminton的分區(qū)路徑。ALTERTABLEstudentdb.phy_course_static_partitionPARTITION(opt_cour='badminton')SETLOCATION'/user/hive/warehouse/studentdb.db/phy_course_static_partition/badminton';第2步，查看badminton的分區(qū)信息。任務2修改刪除分區(qū)4.2.3修改分區(qū)字段名重命名分區(qū)名字的語法格式：ALTERTABLEtable_namePARTITIONpartition_specRENAMETOPARTITIONpartition_spec;任務2修改刪除分區(qū)4.2.3修改分區(qū)字段名【例4-9】將分區(qū)表phy_course_static_partition中opt_cour為basketball的分區(qū)名修改為basketball-1。任務2修改刪除分區(qū)4.2.3修改分區(qū)字段名【例4-9】將分區(qū)表phy_course_static_partition中opt_cour為basketball的分區(qū)名修改為basketball-1。第1步，修改分區(qū)名basketball為basketball-1。ALTERTABLEstudentdb.phy_course_static_partitionPARTITION(opt_cour="basketball")RENAMETOPARTITION(opt_cour="basketball-1");第2步，查看phy_course_static_partition分區(qū)表的存儲目錄和數(shù)據(jù)。任務2修改刪除分區(qū)4.2.4刪除分區(qū)刪除表分區(qū)的語法格式：ALTERTABLEtable_nameDROP[IFEXISTS]PARTITIONpartition_spec[,PARTITIONpartition_spec,...]任務2修改刪除分區(qū)4.2.4刪除分區(qū)【例4-10】將分區(qū)表phy_course_static_partition中opt_cour為basketball-1的分區(qū)刪除。第1步，刪除basketball-1分區(qū)。ALTERTABLEstudentdb.phy_course_static_partitionDROPPARTITION(opt_cour='basketball-1');第2步，查看phy_course_static_partition分區(qū)表的存儲目錄和數(shù)據(jù)。任務2修改刪除分區(qū)4.2.5修改“大數(shù)據(jù)商業(yè)智能選址”ods_site庫中分區(qū)表復制“大數(shù)據(jù)商業(yè)智能選址”ods_site庫中的分區(qū)表銀行APP明細表ods_bank_app，新表名為ods_bank_app_copy：CREATETABLEods_site.ods_bank_app_copyLIKEods_site.ods_bank_app;查看ods_bank_app_copy表的詳細信息，發(fā)現(xiàn)該表未復制表注釋信息：任務2修改刪除分區(qū)4.2.5修改“大數(shù)據(jù)商業(yè)智能選址”ods_site庫中分區(qū)表按照以下要求修改“大數(shù)據(jù)商業(yè)智能選址”ods_site庫中的ods_bank_app_copy表：①添加表注釋，注釋內(nèi)容為：此表為銀行APP明細表ods_bank_app的副本；②添加表字段day，數(shù)據(jù)類型為DATE；③添加兩個表分區(qū)，month為202212和month為202301，并分別指定路徑為/home/hadoop/hivedata/temp202212和/home/hadoop/hivedata/temp202301；④修改month為'202212'分區(qū)字段名為'202302'；⑤刪除month為'202302'的分區(qū)；任務2修改刪除分區(qū)上一任務中成功創(chuàng)建了分區(qū)表，并且每個分區(qū)表都對應一個HDFS目錄，當前表目錄均為空，此時需要將實際業(yè)務數(shù)據(jù)導入到相應分區(qū)表的分區(qū)中，即將數(shù)據(jù)存儲到每個子分區(qū)所對應的HDFS子目錄中。任務3導入數(shù)據(jù)到分區(qū)表4.3.1靜態(tài)分區(qū)導入數(shù)據(jù)方式靜態(tài)分區(qū)方式導入數(shù)據(jù)時，通過LOADDATA、INSERT語句中的PARTITION(partcol1[=val1],partcol2[=val2]...)必須要給分區(qū)字段指定值。任務3導入數(shù)據(jù)到分區(qū)表【例4-11】創(chuàng)建分區(qū)表phy_course_static_partition，設置opt_cour為分區(qū)字段，把表phy_course_hdfs中選修了“basketball”和“football”的學生信息以靜態(tài)分區(qū)方式分別加載進分區(qū)表phy_course_pstatic_partition表中。4.3.1靜態(tài)分區(qū)導入數(shù)據(jù)方式任務3導入數(shù)據(jù)到分區(qū)表第1步，創(chuàng)建分區(qū)表phy_course_static_partition。CREATETABLEphy_course_static_partition(stnameSTRINGCOMMENT'姓名',stidSTRINGCOMMENT'學號',classSTRINGCOMMENT'班級')PARTITIONEDBY(opt_courSTRINGCOMMENT'選修課程');第2步，使用partition子句將查詢到的選修了“basketball”的數(shù)據(jù)插入到指定分區(qū)：INSERTINTOTABLEphy_course_static_partitionPARTITION(opt_cour='basketball')SELECTstname,stid,classFROMphy_course_hdfsWHEREopt_cour='basketball';第3步，使用partition子句將查詢到的選修了“football”的數(shù)據(jù)插入到指定分區(qū)：INSERTINTOTABLEphy_course_static_partitionPARTITION(opt_cour='football')SELECTstname,stid,classFROMphy_course_hdfsWHEREopt_cour='football';4.3.1靜態(tài)分區(qū)導入數(shù)據(jù)方式任務3導入數(shù)據(jù)到分區(qū)表第4步，查看分區(qū)表phy_course_static_partition中的數(shù)據(jù)存儲目錄及目錄中數(shù)據(jù)，發(fā)現(xiàn)該表已經(jīng)創(chuàng)建了opt_cour=basketball和opt_cour=football兩個分區(qū)，且分區(qū)目錄下存儲了選修了該門選修課的學生信息：第5步，通過查詢分區(qū)表phy_course_static_partition，可以查詢到所有選修了basketball和football的學生數(shù)據(jù)：4.3.2動態(tài)分區(qū)導入數(shù)據(jù)方式任務3導入數(shù)據(jù)到分區(qū)表動態(tài)分區(qū)方式導入數(shù)據(jù)時，通過LOADDATA、INSERT語句不需要給分區(qū)字段指定值。使用動態(tài)分區(qū)必須滿足幾個條件：開啟動態(tài)分區(qū)：sethive.exec.dynamic.partition=true;設置分區(qū)模式為非嚴格的：sethive.exec.dynamic.partition.mode=nonstrict;當該屬性值是strict時，就是要求分區(qū)字段必須有一個是靜態(tài)的分區(qū)值；設置為nonstrict時，即可以全部動態(tài)分區(qū)。4.3.2動態(tài)分區(qū)導入數(shù)據(jù)方式任務3導入數(shù)據(jù)到分區(qū)表【例4-12】創(chuàng)建分區(qū)表phy_course_dynamic_partition，將phy_course_hdfs中的數(shù)據(jù)動態(tài)分區(qū)方式按照選修課程分區(qū)插入到phy_course_dynamic_partition中。第1步，創(chuàng)建分區(qū)表phy_course_dynamic_partition。CREATETABLEphy_course_dynamic_partition(stnameSTRINGCOMMENT'姓名',stidSTRINGCOMMENT'學號',classSTRINGCOMMENT'班級')PARTITIONEDBY(opt_courSTRINGCOMMENT'選修課程');第2步，使用動態(tài)分區(qū)語法將phy_course_hdfs中數(shù)據(jù)分區(qū)到phy_course_dynamic_partition中。INSERTINTOTABLEphy_course_dynamic_partitionPARTITION(opt_cour)SELECTstname,stid,class,opt_courFROMphy_course_hdfs;4.3.2動態(tài)分區(qū)導入數(shù)據(jù)方式任務3導入數(shù)據(jù)到分區(qū)表第3步，查看分區(qū)表phy_course_dynamic_partition中數(shù)據(jù)存儲目錄及目錄中數(shù)據(jù)。第4步，通過查詢分區(qū)表phy_course_dynamic_partition，可以查詢到所有選修課學生的160條數(shù)據(jù)，且按照不同的選修課程分區(qū)存儲。4.3.3動靜分區(qū)混合導入數(shù)據(jù)方式任務3導入數(shù)據(jù)到分區(qū)表【例4-13】創(chuàng)建分區(qū)表phy_course_static_dynamic_partition，設置opt_cour、class為分區(qū)字段，將phy_course_hdfs中的數(shù)據(jù)以混合分區(qū)方式將選修課程為“football”的學生信息分班級存儲導入到phy_course_static_dynamic_partition中。4.3.3動靜分區(qū)混合導入數(shù)據(jù)方式任務3導入數(shù)據(jù)到