Search code examples
sqlhadoophivehdfsdata-warehouse

cannot recognize input near database SET LOCATION in alter database statement in hive

during a implementation, tried to change the HDFS path of an existing database in hive with,

changing the /apps/hive/warehouse/ HDFS path from specific path I am getting the following exception as cannot recognize input near database SET LOCATION in alter database statement the database I have created is having the table table_mig created with it.

Please help on the rectifying the possibility and fix for this issue. At this moment I am creating another database with LOCATION specifically with it as

create database db_migg LOCATION '/sit/dwh/hdata/db_migg';

hive> describe database db_mig;
OK
db_mig              hdfs://nnscbhaastest/apps/hive/warehouse/db_mig.db  hiveuser       USER
Time taken: 0.145 seconds, Fetched: 1 row(s)

hive> use db_mig;
OK
Time taken: 0.258 seconds
hive> show tables;
OK
table_mig
Time taken: 0.163 seconds, Fetched: 1 row(s)

hive> ALTER DATABASE db_mig SET LOCATION '/sit/dwh/hdata/db_mig';
NoViableAltException(26@[])
        at org.apache.hadoop.hive.ql.parse.HiveParser.alterDatabaseStatementSuffix(HiveParser.java:10592)
        at org.apache.hadoop.hive.ql.parse.HiveParser.alterStatement(HiveParser.java:9028)
        at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:4057)
        at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1786)
        at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1152)
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:211)
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:171)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:438)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:321)
        at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1224)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1265)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1161)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1151)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:217)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:169)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:380)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:740)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:685)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
FAILED: ParseException line 1:15 cannot recognize input near 'db_mig' 'SET' 'LOCATION' in alter database statement ```
Solution

  • It seems you are using an older version of HIVE. You need to have HIVE version 2.2.1, 2.4.0 or later. Please check the hive wiki for more.

    If you are using lower version of hive, then try dropping and recreating the database with the alter location instead of using it in the ALTER script, as below:

    CREATE DATABASE db_mig LOCATION '/sit/dwh/hdata/db_mig';