超人學(xué)院hadoop就業(yè)培訓(xùn)期價(jià)值8000 hive

深入淺出hadoop

——培訓(xùn)講師：吳超

課程安排Hive是什么？其體系結(jié)構(gòu)簡(jiǎn)介*Hive的安裝與管理*HiveQL數(shù)據(jù)類型，表以及表的操作*HiveQL查詢數(shù)據(jù)***Hive的Java客戶端**加深拓展Hive的自定義函數(shù)UDF*一、什么是Hive（一）Hive是建立在Hadoop上的數(shù)據(jù)倉(cāng)庫(kù)基礎(chǔ)構(gòu)架。它提供了一系列的工具，可以用來(lái)進(jìn)行數(shù)據(jù)提取轉(zhuǎn)化加載（ETL），這是一種可以存儲(chǔ)、查詢和分析存儲(chǔ)在Hadoop中的大規(guī)模數(shù)據(jù)的機(jī)制。Hive定義了簡(jiǎn)單的類SQL查詢語(yǔ)言，稱為QL，它允許熟悉SQL的用戶查詢數(shù)據(jù)。同時(shí)，這個(gè)語(yǔ)言也允許熟悉MapReduce開發(fā)者的開發(fā)自定義的mapper和reducer來(lái)處理內(nèi)建的mapper和reducer無(wú)法完成的復(fù)雜的分析工作。什么是Hive（二）Hive是SQL解析引擎，它將SQL語(yǔ)句轉(zhuǎn)譯成M/RJob然后在Hadoop執(zhí)行。Hive的表其實(shí)就是HDFS的目錄，按表名把文件夾分開。如果是分區(qū)表，則分區(qū)值是子文件夾，可以直接在M/RJob里使用這些數(shù)據(jù)。Hive相當(dāng)于hadoop的客戶端工具，部署時(shí)不放在不一定放在集群節(jié)點(diǎn)中，可以放在某個(gè)節(jié)點(diǎn)上。Hive的數(shù)據(jù)存儲(chǔ)Hive的數(shù)據(jù)存儲(chǔ)基于HadoopHDFSHive沒有專門的數(shù)據(jù)存儲(chǔ)格式存儲(chǔ)結(jié)構(gòu)主要包括：數(shù)據(jù)庫(kù)、文件、表、視圖、索引Hive默認(rèn)可以直接加載文本文件（TextFile），還支持SequenceFile、RCFile創(chuàng)建表時(shí)，指定Hive數(shù)據(jù)的列分隔符與行分隔符，Hive即可解析數(shù)據(jù)Hive的系統(tǒng)架構(gòu)?用戶接口，包括CLI，JDBC/ODBC，WebUI?元數(shù)據(jù)存儲(chǔ)，通常是存儲(chǔ)在關(guān)系數(shù)據(jù)庫(kù)如mysql,derby中?解釋器、編譯器、優(yōu)化器、執(zhí)行器?Hadoop：用HDFS進(jìn)行存儲(chǔ)，利用MapReduce進(jìn)行計(jì)算Hive的安裝把hive-0.9.0.tar.gz復(fù)制到/usr/local解壓hive-0.9.0.tar.gz與重命名#cd/usr/local#tar-zxvfhive-0.9.0.tar.gz#mvhive-0.9.0hive修改/etc/profile文件。#vi/etc/profile增加exportHIVE_HOME=/usr/local/hive修改exportPATH=$JAVA_HOME/bin:$PATH:$HADOOP_HOME/bin:$HIVE_HOME/bin保存退出#source/etc/profile在Hadoop0上Hive的安裝cd$HIVE_HOMEmvhive-env.sh.templatehive-env.shmvhive-default.xml.templatehive-site.xml修改$HIVE_HOME/bin的hive-config.sh，增加以下三行exportJAVA_HOME=/usr/local/jdkexportHIVE_HOME=/usr/local/hiveexportHADOOP_HOME=/usr/local/hadoopHive的安裝啟動(dòng)#hivehive>showtables;hive>createtabletest(idint,namestring);hive>quit;觀察：#hadoopfs-ls/user/hive參數(shù)：hive.metastore.warehouse.dir修改：使用set命令set命令顯示或者修改變量值在代碼中引用時(shí)使用${...};命名空間使用權(quán)限描述hivevar可讀寫$hive--definename=zhangsan;hiveconf可讀寫$hive--hiveconfhive.cli.print.current.db=true;$hive--hiveconfhive.cli.print.header=true;system可讀寫java定義的配置屬性，如system:env只讀shell環(huán)境變量，如env:USER啟動(dòng)時(shí)指定用戶的目錄$>hive--hiveconfhive.metastore.warehouse.dir=/hive/$USER;創(chuàng)建表變量$>today=`date+'%Y_%m_%d'`hive>createtablelog_${env:today}(idint);$>hive-e""$>hive-e"">aaa$>hive-e-S"">aaa$>hive-ffilehive>sourcefile~/.hiverc~/.hivehistoryHive的metastoremetastore是hive元數(shù)據(jù)的集中存放地。metastore默認(rèn)使用內(nèi)嵌的derby數(shù)據(jù)庫(kù)作為存儲(chǔ)引擎Derby引擎的缺點(diǎn)：一次只能打開一個(gè)會(huì)話使用Mysql作為外置存儲(chǔ)引擎，多用戶同時(shí)訪問Hive的安裝配置MySQL的metastore1.上傳mysql-connector-java-5.1.10.jar到$HIVE_HOME/lib2.登錄MYSQL，創(chuàng)建數(shù)據(jù)庫(kù)hive#mysql-uroot-padminmysql>createdatabasehive;mysql>GRANTallONhive.*TOroot@'%'IDENTIFIEDBY'admin';mysql>flushprivileges;mysql>setglobalbinlog_format='MIXED';3.把mysql的數(shù)據(jù)庫(kù)字符類型改為latin1修改$HIVE_HOME/conf/hive-site.xml<property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://chaoren2:3306/hive_cr00?createDatabaseIfNotExist=true</value></property><property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value></property><property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value></property><property> <name>javax.jdo.option.ConnectionPassword</name> <value>admin</value></property>二、數(shù)據(jù)類型日期操作見文檔hive_date_function.txt復(fù)合數(shù)據(jù)類型創(chuàng)建學(xué)生表hive>CREATETABLEstudent(idINT,nameSTRING,favorsARRAY<STRING>,scoresMAP<STRING,FLOAT>);hive記錄中默認(rèn)分隔符分隔符描述語(yǔ)句\n分隔行LINESTERMINATEDBY'\t'^A分隔字段(列)，顯示編碼使用\001FIELDSTERMINATEDBY'\001'^B分隔復(fù)合類型中的元素，顯示編碼使用\002COLLECTIONITEMSTERMINATEDBY'\002'^C分隔map元素的key和value，顯示編碼使用\003MAPKEYSTERMINATEDBY'\003'讀模式與寫模式RDBMS是寫模式Hive是讀模式三、數(shù)據(jù)定義3.1數(shù)據(jù)庫(kù)hive>CREATEDATABASE[IFNOTEXISTS]mydb[LOCATION]'/'[COMMENT]'';hive>SHOWDATABASES;hive>DESCRIBEDATABASE[extended]mydb;hive>DROPDATABASE[IFEXISTED]mydb[CASCADE];3.2表hive>CREATETABLEIFNOTEXISTSt1(...)[COMMENT''][LOCATION'...']hive>SHOWTABLESinmydb;hive>CREATETABLEt2LIKEt1;hive>DESCRIBEt2;3.3列修改列的名稱、類型、位置、注釋hive>ALTERTABLEt3CHANGECOLUMNold_namenew_nameStringCOMMENT'...'AFTERcolumn2;增加列hive>ALTERTABLEADDCOLUMN(genderint);管理表和外部表管理表，也稱作內(nèi)部表外部表createexternaltablexxxlocation'/dir';Hive的數(shù)據(jù)模型-內(nèi)部表與數(shù)據(jù)庫(kù)中的Table在概念上是類似每一個(gè)Table在Hive中都有一個(gè)相應(yīng)的目錄存儲(chǔ)數(shù)據(jù)。例如，一個(gè)表test，它在HDFS中的路徑為：/warehouse/test。warehouse是在hive-site.xml中由${hive.metastore.warehouse.dir}指定的數(shù)據(jù)倉(cāng)庫(kù)的目錄所有的Table數(shù)據(jù)（不包括ExternalTable）都保存在這個(gè)目錄中。刪除表時(shí)，元數(shù)據(jù)與數(shù)據(jù)都會(huì)被刪除Hive的數(shù)據(jù)模型-內(nèi)部表創(chuàng)建數(shù)據(jù)文件inner_table.dat創(chuàng)建表hive>createtableinner_table(keystring);加載數(shù)據(jù)hive>loaddatalocalinpath'/root/inner_table.dat'intotableinner_table;查看數(shù)據(jù)select*frominner_tableselectcount(*)frominner_table刪除表droptableinner_tableHive的數(shù)據(jù)模型-外部表指向已經(jīng)在HDFS中存在的數(shù)據(jù)，可以創(chuàng)建Partition它和內(nèi)部表在元數(shù)據(jù)的組織上是相同的，而實(shí)際數(shù)據(jù)的存儲(chǔ)則有較大的差異內(nèi)部表的創(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ì)訪問將會(huì)直接在數(shù)據(jù)倉(cāng)庫(kù)目錄中完成。刪除表時(shí)，表中的數(shù)據(jù)和元數(shù)據(jù)將會(huì)被同時(shí)刪除外部表只有一個(gè)過(guò)程，加載數(shù)據(jù)和創(chuàng)建表同時(shí)完成，并不會(huì)移動(dòng)到數(shù)據(jù)倉(cāng)庫(kù)目錄中，只是與外部數(shù)據(jù)建立一個(gè)鏈接。當(dāng)刪除一個(gè)外部表時(shí)，僅刪除該鏈接分區(qū)表分區(qū)可以理解為分類，通過(guò)分類把不同類型的數(shù)據(jù)放到不同的目錄下。分類的標(biāo)準(zhǔn)就是分區(qū)字段，可以一個(gè)，也可以多個(gè)。分區(qū)表的意義在于優(yōu)化查詢。查詢時(shí)盡量利用分區(qū)字段。如果不使用分區(qū)字段，就會(huì)全部掃描。hive>CREATETABLEt3(...)PARTITIONEDBY(provincestring);hive>sethive.mapred.mode=strict|nostrict;hive>SHOWPARTITIONSt3[partition(province='beijing')];hive>ALTERTABLEt3ADD[IFNOTEXISTS]PARTITION(...)LOCATION'...';hive>ALTERTABLEt3DROPPARTITION(...);sethive.mapred.mode=strict;Hive的數(shù)據(jù)模型-分區(qū)表Partition對(duì)應(yīng)于數(shù)據(jù)庫(kù)的Partition列的密集索引在Hive中，表中的一個(gè)Partition對(duì)應(yīng)于表下的一個(gè)目錄，所有的Partition的數(shù)據(jù)都存儲(chǔ)在對(duì)應(yīng)的目錄中例如：test表中包含date和city兩個(gè)Partition，則對(duì)應(yīng)于date=20130201,city=bj的HDFS子目錄為：/warehouse/test/date=20130201/city=bj對(duì)應(yīng)于date=20130202,city=sh的HDFS子目錄為；/warehouse/test/date=20130202/city=shHive的數(shù)據(jù)模型-分區(qū)表CREATE

