How to use hive with other user

Question

I installed Hadoop and Hive on 3 cluster. Normally I can use hive and Hadoop but when i log in with other user, I can't use Hive. The error is:

hive> show tables;

FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Cannot get a connection, pool error Could not create a validated object, cause: A read-only user or a user in a read-only database is not permitted to disable read-only mode on a connection.

NestedThrowables:

org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Could not create a validated object, cause: A read-only user or a user in a read-only database is not permitted to disable read-only mode on a connection.

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

I log in with other user,and set permission chmod 777 for the folder which i installed hadoop and hive,i can use hive. But when i log on the other user,it's error. I mean,i have to set permission when i log on to use hive on hadoop. How can use hive on hadoop with one time configure permission ????

Answer 1

I had the same issue and resolved it by the following way. The issue is because of two possible reasons.

• Either, the user you logged in as (do 'whoami' ) does not have write access to the derby metastore database directory used by Hive. So, go to /var/lib/hive/metastore/metastore_db (this is the default in most cases) and verify if your user id has permissions by doing an ls. In case your userid does not have permissions, you should ask the owner of the directory to grant write permissions to you. So, login as the owner/the super user and execute

cd /var/lib/hive/metastore/metastore_db

chmod a+rwx . --recursive

Be warned that this will give permissions to all users. Tweak r-w-x bits accordingly though.

• The other possible reason is that derby is a single user database. So, please clear the lock files by going to

cd /var/lib/hive/metastore/metastore_db

rm *.lck

Answer 2

I can't tell you WHY this worked but it fixed it for me.

cd ../hive/metatstore_db/
rm *.lck

I had an issue with my hadoop account and root account having seperate locks.

Answer 3

Try to avoid using embedded derby server as it depends upon directories and have many limitations.

Its always better to configure your hive with mysql server. You just need to do following configuration in hive-site.xml

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost:3306/hivedb?createDatabaseIfNotExist=true</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>user</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>password</value>
</property>

But remember to give permission to all users who want to use hive by below commands.

GRANT PERMISSIONS

mysql> GRANT ALL ON hivedb.* TO hive_user@'00.00.00.00';

FLUSH PRIVILEGES, Tell the server TO reload the GRANT TABLES

mysql> FLUSH PRIVILEGES;

Answer 4

1)Removing the lock is one option, but that may sometimes kill the job being used by some other user abruptly. 2)You can use hive in different folders for a small team. 3)if you want to share then use derby server and set the xml as above.

Answer 5

In my case i was using sqoop to import a mysql table to hive. Hive had a mysql metastore configured. But somehow sqoop was not knowing that because it was not able to access hive's config file. Once i set HIVE_CONF_DIR environment variable, it worked.

Answer 6

check if the user u are currently logged in with is having write permission on metastore_db file. follow the below steps as:

1. check currently logged in user $whoami

2. navigate to /var/lib/hive/metastore/metastore_db

3. check file permission %ls -l
4. if user not having write permission, grant the permission using chmod command

hopefully this will solve the problem

Answer 7

I ran into the same problem on a new cluster, trying all the above mentioned solutions, none of them seems to be working. After a lot of experimenting, the solution is a three step process:

(a). Verify which hive_site.xml is in use. In my case the default hive_site.xml was used by production hive jobs also, so I thought not to touch it and instead provide a new hive_site.xml to spark for configurations. Incase you don't have any such dependency do not execute point (b), just make changes the hive_site.xml based on point (c).

(b). Copy hive_site.xml to your spark configuration's directory. Like in my case it was: cp hive_site.xml /etc/spark2/conf/.

One more thing incase you are still using spark1.x version or still using its configuration then you need to cp the hive_site.xml to /etc/spark/conf/.

(b). Last Step - Add the following xml properties to the newly copied hive_site.xml

<property>
    <name>mapred.job.tracker</name>
    <value>xx.xx.x.xxx:8032,yy.yy.y.yyy:8032</value>
</property>
<property>
    <name>mapreduce.job.max.split.locations</name>
    <value>25</value>
</property>
<property>
  <name>hive.stats.dbclass</name>
  <value>jdbc:mysql</value>
</property>
<property>
  <name>hive.stats.jdbcdriver</name>
  <value>com.mysql.jdbc.Driver</value>
</property>
<property>
  <name>hive.stats.dbconnectionstring</name>
  <value>jdbc:mysql://xx.xx.x.xxx:3306/metastore_db?useUnicode=true&amp;characterEncoding=UTF-8&amp;user=<your hive user name>;password=<your hive password></value>
</property>
<property>
  <name>hive.aux.jars.path</name>
  <value>file:///usr/share/java/mysql-connector-java.jar</value>
</property>

You need to replace xx.xx.x.xxx:8032,yy.yy.y.yyy:8032 with your own set of master nodes ip's. Similarly you need to replace ; in the xml.