access a hive table specifying database qualifier in spark 2.0

Question

I am trying to access a hive table specifying database qualifier using spark sql. I am using sparksession as it is spark 2.0 or above. For example, I am trying to add a partition to an existing table. The query is

sparksession.sql("ALTER TABLE $databasename.$tablename ADD PARTITION (...) LOCATION ...")

It doesn't throw any error but not even creating any partition. Is it the case that I can't specify databaseName in spark sql?

It's a very big code. Inside that I am implementing a method. Can't share the whole code, basically the code is creating a query which looks like : ALTER TABLE databaseName.tableName ADD PARTITION (partitioncolumns = 'value') LOCATION 'some s3 location' — Pooja Agrawal
It runs perfectly when I don't specify database name, i.e. while it uses the default database. — Pooja Agrawal
Yeah, sparks logs says retries for twice or thrice and then it just stops without throwing an error. I am runnig the spark code on EMR — Pooja Agrawal

Anupam Alok Anupam Alok · Accepted Answer · 2018-02-28T10:28:10

use Database

Command is supported in Latest Spark version you can use

sparksession.sql("use $databasename");
sparksession.sql("ALTER TABLE $tablename ADD PARTITION (...) LOCATION ...");

Try using the above code this can solve you problem.

access a hive table specifying database qualifier in spark 2.0

1 Answers