select query inside hive not working

[#1]

Hello Everyone

While triggering sql SELECT statement inside HIVE i get following error messages. I have mentioned the sql statement and the output below. Any suggestion will be highly appreciated.

hive (default)> show tables;
OK
order_items
Time taken: 0.35 seconds, Fetched: 1 row(s)
hive (default)> select count(1) from order_items;
Exception in thread "d413467f-6da8-4ebc-bf93-730e15b4b23f main" java.lang.NoClassDefFoundError: org/apache/hadoop/hive/io/HdfsUtils$HadoopFileStatus
    at org.apache.hadoop.hive.common.FileUtils.mkdir(FileUtils.java:545)
    at org.apache.hadoop.hive.ql.Context.getStagingDir(Context.java:237)
    at org.apache.hadoop.hive.ql.Context.getExtTmpPathRelTo(Context.java:429)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFileSinkPlan(SemanticAnalyzer.java:6437)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPostGroupByBodyPlan(SemanticAnalyzer.java:8961)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:8850)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9703)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9596)
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:291)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10103)
    at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:228)
    at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:239)
    at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:473)
    at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:319)
    at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1249)
    at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1295)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1178)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1166)
    at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:236)
    at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:187)
    at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
    at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:782)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:721)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:648)
    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:498)
    at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.io.HdfsUtils$HadoopFileStatus
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 30 more
[hduser@storage Softwares]$

