第08周數(shù)據(jù)倉(cāng)庫(kù)hive從入門(mén)到小牛附件3基礎(chǔ)使用丨教程

慕課網(wǎng)首 免費(fèi)課 實(shí)戰(zhàn)課 金職 慕課專 手 從所有的詞條中查詢Java3Hive快速了解

徐老師·更新于2020-08- 上一節(jié)2數(shù)據(jù)庫(kù)與數(shù)據(jù) 4Hive實(shí)戰(zhàn)下一Hive的使操作Hv可以在S 命令行下操作，或者是使用JDBC代碼的方式操下面先來(lái)看一下在命令行中操作的方式針對(duì)命令行這種方式，其實(shí)還有兩種使第一個(gè)是使用 下的hive命令，這個(gè)是從hive一開(kāi)始就支持的使用方后來(lái)又出現(xiàn)一個(gè)beeline命令，它是通過(guò)HveSrver2服務(wù)連接hiv，它是一個(gè)輕量級(jí)的客戶端工具，所以后來(lái)開(kāi)始推薦使用這個(gè)。具體使用哪個(gè)我覺(jué)得屬于個(gè)人的一個(gè)習(xí)慣問(wèn)題，特別是一些做了很多年大數(shù)據(jù)開(kāi)發(fā)的人，已經(jīng)習(xí)慣了使hive命令，如果讓我使用beeline會(huì)感覺(jué)有點(diǎn)別針對(duì)我們寫(xiě)的ves通過(guò)哪一種客戶端去執(zhí)行結(jié)果都是一樣的，沒(méi)有任何區(qū)別，所以在這里我們使用哪個(gè)就無(wú)所謂了。1：先看第一種，這種直接就可以連[root@bigdata04apache-hive-3.1.2-bin]#Hive-on-MRisdeprecatedinHive2andmaynotbeavailableinthefutureHiveSessionID=32d36bcb-21b8-488f-8c13-這里有一行信息提示，從Hve開(kāi)始Hv--MR就過(guò)時(shí)了，并且在以后的版本中可能就不了，建議使用其它的計(jì)算引擎，例如：sprk或者z如果你確實(shí)想使用MapReduce引擎，那建議你使用Hive1.x的版下面以v開(kāi)頭的內(nèi)容就說(shuō)明我們進(jìn)入了Hve令行，在這里可以寫(xiě)HvSQ了khive>showTimetaken:0.18創(chuàng)建一個(gè)hive>createtablet1(idint,nameTimetaken:1.064再查看有哪些hive>showTimetaken:0.18向表里面添加數(shù)據(jù)，注意，此時(shí)就產(chǎn)生了MapReduce任hive>insertintot1(id,name)QueryID=root_20200506162317_02b2802a-5640-4656-88e6-Totaljobs=LaunchingJob1outofNumberofreducetasksdeterminedatcompiletime:Inordertochangetheaverageloadforareducer(insetInordertolimittheumnumberofsetInordertosetaconstantnumberofsetStartingJob=job_1588737504319_0001,TrackingURL=KillCommand=/data/soft/hadoop-3.2.0/bin/mapredjob-killHadoopjobinformationforStage-1:numberofmappers:1;numberof2020-05-0616:23:36,954Stage-1map=0%,reduce=2020-05-0616:23:47,357Stage-1map=100%,reduce=0%,CumulativeCPU2020-05-0616:23:56,917Stage-1map=100%,reduce=100%,CumulativeCPUMapReduceTotalcumulativeCPUtime:5seconds160EndedJob=Stage-4isselectedbyconditionStage-3isfilteredoutbyconditionStage-5isfilteredoutbyconditionMovingdatatodirectoryhdfs://bigdata01:9000/user/hive/warehouse/t1/.hive-LoadingdatatotableMapReduceJobsStage-Stage-1:Map:1Reduce: CumulativeCPU:5.16 HDFSRead:TotalMapReduceCPUTimeSpent:5seconds160Timetaken:41.534查詢數(shù)據(jù)，為什么這時(shí)沒(méi)有產(chǎn)生mprece任務(wù)呢？因?yàn)檫@個(gè)計(jì)算太簡(jiǎn)單了，不需要經(jīng)過(guò)arec任務(wù)就可以獲取到結(jié)果，直接表對(duì)應(yīng)的數(shù)據(jù)文件就可以了。11234hive>select*from1Timetaken:2.438seconds,Fetched:1刪除hive>droptableTimetaken:0.89可以輸入quit退出hive令行，或者直接按ctrl+c也可以退 hive>2：接著看一下第二種方[root@bigdata0apachehive3.1.2bin]which:nohbasein(.:/data/soft/jdk1.8/bin:/data/soft/hadoop-2020-05-0616:43:11:StartingSLF4J:ClasspathcontainsmultipleSLF4JSLF4J:Foundbindingin[jar:file:/data/soft/apache-hive-3.1.2-SLF4J:Foundbindingin[jar:file:/data/soft/hadoop- SLF4J:SeeforanexSLF4J:ActualbindingisoftypeHiveSessionID=008af6a0-4f7a-47f0-b45a-HiveSessionID=670a0c62-7744-4949-a25f-HiveSessionID=7aa43b1a-eafb-4848-9d29-HiveSessionID=a5c20828-7f39-4ed6-ba5e-注意了，啟動(dòng)hiveserver2服務(wù)之后，最下面會(huì)輸出幾行HiveSessionID的信息，一定要等到輸出hiveserver2默認(rèn)會(huì)本機(jī)的10000端口，所以命令是這樣bin/beeline-u當(dāng)hiveserver2服務(wù)沒(méi)有真正啟動(dòng)成功之前連接會(huì)提示這樣的信[root@bigdata04apache-hive-3.1.2-bin]#bin/beeline-uConnectingto20/05/0616:44:21[main]:WARNjdbc.HiveConnection:FailedtoconnecttoCouldnotopenconnectiontotheHS2server.PleasechecktheserverURIError:CouldnotopentransportwithJDBCUri:Beelineversion3.1.2byApache等待hiveserver2服務(wù)真正啟動(dòng)之后再連接，此時(shí)就可以連接進(jìn)[root@bigdata04apache-hive-3.1.2-bin]#bin/beeline-uSLF4J:ClasspathcontainsmultipleSLF4JSLF4J:Foundbindingin[jar:file:/data/soft/apache-hive-3.1.2-SLF4J:Foundbindingin[jar:file:/data/soft/hadoop- SLF4J:SeeforanexSLF4J:ActualbindingisoftypeConnectingtoConnectedto:ApacheHive(versionDriver:HiveJDBC(versionTransactionisolation:Beelineversion3.1.2byApache0:接著嘗試一下建0:jdbc:hive2://localhost:10000>createtablet1(idint,nameNorowsaffected(2.459添加數(shù) 0:jdbc:hive2://localhost:10000>insertintot1(id,name)43d1-INFO:SemanticysisCompleted(retrial=INFO:ReturningHiveschema:Schema(fieldSchemas:[FieldSchema(name:_col0,INFO:Completedcompilingcommand(queryId=root_20200506172404_54b9bfc4-INFO:Concurrencymodeisdisabled,notcreatingalockINFO:Executingcommand(queryId=root_20200506172404_54b9bfc4-d67f-43d1-WARN:Hive-on-MRisdeprecatedinHive2andmaynotbeavailableintheINFO:QueryID=root_20200506172404_54b9bfc4-d67f-43d1- INFO:Totaljobs=INFO:LaunchingJob1outofINFO:Startingtask[Stage-1:MAPRED]inserialINFO:Numberofreducetasksdeterminedatcompiletime:INFO:Inordertochangetheaverageloadforareducer(inINFO setINFO:InordertolimittheumnumberofINFO setINFO:InordertosetaconstantnumberofINFO setINFO:Cleaningupthestagingarea/tmp/hadoop-ERROR:JobStocolPB.to.atatatatatjava.security.AccessController.doPrivileged(Nativeatatat org.apache.hadoop.security.AccessControlException:Permissiondenied:發(fā)現(xiàn)添加數(shù)據(jù)報(bào)錯(cuò)，提示用戶對(duì)/tmp/hadoop-yarn沒(méi)有寫(xiě)權(quán)限給hfs中的/tmp/hadoop-yarn設(shè)置77權(quán)限，讓用戶具備權(quán)限可以直接給tmp及下面的所有 設(shè)置77權(quán)限hdfsdfs-od-R777在啟動(dòng)beeline的時(shí)候指定一個(gè)對(duì)這 有操作權(quán)限的用bin/beeline-ujdbc:hive2://localhost:10000-n在這我就直接指定一個(gè)有操作權(quán)限的用戶[root@bigdata04apache-hive-3.1.2-bin]#bin/beeline-uConnectingtoConnectedto:ApacheHive(versionDriver:HiveJDBC(versionTransactionisolation:B 312 h

