This page shows how to operate Hive on MR3 in a non-secure Hadoop cluster using a MySQL database for Metastore. The same user, not necessarily an administrator of the Hadoop cluster, will run both Metastore and HiveServer2. By following the instruction, the user will learn:

  1. how to install and configure Hive on MR3 in a non-secure Hadoop cluster without Kerberos
  2. how to start and stop Metastore with a MySQL compatible database
  3. how to start and stop HiveServer2 with impersonation
  4. how to create Beeline connections and send queries to HiveServer2

This scenario has the following prerequisites:

  • Java 1.8 is available.
  • A non-secure Hadoop cluster 2.7 or higher is available.
  • The user has access to the home directory and /tmp directory on HDFS.
  • The administrator user can update core-site.xml and restart Yarn.
  • The user has access to the MySQL server with all privileges.

This scenario should take less than 30 minutes to complete, not including the time for downloading an MR3 release. This page has been tested with MR3 release 1.0 on Hadoop 2.7 (HDP 2.6.4) and Hadoop 3.1 (HDP 3.0.1).

Configuring Yarn

In order to support impersonation in HiveServer2, Yarn should be configured to allow the user starting Metastore and HiveServer2 to impersonate. In our example, user hive runs both Metastore and HiveServer, and the administrator user should add two configuration settings to core-site.xml and restart Yarn.

$ vi /etc/hadoop/conf/core-site.xml

<property>
  <name>hadoop.proxyuser.hive.groups</name>
  <value>*</value>
</property>

<property>
  <name>hadoop.proxyuser.hive.hosts</name>
  <value>*</value>
</property> 

hive in hadoop.proxyuser.hive.groups and hadoop.proxyuser.hive.hosts denotes the user starting Metastore and HiveServer2. Thus hadoop.proxyuser.hive.groups specifies the list of groups whose members can be impersonated by user hive, and hadoop.proxyuser.hive.hosts specifies the list of nodes where user hive can impersonate. For simplicity, we allow user hive to impersonate any user on any node.

Accessing MySQL

In our example, we assume that a MySQL user hivemr3 has access to a MySQL database with all privileges. Later we will configure Metastore so that it connects to the MySQL server using the user name hivemr3. The MySQL server may run on any node, not necessarily on the node where Metastore or HiveServer2 will be running.

$ mysql -u hivemr3 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 65588
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GRANTS FOR 'hivemr3'@'%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for hivemr3@%                                                                                            |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hivemr3'@'%' IDENTIFIED BY PASSWORD '*????????????????????????????????????????' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Installation

Login as user hive. Download a pre-built MR3 release and uncompress it. We choose the pre-built MR3 release based on Hive 3.1.2 which corresponds to --hivesrc3 option to be used later.

$ wget https://github.com/mr3project/mr3-release/releases/download/v1.0/hivemr3-1.0-hive3.1.2.tar.gz
$ gunzip -c hivemr3-1.0-hive3.1.2.tar.gz | tar xvf -;

Rename the new directory to mr3-run and change the working directory. Renaming the new directory is not strictly necessary, but it is recommended because the sample configuration file hive-site.xml included in the MR3 release uses the same directory name.

$ mv hivemr3-1.0-hive3.1.2/ mr3-run
$ cd mr3-run/

Configuring Hive on MR3

Open env.sh and set JAVA_HOME and PATH if necessary. Set HADOOP_HOME to the Hadoop installation directory.

$ vi env.sh

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64/jre
export PATH=$JAVA_HOME/bin:$PATH
export HADOOP_HOME=/usr/hdp/3.0.1.0-187/hadoop

Set HIVE_MYSQL_DRIVER to point to the MySQL connector jar file.

HIVE_MYSQL_DRIVER=/usr/share/java/mysql-connector-java-8.0.12.jar

Set the following environment variables to adjust the memory size (in MB) to be allocated to each component:

  • HIVE_METASTORE_HEAPSIZE specifies the memory size for Metastore.
  • HIVE_SERVER2_HEAPSIZE specifies the memory size for HiveServer2.
  • HIVE_CLIENT_HEAPSIZE specifies the memory size of HiveCLI (hive command) and Beeline (beeline command).
  • MR3_AM_HEAPSIZE specifies the memory size of MR3 DAGAppMaster.

In our example, we use the default values.

HIVE_METASTORE_HEAPSIZE=4096
HIVE_SERVER2_HEAPSIZE=16384
HIVE_CLIENT_HEAPSIZE=1024
MR3_AM_HEAPSIZE=10240

