廈門大學-林子雨-大數(shù)據(jù)技術原理與應用-自學教程-Hadoop上的數(shù)據(jù)倉庫Hive初級實踐教程

智建領航者智建領航者廈門大學計算機科學系研究生課程《大數(shù)據(jù)技術原理與應用》自學教程Hadoop上的數(shù)據(jù)倉庫Hive初級實踐教程(版本號：2016年1月13日版本)主講教師：林子雨廈門大學數(shù)據(jù)庫實驗室二零一六年一月（版權聲明：廈門大學數(shù)據(jù)庫實驗室原創(chuàng)內容，請勿用于商業(yè)用途）智建領航者智建領航者

目錄一、Hive簡介 1二、Hive安裝 1三、Hive配置 3四、Hive的常用HiveQL操作 9五、Hive簡單編程實踐 16附錄1:任課教師介紹 18附錄2：課程教材介紹 18智建領航者智建領航者

《大數(shù)據(jù)技術原理與應用》自學教程Hadoop上的數(shù)據(jù)倉庫Hive初級實踐教程主講教師：林子雨E-mail:ziyulin@個人主頁：/linziyu一、Hive簡介Hive是Facebook開發(fā)的構建于Hadoop集群之上的數(shù)據(jù)倉庫應用，可以將結構化的數(shù)據(jù)文件映射為一張數(shù)據(jù)庫表，并提供完整的SQL查詢功能，可以將SQL語句轉換為MapReduce任務進行運行。Hive是一個可以提供有效的、合理的且直觀的組織和使用數(shù)據(jù)的模型，即使對于經驗豐富的Java開發(fā)工程師來說，將這些常見的數(shù)據(jù)運算對應到底層的MapReduceJavaAPI也是令人敬畏的。Hive可以幫用戶做這些工作，用戶就可以集中精力關注查詢本身了。Hive可以將大多數(shù)的查詢轉換為MapReduce任務。Hive最適合于數(shù)據(jù)倉庫應用程序，使用該應用程序進行相關的靜態(tài)數(shù)據(jù)分析，不需要快速響應給出結果，而且數(shù)據(jù)本身也不會頻繁變化。Hive不是一個完整的數(shù)據(jù)庫。Hadoop以及HDFS的設計本身約束和局限性限制了Hive所能勝任的工作。最大的限制就是Hive不支持記錄級別的更新、插入或者刪除。用戶可以通過查詢生成新表或將查詢結果導入到文件中去。因為，Hadoop是一個面向批處理的系統(tǒng)，而MapReduce啟動任務啟動過程需要消耗很長時間，所以Hive延時也比較長。Hive還不支持事務。因此，Hive不支持聯(lián)機事務處理（OLTP），更接近于一個聯(lián)機分析技術（OLAP）工具，但是，目前還沒有滿足“聯(lián)機”部分。Hive提供了一系列的工具，可以用來進行數(shù)據(jù)提取轉化加載(ETL)，其中，ETL是一種可以存儲、查詢和分析存儲在Hadoop中的大規(guī)模數(shù)據(jù)的機制。因此，Hive是最適合數(shù)據(jù)倉庫應用程序的，它可以維護海量數(shù)據(jù)，而且可以對數(shù)據(jù)進行挖掘，然后形成意見和報告等。因為大多數(shù)的數(shù)據(jù)倉庫應用程序是基于SQL的關系數(shù)據(jù)庫現(xiàn)實的，所以，Hive降低了將這些應用程序移植到Hadoop上的障礙。如果用戶懂得SQL，那么學習使用Hive會很容易。因為Hive定義了簡單的類SQL查詢語言——HiveQL，這里值得一提的是，與SQLServer、Oracle相比，HiveQL和MySQL提供的SQL語言更接近。同樣的，相對于其他的Hadoop語言和工具來說，Hive也使得開發(fā)者將基于SQL的應用程序移植到Hadoop變得更加容易。二、Hive安裝因為Hive是構建在Hadoop之上的，所以在安裝Hive前，我們需要安裝Hadoop環(huán)境。Hadoop的安裝可以參照廈門大學數(shù)據(jù)庫實驗室網(wǎng)站上的安裝教程（/blog/install-hadoop-simplify/#more-94），但是，請注意，在按照該教程安裝完Hadoop以后，不要進行配置，Hadoop的配置過程，稍后我們在安裝完Hive后一起進行。下面開始安裝Hive。安裝Hive的過程和安裝Hadoop很類似，首先，我們先下載一個Hive軟件壓縮包（下載地址：/dyn/closer.cgi/hive/），然后進行解壓縮，具體如下：$cd~/下載$sudotar-zxvf./apache-hive-1.2.1-bin.tar.gz-C/usr/local#解壓到/usr/local中$cd/usr/local/$sudomv./apache-hive-1.2.1-bin/./hive#將文件夾名改為hive$sudochown-Rhadoop:hadoop./hive#修改文件權限$sudomkdir-p/usr/local/hive/warehouse#創(chuàng)建元數(shù)據(jù)存儲文件夾$sudochmoda+rwx/usr/local/hive/warehouse#修改文件權限為了方便使用，我們把hive命令加入到環(huán)境變量中去，需要使用以下命令編輯.bashrc文件：$vim~/.bashrc#設置環(huán)境變量進入.bashrc編輯狀態(tài)以后，需要在文件最前面添加如下幾行：exportHIVE_HOME=/usr/local/hiveexportPATH=$PATH:$HIVE_HOME/bin完成上述操作后，需要運行以下命令讓配置生效：$source~/.bashrc#使配置立即生效然后運行hive命令啟動Hive，命令如下：$hive#啟動Hive注意：（1）若啟動不成功，則會出現(xiàn)以下錯誤：則需要進入到hadoop安裝目錄下的share/hadoop/yarn/lib下刪除jline-0.9.94.jar文件，再啟動hive即可（因為高版本的Hadoop對Hive有捆綁）。（2）出現(xiàn)以下界面即為啟動成功：三、Hive配置通過Hadoop的學習我們已經知道，Hadoop的運行模式有三種：本地模式、偽分布式模式和分布式模式。這里我們以介紹本地模式為主，其他模式只進行簡單介紹。因為，本地模式是Hadoop的默認模式，所以，Hadoop不需要進行其他配置，我們只需對Hive進行相應配置。這里只需修改hive-site.xml文件，如果該文件不存在，用戶需要自己新建一個。（注意：在$HIVE_HOME/conf目錄下我們可能會看到hive-default.xml.template這個大文件，這個文件中包含了Hive提供的配置屬性以及默認的屬性值，這些屬性中的絕大多數(shù)，用戶可以忽略不管）。在安裝Hive時，默認情況下，元數(shù)據(jù)存儲在Derby數(shù)據(jù)庫中。Derby是一個完全用Java編寫的數(shù)據(jù)庫，所以可以跨平臺，但需要在JVM中運行。因為多用戶和系統(tǒng)可能需要并發(fā)訪問元數(shù)據(jù)存儲，所以默認的內置數(shù)據(jù)庫并不適用于生產環(huán)境。任何一個適用于JDBC進行連接的數(shù)據(jù)庫都可用作元數(shù)據(jù)庫存儲，這里我們把MySQL作為存儲元數(shù)據(jù)的數(shù)據(jù)庫。接下來，我們分別對這兩種方式進行介紹，即使用Derby數(shù)據(jù)庫的方式和使用MySQL數(shù)據(jù)庫的方式。（一）使用Derby數(shù)據(jù)庫的方式本地模式中，用戶的“表”等元數(shù)據(jù)信息，都默認存儲在file://user/hive/warehouse，對于其他模式默認存儲路徑是hdfs://namenode_server/user/hive/warehouse。使用如下命令編輯hive-site.xml文件：$vim/usr/local/hive/conf/hive-site.xml在hive-site.xml文件添加以下內容：<?xmlversion="1.0"encoding="UTF-8"standalone="no"?><?xml-stylesheettype="text/xsl"href="configuration.xsl"?><configuration><property><name>hive.metastore.warehouse.dir</name><value>/usr/local/hive/warehouse</value><description>locationofdefaultdatabaseforthewarehouse</description></property><property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:derby:;databaseName=/usr/local/hive/metastore_db;create=true</value><description>JDBCconnectstringforaJDBCmetastore</description></property></configuration>偽分布式模式和分布式模式配置Hive，只需根據(jù)Hadoop配置文件core-site.xml中fs.defaultFS的值對hive.metastore.warehouse.dir進行相應修改即可。這里對Hadoop的模式配置，可以參考廈門大學數(shù)據(jù)庫實驗室網(wǎng)站上的的配置教程（/blog/install-hadoop-simplify/#more-94）。Hive配置完成后，下面來啟動它，并使用簡單的HiveQL命令進行相應測試。我們先新建了一個表x，它有1個int型屬性a，然后查找表x中的所有屬性，最后刪除表x，然后退出。HiveQL命令與SQL命令很相似，所以，讀者如果有SQL基礎的話，會很快上手。關于HiveQL命令的使用方法會在第4節(jié)詳細介紹。注意：命令最后需要加“；”。（二）使用MySQL數(shù)據(jù)庫的方式1.首先我們需要安裝MySQL（如果本機上已經安裝MySQL，請?zhí)^此步驟）?？梢詤⒖季W(wǎng)址（/downloads/mysql/）進行下載安裝，也可以用apt-get命令下載，這里我們選擇后者。（1）查看并卸載系統(tǒng)自帶的MySQL相關安裝包，命令如下：$rpm-qa|grepmysql若沒有安裝rpm工具，系統(tǒng)會有提示，按照提示安裝即可。接下來查看是否有系統(tǒng)自帶的MySQL相關安裝包，若有，按下面命令刪除：$sudorpm-e--nodepsmysql-libs-xxxxxx（2）安裝MySQL，命令如下：$sudoapt-getinstallmysql-server（3）啟動設置MySQL，命令如下：$sudoservicemysqlstart#啟動mysql服務$sudochkconfigmysqlon#設置為開機自啟動$sudo

