HIVE操作的常見問題匯總

本篇內(nèi)容主要講解“HIVE操作的常見問題匯總”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“HIVE操作的常見問題匯總”吧!

1、Terminal initialization failed; falling back to unsupported

[ERROR] Terminal initialization failed; falling back to unsupported
java.lang.IncompatibleClassChangeError: Found class jline.Terminal, but interface was expected
        at jline.TerminalFactory.create(TerminalFactory.java:101)
        at jline.TerminalFactory.get(TerminalFactory.java:158)
        at jline.console.ConsoleReader.<init>(ConsoleReader.java:229)
        at jline.console.ConsoleReader.<init>(ConsoleReader.java:221)
        at jline.console.ConsoleReader.<init>(ConsoleReader.java:209)
        at org.apache.hadoop.hive.cli.CliDriver.setupConsoleReader(CliDriver.java:787)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:721)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:136)

Exception in thread "main" java.lang.IncompatibleClassChangeError: Found class jline.Terminal, but interface was expected
        at jline.console.ConsoleReader.<init>(ConsoleReader.java:230)
        at jline.console.ConsoleReader.<init>(ConsoleReader.java:221)
        at jline.console.ConsoleReader.<init>(ConsoleReader.java:209)
        at org.apache.hadoop.hive.cli.CliDriver.setupConsoleReader(CliDriver.java:787)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:721)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:221)

        at org.apache.hadoop.util.RunJar.main(RunJar.java:136)

解決方案：

引起該問題的主要原因是因為Hadoop里面yarn的lib中含有和hive的lib目錄中版本不一致的jline的jar包，只需要把hive中的jline包同步過去就可以了,先刪除yarn的lib中的jline包，再復(fù)制

如：cp /u01/apache-hive-1.2.1-bin/lib/jline-2.12.jar /u01/hadoop-2.6.1/share/hadoop/yarn/lib

2、hive使用derby作為元數(shù)據(jù)庫找不到所創(chuàng)建表的原因

很多初學(xué)者安裝hive使用的是默認元數(shù)據(jù)庫derby，這里面容易遇到下面問題：

安裝好了hive 環(huán)境，建表后關(guān)閉連接窗口，再重新進表就不見了,這是哪里配置的不對？
1.命令行鍵入 hive后創(chuàng)建表，
2.在show tables; 顯示表列表，上面顯示剛才創(chuàng)建的表
3.把窗口關(guān)閉，再重新開個窗口連接服務(wù)器，鍵入hive ，然后show table，表看不到了

為什么會找不到創(chuàng)建的表，這個并沒有消失，而是你的位置不對。

A.如果我們在home下面進入hive，那么我們的metastore_db,就會產(chǎn)生在home目錄下。
B.如果我們在/usr目錄下進入hive，那么我們的metastore_db,就會產(chǎn)生在usr目錄下。

所以如果你更換了目錄啟動hive，那么之前的創(chuàng)建的表你是找不到的。

什么是不同路徑下進入hive,例如

實驗一：目錄下創(chuàng)建表，查看表，不切換目錄操作

查看當(dāng)前目錄，這里是/home/bdata/data目錄

[bdata@bdata4 data]$ pwd

/home/bdata/data

查看內(nèi)容，目前只有IY02_C.txt文件

[bdata@bdata4 data]$ ls

IY02_C.txt

執(zhí)行hive命令，進入客戶端

[bdata@bdata4 data]$ hive

Logging initialized using configuration in jar:file:/u01/apache-hive-1.2.1-bin/lib/hive-common-1.2.1.jar!/hive-log4j.properties
hive> show tables;
OK

Time taken: 0.64 seconds

#創(chuàng)建一個表結(jié)構(gòu)，根據(jù)|分割

