Incomplete HDFS URI While Creating An External Table In Hive

[#1]

Hi!

I've got a cdh 5.4 distribution installed in the cluster mode. When trying to create a new external table based on hdfs location I always get the following error message:

Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: java.io.IOException Incomplete HDFS URI, no host: hdfs://hadoop-nn1:8020:8020/user/hive/warehouse/test.db/profile)

Checked out all configuration files and couldn't find port 8020 to be set twice somewhere in configuration files as mentioned in error message above.

Could you please give a hint what else do I need to check in order to fix this issue? 

Thanks in advance!

[#2]

I have set up a 3 node cluster using CM

CDH 5.5.1

The reference to the namenode is incorrect. Somewhere its picking up an additional ":8020" :-(

The hadoop NN is hadoop01.corp.rhalf.com:8020

ERROR in Hive Metastore server log

2016-01-25 17:32:52,510 ERROR org.apache.hadoop.hive.metastore.RetryingHMSHandler: [pool-3-thread-33]: MetaException(message:Got exception: java.io.IOException Incomplete HDFS URI, no host: hdfs://hadoop01.corp.rhalf.com:8020:8020/user/hive/warehouse/cdr.db/cdr_mjp_uni_srch_usr_hist)
at org.apache.hadoop.hive.metastore.MetaStoreUtils.logAndThrowMetaException(MetaStoreUtils.java:1182)
at org.apache.hadoop.hive.metastore.Warehouse.getFs(Warehouse.java:110)
at org.apache.hadoop.hive.metastore.Warehouse.getDnsPath(Warehouse.java:144)
at org.apache.hadoop.hive.metastore.Warehouse.getDnsPath(Warehouse.java:150)
at org.apache.hadoop.hive.metastore.Warehouse.getTablePath(Warehouse.java:188)
at org.apache.hadoop.hive.metastore.Warehouse.getFileStatusesForUnpartitionedTable(Warehouse.java:542)
at org.apache.hadoop.hive.metastore.MetaStoreUtils.updateUnpartitionedTableStatsFast(MetaStoreUtils.java:179)
at org.apache.hadoop.hive.metastore.MetaStoreUtils.updateUnpartitionedTableStatsFast(MetaStoreUtils.java:174)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1398)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1444)
at sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:133)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:99)
at com.sun.proxy.$Proxy12.create_table_with_environment_context(Unknown Source)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9136)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$create_table_with_environment_context.getResult(ThriftHiveMetastore.java:9120)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:110)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor$1.run(TUGIBasedProcessor.java:106)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671)
at org.apache.hadoop.hive.metastore.TUGIBasedProcessor.process(TUGIBasedProcessor.java:118)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

2016-01-25 17:32:52,510 INFO org.apache.hadoop.hive.ql.log.PerfLogger: [pool-3-thread-33]: </PERFLOG method=create_table_with_environment_context start=1453771972295 end=1453771972510 duration=215 from=org.apache.hadoop.hive.metastore.RetryingHMSHandler threadId=33 retryCount=-1 error=true>

[#3]

Was this a new install or an upgrade?

Things to check;

- value for "fs.defaultFS"

- value for "hive.metastore.warehouse.dir"

Does the issue persists when you create a new Hive table?

For the reported issue in Hive CLI (Check the LOCATION value);

- hive> describe formatted [name of the affected table];

- OR hive> show create table [name of the affected table];

To fix the affected table

- hive> alter table [name of the affected table] set location 'hdfs://replace-this-with-your-namenode:8020/user/hive/warehouse/cdr.db/cdr_mjp_uni_srch_usr_hist'

Let me know if this helps,

Michalis

[#4]

Hi

This is a new install.

I am not able to create any tables yet :-( 

ubuntu@hadoop02:~$ hive -e "use cdr;describe formatted cdr_mjp_uni_srch_usr_hist"

Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.5.1-1.cdh5.5.1.p0.11/jars/hive-common-1.1.0-cdh5.5.1.jar!/hive-log4j.properties

OK

Time taken: 2.006 seconds

FAILED: SemanticException [Error 10001]: Table not found cdr_mjp_uni_srch_usr_hist

[#5]

I queried the table DBS and see how the DB_LOCATION_URI column indeed looked wrong 

mysql> select DB_LOCATION_URI from DBS;

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

| DB_LOCATION_URI                                                     |

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

| hdfs://hadoop01.corp.rhalf.com:8020:8020/user/hive/warehouse        |

| hdfs://hadoop01.corp.rhalf.com:8020:8020/user/hive/warehouse/cdr.db |

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

I update the Hive table to correct the DB_LOCATION_URI column

Then I did some tests like deleting the DB and recreating the DB and tables.

Worked great now.

Still dont know why the DB_LOCATION_URI location got written wrong the first time...But lets move on ahead ! 

Thanks a ton ! 

I believe in CDH and Cloudera ! 

[#6]

@sanjaysubramani, you're exactly right - I noticed the same duplicated service port in the HDFS path (in the metadata tables for Hive):

MariaDB [metastorec02]> select * from DBS;
+-------+-----------------------+----------------------------------------------------------+---------+------------+------------+
| DB_ID | DESC                  | DB_LOCATION_URI                                          | NAME    | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+----------------------------------------------------------+---------+------------+------------+
|     1 | Default Hive database | hdfs://vesta.my.domain:8020:8020/user/hive/warehouse | default | public     | ROLE       |

I followed the same approach as you; I updated DB_LOCATION_URI and excluded one of the :8020 values.

.....

As some additional context, I was simply trying to create an Impala table - stored as Parquet - from an existing table. I was receiving the error:

"ERROR: AnalysisException: Unable to INSERT into target table (default.reqt): Incomplete HDFS URI, no host: hdfs://vesta.my.domain:8020:8020/user/hive/warehouse/reqt"

(CDH 5.8.2)

After applying the fix above, I'm able to create the Impala table.

[#7]

Hi,

so I ran into the same exception on a HDP 3.1.0 for my own external tables, as well as the default and sys tables of hive.

My value for fs.defaultFS was hdfs://my_host.com:8020

• Using ALTER TABLE ... SET LOCATION did not work, since it needs the original location of the table to change its location, which of course isn't possible.
• I then went into the Hive Metastore to change the location of the tables manually. To do this, two Metastore tables are necessary:
  • hive.DBS stores the location of databases in column DB_LOCATION_URI
  • hive.SDS stores the location of tables in column LOCATION
• I then updated those values to only containing the port once using e.g. "UPDATE SDS SET LOCATION = 'example' WHERE SD_ID = 1"
• After that I changed fs.defaultFS to hdfs://my_host.com (without the port number)

After that everything worked out. fs.defaultFS not containing a port number didn't seem to break anything, but be aware.