hive can't create table by hiveserver2

Question

I'm build a simplest hive server(v2.1.1) and want create a simple table on it. but a error encounter:

FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.DDLTask.
 MetaException(message:file:/user/hive/warehouse/test2 is not a directory
 or unable to create one)

my sql on CLI is create table test2(id int) which is very simple. I have test the sql on CLI both of hive and beeline, besides, JDBC also has the same error output.

Other things:
1. show tables works fine.
2. configs under < project-dir > /config/:
1) hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://192.168.1.230:3306/hive?createDatabaseIfNotExist=true</value>
        <description>JDBC connect string for a JDBC metastore</description>    
    </property>   
    <property> 
        <name>javax.jdo.option.ConnectionDriverName</name> 
        <value>com.mysql.jdbc.Driver</value> 
        <description>Driver class name for a JDBC metastore</description>     
    </property>               

    <property> 
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hive</value>
        <description>username to use against metastore database</description>
    </property>
    <property>  
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>hive</value>
        <description>password to use against metastore database</description>  
    </property>

    <property>
        <name>hive.server2.enable.impersonation</name>
        <description>Enable user impersonation for HiveServer2</description>
        <value>true</value>
    </property>
</configuration>

2) hive-default.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://192.168.1.230:3306/hive?createDatabaseIfNotExist=true</value>
        <description>JDBC connect string for a JDBC metastore</description>    
    </property>   
    <property> 
        <name>javax.jdo.option.ConnectionDriverName</name> 
        <value>com.mysql.jdbc.Driver</value> 
        <description>Driver class name for a JDBC metastore</description>     
    </property>               

    <property> 
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hive<value>
        <description>username to use against metastore database</description>
    </property>
    <property>  
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>hive</value>
        <description>password to use against metastore database</description>  
    </property>
</configuration>

3) core-site.xml for hadoop user config

<configuration>
 <property>
      <name>hadoop.proxyuser.hadoop.groups</name>
      <value>*</value>
      <description>Allow the superuser oozie to impersonate any members of the group group1 and group2</description>
 </property>

 <property>
      <name>hadoop.proxyuser.hadoop.hosts</name>
      <value>*</value>
      <description>The superuser can connect only from host1 and host2 to impersonate a user</description>
  </property>
</configuration>

presto can create table in the hive by presto-cli which connect by hive.metastore.uri.
I have ensure hdfs /user/hive/warehouse directory is chmod 755 by other SO topic advice.

I have struggle a whole day. Hope any advice. Thanks

UPDATE also fail with create databse:

hive> create database testdb;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Unable to create database path file:/user/hive/warehouse/testdb.db, failed to create database testdb)

UPDATE
I think it maybe a setting problem with hdfs's user but I'm not sure where I can find all relative settings.

My first guess is a rights/user issue. Does it help if you chmod 777 temporarily? (Or even create the required directory and chmod 777 that). — Dennis Jaheruddin
Not works with hadoop fs -chmod 777 /user/hive/warehouse. Thanks anyway. — LoranceChen
Are you able to create a database (and a table in that?) I am not sure whether tables can be created directly in the warehouse (normally you would at least have a default database or so) — Dennis Jaheruddin
@DennisJaheruddin, fail to create database.....and I'm remember default database's table saved at warehouse dir. — LoranceChen
Try creating an external table with file location to a space where you have access. It seems the Hive userID is not able to write data to the metastore. — Sameer

vasanth vasanth · Accepted Answer · 2017-07-18T05:42:09

It is trying to get the location from local. Ensure hive-site.xml has the configuration like this

<property>
<name>hive.metastore.warehouse.dir</name>
<value>hdfs://CP000187:9000/user/hive/warehouse</value>
<description>location of default database for the
warehouse</description> 
</property>

hive can't create table by hiveserver2

1 Answers