Aditya,
I tried both with cast without cast.
I have executed the statement you sent, it is not coming back, I believe it is
doing full-table-scan. I will let you know the time it took after it comes
back. But before doing that I ran the plan. Please take a look. I don’t see it
limiting with start key and stop keys.
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> select
. . . . . . . . . . . . . . . . . . . . . . .>
trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from
. . . . . . . . . . . . . . . . . . . . . . .> maprfs.views.view_maprdb3 where
trans_id ='7654';
+------------+------------+------------+------------+------------+------------+------------+------------+
| trans_id | trans_date | device_id | game | trans_tpe | trans_amt |
state | country |
+------------+------------+------------+------------+------------+------------+------------+------------+
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> explain plan for select
. . . . . . . . . . . . . . . . . . . . . . .>
trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from
. . . . . . . . . . . . . . . . . . . . . . .> maprfs.views.view_maprdb3 where
trans_id ='7654';
+------------+------------+
| text | json |
+------------+------------+
| 00-00 Screen
00-01 Project(trans_id=[$0], trans_date=[$1], device_id=[$2], game=[$3],
trans_tpe=[$4], trans_amt=[$5], state=[$6], country=[$7])
00-02 UnionExchange
01-01 SelectionVectorRemover
01-02 Filter(condition=[=($0, '7654')])
01-03 Project(trans_id=[CAST($0):VARCHAR(20) CHARACTER SET
"ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL],
trans_date=[CAST(CAST(ITEM($1, 'trans_date')):DATE):DATE NOT NULL],
device_id=[CAST(CAST(ITEM($1, 'device_id')):INTEGER):INTEGER NOT NULL],
game=[CAST(CAST(ITEM($1, 'game')):VARCHAR(50) CHARACTER SET "ISO-8859-1"
COLLATE "ISO-8859-1$en_US$primary"):VARCHAR(50) CHARACTER SET "ISO-8859-1"
COLLATE "ISO-8859-1$en_US$primary" NOT NULL], trans_tpe=[CAST(CAST(ITEM($1,
'trans_type')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL], trans_amt=[CAST(CAST(ITEM($1,
'trans_amt')):INTEGER):INTEGER NOT NULL], state=[CAST(CAST(ITEM($1,
'state')):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL], country=[CAST(CAST(ITEM($1,
'country')):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"):VARCHAR(2) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL])
01-04 Scan(groupscan=[HBaseGroupScan
[HBaseScanSpec=HBaseScanSpec [tableName=trans, startRow=null, stopRow=null,
filter=null], columns=[`row_key`, `cf`.`trans_date`, `cf`.`device_id`,
`cf`.`game`, `cf`.`trans_type`, `cf`.`trans_amt`, `cf`.`state`,
`cf`.`country`]]])
| {
"head" : {
"version" : 1,
"generator" : {
"type" : "ExplainHandler",
"info" : ""
},
"type" : "APACHE_DRILL_PHYSICAL",
"options" : [ ],
"queue" : 0,
"resultMode" : "EXEC"
},
"graph" : [ {
"pop" : "hbase-scan",
"@id" : 65540,
"hbaseScanSpec" : {
"tableName" : "trans",
"startRow" : "",
"stopRow" : "",
"serializedFilter" : null
},
"storage" : {
"type" : "hbase",
"config" : {
"hbase.table.namespace.mappings" : "*:/user/epeck/tables"
},
"size.calculator.enabled" : false,
"enabled" : true
},
"columns" : [ "`row_key`", "`cf`.`trans_date`", "`cf`.`device_id`",
"`cf`.`game`", "`cf`.`trans_type`", "`cf`.`trans_amt`", "`cf`.`state`",
"`cf`.`country`" ],
"cost" : 2.8311552E7
}, {
"pop" : "project",
"@id" : 65539,
"exprs" : [ {
"ref" : "`trans_id`",
"expr" : "cast( (`row_key` ) as VARCHAR(20) )"
}, {
"ref" : "`trans_date`",
"expr" : "cast( (cast( (`cf`.`trans_date` ) as DATE ) ) as DATE )"
}, {
"ref" : "`device_id`",
"expr" : "cast( (cast( (`cf`.`device_id` ) as INT ) ) as INT )"
}, {
"ref" : "`game`",
"expr" : "cast( (cast( (`cf`.`game` ) as VARCHAR(50) ) ) as VARCHAR(50) )"
}, {
"ref" : "`trans_tpe`",
"expr" : "cast( (cast( (`cf`.`trans_type` ) as VARCHAR(20) ) ) as
VARCHAR(20) )"
}, {
"ref" : "`trans_amt`",
"expr" : "cast( (cast( (`cf`.`trans_amt` ) as INT ) ) as INT )"
}, {
"ref" : "`state`",
"expr" : "cast( (cast( (`cf`.`state` ) as VARCHAR(2) ) ) as VARCHAR(2) )"
}, {
"ref" : "`country`",
"expr" : "cast( (cast( (`cf`.`country` ) as VARCHAR(2) ) ) as VARCHAR(2)
)"
} ],
"child" : 65540,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 2.8311552E7
}, {
"pop" : "filter",
"@id" : 65538,
"child" : 65539,
"expr" : "equal(`trans_id`, '7654') ",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 4246732.8
}, {
"pop" : "selection-vector-remover",
"@id" : 65537,
"child" : 65538,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 4246732.8
}, {
"pop" : "union-exchange",
"@id" : 2,
"child" : 65537,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 4246732.8
}, {
"pop" : "project",
"@id" : 1,
"exprs" : [ {
"ref" : "`trans_id`",
"expr" : "`trans_id`"
}, {
"ref" : "`trans_date`",
"expr" : "`trans_date`"
}, {
"ref" : "`device_id`",
"expr" : "`device_id`"
}, {
"ref" : "`game`",
"expr" : "`game`"
}, {
"ref" : "`trans_tpe`",
"expr" : "`trans_tpe`"
}, {
"ref" : "`trans_amt`",
"expr" : "`trans_amt`"
}, {
"ref" : "`state`",
"expr" : "`state`"
}, {
"ref" : "`country`",
"expr" : "`country`"
} ],
"child" : 2,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 4246732.8
}, {
"pop" : "screen",
"@id" : 0,
"child" : 1,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 4246732.8
} ]
} |
+------------+------------+
1 row selected (0.152 seconds)
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11>
Thanks
Sudhakar Thota
On Feb 4, 2015, at 4:18 PM, Aditya <[email protected]> wrote:
> select
> trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from
> maprfs.views.view_maprdb3 where trans_id ='7654';
