12變化日期變化內(nèi)容撰寫(xiě)人V1.02010-12-15起草陳曉軍3目錄 61.1HIVE架構(gòu) 6 71.3Hive和普通關(guān)系數(shù)據(jù)庫(kù)的異同 81.4HIVE元數(shù)據(jù)庫(kù) 9 9 1.5HIVE的數(shù)據(jù)存儲(chǔ) 2.1createtable 2.1.1總述 2.1.2語(yǔ)法 2.1.3基本例子 2.1.4創(chuàng)建分區(qū) 2.1.5其它例子 2.2AlterTable 2.2.1AddPartitions 2.2.2DropPartitions 2.2.4ChangeColumn 2.2.5Add/ReplaceColumns 2.5Load 2.6Insert 2.6.1InsertingdataintoHiveTablesfromqueries 2.6.2Writingdataintofilesystemfromqueries 2.7.3HiveResources 2.7.4調(diào)用python、shell等語(yǔ)言 2.9.1Limit 2.9.2Topk 3.1GroupBy 4.HiveJoin 4 6.1基本函數(shù) 6.1.1關(guān)系操作符 6.1.2代數(shù)操作符 6.1.3邏輯操作符 6.1.4復(fù)雜類型操作符 6.1.5內(nèi)建函數(shù) 6.1.6數(shù)學(xué)函數(shù) 6.1.7集合函數(shù) 6.1.8類型轉(zhuǎn)換 6.1.9日期函數(shù) 6.1.10條件函數(shù) 6.1.11字符串函數(shù) 6.2UDTF 6.2.1Explode 42 8.使用HIVE注意點(diǎn) 8.1字符集 8.3count(distinct) 8.7子查詢 8.8Join中處理null值的語(yǔ)義區(qū)別 9.優(yōu)化與技巧 9.1全排序 9.1.1例1 9.1.2例2 9.2怎樣做笛卡爾積 9.3怎樣寫(xiě)exist/in子句 9.4怎樣決定reducer個(gè)數(shù) 9.5合并MapReduce操作 9.6Bucket與sampling 9.8.3大表Join的數(shù)據(jù)偏斜 9.9合并小文件 5 6不Executor)TrackerTracker一Web不7●Hadoop:用HDFS進(jìn)行存儲(chǔ)，利用MapReduce進(jìn)行計(jì)算1、用戶接口主要有三個(gè)：CLI,Client和WUI。其中最常用的是CLI,Cli啟動(dòng)的時(shí)候，會(huì)同時(shí)啟動(dòng)一個(gè)Hive副本。Client是Hive的客戶端，用戶連接至HiveServer。表的列和分區(qū)及其屬性，表的屬性(是否為外部表等),表的數(shù)據(jù)所在目錄等。查詢計(jì)劃的生成。生成的查詢計(jì)劃存儲(chǔ)在HDFS中，并在隨后有MapReduce調(diào)用執(zhí)行。完成(包含*的查詢，比如select*fromtbl不會(huì)生成MapRedcue任務(wù))。WebUIWebUISOptimizerThriftAPI●HQL中對(duì)查詢語(yǔ)句的解釋、優(yōu)化、生成查詢計(jì)劃是由Hive完成的●查詢計(jì)劃被轉(zhuǎn)化為MapReduce任務(wù)，在Hadoop中執(zhí)行(有些查詢沒(méi)有MR81.3Hive和普通關(guān)系數(shù)據(jù)庫(kù)的異同HiveRDBMS查詢語(yǔ)言HQL數(shù)據(jù)存儲(chǔ)HDFSRawDeviceorLocalFS無(wú)有執(zhí)行MapReduceExcutor執(zhí)行延遲高低處理數(shù)據(jù)規(guī)模大小1.查詢語(yǔ)言。由于SQL被廣泛的應(yīng)用在數(shù)據(jù)倉(cāng)庫(kù)中，因此，專門(mén)針對(duì)Hive的特性設(shè)計(jì)的。而數(shù)據(jù)庫(kù)則可以將數(shù)據(jù)保存在塊設(shè)備或者本地文件系統(tǒng)中。3.數(shù)據(jù)格式。Hive中沒(méi)有定義專門(mén)的數(shù)據(jù)格式，數(shù)據(jù)格式可以由用戶指定，用戶定義數(shù)據(jù)格式需要指定三個(gè)屬性：列分隔符(通常為空格、"\t”、”\x001")、行分隔符("\n")以及讀取文件數(shù)據(jù)的方法(Hive中默認(rèn)有三個(gè)文件格式TextFile,SequenceFile以及RCFile)。由于在加載數(shù)據(jù)的過(guò)程中，不需要從用戶數(shù)據(jù)格式到Hive定義的數(shù)據(jù)格式的轉(zhuǎn)換，因此，Hive在加載的過(guò)程中不會(huì)對(duì)數(shù)據(jù)本身進(jìn)行任何修改，而只是將數(shù)據(jù)內(nèi)容復(fù)制或者移動(dòng)到相應(yīng)的HDFS目錄中。而在數(shù)據(jù)庫(kù)中，不同的數(shù)據(jù)庫(kù)有不同的存儲(chǔ)引擎，定義了自己的數(shù)據(jù)格式。所有數(shù)據(jù)都會(huì)按照一定的組織存儲(chǔ)，因此，數(shù)據(jù)庫(kù)加載數(shù)據(jù)的過(guò)程會(huì)比較耗時(shí)。4.數(shù)據(jù)更新。由于Hive是針對(duì)數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用設(shè)計(jì)的，而數(shù)據(jù)倉(cāng)庫(kù)的內(nèi)容是讀多寫(xiě)少的。5.索引。之前已經(jīng)說(shuō)過(guò)，Hive在加載數(shù)據(jù)的過(guò)程中不會(huì)對(duì)數(shù)據(jù)進(jìn)行任何處理，甚至不會(huì)對(duì)數(shù)據(jù)進(jìn)行掃描，因此也沒(méi)有對(duì)數(shù)據(jù)中的某些Key建立索引。Hive要訪問(wèn)數(shù)據(jù)中滿足條件的特定值時(shí)，需要暴力掃描整個(gè)數(shù)據(jù)，因此訪問(wèn)延遲較高。由于MapReduce的引入，Hive可以并行訪問(wèn)數(shù)據(jù)，因此即使沒(méi)有索引，對(duì)于大數(shù)據(jù)量的訪問(wèn)，Hive仍然可以體現(xiàn)出優(yōu)勢(shì)。數(shù)據(jù)庫(kù)中，通常會(huì)針對(duì)一個(gè)或者幾個(gè)列建立索引，因此對(duì)于少量的特定條件的數(shù)據(jù)的訪問(wèn)，數(shù)據(jù)庫(kù)可以有很高的效率，較低的延遲。由于數(shù)據(jù)的訪問(wèn)延遲較高，決定了Hive不適合在線數(shù)據(jù)查詢。6.執(zhí)行。Hive中大多數(shù)查詢的執(zhí)行是通過(guò)Hadoop提供的MapReduce來(lái)實(shí)現(xiàn)的(類似select*fromtbl的查詢不需要MapReduce)。而數(shù)據(jù)庫(kù)通常有自己的執(zhí)行引擎。7.執(zhí)行延遲。之前提到，Hive在查詢數(shù)據(jù)的時(shí)候，由于沒(méi)有索引，需要掃描整個(gè)表，因此延遲較高。另外一個(gè)導(dǎo)致Hive執(zhí)行延遲高的因素是MapReduce框架。由于9MapReduce本身具有較高的延遲，因此在利用MapReduce執(zhí)行Hive查詢時(shí)，也會(huì)有較高的延遲。相對(duì)的，數(shù)據(jù)庫(kù)的執(zhí)行延遲較低。當(dāng)然，這個(gè)低是有條件的，即數(shù)據(jù)規(guī)模較小，當(dāng)數(shù)據(jù)規(guī)模大到超過(guò)數(shù)據(jù)庫(kù)的處理能力的時(shí)候，Hive的并行計(jì)算顯然能體現(xiàn)出8.可擴(kuò)展性。由于Hive是建立在Hadoop之上的，因此Hive的可擴(kuò)展性是和Hadoop的可擴(kuò)展性是一致的(世界上最大的Hadoop集群在Yahoo!,2009年的規(guī)模在4000臺(tái)節(jié)點(diǎn)左右)。而數(shù)據(jù)庫(kù)由于ACID語(yǔ)義的嚴(yán)格限制，擴(kuò)展行非常有限。目前最先進(jìn)的并行數(shù)據(jù)庫(kù)Oracle在理論上的擴(kuò)展能力也只有100臺(tái)左右。9.數(shù)據(jù)規(guī)模。由于Hive建立在集群上并可以利用MapReduce進(jìn)行并行計(jì)算，因此可以支持很大規(guī)模的數(shù)據(jù)；對(duì)應(yīng)的，數(shù)據(jù)庫(kù)可以支持的數(shù)據(jù)規(guī)模較小。啟動(dòng)HIVE的元數(shù)據(jù)庫(kù)進(jìn)入到hive的安裝目錄1、啟動(dòng)derby數(shù)據(jù)庫(kù)/home/admin/caona/hive/build/dist/2、連接Derby數(shù)據(jù)庫(kù)進(jìn)行測(cè)試<name>javax.jdo.option.ConnectionURL</name><description>JDBCconnectstringforaJDBCmetastore</description>進(jìn)入derby安裝目錄/home/admin/caona/hive/build/dist/db-derby--bin/bin輸入./ijConnect'jdbc:derby://hadoop1:1527/metastore3、元數(shù)據(jù)庫(kù)數(shù)據(jù)字典表名說(shuō)明關(guān)聯(lián)鍵BUCKETING_COLSCOLUMNSHive表字段信息(字段注釋，字段名，字段類型，字段序號(hào))元數(shù)據(jù)庫(kù)信息，存放HDFS路徑信息DB_IDHive分區(qū)表分區(qū)鍵所有hive表、表分區(qū)所對(duì)應(yīng)的hdfs數(shù)據(jù)目錄和數(shù)據(jù)格式。序列化反序列化信息，如行分隔符、列分隔符、NULL的表示字符等SEQUENCE_TABLE表保存了hive對(duì)象的下一個(gè)可用ID,如'org.apache.hadoop.hive.metastore.model.MTable',21,則下一個(gè)新創(chuàng)建的hive表其TBL_ID就是21,同時(shí)SEQUENCE_TABLE表中271786被更新為26(這里每次都是+5?)。同樣，COLUMN,PARTITION等都有相應(yīng)的記錄表級(jí)屬性，如是否外部表，表注釋等所有hive表的基本信息從上面幾張表的內(nèi)容來(lái)看，hive整個(gè)創(chuàng)建表的過(guò)程已經(jīng)比較清楚了1.解析用戶提交hive語(yǔ)句，對(duì)其進(jìn)行解析，分解為表、字段、分區(qū)等hive對(duì)象2.根據(jù)解析到的信息構(gòu)建對(duì)應(yīng)的表、字段、分區(qū)等對(duì)象，從建對(duì)象的最新ID,與構(gòu)建對(duì)象信息(名稱，類型等)一同通過(guò)DA0方法寫(xiě)入到元數(shù)據(jù)實(shí)際上我們常見(jiàn)的RDBMS都是通過(guò)這種方法進(jìn)行組織的，典型的如postgresql,其系統(tǒng)表中和hive元數(shù)據(jù)一樣裸露了這些id信息(oid,cid等),而Oracle等商業(yè)化的系統(tǒng)則隱藏了這些具體的ID。通過(guò)這些元數(shù)據(jù)我們可以很容易的讀到數(shù)據(jù)諸如創(chuàng)建一個(gè)表的數(shù)據(jù)字導(dǎo)出建表語(yǔ)句的shell腳本見(jiàn)附一待完成將存放元數(shù)據(jù)的Derby數(shù)據(jù)庫(kù)遷移到Mysql數(shù)據(jù)庫(kù)組織Hive中的表，只需要在創(chuàng)建表的時(shí)候告訴Hive數(shù)據(jù)中的列分隔符和行分隔符，HiveTable,Partition,Bucket。1.Hive中的Table和數(shù)據(jù)庫(kù)中的Table在概念上是類似的，每一個(gè)Table在Hive/warehouse/xiaojun,其中，wh是在hive-site.xml中由指定的數(shù)據(jù)倉(cāng)庫(kù)的目錄，所有的Table數(shù)據(jù)(不包2.Partition對(duì)應(yīng)于數(shù)據(jù)庫(kù)中的Partition列的密集索引，但是Hive中Partition的組織方式和數(shù)據(jù)庫(kù)中的很不相同。在Hive中，表中的一個(gè)Partition對(duì)應(yīng)于表下的一和city兩個(gè)Partition,則對(duì)應(yīng)于dt=20100801,ctry=US的HDFS子目錄為：/warehouse/xiaojun/dt=20100801/ctry=US;對(duì)應(yīng)于dt=20100801,ctry=CA的HDFS3.Buckets對(duì)指定列計(jì)算hash,根據(jù)hash值切分?jǐn)?shù)據(jù)，目的是為了并行，每一個(gè)Bucket對(duì)應(yīng)一個(gè)文件。將user列分散至32個(gè)bucket,首先對(duì)user列的值計(jì)算hash,對(duì)應(yīng)hash值為0的HDFS目錄為：/wa=20100801/ctry=US/part-00/xiaojun/dt=20100801/ctry=US/part-000204.ExternalTable指向已經(jīng)在HDFS中存在的數(shù)據(jù)，可以創(chuàng)建Partition。它和Table在元數(shù)據(jù)的組織上是相同的，而實(shí)際數(shù)據(jù)的存儲(chǔ)則有較大的差異?！馮able的創(chuàng)建過(guò)程和數(shù)據(jù)加載過(guò)程(這兩個(gè)過(guò)程可以在同一個(gè)語(yǔ)句中完成),在加載數(shù)據(jù)的過(guò)程中，實(shí)際數(shù)據(jù)會(huì)被移動(dòng)到數(shù)據(jù)倉(cāng)庫(kù)目錄中；之后對(duì)數(shù)據(jù)對(duì)訪問(wèn)將會(huì)直接在數(shù)據(jù)倉(cāng)庫(kù)目錄中完成。刪除表時(shí)，表中的數(shù)據(jù)和元數(shù)據(jù)將會(huì)被同時(shí)刪除?！馝xternalTable只有一個(gè)過(guò)程，加載數(shù)據(jù)和創(chuàng)建表同時(shí)完成(CREATEEXTERNTABLE…..LOCATION),實(shí)際數(shù)據(jù)是存儲(chǔ)在LOCATION后面指定的HDFS路徑中，并不會(huì)移動(dòng)到數(shù)據(jù)倉(cāng)庫(kù)目錄中。當(dāng)刪除一個(gè)ExternalTable時(shí)，僅刪除sh$HIVE_HOME/bin/hive--servicehwihadoopfs-text/user/admin/daiqf/createspufp/input/cateinfo|head2.1.1總述●CREATETABLE創(chuàng)建一個(gè)指定名字的表。如果相同名字的表已經(jīng)存在，則拋出異常；用戶可以用IFNOTEXIST選項(xiàng)來(lái)忽略這個(gè)異常?！馝XTERNAL關(guān)鍵字可以讓用戶創(chuàng)建一個(gè)外部表，在建表的同時(shí)指定一個(gè)指向?qū)嶋H數(shù)據(jù)的外部表，僅記錄數(shù)據(jù)所在的路徑，不對(duì)數(shù)據(jù)的位置做任何改變。在刪除表的時(shí)候，內(nèi)部表的元數(shù)據(jù)和數(shù)據(jù)會(huì)被一起刪除，而外部表只刪除元數(shù)據(jù)，不刪除數(shù)據(jù)。●LIKE允許用戶復(fù)制現(xiàn)有的表結(jié)構(gòu)，但是不復(fù)制●用戶在建表的時(shí)候可以自定義SerDe或者使用自帶的SerDe。如果沒(méi)有指定ROW將會(huì)使用自帶的SerDe。在建表的時(shí)候，用戶還需要為表指定列，用戶在指定表的列的同時(shí)也會(huì)指定自定義的SerDe,Hive通過(guò)SerDe確定表的具體的列的數(shù)據(jù)?！袢绻募?shù)據(jù)是純文本，可以使用STOREDASTEXTFILE。如果數(shù)據(jù)需要壓縮，使用STOREDASSEQUENCE?！裼蟹謪^(qū)的表可以在創(chuàng)建的時(shí)候使用PARTITIONEDBY語(yǔ)句。一個(gè)表可以擁有一個(gè)或者多個(gè)分區(qū)，每一個(gè)分區(qū)單獨(dú)存在一個(gè)目錄下。而且，表和分區(qū)都可以對(duì)某個(gè)列進(jìn)行CLUSTEREDBY操作，將若干個(gè)列放入一個(gè)桶(bucket)中。也可以利用SORTBY對(duì)數(shù)據(jù)進(jìn)行排序。這樣可以為特定應(yīng)用提高性能?！癖砻土忻粎^(qū)分大小寫(xiě)，SerDe和屬性名區(qū)分大小寫(xiě)。表和列的注釋是字符串。2.1.2語(yǔ)法CREATE[EXTERNAL]TABLE[IFNOTEXISTS]table_name[(col_namedata_type[COMMENTcol_comment],...)][COMMENTtable_comment][PARTITIONEDBY(col_namedata_type[COMMENT[CLUSTEREDBY(col_name,col_name,...)[SORTED[ASC|DESC],...)]INTOnum_bucketsBUCKETS][[ROWFORMATrow_format][STOREDASfile_format]col_comment],...)]BY(col_name||STOREDBY''[WITHSERDEPROPERTIES][LOCATIONhdfs_path][TBLPROPERTIES(property_name=property_value,...)](Note:onlyavailablestartingwith0.6.0)with0.5.0.)CREATE[EXTERNAL]TABLE[IFNOTEXISTS]table_nameLIKEexistingtablename[LOCATIONhdfs_path]map_typeprimitive_typeSMALLINTBIGINTBOOLEANFLOATDOUBLEarraytype:ARRAY<data_type>map_type:MAP<primitive_type,data_type>:STRUCT<col_name:data_type[COMMENTcol_comment],...>rowformat:DELIMITED[FIELDSTERMINATEDBYchar][COLLECTIONITEMSTERMINATEDBYchar][MAPKEYSTERMINATEDBYchar][LINESTERMINATEDBYchar]SERDEserde_name[WITHSERDEPROPERTIESfilefileformat::SEQUENCEFILETEXTFILEINPUTFORMATinput_format_classnameOUTPUTFORMATstatus/option/type/quantity樣的字段2.1.3基本例子1、如果一個(gè)表已經(jīng)存在，可以使用ifnotexists2、createtablexiaojun(idint,contstring)rowformatdelimitedfieldsterminatedby'\005'storedastextfile;terminatedby:關(guān)于來(lái)源的文本數(shù)據(jù)的字段間隔符如果要將自定義間隔符的文件讀入一個(gè)表，需要通過(guò)創(chuàng)建表的語(yǔ)句來(lái)指明輸入文件間隔符，然后loaddata到這個(gè)表。4、Alibaba數(shù)據(jù)庫(kù)常用間隔符的讀取我們的常用間隔符一般是Ascii碼5,Ascii碼7等。在hive中Ascii碼5用’\005’表示，Ascii碼7用'\007'表示，依此類推。5、裝載數(shù)據(jù)LOADDATAINPATH'/user/admin/xiaojun/a.txt'OVERWRITEINTOTABLExiaojun;6、如果使用external建表和普通建表區(qū)別A、指定一個(gè)位置，而不使用默認(rèn)的位置。如：createEXTERNALtablexiaojun(idint,contstring)rowterminatedby'\005'storedastextfilelocaticheck結(jié)果ij>selectLOCATIONfromtblsa,sdsbwherea.sd_id=b.sd_idandtbl_name='xiaojun';hdfs://hadoop1:7000/user/admin/xiaojunij>selectLOCATIONfromtblsa,sdsbwherea.sd_id=b.sd_idandtbl_name='c';LOCATIONhdfs://hadoop1:7000/user/hive/warehouse/cB、對(duì)于使用createtableexternal建表完成后，再drop掉表，表中的數(shù)據(jù)還在文件系統(tǒng)hive>createEXTERNALtablexiaojun(idint,contstring)rowformatdelimitedfieldsterminatedby'\005'storedastextfile;OKhive>LOADDATAINPATH'/user/admin/xiaojun'OVERWRITEINTOTABLExiaojun;LoadingdatatotablexiaojunOKhive>droptablexiaojun;[admin@hadooplbin]$./hadoopfs-lshdfs://hadoop1:7000/user/hive/warehouse/xiaojun2.1.4創(chuàng)建分區(qū)HIVE的分區(qū)通過(guò)在創(chuàng)建表時(shí)啟用partitionby實(shí)現(xiàn)，用來(lái)partition的維度并不是實(shí)際數(shù)據(jù)的某一列，具體分區(qū)的標(biāo)志是由插入內(nèi)容時(shí)給定的。當(dāng)要查詢某一分區(qū)的內(nèi)容時(shí)可以采用where語(yǔ)句，形似wheretablename.partition_key>a來(lái)實(shí)現(xiàn)。CREATETABLEpageview(viewTimeINT,useridBpageurlSTRING,referrerurlipSTRINGCOMMENT'IPAddressoftheUser')COMMENT'Thisisthepageviewtable'PARTITIONEDBY(dtSTRING,countrySTRING)CLUSTEREDBY(userid)SORTEDBY(viewTime)INTO32BUCKETSROWFORMATDELIMITEDFIELDSTERMINATEDBY'\001'COLLECTIONITEMSTERMINATEDBY'\002'MAPKEYSTERMINATEDBY'\003'STOREDASSEQUENCEFILE;Eg:(yyyymmddstring,idINT,countrystring,cookie_idstring,page_idstring,clickstaturlidint,query_stringstring,referstring)PARTITIONEDBY(dtSTRING)裝載數(shù)據(jù)：PARTITION(dt='20101101');訪問(wèn)某一個(gè)分區(qū)SELECTcount(*)FROMc02_clickstat_fatdt1aWHEREa.dt>='20101101'ANDa.dt<'20101102';1、指定LOCATION位置CREATEEXTERNALTABLEpage_view(viewTimeINT,useridBIGINT,pageurlSTRING,referrerurlSTRING,ipSTRINGCOMMENT'IPAddressoftheUser',countrySTRINGCOMMENT'countryoforigination')COMMENT'Thisisthestagingpageviewtable'ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\054'STOREDASTEXTFILELOCATION'<hdfs_location>';2、復(fù)制一個(gè)空表CREATECREATETABLEempty_key_value_store'locationl']partitionspec[LOCATION'location2']...partition_spec::PARTITION(partition_col=partition_col_value,partition_col=partiton_col_value,...)ALTERTABLEc02_clickstat_fatdt1ADDPARTITION(dt='20101202')'/user/hive/warehouse/c02_clickstat_fatdt1/part20101202'PARTITION(dt='20101203')'/user/hive/warehouse/c02_clickstat_fatdt1/part20101203';locationALTERTABLEtable_nameDROPpartition_spec,partition_spec,...ALTERTABLEc02_clickstat_fatdt1DROPPARTITION(dt='20101202');ALTERTABLEtablenameRENAMETOnewtablename這個(gè)命令可以讓用戶為表更名。數(shù)據(jù)所在的位置和分區(qū)名并不改變。換而言之，老的表名并未“釋放”,對(duì)老表的更改會(huì)改變新表的數(shù)據(jù)。ALTERTABLEtable_column_type[COMMENTcol_comment][FIRST|AFTERcolumn_name]這個(gè)命令可以允許改變列名、數(shù)據(jù)類型、注釋、列位置或者它們的任意組合2.2.5Add/ReplaceColumnsALTERTABLEtable_nameADD|REPLACECOLUMNS(col_namedata_type[COMMENTcol_comment],...)ADD是代表新增一字段，字段位置在所有列后面(partition列前);REPLACE則是表示替換表中所有字段。hive>descxi;Timetaken:0.061secondshive>createtablexibaklikexi;Timetaken:0.157secondshive>altertablexibakreplacecolumns(ins_datestring);Timetaken:0.109secondshive>descxibak;column_comment],...)][COMMENTview_comment][TBLPROPERTIES(property_name=property_value,...)]2.4Show查看表名查看表名，部分匹配TABLES'page.*';TABLES'.*view';查看某表的所有Partition,如果沒(méi)有就報(bào)錯(cuò)：SHOWPARTITIONSpage_view;查看某表結(jié)構(gòu)：DESCRIBEDESCRIBEinvites;查看分區(qū)內(nèi)容查看分區(qū)內(nèi)容查看有限行內(nèi)容，同Greenplum,用limit關(guān)鍵詞SELECTSELECTa.fooFROMinvitesa查看表分區(qū)定義DESCRIBEEXTENDEDpage_viewPARTITION(ds='2008-08-08');2.5LoadHIVE裝載數(shù)據(jù)沒(méi)有做任何轉(zhuǎn)換加載到表中的數(shù)據(jù)只是進(jìn)入相應(yīng)的配置單元表的位置移動(dòng)數(shù)據(jù)文件。純加載操作復(fù)制/移動(dòng)操作。3.1語(yǔ)法LOADDATA[LOCAL]INPATH'filepath'[OVERWRITE]INTOTABLEtablename[PARTITION(partcoll=vall,partcol2=val2...)]Load操作只是單純的復(fù)制/移動(dòng)操作，將數(shù)據(jù)文件移動(dòng)到Hive表對(duì)應(yīng)的位置。o相對(duì)路徑，例如：project/datalo包含模式的完整URI,例如：hdfs://namenode:9000/user/hive/project/datal區(qū)的分區(qū)名。應(yīng)的目錄中)或者是一個(gè)目錄(在這種情況下，Hive會(huì)將目錄中的所有文件移動(dòng)至表所對(duì)應(yīng)的目錄中)。oload命令會(huì)去查找本地文件系統(tǒng)中的filepath。如file:///user/hive/project/data1.對(duì)應(yīng)的位置。o如果沒(méi)有指定schema或者authority,Hive會(huì)使用在hadoop配置文件中定義的schema和authority,指定o如果路徑不是絕對(duì)的，Hive相對(duì)于/user/進(jìn)行解釋。oHive會(huì)將filepath中指定的文件內(nèi)容移動(dòng)到table(或者關(guān)鍵字，則目標(biāo)表(或者分區(qū))中的內(nèi)容(如果有)會(huì)被刪除，然后再將filepath指向的文件/目錄中的內(nèi)容添加到表/分區(qū)中?！袢绻繕?biāo)表(分區(qū))已經(jīng)有一個(gè)文件，并且文件名和filepath中的文件名沖突，那么現(xiàn)有的文件會(huì)被新文件所替代。從本地導(dǎo)入數(shù)據(jù)到表格并追加原表LOADDATALOCALINPATH`/tmp/pv_2008-06-08_us.txt`INTOTABLEc02PARTITION(date='2008-06-08',country='US')從本地導(dǎo)入數(shù)據(jù)到表格并追加記錄LOADLOADDATALOCALINPATH'./examples/files/kv1.txt'INTOTABLEpokes;從hdfs導(dǎo)入數(shù)據(jù)到表格并覆蓋原表'/user/admin/SqlldrDat/CnClickstat/20101101/18/clickstatgpfatdt0/0'INTOtablec02_clickstat_fatdt1OVERWRITEPARTITIO21關(guān)于來(lái)源的文本數(shù)據(jù)的字段間隔符如果要將自定義間隔符的文件讀入一個(gè)表，需要通過(guò)創(chuàng)建表的語(yǔ)句來(lái)指明輸入文件間隔符，然后loaddata到這個(gè)表就ok了。2.6.1InsertingdataintoHiveTablesfromqueriespartcol2=val2...)]selectstatement1FROMfromstatementHiveextension(multipleinserts):partcol2=val2...)]selectstatement1selectstatement2]...Hiveextension(dynamicpartitioninserts):INSERTOVERWRITETABLEtablenamePARTITION(partcol1[=vall],partcol2[=val2]...)selectstatementFROMfromstatementInsertInsert時(shí)，from子句既可以放在select子句后，也可以放在insert子句前，下面兩句是等價(jià)的hive>FROMinvitesaINSERTOVERWRITETABLEeventsSELECTa.bar,hive>INSERTOVERWRITETABLEeventsSELECTa.bar,count(*)FROMinvitesaWHEREa.foo>0GROUPBYa.bar;hive沒(méi)有直接插入一條數(shù)據(jù)的sql,不過(guò)可以通過(guò)其他方法實(shí)現(xiàn)：假設(shè)有一張表B至少有一條數(shù)據(jù)，我們想向表A(int,string)中插入一條數(shù)據(jù)，可以用下面的方法實(shí)現(xiàn)：fromBinserttableAselect1,'abc'limit1;我覺(jué)得hive好像不能夠插入一個(gè)記錄，因?yàn)槊看文銓?xiě)insert語(yǔ)句的時(shí)候都是要將整個(gè)表的值overwrite。我想這個(gè)應(yīng)該是與hive的storagelayer是有關(guān)系的，因?yàn)樗拇鎯?chǔ)層是HDFS,插入一個(gè)數(shù)據(jù)要全表掃描，還不如用整個(gè)表的替換來(lái)的快些。Hive不支持一條一條的用insert語(yǔ)句進(jìn)行插入操作，也不支持update的操作。數(shù)據(jù)是以load的方式，加載到建立好的表中。數(shù)據(jù)一旦導(dǎo)入，則不可修改。要么drop掉整個(gè)表，要么建2.6.2WritingdataintofilesystemfromqueriesStandardStandardsyntax:INSERTOVERWRITE[LOCAL]DIRECTORYdirectory1SELECT...FROM...Hiveextension(multipleinserts):[INSERTOVERWRITE[LOCAL]DIRECTORYdirectory2selectstatement2]...導(dǎo)出文件到本地INSERTINSERTOVERWRITELOCALDIRECTORY'/tmp/local_out'SELECTa.*FROMpokesINSERTINSERTOVERWRITEDIRECTORY'/user/admin/SqlldrDat/CnClickstat/20101101/19/clickstatgpfatdt0/0'SELECTa.*FROMc02clickstatfatdt1aWHEREdt='20101201';一個(gè)源可以同時(shí)插入到多個(gè)目標(biāo)表或目標(biāo)文件，多目標(biāo)一個(gè)源可以同時(shí)插入到多個(gè)目標(biāo)表或目標(biāo)文件，多目標(biāo)insert可以用一句話來(lái)完成FROMsrcINSERTOVERWRITETABLEdest1SELECTsrc.*WHEREsrc.key<100INSERTOVERWRITETABLEdest2SELECTsrc.key,src.valueWHEREsrc.key>=INSERTOVERWRITETABLEdest3PARTITION(ds='2008-04-08',hr='12')SELECTsrc.keyWHEREsrc.key>=200andsrc.key<300INSERTOVERWRITELOCALDIRECTORY'/tmp/dest4.out'SELECTsrc.valueWHEREsrc.key>=300;insertoverwritetabletest2select'1,2,3'limit1insertoverwritetabledselect'4,5,6'limit1;2.7.1HiveCommandlineOptionsUsageUsage:hive[-hiveconfx=y]*[<-ifilename>]*[<-ffilename>|<-equery-string>][-S]-i<filename>automaticallyandsilentlybeforeanyothercommands)-e'quotedquerystring-f<filename>dataisemitted-hiveconfx=yvariables.-eand-fcannotbespecifiedtogether.Intheabsenceoftheseoptions,interactiveshellisstarted.of-icanbeusedtoexecutemultipleinitscripts.Toseethisusagehelp,runhive-hUsethistosethive/hadoopconfiguration運(yùn)行一個(gè)查詢：Exampleofsettinghiveconfigurationvariables$$HIVEHOME/bin/hive-e'selecta.colfromtabla'-hiveconfhive.exec.scratchdir=/home/my/hivescratch-hiveconfmapred.reduce.tasks=32將查詢結(jié)果導(dǎo)出到一個(gè)文件24HIVEHOME/bin/hive-S-e'selectcount(*)fromc02clickstatfatdt1'>運(yùn)行一個(gè)腳本HIVE_HOME/bin/hive-f/home/my/hive-script.sqlExampleofrunningHIVE_HOME/bin/hive-i/home/my/hive-init.sql2.7.2HiveinteractiveShellCommand使用quitorexit退出使用這個(gè)方式來(lái)設(shè)置特定的配置變量的值。有一點(diǎn)需要注意的是，如果你拼錯(cuò)了變量名，CLI將不會(huì)顯示錯(cuò)誤。這將打印的配置變量，如果沒(méi)有指定變量則由顯示HIVE和用戶變量。如setI則顯示i的值，set則顯示hive內(nèi)部變量值Thiswillgiveallpossiblehadoop/hivehive>seti=32;hive>seti;hive>selecta.*fromxiaojuna;hive>!ls;hive>dfs-ls;hive>set$i='4.128160791368.5';2.7.3HiveResourcesHivecanmanagetheadditionofresourcestoasessionwherethoseresourcesneedtobemadeavailableatquaccessiblefilecanbeaddedtothesession.Onceafileisaddedtoasession,hivequerycanrefertothisfilebyitsname(inmap/reduce/transformclauses)andthisfileisavailablelocallyatexecutiontimeontheentirehadoopcluster.HiveusesHadoop'sDistributedCachetodistributetheaddedfilestoallthemachinesintheclusteratqueryexecutiontime.ADDADD{FILE[S]|JAR[S]|ARCHIVE[S]}<filepath1>[<filepath2>]*LIST{FILE[S]|JAR[S]|ARCHIVE[S]}[<filepathl><filepath2>..]DELETE{FILE[S]|JAR[S]|ARCHIVE[S]}[<filepath1><filepath2>..]thismightbesomethinglikeatransformscripttobedistributingthem.Example:●●hive>addFILE/tmp/tt.py;hive>fromnetworksaMAPworkidUSING'pythontt.py'asnnwherea.ds='2009-01-04'limit10;Itisnotneccessartransformscriptarealreadyavailableonallmachinesinthehadoopclusterusingthesamepathname.Forexample:availableonallmachines'/home/nfsserv1/hadoopscripts/tt.py'...:herett.pymaybeaccessibleviaanfsmountpointthat'sconfiguredidenticallyonalltheclusternodes2.7.4調(diào)用python、shell等語(yǔ)言CREATECREATETABLEu_data_new(useridINT,ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t';addFILEweekday_mapper.py;INSERTOVERWRITETABLEudatanewSELECTTRANSFORM(userid,movieid,rating,unixtime)USING'pythonweekday_mapper.py'AS(userid,movieid,rating,weekday)FROMu_data;,其中,其中weekday_mapper.py內(nèi)容如下importdatetimeline=line.strip()userid,movieid,rating,unixtime=line.split('\t')weekday=datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()print'\t'.join([userid,movieid,rating,str(weekday)])如下面的例子則是使用了如下面的例子則是使用了shell的cat命令來(lái)處理數(shù)據(jù)FROMinvitesaINSERTOVERWRITETABLEeventsSELECTTRANSFORM(a.foo,a.bar)AS(oof,rab)USING'/bin/cat'WHEREa.ds>'2008-08-09';刪除一個(gè)內(nèi)部表的同時(shí)會(huì)同時(shí)刪除表的元數(shù)據(jù)和數(shù)據(jù)。刪除一個(gè)外部表，只刪除元數(shù)據(jù)而保留數(shù)據(jù)。2.9.1Limit表中隨機(jī)查詢5條記錄：SELECT*FROMt1LIMIT52.9.2Topk下面的查詢語(yǔ)句查詢銷售記錄最大的5個(gè)銷售代表。SELECT*FROMsalesSORTBYamountDESCLIMIT52.9.3REGEXColumnSpecificationSELECT語(yǔ)句可以使用正則表達(dá)式做列選擇，下面的語(yǔ)句查詢除了外的所有列：SELECT`(ds|hr)?+.+`FROMsales語(yǔ)法：SELECTSELECT[ALL|DISTINCT]select_expr,select_expr,...[GROUPBYcol_list][CLUSTERBYcol_list|[DISTRIBUTEBYcol_list][SORTBYcol_lis]3.1GroupBy基本語(yǔ)法：groupByClausegroupByClause:GROUPBYgroupByExpression(,groupByExpression)*groupByExpression:expressiongroupByQuery:SELECTexpression(,expression)*FROMsrcgroupB高級(jí)特性：高級(jí)特性：量按年齡劃分。如下面的例子：FROMpv_usersINSERTOVERWRITETABLEpv_gender_sumSELECTpv_users.gender,count(DISTINCTpv_users.userid)INSERTOVERWRITEDIRECTORY'/user/facebook/tmp/pv_age_sum'SELECTpv_users.age,count(DISTINCTpv_users.userid)GROUPBYpv_users.age;直接在MAP上的任務(wù)。這通常提供更好的效率，但可能需要更多的內(nèi)存來(lái)運(yùn)行成功。setsethive.map.aggr=true;SELECTCOUNT(*)FROMtable2;PS:在要特定的場(chǎng)合使用可能會(huì)加效率。不過(guò)我試了一下，比直接使用False慢很多。query:SELECTexpression(','expression)*FROMsrcorderBySort順序?qū)⒏鶕?jù)列類型而定。如果數(shù)字類型的列，則排序順序也以數(shù)字順序。如果字符串類型的列，則排序順序?qū)⒆值漤樞颉ortBy:SORTBYcolNamecol0rder?(','colNamecol0rder?)*query:SELECTexpression(','expression)*FROMsrcsortBy語(yǔ)法語(yǔ)法jointable:tablereferenceJOINtablefactor[joincondition]joinconditiontablereference:tablefactortblname[alias]table_subqueryalias(tablereferences)joincondition:ONequalityexpression(ANDequalityexpression)*expression=expressionHive只支持等值連接(equalityjoins)、外連接(outerjoins)和(left/rightjoins)。Hive不支持所有非等值的連接，因?yàn)榉堑戎颠B接非常難轉(zhuǎn)化到map/reduce任務(wù)。另外，Hive支持多于2個(gè)表的連接。寫(xiě)join查詢時(shí)，需要注意幾個(gè)關(guān)鍵點(diǎn)：1、只支持等值joinSELECTa.*FROMaJOINbON(a.id=b.id)SELECTa.*FROMaJOINbON(a.id=b.idANDa.department=b.department)是正確的，然而：SELECTa.*FROMaJOINbON(a.idb.id)是錯(cuò)誤的。1.可以join多于2個(gè)表。例如SELECTa.val,b.val,c.valFROMaJOINbON(a.key=b.key1)JOINcON(c.key=b.key2)如果join中多個(gè)表的joinkey是同一個(gè)，則join會(huì)被轉(zhuǎn)化為單個(gè)map/reduce任務(wù)，SELECTa.val,b.val,c.valFROMaJOINbON(a.key=b.keyl)JOINcON(c.key=b.key1)被轉(zhuǎn)化為單個(gè)map/reduce任務(wù)，因?yàn)閖oin中只使用了b.key1作為joinkey。SELECTa.val,b.val,c.valFROMaJOINbON(a.key=b.key1)JOINcON(c.key=b.key2)而這一join被轉(zhuǎn)化為2個(gè)map/reduce任務(wù)。因?yàn)閎.key1用于第一次join條件，而b.key2用于第二次join。3.join時(shí)，每次map/reduce任務(wù)的邏輯：reducer會(huì)緩存join序列中除了最后一個(gè)表的所有表的記錄，再通過(guò)最后一個(gè)表將結(jié)果序列化到文件系統(tǒng)。這一實(shí)現(xiàn)有助于在reduce端減少內(nèi)存的使用量。實(shí)踐中，應(yīng)該把最大的那個(gè)表寫(xiě)在最后(否則會(huì)因?yàn)榫彺胬速M(fèi)大量?jī)?nèi)存)。例如：SELECTa.val,b.val,c.valFROMaJOINbON(a.key=b.key1)JOINcON(c.key=b.key1)和b表的記錄，然后每次取得一個(gè)c表的記錄就計(jì)算一次join結(jié)果，類似的還有：SELECTa.val,b.val,c.valFROMaJOINbON(a.key=b.key1)JOINcON(c.key=b.key2)這里用了2次map/reduce任務(wù)。第一次緩存a表，用b表序列化；第二次緩存第一次map/reduce任務(wù)的結(jié)果，然后用c表序列化。JOINbON(a.key=b.key)對(duì)應(yīng)所有a表中的記錄都有一條記錄輸出。輸出的結(jié)果應(yīng)該是a.val,b.val,當(dāng)a.key=b.key時(shí)，而當(dāng)b.key中找不到等值的a.key記錄時(shí)也會(huì)輸出a.val,NULL?！癋ROMaLEFTOUTERJOINb”這句一定要寫(xiě)在同一行——意思是a表在b表的左邊，所JOIN語(yǔ)義應(yīng)該是遵循標(biāo)準(zhǔn)SQLspec的。條件——或是在join子句中寫(xiě)。這里面一個(gè)容易混淆的問(wèn)題是表分區(qū)的情況：LEFTOUTERJOINbON(a.key=b.key)WHEREa.ds='2009-07-07'ANDb.ds='2009-07-07'用其他列作為過(guò)濾條件。但是，如前所述，如果b表中找不到對(duì)應(yīng)a表的記錄，b表的所有列都會(huì)列出NULL,包括ds列。也就是說(shuō)，join會(huì)過(guò)濾b表中不能找到匹配a表joinkey的所有記錄。這樣的話，LEFTOUTER就使得查詢結(jié)果與WHERE子句無(wú)關(guān)了。解決的辦SELECTa.val,b.valON(a.key=b.keyANDb.ds='2009-07-07'ANDa.ds='2009-07-07')這一查詢的結(jié)果是預(yù)先在join階段過(guò)濾過(guò)的，所以不會(huì)存在上述問(wèn)題。這一邏輯也可以應(yīng)Join是不能交換位置的。無(wú)論是LEFT還是RIGHTjoin,都是左連接的。SELECTJOINbON(a.key=b.key)LEFTOUTERJOINcON(a做join。這一表述有一個(gè)不太明顯的問(wèn)題，就是當(dāng)一個(gè)key在a表和c表都存在，但是b表中不存在的時(shí)候：整個(gè)記錄在第一次join,即aJOINb的時(shí)候都被丟掉了(包括a.val1,a.val2和a.key),然后我們?cè)俸蚦表join的時(shí)候，如果c.key與a.key或b.key相等，就會(huì)得到這樣的結(jié)果：NULL,NULL,NULL,c.val。5.LEFTSEMIJOIN是IN/EXISTS子查詢的一種更高效的實(shí)現(xiàn)。Hive當(dāng)前沒(méi)有實(shí)現(xiàn)子句或其他地方過(guò)濾都不行。(SELECTb.key可以被重寫(xiě)為：SELECTa.key,a.valFROMaLEFTSEMIJOINbon(a.k5.HIVE參數(shù)設(shè)置開(kāi)發(fā)Hive應(yīng)用時(shí)，不可避免地需要設(shè)定Hive的參數(shù)。設(shè)定Hive的參數(shù)可以調(diào)優(yōu)HQL代碼的執(zhí)行效率，或幫助定位問(wèn)題。然而實(shí)踐中經(jīng)常遇到的一個(gè)問(wèn)題是，為什么設(shè)定的參數(shù)沒(méi)有起作用?這通常是錯(cuò)誤的設(shè)定方式導(dǎo)致的。對(duì)于一般參數(shù)，有以下三種設(shè)定方式：●配置文件·參數(shù)聲明配置文件：Hive的配置文件包括●用戶自定義配置文件：$HIVE_CO用戶自定義配置會(huì)覆蓋默認(rèn)配置。另外，Hive也會(huì)讀入Hadoop的配置，因?yàn)镠ive是作為Hadoop的客戶端啟動(dòng)的，Hadoop的配置文件包括Hive的配置會(huì)覆蓋Hadoop的配置。配置文件的設(shè)定對(duì)本機(jī)啟動(dòng)的所有Hive進(jìn)程都有效。命令行參數(shù)：?jiǎn)?dòng)Hive(客戶端或Server方式)時(shí)，可以在命令行添加-hiveconfparam=value來(lái)設(shè)定參數(shù)，例如：bin/hive-hiveconfhive.root.logger=INFO,console這一設(shè)定對(duì)本次啟動(dòng)的Session(對(duì)于Server方式啟動(dòng)，則是所有請(qǐng)求的Sessions)有效。參數(shù)聲明：可以在HQL中使用SET關(guān)鍵字設(shè)定參數(shù)，例如：setmapred.reduce.tasks=100;這一設(shè)定的作用域也是Session級(jí)的。上述三種設(shè)定方式的優(yōu)先級(jí)依次遞增。即參數(shù)聲明覆蓋命令行參數(shù)，命令行參數(shù)覆蓋配置文件設(shè)定。注意某些系統(tǒng)級(jí)的參數(shù)，例如log4j相關(guān)的設(shè)定，必須用前兩種方式設(shè)定，因?yàn)槟切﹨?shù)的讀取在Session建立以前已經(jīng)完成了。另外，SerDe參數(shù)必須寫(xiě)在DDL(建表)語(yǔ)句中。例如：createtableifnotexistst_dummy()ROWFORMATSERDE'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'WITHSERDEPROPERTIES('field.delim'='\t','escape.delim'='V','serialization.null.format'='')STOREDASTEXTFILE;類似serialization.null.format這樣的參數(shù)，必須和某個(gè)表或分區(qū)關(guān)聯(lián)。在DDL外部聲明將不起作用。DESCRIBEFUNCTION