Hive Installation With MySQL Database Metastore





Hive Installation With MySQL Database Metastore




NOTE

Hive versions 1.2 onward require Java 1.7 or newer. Hive versions 0.14 to 1.1 work with Java 1.6 as well.

Hadoop 2.x (preferred), 1.x (not supported by Hive 2.0.0 onward). Hive versions up to 0.13 also supported Hadoop 0.20.x, 0.23.x.

Hive Installation Steps

Step 1 - Installing MySQL Server. Open a terminal (CTRL + ALT + T) and type the following sudo command.

$ sudo apt-get install mysql-server

During mysql-server installation it will ask password to enter for root, give it as 'root' or something else. In my case i have given it as root to remember easily.

Step 2 - Installing MySQL Java Connector. This will install libraries (mysql-connector-java.jar) in /usr/share/java/ folder using that we can connect MySQL using Java.

$ sudo apt-get install libmysql-java

Step 3 - Enter into MySQL command line inteface(CLI). Open a terminal (CTRL + ALT + T) and type the following command.

$ mysql -u root -p

Enter password

Enter password :****

Step 4 - Creating new user

mysql> CREATE USER 'hduser'@'%' IDENTIFIED BY 'hduser';

Step 5 - Grant all privileges to new user

mysql> GRANT all on *.* to 'hduser'@localhost identified by 'hduser';

Step 6 - Flush privileges

mysql> flush privileges;

Step 7 - Creating hive directory. Open a new terminal(CTRL + ALT + T) and enter the following command.

$ sudo mkdir /usr/local/hive

Step 8 - Change the ownership and permissions of the directory /usr/local/hive. Here 'hduser' is an Ubuntu username.

$ sudo chown -R hduser /usr/local/hive
$ sudo chmod -R 755 /usr/local/hive


Step 9 - Switch User, is used by a computer user to execute commands with the privileges of another user account.

$ su hduser

Step 10 - Change the directory to /home/hduser/Desktop , In my case the downloaded apache-hive-2.1.0-bin.tar.gz file is in /home/hduser/Desktop folder. For you it might be in /downloads folder check it.

$ cd /home/hduser/Desktop/


Step 11 - Untar the apache-hive-2.1.0-bin.tar.gz file.

$ tar xzf apache-hive-2.1.0-bin.tar.gz


Step 12 - Move the contents of apache-hive-2.1.0-bin folder to /usr/local/hive

$ mv apache-hive-2.1.0-bin/* /usr/local/hive


Step 13 - Edit $HOME/.bashrc file by adding the pig path.

$ sudo gedit $HOME/.bashrc

$HOME/.bashrc file. Add the following lines

export HIVE_HOME=/usr/local/hive
export PATH=$HIVE_HOME/bin:$HIVE_HOME/lib:$PATH


Step 14 - Reload your changed $HOME/.bashrc settings

$ source $HOME/.bashrc

Step 15 - Change the directory to /usr/local/hive/conf

$ cd $HIVE_HOME/conf


Step 16 - Copy the default hive-env.sh.template to hive-env.sh

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


Step 17 - Edit hive-env.sh file.

$ gedit hive-env.sh


Step 18 - Add the below lines to hive-env.sh file. Save and Close.

export HADOOP_HOME=/usr/local/hadoop
export HIVE_CONF_DIR=$HIVE_CONF_DIR
export HIVE_AUX_JARS_PATH=$HIVE_AUX_JARS_PATH


Step 19 - Copy the default hive-default.xml.template to hive-site.xml

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


Step 20 - Edit hive-site.xml file.

$ gedit hive-site.xml


Step 21 - Add or update below properties in hive-site.xml file.

       
javax.jdo.option.ConnectionURL
jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true
metadata is stored in a MySQL server



javax.jdo.option.ConnectionDriverName
com.mysql.jdbc.Driver
MySQL JDBC driver class



javax.jdo.option.ConnectionUserName
hduser
user name for connecting to mysql server



javax.jdo.option.ConnectionPassword
hduser
password for connecting to mysql server



hive.metastore.warehouse.dir
hdfs://localhost:9000/user/hive/warehouse
location of default database for the warehouse 



Step 22 - Remove below property in hive-site.xml file. Save and Close.

    javax.jdo.option.ConnectionUserName
    APP
    Username to use against metastore database

  


Step 23 - Copy mysql-connector-java-5.1.28.jar from /usr/share/java/ to $HIVE_HOME/lib/ folder.

$ cp /usr/share/java/mysql-connector-java-5.1.28.jar $HIVE_HOME/lib/


Step 24 - Change the directory to /usr/local/hadoop/sbin

$ cd /usr/local/hadoop/sbin


Step 25 - Start all hadoop daemons.

$ start-all.sh


Step 26 - You must use below HDFS commands to create /tmp and /user/hive/warehouse (aka hive.metastore.warehouse.dir) and set them chmod g+w before you can create a table in Hive.


$ hdfs dfs -mkdir /tmp

$ hdfs dfs -chmod 777 /tmp 

$ hdfs dfs -mkdir /user/hive/warehouse

$ hdfs dfs -chmod g+w /tmp

$ hdfs dfs -chmod g+w /user/hive/warehouse


Step 27 - Change the directory to /usr/local/hive/bin

$ cd $HIVE_HOME/bin


Step 28 - We need to run the schematool command below as an initialization step. For example, we can use "mysql" as db type.

$ schematool -initSchema -dbType mysql


Step 29 - To use the Hive command line interface (CLI) from the shell.

$ hive


Step 30 - To list all the tables those are present in mysql database.

$ show tables;


Step 31 - Enter into MySQL command line inteface(CLI). Open a terminal (CTRL + ALT + T) and type the following command.

$ mysql -u hduser -p 


Enter password

Enter password: hduser


Step 32 - Use matastore database.

mysql> use metastore;


Step 33 - To list all the tables those are present in mysql metastore database.

select * from TBLS;

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *