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.

NOT IN function and Left Join where b.column is null giving error in Zeppline 0.6.0

Highlighted

NOT IN function and Left Join where b.column is null giving error in Zeppline 0.6.0

New Contributor

I am using zeppline 0.6.0 . I am using %jdbc(phoenix) interpretor and querying phoenix tables.

I am trying to get the records which are unique comparing columns which belong to two different tables using NOT IN function .

The query is some thing like this

SELECT  l.*
FROM    t_left l
WHERE   l.value NOT IN
(
SELECT  value
FROM    t_right r
)

The error what I am getting is

Encountered exception in sub plan [0] execution.
class java.sql.SQLException
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:198)
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:143)
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:138)
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:281)
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266)
org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:265)
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1444)
org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:356)
org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:442)
org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:94)
org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:341)
org.apache.zeppelin.scheduler.Job.run(Job.java:176)
org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
java.util.concurrent.FutureTask.run(FutureTask.java:266)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
java.lang.Thread.run(Thread.java:745)







ERROR   




I tried to implement using left join and IS NULL function , but getting the same error.

SELECT  l.*
FROM    t_left l
LEFT JOIN
t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

The error what I am getting is same

Encountered exception in sub plan [0] execution.
class java.sql.SQLException
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:198)
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:143)
org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:138)
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:281)
org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266)
org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:265)
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1444)
org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:356)
org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:442)
org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:94)
org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:341)
org.apache.zeppelin.scheduler.Job.run(Job.java:176)
org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
java.util.concurrent.FutureTask.run(FutureTask.java:266)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
java.lang.Thread.run(Thread.java:745)
ERROR  

The versions are HDP-2.5.3.0, hbase version is 1.1.2 , Zeppline is 0.60 , pheonix 4.7

Don't have an account?
Coming from Hortonworks? Activate your account here