hive> CREATE TABLE YHJHK_IY02(AAB301 String,AAE043 String,AAB001 String,AAC001 String,AAC003 String,AAC004 String,AAC002 String,AAC005 String,AAC006 String,AAC009 String,AAC012 String,AAC028 String,AAC064 String,AAC087 String,AAC007 String,AAC032 String,AIC001 int, AIC090 int, AAC031 String,AAE160 String,AIC104 int, AIC164 int, AIC020 int, AAC095 int, AAC096 int, AAC097 int, AAC098 int, AAC099 int, AAC103 int, AAC104 int, AIC268 int, AIC115 int, AIC116 int, AIC117 int, AAB000 int, SUBJOB_ID int, VZE105 String, VZE003 String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE;

#加載HDFS中的文件，這里/data/IY02_C.txt是我們提前存放到hdfs文件系統(tǒng)下的

hive> load data inpath '/data/IY02_C.txt' into table YHJHK_IY02;

#再次查看表，確定存在表yhjhk_iy02

hive> show tables;
OK
yhjhk_iy02

Time taken: 0.686 seconds, Fetched: 1 row(s)

#退出

hive> quit;

再次查看該目錄，則多了兩個文件metastore_db, derby.log

[bdata@bdata4 data]$ ls

derby.log  IY02_C.txt  metastore_db

實驗二：切換目錄，進入hive，查看表信息

回到主目錄

[bdata@bdata4 data]$ cd

查看當(dāng)前路徑，確認目錄已經(jīng)切換

[bdata@bdata4 ~]$ pwd

/home/bdata

查看當(dāng)前目錄，確認是新目錄，沒有metastore_db和derby.log，只有一個data目錄

[bdata@bdata4 ~]$ ls

data

進入hive客戶端

[bdata@bdata4 ~]$ hive

Logging initialized using configuration in jar:file:/u01/apache-hive-1.2.1-bin/lib/hive-common-1.2.1.jar!/hive-log4j.properties

#查看表信息，這里查詢出沒有任何表信息

hive> show tables;
OK

Time taken: 0.64 seconds

hive> quit;

查看當(dāng)前目錄，已經(jīng)生成了metastore_db和derby.log文件

[bdata@bdata4 ~]$ ls

data metastore_db derby.log

通過以上方式確定，我們在用derby的時候，如果突然執(zhí)行hive查詢表時，沒有查詢到數(shù)據(jù)，此時，不用擔(dān)心，表數(shù)據(jù)并未消失，只需要切換到之前進行hive建表的目錄執(zhí)行客戶端操作即可,或者提前切換為MYSQL來存儲原信息則不會出現(xiàn)該問題。

3、執(zhí)行建表命令報錯：FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.)

這個是由于我的mysql不再本地(默認使用本地數(shù)據(jù)庫)，這里需要配置遠端元數(shù)據(jù)服務(wù)器，然后在hive服務(wù)端啟動元數(shù)據(jù)存儲服務(wù)，metastore用于和mysql之間的表結(jié)構(gòu)創(chuàng)建或更新時通訊

首先在配置文件配置metastore的配置，如下

<property> 

     <name>hive.metastore.uris</name> 

     <value>thrift://192.168.10.34:9083</value>

     <description>jdbc/odbc connection hive,if mysql must set </description>  

</property>

配置好后，啟動通訊服務(wù)：

[bdata@bdata4 bin]$ ./hive --service metastore -hiveconf hbase.zookeeper.quorum=bdata1,bdata2,bdata3 -hiveconf hbase.zookeeper.property.clientPort=2181  &

4、執(zhí)行HIVE命令報錯：Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
        at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:522)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:677)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
        at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1523)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:86)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:132)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104)
        at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3005)
        at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3024)
        at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:503)
        ... 8 more
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1521)
        ... 14 more
Caused by: MetaException(message:Could not connect to meta store using any of the URIs provided. Most recent failure: org.apache.thrift.transport.TTransportException: java.net.ConnectException: Connection refused
        at org.apache.thrift.transport.TSocket.open(TSocket.java:187)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:420)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:236)
        at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:74)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1521)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:86)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:132)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104)
        at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3005)
        at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3024)
        at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:503)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:677)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: java.net.ConnectException: Connection refused
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:579)
        at org.apache.thrift.transport.TSocket.open(TSocket.java:182)
        ... 22 more
)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:466)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:236)
        at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:74)

        ... 19 more

解決辦法

同問題3解決辦法一致

5、建表時，報錯：FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

解決辦法：

修改數(shù)據(jù)庫的字符集

登錄數(shù)據(jù)庫，進入HIVE數(shù)據(jù)庫，刪除所有的表

執(zhí)行查詢命令：

alter database hive character set latin1;

修改成功后，重啟metastore，重新進入hive客戶端執(zhí)行創(chuàng)建表的操作即可成功

6.hiveserver2啟動后，beeline不能連接的涉及的問題：
原因：權(quán)限問題
解決：
/user/hive/warehouse
/tmp
/history (如果配置了jobserver 那么/history也需要調(diào)整)
這三個目錄，hive在運行時要讀取寫入目錄里的內(nèi)容，所以把權(quán)限放開，設(shè)置權(quán)限：

hadoop fs -chmod -R 777 /hive/tmp

hadoop fs -chmod -R 777 /hive/warehouse

7.beeline 鏈接拒絕報錯信息
原因：官方的一個bug
解決：
hive.server2.long.polling.timeout

hive.server2.thrift.bind.host 注意把host改成自己的host

8、java.lang.IllegalArgumentException: java.net.UnknownHostException: dfscluster

解決辦法：

找不到hdfs集群名字dfscluster,這個文件在HADOOP的etc/hadoop下面，有個文件hdfs-site.xml，復(fù)制到hive的conf下，重啟即可

[bdata@bdata4 hadoop]$ cp  hdfs-site.xml /u01/apache-hive-1.2.1-bin/conf/

到此，相信大家對“HIVE操作的常見問題匯總”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！