Install/Configure Hive 3.1.1 Using MySQL Metastore

This task is verified on top of Hadoop 3.1.1. Work as user “hadoop” if not specified.

Download Hive 3.1.1

$ pwd
/localuser/hadoop

$ wget http://mirror.dsrg.utoronto.ca/apache/hive/hive-3.1.1/apache-hive-3.1.1-bin.tar.gz

$ tar zxvf apache-hive-3.1.1-bin.tar.gz

$ ls
apache-hive-3.1.1-bin  apache-hive-3.1.1-bin.tar.gz  hadoop  hadoop-3.1.1.tar.gz  hadoopdata  perl5

$ mv apache-hive-3.1.1-bin hive

$ ls
apache-hive-3.1.1-bin.tar.gz  hadoop  hadoop-3.1.1.tar.gz  hadoopdata  hive  perl5

Set environment variables

Set environment variables for user “hadoop”. With all the Hadoop settings user hadoop’s “.bashrc” file should look like following:

$ cat .bashrc
# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
    . /etc/bashrc
fi

# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=

# User specific aliases and functions
export HADOOP_HOME=/localuser/hadoop/hadoop
export HADOOP_INSTALL=$HADOOP_HOME
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native
export PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin

# hive
export HIVE_HOME=/localuser/hadoop/hive
export PATH=$HIVE_HOME/bin:$PATH

[hadoop@li-9 ~]$
[hadoop@li-9 ~]$ source .bashrc

Create directories on HDFS for Hive

$ hadoop fs -mkdir /tmp
$ hadoop fs -mkdir /user
$ hadoop fs -mkdir /user/hadoop
$ hadoop fs -mkdir /user/hive
# warehouse is where Hive stores actual data if you create a table and insert data later on.
$ hadoop fs -mkdir /user/hive/warehouse
$ hadoop fs -chmod g+w /tmp
$ hadoop fs -chmod g+w /user/hive/warehouse

Note, following jar file is provided by both Hadoop and Hive. We’ll remove it from Hive to avoid errors.

$ rm /localuser/hadoop/hive/lib/log4j-slf4j-impl.jar

Configure hive-site.xml with metastore information to be used

$ pwd
/localuser/hadoop/hive/conf
$ cat hive-site.xml
<configuration>

 <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://<MySQL Server>: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>hadoop</value>
    <description>username to use against metastore database</description>
 </property>

 <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>hadoop123</value>
    <description>password to use against metastore database</description>
 </property>

 <property>
    <name>hive.metastore.uris</name>
    <value>thrift://localhost:9083</value>
 </property>

</configuration>

Configure MySQL database for hive-site.xml

On MySQL database server as user root, create “hive” database and user “hadoop” based on information in hive-site.xml:

# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

mysql> create database hive;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'hadoop'@'%' identified by 'hadoop123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on hive.* to 'hadoop'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

Back on Hive machine, Verify above changes:

$ mysql -h <MySQL server> -u hadoop -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MySQL [(none)]> use hive;
Database changed
MySQL [hive]>

Put MySQL JDBC jar file in place for Hive

$ pwd
/localuser/hadoop/hive/lib
$ cp /scratch/support/li/pac-package/mysql-connector-java-5.1.22-bin.jar .

Initialize metastore schemas

$ schematool -dbType mysql -initSchema
Metastore connection URL:     jdbc:mysql://li-4:3306/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :     com.mysql.jdbc.Driver
Metastore connection User:     hadoop
Starting metastore schema initialization to 3.1.0
Initialization script hive-schema-3.1.0.mysql.sql
…
Initialization script completed
schemaTool completed

$ mysql -h li-4 -u hadoop -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
...

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hive               |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MySQL [(none)]> use hive
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [hive]> show tables;
+-------------------------------+
| Tables_in_hive                |
+-------------------------------+
| AUX_TABLE                     |
| BUCKETING_COLS                |
…...
| WM_TRIGGER                    |
| WRITE_SET                     |
+-------------------------------+
74 rows in set (0.00 sec)

Launch metastore service

Modify/create hive-env.sh in /localuser/hadoop/hive/conf/hive-env.sh to add

        export METASTORE_PORT=9083

This port should be consistent to the thrift URI parameter in hive-site.xml:

<property>
    <name>hive.metastore.uris</name>
    <value>thrift://localhost:9083</value>
 </property>

Now start metastore service

$ hive --service metastore
Start Metastore Service ...

Launch Hive CLI and Do Tests

$ hive
...
hive> show tables;
OK
Time taken: 1.929 seconds
hive>
hive> create table mytable
    > (
    > no int,
    > name string,
    ...
    > )
    > row format delimited
    > fields terminated by ','
    > ;
OK
Time taken: 15.778 seconds
hive> show tables;
OK
mytable
Time taken: 0.085 seconds, Fetched: 1 row(s)
hive>
hive> load data local inpath '/localuser/hadoop/testhive/mytabledata.txt' overwrite into table mytable;
Loading data to table default.mytable
OK
Time taken: 2.205 seconds
hive> select * from mytable;
OK
...

Hive stores the actual data in HDFS Hive warehouse

$ hadoop fs -ls /user/hive/warehouse
Found 1 items
drwxr-xr-x   - hadoop supergroup          0 2019-01-11 12:50 /user/hive/warehouse/mytable
$ hadoop fs -ls /user/hive/warehouse/employee
Found 1 items
-rw-r--r--   1 hadoop supergroup        156 2019-01-11 12:50 /user/hive/warehouse/mytable/mytabledata.txt

Written on January 13, 2019