Hive Databases Only List default DB

Question

When I try to list all hive databases through Spark (1.6)

scala> val tdf = sqlContext.sql("SHOW DATABASES");
tdf: org.apache.spark.sql.DataFrame = [result: string]
scala> tdf.show
+-------+
| result|
+-------+
|default|
+-------+

When I try to list all hive databases through hive shell

hive> show databases;
OK
default
Time taken: 0.621 seconds, Fetched: 1 row(s)

While in my hive, actually I already have lot of databases. Am I miss some configuration on my Cloudera cluster? Or maybe there are some problem with my hive metastore?

How you have setup the spark .. i think you are missing coping hive-site.xml copy at spark conf directory.. — sandeep rawat, Jul 02 '18 at 03:21

Kishore · Accepted Answer · 2018-07-03T05:56:31.050

3

use HiveContext to fetch data from the hive. set hive.metastore.uris by

spark code -

System.setProperty("hive.metastore.uris","thrift://hostserver:9083")
val hivecontext = new HiveContext(sparkContext)
val tdf = hivecontext.sql("SHOW DATABASES");

spark-shell

spark-shell --driver-java-options "-Dhive.metastore.uris=thrift://hostserver:9083"

edited Jul 03 '18 at 05:56

answered Jul 02 '18 at 05:35

Kishore

5,761
5
28
53

ya, its working after passing thrift url as argument: spark-shell --driver-java-options "-Dhive.metastore.uris=thrift://hostserver:9083" – Mahadi Siregar Jul 03 '18 at 03:07

score 0 · Answer 2 · answered Jul 02 '18 at 06:01

Since Hive shell also shows only default database, the Hive metastore configuration can be checked.

To start with, you can log into the database having the metastore, and run this query that should list Hive databases. Example query for MySQL database is:

mysql> SELECT NAME, DB_LOCATION_URI FROM hive.DBS;

Then, you can verify and update hive-site.xml as per below. The location of this file on CDH is generally at /usr/lib/hive/conf/hive-site.xml, and on HDP is generally at /usr/hdp/current/hive-client/conf/hive-site.xml.

The documentation reference for configuration of the metastore:

a) https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin#AdminManualMetastoreAdmin-RemoteMetastoreDatabase

b) (CDH) https://www.cloudera.com/documentation/enterprise/5-6-x/topics/cdh_ig_hive_metastore_configure.html (Refer to the section: 4. Configure the metastore service to communicate with the MySQL database)

Example configuration:

<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost/hive?createDatabaseIfNotExist=true</value>
</property>

<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>hive</value>
</property>

Hive Databases Only List default DB

2 Answers2