drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andries Engelbrecht (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-1622) Analytic tool generated SQL queries hang or fail
Date Mon, 03 Nov 2014 03:06:33 GMT

    [ https://issues.apache.org/jira/browse/DRILL-1622?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14194208#comment-14194208
] 

Andries Engelbrecht commented on DRILL-1622:
--------------------------------------------

Attached log and out files of the drill cluster for the hanging query. To make things easier
to read the cluster was stopped old files moved, and then cluster was started and query executed.



> Analytic tool generated SQL queries hang or fail
> ------------------------------------------------
>
>                 Key: DRILL-1622
>                 URL: https://issues.apache.org/jira/browse/DRILL-1622
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 0.6.0
>         Environment: CentOS, DFS, Hive, HBase
> MapR 3.1.1
>            Reporter: Andries Engelbrecht
>            Priority: Blocker
>
> Queries generated by Analytic tools can include unnecessary tables and joins. When tracing
ODBC calls and executing queries in sqlline the problems are still experienced.
> Example query that hangs:  dfs.views .orderview points to CSV files in MapR-FS and dfs.views.customerview
points to MapR-DB
> {code}
> SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust
where cust.cust_id=ord.cust_id group by ord.`month`
> {code}
> The following query fails:
> {code}
> SELECT ord.`month`, count(*) FROM dfs.views.orderview ord, dfs.views.customerview cust
group by ord.`month` 
> {code}
> {code}
> Query failed: Failure while parsing sql. Node [rel#25065:Subset#7.LOGICAL.ANY([]).[]]
could not be implemented; planner state:
> Output received below
> Root: rel#25065:Subset#7.LOGICAL.ANY([]).[]
> Original rel:
> AbstractConverter(subset=[rel#25065:Subset#7.LOGICAL.ANY([]).[]], convention=[LOGICAL],
DrillDistributionTraitDef=[ANY([])], sort=[[]]): rowcount = 1.7976931348623157E308, cumulative
cost = {inf}, id = 25066
>   AggregateRel(subset=[rel#25064:Subset#7.NONE.ANY([]).[]], group=[{0}], EXPR$1=[COUNT()]):
rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307 rows, 0.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 25063
>     ProjectRel(subset=[rel#25062:Subset#6.NONE.ANY([]).[]], month=[$1]): rowcount = 1.7976931348623157E308,
cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu, 0.0 io, 0.0 network,
0.0 memory}, id = 25061
>       JoinRel(subset=[rel#25060:Subset#5.NONE.ANY([]).[]], condition=[true], joinType=[inner]):
rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 0.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 25059
>         ProjectRel(subset=[rel#25053:Subset#1.NONE.ANY([]).[]], order_id=[CAST(ITEM($1,
0)):BIGINT NOT NULL], month=[CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL], purch_date=[CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL],
cust_id=[CAST(ITEM($1, 3)):BIGINT NOT NULL], state=[CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER
SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], prod_id=[CAST(ITEM($1, 5)):BIGINT
NOT NULL], order_total=[CAST(ITEM($1, 6)):INTEGER NOT NULL]): rowcount = 100.0, cumulative
cost = {100.0 rows, 700.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25052
>           EnumerableTableAccessRel(subset=[rel#25051:Subset#0.ENUMERABLE.ANY([]).[]],
table=[[dfs, data, /orders]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 25027
>         ProjectRel(subset=[rel#25058:Subset#4.NONE.ANY([]).[]], cust_id=[$0], cust_name=[CAST($1):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], cust_age=[CAST($2):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], agg_rev=[CAST($3):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], membership=[CAST($4):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL], address=[CAST($5):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL]): rowcount = 1.7976931348623157E308,
cumulative cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network, 0.0 memory},
id = 25057
>           ProjectRel(subset=[rel#25056:Subset#3.NONE.ANY([]).[]], cust_id=[CAST($0):BIGINT
NOT NULL], cust_name=[CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary"], cust_age=[CAST(ITEM($3, 'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1"
COLLATE "ISO-8859-1$en_US$primary"], agg_rev=[CAST(ITEM($2, 'agg_rev')):VARCHAR(20) CHARACTER
SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], membership=[CAST(ITEM($2, 'membership')):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], address=[CAST(ITEM($1, 'state')):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"]): rowcount = 100.0, cumulative
cost = {100.0 rows, 600.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 25055
>             EnumerableTableAccessRel(subset=[rel#25054:Subset#2.ENUMERABLE.ANY([]).[]],
table=[[maprdb, customers]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 25031
> Sets:
> Set#0, type: (DrillRecordRow[*, columns])
> 	rel#25051:Subset#0.ENUMERABLE.ANY([]).[], best=rel#25027, importance=0.5904900000000001
> 		rel#25027:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[dfs, data, /orders]),
rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25117:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=6456.0, cumulative cost={inf}
> 	rel#25116:Subset#0.LOGICAL.ANY([]).[], best=rel#25137, importance=0.531441
> 		rel#25118:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=100.0, cumulative cost={inf}
> 		rel#25137:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan
[selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`*`]]]),
rowcount=6456.0, cumulative cost={6456.0 rows, 6.456E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#1, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT
cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total)
> 	rel#25053:Subset#1.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25052:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],order_id=CAST(ITEM($1,
0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1,
3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1,
6)):INTEGER NOT NULL), rowcount=100.0, cumulative cost={inf}
> 	rel#25114:Subset#1.LOGICAL.ANY([]).[], best=rel#25113, importance=0.6561
> 		rel#25115:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25113:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25112:Subset#14.LOGICAL.ANY([]).[],order_id=CAST(ITEM($0,
0)):BIGINT NOT NULL,month=CAST(ITEM($0, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($0, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($0,
3)):BIGINT NOT NULL,state=CAST(ITEM($0, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($0, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($0,
6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6484.0 cpu, 0.0 io,
0.0 network, 0.0 memory}
> 		rel#25136:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],order_id=CAST(ITEM($1,
0)):BIGINT NOT NULL,month=CAST(ITEM($1, 1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL,purch_date=CAST(ITEM($1, 2)):TIMESTAMP(0) NOT NULL,cust_id=CAST(ITEM($1,
3)):BIGINT NOT NULL,state=CAST(ITEM($1, 4)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE
"ISO-8859-1$en_US$primary" NOT NULL,prod_id=CAST(ITEM($1, 5)):BIGINT NOT NULL,order_total=CAST(ITEM($1,
6)):INTEGER NOT NULL), rowcount=6456.0, cumulative cost={12912.0 rows, 6.4560028E7 cpu, 0.0
io, 0.0 network, 0.0 memory}
> Set#2, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP address, (VARCHAR(1), ANY)
MAP loyalty, (VARCHAR(1), ANY) MAP personal)
> 	rel#25054:Subset#2.ENUMERABLE.ANY([]).[], best=rel#25031, importance=0.531441
> 		rel#25031:EnumerableTableAccessRel.ENUMERABLE.ANY([]).[](table=[maprdb, customers]),
rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25121:AbstractConverter.ENUMERABLE.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],convention=ENUMERABLE,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1048576.0, cumulative cost={inf}
> 	rel#25120:Subset#2.LOGICAL.ANY([]).[], best=rel#25138, importance=0.4782969000000001
> 		rel#25122:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=100.0, cumulative cost={inf}
> 		rel#25138:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan
[HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null],
columns=[SchemaPath [`*`]]]), rowcount=1048576.0, cumulative cost={1048576.0 Error: exception
while executing query: Failure while trying to get next result batch. (state=,code=0)
> rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#3, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age,
VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
> 	rel#25056:Subset#3.NONE.ANY([]).[], best=null, importance=0.5904900000000001
> 		rel#25055:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3,
'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2,
'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2,
'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1,
'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=100.0,
cumulative cost={inf}
> 	rel#25108:Subset#3.LOGICAL.ANY([]).[], best=rel#25107, importance=0.6561
> 		rel#25109:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25107:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL,cust_name=CAST(ITEM($1, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($1,
'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2,
'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2,
'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($3,
'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0,
cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25135:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL,cust_name=CAST(ITEM($3, 'name')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",cust_age=CAST(ITEM($3,
'age')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",agg_rev=CAST(ITEM($2,
'agg_rev')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",membership=CAST(ITEM($2,
'membership')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary",address=CAST(ITEM($1,
'state')):VARCHAR(20) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), rowcount=1048576.0,
cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#4, type: RecordType(BIGINT cust_id, VARCHAR(20) cust_name, VARCHAR(20) cust_age,
VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20) address)
> 	rel#25058:Subset#4.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25057:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1.7976931348623157E308,
cumulative cost={inf}
> 		rel#25102:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):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,cust_age=CAST(CAST(ITEM($3, 'age')):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,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):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,membership=CAST(CAST(ITEM($2, 'membership')):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,address=CAST(CAST(ITEM($1, 'state')):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), rowcount=100.0, cumulative cost={inf}
> 	rel#25125:Subset#4.LOGICAL.ANY([]).[], best=rel#25131, importance=0.6561
> 		rel#25126:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25131:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25106:Subset#13.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL,cust_name=CAST(CAST(ITEM($1, 'name')):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,cust_age=CAST(CAST(ITEM($1, 'age')):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,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):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,membership=CAST(CAST(ITEM($2, 'membership')):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,address=CAST(CAST(ITEM($3, 'state')):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), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0
network, 0.0 memory}
> 		rel#25132:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL,cust_name=CAST(CAST(ITEM($3, 'name')):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,cust_age=CAST(CAST(ITEM($3, 'age')):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,agg_rev=CAST(CAST(ITEM($2, 'agg_rev')):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,membership=CAST(CAST(ITEM($2, 'membership')):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,address=CAST(CAST(ITEM($1, 'state')):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), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194328.0 cpu, 0.0 io, 0.0
network, 0.0 memory}
> 		rel#25134:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0,cust_name=CAST($1):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,cust_age=CAST($2):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,agg_rev=CAST($3):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,membership=CAST($4):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL,address=CAST($5):VARCHAR(20)
CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=1048576.0,
cumulative cost={3145728.0 rows, 4194352.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#5, type: RecordType(BIGINT order_id, VARCHAR(1) month, TIMESTAMP(0) purch_date, BIGINT
cust_id, VARCHAR(1) state, BIGINT prod_id, INTEGER order_total, BIGINT cust_id0, VARCHAR(20)
cust_name, VARCHAR(20) cust_age, VARCHAR(20) agg_rev, VARCHAR(20) membership, VARCHAR(20)
address)
> 	rel#25060:Subset#5.NONE.ANY([]).[], best=null, importance=0.7290000000000001
> 		rel#25059:JoinRel.NONE.ANY([]).[](left=rel#25053:Subset#1.NONE.ANY([]).[],right=rel#25058:Subset#4.NONE.ANY([]).[],condition=true,joinType=inner),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#25088:Subset#5.LOGICAL.ANY([]).[], best=null, importance=0.81
> 		rel#25089:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25133:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25114:Subset#1.LOGICAL.ANY([]).[],right=rel#25125:Subset#4.LOGICAL.ANY([]).[],condition=true,joinType=inner),
rowcount=1048576.0, cumulative cost={inf}
> Set#6, type: RecordType(VARCHAR(1) month)
> 	rel#25062:Subset#6.NONE.ANY([]).[], best=null, importance=0.81
> 		rel#25061:ProjectRel.NONE.ANY([]).[](child=rel#25060:Subset#5.NONE.ANY([]).[],month=$1),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25078:ProjectRel.NONE.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],month=$0),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#25067:Subset#6.LOGICAL.ANY([]).[], best=null, importance=0.9
> 		rel#25068:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25081:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25079:Subset#10.LOGICAL.ANY([]).[],month=$0),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25090:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25088:Subset#5.LOGICAL.ANY([]).[],month=$1),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> Set#7, type: RecordType(VARCHAR(1) month, BIGINT EXPR$1)
> 	rel#25064:Subset#7.NONE.ANY([]).[], best=null, importance=0.9
> 		rel#25063:AggregateRel.NONE.ANY([]).[](child=rel#25062:Subset#6.NONE.ANY([]).[],group={0},EXPR$1=COUNT()),
rowcount=1.7976931348623158E307, cumulative cost={inf}
> 	rel#25065:Subset#7.LOGICAL.ANY([]).[], best=null, importance=1.0
> 		rel#25066:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25064:Subset#7.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25069:DrillAggregateRel.LOGICAL.ANY([]).[](child=rel#25067:Subset#6.LOGICAL.ANY([]).[],group={0},EXPR$1=COUNT()),
rowcount=1.7976931348623158E307, cumulative cost={inf}
> Set#8, type: RecordType(VARCHAR(1) month)
> 	rel#25074:Subset#8.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25070:ProjectRel.NONE.ANY([]).[](child=rel#25053:Subset#1.NONE.ANY([]).[],month=$1),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25097:ProjectRel.NONE.ANY([]).[](child=rel#25051:Subset#0.ENUMERABLE.ANY([]).[],month=CAST(ITEM($1,
1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=100.0,
cumulative cost={inf}
> 	rel#25083:Subset#8.LOGICAL.ANY([]).[], best=rel#25101, importance=0.7290000000000001
> 		rel#25084:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25074:Subset#8.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25101:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25100:Subset#12.LOGICAL.ANY([]).[],month=CAST(ITEM($0,
1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0,
cumulative cost={12912.0 rows, 6460.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25119:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25116:Subset#0.LOGICAL.ANY([]).[],month=CAST(ITEM($1,
1)):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary" NOT NULL), rowcount=6456.0,
cumulative cost={12912.0 rows, 6.4560004E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
> 		rel#25128:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25114:Subset#1.LOGICAL.ANY([]).[],month=$1),
rowcount=6456.0, cumulative cost={19368.0 rows, 6488.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#9, type: RecordType(BIGINT cust_id)
> 	rel#25075:Subset#9.NONE.ANY([]).[], best=null, importance=0.6561
> 		rel#25071:ProjectRel.NONE.ANY([]).[](child=rel#25058:Subset#4.NONE.ANY([]).[],cust_id=$0),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25091:ProjectRel.NONE.ANY([]).[](child=rel#25056:Subset#3.NONE.ANY([]).[],cust_id=$0),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25092:ProjectRel.NONE.ANY([]).[](child=rel#25054:Subset#2.ENUMERABLE.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL), rowcount=100.0, cumulative cost={inf}
> 	rel#25085:Subset#9.LOGICAL.ANY([]).[], best=rel#25096, importance=0.7290000000000001
> 		rel#25086:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25075:Subset#9.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25096:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25095:Subset#11.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 1048580.0 cpu, 0.0 io, 0.0
network, 0.0 memory}
> 		rel#25123:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25120:Subset#2.LOGICAL.ANY([]).[],cust_id=CAST($0):BIGINT
NOT NULL), rowcount=1048576.0, cumulative cost={2097152.0 rows, 4194308.0 cpu, 0.0 io, 0.0
network, 0.0 memory}
> 		rel#25124:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25108:Subset#3.LOGICAL.ANY([]).[],cust_id=$0),
rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0
memory}
> 		rel#25127:DrillProjectRel.LOGICAL.ANY([]).[](child=rel#25125:Subset#4.LOGICAL.ANY([]).[],cust_id=$0),
rowcount=1048576.0, cumulative cost={3145728.0 rows, 4194332.0 cpu, 0.0 io, 0.0 network, 0.0
memory}
> Set#10, type: RecordType(VARCHAR(1) month, BIGINT cust_id)
> 	rel#25077:Subset#10.NONE.ANY([]).[], best=null, importance=0.7290000000000001
> 		rel#25076:JoinRel.NONE.ANY([]).[](left=rel#25074:Subset#8.NONE.ANY([]).[],right=rel#25075:Subset#9.NONE.ANY([]).[],condition=true,joinType=inner),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 	rel#25079:Subset#10.LOGICAL.ANY([]).[], best=null, importance=0.81
> 		rel#25080:AbstractConverter.LOGICAL.ANY([]).[](child=rel#25077:Subset#10.NONE.ANY([]).[],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[]),
rowcount=1.7976931348623157E308, cumulative cost={inf}
> 		rel#25087:DrillJoinRel.LOGICAL.ANY([]).[](left=rel#25083:Subset#8.LOGICAL.ANY([]).[],right=rel#25085:Subset#9.LOGICAL.ANY([]).[],condition=true,joinType=inner),
rowcount=1048576.0, cumulative cost={inf}
> Set#11, type: RecordType(ANY row_key)
> 	rel#25095:Subset#11.LOGICAL.ANY([]).[], best=rel#25093, importance=0.6561
> 		rel#25093:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan
[HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null],
columns=[SchemaPath [`row_key`]]]), rowcount=1048576.0, cumulative cost={1048576.0 rows, 1048576.0
cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#12, type: RecordType(ANY columns)
> 	rel#25100:Subset#12.LOGICAL.ANY([]).[], best=rel#25098, importance=0.6561
> 		rel#25098:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan
[selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[1]]]]),
rowcount=6456.0, cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
> Set#13, type: RecordType(ANY row_key, (VARCHAR(1), ANY) MAP personal, (VARCHAR(1), ANY)
MAP loyalty, (VARCHAR(1), ANY) MAP address)
> 	rel#25106:Subset#13.LOGICAL.ANY([]).[], best=rel#25104, importance=0.5904900000000001
> 		rel#25104:DrillScanRel.LOGICAL.ANY([]).[](table=[maprdb, customers],groupscan=HBaseGroupScan
[HBaseScanSpec=HBaseScanSpec [tableName=customers, startRow=null, stopRow=null, filter=null],
columns=[SchemaPath [`row_key`], SchemaPath [`personal`.`name`], SchemaPath [`personal`.`age`],
SchemaPath [`loyalty`.`agg_rev`], SchemaPath [`loyalty`.`membership`], SchemaPath [`address`.`state`]]]),
rowcount=1048576.0, cumulative cost={1048576.0 rows, 4194304.0 cpu, 0.0 io, 0.0 network, 0.0
memory}
> Set#14, type: RecordType(ANY columns)
> 	rel#25112:Subset#14.LOGICAL.ANY([]).[], best=rel#25110, importance=0.5904900000000001
> 		rel#25110:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, data, /orders],groupscan=EasyGroupScan
[selectionRoot=/mapr/c1/andries/demo/data/orders, numFiles=10, columns = [SchemaPath [`columns`[0]],
SchemaPath [`columns`[1]], SchemaPath [`columns`[2]], SchemaPath [`columns`[3]], SchemaPath
[`columns`[4]], SchemaPath [`columns`[5]], SchemaPath [`columns`[6]]]]), rowcount=6456.0,
cumulative cost={6456.0 rows, 6456.0 cpu, 0.0 io, 0.0 network, 0.0 memory}
>  [8224f29c-1824-45d9-a74b-3d31e48a3419]
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message