TABLEtmp_table#表名(titlestring,

#字段名稱字段類型minimum_biddouble,quantitybigint,have_invoicebigint)COMMENT'注釋：XXX'

#表注釋

PARTITIONEDBY(ptSTRING)

#分區(qū)表字段（如果你文件非常之大的話，采用分區(qū)表可以快過(guò)濾出按分區(qū)字段劃分的數(shù)據(jù)）

ROWFORMATDELIMITED

FIELDSTERMINATEDBY

'\001'

#字段是用什么分割開的STOREDASSEQUENCEFILE;#用哪種方式存儲(chǔ)數(shù)據(jù)，SEQUENCEFILE是hadoop自帶的文件壓縮格式一些相關(guān)命令SHOW

TABLES;#查看所有的表SHOW

TABLES

'*TMP*';#支持模糊查詢SHOWPARTITIONSTMP_TABLE;#查看表有哪些分區(qū)DESCRIBE

TMP_TABLE;#查看表結(jié)構(gòu)Hive的數(shù)據(jù)模型-分區(qū)表創(chuàng)建數(shù)據(jù)文件partition_table.dat創(chuàng)建表createtablepartition_table(rectimestring,msisdnstring)partitionedby(daytimestring,citystring)rowformatdelimitedfieldsterminatedby'\t'storedasTEXTFILE;加載數(shù)據(jù)到分區(qū)loaddatalocalinpath'/home/partition_table.dat'intotablepartition_tablepartition(daytime='2013-02-01',city='bj');查看數(shù)據(jù)select*frompartition_tableselectcount(*)frompartition_table?刪除表droptablepartition_tableHive的數(shù)據(jù)模型-分區(qū)表altertablepartition_tableaddpartition(daytime='2013-02-04',city='bj');通過(guò)loaddata加載數(shù)據(jù)altertablepartition_tabledroppartition(daytime='2013-02-04',city='bj')元數(shù)據(jù)，數(shù)據(jù)文件刪除，但目錄daytime=2013-02-04還在裝載數(shù)據(jù)從文件中裝載數(shù)據(jù)hive>LOADDATA[LOCAL]INPATH'...'[OVERWRITE]INTOTABLEt2[PARTITION(province='beijing')];通過(guò)查詢表裝載數(shù)據(jù)hive>INSERTOVERWRITETABLEt2PARTITION(province='beijing')SELECT*FROMxxxWHERExxxhive>FROMt4INSERTOVERWRITETABLEt3PARTITION(...)SELECT...WHERE...INSERTOVERWRITETABLEt3PARTITION(...)SELECT...WHERE...INSERTOVERWRITETABLEt3PARTITION(...)SELECT...WHERE...動(dòng)態(tài)分區(qū)裝載數(shù)據(jù)hive>sethive.exec.dynamic.partition=true;hive>sethive.exec.dynamic.partition.mode=nostrict;hive>sethive.exec.max.dynamic.partitions.pernode=1000;hive>INSERTOVERWRITETABLEt3PARTITION(province,city)SELECTvince,t.cityFROMtempt;hive>INSERTOVERWRITETABLEt3PARTITION(province='bj',city)SELECTvince,t.cityFROMtemptWHEREvince='bj';單語(yǔ)句建表并同時(shí)裝載數(shù)據(jù)hive>CREATETABLEt4ASSELECT導(dǎo)出數(shù)據(jù)在hdfs復(fù)制文件(夾)$hadoopfs-cpsourcedestination使用DIRECTORYhive>INSERTOVERWRITELOCALDIRECTORY'...'SELECT...FROM...WHERE...;Hive運(yùn)行模式Hive的運(yùn)行模式即任務(wù)的執(zhí)行環(huán)境分為本地與集群兩種我們可以通過(guò)mapred.job.tracker來(lái)指明設(shè)置方式：hive>SETmapred.job.tracker=local;Hive的啟動(dòng)方式1、hive命令行模式，直接輸入#/hive/bin/hive的執(zhí)行程序，或者輸入#hive--servicecli2、hiveweb界面的(端口號(hào)9999)啟動(dòng)方式#hive--servicehwi&用于通過(guò)瀏覽器來(lái)訪問hive