/usr/bin/mysqladmin-urootpassword'123'#設置root用戶登錄密碼$mysql-uroot-p#登錄mysql（4）創(chuàng)建hive用戶,數(shù)據(jù)庫等，命令如下：$insertintomysql.user(Host,User,Password)values("localhost","hive",password("hive"));#創(chuàng)建hive用戶$createdatabasehive;#創(chuàng)建hive數(shù)據(jù)庫$grantallonhive.*tohive@'%'identifiedby'hive';$grantallonhive.*tohive@'localhost'identifiedby'hive';$flushprivileges;$exit#退出mysql$mysql-uhive-phive#驗證hive用戶$showdatabases;看到以下信息，則說明創(chuàng)建成功。2.配置Hive，修改hive-site.xml文件，修改后的結果如下：<?xmlversion="1.0"encoding="UTF-8"standalone="no"?><?xml-stylesheettype="text/xsl"href="configuration.xsl"?><configuration><property><name>hive.metastore.warehouse.dir</name><value>/usr/local/hive/warehouse</value><description>locationofdefaultdatabaseforthewarehouse</description></property><property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:mysql://localhost:3306/hive;createDatebaseIfNotExist=true</value><description>JDBCconnectstringforaJDBCmetastore</description></property><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value><description>DriverclassnameforaJDBCmetastore</description></property><property><name>javax.jdo.option.ConnectionPassword</name><value>hive</value></property><property><name>javax.jdo.option.ConnectionUserName</name><value>hive</value><description>Usernametouseagainstmetastoredatabase</description></property></configuration>修改完hive-site.xml文件后，我們需要把JDBC驅動放置在lib目錄下（JDBC驅動程序mysql-connector-java-x.x.x-bin.jar文件的下載地址為http://www.mysql.com/downloads/connector/j/），命令如下：$mv~/下載/mysql-connector-java-5.1.30-bin.jar/usr/local/hive/lib/#拷貝mysql-connector-java-5.1.30-bin.jar到hive的lib下$cp/usr/local/hive/lib/jline-2.12.jar/usr/local/hadoop/share/hadoop/yarn/lib#把jline-2.12.jar拷貝到hadoop相應目錄下$mkdir

