基於MySQL中繼資料的Hive的安裝和簡單測試

標籤：hive   hadoop叢集   thrift   ubuntu   安裝   

引言：　Hive是一種強大的資料倉儲查詢語言，類似SQL，本文將介紹如何搭建Hive的開發測試環境。

1. 什麼是Hive?

   hive是基於Hadoop的一個資料倉儲工具，可以將結構化的資料檔案映射為一張資料庫表，並提供簡單的sql查詢功能，可以將sql語句轉換為MapReduce任務進行運行。 其優點是學習成本低，可以通過類SQL語句快速實現簡單的MapReduce統計，不必開發專門的MapReduce應用，十分適合資料倉儲的統計分析。

2.  按照Ｈive的準備條件

    2.1  Hadoop叢集環境已經安裝完畢

　2.2 本文使用Ubuntu做為開發環境(14.04)

３. 安裝步驟

    3.1 下載Ｈｉｖｅ包：apache-hive-0.13.1-bin.tar.gz

    3.2 將其解壓到/opt目錄下

　　　tar xzvf apache-hive-0.13.1-bin.tar.gz

    3.3 設定環境變數

   export HIVE_HOME=/opt/apache-hive-0.13

   export PATH=$PATH:$HIVE_HOME/bin

   export CLASSPATH=$CLASSPATH:$HIVE_HOME/bin

   3.4. 修改hive-env.xml，複製hive-env.xml.template.

    # Set HADOOP_HOME to point to a specific hadoop install directory

    HADOOP_HOME=/opt/hadoop-1.2.1

    # Hive Configuration Directory can be controlled by:

     export HIVE_CONF_DIR=/opt/apache-hive-0.13/conf

   3.5 修改hive-site.xml,主要修改資料庫的串連資訊.

 <property>  <name>hive.metastore.uris</name>  <value>thrift://127.0.0.1:9083</value>  <description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description></property><property>  <name>javax.jdo.option.ConnectionURL</name>  <value>jdbc:mysql://BladeStone-Laptop:3306/hive?createDatabaseIfNotExist=true</value>  <description>JDBC connect string for a JDBC metastore</description></property><property>  <name>javax.jdo.option.ConnectionDriverName</name>  <value>com.mysql.jdbc.Driver</value>  <description>Driver class name for a JDBC metastore</description></property><property>  <name>javax.jdo.option.ConnectionUserName</name>  <value>hive</value>  <description>username to use against metastore database</description></property><property>  <name>javax.jdo.option.ConnectionPassword</name>  <value>123456</value>  <description>password to use against metastore database</description></property>

3.6 安裝ｍｙｓｑｌ資料庫(Ubuntu系統)

  sudo apt-get install mysql-server

  
3.7 建立mysql使用者hive

 

  3.8 在mysql中建立hive資料庫

   

  3.9  下載mysql驅動,並將驅動複製到hive_home／lib類庫

　　mysql-connector-java-5.1.31-bin.jar

  3.10 啟動Hive

    

 3.11 在Ｈive中建立表

    

  3.12 登入mysql，訪問hive資料庫

     

     

     3.13 刪除Hive中的表

     

    3.14 登入mysql,查詢TBLS中的資料

     

４.  總結

　通過以上的步驟，我們完整的安裝了一個Hive，並通過添加和刪除一張資料庫表的示範，來展示了Hive和Mysql中繼資料庫之間的關係操作。

５.  常見問題

　５.1 直接通過hive命令來啟動，則會出現一下錯誤資訊：　　　

Logging initialized using configuration in jar:file:/opt/apache-hive-0.13/lib/hive-common-0.13.1.jar!/hive-log4j.propertiesException in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClientat org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:346)at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:483)at org.apache.hadoop.util.RunJar.main(RunJar.java:160)Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClientat org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1412)at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:62)at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:72)at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:2453)at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:2465)at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:340)... 7 moreCaused by: java.lang.reflect.InvocationTargetExceptionat sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)at java.lang.reflect.Constructor.newInstance(Constructor.java:408)at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1410)... 12 moreCaused 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 refusedat org.apache.thrift.transport.TSocket.open(TSocket.java:185)at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:336)at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:214)at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)at java.lang.reflect.Constructor.newInstance(Constructor.java:408)at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1410)at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:62)at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:72)at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:2453)at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:2465)at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:340)at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:483)at org.apache.hadoop.util.RunJar.main(RunJar.java:160)Caused by: java.net.ConnectException: Connection refusedat java.net.PlainSocketImpl.socketConnect(Native Method)at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:345)at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)at java.net.Socket.connect(Socket.java:589)at org.apache.thrift.transport.TSocket.open(TSocket.java:180)... 19 more)at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:382)at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:214)... 17 more

　　解決辦法：　hive --service metastore , 用這條命令來啟動hive。

　　5.2 配置 hive.metastore.uris
        a.不啟動metastore和hiveserver服務
          直接命令列hive進入hive shell環境，然後執行show databases  報錯如下：
             ERROR ql.Driver: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: Unable to instantiate    org.apache.hadoop.hive.metastore.HiveMetaStoreClient
     　b.第二種種情況
          1.配置 hive.metastore.uris
          2.啟動metastore服務  hive  --service metastore
         然後直接命令列hive進入hive shell環境，然後執行show databases   

         c：第三種情況
           1.注釋配置項 hive.metastore.uris
           2.不啟動metastore服務
           然後直接命令列hive進入hive shell環境，然後執行show databases