| Hi Drillers, I am encountering some strange situation with views when it comes to accessing a record using drill. Here is the complete story. 1. Table in MapR DB, called trans 2. Access the table with row_key cast to varchar gets access by row_key and is very quick. select cast(t.cf.device_id as INT) from evan.trans t where cast(row_key as varchar(20))='7654’; 3. Access the table with row_key cast to int gets the record after a long time which is by full-table-scan. select cast(t.cf.device_id as INT) from evan.trans t where cast(row_key as int)=7654; 4. Created a view with all the columns in the table and casting the row_key to varchar(20), accessing the view using drill make a full-table-scan. ------------------------------------ create or replace view view_maprdb3 as SELECT CAST(`row_key` AS VARCHAR(20)) AS `trans_id`, CAST(`t`.`cf`['trans_date'] AS DATE) AS `trans_date`, CAST(`t`.`cf`['device_id'] AS INTEGER) AS `device_id`, CAST(`t`.`cf`['game'] AS VARCHAR(50)) AS `game`, CAST(`t`.`cf`['trans_type'] AS VARCHAR(20)) AS `trans_tpe`, CAST(`t`.`cf`['trans_amt'] AS INTEGER) AS `trans_amt`, CAST(`t`.`cf`['state'] AS VARCHAR(2)) AS `state`, CAST(`t`.`cf`['country'] AS VARCHAR(2)) AS `country` FROM `evan`.`trans` AS `t`; select trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from maprfs.views.view_maprdb3 where cast(trans_id as varchar(20))='7654'; ------------------------------------ 5. Created a view with all the columns in the table and casting row_key to int, accessing the view using drill make full-table-scan. ------------------------------------ Create or replace view view_maprdb as SELECT CAST(`row_key` AS INTEGER) AS `trans_id`, CAST(`t`.`cf`['trans_date'] AS DATE) AS `trans_date`, CAST(`t`.`cf`['device_id'] AS INTEGER) AS `device_id`, CAST(`t`.`cf`['game'] AS VARCHAR(50)) AS `game`, CAST(`t`.`cf`['trans_type'] AS VARCHAR(20)) AS `trans_tpe`, CAST(`t`.`cf`['trans_amt'] AS INTEGER) AS `trans_amt`, CAST(`t`.`cf`['state'] AS VARCHAR(2)) AS `state`, CAST(`t`.`cf`['country'] AS VARCHAR(2)) AS `country` FROM `maprdb`.`/user/epeck/tables/trans` AS `t`; select trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from maprfs.epeck.view_maprdb where cast(trans_id as varchar(20))='7654’; select trans_id,trans_date,device_id,game,trans_tpe,trans_amt,state,country from maprfs.epeck.view_maprdb where trans_id=7654; ------------------------------------ I think I am either defining the view wrong or drill is not doing the expected. I have included the drill calls and explain plans in the attached file. Please let me if you had similar experience, also let me know if you have any work around. |
{\rtf1\ansi\ansicpg1252\cocoartf1265\cocoasubrtf210
{\fonttbl\f0\fswiss\fcharset0 Helvetica;\f1\fnil\fcharset0 Menlo-Regular;}
{\colortbl;\red255\green255\blue255;\red243\green235\blue0;\red255\green255\blue51;}
\margl1440\margr1440\vieww27080\viewh11800\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural
\f0\fs24 \cf0 \cb2 Case-1a: Querying MapR DB table directly casting row_key as varchar. ( Goes by primary key. )\cb1 \
\
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural
\f1\fs22 \cf0 \CocoaLigature0 0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> explain plan for select cast(t.cf.device_id as INT) from evan.trans t where cast(row_key as varchar(20))='7654';\
+------------+------------+\
| text | json |\
+------------+------------+\
| 00-00 Screen\
00-01 Project(EXPR$0=[CAST(ITEM($1, 'device_id')):INTEGER])\
00-02 Scan(groupscan=[HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=trans, startRow=7654, stopRow=7654, filter=null], columns=[`*`]]])\
| \{\
"head" : \{\
"version" : 1,\
"generator" : \{\
"type" : "ExplainHandler",\
"info" : ""\
\},\
"type" : "APACHE_DRILL_PHYSICAL",\
"options" : [ ],\
"queue" : 0,\
"resultMode" : "EXEC"\
\},\
"graph" : [ \{\
"pop" : "hbase-scan",\
"@id" : 2,\
"hbaseScanSpec" : \{\
"tableName" : "trans",\
"startRow" : "NzY1NA==",\
"stopRow" : "NzY1NA==",\
"serializedFilter" : null\
\},\
"storage" : \{\
"type" : "hbase",\
"config" : \{\
"hbase.table.namespace.mappings" : "*:/user/epeck/tables"\
\},\
"size.calculator.enabled" : false,\
"enabled" : true\
\},\
"columns" : [ "`*`" ],\
"cost" : 1048576.0\
\}, \{\
"pop" : "project",\
"@id" : 1,\
"exprs" : [ \{\
"ref" : "`EXPR$0`",\
"expr" : "cast( (`cf`.`device_id` ) as INT )"\
\} ],\
"child" : 2,\
"initialAllocation" : 1000000,\
"maxAllocation" : 10000000000,\
"cost" : 1048576.0\
\}, \{\
"pop" : "screen",\
"@id" : 0,\
"child" : 1,\
"initialAllocation" : 1000000,\
"maxAllocation" : 10000000000,\
"cost" : 1048576.0\
\} ]\
\} |\
+------------+------------+\
1 row selected (0.144 seconds)\
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> select cast(t.cf.device_id as INT) from evan.trans t where cast(row_key as varchar(20))='7654';\
+------------+\
| EXPR$0 |\
+------------+\
| 7339888 |\
+------------+\
1 row selected (0.139 seconds)\
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> \
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural
\f0\fs24 \cf0 \CocoaLigature1 \
\
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural
\cf0 \cb2 Case-1b: Querying MapR DB table directly casting row_key as int. ( Goes to full table scan. )\cb1 \
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural
\cf0 \
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural
\f1\fs22 \cf0 \CocoaLigature0 0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> explain plan for select cast(t.cf.device_id as INT) from evan.trans t where cast(row_key as int)=7654;\
+------------+------------+\
| text | json |\
+------------+------------+\
| 00-00 Screen\
00-01 UnionExchange\
01-01 Project(EXPR$0=[CAST(ITEM($1, 'device_id')):INTEGER])\
01-02 SelectionVectorRemover\
01-03 Filter(condition=[=(CAST($0):INTEGER NOT NULL, 7654)])\
01-04 Scan(groupscan=[HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=trans, startRow=null, stopRow=null, filter=null], columns=[`*`]]])\
| \{\
"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" : [ "`*`" ],\
"cost" : 2.8311552E7\
\}, \{\
"pop" : "filter",\
"@id" : 65539,\
"child" : 65540,\
"expr" : "equal(cast( (`row_key` ) as INT ), 7654) ",\
"initialAllocation" : 1000000,\
"maxAllocation" : 10000000000,\
"cost" : 4246732.8\
\}, \{\
"pop" : "selection-vector-remover",\
"@id" : 65538,\
"child" : 65539,\
"initialAllocation" : 1000000,\
"maxAllocation" : 10000000000,\
"cost" : 4246732.8\
\}, \{\
"pop" : "project",\
"@id" : 65537,\
"exprs" : [ \{\
"ref" : "`EXPR$0`",\
"expr" : "cast( (`cf`.`device_id` ) as INT )"\
\} ],\
"child" : 65538,\
"initialAllocation" : 1000000,\
"maxAllocation" : 10000000000,\
"cost" : 4246732.8\
\}, \{\
"pop" : "union-exchange",\
"@id" : 1,\
"child" : 65537,\
"initialAllocation" : 1000000,\
"maxAllocation" : 10000000000,\
"cost" : 4246732.8\
\}, \{\
"pop" : "screen",\
"@id" : 0,\
"child" : 1,\
"initialAllocation" : 1000000,\
"maxAllocation" : 10000000000,\
"cost" : 4246732.8\
\} ]\
\} |\
+------------+------------+\
1 row selected (0.124 seconds)\
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> \
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural
\f0\fs24 \cf0 \CocoaLigature1 \
\
\
\cb3 Case-2: Creating a view with casting row_key as varchar(20) and querying it. ( Goes to full table scan. )\cb1 \
\
View definition:
\f1\fs22 \CocoaLigature0 view_maprdb3
\f0\fs24 \CocoaLigature1 \
\
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural
\f1\fs22 \cf0 \CocoaLigature0 | DRILL | maprfs.views | view_maprdb3 | SELECT CAST(`row_key` AS VARCHAR(20)) AS `trans_id`, CAST(`t`.`cf`['trans_date'] AS DATE) AS `trans_date`, CAST(`t`.`cf`['device_id'] AS INTEGER) AS `device_id`, CAST(`t`.`cf`['game'] AS VARCHAR(50)) AS `game`, CAST(`t`.`cf`['trans_type'] AS VARCHAR(20)) AS `trans_tpe`, CAST(`t`.`cf`['trans_amt'] AS INTEGER) AS `trans_amt`, CAST(`t`.`cf`['state'] AS VARCHAR(2)) AS `state`, CAST(`t`.`cf`['country'] AS VARCHAR(2)) AS `country`\
FROM `evan`.`trans` AS `t` |\
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural
\f0\fs24 \cf0 \CocoaLigature1 \
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural
\cf0 \
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural
\f1\fs22 \cf0 \CocoaLigature0 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 cast(trans_id as varchar(20))='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=[=(CAST($0):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, '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(cast( (`trans_id` ) as VARCHAR(20) ), '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.201 seconds)\
\
\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\pardirnatural
\cf0 \cb3 Case-3:
\f0\fs24 \cb3 \CocoaLigature1 Creating a view with casting row_key as integer and querying it. ( Goes to full table scan. )
\f1\fs22 \cb1 \CocoaLigature0 \
\
| DRILL | maprfs.epeck | view_maprdb | SELECT CAST(`row_key` AS INTEGER) AS `trans_id`, CAST(`t`.`cf`['trans_date'] AS DATE) AS `trans_date`, CAST(`t`.`cf`['device_id'] AS INTEGER) AS `device_id`, CAST(`t`.`cf`['game'] AS VARCHAR(50)) AS `game`, CAST(`t`.`cf`['trans_type'] AS VARCHAR(20)) AS `trans_tpe`, CAST(`t`.`cf`['trans_amt'] AS INTEGER) AS `trans_amt`, CAST(`t`.`cf`['state'] AS VARCHAR(2)) AS `state`, CAST(`t`.`cf`['country'] AS VARCHAR(2)) AS `country`\
FROM `maprdb`.`/user/epeck/tables/trans` AS `t` |\
\
\
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.epeck.view_maprdb where cast(trans_id as varchar(20))='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=[=(CAST($0):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL, '7654')])\
01-03 Project(trans_id=[CAST($0):INTEGER 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=/user/epeck/tables/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" : "/user/epeck/tables/trans",\
"startRow" : "",\
"stopRow" : "",\
"serializedFilter" : null\
\},\
"storage" : \{\
"type" : "hbase",\
"config" : \{\
"hbase.table.namespace.mappings" : "*:/demos/drill-v1/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 INT )"\
\}, \{\
"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(cast( (`trans_id` ) as VARCHAR(20) ), '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.157 seconds)\
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> \
\
\f0\fs24 \cb3 \CocoaLigature1 Case-4: Without casting row_key and querying it. ( Goes to full table scan. )
\f1\fs22 \cb1 \CocoaLigature0 \
\
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.epeck.view_maprdb 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):INTEGER 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=/user/epeck/tables/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" : "/user/epeck/tables/trans",\
"startRow" : "",\
"stopRow" : "",\
"serializedFilter" : null\
\},\
"storage" : \{\
"type" : "hbase",\
"config" : \{\
"hbase.table.namespace.mappings" : "*:/demos/drill-v1/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 INT )"\
\}, \{\
"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.158 seconds)\
0: jdbc:drill:zk=10.10.15.10:5181,10.10.15.11> \
}Thanks Sudhakar Thota |