-p/usr/local/hive/tmp#創(chuàng)建hive臨時文件夾$sudochmoda+rwx/usr/local/hive/tmp#修改文件權限接下來，啟動測試Hive。首先啟動Hadoop后，執(zhí)行Hive命令“hive”，并輸入”showdatabases;“進行測試。hiveclient常用命令可以通過hive--help查看。下面再介紹一些hiveclient常用命令，用戶可以自行測試：$showtables;#查看所有表名$showtables'ad*';#查看以'ad'開頭的表名$set命令#設置變量與查看變量$set–v;#查看所有的變量$sethive.stats.atomic;#查看hive.stats.atomic變量$sethive.stats.atomic=false;#設置hive.stats.atomic變量$dfs-ls;#查看hadoop所有文件路徑$dfs-ls/usr/local/hive/warehouse/;#查看hive所有文件$sourcefile<filepath>#在client里執(zhí)行一個hive腳本文件$quit;#退出交互式shell$exit#退出交互式shell$reset#重置配置為默認值$!ls#從Hiveshell執(zhí)行一個shell命令四、Hive的常用HiveQL操作（一）Hive基本數(shù)據(jù)類型首先，我們簡單敘述一下HiveQL的基本數(shù)據(jù)類型。Hive支持基本數(shù)據(jù)類型和復雜類型,基本數(shù)據(jù)類型主要有數(shù)值類型(INT、FLOAT、DOUBLE)、布爾型和字符串,復雜類型有三種:ARRAY、MAP和STRUCT。

