How to drop a database from hive metastore when underlying HDFS cluster in no longer there

1
votes

I'm working with ephemeral GCP Dataproc clusters ( Apache Spark 2.2.1, Apache Hadoop 2.8.4 and Apache Hive 2.1.1). These clusters all point to the same Hive Metastore (hosted on a Google Cloud SQL instance).

I created a database on one such cluster and set it's location to 'HDFS:///database_name' like so:

$ gcloud dataproc jobs submit hive \
    -e "create database db_name LOCATION 'hdfs:///db_name'" \
    --cluster=my-first-ephemeral-cluster --region=europe-west1

my-first-ephemeral-cluster then got deleted and with it the associated HDFS.

On all subsequent clusters the following error has since been popping up:

u'java.net.UnknownHostException: my-first-ephemeral-cluster-m'

This is probably because the Hive Metastore now has an entry for a location that does not exist. Trying to drop the corrupted database is a no go as well:

$ gcloud dataproc jobs submit hive \
    -e 'drop database db_name' \
    --cluster=my-second-ephemeral-cluster --region=europe-west1

Job [4462cb1d-88f2-4e2b-8a86-c342c0ce46ee] submitted.
Waiting for job output...
Connecting to jdbc:hive2://my-second-ephemeral-cluster-m:10000
Connected to: Apache Hive (version 2.1.1)
Driver: Hive JDBC (version 2.1.1)
18/11/03 13:40:04 [main]: WARN jdbc.HiveConnection: Request to set autoCommit to false; Hive does not support autoCommit=false.
Transaction isolation: TRANSACTION_REPEATABLE_READ
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:java.lang.IllegalArgumentException: java.net.UnknownHostException: my-first-ephemeral-cluster-m) (state=08S01,code=1)
Closing: 0: jdbc:hive2://my-second-ephemeral-cluster-m:10000

The reason being that the host my-first-ephemeral-cluster-m is no longer valid. Since changing the database's location is not an option in the version of hive I'm using, I need a different workaround to dropping this database.

2
hivehdfsgoogle-cloud-dataprochive-metastore
You can try to delete database from the Hive warehouse location, like described here: stackoverflow.com/a/48315225/3227693 - Igor Dvorzhak
that's not about deleting warehouse location, that's about removing db entries from Hive metastore altogether (if metastore is accessible of course) - mangusta
Have you tried recreating a small cluster named 'my-first-ephemeral-cluster' pointing at the same shared metastore just to run your drop database command? - Dennis Huo
@Dennis Yeah, that's exactly what I did :) - Ketan Vatsalya

2 Answers

0
votes

https://cwiki.apache.org/confluence/display/Hive/Hive+MetaTool

The Hive MetaTool enables administrators to do bulk updates on the location fields in database, table, and partition records in the metastore (...)

Example (...)
./hive --service metatool -updateLocation hdfs://localhost:9000 hdfs://namenode2:8020

But first, you need to know how exactly the pseudo-HDFS paths have been saved in the Metastore, in their "canonical" form e.g. hdfs://my-first-ephemeral-cluster-m/db_name (if Google follows Hadoop standards somewhat)

0
votes

Since my point of view, the correct way to delete the Hive metastore entry that causes error is removing the database just before you delete the cluster my-first-ephemeral-cluster, for example an script with this sequence:

gcloud dataproc jobs submit hive -e 'drop database db_name' --cluster=my-first-ephemeral-cluster --region=europe-west1
gcloud dataproc clusters delete my-first-ephemeral-cluster

However, I found instructions of Cloud SQL proxy for setting up a shared hive warehouse between different Dataproc clusters using cloud storage (instead of LOCATION 'hdfs:///db_name' that creates the hive warehouse in the local HDFS), which could give you a behavior like the one you are looking for.