Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Spark2 shell is not displaying all Hive databases. only listing default database

avatar
Super Collaborator

I am using HDP 3.0 with Hive LLAP.

I have pasted the code and output below:

scala> import org.apache.spark.sql.hive.HiveContext

import org.apache.spark.sql.hive.HiveContext

scala> val sqlContext = new org.apache.spark.sql.hive.HiveContext(sc)

warning: there was one deprecation warning; re-run with -deprecation for details sqlContext: org.apache.spark.sql.hive.HiveContext = org.apache.spark.sql.hive.HiveContext@19e03398

scala> sqlContext.sql("show databases").show()

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

|databaseName|

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

| default| +

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

In Hive shell i am able to see all the databases:

0: jdbc:hive2://ip-10-0-10-76.amer.o9solution> show databases; INFO : Compiling command(queryId=hive_20180924093400_b66c3d0c-8e76-4a16-aed7-771fcae43225): show databases INFO : Semantic Analysis Completed (retrial = false) INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:database_name, type:string, comment:from deserializer)], properties:null) INFO : Completed compiling command(queryId=hive_20180924093400_b66c3d0c-8e76-4a16-aed7-771fcae43225); Time taken: 0.003 seconds INFO : Executing command(queryId=hive_20180924093400_b66c3d0c-8e76-4a16-aed7-771fcae43225): show databases INFO : Starting task [Stage-0:DDL] in serial mode INFO : Completed executing command(queryId=hive_20180924093400_b66c3d0c-8e76-4a16-aed7-771fcae43225); Time taken: 0.005 seconds INFO : OK

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

| database_name |

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

| default |

| information_schema |

| rh_ml |

| schema_7539 |

| sys |

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

Any help to resolve this issue? Thank you in advance.

1 ACCEPTED SOLUTION

avatar

@subhash parise

From HDP 3.0 onwards spark has its own separate catalog. This is reason why you don't see any hive databases. To work with hive databases you should use the HiveWarehouseConnector. Link to documentation:

https://docs.hortonworks.com/HDPDocuments/HDP3/HDP-3.0.0/integrating-hive/content/hive_configure_a_s...

HTH

*** If you found this answer addressed your question, please take a moment to login and click the "accept" link on the answer.

View solution in original post

10 REPLIES 10

avatar
New Contributor

Spark and Hive use separate catalogs to access SparkSQL or Hive tables in HDP 3.0 and later. The Spark catalog contains a table created by Spark. The Hive catalog contains a table created by Hive. By default, standard Spark APIs access tables in the Spark catalog. To access tables in the hive catalog, we have to edit the metastore.catalog.default property in hive-site.xml (Set that property value to 'hive' instead of 'spark').

 

Config File Path:  $SPARK_HOME/conf/hive-site.xml

 

Before change the config

 

<property>
    <name>metastore.catalog.default</name>
    <value>spark</value>
</property>

 

before_change_conf.png
After change the config

 

<property>
    <name>metastore.catalog.default</name>
    <value>hive</value>
</property>

 

after_change_conf.png