大數(shù)據(jù)技術(shù)基礎(chǔ)教程 課件 第7章 數(shù)據(jù)倉庫Hive

第7章數(shù)據(jù)倉庫Hive《大數(shù)據(jù)技術(shù)基礎(chǔ)教程》學(xué)習(xí)目標(biāo)/Target了解數(shù)據(jù)倉庫Hive的架構(gòu)原理、數(shù)據(jù)類型和數(shù)據(jù)模型掌握數(shù)據(jù)倉庫Hive的DML和DDL的基本操作掌握數(shù)據(jù)倉庫Hive

JDBC的基本操作章節(jié)概述/

SummaryHive是Hadoop中的一個重要子項(xiàng)目,它利用MapReduce編程技術(shù),實(shí)現(xiàn)了部分SQL語句，提供了類SQL的編程接口。Hive的出現(xiàn)極大地推進(jìn)了Hadoop在數(shù)據(jù)倉庫方面的發(fā)展。hive是基于Hadoop的一個數(shù)據(jù)倉庫工具，用來進(jìn)行數(shù)據(jù)提取、轉(zhuǎn)化、加載，本章將簡要介紹Hive簡介、Hive架構(gòu)原理、Hive數(shù)據(jù)類型及應(yīng)用、Hive數(shù)據(jù)模型、DDL數(shù)據(jù)庫語言應(yīng)用、HiveJDBC編程實(shí)踐。目錄/Contents01020304Hive簡介Hive架構(gòu)原理Hive數(shù)據(jù)類型及應(yīng)用Hive數(shù)據(jù)模型05DDL數(shù)據(jù)庫語言應(yīng)用06DML數(shù)據(jù)庫語言應(yīng)用07HiveJDBC訪問Hive簡介7.17.1Hive簡介7.1.1Hive概論Hive是一個基于Hadoop的開源數(shù)據(jù)倉庫工具，用于存儲和處理海量結(jié)構(gòu)化數(shù)據(jù)。它是Facebook2008年8月開源的一個數(shù)據(jù)倉庫框架，它提供了一個被稱為Hive查詢語言(簡稱HiveOL或HOL)的SOL語言，來查詢存儲在Hadoop集群中的數(shù)據(jù)。7.1Hive簡介7.1.1Hive概論Hive不支持OLTP（聯(lián)機(jī)事務(wù)處理）所需的關(guān)鍵功能，而更接近成為一個OLAP(聯(lián)機(jī)分析技術(shù)）工具。但是由于Hadoop本身的時間開銷很大，并且Hadoop所被設(shè)計用來處理的數(shù)據(jù)規(guī)模非常大，因此提交查詢和返回結(jié)果是可能具有非常大的延時的，所以Hive并沒有滿足OLAP中的“聯(lián)機(jī)”部分，至少目前并沒有滿足。7.1Hive簡介7.1.1Hive概論由于大多數(shù)的數(shù)據(jù)倉庫應(yīng)用程序是使用基于SQL的關(guān)系型數(shù)據(jù)庫實(shí)現(xiàn)的，所以Hive降低了將這些應(yīng)用程序移植到Hadoop上的障礙。用戶如果懂得SQL，那么學(xué)習(xí)使用Hive將會很容易。如果沒有Hive，那么這些用戶就需要去重新學(xué)習(xí)新的語言和新的工具后才能進(jìn)行生產(chǎn)。7.1Hive簡介7.1.2Hive的優(yōu)缺點(diǎn)由于Hive使用類SQL查詢語法,最大限度的實(shí)現(xiàn)了和SQL標(biāo)準(zhǔn)的兼容，大大降低了傳統(tǒng)數(shù)據(jù)分析人員學(xué)習(xí)的曲線使用JDBC接口/ODBC接口，開發(fā)人員更易開發(fā)應(yīng)用Hive以MR作為計算引擎、HDFS作為存儲系統(tǒng)，為超大數(shù)據(jù)集設(shè)計的計算和擴(kuò)展能力具有統(tǒng)一的元數(shù)據(jù)管理（Derby、MySql等），并可與Pig、Presto等共享Hive還支持用戶自定義函數(shù)，用戶可以根據(jù)自己的需求來實(shí)現(xiàn)自己的函數(shù)Hive的優(yōu)點(diǎn)7.1Hive簡介7.1.2Hive的優(yōu)缺點(diǎn)由于Hive基于MR計算引擎，涉及到過多的磁盤I/O，因此hive的延遲比較高，比較適用用大量數(shù)據(jù)的統(tǒng)計分析,主要用于離線統(tǒng)計分析，對于實(shí)時要求不高的場合。Hive的HQL表達(dá)能力有限，導(dǎo)致迭代式算法無法表達(dá)，同時由于MapReduce數(shù)據(jù)處理流程的限制，Hive不能勝任數(shù)據(jù)挖掘方面的工作。一般而言，Hive自動生成的MapReduce作業(yè)，通常情況下不夠智能化，因而效率比較低，調(diào)優(yōu)比較困難，粒度較粗。Hive的缺點(diǎn)7.1Hive簡介7.1.3Hive和傳統(tǒng)數(shù)據(jù)庫比較Hive和數(shù)據(jù)庫除了擁有類似的查詢語言，再無類似之處。Hive構(gòu)架原理7.27.2Hive構(gòu)架原理作為基于Hadoop的主要數(shù)據(jù)倉庫解決方案,HiveSQL是主要的交互接口,實(shí)際的數(shù)據(jù)保存在HDFS文件中，真正的計算和執(zhí)行則由MapReduce完成，而它們之間的橋梁是Hive引擎。7.2Hive構(gòu)架原理7.2.1用戶接口shell/CLI,jdbc/odbc,webuicommandlineinterfaceCLI,shell終端命令行，采用交互式使用hive命令行與hive進(jìn)行交互JDBC/ODBC，是hive基于JDBC提供的客戶端，用戶通過它連接至hiveserver服務(wù)WebUI，通過瀏覽器訪問hive7.2Hive構(gòu)架原理7.2.2元數(shù)據(jù)Metastore表所屬數(shù)據(jù)庫（默認(rèn)是default）、表的擁有者、表名及表的注釋、字段及字段的注釋、列/分區(qū)字段、表的類型（是否是外部表）、表數(shù)據(jù)所在目錄等。而表里面具體的內(nèi)容則在HDFS里，很多框架比如Atlas就是監(jiān)控元數(shù)據(jù)庫matestore中的表信息來實(shí)現(xiàn)元數(shù)據(jù)管理。元數(shù)據(jù)默認(rèn)存儲在自帶的derby數(shù)據(jù)庫（小巧但是很多缺點(diǎn)，比如不支持并發(fā)連接，可以理解為輕量級的MySQL數(shù)據(jù)庫）中，一般都采用MySQL存儲Metastore（即換成用MySQL來存元數(shù)據(jù)）。7.2Hive構(gòu)架原理7.2.3驅(qū)動器：DriverHive通過給用戶提供的一系列交互接口，接收到用戶的指令(SQL)，使用自己的Driver，結(jié)合元數(shù)據(jù)(MetaStore)，將這些指令翻譯成MapReduce，提交到Hadoop中執(zhí)行，最后，將執(zhí)行返回的結(jié)果輸出到用戶交互接口。7.2Hive構(gòu)架原理7.2.3驅(qū)動器：Driver（1）解析器（SQLParser）：將SQL字符串轉(zhuǎn)換成抽象語法樹AST，這一步一般都用第三方工具庫完成，比如antlr；對AST進(jìn)行語法分析，比如表是否存在、字段是否存在、SQL語義是否有誤。（2）編譯器（PhysicalPlan）：將AST編譯生成邏輯執(zhí)行計劃。Hive的數(shù)據(jù)存儲在HDFS中，大部分的查詢由MapReduce完成7.2Hive構(gòu)架原理7.2.3驅(qū)動器：Driver（3）優(yōu)化器（QueryOptimizer）：對邏輯執(zhí)行計劃進(jìn)行優(yōu)化。（4）執(zhí)行器（Execution）：把邏輯執(zhí)行計劃轉(zhuǎn)換成可以運(yùn)行的物理計劃。Hive數(shù)據(jù)類型及應(yīng)用7.37.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.1數(shù)據(jù)類型（1）簡單類型HIVEMySQLJAVA長度例子TINYINTTINYINTbyte1byte有符號整數(shù)2SMALINTSMALINTshort2byte有符號整數(shù)20INTINTint4byte有符號整數(shù)20BIGINTBIGINTlong8byte有符號整數(shù)20BOOLEAN無boolean布爾類型，true或者falseTRUEFALSEFLOATFLOATfloat單精度浮點(diǎn)數(shù)3.14159DOUBLEDOUBLEdouble雙精度浮點(diǎn)數(shù)3.14159STRINGVARCHARstring字符系列?？梢灾付ㄗ址？梢允褂脝我柣蛘唠p引號?！畁owisthetime’“forallgoodmen”TIMESTAMPTIMESTAMP

時間類型

BINARYBINARY

字節(jié)數(shù)組

Hive基本數(shù)據(jù)類型7.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.1數(shù)據(jù)類型（2）復(fù)雜類型STRUCT：和C語言中的struct類似，都可以通過“點(diǎn)”符號訪問元素內(nèi)容。例如，如果某個列的數(shù)據(jù)類型是STRUCT{firstSTRING,lastSTRING},那么第1個元素可以通過字段.first來引用，例如struct<street:string,city:string>。7.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.1數(shù)據(jù)類型（2）復(fù)雜類型MAP：MAP是一組鍵-值對元組集合，使用數(shù)組表示法可以訪問數(shù)據(jù)。例如，如果某個列的數(shù)據(jù)類型是MAP，其中鍵->值對是’first’->’John’和’last’->’Doe’，那么可以通過字段名[‘last’]獲取最后一個元素，例如map<string,int>。7.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.1數(shù)據(jù)類型（2）復(fù)雜類型ARRAY數(shù)組：是一組具有相同類型和名稱的變量的集合。這些變量稱為數(shù)組的元素，每個數(shù)組元素都有一個編號，編號從零開始。例如：數(shù)組值為[‘John’,‘Doe’]，那么第2個元素可以通過數(shù)組名[1]進(jìn)行引用。7.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.1數(shù)據(jù)類型（3）案例分析1）某表有如用JSON格式來表示其數(shù)據(jù)結(jié)構(gòu)的內(nèi)容。2）基于上述數(shù)據(jù)結(jié)構(gòu)，我們在Hive里創(chuàng)建對應(yīng)的表，并導(dǎo)入數(shù)據(jù)。創(chuàng)建本地測試文件test.txt，內(nèi)容如下：wukong,bingbing_lili,xiaobai:18_xiaohei:19,huaguoshan_jiangsu_10010zhubajie,caicai_susu,xiaohong:18_xiaolan:19,lianyundong_gaolaozhuang_100117.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.1數(shù)據(jù)類型（3）案例分析3）Hive上創(chuàng)建測試表test，具體的代碼如下：createtabletest(namestring,friendsarray<string>,childrenmap<string,int>,addressstruct<street:string,city:string,email:int>)rowformatdelimitedfieldsterminatedby','collectionitemsterminatedby'_'mapkeysterminatedby':'linesterminatedby'\n';7.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.1數(shù)據(jù)類型（3）案例分析4）導(dǎo)入文本數(shù)據(jù)到測試表0:jdbc:hive2://hadoop105:10000>loaddatalocalinpath'/opt/module/hive-3.1.2/datas/test.txt'intotabletest;5）訪問三種集合列里的數(shù)據(jù)，以下分別是ARRAY，MAP，STRUCT的訪問方式。0:jdbc:hive2://hadoop105:10000>selectfriends[1],children['xiaobai'],address.cityfromtestwherename="wukong";7.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.2類型轉(zhuǎn)化隱式類型轉(zhuǎn)換規(guī)則如下：任何整數(shù)類型都可以隱式地轉(zhuǎn)換為一個范圍更廣的類型，如TINYINT可以轉(zhuǎn)換成INT，INT可以轉(zhuǎn)換成BIGINT。所有整數(shù)類型、FLOAT和STRING類型都可以隱式地轉(zhuǎn)換成DOUBLE。TINYINT、SMALLINT、INT都可以轉(zhuǎn)換為FLOAT。BOOLEAN類型不可以轉(zhuǎn)換為任何其它的類型。7.3Hive數(shù)據(jù)類型及應(yīng)用離線分析系統(tǒng)7.3.2類型轉(zhuǎn)化可以使用CAST操作顯示地進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換：0:jdbc:hive2://hadoop105:10000>select'1'+2,cast('1'asint)+2;Hive的數(shù)據(jù)模型7.47.4Hive的數(shù)據(jù)模型Hive中包含以下數(shù)據(jù)模型:Table、Partition、Bucket、ExternalTable。(1）表（Table)Hive中的Table和關(guān)系型數(shù)據(jù)庫中的Table，在概念上是類似的，每一個Table在Hive中都有一個相應(yīng)的HDFS中的目錄存儲數(shù)據(jù)。例如，一個表pvs，它在HDFS中的路徑為:/wh/pvs，其中，wh是在hive-site.xml中由${hive.metastore.warehouse.dir}指定的數(shù)據(jù)倉庫的目錄，所有的Table數(shù)據(jù)（不包括ExternalTable）都保存在這個目錄中。7.4Hive的數(shù)據(jù)模型Hive中包含以下數(shù)據(jù)模型:Table、Partition、Bucket、ExternalTable。(2）分區(qū)（Partition）Partition類似于關(guān)系型數(shù)據(jù)庫中的Partition列的密集索引，但是Hive中Partition的組織方式和數(shù)據(jù)庫中的有很大不同。在Hive中，表中的一個Partition對應(yīng)于表下的一個目錄,所有的Partition的數(shù)據(jù)都存儲在對應(yīng)的目錄中。分區(qū)表實(shí)際上就是對應(yīng)一個HDFS文件系統(tǒng)上的獨(dú)立的文件夾，該文件夾下是該分區(qū)所有的數(shù)據(jù)文件。Hive中的分區(qū)就是分目錄，把一個大的數(shù)據(jù)集根據(jù)業(yè)務(wù)需要分割成小的數(shù)據(jù)集。在查詢時通過WHERE子句中的表達(dá)式選擇查詢所需要的指定的分區(qū)，這樣的查詢效率會提高很多。7.4Hive的數(shù)據(jù)模型Hive中包含以下數(shù)據(jù)模型:Table、Partition、Bucket、ExternalTable。（3）分桶表（Bucket）分區(qū)提供一個隔離數(shù)據(jù)和優(yōu)化查詢的便利方式。不過，并非所有的數(shù)據(jù)集都可形成合理的分區(qū)。對于一張表或者分區(qū)，Hive可以進(jìn)一步組織成桶，也就是更為細(xì)粒度的數(shù)據(jù)范圍劃分。分桶是將數(shù)據(jù)集分解成更容易管理的若干部分的另一個技術(shù)。分區(qū)針對的是數(shù)據(jù)的存儲路徑；分桶針對的是數(shù)據(jù)文件。7.4Hive的數(shù)據(jù)模型Hive中包含以下數(shù)據(jù)模型:Table、Partition、Bucket、ExternalTable。(4）外部表（ExternalTable)ExternalTable指向已經(jīng)在HDFS中存在的數(shù)據(jù)目錄，它可以創(chuàng)建Table和Partition,表中數(shù)據(jù)假設(shè)是用Hive兼容的格式。它和Table在元數(shù)據(jù)的組織上是相同的，而實(shí)際數(shù)據(jù)的存儲則有較大的差異。Table的創(chuàng)建過程和數(shù)據(jù)加載過程是兩個過程（但這兩個過程可以在同一個語句中完成)。在加載數(shù)據(jù)的過程中，實(shí)際數(shù)據(jù)會被移動到數(shù)據(jù)倉庫目錄中;之后對數(shù)據(jù)的訪問將會直接在數(shù)據(jù)倉庫目錄中完成。刪除表時，表中的數(shù)據(jù)和元數(shù)據(jù)將會被同時刪除。DDL數(shù)據(jù)庫定義語言應(yīng)用7.57.5DDL數(shù)據(jù)庫定義語言應(yīng)用1.創(chuàng)建數(shù)據(jù)庫2.查看數(shù)據(jù)庫7.5DDL數(shù)據(jù)庫定義語言應(yīng)用3.切換數(shù)據(jù)庫4.刪除數(shù)據(jù)庫hive>dropdatabaseifexistsDATABASES;7.5DDL數(shù)據(jù)庫定義語言應(yīng)用5.創(chuàng)建表創(chuàng)建一個外部表，表有字段emp，ename，job,mgr,hiredate等信息。hive>createexternaltableifnotexistsdefault.emp(empnoint,enamestring,jobstring,mgrint,hiredatestring,saldouble,commdouble,deptnoint)rowformatdelimitedfieldsterminatedby'\t';7.5DDL數(shù)據(jù)庫定義語言應(yīng)用6.查看所有表7.查看表信息7.5DDL數(shù)據(jù)庫定義語言應(yīng)用8.查看拓展描述信息9.刪除表hive>droptableemp;DML數(shù)據(jù)操作語言應(yīng)用7.67.6DML數(shù)據(jù)操作語言應(yīng)用Load語法結(jié)構(gòu)LOADDATA[LOCAL]INPATH'filepath'[OVERWRITE]INTOTABLEtablename[PARTITION(partcol1=val1,partcol2=val2...)]說明：Load操作只是單純的復(fù)制/移動操作，將數(shù)據(jù)文件移動到Hive表對應(yīng)的位置。filepath：相對路徑，例如：project/data1絕對路徑，例如：/user/hive/project/data1包含模式的完整URI，例如：hdfs://namenode:9000/user/hive/project/data17.6DML數(shù)據(jù)操作語言應(yīng)用LOCAL如果指定了LOCAL，那么：load命令會去查找本地文件系統(tǒng)中的filepath。如果發(fā)現(xiàn)是相對路徑，則路徑會被解釋為相對于當(dāng)前用戶的當(dāng)前路徑。load命令會將filepath中的文件復(fù)制到目標(biāo)文件系統(tǒng)中。目標(biāo)文件系統(tǒng)由表的位置屬性決定。被復(fù)制的數(shù)據(jù)文件移動到表的數(shù)據(jù)對應(yīng)的位置。如果沒有指定LOCAL關(guān)鍵字，如果filepath指向的是一個完整的URI，hive會直接使用這個URI。否則：如果沒有指定schema或者authority，Hive會使用在hadoop配置文件中定義的schema和authority，

指定了Namenode的URI。如果路徑不是絕對的，Hive相對于/user/進(jìn)行解釋。Hive會將filepath中指定的文件內(nèi)容移動到table（或者partition）所指定的路徑中。7.6DML數(shù)據(jù)操作語言應(yīng)用LOCAL創(chuàng)建一張表hive(default)>createtablestudent(idstring,namestring)rowformatdelimitedfieldsterminatedby'\t';加載本地數(shù)據(jù)（LOCAL關(guān)鍵字）hive(default)>loaddatalocalinpath'/home/hadoop/Downloads/dates/students.txt'intotabledefault.student;7.6DML數(shù)據(jù)操作語言應(yīng)用OVERWRITE如果使用了OVERWRITE關(guān)鍵字，則目標(biāo)表（或者分區(qū)）中的內(nèi)容（如果有）會被刪除，然后再將filepath指向的文件/目錄中的內(nèi)容添加到表/分區(qū)中。如果目標(biāo)表（分區(qū)）已經(jīng)有一個文件，并且文件名和filepath中的文件名沖突，那么現(xiàn)有的文件會被新文件所替代。7.6DML數(shù)據(jù)操作語言應(yīng)用OVERWRITE加載HDFS文件系統(tǒng)數(shù)據(jù)hive(default)>loaddatainpath'/user/atguigu/hive/student.txt'intotabledefault.student;加載數(shù)據(jù)覆蓋表中已有的數(shù)據(jù)hive(default)>loaddatainpath'/user/atguigu/hive/student.txt'overwriteintotabledefault.student;7.6DML數(shù)據(jù)操作語言應(yīng)用Insert將查詢結(jié)果插入Hive表語法結(jié)構(gòu)：Standardsyntax:INSERTOVERWRITETABLEtablename1[PARTITION(partcol1=val1,partcol2=val2...)]select_statement1FROMfrom_statement7.6DML數(shù)據(jù)操作語言應(yīng)用InsertMultipleinserts:INSERTOVERWRITETABLEtablename1[PARTITION(partcol1=val1,partcol2=val2...)]select_statement1FROMfrom_statement[INSERTOVERWRITETABLEtablename2[PARTITION...]select_statement2]...Dynamicpartitioninserts:INSERTOVERWRITETABLEtablenamePARTITION(partcol1[=val1],partcol2[=val2]...)select_statementFROMfrom_statement7.6DML數(shù)據(jù)操作語言應(yīng)用具體實(shí)例創(chuàng)建一張分區(qū)表hive(default)>createtablestudent(idint,namestring)partitionedby(monthstring)rowformatdelimitedfieldsterminatedby'\t';基本插入數(shù)據(jù)hive(default)>insertintotablestudentpartition(month='201709')values(1,'wangwu');7.6DML數(shù)據(jù)操作語言應(yīng)用具體實(shí)例基本模式插入（根據(jù)單張表查詢結(jié)果）hive(default)>insertoverwritetablestudentpartition(month='201708')selectid,namefromstudentwheremonth='201709';多插入模式（根據(jù)多張表查詢結(jié)果）hive(default)>fromstudentinsertoverwritetablestudentpartition(month='201707')selectid,namewheremonth='201709'insertoverwritetablestudentpartition(month='201706')selectid,namewheremonth='201709';7.6DML數(shù)據(jù)操作語言應(yīng)用導(dǎo)出表數(shù)據(jù)語法結(jié)構(gòu)Standardsyntax:INSERTOVERWRITE[LOCAL]DIRECTORYdirectory1SELECT...FROM...Hiveextension(multipleinserts):FROMfrom_statementINSERTOVERWRITE[LOCAL]DIRECTORYdirectory1select_statement1[INSERTOVERWRITE[LOCAL]DIRECTORYdirectory2select_st