05hiveql數(shù)據(jù)定義創(chuàng)建表表與外部表

數(shù)據(jù)倉庫《Hive編程指本章主要內(nèi)容創(chuàng)建數(shù)據(jù)修改數(shù)據(jù)創(chuàng)建表（表與外部表的格式）學(xué)習(xí)目標(biāo)能夠使用HQL

刪除表屬性，修改屬性）能夠使用HQL創(chuàng)建管理表、創(chuàng)建外部表掌握管理表和外部表的不同掌握分區(qū)表的使能夠使用HQL刪除能夠使用HQL修改數(shù)據(jù)庫的基本操表的基本操表什么情況下使用表表不方便和其他工作共享數(shù)據(jù)以下情況可使用表（1）數(shù)據(jù)是臨時（2）數(shù)據(jù)的唯一方式是通過Hive，而且要用Hive完控制數(shù)據(jù)和數(shù)據(jù)的生命周期為什么要有外部表的存在表不方便和其他工作共享數(shù)據(jù)例如，假設(shè)我們有一份ig或者其他工具創(chuàng)建并且主要由這一工具使用的數(shù)，時們想用iv在這份數(shù)據(jù)上執(zhí)行一些查詢，可是并沒有給予iv對數(shù)據(jù)的所，我們可以創(chuàng)建一個外部指這數(shù)并需對具所 外部表-例1：創(chuàng)建學(xué)生表studentwai，列以逗號","createexternaltableidnamestring,genderstring)rowformatfieldsterminatedby','location'/my/studentwai';

