Hive Installation in Ubuntu

Installing Hive from Cloudera is very simple and it needs to follow below simple steps :-

1. sudo apt-get install hive hive-metastore hive-server2 hive-hbase
2. sudo apt-get install hive-jdbc
3. Add /usr/lib/hive/lib/*.jar and /usr/lib/hadoop/*.jar to your classpath.

$sudo gedit .bashrc
export HIVE_HOME=/usr/lib/hive
export PATH=$PATH:$HIVE_HOME/bin
export CLASSPATH=$CLASSPATH:/usr/lib/Hadoop/lib/*:.
export CLASSPATH=$CLASSPATH:/usr/lib/hive/lib/*:.

$ cd $HIVE_HOME/conf
$ sudo cp hive-env.sh.template hive-env.sh

$sudo gedit hive-env.sh
export HADOOP_HOME=/usr/lib/hadoop

Well that will do enough to install hive but you need to do bit more configuration for metastore.

Step 1 :- You first need to install MySql

$ sudo apt-get install mysql-server
$ sudo service mysql start
$ sudo apt-get install libmysql-java
$ sudo ln -s /usr/share/java/libmysql-java.jar /usr/lib/hive/lib/libmysql-java.jar [to be done after installing hive]
$ sudo /usr/bin/mysql_secure_installation
$ sudo apt-get install sysv-rc-conf

Step 2 :- Create metastore database in mysql and user

$ sudo sysv-rc-conf mysql on
$ mysql -u root -p
Enter password:
mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> SOURCE /usr/lib/hive/scripts/metastore/upgrade/mysql/hive-schema-0.12.0.mysql.sql;

mysql> CREATE USER 'hive'@'localhost' IDENTIFIED BY 'mypassword';
...
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hive'@'localhost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,EXECUTE ON metastore.* TO 'hive'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit;

Step 3 :- Configure Hive Site xml file to make Hive use the metastore

sudo gedit /usr/lib/hive/conf/hive-site.xml

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost/metastore</value>
  <description>the URL of the MySQL database</description>
</property>

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hive</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>mypassword</value>
</property>

<property>
  <name>datanucleus.autoCreateSchema</name>
  <value>false</value>
</property>

<property>
  <name>datanucleus.fixedDatastore</name>
  <value>true</value>
</property>

<property>
  <name>datanucleus.autoStartMechanism</name>
  <value>SchemaTable</value>
</property>

<property>
  <name>hive.metastore.uris</name>
  <value>thrift://localhost:9083</value>
  <description>IP address (or fully-qualified domain name) and port of the metastore host</description>
</property>


<property>
  <name>hive.support.concurrency</name>
  <description>Enable Hive's Table Lock Manager Service</description>
  <value>true</value>
</property>

<property>
  <name>hive.zookeeper.quorum</name>
  <description>Zookeeper quorum used by Hive's Table Lock Manager</description>
  <value>localhost</value>
</property>



<property>
  <name>hive.zookeeper.client.port</name>
  <value>2181</value>
  <description>
  The port at which the clients will connect.
  </description>
</property>

Step 4 :- Create the below directory for hive to access


sudo -u hdfs hadoop fs -mkdir -p /user/hive/warehouse
sudo -u hdfs hadoop fs -chmod g+w /user/hive/warehouse

Step 5 :- Trouble Shooting Hive

Though the above steps will be enough to run hive successfully but in case it is not running you need to check the log files in /var/log/hive directory
I have faced two problem even after successfully installing it.

1. BOPTM connection failure to use it's metastore.[https://hadooptutorial.info/datastore-driver-was-not-found/]

For this you need to download latest version of mysql connector and install it in the below specified way.

$ cd Downloads/
$ tar -xzf mysql-connector-java-5.1.35.tar.gz
$ cd mysql-connector-java-5.1.35/
$ sudo cp mysql-connector-java-5.1.35-bin.jar $HIVE_HOME/lib/

2. Unknown column 'OWNER_NAME' in 'field list' [https://community.cloudera.com/t5/Interactive-Short-cycle-SQL/CDH-upgrade-from-4-7-to-CDH-5-2-hive-metastore-issue/td-p/20626]

This has happened for the previous step where we have run  SOURCE /usr/lib/hive/scripts/metastore/upgrade/mysql/hive-schema-0.12.0.mysql.sql;. This will only valid for hive version 0.8 but as our new hive version is 1.1.0 so we have to run the code

SOURCE /usr/lib/hive/scripts/metastore/upgrade/mysql/hive-schema-1.1.0.mysql.sql;

but this file will have a reference to txn-0.13.0 schema sql file but the entire path is not mentioned on the schema sql file hence make sure you modify the file hive-schema-1.1.0.mysql.sql and give the full path as /usr/lib/hive/scripts/metastore/upgrade/mysql/txn-0.13.0.mysql.sql