Creating Hive Metastore Database Tables Error

ok cloudera is using version HIVE 0.10, that doesn't support remote login

but you need to go around that bug, logging to the server that is getting the error the cloudera manager will tell you the ip

1) create login in to the Server that fails to install HIVE 2) Create a $HADDOP_HOME

$HADOOP_HOME="/usr/lib/hadoop/"

3) INSTALL postgres in the server that fails

`$ sudo apt-get install postgresql`

$ cat /etc/postgresql/9.1/main/postgresql.conf | grep -e listen -e standard_conforming_strings

modify this to lines in the file

listen_addresses = '*'
standard_conforming_strings = off

You also need to configure authentication for your network in pg_hba.conf. You need to make sure that the PostgreSQL user that you will create in the next step will have access to the server from a remote host. To do this, add a new line into pg_hba.con that has the following information:

host    <database>         <user>         <network address>         <mask>               password

Start PostgreSQL Server

$ sudo service postgresql start

Use chkconfig utility to ensure that your PostgreSQL server will start at a boot time:

chkconfig postgresql on

You can use the chkconfig utility to verify that PostgreSQL server will be started at boot time, for example:

chkconfig --list postgresql

Step 2: Install the Postgres JDBC Driver

Before you can run the Hive metastore with a remote PostgreSQL database, you must configure a JDBC driver to the remote PostgreSQL database, set up the initial database schema, and configure the PostgreSQL user account for the Hive user.

To install the PostgreSQL JDBC Driver on a Debian/Ubuntu system:

Install libpostgresql-jdbc-java and symbolically link the file into the /usr/lib/hive/lib/ directory.

$ sudo apt-get install libpostgresql-jdbc-java
$ ln -s /usr/share/java/postgresql-jdbc4.jar /usr/lib/hive/lib/postgresql-jdbc4.jar

Step 3: Create the metastore database and user account

bash# sudo –u postgres psql
bash$ psql
postgres=# CREATE USER hiveuser WITH PASSWORD 'mypassword';
postgres=# CREATE DATABASE metastore;
postgres=# \c metastore;
You are now connected to database 'metastore'.
postgres=# \i /usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-0.10.0.postgres.sql
SET
SET
...

Now you need to grant permission for all metastore tables to user hiveuser. PostgreSQL does not have statements to grant the permissions for all tables at once; you'll need to grant the permissions one table at a time. You could automate the task with the following SQL script:

bash# sudo –u postgres psql
metastore=#         \o /tmp/grant-privs
metastore=#         SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE ON "'  || schemaname || '"."' || tablename || '" TO hiveuser ;'
metastore-#           FROM pg_tables
metastore-#           WHERE tableowner = CURRENT_USER and schemaname = 'public';
metastore=#         \o
metastore=#         \i /tmp/grant-privs

You can verify the connection from the machine where you'll be running the metastore service as follows:

psql –h myhost –U hiveuser –d metastore
metastore=#

Step 4: Configure the Metastore Service to Communicate with the PostgreSQL Database

change the IP of the AWS amazon master Server, or your master server, don't use DNS name

$find / -name hive-site.xml
$nano /run/cloudera-scm-agent/process/27-hive-metastore-create-tables/hive-site.xml

in the File search for:

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:postgresql://myhost/metastore</value>
</property>

and change to the correct IP that is your Master Hadoop Server where u are running Cloudera Manager also every link in that file that is not correctly write to the hadoop master Cloudera manager connector , you will have to change to the correct IP

after all this just get back to the autoinstall of cloudera manager and run again and it will be all good :)

that it all the installation that you have to work around our contract cloudera support (that's their business) :)

all this it works fine for me when i have this problem in de cloudera CDH 4.X with sorl

Regards

Go to this link : http://www.cloudera.com/documentation/enterprise/5-7-x/topics/cm_ig_mysql.html

Go to this topic: Installing the MySQL JDBC Driver

Follow the instruction. Finally Restart your hive service

Thx Kumar