[#2]

I have to write all the steps for hadoop installation but  i guess with .bashrc you will get to know the location for hadoop and hive. Please advise

# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
    . /etc/bashrc
fi

# Set Hadoop-related environment variables
#export HADOOP_HOME=/home/hduser/hadoop
export HADOOP_HOME=/home/hduser/hadoop-2.6.5
export HADOOP_INSTALL=/home/hduser/hadoop-2.6.5

#Set JAVA_HOME (we will also configure JAVA_HOME directly for Hadoop later on)

export JAVA_HOME=/usr/local/jdk1.8.0_111
export PATH=$PATH:$JAVA_HOME/bin
PATH=$PATH:$HOME/bin
export PATH


# Some convenient aliases and functions for running Hadoop-related commands
unalias fs &> /dev/null
alias fs="hadoop fs"
unalias hls &> /dev/null
alias hls="fs -ls"

# If you have LZO compression enabled in your Hadoop cluster and
# compress job outputs with LZOP (not covered in this tutorial):
# Conveniently inspect an LZOP compressed file from the command
# line; run via:
#
# $ lzohead /hdfs/path/to/lzop/compressed/file.lzo
#
# Requires installed 'lzop' command.

lzohead () {
    hadoop fs -cat $1 | lzop -dc | head -1000 | less
}

# Add Hadoop bin/ directory to PATH
export PATH=$PATH:$HADOOP_HOME/bin

# Add Pig bin / directory to PATH
export PIG_HOME=/home/hduser/pig-0.15.0
export PATH=$PATH:$PIG_HOME/bin

# User specific aliases and functions

export HADOOP_INSTALL=$HADOOP_HOME
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native
export HADOOP_OPTS="-Djava.library.path=$HADOOP_HOME/lib"
export PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin
export PATH=$PATH:$HADOOP_HOME/bin:$HADOOP_HOME/sbin

export SCALA_HOME=/home/hduser/scala/
export PATH=$PATH:$SCALA_HOME:/bin/

# Add Sqoop bin / directory to PATH
export SQOOP_HOME=/home/hduser/Softwares/sqoop
export PATH=$PATH:$SQOOP_HOME/bin/

# Add Hive bin / directory to PATH
export HIVE_HOME=/home/hduser/Softwares/apache-hive-2.0.1-bin
export PATH=$PATH:$HIVE_HOME/bin/
export HIVE_CONF_DIR=$HIVE_HOME/conf

[hduser@storage ~]$

[#3]

Can you try again by starting Hive CLI using debug mode:

hive --hiveconf hive.root.logger=DEBUG,console

And then run the query that failed:

select count(1) from order_items;

Please copy and paste the output after you run SELECT query here for review.

Thanks

[#4]

Another test you can do is below:

1. start HiveMetaStore server:
hive --service metastore &

2. start HiveServer2 server:
hiveserver2 &

3. start beeline:
beeline -u 'jdbc:hive2://localhost:10000/default' -n hive

4. run the SELECT query to see if it fails the same way

5. if still failed, check the HiveServer2 server log file, which is defined under /home/hduser/Softwares/apache-hive-2.0.1-bin/conf/hive-log4j2.properties file with the following properties:

property.hive.log.dir
property.hive.log.file

Check the error there.

[#5]

Please find the output of the command from below :-

1. start HiveMetaStore server:
hive --service metastore &

[hduser@storage Desktop]$ hive --service metastore &
[1] 13900
[hduser@storage Desktop]$ which: no hbase in (/usr/local/jdk1.8.0_111/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/:/home/hduser/bin:/home/hduser/scala//bin/:/home/hduser/Softwares/sqoop//bin/:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/)
Starting Hive Metastore Server
[hduser@storage Desktop]$


2. start HiveServer2 server:
hiveserver2 &

[hduser@storage Desktop]$ hiveserver2 &
[2] 14114
[hduser@storage Desktop]$ which: no hbase in (/usr/local/jdk1.8.0_111/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/:/home/hduser/bin:/home/hduser/scala//bin/:/home/hduser/Softwares/sqoop//bin/:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/)
[hduser@storage Desktop]$



3. start beeline:
beeline -u 'jdbc:hive2://localhost:10000/default' -n hive

[hduser@storage Desktop]$ beeline -u 'jdbc:hive2://localhost:10000/default' -n hive
which: no hbase in (/usr/local/jdk1.8.0_111/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/:/home/hduser/bin:/home/hduser/scala//bin/:/home/hduser/Softwares/sqoop//bin/:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/)
ls: cannot access /home/hduser/Softwares/apache-hive-2.0.1-bin/lib/hive-jdbc-*-standalone.jar: No such file or directory
Connecting to jdbc:hive2://localhost:10000/default
17/05/05 17:37:50 [main]: INFO jdbc.HiveConnection: Transport Used for JDBC connection: null
Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000/default: java.net.ConnectException: Connection refused (Connection refused) (state=08S01,code=0)
Beeline version 2.0.1 by Apache Hive
beeline>


4. run the SELECT query to see if it fails the same way

Question : At 4, You mentioned to trigger select query. So here you want me to trigger select query from hive > or beeline > ? . However, I tried it from hive > . It shows an error. Output mentioned below :

hduser@storage Desktop]$ hive
which: no hbase in (/usr/local/jdk1.8.0_111/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/:/home/hduser/bin:/home/hduser/scala//bin/:/home/hduser/Softwares/sqoop//bin/:/usr/local/jdk1.8.0_111/bin:/home/hduser/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/pig-0.15.0/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/bin:/home/hduser/hadoop-2.6.5/sbin:/home/hduser/scala/:/bin/:/home/hduser/Softwares/sqoop/bin/:/home/hduser/Softwares/apache-hive-2.0.1-bin/bin/)

Logging initialized using configuration in jar:file:/home/hduser/Softwares/apache-hive-2.0.1-bin/lib/hive-common-2.1.1.jar!/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> show databases;
OK
default
hive
Time taken: 1.436 seconds, Fetched: 2 row(s)
hive> use hive;
OK
Time taken: 0.031 seconds
hive> show tables;
OK
orders
Time taken: 0.29 seconds, Fetched: 1 row(s)
hive> select * from orders;
Exception in thread "fc443fd9-8e62-4269-8df3-fae076efb9c2 main" java.lang.NoClassDefFoundError: org/apache/hadoop/hive/io/HdfsUtils$HadoopFileStatus
    at org.apache.hadoop.hive.common.FileUtils.mkdir(FileUtils.java:545)
    at org.apache.hadoop.hive.ql.Context.getStagingDir(Context.java:237)
    at org.apache.hadoop.hive.ql.Context.getExtTmpPathRelTo(Context.java:429)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFileSinkPlan(SemanticAnalyzer.java:6437)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPostGroupByBodyPlan(SemanticAnalyzer.java:8961)
    at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:8850)


5. if still failed, check the HiveServer2 server log file, which is defined under /home/hduser/Softwares/apache-hive-2.0.1-bin/conf/hive-log4j2.properties file with the following properties:

Answer :  There is no hive-log4j2.properties. Steps implemented are mentioned below

property.hive.log.dir
property.hive.log.file

Check the error there.

[hduser@storage Desktop]$ more /home/hduser/Softwares/apache-hive-2.0.1-bin/conf/hive-log4j2.properties
/home/hduser/Softwares/apache-hive-2.0.1-bin/conf/hive-log4j2.properties: No such file or directory
[hduser@storage Desktop]$  cd /home/hduser/Softwares/apache-hive-2.0.1-bin/conf/hive-log4j2.properties
bash: cd: /home/hduser/Softwares/apache-hive-2.0.1-bin/conf/hive-log4j2.properties: No such file or directory
[hduser@storage Desktop]$  cd /home/hduser/Softwares/apache-hive-2.0.1-bin/conf/
[hduser@storage conf]$  pwd
/home/hduser/Softwares/apache-hive-2.0.1-bin/conf
[hduser@storage conf]$  ls -ltr hive-log4j2.properties.*
-rw-r--r--. 1 hduser hadoop 2758 Apr 23  2016 hive-log4j2.properties.template
[hduser@storage conf]$

Will look forward to hear from you

Thank You

Ujjwal Rana

[#6 · Accepted solution]

I am able to solve the issue. I simply uninstall and re-install hive and with that it works. Select query is now able to show an output without any issue.