@ilia kheifets
I assume you have installed Postgres or Mysql and run the corresponding on host ubuntu17
ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar
ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql-jdbc.jar
I have used the same user, password and database name for simplicity.
For Postgres
As the root switch to Postgres user
# su - postgres
postgres@ubuntu17:~$ psql
psql (9.5.9) Type "help" for help.
Hive user/database setup
postgres=# DROP DATABASE if exists hive;
postgres=# CREATE USER hive PASSWORD 'hive';
postgres=# CREATE DATABASE hive OWNER hive;
postgres=# grant all privileges on database hive to hive;
postgres=# \q
Oozie user/database setup
postgres=# DROP DATABASE if exists oozie;
postgres=# CREATE USER oozie PASSWORD 'oozie';
postgres=# CREATE DATABASE oozie OWNER oozie;
postgres=# grant all privileges on database oozie to oozie;
postgres=# \q
Ranger user/database setup
postgres=# DROP DATABASE if exists rangerdb;
postgres=# CREATE USER rangerdb PASSWORD 'rangerdb';
postgres=# CREATE DATABASE rangerdb OWNER rangerdb;
postgres=# grant all privileges on database rangerdb to rangerdb;
postgres=# \q
Edit the pg_hba.conf
vi /etc/postgresql/9.5/main/pg_hba.conf at the end of the file in example below my ambari,hive and ranger are using postgres database
local all ambari,hive,oozie,ranger,mapred md5
host all ambari,hive,oozie,ranger,mapred 0.0.0.0/0 md5
host all ambari,hive,oozie,ranger,mapred ::/0 md5
Then restart postgres
/etc/init.d/postgresql restart
For Mysql
In this exampel I assume the root password is hadoop
hive user
# mysql -u root -phadoop
CREATE USER 'hive'@'localhost' IDENTIFIED BY 'hive';
GRANT ALL PRIVILEGES ON *.* TO 'hive'@'localhost';
CREATE USER 'hive'@'%' IDENTIFIED BY 'hive';
GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%';
CREATE USER 'hive'@'<HIVEMETASTORE_FQDN>' IDENTIFIED BY 'hive';
GRANT ALL PRIVILEGES ON *.* TO 'hive'@'<HIVEMETASTOREFQDN>'; FLUSH PRIVILEGES;
Create the Hive database
The Hive database must be created before loading the Hive database schema.
mysql -u root -phadoop
CREATE DATABASE hive; quit;
Oozie user setup
mysql -u root -phadoop
CREATE USER 'hive'@'%' IDENTIFIED BY 'hive';
GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%'; FLUSH PRIVILEGES;
Create the Oozie database
The oozie database must be created before loading the oozie database schema.
mysql -u root -phadoop
CREATE DATABASE oozie;
After either the MySQL or Postrges setup, now in the Ambari UI hive setup see attached screenshots, you will need to use the credentials setup earlier. Choose use existing PostgreSQL/MySQL database
In the initial setup, it will ask you to test the connectivity between hive and Postgres this MUST succeed.In both setups just make sure for both cases the below correct entries are chosen.
Hive Database
Database Name
Database Username
Database Password
JDBC Driver class
Database URL
Hive Database Type
Hope that helps
