Hive:FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'Field'

Question

there! I am Cherry! Recently a problem always confused me a lot of time!

    > use test;
OK
Time taken: 0.046 seconds
hive> show tables;
OK
detectionindex
field
first1
galspecline
neighbors
photoobjall
photoz
spplines
sppparams
thingindex
zoonospec
Time taken: 0.092 seconds, Fetched: 11 row(s)

When I run the command below:

hive -e "use test;"
hive -e "SELECT * from Field;"

It shows:

FAILED: SemanticException [Error 10001]: Line 1:31 Table not found 'Field'

• The hive.log is here:

ERROR [main]: ql.Driver (SessionState.java:printError(960)) - FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'Field' org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:14 Table not found 'Field' at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:1868) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:1545) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genResolvedParseTree(SemanticAnalyzer.java:10077) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10128) at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:209) at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:227) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:424) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:308) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1122) at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1170) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1049) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:213) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:165) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:311) at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:708) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:14 Table not found 'Field' at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:1594) ... 24 more

2015-08-09 14:43:23,697 INFO [main]: log.PerfLogger (PerfLogger.java:PerfLogEnd(148)) - 2015-08-09 14:43:23,698 INFO [main]: log.PerfLogger (PerfLogger.java:PerfLogBegin(121)) - 2015-08-09 14:43:23,698 INFO [main]: log.PerfLogger (PerfLogger.java:PerfLogEnd(148)) - 2015-08-09 14:43:23,704 INFO [main]: log.PerfLogger (PerfLogger.java:PerfLogBegin(121)) - 2015-08-09 14:43:23,704 INFO [main]: log.PerfLogger (PerfLogger.java:PerfLogEnd(148)) -

My hive-site.xml is here:

<property>
  <name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost: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>
  </property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>hive</value>
</property>

    </configuration>

Please help me! Thanks!

Answer 1

Each hive -e command-line starts its own Java VM, executes the instruction(s) passed as argument, then shuts down.

Different executions, different sessions, no memory of previous queries.

Try instead

hive -e "use test ; select * from Field"

Or

hive -e "select * from test.Field"

Answer 2

Try:

hive -e "use test"
hive -e "SELECT * from Field"

Answer 3

I realize this is old, but in case someone runs into a similar problem, here is how I fixed it -- also might be a different cause, but I had recently upgraded from Hive 2.1 to Hive 2.3. Bottom line, the problem is caused by an invalid metastore schema for the version being used and the schema must be upgraded to match the Hive version.

Look in the directory $HIVE_HOME/scripts/metastore/upgrade/ and find the directory that matches the database you are using for you metastore. Upgrade scripts are provided for:

1. derby
2. mssql
3. mysql
4. oracle
5. postgre

Inside each directory is a README file that explains how to upgrade your metastore schema. In my case, I use a mysql database for my metastore, and went from Hive 2.1 to Hive 2.3 which meant I needed to run: 1) upgrade-2.1.0-to-2.2.0.mysql.sql AND 2) upgrade-2.2.0-to-2.3.0.mysql.sql in sequence.

Went back to Hive, ran the select statement, and it worked for me. Hope this helps.

Answer 4

You can use the database first. Then perform the queries. For ex:

USE database_name; SELECT * FROM table_name LIMIT 10;

This would fix the 'Table not found' issue in Hive.