drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Aman Sinha <asi...@maprtech.com>
Subject Re: Queries failure for exists or IN
Date Sun, 17 May 2015 01:06:46 GMT
Hi George,
for the first query (NOT IN), did you try doing a CONVERT_FROM(T.log.pn,'UTF8')
 instead of CAST ?

Also, are you able to do an IN and EXISTS query ?

Aman

On Sat, May 16, 2015 at 5:49 PM, George Lu <luwenbin888@gmail.com> wrote:

> Any one can answer my question?
>
> Thanks
>
> George
>
> On Sat, May 16, 2015 at 6:00 PM, George Lu <luwenbin888@gmail.com> wrote:
>
> > Now, if I change to use not exists
> >
> > SELECT * FROM activities T WHERE T.row_key >= '0000014d584dc0e4' AND
> > CAST(T.log.c AS INT) = 35 AND CONVERT_FROM(T.log.d,'UTF8') <>
> 'ScanHandler'
> > AND CAST(T.log.`at` AS INT) = 2 AND *NOT EXISTS* (SELECT T1.log.pn FROM
> > activities T1 WHERE T1.row_key >= '0000014d532765d8' AND T1.row_key <
> > '0000014d584dc0e4' AND *CONVERT_FROM(T1.log.pn <http://T1.log.pn
> >,'UTF8')
> > = CONVERT_FROM(T.log.pn <http://T.log.pn>,'UTF8')* AND CAST(T1.log.c AS
> > INT) = 35 AND CONVERT_FROM(T1.log.d,'UTF8') <> 'ScanHandler' AND
> > CAST(T1.log.`at` AS INT) = 2)
> >
> > Drill give me this error:
> > Query failed: SYSTEM ERROR: Map, Array or repeated scalar type should not
> > be used in group by, order by or in a comparison operator. Drill does not
> > support compare between MAP:REQUIRED and MAP:REQUIRED.
> >
> > Fragment 6:0
> >
> > The bold compare outer table's phone number with inner table's phone
> > number.
> >
> > What is the error? I seriously have no idea about it..
> >
> >
> >
> > On Sat, May 16, 2015 at 5:22 PM, George Lu <luwenbin888@gmail.com>
> wrote:
> >
> >> Hi all,
> >>
> >> I have a simple queries using NOT IN, I have a data set stored in HBase
> >> and I have to find some customer's phone number only appears today and
> not
> >> in previous day.
> >>
> >> Here is my query ( I can execute two queries separately)
> >>
> >> SELECT * FROM activities T WHERE row_key >= '0000014d584dc0e4' AND
> >> CAST(T.log.c AS INT) = 35 AND CONVERT_FROM(T.log.d,'UTF8') <>
> 'ScanHandler'
> >> AND CAST(T.log.`at` AS INT) = 2 AND *CAST(T.log.pn <http://T.log.pn> AS
> >> CHAR(11)) NOT IN* (SELECT *CAST(T.log.pn <http://T.log.pn> AS
> CHAR(11))*
> >> FROM activities T WHERE row_key >= '0000014d532765d8' AND row_key <
> >> '0000014d584dc0e4' AND CAST(T.log.c AS INT) = 35 AND
> >> CONVERT_FROM(T.log.d,'UTF8') <> 'ScanHandler' AND CAST(T.log.`at` AS
> INT) =
> >> 2)
> >>
> >> After executing it, I got below error..
> >> Can anyone explain to me how to make it correct?
> >>
> >> Fragment 1:0
> >>
> >> [120da024-767f-4526-a950-3dd6c95877e0 on prod9:31010]
> >> org.apache.drill.common.exceptions.UserException: SYSTEM ERROR: Failure
> >> while reading vector.  Expected vector class of
> >> org.apache.drill.exec.vector.NullableIntVector but was holding vector
> class
> >> org.apache.drill.exec.vector.NullableVarBinaryVector.
> >>
> >> Fragment 1:0
> >>
> >> [120da024-767f-4526-a950-3dd6c95877e0 on prod9:31010]
> >> at
> >>
> org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:465)
> >> ~[drill-common-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.work.fragment.FragmentExecutor.sendFinalState(FragmentExecutor.java:262)
> >> [drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:232)
> >> [drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> >> [na:1.8.0_25]
> >> at
> >>
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> >> [na:1.8.0_25]
> >> at java.lang.Thread.run(Thread.java:745) [na:1.8.0_25]
> >> Caused by: java.lang.IllegalStateException: Failure while reading
> >> vector.  Expected vector class of
> >> org.apache.drill.exec.vector.NullableIntVector but was holding vector
> class
> >> org.apache.drill.exec.vector.NullableVarBinaryVector.
> >> at
> >>
> org.apache.drill.exec.record.VectorContainer.getValueAccessorById(VectorContainer.java:241)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.getValueAccessorById(AbstractRecordBatch.java:197)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.getValueAccessorById(IteratorValidatorBatchIterator.java:110)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.test.generated.NestedLoopJoinGen653.doSetup(NestedLoopJoinTemplate.java:89)
> >> ~[na:na]
> >> at
> >>
> org.apache.drill.exec.test.generated.NestedLoopJoinGen653.setupNestedLoopJoin(NestedLoopJoinTemplate.java:75)
> >> ~[na:na]
> >> at
> >>
> org.apache.drill.exec.physical.impl.join.NestedLoopJoinBatch.innerNext(NestedLoopJoinBatch.java:164)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:144)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:118)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:101)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:91)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext(AbstractSingleRecordBatch.java:51)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:130)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:144)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:118)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:101)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.test.generated.HashJoinProbeGen657.executeProbePhase(HashJoinProbeTemplate.java:119)
> >> ~[na:na]
> >> at
> >>
> org.apache.drill.exec.test.generated.HashJoinProbeGen657.probeAndProject(HashJoinProbeTemplate.java:223)
> >> ~[na:na]
> >> at
> >>
> org.apache.drill.exec.physical.impl.join.HashJoinBatch.innerNext(HashJoinBatch.java:223)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:144)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:118)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:101)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:91)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext(AbstractSingleRecordBatch.java:51)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:144)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:118)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:101)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:91)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext(AbstractSingleRecordBatch.java:51)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.svremover.RemovingRecordBatch.innerNext(RemovingRecordBatch.java:96)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:144)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:118)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:101)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:91)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractSingleRecordBatch.innerNext(AbstractSingleRecordBatch.java:51)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:130)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:144)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.next(IteratorValidatorBatchIterator.java:118)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:74)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.SingleSenderCreator$SingleSenderRootExec.innerNext(SingleSenderCreator.java:91)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:64)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:199)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at
> >>
> org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:193)
> >> ~[drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> at java.security.AccessController.doPrivileged(Native Method)
> >> ~[na:1.8.0_25]
> >> at javax.security.auth.Subject.doAs(Subject.java:422) ~[na:1.8.0_25]
> >> at
> >>
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1556)
> >> ~[hadoop-common-2.4.1.jar:na]
> >> at
> >>
> org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:193)
> >> [drill-java-exec-0.9.0-rebuffed.jar:0.9.0]
> >> ... 3 common frames omitted
> >> 2015-05-16 17:09:29,745 [BitServer-3] INFO
> >>  o.a.d.e.w.fragment.FragmentExecutor -
> >> 2aa8f6cb-b8d7-692a-9c53-ab76dff01031:1:0: State change requested from
> >> FAILED --> CANCELLATION_REQUESTED for
> >> 2015-05-16 17:09:29,745 [BitServer-3] WARN
> >>  o.a.d.e.w.fragment.FragmentExecutor - Ignoring unexpected state
> transition
> >> FAILED => CANCELLATION_REQUESTED.
> >> 2015-05-16 17:09:29,746 [BitServer-3] INFO
> >>  o.a.d.e.w.fragment.FragmentExecutor -
> >> 2aa8f6cb-b8d7-692a-9c53-ab76dff01031:1:3: State change requested from
> >> RUNNING --> CANCELLATION_REQUESTED for
> >> 2015-05-16 17:09:29,746 [BitServer-3] INFO
> >>  o.a.d.e.w.f.AbstractStatusReporter - State changed for
> >> 2aa8f6cb-b8d7-692a-9c53-ab76dff01031:1:3. New state:
> CANCELLATION_REQUESTED
> >> 2015-05-16 17:09:29,829 [2aa8f6cb-b8d7-692a-9c53-ab76dff01031:frag:1:3]
> >> INFO  o.a.d.e.w.fragment.FragmentExecutor -
> >> 2aa8f6cb-b8d7-692a-9c53-ab76dff01031:1:3: State change requested from
> >> CANCELLATION_REQUESTED --> FAILED for
> >> 2015-05-16 17:09:29,840 [2aa8f6cb-b8d7-692a-9c53-ab76dff01031:frag:1:3]
> >> ERROR o.a.d.c.e.DrillRuntimeException - SYSTEM ERROR: You tried to do a
> >> batch data read operation when you were in a state of NOT_YET.  You can
> >> only do this type of operation when you are in a state of OK or
> >> OK_NEW_SCHEMA.
> >>
> >> Thanks!
> >>
> >> Regards,
> >> George Lu
> >>
> >
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message