第7章數據倉庫Hive《大數據技術基礎教程》學習目標/Target了解數據倉庫Hive的架構原理、數據類型和數據模型掌握數據倉庫Hive的DML和DDL的基本操作掌握數據倉庫Hive

JDBC的基本操作章節概述/

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

時間類型

BINARYBINARY

字節數組

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

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