Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

HDP 2.5 Hive DB connection error

Highlighted

HDP 2.5 Hive DB connection error

Explorer

Hi , I am installing HDP 2.5.3 using Ambari 2.4.1.0, While installation of Hive, i was trying to use Postgres or MySQL databases but with both db , hive connection is not successful. I am getting an error.

I have copied DBConn. files from ambari-server to ambari-agent path, as suggested but it is still not working. Any idea how to resolve this issue.

Error downloading DBConnectionVerification.jar from Ambari Server resources. Check network access to Ambari Server.

HTTP Error 404: Not Found

4 REPLIES 4
Highlighted

Re: HDP 2.5 Hive DB connection error

Contributor

@Rohit M

Please follow the below steps while Ambari installation. It will help to understand how to use non default databases with the appropriate method.

Prepare Mysql server for the metadata database ( For POC easy to use same Ambari host for MySQL database server)

Login with the root super user or sudo user

# sudo su Install MySQL connector

# yum install mysql-connector-java

----------------------------------------------------------------------------

Installation steps for community release

# wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm

#rpm -ivh mysql57-community-release-el7-11.noarch.rpm

# yum install mysql-server

# service mysqld start #service mysqld status ( to validate service is up and running)

#grep 'temporary password' /var/log/mysqld.log

XXXXX [Note] A temporary password is generated for root@localhost: XXXXXX

----------------------------------------------------------------------------

Setup secure MySQL which is help to set super user password

#mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:

... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:

The 'validate_password' plugin is installed on the server.

The subsequent steps will run with the existing configuration

of the plugin.

Using existing password for root.

Estimated strength of the password: 100

Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

New password:

Re-enter new password:

Estimated strength of the password: 100

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

By default, a MySQL installation has an anonymous user,

allowing anyone to log into MySQL without having to have

a user account created for them. This is intended only for

testing, and to make the installation go a bit smoother.

You should remove them before moving into a production

environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Success.

Normally, root should only be allowed to connect from

'localhost'. This ensures that someone cannot guess at

the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N

... skipping.

By default, MySQL comes with a database named 'test' that

anyone can access. This is also intended only for testing,

and should be removed before moving into a production

environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N

... skipping.

Reloading the privilege tables will ensure that all changes

made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

Success.

All done!

-------------------------------------------------------------------------------------------------

Metadata database creation Create database and provide access to ambari management users

# mysql -u root -p

CREATE USER 'ambari'@'%' IDENTIFIED BY '<AMBARIPASSWORD>';

GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'%';

CREATE USER 'ambari'@'localhost' IDENTIFIED BY '<AMBARIPASSWORD>';

GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'localhost';

CREATE USER 'ambari'@'<FQDN>' IDENTIFIED BY '<AMBARIPASSWORD>';

GRANT ALL PRIVILEGES ON *.* TO 'ambari'@'<FQDN>';

FLUSH PRIVILEGES;

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

HIVE metadata non default database creation ( you can also create for Oozie only replace the user and database values)

# mysql -u root -p

CREATE DATABASE <HIVEDATABASE>

CREATE USER ‘<HIVEUSER>’@’localhost’ IDENTIFIED BY ‘<HIVEPASSWORD>’;

GRANT ALL PRIVILEGES ON <HIVEDATABASE>.* TO '<HIVEUSER>'@'localhost';

CREATE USER ‘<HIVEUSER>’@’%’ IDENTIFIED BY ‘<HIVEPASSWORD>’;

GRANT ALL PRIVILEGES ON *.* TO '<HIVEUSER>'@'%';

CREATE USER '<HIVEUSER>'@'<HIVEMETASTOREFQDN>'IDENTIFIED BY '<HIVEPASSWORD>';

GRANT ALL PRIVILEGES ON <HIVEDATABASE>.* TO '<HIVEUSER>'@'<HIVEMETASTOREFQDN>';

FLUSH PRIVILEGES;

-------------------------------------------------------------------------------------------------

Install Ambari packages

skip this step if you already installed the Ambari packages

#wget -nv http://public-repo-1.hortonworks.com/ambari/centos7/2.x/updates/2.5.2.0/ambari.repo -O /etc/yum.repos.d/ambari.repo

#yum repolist #yum install ambari-server

--------------------------------------------------------------------------------------------------

Ambari database and table creation script

# mysql -u ambari -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> CREATE DATABASE hdp;

Query OK, 1 row affected (0.00 sec)

mysql> USE hdp;

Database changed

mysql> SOURCE Ambari-DDL-MySQL-CREATE.sql;

----------------------------------------------------------------------------------------------------

Setup MySQL connector on Ambari server

ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar

Using python /usr/bin/python

Setup ambari-server

Copying /usr/share/java/mysql-connector-java.jar to /var/lib/ambari-server/resources

If you are updating existing jdbc driver jar for mysql with mysql-connector-java.jar. Please remove the old driver jar, from all hosts. Restarting services that need the driver, will automatically copy the new jar to the hosts.

JDBC driver was successfully initialized.

Ambari Server 'setup' completed successfully.

Note: While the cluster deployment through the ambari, select the existing non default MySQL database for hive oozie. Please provide right information for username, database, password

Highlighted

Re: HDP 2.5 Hive DB connection error

Explorer

Hi Vikas, Thanks ... I did follow the steps mentioned in the below link. I am fine to use default database as well for my installation, but i am getting the same issue with both options.

https://docs.hortonworks.com/HDPDocuments/Ambari-2.4.2.0/bk_ambari-reference/content/using_hive_with...

Highlighted

Re: HDP 2.5 Hive DB connection error

Contributor

can you please share the logs and if possible screenshot as well

Re: HDP 2.5 Hive DB connection error

Contributor

Try below points and provide the output @Rohit M

If you are using different host for mysql server please check the in database you are giving the grant permission

1) check the iptables and selinux status it should be disabled

2) use mysql client command from the Ambari server to check MySQL database get connected

mysql -u<username> -p<password> -D<databasename> -h<database-server-hostname>

Don't have an account?
Coming from Hortonworks? Activate your account here