Hive Installation step- by-step with MySQL Metastore

1) Download hive "apache-hive-0.13.0-bin.tar.gz"

2) Gunzip and Untar at path /opt/ds/app/hive-0.13.0

3) Edit ~/.bashrc  and add below lines:-

#HIVE
export HIVE_HOME=/opt/ds/app/hive-0.13.0

export PATH=$PATH:$HIVE_HOME/bin

4) Change directory to /opt/ds/app/hive-0.13.0/conf

5) Create hive-log4j.properties from template

6) Create hive-env.sh from template. Also,set 

#
 if [ "$SERVICE" = "cli" ]; then
   if [ -z "$DEBUG" ]; then
     export HADOOP_OPTS="$HADOOP_OPTS -XX:NewRatio=12 -Xms10m -XX:MaxHeapFreeRatio=40 -XX:MinHeapFreeRatio=15 -XX:+UseParNewGC -XX:-UseGCOverheadLimit"
   else
     export HADOOP_OPTS="$HADOOP_OPTS -XX:NewRatio=12 -Xms10m -XX:MaxHeapFreeRatio=40 -XX:MinHeapFreeRatio=15 -XX:-UseGCOverheadLimit"
   fi
 fi

# The heap size of the jvm stared by hive shell script can be controlled via:
#
export HADOOP_HEAPSIZE="1024"

export HADOOP_CLIENT_OPTS="-Xmx${HADOOP_HEAPSIZE}m $HADOOP_CLIENT_OPTS"

# Set HADOOP_HOME to point to a specific hadoop install directory
HADOOP_HOME=/opt/ds/app/hadoop-2.2.0

# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/opt/ds/app/hive-0.13.0/conf


7) Create directory on HDFS 

hadoop fs -mkdir /usr/hive/warehouse

hadoop fs -mkdir /temp

hadoop fs -chmod g+w /usr/hive/warehouse

hadoop fs -chmod g+w /temp

8)  On MySQL part do this

$ 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.10.0.mysql.sql;

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

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

mysql> FLUSH PRIVILEGES;

mysql> quit;

Note that create user for each host from where you are going to access the database.

9) Create hive-site.xml from template and add mysql configuration as follows:

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

<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/usr/hive/warehouse</value>
    <description>location of default database for the warehouse</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://<n.n.n.n>:9083</value>
  <description>IP address (or fully-qualified domain name) and port of the metastore host</description>

</property>


10) start hive service 
 hive --service metastore &

11)  execute command 
hive

12) execute below command to create table in Hive.
create table hivetesting(id string);

13) connect to mysql
mysql -h hive -p metastore

mysql> select * from TBLS;  // This command will show the hive tables.
your hivetesting table will be listed here.