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 <[email protected]> wrote: > Any one can answer my question? > > Thanks > > George > > On Sat, May 16, 2015 at 6:00 PM, George Lu <[email protected]> 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 <[email protected]> > 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 > >> > > > > >