外部表-例2：使用describeextendedtableName語句的輸出中查看describeextendedtableType：EXTERNAL_TABLE說明是外部表tableType：MANAGED_TABLE說明是表外部表-拷貝例3：對外部表進行表結(jié)構(gòu)的，不會數(shù)createexternaltableifnotexistslikelocation外部表-例4：刪除外部droptableifexistsdfs-ls可以看到HDFS的 下的數(shù)據(jù)仍然存在。也就是說除外部表只會刪除表的元數(shù)據(jù)信息，并不能刪除數(shù)據(jù)文件表及外部表表及外部表1、在本地/tmp準(zhǔn)備一個文本文件northdakotanewyorknewjersey2、上傳到HDFS的/user/demo/states3、創(chuàng)建一個表states_interalstates.txt中的數(shù)據(jù)4、查看表states_interal的描述5、查詢表states_interal中的數(shù)6、在/user/demo/states中準(zhǔn)備另一個文件，內(nèi)容自7、再次查看states_interal表中的數(shù)10、查詢外部表中的數(shù)14、刪除表states_interal15、查詢外部表states_external中的數(shù)vi/tmp/states.txt northdakotanewyorknewjersey2、上傳到HDFS的/user/demo/statesdfs-put/tmp/states.txt/user/demo/statesdfs-ls/user/demo/states3、創(chuàng)建一個表states.txt中的數(shù)據(jù)createtablestates_internal(statestring)location4、查看表的描describeformatted5、查詢表中的數(shù)select*from6、在/user/demo/states中準(zhǔn)備另一個文vidfs-put/tmp/morestates.txtselect*fromstates_internal;可以看到兩個文本的數(shù)據(jù)都可以查8、創(chuàng)建一個外部createexternaltablestates_external(statestring)9、查看外部表的describeformattedselect*fromstates_external;現(xiàn)在，我們在同一數(shù)據(jù)集上創(chuàng)建了兩張表。可以使用任何一張表查詢同樣的數(shù)據(jù)。11、再次創(chuàng)建另一張外部createexternaltablestates_external2(statestring)droptablestates_external2select*from可見，刪除一個外部表并不會影響底層數(shù)據(jù)14、刪除表drop 查看數(shù)據(jù)，發(fā) 表刪除時連同底層數(shù)據(jù)一起刪除表（管理表）表（管理表外部未被external修飾的是（managed被external修飾的為外部（external數(shù)據(jù)由Hive自身管數(shù)據(jù)由HDFS管外部表數(shù)據(jù)的位置由自己指刪除表會直接刪除元數(shù)（metadata）及數(shù)刪除外部表僅僅會刪除元數(shù)據(jù)HDFS上的文件并不會被刪對表的修改會將修改直接同給元數(shù)則需要修復(fù)（MSCKREPAIRTABLEtable_name;）為什么要有分區(qū)表在HiveSlepaition概念。分區(qū)是表中部分列的集合，可以為頻繁使用的數(shù)據(jù)建立區(qū)，有利于提高查詢效率每個分區(qū)表對應(yīng)數(shù)據(jù)庫中相應(yīng)分區(qū)列的一個索引，每個區(qū)表對應(yīng)著表下的一 一個表可以在多個維度上進行分區(qū)，并且分區(qū)可以嵌套使TITDBY子句指定。例1：創(chuàng)建分區(qū)表第一步，創(chuàng)建分createtabletslinepartitionedby(dtstring,countrystring)rowformatdelimitedfieldsterminatedby例1在linux提示符下數(shù)據(jù)內(nèi)容如下 o o o例1loaddatalocalinpath'/file.txt'intotablelogspartition(dt='2019-03-02',country='china');load操作只是將數(shù)據(jù)到HIVE表對應(yīng)位置，自動創(chuàng)建一文件file.txtdfs-ls可一層 查看，最終可看到數(shù)據(jù)文件例1vi4,byeworld5,byehive6,byeloaddatalocalinpath'/file1.txt'intotablepartition(dt='2019-03-例1再次一層層查看HDFS上 和文dfs-ls發(fā)現(xiàn)logs下有例2：創(chuàng)建p表，該表的數(shù)據(jù)來源于員工信息數(shù)據(jù)文件，并且以省份povin和城市it作為分區(qū)字段。員工信息數(shù)據(jù)文錯誤的導(dǎo)入數(shù)據(jù)到分區(qū)表loaddatalocalinpathintotableemp;報錯如下FAILED:SemanticException[Error10062]:Needtospecifypartitioncolumnsbecausethedestinationtableispartitioned正確的導(dǎo)入數(shù)據(jù)到分區(qū)表loaddatalocalinpath'/data/emp/emp_guangdong_foshan.txt'intotableemppartition(province='guangdong',city='foshan')分區(qū)表改變了Hv對數(shù)據(jù)的組織方式。將所有數(shù)據(jù)加載到對應(yīng)的分區(qū)后能在HDS中查看到可以反映分區(qū)結(jié)構(gòu)的子 。在將數(shù)據(jù)加載到表內(nèi)之前，需要數(shù)據(jù)加載明確知道所加載的數(shù)據(jù)屬于哪一個分區(qū)。使用分區(qū)在某些應(yīng)用場景下能給有效地提高性能，當(dāng)只需注意：PARTITONEDBY子句中定義的列是表中正式的建好分區(qū)表，想要查詢廣州的所有員工信息，是不是就更快速了。select*fromempwhereprovince='guangdong'and對數(shù)據(jù)進行分區(qū)，最重要的原因就是為了更快的查詢。上面的select語句將結(jié)果范圍限制在廣州文件夾的查詢中，僅僅需要掃描一個 下的內(nèi)容即可，即使我們成千上萬個城市區(qū)對非大數(shù)集分可以顯著提高查詢性能。hivehivehiv提供了一個ic嚴格模式，可以防止用戶執(zhí)行那些可能產(chǎn)生意想不到的不好的效果的查詢。即某些查詢在嚴格模式下無法執(zhí)行。通過設(shè)置hive.apd.ode的值為ic。在ic模式下對分區(qū)表進行查詢，除非語句中包含分區(qū)字段過濾條件來顯示數(shù)據(jù)范圍，否則不允許執(zhí)行。進行這個限制的原因是，通常分區(qū)表都擁有非常大的數(shù)sethive.mapred.mode=strict;（將hive設(shè)置成strict模式sethive.mapred.mode=nonstrict;（將hive設(shè)置strict式在strict模式下查詢所有分區(qū)會報select*fromFAILED:SemanticExceptionQueriesagainstpartitionedtableswithoutapartitionfilteraredisabledforsafetyreasons.Ifyouknowwhatyouareng,pleasemakesurethathive.strict.checks.large.queryissettofalseandthathive.mapred.modeisnotsetto'strict'toenablethem.NopartitionpredicateforAlias"emp"Table"emp" rtitionsdescribeextended外部表同樣可以使用分區(qū)。事實上，這是管理大型生產(chǎn)數(shù)據(jù)具共享數(shù)據(jù)的方式，同時也可以優(yōu)化查詢性能。例3：每天產(chǎn)生大量的日志文件，日志件按天進行劃分創(chuàng)建外部分區(qū)表createexternaltablelog_messages(hmsint,messagepartitionedby(yearint,monthint,dayint)rowformatdelimitedfieldsterminatedby例4：對于外部分區(qū)表我們可以使用altertable語句單獨進altertablelog_messagesaddlocation接下來，我們只需要將當(dāng)天的日志存放 中即可例5：增加表多個分altertablelog_messagespartition(year='2019',month='01',day='02')location'/test/log_messages/2019/01/02'partition(year='2019',month='01',day='03')location'/test/log_messages/2019/01/03'例6：修改某個分區(qū)的路altertablelog_messagessetlocation'/test/log_messages/new'例7：刪除某個分區(qū)的路altertablelog_messagesdrop對于管理表，即使使用altertable…addpartition語句增加的例8：表重命altertablelog_messagesrenameto例9：增加altertablelogmsgsadd(severitystring comment'yanzhongxing',session_idintcomment'huihua')新增的字段在分區(qū)字段之前已有字段之后例9：修改列信altertablechangecolumn comment'thehoursminutes,andsecondspartoftheaft