Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

select query inside hive not working

Solved Go to solution

Re: select query inside hive not working

Contributor

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 ~]$

Re: select query inside hive not working

Guru
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

Re: select query inside hive not working

Guru
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.
Highlighted

Re: select query inside hive not working

Contributor

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

 

Re: select query inside hive not working

Contributor

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.