INFO:Compilingcommand(queryId=root_20200506174646_0087c68d-289a-4fb8-INFO:Concurrencymodeisdisabled,notcreatingalockINFO:SemanticysisCompleted(retrial=INFO:ReturningHiveschema:Schema(fieldSchemas:[FieldSchema(name:_col0,INFO:Completedcompilingcommand(queryId=root_20200506174646_0087c68d-INFO:Concurrencymodeisdisabled,notcreatingalockINFO:Executingcommand(queryId=root_20200506174646_0087c68d-289a-4fb8-WARN:Hive-on-MRisdeprecatedinHive2andmaynotbeavailableintheINFO:QueryID=root_20200506174646_0087c68d-289a-4fb8-89d4-INFO:Totaljobs=INFO:LaunchingJob1outofINFO:Startingtask[Stage-1:MAPRED]inserialINFO:Numberofreducetasksdeterminedatcompiletime:INFO:Inordertochangetheaverageloadforareducer(inINFO setINFO:InordertolimittheumnumberofINFO setINFO:InordertosetaconstantnumberofINFO setINFO:numberofINFO:Submittingtokensforjob:INFO:Executingwithtokens:INFO:Theurltotrackthejob:INFO:StartingJob=job_1588756653704_0002,TrackingURL=INFO:KillCommand=/data/soft/hadoop-3.2.0/bin/mapredjob-killINFO:HadoopjobinformationforStage-1:numberofmappers:1;numberofINFO:2020-05-0617:47:01,940Stage-1map=0%,reduce=INFO:2020-05-0617:47:09,397Stage-1map=100%,reduce=0%,INFO:2020-05-0617:47:18,642Stage-1map=100%,reduce=100%,INFO:MapReduceTotalcumulativeCPUtime:3seconds240INFO:EndedJob=INFO:Startingtask[Stage-7:CONDITIONAL]inserialINFO:Stage-4isselectedbyconditionINFO:Stage-3isfilteredoutbyconditionINFO:Stage-5isfilteredoutbyconditionINFO:Startingtask[Stage-4:MOVE]inserialINFO:MovingdatatodirectoryINFO:Startingtask[Stage-0:MOVE]inserialINFO:Loadingdatatotabledefault.t1fromINFO:Startingtask[Stage-2:STATS]inserialINFO:MapReduceJobsINFO:Stage-Stage-1:Map:1Reduce: CumulativeCPU:3.24 HDFSINFO:TotalMapReduceCPUTimeSpent:3seconds240INFO:Completedexecutingcommand(queryId=root_20200506174646_0087c68d-INFO:INFO:Concurrencymodeisdisabled,notcreatingalockNorowsaffected(35.069查詢數(shù)0:jdbc:hive2://localhost:10000>select*fromINFO:Compilingcommand(queryId=root_20200506174821_360087a8-eb30-49d3-INFO:Concurrencymodeisdisabled,notcreatingalockINFO:SemanticysisCompleted(retrial=INFO:ReturningHiveschema:Schema(fieldSchemas:[FieldSchema(name:t1.id,INFO:Completedcompilingcommand(queryId=root_20200506174821_360087a8-INFO:Concurrencymodeisdisabled,notcreatingalockINFO:Executingcommand(queryId=root_20200506174821_360087a8-eb30-49d3-INFO:Completedexecutingcommand(queryId=root_20200506174821_360087a8-INFO: didibl l |t1.id| | | 1rowselected(1.2110:此時(shí)使用bin/hive命令行查看也是可以的，這兩種方式的是同一份11234hive>select*from1Timetaken:2.438seconds,Fetched:1注意：在beeline后面指定hiveserver2的地址的時(shí)候，可以指定當(dāng)前機(jī)器的內(nèi)網(wǎng)ip也是可以的[root@bigdata04apache-hive-3.1.2-bin]#bin/beeline-uConnectingtoConnectedto:ApacheHive(versionDriver:HiveJDBC(versionTransactionisolation:Beelineversion3.1.2byApache0:退出beeline客戶端，按ctrl+c即可后面我們使用的時(shí)候我還是使用hv命令，已經(jīng)習(xí)慣用這個(gè)了，還有一個(gè)就是大家如果也用這個(gè)的話，別人是不是感覺(jué)你也是老了，但是你要知道目前是推薦使用ben命令的在工作中我們?nèi)绻龅搅嗣刻於夹枰獔?zhí)行令，那我肯定想要把具體的執(zhí)行s寫(xiě)到中去執(zhí)行，但是現(xiàn)在這種用法每次都需要開(kāi)啟一個(gè)會(huì)話，好像還沒(méi)辦法把命令寫(xiě)到中。注意了，ve后面可以使用-e命令，這樣這條ve命令就可以放到中定時(shí)調(diào)度執(zhí)行了因?yàn)檫@樣每次v[root@bigdata04apache-hive-3.1.2-bin]#bin/hive-e"select*fromHiveSessionID=efadf29a-4ed7-4aba-84c8-3Logginginitializedusingconfigurationinjar:file:/data/soft/apache-HiveSessionID=65b9718b-4030-4c0f-a557- Timetaken:3.263seconds,Fetched:1[root@bigdata04apache-hive-3.1.2-當(dāng)然了beeline也可以，后面也是跟一個(gè)-e參[root@bigdata04apache-hive-3.1.2-bin]#bin/beeline-uConnectingtojdbc:hive2://192168182Driver:HiveJDBC(versionTransactionisolation:INFO:Compilingcommand(queryId=root_20200506191420_9132f97a-af65-4f5a-INFO:Concurrencymodeisdisabled,notcreatingalockINFO:SemanticysisCompleted(retrial=INFO:ReturningHiveschema:Schema(fieldSchemas:[FieldSchema(name:t1.id,INFO:Completedcompilingcommand(queryId=root_20200506191420_9132f97a-INFO:Concurrencymodeisdisabled,notcreatingalockINFO:Executingcommand(queryId=root_20200506191420_9132f97a-af65-4f5a-INFO:Completedexecutingcommand(queryId=root_20200506191420_9132f97a-INFO:INFO:Concurrencymodeisdisabled,notcreatingalock |t1.id| | | 1rowselected(0.307Beelineversion3.1.2byApacheClosing:0:[root@bigdata04apache-hive-3.1.2-此時(shí)我們?cè)侔裩ive的 配置到path環(huán)境變量中，在直接使用hive或者beeline就可以[root@bigdata04apache-hive-3.1.2-bin]#viexportexportHADOOP_HOME=/data/soft/hadoop-exportHIVE_HOME=/data/soft/apache-hive-3.1.2-export[root@bigdata04apache-hive-3.1.2-bin]#sourceJDBC這種方式也需要連接vsrvr2服務(wù)，前面我們已經(jīng)啟動(dòng)了vesrvr服務(wù)，在這里直接使用就可以了創(chuàng)建maven項(xiàng)目在pom中添加hive-jdbchive-jdbc<artifactId>hive-開(kāi)發(fā)代碼，創(chuàng)建包名：創(chuàng)建類名代碼如下 package2importimportimportimport *JDBC代碼操作*注意：需要先啟動(dòng)hiveserver2服*CreatedbypublicclassHiveJdbcDemopublicstaticvoidmain(String[]args)throws//指定hiveserver2StringjdbcUrl=//獲取jdbc連接，這里的user使用root，就是linux中的用戶名，password隨便指Connectionconn=DriverManager.getConnection(jdbcUrl,"root",//獲取Statementstmt=//指定查詢的Stringsql="select*from//執(zhí)行ResultSetres=//循環(huán)結(jié)while 執(zhí)行代碼，可以看到查詢出來(lái)的結(jié)果，但是會(huì)打印出來(lái)一堆紅色的警告信SLF4J:ClasspathcontainsmultipleSLF4JSLF4J:Foundbindingin[jar:file:/D:/.m2/org/apache/logging/log4j/log4j-SLF4J:Foundbindingin[jar:file:/D:/.m2/org/slf4j/slf4j-SLF4J:SeeforanexSLF4J:ActualbindingisoftypeERRORStatusLoggerNolog4j2configurationfilefound.Usingdefault 分析上面的警告信息，發(fā)現(xiàn)現(xiàn)在是有兩個(gè)lgj的實(shí)現(xiàn)類，需要去掉一個(gè)，還有就是缺少lgjo42的配置文件是x格式的，不是roris1:去掉多余的log4j依賴，從日志中可以看到日志的路org/apache/logging/log4j/log4j-slf4j-impl/2.10.0/log4j-slf4j-impl-org/apache/logging/log4j/log4j-slf4j-impl/2.10.0/log4j-slf4j-impl-org/slf4j/slf4j-log4j12/1.6.1/slf4j-log4j12-這兩個(gè)去掉哪個(gè)都可以，這兩個(gè)都是hve-jdbc這個(gè)依賴帶過(guò)來(lái)的，所以需要修改pom文件中hve-j的依賴hive-jdbc<artifactId>hive-去掉log4j依賴 2：在項(xiàng)目的 中增加log4j2.xml配置文<?xml<?xmlversion="1.0"encoding="UTF-<Configuration<Consolename="Console"<PatternLayoutpattern="%d{YYYY-MM-ddHH:mm:ss}[%t]%-5p<Root<AppenderRefref="Console"再執(zhí)行代碼，打印的就只有結(jié)果1在vs其實(shí)就是臨時(shí)修改v-sx中參數(shù)的值不過(guò)通過(guò)set命令設(shè)置的參數(shù)只在當(dāng)前會(huì)話有效，退出重新打開(kāi)如果想要對(duì)當(dāng)前機(jī)器上的當(dāng)前用戶有效的話可以把命令配置在~/.hiverc文件所以總結(jié)一下，使用st命令配置的參數(shù)是當(dāng)前會(huì)話有效，在/.hvrc文件中配置的是當(dāng)前機(jī)器中的當(dāng)前用戶有效，而在ve-sexl中配置的則是永久有效了，在hive-site.xml中有一個(gè)參數(shù)是hive.cli.print.current.db，這個(gè)參數(shù)可以顯示當(dāng)前所在的在這里我們?cè)O(shè)置為hive>sethive.cli.print.current.db=hive還有一個(gè)參數(shù)hive.cli.print.header可以控制獲取結(jié)果的時(shí)候顯示字段名稱，這樣看起來(lái)會(huì)比較清hive(default)>select*from Timetaken:0.184seconds,Fetched:1hive(default)>sethive.cli.print.header=hive(default)>select*from Timetaken:0.202seconds,Fetched:1這些參數(shù)屬于的個(gè)人習(xí)慣，所以我希望把這個(gè)配置放到我個(gè)人用戶下修改~/.hiverc，我們每次在進(jìn)入hive命令行的時(shí)候都會(huì)加載當(dāng)前用 下的.hiverc文件中的內(nèi)[root@bigdata04apache-hive-3.1.2-bin]#visethive.cli.print.current.db=sethive.cli.print.header=這個(gè)時(shí)候重新進(jìn)來(lái)確認(rèn)一下效 hive如果我們想查看一下hive的歷史操作命令如何查看呢linux中有一個(gè)history命令可以查看歷史操作命hive中也有類似的功能，hive中的歷史命令會(huì)在當(dāng)前用 下的 [root@bigdata04[root@bigdata04apache-hive-3.1.2-bin]#moreshowHive的日[root@bigdata04conf]#which:nohbasein(.:/data/soft/jdk1.8/bin:/data/soft/hadoop-SLF4J:ClasspathcontainsmultipleSLF4JSLF4J:Foundbindingin[jar:file:/data/soft/apache-hive-3.1.2-SLF4J:Foundbindingin[jar:file:/data/soft/hadoop- SLF4J:SeeforanexSLF4J:ActualbindingisoftypeHiveSessionID=3b4db1d6-d283-48a7-b986-9Logginginitializedusingconfigurationinjar:file:/data/soft/apache-hive-HiveSessionID=83532edf-47e9-47ef-87fc-Hive-on-MRisdeprecatedinHive2andmaynotbeavailabl