1.基本數(shù)據(jù)類型

TINYINT:1個字節(jié)

SMALLINT:2個字節(jié)

INT:4個字節(jié)

BIGINT:8個字節(jié)

BOOLEAN:TRUE/FALSE

FLOAT:4個字節(jié)，單精度浮點型

DOUBLE:8個字節(jié)，雙精度浮點型STRING字符串

2.復雜數(shù)據(jù)類型

ARRAY:有序字段

MAP:無序字段

STRUCT:一組命名的字段（二）常用的HiveQL操作命令Hive常用的HiveQL操作命令主要包括：數(shù)據(jù)定義、數(shù)據(jù)操作。接下來詳細介紹一下這些命令即用法（想要了解更多請參照《Hive編程指南》一書）。1.數(shù)據(jù)定義主要用于創(chuàng)建修改和刪除數(shù)據(jù)庫、表、視圖、函數(shù)和索引。（1）創(chuàng)建、修改和刪除數(shù)據(jù)庫$createdatabaseifnotexistshive;#創(chuàng)建數(shù)據(jù)庫$showdatabases;#查看Hive中包含數(shù)據(jù)庫$showdatabaseslike'h.*';#查看Hive中以h開頭數(shù)據(jù)庫$describedatabases;#查看hive數(shù)據(jù)庫位置等信息$alterdatabasehivesetdbproperties;#為hive設置鍵值對屬性$usehive;#切換到hive數(shù)據(jù)庫下$dropdatabaseifexistshive;#刪除不含表的數(shù)據(jù)庫$dropdatabaseifexistshivecascade;#刪除數(shù)據(jù)庫和它中的表注意：除dbproperties屬性外，數(shù)據(jù)庫的元數(shù)據(jù)信息都是不可更改的，包括數(shù)據(jù)庫名和數(shù)據(jù)庫所在的目錄位置，沒有辦法刪除或重置數(shù)據(jù)庫屬性。（2）創(chuàng)建、修改和刪除表$createtableifnotexistshive.usr(#創(chuàng)建內部表（管理表）namestringcomment'username',pwdstringcomment'password',addressstruct<street:string,city:string,state:string,zip:int>,comment'homeaddress',identifymap<int,tinyint>comment'number,sex')comment'descriptionofthetable'tblproperties('creator'='me','time'='2016.1.1');$createexternaltableifnotexistsusr2(#創(chuàng)建外部表namestring,pwdstring,addressstruct<street:string,city:string,state:string,zip:int>,identifymap<int,tinyint>)rowformatdelimitedfieldsterminatedby','location'/usr/local/hive/warehouse/hive.db/usr';$createtableifnotexistsusr3(#創(chuàng)建分區(qū)表namestring,pwdstring,addressstruct<street:string,city:string,state:string,zip:int>,identifymap<int,tinyint>)partitionedby(citystring,statestring);$createtableifnotexistshive.usr1likehive.usr;#復制usr表的表模式$showtablesinhive;#查看hive數(shù)據(jù)庫中所有表$showtables'u.*';#查看hive中以u開頭的表$describehive.usr;#查看usr表相關信息$altertableusrrenametocustom;#重命名表$altertableusr2addifnotexists#為表增加一個分區(qū)partition(city=”beijing”,state=”China”)location'/usr/local/hive/warehouse/usr2/China/beijing';$altertableusr2partition(city=”beijing”,state=”China”)#修改分區(qū)路徑setlocation'/usr/local/hive/warehouse/usr2/CH/beijing';$altertableusr2dropifexistspartition(city=”beijing”,state=”China”)#刪除分區(qū)$altertableusrchangecolumnpwdpasswordstringafteraddress;#修改列信息$altertableusraddcolumns(hobbystring);#增加列$altertableusrreplacecolumns(unamestring);#刪除替換列$altertableusrsettblproperties('creator'='liming');#修改表屬性$altertableusr2partition(city=”beijing”,state=”China”)#修改存儲屬性setfileformatsequencefile;$usehive;#切換到hive數(shù)據(jù)庫下$droptableifexistsusr1;#刪除表$dropdatabaseifexistshivecascade;#刪除數(shù)據(jù)庫和它中的表（3）視圖和索引的創(chuàng)建、修改和刪除主要語法如下，用戶可自行實現(xiàn)。$createviewview_nameas;#創(chuàng)建視圖$alterviewview_namesettblproperties(…);#修改視圖因為視圖是只讀的，所以對于視圖只允許改變元數(shù)據(jù)中的tblproperties屬性。$dropviewifexistsview_name;#刪除視圖$createindexindex_nameontabletable_name(partition_name/column_name)#創(chuàng)建索引as'pact.CompactIndexHandler'withdeferredrebuild;這里'pact.CompactIndexHandler'是一個索引處理器，即一個實現(xiàn)了索引接口的Java類，另外Hive還有其他的索引實現(xiàn)。$alterindexindex_nameontabletable_namepartition(...)rebulid;#重建索引如果使用deferredrebuild，那么新索引成空白狀態(tài)，任何時候可以進行第一次索引創(chuàng)建或重建。$showformattedindexontable_name;#顯示索引$dropindexifexistsindex_nameontabletable_name;#刪除索引（4）用戶自定義函數(shù)在新建用戶自定義函數(shù)（UDF）方法前，先了解一下Hive自帶的那些函數(shù)?！皊howfunctions;”命令會顯示Hive中所有的函數(shù)名稱：若想要查看具體函數(shù)使用方法可使用describefunction函數(shù)名；首先編寫自己的UDF前需要繼承UDF類并實現(xiàn)evaluate()函數(shù)，或是繼承GenericUDF類實現(xiàn)initialize()函數(shù)、evaluate()函數(shù)和getDisplayString()函數(shù)，還有其他的實現(xiàn)方法，感興趣的用戶可以自行學習。另外，如果用戶想在Hive中使用該UDF需要將我們編寫的Java代碼進行編譯，然后將編譯后的UDF二進制類文件(.class文件)打包成一個JAR文件，然后在Hive會話中將這個JAR文件加入到類路徑下，在通過createfunction語句定義好使用這個Java類的函數(shù)。$addjar<jar文件的絕對路徑>;#創(chuàng)建函數(shù)$createtemporaryfunctionfunction_name;$droptemporaryfunctionifexistsfunction_name;#刪除函數(shù)2.數(shù)據(jù)操作主要實現(xiàn)的是將數(shù)據(jù)裝載到表中（或是從表中導出），并進行相應查詢操作，對熟悉SQL語言的用戶應該不會陌生。（1）向表中裝載數(shù)據(jù)這里我們以只有兩個屬性的簡單表為例來介紹。首先創(chuàng)建表stu和course，stu有兩個屬性id與name，course有兩個屬性cid與sid。$createtableifnotexistshive.stu(idint,namestring)rowformatdelimitedfieldsterminatedby'\t';$createtableifnotexistshive.course(cidint,sidint)rowformatdelimitedfieldsterminatedby'\t';向表中裝載數(shù)據(jù)有兩種方法：從文件中導入和通過查詢語句插入。a.從文件中導入假如這個表中的記錄存儲于文件stu.txt中，該文件的存儲路徑為usr/local/hadoop/examples/stu.txt，內容如下，stu.txt：1 xiapi2 xiaoxue3 qingqing下面我們把這個文件中的數(shù)據(jù)裝載到表stu中，操作如下：$loaddatalocalinpath'/usr/local/hadoop/examples/stu.txt'overwriteintotablestu;如果stu.txt文件存儲在HDFS上,則不需要local關鍵字。b.通過查詢語句插入使用如下命令，創(chuàng)建stu1表，它和stu表屬性相同，我們要把從stu表中查詢得到的數(shù)據(jù)插入到stu1中：$createtablestu1asselectid,namefromstu;上面是創(chuàng)建表，并直接向新表插入數(shù)據(jù)；若表已經存在，向表中插入數(shù)據(jù)需執(zhí)行以下命令：$insertoverwritetablestu1selectid,namefromstuwhere（條件）;這里關鍵字overwrite的作用是替換掉表（或分區(qū)）中原有數(shù)據(jù)，換成into關鍵字，直接追加到原有內容后。（2）從表中導出數(shù)據(jù)a.可以簡單拷貝文件或文件夾命令如下：$hadoopfs-cpsource_pathtarget_path;b.寫入臨時文件命令如下：$insertoverwritelocaldirectory'/usr/local/hadoop/tmp/stu'selectid,namefromstu;（3）查詢操作和SQL的查詢完全一樣，這里不再贅述。主要使用select...from...where...等語句，再結合關鍵字groupby、having、like、rlike等操作。這里我們簡單介紹一下SQL中沒有的case...when...then...句式、join操作和子查詢操作。case...when...then...句式和if條件語句類似，用于處理單個列的查詢結果，語句如下：$selectid,name,casewhenid=1then'first'whenid=2then'second'else'third'endfromstu;結果如下：連接（join）是將兩個表中在共同數(shù)據(jù)項上相互匹配的那些行合并起來,HiveQL的連接分為內連接、左向外連接、右向外連接、全外連接和半連接5種。a.內連接(等值連接)

