external hive metastore issue in EMR cluster

Question

I am pointing my EMR cluster's hive metastore to exteral MySQL RDS instance. I have created new hive database "mydb" and I got the entry in external MySQL DB in hive.DBS table.

hdfs://ip-10-239-1-118.ec2.internal:8020/user/hive/warehouse/mydb.db mydb hadoop USER

I have also created new hive table "mytable" under mydb database. I got the entry in external MySQL DB in hive.TBLS. so far everything is good..

I terminated my cluster..When I come back next day..I launched new cluster now, I did the below, USE MYDB; create table mytable_2(id int); I am getting below error,

Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: java.net.NoRouteToHostException No Route to Host from ip-10-239-1-4.ec2.internal/10.239.1.4 to ip-10-239-1-118.ec2.internal:8020 failed on socket timeout exception: java.net.NoRouteToHostException: No route to host; For more details see: http://wiki.apache.org/hadoop/NoRouteToHost)

note : IP 10.239.1.4 is my current cluster's name node. IP 10.239.1.118 is my earlier cluster's name node

please let me know what properties need to override to avoid this kind of errors?

Answer 1

I have same issue, and fixed. ^_^

hive> create table sales.t1(i int);

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: java.net.NoRouteToHostException

No Route to Host from ip-123-234-101-101.ec2.internal/123-234-101-101 to ip-111-111-202-202.ec2.internal:8020 failed on socket timeout exception: java.net.NoRouteToHostException: No route to host;

For more details see: http://wiki.apache.org/hadoop/NoRouteToHost)

Cause:

We had an external Metastore for the cluster so that we could get rid of the cluster and spin up a new one anytime. Hive Metastore still keeps references to old cluster if there are ‘MANAGED’ tables.

Solution:

hive --service metatool -listFSRoot

hive --service metatool -updateLocation < new_value > < old_value >

E.g.:

new_value = hdfs://ip-XXX.New.XXX.XXX:PORT/user/hive/warehouse
old_value = hdfs://ip-YYY.Old.YYY.YYY:PORT/user/hive/warehouse

Answer 2

Alternatively, you can go into Glue in the AWS console, go to databases/default and edit the entry to have the updated ip in the Location field (which is the output of hive --service metatool -listFSRoot )