Configuring MariaDB for the Hive Metastore

Installing MariaDB

To install MariaDB, use the MariaDB Repository Configuration Tool. See MariaDB Downloads.

Configuring Repositories

The following steps describe how to configure a repository and install the latest available stable version of MariaDB for different operating systems.

Configure a repository for MariaDB:
- Red Hat / CentOS and SLES
  Copy and paste the following custom MariaDB repository entry into a file under /etc/yum.repos.d/. You can name the file MariaDB.repo or something similar:
```
# MariaDB 10.4 RedHat repository list
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/rhel7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
```
- Ubuntu
  You can also create a custom MariaDB sources.list file. To do so, after importing the signing key as outlined above, copy and paste the following into a file under /etc/apt/sources.list.d/. You can name the file MariaDB.list or something similar. Or you can add it to the bottom of your /etc/apt/sources.list file:
```
# MariaDB 10.4 repository list - created 2020-04-17 08:34 UTC
# http://downloads.mariadb.org/mariadb/repositories/
deb [arch=amd64,arm64,ppc64el] http://mirror.mephi.ru/mariadb/repo/10.4/ubuntu bionic main
deb-src http://mirror.mephi.ru/mariadb/repo/10.4/ubuntu bionic main
```

After the sources.list file is in place, install MariaDB:

Red Hat / CentOS

sudo yum clean all && sudo yum install MariaDB-server MariaDB-client

SLES

sudo zypper update && sudo zypper install mariadb

Ubuntu

sudo apt update && sudo apt install mariadb-server

Start the MariaDB server:
- Red Hat / CentOS and Ubuntu
```
sudo service mariadb start
```
- SLES
```
sudo systemctl start mariadb
```

In the command line, run the mysql_secure_installation shell script:

sudo mysql_secure_installation
Enter current password for root (enter for none): press Enter
Set root password? Y
New password: Type new root password
Re-enter new password: Confirm the password
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

Configuring a JDBC Driver for MariaDB

Before you can run the Hive Metastore with a MariaDB database, you must:

Configure a JDBC driver for the MariaDB database.
Set up the initial database schema.
Configure the MariaDB user account for the Hive user.

Use the following steps:

Install the MariaDB Connector/J manually with a .jar file. The MariaDB Connector/J can also be installed by manually installing a .jar file to a directory in your CLASSPATH. Download the MariaDB Connector/J .jar files from the following URL: https://downloads.mariadb.com/Connectors/java/connector-java-2.5.4/.

Copy the .jar files to the /opt/mapr/hive/hive-<version>/lib/ directory:

cp mariadb-java-client-2.5.4-sources.jar /opt/mapr/hive-<version>/lib/
cp mariadb-java-client-2.5.4.jar /opt/mapr/hive/hive-<version>/lib/
cp mariadb-java-client-2.5.4-javadoc.jar /opt/mapr/hive/hive-<version>/lib/

Restart Hive services:

maprcli node services -name hivemeta -action restart -nodes 'hostname -f'
maprcli node services -name hs2 -action restart -nodes 'hostname -f'

Create the Hive Metastore database and user accounts:

$ mysql -u root -p <password>

MariaDB [(none)]> CREATE USER hiveuser WITH PASSWORD 'password';
MariaDB [(none)]> CREATE DATABASE metastore;
MariaDB [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hiveuser'@'metastorehost';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON metastore.* TO 'hiveuser'@'metastorehost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> quit;

Configuring the Hive Metastore on MariaDB

Use these steps:

In the Hive configuration directory (/opt/mapr/hive/hive-<version>/conf), update the hive-site.xml file with the following properties. Beginning with MEP 7.0.0, you must use the MySQL driver with MariaDB:

<property>
    <description>the URL of the MariaDB database</description>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://<hostname>:3306/metastore</value>
</property>
<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>org.mariadb.jdbc.Driver</value>
</property>
<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value><hiveuser></value>
</property>
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value><fill in with password></value>
</property>
<property>
    <description>IP address (or FQDN) and port of the metastore host</description>
    <name>hive.metastore.uris</name>
    <value>thrift://<hostname>:9083</value>
</property>

Run the schematool command as an initialization step:

/opt/mapr/hive/hive-<version>/bin/schematool -dbType mysql -initSchema