[
https://issues.apache.org/jira/browse/DRILL-4378?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jinfeng Ni reassigned DRILL-4378:
---------------------------------
Assignee: Jinfeng Ni
> CONVERT_FROM in View results in table scan of MapR-DB and perhaps HBASE
> -----------------------------------------------------------------------
>
> Key: DRILL-4378
> URL: https://issues.apache.org/jira/browse/DRILL-4378
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization, Storage - HBase
> Affects Versions: 1.4.0
> Reporter: John Omernik
> Assignee: Jinfeng Ni
>
> I created a view to avoid forcing users to write queries that always
> included the CONVERT_FROM statements. (I am a huge advocate of making things
> easy for the the users and writing queries with CONVERT_FROM statements isn't
> easy).
> I ran a query the other day on one of these views and noticed that a query
> that took 30 seconds really shouldn't take 30 seconds. What do I mean? well
> I wanted to get part of a record by looking up the MapR-DB Row key (equiv. to
> HBASE row key) That should be an instant lookup. Sure enough, when I tried
> it in the hbase shell that returns instantly. So why did Drill take 30
> seconds? I shot an email to Ted and Jim at MapR to ask this very question.
> Ted suggested that I try the query without a view. Sure enough, If I use the
> convert_from in a direct query, it's an instant (sub second) return. Thus it
> appears something in the view is not allowing the query to short circuit the
> read.
> Ted suggests I post here (I am curious if anyone who has HBASE setup is
> seeing this same issue with views) but also include the EXPLAIN plan.
> Basically, using my very limited ability to read EXPLAIN plans (If someone
> has a pointer to a blog post or docs on how to read EXPLAIN I would love
> that!) it looks like in the view the startRow and stopRow in the
> hbaseScanSpec are not set, seeming to cause a scan. Is there any away to
> assist the planner when running this through a view so that we can get the
> performance of the query without the view but with the easy of
> use/readability of using the view?
> Thanks!!!
> John
> View Creation
> CREATE VIEW view_testpaste as
> SELECT
> CONVERT_FROM(row_key, 'UTF8') AS pasteid,
> CONVERT_FROM(pastes.pdata.lang, 'UTF8') AS lang,
> CONVERT_FROM(pastes.raw.paste, 'UTF8') AS paste
> FROM dfs.`pastes`.`/pastes` pastes;
> Select from view takes 32 seconds (seems to be a scan)
> > select paste from view_testpaste where pasteid = 'djHEHcPM'
> 1 row selected (32.302 seconds)
> Just a direct select returns very fast (0.486 seconds)
> > select CONVERT_FROM(pastes.raw.paste, 'UTF8') AS paste
> FROM dfs.`pastes`.`/pastes` pastes where
> CONVERT_FROM(row_key, 'UTF8') = 'djHEHcPM';
> 1 row selected (0.486 seconds)
> EXPLAIN PLAN FOR select paste from view_testpaste where pasteid = 'djHEHcPM'
> +------+------+
> | text | json |
> +------+------+
> | 00-00 Screen
> 00-01 UnionExchange
> 01-01 Project(paste=[CONVERT_FROMUTF8($1)])
> 01-02 SelectionVectorRemover
> 01-03 Filter(condition=[=(CONVERT_FROMUTF8($0), 'djHEHcPM')])
> 01-04 Project(row_key=[$1], ITEM=[ITEM($0, 'paste')])
> 01-05 Scan(groupscan=[MapRDBGroupScan
> [HBaseScanSpec=HBaseScanSpec [tableName=maprfs:///data/pastebiner/pastes,
> startRow=null, stopRow=null, filter=null], columns=[`row_key`,
> `raw`.`paste`]]])
> | {
> "head" : {
> "version" : 1,
> "generator" : {
> "type" : "ExplainHandler",
> "info" : ""
> },
> "type" : "APACHE_DRILL_PHYSICAL",
> "options" : [ ],
> "queue" : 0,
> "resultMode" : "EXEC"
> },
> "graph" : [ {
> "pop" : "maprdb-scan",
> "@id" : 65541,
> "userName" : "darkness",
> "hbaseScanSpec" : {
> "tableName" : "maprfs:///data/pastebiner/pastes",
> "startRow" : "",
> "stopRow" : "",
> "serializedFilter" : null
> },
> "storage" : {
> "type" : "file",
> "enabled" : true,
> "connection" : "maprfs:///",
> "workspaces" : {
> "root" : {
> "location" : "/",
> "writable" : false,
> "defaultInputFormat" : null
> },
> "pastes" : {
> "location" : "/data/pastebiner",
> "writable" : true,
> "defaultInputFormat" : null
> },
> "dev" : {
> "location" : "/data/dev",
> "writable" : true,
> "defaultInputFormat" : null
> },
> "hive" : {
> "location" : "/user/hive",
> "writable" : true,
> "defaultInputFormat" : null
> },
> "tmp" : {
> "location" : "/tmp",
> "writable" : true,
> "defaultInputFormat" : null
> }
> },
> "formats" : {
> "psv" : {
> "type" : "text",
> "extensions" : [ "tbl" ],
> "delimiter" : "|"
> },
> "csv" : {
> "type" : "text",
> "extensions" : [ "csv" ],
> "escape" : "`",
> "delimiter" : ","
> },
> "tsv" : {
> "type" : "text",
> "extensions" : [ "tsv" ],
> "delimiter" : "\t"
> },
> "parquet" : {
> "type" : "parquet"
> },
> "json" : {
> "type" : "json"
> },
> "maprdb" : {
> "type" : "maprdb"
> }
> }
> },
> "columns" : [ "`row_key`", "`raw`.`paste`" ],
> "cost" : 573950.0
> }, {
> "pop" : "project",
> "@id" : 65540,
> "exprs" : [ {
> "ref" : "`row_key`",
> "expr" : "`row_key`"
> }, {
> "ref" : "`ITEM`",
> "expr" : "`raw`.`paste`"
> } ],
> "child" : 65541,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 573950.0
> }, {
> "pop" : "filter",
> "@id" : 65539,
> "child" : 65540,
> "expr" : "equal(convert_fromutf8(`row_key`) , 'djHEHcPM') ",
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 86092.5
> }, {
> "pop" : "selection-vector-remover",
> "@id" : 65538,
> "child" : 65539,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 86092.5
> }, {
> "pop" : "project",
> "@id" : 65537,
> "exprs" : [ {
> "ref" : "`paste`",
> "expr" : "convert_fromutf8(`ITEM`) "
> } ],
> "child" : 65538,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 86092.5
> }, {
> "pop" : "union-exchange",
> "@id" : 1,
> "child" : 65537,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 86092.5
> }, {
> "pop" : "screen",
> "@id" : 0,
> "child" : 1,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 86092.5
> } ]
> } |
> +------+------+
> 1 row selected (0.42 seconds)
> EXPLAIN PLAN FOR select CONVERT_FROM(pastes.raw.paste, 'UTF8') AS paste
> FROM dfs.`pastes`.`/pastes` pastes where
> CONVERT_FROM(row_key, 'UTF8') = 'djHEHcPM';
> +------+------+
> | text | json |
> +------+------+
> | 00-00 Screen
> 00-01 Project(paste=[CONVERT_FROMUTF8($1)])
> 00-02 Project(row_key=[$1], ITEM=[ITEM($0, 'paste')])
> 00-03 Scan(groupscan=[MapRDBGroupScan [HBaseScanSpec=HBaseScanSpec
> [tableName=maprfs:///data/pastebiner/pastes, startRow=djHEHcPM,
> stopRow=djHEHcPM\x00, filter=RowFilter (EQUAL, djHEHcPM)],
> columns=[`row_key`, `raw`.`paste`]]])
> | {
> "head" : {
> "version" : 1,
> "generator" : {
> "type" : "ExplainHandler",
> "info" : ""
> },
> "type" : "APACHE_DRILL_PHYSICAL",
> "options" : [ ],
> "queue" : 0,
> "resultMode" : "EXEC"
> },
> "graph" : [ {
> "pop" : "maprdb-scan",
> "@id" : 3,
> "userName" : "darkness",
> "hbaseScanSpec" : {
> "tableName" : "maprfs:///data/pastebiner/pastes",
> "startRow" : "ZGpIRUhjUE0=",
> "stopRow" : "ZGpIRUhjUE0A",
> "serializedFilter" :
> "CihvcmcuYXBhY2hlLmhhZG9vcC5oYmFzZS5maWx0ZXIuUm93RmlsdGVyEkUKQwgCEj8KL29yZy5hcGFjaGUuaGFkb29wLmhiYXNlLmZpbHRlci5CaW5hcnlDb21wYXJhdG9yEgwKCgoIZGpIRUhjUE0="
> },
> "storage" : {
> "type" : "file",
> "enabled" : true,
> "connection" : "maprfs:///",
> "workspaces" : {
> "root" : {
> "location" : "/",
> "writable" : false,
> "defaultInputFormat" : null
> },
> "pastes" : {
> "location" : "/data/pastebiner",
> "writable" : true,
> "defaultInputFormat" : null
> },
> "dev" : {
> "location" : "/data/dev",
> "writable" : true,
> "defaultInputFormat" : null
> },
> "hive" : {
> "location" : "/user/hive",
> "writable" : true,
> "defaultInputFormat" : null
> },
> "tmp" : {
> "location" : "/tmp",
> "writable" : true,
> "defaultInputFormat" : null
> }
> },
> "formats" : {
> "psv" : {
> "type" : "text",
> "extensions" : [ "tbl" ],
> "delimiter" : "|"
> },
> "csv" : {
> "type" : "text",
> "extensions" : [ "csv" ],
> "escape" : "`",
> "delimiter" : ","
> },
> "tsv" : {
> "type" : "text",
> "extensions" : [ "tsv" ],
> "delimiter" : "\t"
> },
> "parquet" : {
> "type" : "parquet"
> },
> "json" : {
> "type" : "json"
> },
> "maprdb" : {
> "type" : "maprdb"
> }
> }
> },
> "columns" : [ "`row_key`", "`raw`.`paste`" ],
> "cost" : 286975.0
> }, {
> "pop" : "project",
> "@id" : 2,
> "exprs" : [ {
> "ref" : "`row_key`",
> "expr" : "`row_key`"
> }, {
> "ref" : "`ITEM`",
> "expr" : "`raw`.`paste`"
> } ],
> "child" : 3,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 286975.0
> }, {
> "pop" : "project",
> "@id" : 1,
> "exprs" : [ {
> "ref" : "`paste`",
> "expr" : "convert_fromutf8(`ITEM`) "
> } ],
> "child" : 2,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 286975.0
> }, {
> "pop" : "screen",
> "@id" : 0,
> "child" : 1,
> "initialAllocation" : 1000000,
> "maxAllocation" : 10000000000,
> "cost" : 286975.0
> } ]
> } |
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
