Skip to content

Instantly share code, notes, and snippets.

@ianbeckett ianbeckett/1 - setup_query.hql
Last active Dec 27, 2015

Embed
What would you like to do?
Learn more about clone URLs
Download ZIP
Hive Java error when using a view with a sub-query from inside another sub-query and including non-views in the FROM clause
Raw
1 - setup_query.hql
-- Create a simple table with 2 columns
-- and then create view that uses the table
-- and has a sub query
CREATE EXTERNAL TABLE IF NOT EXISTS TestTable (
TestID INT,
TestText STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/testtable/';
CREATE VIEW IF NOT EXISTS vTestView (TestID, TestText)
AS SELECT b.TestID, b.TestText
FROM TestTable a
JOIN (
SELECT TestID, TestText
FROM TestTable
) b ON a.TestID = b.TestID;
Raw
2 - failing_selects_query.hql
--Both of the below sub query examples fail when referencing the view.
-- They succeed if referencing the table without using the view
-- SELECT Example #1
SELECT a.TestID, b.TestText
FROM TestTable a
JOIN (SELECT TestID, TestText FROM vTestView) b ON a.TestID = b.TestID
WHERE a.TestID = 1;
-- SELECT Example #2
FROM (SELECT * FROM vTestView) a
JOIN TestTable b ON a.TestID = b.TestID
SELECT a.TestText
WHERE a.TestID = 1;
Raw
3 - Error for Select Examples
tor.java:647)
at org.apache.log4j.PropertyConfigurator.configureRootCategory(PropertyC
onfigurator.java:544)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:440)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:476)
at org.apache.log4j.helpers.OptionConverter.selectAndConfigure(OptionCon
verter.java:471)
at org.apache.log4j.LogManager.<clinit>(LogManager.java:125)
at org.apache.log4j.Logger.getLogger(Logger.java:105)
at org.apache.commons.logging.impl.Log4JLogger.getLogger(Log4JLogger.jav
a:289)
at org.apache.commons.logging.impl.Log4JLogger.<init>(Log4JLogger.java:1
09)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct
orAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
onstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at org.apache.commons.logging.impl.LogFactoryImpl.createLogFromClass(Log
FactoryImpl.java:1116)
at org.apache.commons.logging.impl.LogFactoryImpl.discoverLogImplementat
ion(LogFactoryImpl.java:914)
at org.apache.commons.logging.impl.LogFactoryImpl.newInstance(LogFactory
Impl.java:604)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:336)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:310)
at org.apache.commons.logging.LogFactory.getLog(LogFactory.java:685)
at org.apache.hadoop.conf.Configuration.<clinit>(Configuration.java:146)
at org.apache.hadoop.util.RunJar.main(RunJar.java:113)
log4j:ERROR Could not instantiate appender named "ETW".
log4j:ERROR Could not instantiate class [com.microsoft.log4jappender.FilterLogAp
pender].
java.lang.ClassNotFoundException: com.microsoft.log4jappender.FilterLogAppender
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:186)
at org.apache.log4j.helpers.Loader.loadClass(Loader.java:179)
at org.apache.log4j.helpers.OptionConverter.instantiateByClassName(Optio
nConverter.java:320)
at org.apache.log4j.helpers.OptionConverter.instantiateByKey(OptionConve
rter.java:121)
at org.apache.log4j.PropertyConfigurator.parseAppender(PropertyConfigura
tor.java:664)
at org.apache.log4j.PropertyConfigurator.parseCategory(PropertyConfigura
tor.java:647)
at org.apache.log4j.PropertyConfigurator.configureRootCategory(PropertyC
onfigurator.java:544)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:440)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:476)
at org.apache.log4j.helpers.OptionConverter.selectAndConfigure(OptionCon
verter.java:471)
at org.apache.log4j.LogManager.<clinit>(LogManager.java:125)
at org.apache.log4j.Logger.getLogger(Logger.java:105)
at org.apache.commons.logging.impl.Log4JLogger.getLogger(Log4JLogger.jav
a:289)
at org.apache.commons.logging.impl.Log4JLogger.<init>(Log4JLogger.java:1
09)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct
orAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
onstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at org.apache.commons.logging.impl.LogFactoryImpl.createLogFromClass(Log
FactoryImpl.java:1116)
at org.apache.commons.logging.impl.LogFactoryImpl.discoverLogImplementat
ion(LogFactoryImpl.java:914)
at org.apache.commons.logging.impl.LogFactoryImpl.newInstance(LogFactory
Impl.java:604)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:336)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:310)
at org.apache.commons.logging.LogFactory.getLog(LogFactory.java:685)
at org.apache.hadoop.conf.Configuration.<clinit>(Configuration.java:146)
at org.apache.hadoop.util.RunJar.main(RunJar.java:113)
log4j:ERROR Could not instantiate appender named "FilterLog".
log4j:ERROR Could not instantiate class [com.microsoft.log4jappender.EtwAppender
].
java.lang.ClassNotFoundException: com.microsoft.log4jappender.EtwAppender
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:186)
at org.apache.log4j.helpers.Loader.loadClass(Loader.java:179)
at org.apache.log4j.helpers.OptionConverter.instantiateByClassName(Optio
nConverter.java:320)
at org.apache.log4j.helpers.OptionConverter.instantiateByKey(OptionConve
rter.java:121)
at org.apache.log4j.PropertyConfigurator.parseAppender(PropertyConfigura
tor.java:664)
at org.apache.log4j.PropertyConfigurator.parseCategory(PropertyConfigura
tor.java:647)
at org.apache.log4j.PropertyConfigurator.parseCatsAndRenderers(PropertyC
onfigurator.java:568)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:442)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:476)
at org.apache.log4j.helpers.OptionConverter.selectAndConfigure(OptionCon
verter.java:471)
at org.apache.log4j.LogManager.<clinit>(LogManager.java:125)
at org.apache.log4j.Logger.getLogger(Logger.java:105)
at org.apache.commons.logging.impl.Log4JLogger.getLogger(Log4JLogger.jav
a:289)
at org.apache.commons.logging.impl.Log4JLogger.<init>(Log4JLogger.java:1
09)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct
orAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
onstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at org.apache.commons.logging.impl.LogFactoryImpl.createLogFromClass(Log
FactoryImpl.java:1116)
at org.apache.commons.logging.impl.LogFactoryImpl.discoverLogImplementat
ion(LogFactoryImpl.java:914)
at org.apache.commons.logging.impl.LogFactoryImpl.newInstance(LogFactory
Impl.java:604)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:336)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:310)
at org.apache.commons.logging.LogFactory.getLog(LogFactory.java:685)
at org.apache.hadoop.conf.Configuration.<clinit>(Configuration.java:146)
at org.apache.hadoop.util.RunJar.main(RunJar.java:113)
log4j:ERROR Could not instantiate appender named "ETW".
log4j:ERROR Could not instantiate class [com.microsoft.log4jappender.FilterLogAp
pender].
java.lang.ClassNotFoundException: com.microsoft.log4jappender.FilterLogAppender
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:186)
at org.apache.log4j.helpers.Loader.loadClass(Loader.java:179)
at org.apache.log4j.helpers.OptionConverter.instantiateByClassName(Optio
nConverter.java:320)
at org.apache.log4j.helpers.OptionConverter.instantiateByKey(OptionConve
rter.java:121)
at org.apache.log4j.PropertyConfigurator.parseAppender(PropertyConfigura
tor.java:664)
at org.apache.log4j.PropertyConfigurator.parseCategory(PropertyConfigura
tor.java:647)
at org.apache.log4j.PropertyConfigurator.parseCatsAndRenderers(PropertyC
onfigurator.java:568)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:442)
at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurato
r.java:476)
at org.apache.log4j.helpers.OptionConverter.selectAndConfigure(OptionCon
verter.java:471)
at org.apache.log4j.LogManager.<clinit>(LogManager.java:125)
at org.apache.log4j.Logger.getLogger(Logger.java:105)
at org.apache.commons.logging.impl.Log4JLogger.getLogger(Log4JLogger.jav
a:289)
at org.apache.commons.logging.impl.Log4JLogger.<init>(Log4JLogger.java:1
09)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct
orAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
onstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at org.apache.commons.logging.impl.LogFactoryImpl.createLogFromClass(Log
FactoryImpl.java:1116)
at org.apache.commons.logging.impl.LogFactoryImpl.discoverLogImplementat
ion(LogFactoryImpl.java:914)
at org.apache.commons.logging.impl.LogFactoryImpl.newInstance(LogFactory
Impl.java:604)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:336)
at org.apache.commons.logging.impl.LogFactoryImpl.getInstance(LogFactory
Impl.java:310)
at org.apache.commons.logging.LogFactory.getLog(LogFactory.java:685)
at org.apache.hadoop.conf.Configuration.<clinit>(Configuration.java:146)
at org.apache.hadoop.util.RunJar.main(RunJar.java:113)
log4j:ERROR Could not instantiate appender named "FilterLog".
Execution log at: /tmp/ClairvoyantRD/.log
2013-10-31 05:58:47 Starting to launch local task to process map join;
maximum memory = 932118528
2013-10-31 05:58:48 Processing rows: 1 Hashtable size: 1
Memory usage: 31130536 rate: 0.033
2013-10-31 05:58:48 Dump the hashtable into file: file:/tmp/ClairvoyantRD/hi
ve_2013-10-31_17-58-18_834_3355499043125442456/-local-10003/HashTable-Stage-6/Ma
pJoin-mapfile11--.hashtable
2013-10-31 05:58:48 Upload 1 File to: file:/tmp/ClairvoyantRD/hive_2013-10-3
1_17-58-18_834_3355499043125442456/-local-10003/HashTable-Stage-6/MapJoin-mapfil
e11--.hashtable File size: 344
2013-10-31 05:58:48 Processing rows: 1 Hashtable size: 1
Memory usage: 34799720 rate: 0.037
2013-10-31 05:58:48 Dump the hashtable into file: file:/tmp/ClairvoyantRD/hi
ve_2013-10-31_17-58-18_834_3355499043125442456/-local-10003/HashTable-Stage-6/Ma
pJoin-mapfile00--.hashtable
2013-10-31 05:58:48 Upload 1 File to: file:/tmp/ClairvoyantRD/hive_2013-10-3
1_17-58-18_834_3355499043125442456/-local-10003/HashTable-Stage-6/MapJoin-mapfil
e00--.hashtable File size: 342
2013-10-31 05:58:48 End of local task; Time Taken: 1.109 sec.
Execution completed successfully
Mapred Local Task Succeeded . Convert the Join into MapJoin
Mapred Local Task Succeeded . Convert the Join into MapJoin
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201310301515_0123, Tracking URL = http://jobtrackerhost:50030
/jobdetails.jsp?jobid=job_201310301515_0123
Kill Command = c:\apps\dist\hadoop-1.2.0.1.3.1.0-06\bin\hadoop.cmd job -kill jo
b_201310301515_0123
Hadoop job information for Stage-6: number of mappers: 1; number of reducers: 0
2013-10-31 17:59:05,288 Stage-6 map = 0%, reduce = 0%
2013-10-31 18:00:05,516 Stage-6 map = 0%, reduce = 0%
2013-10-31 18:00:10,532 Stage-6 map = 100%, reduce = 100%
Ended Job = job_201310301515_0123 with errors
Error during job, obtaining debugging information...
Job Tracking URL: http://jobtrackerhost:50030/jobdetails.jsp?jobid=job_201310301
515_0123
Examining task ID: task_201310301515_0123_m_000002 (and more) from job job_20131
0301515_0123
Task with the most failures(8):
-----
Task ID:
task_201310301515_0123_m_000000
URL:
http://jobtrackerhost:50030/taskdetails.jsp?jobid=job_201310301515_0123&tipid=
task_201310301515_0123_m_000000
-----
Diagnostic Messages for this Task:
java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: ja
va.lang.NullPointerException
at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:162)
at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:50)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:429)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:365)
at org.apache.hadoop.mapred.Child$4.run(Child.java:266)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInforma
tion.java:1233)
at org.apache.hadoop.mapred.Child.main(Child.java:260)
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.NullPoint
erException
at org.apache.hadoop.hive.ql.exec.MapJoinOperator.loadHashTable(MapJoinO
perator.java:198)
at org.apache.hadoop.hive.ql.exec.MapJoinOperator.cleanUpInputFileChange
dOp(MapJoinOperator.java:212)
at org.apache.hadoop.hive.ql.exec.Operator.cleanUpInputFileChanged(Opera
tor.java:1377)
at org.apache.hadoop.hive.ql.exec.Operator.cleanUpInputFileChanged(Opera
tor.java:1381)
at org.apache.hadoop.hive.ql.exec.Operator.cleanUpInputFileChanged(Opera
tor.java:1381)
at org.apache.hadoop.hive.ql.exec.Operator.cleanUpInputFileChanged(Opera
tor.java:1381)
at org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:6
13)
at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:144)
... 8 more
Caused by: java.lang.NullPointerException
at org.apache.hadoop.hive.ql.exec.MapJoinOperator.loadHashTable(MapJoinO
perator.java:186)
... 15 more
FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRe
dTask
MapReduce Jobs Launched:
Job 0: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL
Total MapReduce CPU Time Spent: 0 msec
hive>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.