3、hive遠(yuǎn)程服務(wù)(端口號(hào)10000)啟動(dòng)方式#hive--servicehiveserver&四、強(qiáng)大的查詢功能簡(jiǎn)單查詢select...from...where...使用各種函數(shù)hive>showfunctions;hive>describefunctionexplode;LIMIT語(yǔ)句列別名嵌套select語(yǔ)句什么時(shí)候可以避免執(zhí)行MapReduce？select*where語(yǔ)句中只有分區(qū)字段sethive.exec.mode.local.auto=true;groupby語(yǔ)句having語(yǔ)句orderby、sortby、distributeby、clusterby語(yǔ)句orderby是全局排序、sortby是單reduce排序、distributeby是分區(qū)字段;clusterby是distributeby和sortby的簡(jiǎn)寫。表連接(只支持等值連接)INNERJOIN表中都有，且兩表符合連接條件LEFTOUTERJOIN左表中符合where條件出現(xiàn)，右表可以為空RIGHTOUTERJOIN右表中符合where條件出現(xiàn)，左表可以為空FULLOUTERJOINLEFTSEMI-JOIN左表中符合on條件出現(xiàn)，右表不出現(xiàn)笛卡爾積是mxn的結(jié)果map-sideJOIN桶表hive>createtablebuckets(...)CLUSTEREDBY(...)INTO3BUCKETS;hive>SEThive.enforce.bucketing=true;INSERTOVERWRITETABLEbucketsSELECT...FROM...;Hive的數(shù)據(jù)模型—桶表桶表是對(duì)數(shù)據(jù)進(jìn)行哈希取值，然后放到不同文件中存儲(chǔ)。創(chuàng)建表 createtablebucket_table(idstring)clusteredby(id)into4buckets; 加載數(shù)據(jù) sethive.enforce.bucketing=true; insertintotablebucket_tableselectnamefromstu; insertoverwritetablebucket_tableselectnamefromstu;數(shù)據(jù)加載到桶表時(shí)，會(huì)對(duì)字段取hash值，然后與桶的數(shù)量取模。把數(shù)據(jù)放到對(duì)應(yīng)的文件中。抽樣查詢 select*frombucket_tabletablesample(bucket1outof4onid);Hive的數(shù)據(jù)模型-外部表CREATEEXTERNALTABLEpage_view(viewTimeINT,