HIVE3_DATABASE_HOST specifies the node where the MySQL server runs, and HIVE3_METASTORE_HOST specifies the node where Metastore is to run. In our example, the MySQL server runs on a separate node.

HIVE3_DATABASE_HOST=indigo2
HIVE3_METASTORE_HOST=indigo1

HIVE3_DATABASE_NAME specifies the database name to be created for Metastore inside MySQL. In our example, we use hive3mr3test.

HIVE3_DATABASE_NAME=hive3mr3test

HIVE3_HDFS_WAREHOUSE specifies the warehouse directory on HDFS. Update it to use the current user name.

HIVE3_HDFS_WAREHOUSE=/user/hive/warehouse

Open conf/tpcds/hive3/hive-site.xml and set the following four configuration keys according to the current user name and the working directory.

$ vi conf/tpcds/hive3/hive-site.xml 

<property>
  <name>hive.users.in.admin.role</name>
  <value>hive</value>
</property>

<property>
  <name>hive.aux.jars.path</name>
  <value>/home/hive/mr3-run/hive/hivejar/apache-hive-3.1.2-bin/lib/hive-llap-common-3.1.2.jar,/home/hive/mr3-run/hive/hivejar/apache-hive-3.1.2-bin/lib/hive-llap-server-3.1.2.jar,/home/hive/mr3-run/hive/hivejar/apache-hive-3.1.2-bin/lib/hive-llap-tez-3.1.2.jar</value>
</property>

<property>
  <name>hive.exec.scratchdir</name>
  <value>/tmp/hive</value>
</property>

<property>
  <name>hive.server2.logging.operation.log.location</name>
  <value>/tmp/hive/operation_logs</value>
</property>

The following configuration keys specify the resource to be allocated to a Map Task, a Reduce Task, or a ContainerWorker. By default, we allocate 4GB and a single core to a Map Task, a Reduce Task, and a ContainerWorker.

<property>
  <name>hive.mr3.map.task.memory.mb</name>
  <value>4096</value>
</property>

<property>
  <name>hive.mr3.map.task.vcores</name>
  <value>1</value>
</property>

<property>
  <name>hive.mr3.reduce.task.memory.mb</name>
  <value>4096</value>
</property>

<property>
  <name>hive.mr3.reduce.task.vcores</name>
  <value>1</value>
</property>

<property>
  <name>hive.mr3.all-in-one.containergroup.memory.mb</name>
  <value>4096</value>
</property>

<property>
  <name>hive.mr3.all-in-one.containergroup.vcores</name>
  <value>1</value>
</property>

When updating these configuration keys, we should meet the following requirements:

  • hive.mr3.map.task.memory.mb <= hive.mr3.all-in-one.containergroup.memory.mb
  • hive.mr3.map.task.vcores <= hive.mr3.all-in-one.containergroup.vcores
  • hive.mr3.reduce.task.memory.mb <= hive.mr3.all-in-one.containergroup.memory.mb
  • hive.mr3.reduce.task.vcores <= hive.mr3.all-in-one.containergroup.vcores

We enable impersonation by setting hive.server2.enable.doAs to true.

<property>
  <name>hive.server2.enable.doAs</name>
  <value>true</value>
</property>

Set javax.jdo.option.ConnectionUserName to the MySQL user name and javax.jdo.option.ConnectionPassword to the password.

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

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

Creating directories on HDFS

Create the warehouse directory specified in env.sh.

$ grep HIVE3_HDFS_WAREHOUSE env.sh
HIVE3_HDFS_WAREHOUSE=/user/hive/warehouse
$ hdfs dfs -mkdir -p /user/hive/warehouse

Create a directory for storing MR3 and Tez jar files.

$ hdfs dfs -mkdir /user/hive/lib

Load MR3 jar files.

$ mr3/upload-hdfslib-mr3.sh

Load Tez jar files.

$ tez/upload-hdfslib-tez.sh 

Check if /tmp/hive exists on HDFS. If the directory already exists, make sure that its permission is set to 733. HiveServer2 automatically creates a new directory with permission 733 if it does not exist.

$ hdfs dfs -ls /tmp/ | grep hive
drwx-wx-wx   - hive          hdfs          0 2019-10-20 13:52 /tmp/hive

Make sure that /tmp/hive/hive does not exist on HDFS.

