Apache Hive 2.3.0 installation

with mysql as metastore

• Download Apache hive 2.3.0 tar ball .

         wget http://apache.org/dist/hive/hive-2.3.0/apache-hive-2.3.0-bin.tar.gz

• Untar and move to /opt/

    tar -xzf apache-hive-2.3.0-bin.tar.gz

    sudo mv apache-hive-2.3.0-bin /opt/hive-2.3.0

    sudo chown -R hadoop:hadoop /opt/hive-2.3.0

• Update .bashrc file with hive path

    sudo vi ~/.bashrc

        export HIVE_HOME=/opt/hive-2.3.0

export PATH=$PATH:$HIVE_HOME/bin

    source ~/.bashrc

    exec bash

• Create tmp folder in the hive directory for hive session_id and for few configuration related files.

    sudo mkdir /opt/hive-2.3.0/tmp

sudo chown -R hadoop:hadoop /opt/hive-2.3.0/tmp

• Create hive warehouse directory on hdfs

    hdfs dfs -mkdir -p /opt/hive/warehouse

    hdfs dfs -chmod g+w /opt/hive/warehouse

• Download and install Mysql and also mysql jdbc connector.

sudo apt-get install mysql-server libmysql-java

• Copy the mysql-connector-java.jar to /opt/hive-2.3.0/lib/

sudo cp /usr/share/java/mysql-connector-java.jar  /opt/hive-2.3.0/lib/mysql-connector-java.jar

sudo chown -R hadoop:hadoop /opt/hive-2.3.0/lib/

• Create user in mysql for hive

    mysql -u root -p

        create user ‘hive’@’%’identified by ‘hive’;

    grant all on *.* to ‘hive’@’<hostname>’ identified by ‘hive’;

        flush privileges;

• Create database in mysql

    create database metastore;

• Update hive configuration files in /opt/hive-2.3.0/conf/ directory

    cd /opt/hive-2.3.0/conf/

    Take a backup of the below two files

    cp hive-default.xml.template hive-site.xml

    cp hive-env.sh.template hive-env.sh

    Update with the below content in the above two created files

    hive-env.sh ::

    export HADOOP_HOME=/opt/hadoop-2.7.3

    export HIVE_CONF_DIR=/opt/hive-2.3.0/conf

    hive-site.xml ::

    <property>

    <name>hive.exec.scratchdir</name>

    <value>/opt/hive-2.3.0/tmp</value>

    <description>HDFS root scratch dir for Hive jobs which gets created with

write all (733) permission. For each connecting user, an HDFS

scratch dir:  ${hive.exec.scratchdir}/&lt;username&gt; is created,

with ${hive.scratch.dir.permission}.

</description>

     </property>

   

    <property>

    <name>hive.exec.local.scratchdir</name>

    <value>/opt/hive-2.3.0/tmp</value>

    <description>Local scratch space for Hive jobs</description>

 </property>

<property>

    <name>hive.downloaded.resources.dir</name>

    <value>/opt/hive-2.3.0/tmp/${hive.session.id}_resources</value>

    <description>Temporary local directory for added resources in the remote

File  system.

</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.ConnectionURL</name>

<value>jdbc:mysql://hadoop-master-prd-demolab/metastore?createDat

abaseIfNotExist=true</value>

    <description> JDBC connect string for a JDBC metastore.To use SSL to

encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.

    </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>hive</value>

    <description>password to use against metastore database</description>

</property>

• Initialize the schema using schematool

    schematool -initSchema -dbType mysql

• After successfully, initialized the schema. Now start hive  
• Make sure, all hadoop daemons are running.
• hive
  SLF4J: Class path contains multiple SLF4J bindings.
  SLF4J: Found binding in [jar:file:/opt/hive-2.3.0/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  SLF4J: Found binding in [jar:file:/opt/hadoop-2.7.3/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  
  Logging initialized using configuration in jar:file:/opt/hive-2.3.0/lib/hive-common-2.3.0.jar!/hive-log4j2.properties Async: true
  Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
  hive> show databases;
  OK
  default
  Time taken: 6.415 seconds, Fetched: 1 row(s)
  hive> create database hive_db1;
  OK
  Time taken: 0.216 seconds
  hive> show databases;
  OK
  default
  hive_db1
  Time taken: 0.031 seconds, Fetched: 2 row(s)
  hive>