內連接使用比較運算符根據(jù)每個表共有的列的值匹配兩個表中的行。首先，我們先把以下內容插入到course表中（自行完成）。1 32 13 1

下面,查詢stu和course表中學號相同的所有行，命令如下：$selectstu.*,course.*fromstujoincourseon(stu.id=course.sid);執(zhí)行結果如下：b.左連接

左連接的結果集包括“LEFTOUTER”子句中指定的左表的所有行,而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行,則在相關聯(lián)的結果集中右表的所有選擇列均為空值，命令如下：$selectstu.*,course.*fromstuleftouterjoincourseon(stu.id=course.sid);執(zhí)行結果如下：c.右連接

右連接是左向外連接的反向連接,將返回右表的所有行。如果右表的某行在左表中沒有匹配行,則將為左表返回空值。命令如下：$selectstu.*,course.*fromsturightouterjoincourseon(stu.id=course.sid);執(zhí)行結果如下：d.全連接

全連接返回左表和右表中的所有行。當某行在另一表中沒有匹配行時,則另一個表的選擇列表包含空值。如果表之間有匹配行,則整個結果集包含基表的數(shù)據(jù)值。命令如下：$selectstu.*,course.*fromstufullouterjoincourseon(stu.id=course.sid);執(zhí)行結果如下：e.半連接

半連接是Hive