useridBIGINT,page_urlSTRING,

referrer_urlSTRING,

ipSTRINGCOMMENT'IPAddressoftheUser',countrySTRINGCOMMENT'countryoforigination‘)COMMENT'Thisisthestagingpageviewtable'ROWFORMATDELIMITEDFIELDSTERMINATEDBY

'44'

LINES TERMINATEDBY

'12'STOREDASTEXTFILELOCATION'hdfs://centos:9000/user/data/staging/page_view';Hive的數(shù)據(jù)模型-外部表創(chuàng)建數(shù)據(jù)文件external_table.dat創(chuàng)建表hive>createexternaltableexternal_table1(keystring)ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t'location'/home/external';在HDFS創(chuàng)建目錄/home/external#hadoopfs-put/home/external_table.dat/home/external加載數(shù)據(jù)LOADDATAINPATH'/home/external_table1.dat'INTOTABLEexternal_table1;查看數(shù)據(jù)select*fromexternal_tableselectcount(*)fromexternal_table刪除表droptableexternal_tableHive與傳統(tǒng)數(shù)據(jù)庫(kù)查詢語(yǔ)言HiveQLSQL數(shù)據(jù)存儲(chǔ)位置HDFSRawDeviceor本地FS數(shù)據(jù)格式用戶定義系統(tǒng)決定數(shù)據(jù)更新不支持支持索引新版本有，但弱有執(zhí)行MapReduceExecutor執(zhí)行延遲高低可擴(kuò)展性高低數(shù)據(jù)規(guī)模大小Hive的數(shù)據(jù)模型-數(shù)據(jù)庫(kù)類似傳統(tǒng)數(shù)據(jù)庫(kù)的DataBase默認(rèn)數(shù)據(jù)庫(kù)"default"使用#hive命令后，不使用hive>use<數(shù)據(jù)庫(kù)名>，系統(tǒng)默認(rèn)的數(shù)據(jù)庫(kù)。可以顯式使用hive>usedefault;創(chuàng)建一個(gè)新庫(kù)hive>createdatabasetest_dw;視圖操作視圖的創(chuàng)建CREATEVIEWv1ASselect*fromt1;查詢selectSELECT[ALL|DISTINCT]select_expr,select_expr,... FROMtable_reference [WHEREwhere_condition] [GROUPBYcol_list] [CLUSTERBYcol_list|[DISTRIBUTEBYcol_list][SORTBYcol_list]|[ORDERBYcol_list]] [LIMITnumber]DISTRIBUTEBY指定分發(fā)器（Partitioner）,多Reducer可用查詢基于Partition的查詢一般SELECT查詢是全表掃描。但如果是分區(qū)表，查詢就可以利用分區(qū)剪枝（inputpruning）的特性，類似“分區(qū)索引“”，只掃描一個(gè)表中它關(guān)心的那一部分。Hive當(dāng)前的實(shí)現(xiàn)是，只有分區(qū)斷言（Partitionedby）出現(xiàn)在離FROM子句最近的那個(gè)WHERE子句中，才會(huì)啟用分區(qū)剪枝。例如，如果page_views表（按天分區(qū)）使用date列分區(qū)，以下語(yǔ)句只會(huì)讀取分區(qū)為‘2008-03-01’的數(shù)據(jù)。

