2.5. Database Requirements

To use external database for Hive or Oozie metastore, ensure that a MySQL or Oracle or PostgreSQL database is deployed and available.
(By default, Hive and Oozie use Derby database for the metastore.)
- For instructions on deploying and/or configuring MySQL database instance, see here.
- For instructions on configuring an existing Oracle database instance, see here.
  Note
  To deploy a new Oracle instance, consult your database administrator.
- For instructions on deploying and/or configuring an existing PostgreSQL database instance, see here.
Ensure that your database administrator creates the following databases and users:
- If deploying Hive:
  1. hive_dbname: Required if using MySQL database for Hive Metastore.
  2. hive_dbuser
  3. hive_dbpasswd
- If deploying Oozie:
  1. oozie_dbname: Required if using MySQL database for Oozie Metastore.
  2. oozie_dbuser
  3. oozie_dbpasswd

Instructions to setup MySQL database

Connect to the host machine where you plan to deploy MySQL instance and from a terminal window, type:
- For RHEL and CentOS:
```
yum install mysql-server
```
- For SLES:
```
zypper install mysql
```
Start the instance.
- For RHEL and CentOS:
```
/etc/init.d/mysqld start
```
- For SLES:
```
/etc/init.d/mysql start
```
[Optional] - Execute the following command to start MySQL database every time host machine boots up:
```
chkconfig mysqld on
```
Set the root user password and remove unnecessary information from log and STDOUT.
```
mysqladmin -u root password ‘$password’
```
```
mysqladmin -u root 2>&1 >/dev/null
```
Manually create users for MySQL.
- As root, use mysql (or other client tool) to create the dbuser and grant it adequate privileges.
  (For access to Hive metastore, create hive_dbuser and for access to Oozie metastore, create oozie_dbuser.
```
CREATE USER 'dbusername'@'%' IDENTIFIED BY 'dbuserpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dbusername'@'%';
flush privileges;
```
- See if you can connect to the database as that user. You are prompted to enter the dbuserpassword password above.
```
mysql -u $dbusername -p
```

Instructions to configure Oracle database

Ensure that the following SQL script is run against your Hive schema:
```
/usr/lib/hive/scripts/metastore/upgrade/oracle/hive-schema-0.10.0.oracle.sql
```

Instructions to deploy and configure PostgreSQL database

Connect to the host machine where you plan to deploy PostgreSQL instance and from a terminal window, type:
- For RHEL and CentOS:
```
yum install postgresql-server
```
- For SLES:
```
zypper install postgresql-server
```
Start the instance.For RHEL and CentOS:
```
/etc/init.d/postgresql start
```
Note
For some newer versions of PostgreSQL, you might need to execute the following command:
/etc/init.d/postgresql initdb
Reconfigure PostgreSQL server:
1. Edit the /var/lib/pgsql/data/postgresql.conf file and change the value of #listen_addresses = 'localhost' to the following:
```
listen_addresses = '*'
```
2. Edit the /var/lib/pgsql/data/postgresql.conf file and change the port setting #port = 5432 to the following:
```
port = 5432
```
3. Edit the /var/lib/pgsql/data/pg_hba.conf and add the following:
```
host all all 0.0.0.0/0 trust
```
4. Optional - If you are using PostgreSQL v9.1 or later, add the following to the /var/lib/pgsql/data/postgresql.conf file:
```
standard_conforming_strings = off
```

Create users for PostgreSQL server:

echo "CREATE DATABASE $dbname;" | psql -U postgres
echo "CREATE USER $user WITH PASSWORD '$passwd';" | psql -U postgres
echo "GRANT ALL PRIVILEGES ON DATABASE $dbname TO $user;" | psql -U postgres

	Note
	For access to Hive metastore, create `hive_dbuser` and for access to Oozie metastore, create `oozie_dbuser`.

Ensure that the following SQL script is run against your Hive schema:
```
/usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-0.10.0.postgres.sql
```

Legal notices

	Note
	To deploy a new Oracle instance, consult your database administrator.