$ hdfs dfs -ls /tmp/hive/hive
ls: `/tmp/hive/hive': No such file or directory

Creating temporary directories

Create a new directory specified by hive.server2.logging.operation.log.location.

$ ls -alt /tmp/hive/operation_logs
ls: cannot access /tmp/hive/operation_logs: No such file or directory
$ mkdir -p /tmp/hive/operation_logs

Running Metastore

Metastore uses the port specified by the environment variable HIVE3_METASTORE_PORT in env.sh. Make sure that the port is not in use.

$ grep HIVE3_METASTORE_PORT env.sh
HIVE3_METASTORE_PORT=9830
$ ss -anpt | grep -E "LISTEN.*:9830"
$ 

Run Metastore with a MySQL database using --tpcds option, and initialize it using --init-schema option.

$ hive/metastore-service.sh start --tpcds --hivesrc3 --init-schema

After a while, check if Metastore has successfully started.

$ cat /home/hive/mr3-run/hive/metastore-service-result/hive-mr3--2020-02-03--03-32-33-2969391c/out-metastore.txt | grep -e Metastore -e Init
Metastore connection URL:  jdbc:mysql://indigo2/hive3mr3test?createDatabaseIfNotExist=true
Metastore Connection Driver :  com.mysql.jdbc.Driver
Metastore connection User:   hivemr3
Initialization script hive-schema-3.1.0.mysql.sql
Initialization script completed
2020-02-03 03:32:41: Starting Hive Metastore Server

When the user restarts Metastore, do not use --init-schema option in order to reuse existing Hive databases.

The log file for Metastore is found under /tmp/hive.

$ ls /tmp/hive/hive.log
/tmp/hive/hive.log

Running HiveServer2

Run HiveServer2 using --tpcds option. In order to use LocalProcess mode for MR3 DAGAppMaster, use --amprocess option. In our example, we use --amprocess option.

$ hive/hiveserver2-service.sh start --tpcds --hivesrc3 --amprocess

After a while, check if HiveServer2 has successfully started by inspecting its log file.

$ grep -e "New MR3Session created" /home/hive/mr3-run/hive/hiveserver2-service-result/hive-mr3--2020-02-03--03-40-00-06c97e62/hive-logs/hive.log 
2020-02-03T03:40:23,930  INFO [main] session.MR3SessionManagerImpl: New MR3Session created: 0134c5de-c441-4268-a467-fbf7dda4884b, hive

The user can find a new Yarn application of type mr3 submitted by user hive.

$ yarn application -list
20/02/03 03:41:26 INFO client.RMProxy: Connecting to ResourceManager at indigo1/10.1.91.18:8050
20/02/03 03:41:26 INFO client.AHSProxy: Connecting to Application History server at indigo2/10.1.91.19:10200
Total number of applications (application-types: [], states: [SUBMITTED, ACCEPTED, RUNNING] and tags: []):1
                Application-Id      Application-Name      Application-Type        User       Queue               State         Final-State         Progress                        Tracking-URL
application_1552232727432_0248  0134c5de-c441-4268-a467-fbf7dda4884b                   mr3        hive     default             RUNNING           UNDEFINED               0%                                 N/A

The user can also find the process for MR3 DAGAppMaster.

$ ps -ef | grep DAGAppMaster | grep mr3
hive      6128  5610 12 03:40 pts/9    00:00:11 /usr/jdk64/jdk1.8.0_112/jre/bin/java -server -Djava.net.preferIPv4Stack=true -Dhadoop.metrics.log.level=WARN -XX:+PrintGCDetails -verbose:gc -XX:+PrintGCTimeStamps -XX:+UseNUMA -XX:+UseG1GC -XX:+ResizeTLAB -Xmx8192m -Dlog4j.configurationFile=mr3-container-log4j2.properties -Dlog4j.configuratorClass=com.datamonad.mr3.MR3Log4jConfigurator -Dmr3.root.logger=INFO -Dyarn.app.container.log.dir=/home/hive/mr3-run/hive/hiveserver2-service-result/hive-mr3--2020-02-03--03-40-00-06c97e62/application_1552232727432_0248 -Dsun.nio.ch.bugLevel='' com.datamonad.mr3.master.DAGAppMaster --session

The user can find the log file for MR3 DAGAppMaster.

$ ls /home/hive/mr3-run/hive/hiveserver2-service-result/hive-mr3--2020-02-03--03-40-00-06c97e62/application_1552232727432_0248/run.log 
/home/hive/mr3-run/hive/hiveserver2-service-result/hive-mr3--2020-02-03--03-40-00-06c97e62/application_1552232727432_0248/run.log 

Running queries

Download a sample dataset.

$ wget https://github.com/mr3project/mr3-release/releases/download/v1.0/pokemon.csv

Run Beeline using --tpcds option.

$ hive/run-beeline.sh --tpcds --hivesrc3

Use the default database.

0: jdbc:hive2://indigo1:9832/> use default;

Create a table called pokemon.

0: jdbc:hive2://indigo1:9832/> CREATE TABLE pokemon (Number Int,Name String,Type1 String,Type2 String,Total Int,HP Int,Attack Int,Defense Int,Sp_Atk Int,Sp_Def Int,Speed Int) row format delimited fields terminated BY ',' lines terminated BY '\n' tblproperties("skip.header.line.count"="1");

Import the sample dataset.

0: jdbc:hive2://indigo1:9832/> load data local inpath './pokemon.csv' INTO table pokemon;

Execute queries.

0: jdbc:hive2://indigo1:9832/> select avg(HP) from pokemon;
...

0: jdbc:hive2://indigo1:9832/> create table pokemon1 as select *, IF(HP>160.0,'strong',IF(HP>140.0,'moderate','weak')) AS power_rate from pokemon;
...

0: jdbc:hive2://indigo1:9832/> select COUNT(name), power_rate from pokemon1 group by power_rate;
...

Exit Beeline. The warehouse directory on HDFS has now two sub-directories.

$ hdfs dfs -ls /user/hive/warehouse/
Found 2 items
drwxr-xr-x   - hive hdfs          0 2020-02-03 03:44 /user/hive/warehouse/pokemon
drwxr-xr-x   - hive hdfs          0 2020-02-03 03:45 /user/hive/warehouse/pokemon1

Change the permission on hive/run-beeline-result to 777 so that any user can run Beeline on /home/hive/mr3-run.

$ chmod 755 /home/hive/
$ chmod 777 hive/run-beeline-result

Running queries by a different user

Login as another user. In our example, we login as user gla.

Change to /home/hive/mr3-run and run Beeline.

$ cd /home/hive/mr3-run
$ hive/run-beeline.sh --tpcds --hivesrc3

Use the default database.

0: jdbc:hive2://indigo1:9832/> use default;

Execute queries.

0: jdbc:hive2://indigo1:9832/> select avg(HP) from pokemon;
...

0: jdbc:hive2://indigo1:9832/> select COUNT(name), power_rate from pokemon1 group by power_rate;
...

HiveServer2 creates a scratch directory /tmp/hive/gla on HDFS for user gla

$ hdfs dfs -ls /tmp/hive/ | grep gla
drwx-wx-wx   - gla           hdfs          0 2020-02-03 04:44 /tmp/hive/gla

User gla, however, cannot create a new table. This is because we enable impersonation in HiveServer2 while user gla does not have write permission on the warehouse directory on HDFS.

0: jdbc:hive2://indigo1:9832/> create table pokemon_new as select *, IF(HP>160.0,'strong',IF(HP>140.0,'moderate','weak')) AS power_rate from pokemon;
Error: Error while compiling statement: FAILED: SemanticException 0:0 Error creating temporary folder on: hdfs://indigo1:8020/user/hive/warehouse. Error encountered near token 'TOK_TMP_FILE' (state=42000,code=40000)

Grant write permission on the warehouse directory to everyone.

$ hdfs dfs -chmod 777 /user/hive/warehouse

Now user gla can create a new table.

0: jdbc:hive2://indigo1:9832/> create table pokemon_new as select *, IF(HP>160.0,'strong',IF(HP>140.0,'moderate','weak')) AS power_rate from pokemon;
...

0: jdbc:hive2://indigo1:9832/> select COUNT(name), power_rate from pokemon_new group by power_rate; 
...

The warehouse directory contains a new sub-directory creatd by user gla.

$ hdfs dfs -ls /user/hive/warehouse
Found 4 items
drwxr-xr-x   - hive hdfs          0 2020-02-03 04:43 /user/hive/warehouse/pokemon
drwxr-xr-x   - hive hdfs          0 2020-02-03 04:43 /user/hive/warehouse/pokemon1
drwxr-xr-x   - gla  hdfs          0 2020-02-03 05:16 /user/hive/warehouse/pokemon_new

Finally we revert the permission on the warehouse directory.

$ hdfs dfs -chmod 755 /user/hive/warehouse

Stopping HiveServer2 and Metastore

Stop HiveServer2.

$ hive/hiveserver2-service.sh stop --tpcds --hivesrc3

Stop Metastore.

$ hive/metastore-service.sh stop --tpcds --hivesrc3

The user can check if Metastore has successfully stopped by reading its log file.

$ tail -n3 /tmp/hive/hive.log
/************************************************************
SHUTDOWN_MSG: Shutting down HiveMetaStore at indigo1/10.1.91.18
************************************************************/