SELECTpage_views.*FROMpage_viewsWHEREpage_views.date>='2013-03-01'ANDpage_views.date<='2013-03-01'LIMITClauseLimit可以限制查詢的記錄數(shù)。查詢的結(jié)果是隨機(jī)選擇的。下面的查詢語(yǔ)句從t1表中隨機(jī)查詢5條記錄：SELECT*FROMt1LIMIT5TopN查詢下面的查詢語(yǔ)句查詢銷售記錄最大的5個(gè)銷售代表。SETmapred.reduce.tasks=1

SELECT*FROMsalesSORTBYamountDESCLIMIT5表連接導(dǎo)入ac信息表hive>createtableacinfo(namestring,acipstring)rowformatdelimitedfieldsterminatedby'\t'storedasTEXTFILE;hive>loaddatalocalinpath'/home/acinfo/ac.dat'intotableacinfo;內(nèi)連接select,a.*fromdim_acajoin

acinfobon(a.ac=b.acip)limit10;左外連接select,a.*fromdim_acaleftouterjoinacinfobona.ac=b.aciplimit10;UDF1、UDF函數(shù)可以直接應(yīng)用于select語(yǔ)句，對(duì)查詢結(jié)構(gòu)做格式化處理后，再輸出內(nèi)容。2、編寫UDF函數(shù)的時(shí)候需要注意一下幾點(diǎn)：a）自定義UDF需要繼承org.apache.hadoop.hive.ql.UDF。b）需要實(shí)現(xiàn)evaluate函數(shù)，evaluate函數(shù)支持重載。4、步驟a）把程序打包放到目標(biāo)機(jī)器上去；b）進(jìn)入hive客戶端，添加jar包：hive>addjar/run/jar/udf_test.jar;c）創(chuàng)建臨時(shí)函數(shù)：hive>CREATETEMPORARYFUNCTIONadd_exampleAS'hive.udf.Add';d）查