You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Volodymyr Vysotskyi (JIRA)" <ji...@apache.org> on 2018/01/04 14:57:00 UTC
[jira] [Created] (DRILL-6069) Hash agg operator requires large
memory amount when planner.width.max_per_node is large
Volodymyr Vysotskyi created DRILL-6069:
------------------------------------------
Summary: Hash agg operator requires large memory amount when planner.width.max_per_node is large
Key: DRILL-6069
URL: https://issues.apache.org/jira/browse/DRILL-6069
Project: Apache Drill
Issue Type: Bug
Reporter: Volodymyr Vysotskyi
Queries, whose plan contains few HashAgg operators, requires large memory amount when planner.width.max_per_node is large.
It may be observed using this physical plan:
{code:xml}
{
"head" : {
"version" : 1,
"generator" : {
"type" : "DefaultSqlHandler",
"info" : ""
},
"type" : "APACHE_DRILL_PHYSICAL",
"options" : [ {
"kind" : "BOOLEAN",
"accessibleScopes" : "ALL",
"name" : "planner.enable_decimal_data_type",
"bool_val" : true,
"scope" : "SESSION"
}, {
"kind" : "LONG",
"accessibleScopes" : "ALL",
"name" : "exec.hashagg.min_batches_per_partition",
"num_val" : 1,
"scope" : "SESSION"
}, {
"kind" : "LONG",
"accessibleScopes" : "ALL",
"name" : "planner.width.max_per_node",
"num_val" : 2,
"scope" : "SESSION"
}, {
"kind" : "BOOLEAN",
"accessibleScopes" : "ALL",
"name" : "exec.errors.verbose",
"bool_val" : true,
"scope" : "SESSION"
} ],
"queue" : 0,
"hasResourcePlan" : false,
"resultMode" : "EXEC"
},
"graph" : [ {
"pop" : "parquet-scan",
"@id" : 131093,
"entries" : [ {
"path" : "file:/tmp/parquet/ship_mode"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`sm_ship_mode_sk`", "`sm_carrier`" ],
"selectionRoot" : "file:/tmp/parquet/ship_mode",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/ship_mode/0_0_0.parquet" ],
"cost" : 20.0
}, {
"pop" : "filter",
"@id" : 131090,
"child" : 131093,
"expr" : "booleanOr(equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZOUROS') , equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZHOU') ) ",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "selection-vector-remover",
"@id" : 131088,
"child" : 131090,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "project",
"@id" : 131086,
"exprs" : [ {
"ref" : "`sm_ship_mode_sk`",
"expr" : "cast( (`sm_ship_mode_sk` ) as INT )"
} ],
"child" : 131088,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "parquet-scan",
"@id" : 131107,
"entries" : [ {
"path" : "file:/tmp/parquet/date_dim"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`d_date_sk`", "`d_year`", "`d_moy`" ],
"selectionRoot" : "file:/tmp/parquet/date_dim",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/date_dim/0_0_0.parquet" ],
"cost" : 73049.0
}, {
"pop" : "filter",
"@id" : 131106,
"child" : 131107,
"expr" : "equal(cast( (`d_year` ) as INT ), 1998) ",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 10957.35
}, {
"pop" : "selection-vector-remover",
"@id" : 131104,
"child" : 131106,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 10957.35
}, {
"pop" : "project",
"@id" : 131102,
"exprs" : [ {
"ref" : "`d_date_sk`",
"expr" : "cast( (`d_date_sk` ) as INT )"
}, {
"ref" : "`d_year`",
"expr" : "cast( (`d_year` ) as INT )"
}, {
"ref" : "`d_moy`",
"expr" : "cast( (`d_moy` ) as INT )"
} ],
"child" : 131104,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 10957.35
}, {
"pop" : "parquet-scan",
"@id" : 524292,
"entries" : [ {
"path" : "file:/tmp/parquet/ship_mode"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`sm_ship_mode_sk`", "`sm_carrier`" ],
"selectionRoot" : "file:/tmp/parquet/ship_mode",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/ship_mode/0_0_0.parquet" ],
"cost" : 20.0
}, {
"pop" : "filter",
"@id" : 524291,
"child" : 524292,
"expr" : "booleanOr(equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZOUROS') , equal(cast( (`sm_carrier` ) as VARCHAR(200) ), 'ZHOU') ) ",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "selection-vector-remover",
"@id" : 524290,
"child" : 524291,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "project",
"@id" : 524289,
"exprs" : [ {
"ref" : "`sm_ship_mode_sk`",
"expr" : "cast( (`sm_ship_mode_sk` ) as INT )"
} ],
"child" : 524290,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "broadcast-exchange",
"@id" : 262149,
"child" : 524289,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "parquet-scan",
"@id" : 131101,
"entries" : [ {
"path" : "file:/tmp/parquet/warehouse"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`w_warehouse_sk`", "`w_warehouse_name`", "`w_warehouse_sq_ft`", "`w_city`", "`w_county`", "`w_state`", "`w_country`" ],
"selectionRoot" : "file:/tmp/parquet/warehouse",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/warehouse/0_0_0.parquet" ],
"cost" : 5.0
}, {
"pop" : "project",
"@id" : 131099,
"exprs" : [ {
"ref" : "`w_warehouse_sk`",
"expr" : "cast( (`w_warehouse_sk` ) as INT )"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "cast( (`w_warehouse_name` ) as VARCHAR(200) )"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "cast( (`w_warehouse_sq_ft` ) as INT )"
}, {
"ref" : "`w_city`",
"expr" : "cast( (`w_city` ) as VARCHAR(200) )"
}, {
"ref" : "`w_county`",
"expr" : "cast( (`w_county` ) as VARCHAR(200) )"
}, {
"ref" : "`w_state`",
"expr" : "cast( (`w_state` ) as VARCHAR(200) )"
}, {
"ref" : "`w_country`",
"expr" : "cast( (`w_country` ) as VARCHAR(200) )"
} ],
"child" : 131101,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "parquet-scan",
"@id" : 458756,
"entries" : [ {
"path" : "file:/tmp/parquet/time_dim"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`t_time_sk`", "`t_time`" ],
"selectionRoot" : "file:/tmp/parquet/time_dim",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/time_dim/0_0_0.parquet" ],
"cost" : 86400.0
}, {
"pop" : "filter",
"@id" : 458755,
"child" : 458756,
"expr" : "booleanAnd(greater_than_or_equal_to(cast( (`t_time` ) as INT ), 7249) , less_than_or_equal_to(cast( (`t_time` ) as INT ), 36049) ) ",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21600.0
}, {
"pop" : "selection-vector-remover",
"@id" : 458754,
"child" : 458755,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21600.0
}, {
"pop" : "project",
"@id" : 458753,
"exprs" : [ {
"ref" : "`t_time_sk`",
"expr" : "cast( (`t_time_sk` ) as INT )"
} ],
"child" : 458754,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21600.0
}, {
"pop" : "broadcast-exchange",
"@id" : 262152,
"child" : 458753,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21600.0
}, {
"pop" : "parquet-scan",
"@id" : 131098,
"entries" : [ {
"path" : "file:/tmp/parquet/time_dim"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`t_time_sk`", "`t_time`" ],
"selectionRoot" : "file:/tmp/parquet/time_dim",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/time_dim/0_0_0.parquet" ],
"cost" : 86400.0
}, {
"pop" : "filter",
"@id" : 131096,
"child" : 131098,
"expr" : "booleanAnd(greater_than_or_equal_to(cast( (`t_time` ) as INT ), 7249) , less_than_or_equal_to(cast( (`t_time` ) as INT ), 36049) ) ",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21600.0
}, {
"pop" : "selection-vector-remover",
"@id" : 131094,
"child" : 131096,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21600.0
}, {
"pop" : "project",
"@id" : 131091,
"exprs" : [ {
"ref" : "`t_time_sk`",
"expr" : "cast( (`t_time_sk` ) as INT )"
} ],
"child" : 131094,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21600.0
}, {
"pop" : "parquet-scan",
"@id" : 262160,
"entries" : [ {
"path" : "file:/tmp/parquet/catalog_sales"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`cs_sold_date_sk`", "`cs_sold_time_sk`", "`cs_ship_mode_sk`", "`cs_warehouse_sk`", "`cs_quantity`", "`cs_ext_sales_price`", "`cs_net_paid`" ],
"selectionRoot" : "file:/tmp/parquet/catalog_sales",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/catalog_sales/1_1_0.parquet", "/tmp/parquet/catalog_sales/1_0_0.parquet" ],
"cost" : 1441548.0
}, {
"pop" : "project",
"@id" : 262159,
"exprs" : [ {
"ref" : "`cs_sold_date_sk`",
"expr" : "cast( (`cs_sold_date_sk` ) as INT )"
}, {
"ref" : "`cs_sold_time_sk`",
"expr" : "cast( (`cs_sold_time_sk` ) as INT )"
}, {
"ref" : "`cs_ship_mode_sk`",
"expr" : "cast( (`cs_ship_mode_sk` ) as INT )"
}, {
"ref" : "`cs_warehouse_sk`",
"expr" : "cast( (`cs_warehouse_sk` ) as INT )"
}, {
"ref" : "`cs_quantity`",
"expr" : "cast( (`cs_quantity` ) as INT )"
}, {
"ref" : "`cs_ext_sales_price`",
"expr" : "cast( (`cs_ext_sales_price` ) as FLOAT8 )"
}, {
"ref" : "`cs_net_paid`",
"expr" : "cast( (`cs_net_paid` ) as FLOAT8 )"
} ],
"child" : 262160,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "parquet-scan",
"@id" : 327684,
"entries" : [ {
"path" : "file:/tmp/parquet/date_dim"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`d_date_sk`", "`d_year`", "`d_moy`" ],
"selectionRoot" : "file:/tmp/parquet/date_dim",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/date_dim/0_0_0.parquet" ],
"cost" : 73049.0
}, {
"pop" : "filter",
"@id" : 327683,
"child" : 327684,
"expr" : "equal(cast( (`d_year` ) as INT ), 1998) ",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 10957.35
}, {
"pop" : "selection-vector-remover",
"@id" : 327682,
"child" : 327683,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 10957.35
}, {
"pop" : "project",
"@id" : 327681,
"exprs" : [ {
"ref" : "`d_date_sk`",
"expr" : "cast( (`d_date_sk` ) as INT )"
}, {
"ref" : "`d_year`",
"expr" : "cast( (`d_year` ) as INT )"
}, {
"ref" : "`d_moy`",
"expr" : "cast( (`d_moy` ) as INT )"
} ],
"child" : 327682,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 10957.35
}, {
"pop" : "broadcast-exchange",
"@id" : 262158,
"child" : 327681,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 10957.35
}, {
"pop" : "hash-join",
"@id" : 262157,
"left" : 262159,
"right" : 262158,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`cs_sold_date_sk`",
"right" : "`d_date_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "parquet-scan",
"@id" : 131105,
"entries" : [ {
"path" : "file:/tmp/parquet/web_sales"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`ws_sold_date_sk`", "`ws_sold_time_sk`", "`ws_ship_mode_sk`", "`ws_warehouse_sk`", "`ws_quantity`", "`ws_ext_sales_price`", "`ws_net_paid_inc_ship`" ],
"selectionRoot" : "file:/tmp/parquet/web_sales",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/web_sales/0_0_0.parquet" ],
"cost" : 719384.0
}, {
"pop" : "project",
"@id" : 131103,
"exprs" : [ {
"ref" : "`ws_sold_date_sk`",
"expr" : "cast( (`ws_sold_date_sk` ) as INT )"
}, {
"ref" : "`ws_sold_time_sk`",
"expr" : "cast( (`ws_sold_time_sk` ) as INT )"
}, {
"ref" : "`ws_ship_mode_sk`",
"expr" : "cast( (`ws_ship_mode_sk` ) as INT )"
}, {
"ref" : "`ws_warehouse_sk`",
"expr" : "cast( (`ws_warehouse_sk` ) as INT )"
}, {
"ref" : "`ws_quantity`",
"expr" : "cast( (`ws_quantity` ) as INT )"
}, {
"ref" : "`ws_ext_sales_price`",
"expr" : "cast( (`ws_ext_sales_price` ) as FLOAT8 )"
}, {
"ref" : "`ws_net_paid_inc_ship`",
"expr" : "cast( (`ws_net_paid_inc_ship` ) as FLOAT8 )"
} ],
"child" : 131105,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "hash-join",
"@id" : 131100,
"left" : 131103,
"right" : 131102,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`ws_sold_date_sk`",
"right" : "`d_date_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "hash-join",
"@id" : 131097,
"left" : 131100,
"right" : 131099,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`ws_warehouse_sk`",
"right" : "`w_warehouse_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "project",
"@id" : 131095,
"exprs" : [ {
"ref" : "`ws_sold_date_sk`",
"expr" : "`ws_sold_date_sk`"
}, {
"ref" : "`ws_sold_time_sk`",
"expr" : "`ws_sold_time_sk`"
}, {
"ref" : "`ws_ship_mode_sk`",
"expr" : "`ws_ship_mode_sk`"
}, {
"ref" : "`ws_warehouse_sk`",
"expr" : "`ws_warehouse_sk`"
}, {
"ref" : "`ws_quantity`",
"expr" : "`ws_quantity`"
}, {
"ref" : "`ws_ext_sales_price`",
"expr" : "`ws_ext_sales_price`"
}, {
"ref" : "`ws_net_paid_inc_ship`",
"expr" : "`ws_net_paid_inc_ship`"
}, {
"ref" : "`w_warehouse_sk`",
"expr" : "`w_warehouse_sk`"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`d_date_sk`",
"expr" : "`d_date_sk`"
}, {
"ref" : "`d_year`",
"expr" : "`d_year`"
}, {
"ref" : "`d_moy`",
"expr" : "`d_moy`"
} ],
"child" : 131097,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "project",
"@id" : 131092,
"exprs" : [ {
"ref" : "`ws_sold_time_sk`",
"expr" : "`ws_sold_time_sk`"
}, {
"ref" : "`ws_ship_mode_sk`",
"expr" : "`ws_ship_mode_sk`"
}, {
"ref" : "`ws_quantity`",
"expr" : "`ws_quantity`"
}, {
"ref" : "`ws_ext_sales_price`",
"expr" : "`ws_ext_sales_price`"
}, {
"ref" : "`ws_net_paid_inc_ship`",
"expr" : "`ws_net_paid_inc_ship`"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`d_year`",
"expr" : "`d_year`"
}, {
"ref" : "`d_moy`",
"expr" : "`d_moy`"
} ],
"child" : 131095,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "hash-join",
"@id" : 131089,
"left" : 131092,
"right" : 131091,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`ws_sold_time_sk`",
"right" : "`t_time_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "project",
"@id" : 131087,
"exprs" : [ {
"ref" : "`ws_ship_mode_sk`",
"expr" : "`ws_ship_mode_sk`"
}, {
"ref" : "`ws_quantity`",
"expr" : "`ws_quantity`"
}, {
"ref" : "`ws_ext_sales_price`",
"expr" : "`ws_ext_sales_price`"
}, {
"ref" : "`ws_net_paid_inc_ship`",
"expr" : "`ws_net_paid_inc_ship`"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`d_year`",
"expr" : "`d_year`"
}, {
"ref" : "`d_moy`",
"expr" : "`d_moy`"
} ],
"child" : 131089,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "hash-join",
"@id" : 131085,
"left" : 131087,
"right" : 131086,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`ws_ship_mode_sk`",
"right" : "`sm_ship_mode_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "project",
"@id" : 131084,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`d_year`"
}, {
"ref" : "`$f7`",
"expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f8`",
"expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f9`",
"expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f10`",
"expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f11`",
"expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f12`",
"expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f13`",
"expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f14`",
"expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f15`",
"expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f16`",
"expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f17`",
"expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f18`",
"expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`ws_ext_sales_price`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f19`",
"expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f20`",
"expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f21`",
"expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f22`",
"expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f23`",
"expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f24`",
"expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f25`",
"expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f26`",
"expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f27`",
"expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f28`",
"expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f29`",
"expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f30`",
"expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`ws_net_paid_inc_ship`, `ws_quantity`) ) else (0 ) end ) "
} ],
"child" : 131085,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 719384.0
}, {
"pop" : "hash-aggregate",
"@id" : 131082,
"child" : 131084,
"cardinality" : 1.0,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"aggPhase" : "PHASE_1of2",
"groupByExprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
} ],
"aggrExprs" : [ {
"ref" : "`jan_sales`",
"expr" : "sum(`$f7`) "
}, {
"ref" : "`feb_sales`",
"expr" : "sum(`$f8`) "
}, {
"ref" : "`mar_sales`",
"expr" : "sum(`$f9`) "
}, {
"ref" : "`apr_sales`",
"expr" : "sum(`$f10`) "
}, {
"ref" : "`may_sales`",
"expr" : "sum(`$f11`) "
}, {
"ref" : "`jun_sales`",
"expr" : "sum(`$f12`) "
}, {
"ref" : "`jul_sales`",
"expr" : "sum(`$f13`) "
}, {
"ref" : "`aug_sales`",
"expr" : "sum(`$f14`) "
}, {
"ref" : "`sep_sales`",
"expr" : "sum(`$f15`) "
}, {
"ref" : "`oct_sales`",
"expr" : "sum(`$f16`) "
}, {
"ref" : "`nov_sales`",
"expr" : "sum(`$f17`) "
}, {
"ref" : "`dec_sales`",
"expr" : "sum(`$f18`) "
}, {
"ref" : "`jan_net`",
"expr" : "sum(`$f19`) "
}, {
"ref" : "`feb_net`",
"expr" : "sum(`$f20`) "
}, {
"ref" : "`mar_net`",
"expr" : "sum(`$f21`) "
}, {
"ref" : "`apr_net`",
"expr" : "sum(`$f22`) "
}, {
"ref" : "`may_net`",
"expr" : "sum(`$f23`) "
}, {
"ref" : "`jun_net`",
"expr" : "sum(`$f24`) "
}, {
"ref" : "`jul_net`",
"expr" : "sum(`$f25`) "
}, {
"ref" : "`aug_net`",
"expr" : "sum(`$f26`) "
}, {
"ref" : "`sep_net`",
"expr" : "sum(`$f27`) "
}, {
"ref" : "`oct_net`",
"expr" : "sum(`$f28`) "
}, {
"ref" : "`nov_net`",
"expr" : "sum(`$f29`) "
}, {
"ref" : "`dec_net`",
"expr" : "sum(`$f30`) "
} ],
"cost" : 713763.8125
}, {
"pop" : "hash-aggregate",
"@id" : 131080,
"child" : 131082,
"cardinality" : 1.0,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"aggPhase" : "PHASE_2of2",
"groupByExprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
} ],
"aggrExprs" : [ {
"ref" : "`jan_sales`",
"expr" : "sum(`jan_sales`) "
}, {
"ref" : "`feb_sales`",
"expr" : "sum(`feb_sales`) "
}, {
"ref" : "`mar_sales`",
"expr" : "sum(`mar_sales`) "
}, {
"ref" : "`apr_sales`",
"expr" : "sum(`apr_sales`) "
}, {
"ref" : "`may_sales`",
"expr" : "sum(`may_sales`) "
}, {
"ref" : "`jun_sales`",
"expr" : "sum(`jun_sales`) "
}, {
"ref" : "`jul_sales`",
"expr" : "sum(`jul_sales`) "
}, {
"ref" : "`aug_sales`",
"expr" : "sum(`aug_sales`) "
}, {
"ref" : "`sep_sales`",
"expr" : "sum(`sep_sales`) "
}, {
"ref" : "`oct_sales`",
"expr" : "sum(`oct_sales`) "
}, {
"ref" : "`nov_sales`",
"expr" : "sum(`nov_sales`) "
}, {
"ref" : "`dec_sales`",
"expr" : "sum(`dec_sales`) "
}, {
"ref" : "`jan_net`",
"expr" : "sum(`jan_net`) "
}, {
"ref" : "`feb_net`",
"expr" : "sum(`feb_net`) "
}, {
"ref" : "`mar_net`",
"expr" : "sum(`mar_net`) "
}, {
"ref" : "`apr_net`",
"expr" : "sum(`apr_net`) "
}, {
"ref" : "`may_net`",
"expr" : "sum(`may_net`) "
}, {
"ref" : "`jun_net`",
"expr" : "sum(`jun_net`) "
}, {
"ref" : "`jul_net`",
"expr" : "sum(`jul_net`) "
}, {
"ref" : "`aug_net`",
"expr" : "sum(`aug_net`) "
}, {
"ref" : "`sep_net`",
"expr" : "sum(`sep_net`) "
}, {
"ref" : "`oct_net`",
"expr" : "sum(`oct_net`) "
}, {
"ref" : "`nov_net`",
"expr" : "sum(`nov_net`) "
}, {
"ref" : "`dec_net`",
"expr" : "sum(`dec_net`) "
} ],
"cost" : 71376.38124999999
}, {
"pop" : "project",
"@id" : 131078,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "concatOperator(concatOperator('ZOUROS', ',') , 'ZHOU') "
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
} ],
"child" : 131080,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 7193.839999999999
}, {
"pop" : "parquet-scan",
"@id" : 393218,
"entries" : [ {
"path" : "file:/tmp/parquet/warehouse"
} ],
"storage" : {
"type" : "file",
"enabled" : true,
"connection" : "file:///",
"config" : null,
"workspaces" : {
"root" : {
"location" : "/",
"writable" : false,
"defaultInputFormat" : null
},
"tmp" : {
"location" : "/home/mapr/drill/exec/java-exec/./target/org.apache.drill.exec.vector.complex.writer.TestJsonReader/dfsTestTmp/1514539108116-0",
"writable" : true,
"defaultInputFormat" : null
}
},
"formats" : {
"psv" : {
"type" : "text",
"extensions" : [ "tbl" ],
"delimiter" : "|"
},
"csv" : {
"type" : "text",
"extensions" : [ "csv" ],
"delimiter" : ","
},
"tsv" : {
"type" : "text",
"extensions" : [ "tsv" ],
"delimiter" : "\t"
},
"httpd" : {
"type" : "httpd",
"logFormat" : "%h %t \"%r\" %>s %b \"%{Referer}i\""
},
"parquet" : {
"type" : "parquet"
},
"json" : {
"type" : "json",
"extensions" : [ "json" ]
},
"pcap" : {
"type" : "pcap"
},
"avro" : {
"type" : "avro"
},
"sequencefile" : {
"type" : "sequencefile",
"extensions" : [ "seq" ]
},
"csvh" : {
"type" : "text",
"extensions" : [ "csvh" ],
"extractHeader" : true,
"delimiter" : ","
},
"txt" : {
"type" : "text",
"extensions" : [ "txt" ],
"delimiter" : "\u0000"
},
"ssv" : {
"type" : "text",
"extensions" : [ "ssv" ],
"delimiter" : " "
},
"csvh-test" : {
"type" : "text",
"extensions" : [ "csvh-test" ],
"skipFirstLine" : true,
"extractHeader" : true,
"delimiter" : ","
}
}
},
"format" : {
"type" : "parquet"
},
"columns" : [ "`w_warehouse_sk`", "`w_warehouse_name`", "`w_warehouse_sq_ft`", "`w_city`", "`w_county`", "`w_state`", "`w_country`" ],
"selectionRoot" : "file:/tmp/parquet/warehouse",
"filter" : "true",
"fileSet" : [ "/tmp/parquet/warehouse/0_0_0.parquet" ],
"cost" : 5.0
}, {
"pop" : "project",
"@id" : 393217,
"exprs" : [ {
"ref" : "`w_warehouse_sk`",
"expr" : "cast( (`w_warehouse_sk` ) as INT )"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "cast( (`w_warehouse_name` ) as VARCHAR(200) )"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "cast( (`w_warehouse_sq_ft` ) as INT )"
}, {
"ref" : "`w_city`",
"expr" : "cast( (`w_city` ) as VARCHAR(200) )"
}, {
"ref" : "`w_county`",
"expr" : "cast( (`w_county` ) as VARCHAR(200) )"
}, {
"ref" : "`w_state`",
"expr" : "cast( (`w_state` ) as VARCHAR(200) )"
}, {
"ref" : "`w_country`",
"expr" : "cast( (`w_country` ) as VARCHAR(200) )"
} ],
"child" : 393218,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "broadcast-exchange",
"@id" : 262156,
"child" : 393217,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 5.0
}, {
"pop" : "hash-join",
"@id" : 262155,
"left" : 262157,
"right" : 262156,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`cs_warehouse_sk`",
"right" : "`w_warehouse_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "project",
"@id" : 262154,
"exprs" : [ {
"ref" : "`cs_sold_date_sk`",
"expr" : "`cs_sold_date_sk`"
}, {
"ref" : "`cs_sold_time_sk`",
"expr" : "`cs_sold_time_sk`"
}, {
"ref" : "`cs_ship_mode_sk`",
"expr" : "`cs_ship_mode_sk`"
}, {
"ref" : "`cs_warehouse_sk`",
"expr" : "`cs_warehouse_sk`"
}, {
"ref" : "`cs_quantity`",
"expr" : "`cs_quantity`"
}, {
"ref" : "`cs_ext_sales_price`",
"expr" : "`cs_ext_sales_price`"
}, {
"ref" : "`cs_net_paid`",
"expr" : "`cs_net_paid`"
}, {
"ref" : "`w_warehouse_sk`",
"expr" : "`w_warehouse_sk`"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`d_date_sk`",
"expr" : "`d_date_sk`"
}, {
"ref" : "`d_year`",
"expr" : "`d_year`"
}, {
"ref" : "`d_moy`",
"expr" : "`d_moy`"
} ],
"child" : 262155,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "project",
"@id" : 262153,
"exprs" : [ {
"ref" : "`cs_sold_time_sk`",
"expr" : "`cs_sold_time_sk`"
}, {
"ref" : "`cs_ship_mode_sk`",
"expr" : "`cs_ship_mode_sk`"
}, {
"ref" : "`cs_quantity`",
"expr" : "`cs_quantity`"
}, {
"ref" : "`cs_ext_sales_price`",
"expr" : "`cs_ext_sales_price`"
}, {
"ref" : "`cs_net_paid`",
"expr" : "`cs_net_paid`"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`d_year`",
"expr" : "`d_year`"
}, {
"ref" : "`d_moy`",
"expr" : "`d_moy`"
} ],
"child" : 262154,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "hash-join",
"@id" : 262151,
"left" : 262153,
"right" : 262152,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`cs_sold_time_sk`",
"right" : "`t_time_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "project",
"@id" : 262150,
"exprs" : [ {
"ref" : "`cs_ship_mode_sk`",
"expr" : "`cs_ship_mode_sk`"
}, {
"ref" : "`cs_quantity`",
"expr" : "`cs_quantity`"
}, {
"ref" : "`cs_ext_sales_price`",
"expr" : "`cs_ext_sales_price`"
}, {
"ref" : "`cs_net_paid`",
"expr" : "`cs_net_paid`"
}, {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`d_year`",
"expr" : "`d_year`"
}, {
"ref" : "`d_moy`",
"expr" : "`d_moy`"
} ],
"child" : 262151,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "hash-join",
"@id" : 262148,
"left" : 262150,
"right" : 262149,
"conditions" : [ {
"relationship" : "EQUALS",
"left" : "`cs_ship_mode_sk`",
"right" : "`sm_ship_mode_sk`"
} ],
"joinType" : "INNER",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "project",
"@id" : 262147,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`d_year`"
}, {
"ref" : "`$f7`",
"expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f8`",
"expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f9`",
"expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f10`",
"expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f11`",
"expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f12`",
"expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f13`",
"expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f14`",
"expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f15`",
"expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f16`",
"expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f17`",
"expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f18`",
"expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`cs_ext_sales_price`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f19`",
"expr" : " ( if (equal(`d_moy`, 1) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f20`",
"expr" : " ( if (equal(`d_moy`, 2) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f21`",
"expr" : " ( if (equal(`d_moy`, 3) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f22`",
"expr" : " ( if (equal(`d_moy`, 4) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f23`",
"expr" : " ( if (equal(`d_moy`, 5) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f24`",
"expr" : " ( if (equal(`d_moy`, 6) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f25`",
"expr" : " ( if (equal(`d_moy`, 7) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f26`",
"expr" : " ( if (equal(`d_moy`, 8) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f27`",
"expr" : " ( if (equal(`d_moy`, 9) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f28`",
"expr" : " ( if (equal(`d_moy`, 10) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f29`",
"expr" : " ( if (equal(`d_moy`, 11) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
}, {
"ref" : "`$f30`",
"expr" : " ( if (equal(`d_moy`, 12) ) then (multiply(`cs_net_paid`, `cs_quantity`) ) else (0 ) end ) "
} ],
"child" : 262148,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 1441548.0
}, {
"pop" : "hash-aggregate",
"@id" : 262146,
"child" : 262147,
"cardinality" : 1.0,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"aggPhase" : "PHASE_1of2",
"groupByExprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
} ],
"aggrExprs" : [ {
"ref" : "`jan_sales`",
"expr" : "sum(`$f7`) "
}, {
"ref" : "`feb_sales`",
"expr" : "sum(`$f8`) "
}, {
"ref" : "`mar_sales`",
"expr" : "sum(`$f9`) "
}, {
"ref" : "`apr_sales`",
"expr" : "sum(`$f10`) "
}, {
"ref" : "`may_sales`",
"expr" : "sum(`$f11`) "
}, {
"ref" : "`jun_sales`",
"expr" : "sum(`$f12`) "
}, {
"ref" : "`jul_sales`",
"expr" : "sum(`$f13`) "
}, {
"ref" : "`aug_sales`",
"expr" : "sum(`$f14`) "
}, {
"ref" : "`sep_sales`",
"expr" : "sum(`$f15`) "
}, {
"ref" : "`oct_sales`",
"expr" : "sum(`$f16`) "
}, {
"ref" : "`nov_sales`",
"expr" : "sum(`$f17`) "
}, {
"ref" : "`dec_sales`",
"expr" : "sum(`$f18`) "
}, {
"ref" : "`jan_net`",
"expr" : "sum(`$f19`) "
}, {
"ref" : "`feb_net`",
"expr" : "sum(`$f20`) "
}, {
"ref" : "`mar_net`",
"expr" : "sum(`$f21`) "
}, {
"ref" : "`apr_net`",
"expr" : "sum(`$f22`) "
}, {
"ref" : "`may_net`",
"expr" : "sum(`$f23`) "
}, {
"ref" : "`jun_net`",
"expr" : "sum(`$f24`) "
}, {
"ref" : "`jul_net`",
"expr" : "sum(`$f25`) "
}, {
"ref" : "`aug_net`",
"expr" : "sum(`$f26`) "
}, {
"ref" : "`sep_net`",
"expr" : "sum(`$f27`) "
}, {
"ref" : "`oct_net`",
"expr" : "sum(`$f28`) "
}, {
"ref" : "`nov_net`",
"expr" : "sum(`$f29`) "
}, {
"ref" : "`dec_net`",
"expr" : "sum(`$f30`) "
} ],
"cost" : 1430285.90625
}, {
"pop" : "project",
"@id" : 262145,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
}, {
"ref" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`",
"expr" : "hash32asdouble(`year1`, hash32asdouble(`w_country`, hash32asdouble(`w_state`, hash32asdouble(`w_county`, hash32asdouble(`w_city`, hash32asdouble(`w_warehouse_sq_ft`, hash32asdouble(`w_warehouse_name`, 1301011) ) ) ) ) ) ) "
} ],
"child" : 262146,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 144154.8
}, {
"pop" : "unordered-mux-exchange",
"@id" : 196609,
"child" : 262145,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 144154.8
}, {
"pop" : "hash-to-random-exchange",
"@id" : 131083,
"child" : 196609,
"expr" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 144154.8
}, {
"pop" : "project",
"@id" : 131081,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
} ],
"child" : 131083,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 144154.8
}, {
"pop" : "hash-aggregate",
"@id" : 131079,
"child" : 131081,
"cardinality" : 1.0,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"aggPhase" : "PHASE_2of2",
"groupByExprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
} ],
"aggrExprs" : [ {
"ref" : "`jan_sales`",
"expr" : "sum(`jan_sales`) "
}, {
"ref" : "`feb_sales`",
"expr" : "sum(`feb_sales`) "
}, {
"ref" : "`mar_sales`",
"expr" : "sum(`mar_sales`) "
}, {
"ref" : "`apr_sales`",
"expr" : "sum(`apr_sales`) "
}, {
"ref" : "`may_sales`",
"expr" : "sum(`may_sales`) "
}, {
"ref" : "`jun_sales`",
"expr" : "sum(`jun_sales`) "
}, {
"ref" : "`jul_sales`",
"expr" : "sum(`jul_sales`) "
}, {
"ref" : "`aug_sales`",
"expr" : "sum(`aug_sales`) "
}, {
"ref" : "`sep_sales`",
"expr" : "sum(`sep_sales`) "
}, {
"ref" : "`oct_sales`",
"expr" : "sum(`oct_sales`) "
}, {
"ref" : "`nov_sales`",
"expr" : "sum(`nov_sales`) "
}, {
"ref" : "`dec_sales`",
"expr" : "sum(`dec_sales`) "
}, {
"ref" : "`jan_net`",
"expr" : "sum(`jan_net`) "
}, {
"ref" : "`feb_net`",
"expr" : "sum(`feb_net`) "
}, {
"ref" : "`mar_net`",
"expr" : "sum(`mar_net`) "
}, {
"ref" : "`apr_net`",
"expr" : "sum(`apr_net`) "
}, {
"ref" : "`may_net`",
"expr" : "sum(`may_net`) "
}, {
"ref" : "`jun_net`",
"expr" : "sum(`jun_net`) "
}, {
"ref" : "`jul_net`",
"expr" : "sum(`jul_net`) "
}, {
"ref" : "`aug_net`",
"expr" : "sum(`aug_net`) "
}, {
"ref" : "`sep_net`",
"expr" : "sum(`sep_net`) "
}, {
"ref" : "`oct_net`",
"expr" : "sum(`oct_net`) "
}, {
"ref" : "`nov_net`",
"expr" : "sum(`nov_net`) "
}, {
"ref" : "`dec_net`",
"expr" : "sum(`dec_net`) "
} ],
"cost" : 143028.59062499998
}, {
"pop" : "project",
"@id" : 131077,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "concatOperator(concatOperator('ZOUROS', ',') , 'ZHOU') "
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
} ],
"child" : 131079,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 14415.48
}, {
"pop" : "union-all",
"@id" : 131076,
"children" : [ 131078, 131077 ],
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21609.32
}, {
"pop" : "project",
"@id" : 131075,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "`ship_carriers`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`$f20`",
"expr" : "divide(`jan_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f21`",
"expr" : "divide(`feb_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f22`",
"expr" : "divide(`mar_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f23`",
"expr" : "divide(`apr_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f24`",
"expr" : "divide(`may_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f25`",
"expr" : "divide(`jun_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f26`",
"expr" : "divide(`jul_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f27`",
"expr" : "divide(`aug_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f28`",
"expr" : "divide(`sep_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f29`",
"expr" : "divide(`oct_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f30`",
"expr" : "divide(`nov_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`$f31`",
"expr" : "divide(`dec_sales`, `w_warehouse_sq_ft`) "
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
} ],
"child" : 131076,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 21609.32
}, {
"pop" : "hash-aggregate",
"@id" : 131074,
"child" : 131075,
"cardinality" : 1.0,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"aggPhase" : "PHASE_1of2",
"groupByExprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "`ship_carriers`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
} ],
"aggrExprs" : [ {
"ref" : "`jan_sales`",
"expr" : "sum(`jan_sales`) "
}, {
"ref" : "`feb_sales`",
"expr" : "sum(`feb_sales`) "
}, {
"ref" : "`mar_sales`",
"expr" : "sum(`mar_sales`) "
}, {
"ref" : "`apr_sales`",
"expr" : "sum(`apr_sales`) "
}, {
"ref" : "`may_sales`",
"expr" : "sum(`may_sales`) "
}, {
"ref" : "`jun_sales`",
"expr" : "sum(`jun_sales`) "
}, {
"ref" : "`jul_sales`",
"expr" : "sum(`jul_sales`) "
}, {
"ref" : "`aug_sales`",
"expr" : "sum(`aug_sales`) "
}, {
"ref" : "`sep_sales`",
"expr" : "sum(`sep_sales`) "
}, {
"ref" : "`oct_sales`",
"expr" : "sum(`oct_sales`) "
}, {
"ref" : "`nov_sales`",
"expr" : "sum(`nov_sales`) "
}, {
"ref" : "`dec_sales`",
"expr" : "sum(`dec_sales`) "
}, {
"ref" : "`jan_sales_per_sq_foot`",
"expr" : "sum(`$f20`) "
}, {
"ref" : "`feb_sales_per_sq_foot`",
"expr" : "sum(`$f21`) "
}, {
"ref" : "`mar_sales_per_sq_foot`",
"expr" : "sum(`$f22`) "
}, {
"ref" : "`apr_sales_per_sq_foot`",
"expr" : "sum(`$f23`) "
}, {
"ref" : "`may_sales_per_sq_foot`",
"expr" : "sum(`$f24`) "
}, {
"ref" : "`jun_sales_per_sq_foot`",
"expr" : "sum(`$f25`) "
}, {
"ref" : "`jul_sales_per_sq_foot`",
"expr" : "sum(`$f26`) "
}, {
"ref" : "`aug_sales_per_sq_foot`",
"expr" : "sum(`$f27`) "
}, {
"ref" : "`sep_sales_per_sq_foot`",
"expr" : "sum(`$f28`) "
}, {
"ref" : "`oct_sales_per_sq_foot`",
"expr" : "sum(`$f29`) "
}, {
"ref" : "`nov_sales_per_sq_foot`",
"expr" : "sum(`$f30`) "
}, {
"ref" : "`dec_sales_per_sq_foot`",
"expr" : "sum(`$f31`) "
}, {
"ref" : "`jan_net`",
"expr" : "sum(`jan_net`) "
}, {
"ref" : "`feb_net`",
"expr" : "sum(`feb_net`) "
}, {
"ref" : "`mar_net`",
"expr" : "sum(`mar_net`) "
}, {
"ref" : "`apr_net`",
"expr" : "sum(`apr_net`) "
}, {
"ref" : "`may_net`",
"expr" : "sum(`may_net`) "
}, {
"ref" : "`jun_net`",
"expr" : "sum(`jun_net`) "
}, {
"ref" : "`jul_net`",
"expr" : "sum(`jul_net`) "
}, {
"ref" : "`aug_net`",
"expr" : "sum(`aug_net`) "
}, {
"ref" : "`sep_net`",
"expr" : "sum(`sep_net`) "
}, {
"ref" : "`oct_net`",
"expr" : "sum(`oct_net`) "
}, {
"ref" : "`nov_net`",
"expr" : "sum(`nov_net`) "
}, {
"ref" : "`dec_net`",
"expr" : "sum(`dec_net`) "
} ],
"cost" : 21524.90859375
}, {
"pop" : "project",
"@id" : 131073,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "`ship_carriers`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`jan_sales_per_sq_foot`",
"expr" : "`jan_sales_per_sq_foot`"
}, {
"ref" : "`feb_sales_per_sq_foot`",
"expr" : "`feb_sales_per_sq_foot`"
}, {
"ref" : "`mar_sales_per_sq_foot`",
"expr" : "`mar_sales_per_sq_foot`"
}, {
"ref" : "`apr_sales_per_sq_foot`",
"expr" : "`apr_sales_per_sq_foot`"
}, {
"ref" : "`may_sales_per_sq_foot`",
"expr" : "`may_sales_per_sq_foot`"
}, {
"ref" : "`jun_sales_per_sq_foot`",
"expr" : "`jun_sales_per_sq_foot`"
}, {
"ref" : "`jul_sales_per_sq_foot`",
"expr" : "`jul_sales_per_sq_foot`"
}, {
"ref" : "`aug_sales_per_sq_foot`",
"expr" : "`aug_sales_per_sq_foot`"
}, {
"ref" : "`sep_sales_per_sq_foot`",
"expr" : "`sep_sales_per_sq_foot`"
}, {
"ref" : "`oct_sales_per_sq_foot`",
"expr" : "`oct_sales_per_sq_foot`"
}, {
"ref" : "`nov_sales_per_sq_foot`",
"expr" : "`nov_sales_per_sq_foot`"
}, {
"ref" : "`dec_sales_per_sq_foot`",
"expr" : "`dec_sales_per_sq_foot`"
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
}, {
"ref" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`",
"expr" : "hash32asdouble(`year1`, hash32asdouble(`ship_carriers`, hash32asdouble(`w_country`, hash32asdouble(`w_state`, hash32asdouble(`w_county`, hash32asdouble(`w_city`, hash32asdouble(`w_warehouse_sq_ft`, hash32asdouble(`w_warehouse_name`, 1301011) ) ) ) ) ) ) ) "
} ],
"child" : 131074,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 2160.932
}, {
"pop" : "unordered-mux-exchange",
"@id" : 65537,
"child" : 131073,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 2160.932
}, {
"pop" : "hash-to-random-exchange",
"@id" : 8,
"child" : 65537,
"expr" : "`E_X_P_R_H_A_S_H_F_I_E_L_D`",
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 2160.932
}, {
"pop" : "project",
"@id" : 7,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "`ship_carriers`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`jan_sales_per_sq_foot`",
"expr" : "`jan_sales_per_sq_foot`"
}, {
"ref" : "`feb_sales_per_sq_foot`",
"expr" : "`feb_sales_per_sq_foot`"
}, {
"ref" : "`mar_sales_per_sq_foot`",
"expr" : "`mar_sales_per_sq_foot`"
}, {
"ref" : "`apr_sales_per_sq_foot`",
"expr" : "`apr_sales_per_sq_foot`"
}, {
"ref" : "`may_sales_per_sq_foot`",
"expr" : "`may_sales_per_sq_foot`"
}, {
"ref" : "`jun_sales_per_sq_foot`",
"expr" : "`jun_sales_per_sq_foot`"
}, {
"ref" : "`jul_sales_per_sq_foot`",
"expr" : "`jul_sales_per_sq_foot`"
}, {
"ref" : "`aug_sales_per_sq_foot`",
"expr" : "`aug_sales_per_sq_foot`"
}, {
"ref" : "`sep_sales_per_sq_foot`",
"expr" : "`sep_sales_per_sq_foot`"
}, {
"ref" : "`oct_sales_per_sq_foot`",
"expr" : "`oct_sales_per_sq_foot`"
}, {
"ref" : "`nov_sales_per_sq_foot`",
"expr" : "`nov_sales_per_sq_foot`"
}, {
"ref" : "`dec_sales_per_sq_foot`",
"expr" : "`dec_sales_per_sq_foot`"
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
} ],
"child" : 8,
"outputProj" : false,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 2160.932
}, {
"pop" : "hash-aggregate",
"@id" : 6,
"child" : 7,
"cardinality" : 1.0,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"aggPhase" : "PHASE_2of2",
"groupByExprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "`ship_carriers`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
} ],
"aggrExprs" : [ {
"ref" : "`jan_sales`",
"expr" : "sum(`jan_sales`) "
}, {
"ref" : "`feb_sales`",
"expr" : "sum(`feb_sales`) "
}, {
"ref" : "`mar_sales`",
"expr" : "sum(`mar_sales`) "
}, {
"ref" : "`apr_sales`",
"expr" : "sum(`apr_sales`) "
}, {
"ref" : "`may_sales`",
"expr" : "sum(`may_sales`) "
}, {
"ref" : "`jun_sales`",
"expr" : "sum(`jun_sales`) "
}, {
"ref" : "`jul_sales`",
"expr" : "sum(`jul_sales`) "
}, {
"ref" : "`aug_sales`",
"expr" : "sum(`aug_sales`) "
}, {
"ref" : "`sep_sales`",
"expr" : "sum(`sep_sales`) "
}, {
"ref" : "`oct_sales`",
"expr" : "sum(`oct_sales`) "
}, {
"ref" : "`nov_sales`",
"expr" : "sum(`nov_sales`) "
}, {
"ref" : "`dec_sales`",
"expr" : "sum(`dec_sales`) "
}, {
"ref" : "`jan_sales_per_sq_foot`",
"expr" : "sum(`jan_sales_per_sq_foot`) "
}, {
"ref" : "`feb_sales_per_sq_foot`",
"expr" : "sum(`feb_sales_per_sq_foot`) "
}, {
"ref" : "`mar_sales_per_sq_foot`",
"expr" : "sum(`mar_sales_per_sq_foot`) "
}, {
"ref" : "`apr_sales_per_sq_foot`",
"expr" : "sum(`apr_sales_per_sq_foot`) "
}, {
"ref" : "`may_sales_per_sq_foot`",
"expr" : "sum(`may_sales_per_sq_foot`) "
}, {
"ref" : "`jun_sales_per_sq_foot`",
"expr" : "sum(`jun_sales_per_sq_foot`) "
}, {
"ref" : "`jul_sales_per_sq_foot`",
"expr" : "sum(`jul_sales_per_sq_foot`) "
}, {
"ref" : "`aug_sales_per_sq_foot`",
"expr" : "sum(`aug_sales_per_sq_foot`) "
}, {
"ref" : "`sep_sales_per_sq_foot`",
"expr" : "sum(`sep_sales_per_sq_foot`) "
}, {
"ref" : "`oct_sales_per_sq_foot`",
"expr" : "sum(`oct_sales_per_sq_foot`) "
}, {
"ref" : "`nov_sales_per_sq_foot`",
"expr" : "sum(`nov_sales_per_sq_foot`) "
}, {
"ref" : "`dec_sales_per_sq_foot`",
"expr" : "sum(`dec_sales_per_sq_foot`) "
}, {
"ref" : "`jan_net`",
"expr" : "sum(`jan_net`) "
}, {
"ref" : "`feb_net`",
"expr" : "sum(`feb_net`) "
}, {
"ref" : "`mar_net`",
"expr" : "sum(`mar_net`) "
}, {
"ref" : "`apr_net`",
"expr" : "sum(`apr_net`) "
}, {
"ref" : "`may_net`",
"expr" : "sum(`may_net`) "
}, {
"ref" : "`jun_net`",
"expr" : "sum(`jun_net`) "
}, {
"ref" : "`jul_net`",
"expr" : "sum(`jul_net`) "
}, {
"ref" : "`aug_net`",
"expr" : "sum(`aug_net`) "
}, {
"ref" : "`sep_net`",
"expr" : "sum(`sep_net`) "
}, {
"ref" : "`oct_net`",
"expr" : "sum(`oct_net`) "
}, {
"ref" : "`nov_net`",
"expr" : "sum(`nov_net`) "
}, {
"ref" : "`dec_net`",
"expr" : "sum(`dec_net`) "
} ],
"cost" : 2152.4908593749997
}, {
"pop" : "top-n",
"@id" : 5,
"child" : 6,
"orderings" : [ {
"order" : "ASC",
"expr" : "`w_warehouse_name`",
"nullDirection" : "LAST"
} ],
"reverse" : false,
"limit" : 100,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 216.09319999999997
}, {
"pop" : "selection-vector-remover",
"@id" : 4,
"child" : 5,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 216.09319999999997
}, {
"pop" : "limit",
"@id" : 3,
"child" : 4,
"first" : 0,
"last" : 100,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 100.0
}, {
"pop" : "selection-vector-remover",
"@id" : 2,
"child" : 3,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 100.0
}, {
"pop" : "project",
"@id" : 1,
"exprs" : [ {
"ref" : "`w_warehouse_name`",
"expr" : "`w_warehouse_name`"
}, {
"ref" : "`w_warehouse_sq_ft`",
"expr" : "`w_warehouse_sq_ft`"
}, {
"ref" : "`w_city`",
"expr" : "`w_city`"
}, {
"ref" : "`w_county`",
"expr" : "`w_county`"
}, {
"ref" : "`w_state`",
"expr" : "`w_state`"
}, {
"ref" : "`w_country`",
"expr" : "`w_country`"
}, {
"ref" : "`ship_carriers`",
"expr" : "`ship_carriers`"
}, {
"ref" : "`year1`",
"expr" : "`year1`"
}, {
"ref" : "`jan_sales`",
"expr" : "`jan_sales`"
}, {
"ref" : "`feb_sales`",
"expr" : "`feb_sales`"
}, {
"ref" : "`mar_sales`",
"expr" : "`mar_sales`"
}, {
"ref" : "`apr_sales`",
"expr" : "`apr_sales`"
}, {
"ref" : "`may_sales`",
"expr" : "`may_sales`"
}, {
"ref" : "`jun_sales`",
"expr" : "`jun_sales`"
}, {
"ref" : "`jul_sales`",
"expr" : "`jul_sales`"
}, {
"ref" : "`aug_sales`",
"expr" : "`aug_sales`"
}, {
"ref" : "`sep_sales`",
"expr" : "`sep_sales`"
}, {
"ref" : "`oct_sales`",
"expr" : "`oct_sales`"
}, {
"ref" : "`nov_sales`",
"expr" : "`nov_sales`"
}, {
"ref" : "`dec_sales`",
"expr" : "`dec_sales`"
}, {
"ref" : "`jan_sales_per_sq_foot`",
"expr" : "`jan_sales_per_sq_foot`"
}, {
"ref" : "`feb_sales_per_sq_foot`",
"expr" : "`feb_sales_per_sq_foot`"
}, {
"ref" : "`mar_sales_per_sq_foot`",
"expr" : "`mar_sales_per_sq_foot`"
}, {
"ref" : "`apr_sales_per_sq_foot`",
"expr" : "`apr_sales_per_sq_foot`"
}, {
"ref" : "`may_sales_per_sq_foot`",
"expr" : "`may_sales_per_sq_foot`"
}, {
"ref" : "`jun_sales_per_sq_foot`",
"expr" : "`jun_sales_per_sq_foot`"
}, {
"ref" : "`jul_sales_per_sq_foot`",
"expr" : "`jul_sales_per_sq_foot`"
}, {
"ref" : "`aug_sales_per_sq_foot`",
"expr" : "`aug_sales_per_sq_foot`"
}, {
"ref" : "`sep_sales_per_sq_foot`",
"expr" : "`sep_sales_per_sq_foot`"
}, {
"ref" : "`oct_sales_per_sq_foot`",
"expr" : "`oct_sales_per_sq_foot`"
}, {
"ref" : "`nov_sales_per_sq_foot`",
"expr" : "`nov_sales_per_sq_foot`"
}, {
"ref" : "`dec_sales_per_sq_foot`",
"expr" : "`dec_sales_per_sq_foot`"
}, {
"ref" : "`jan_net`",
"expr" : "`jan_net`"
}, {
"ref" : "`feb_net`",
"expr" : "`feb_net`"
}, {
"ref" : "`mar_net`",
"expr" : "`mar_net`"
}, {
"ref" : "`apr_net`",
"expr" : "`apr_net`"
}, {
"ref" : "`may_net`",
"expr" : "`may_net`"
}, {
"ref" : "`jun_net`",
"expr" : "`jun_net`"
}, {
"ref" : "`jul_net`",
"expr" : "`jul_net`"
}, {
"ref" : "`aug_net`",
"expr" : "`aug_net`"
}, {
"ref" : "`sep_net`",
"expr" : "`sep_net`"
}, {
"ref" : "`oct_net`",
"expr" : "`oct_net`"
}, {
"ref" : "`nov_net`",
"expr" : "`nov_net`"
}, {
"ref" : "`dec_net`",
"expr" : "`dec_net`"
} ],
"child" : 2,
"outputProj" : true,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 100.0
}, {
"pop" : "screen",
"@id" : 0,
"child" : 1,
"initialAllocation" : 1000000,
"maxAllocation" : 10000000000,
"cost" : 100.0
} ]
}
{code}
With option values:
{code:sql}
`planner.width.max_per_node` = 22
`planner.memory.max_query_memory_per_node` = 10737418240
`drill.exec.hashagg.fallback.enabled` = true
{code}
This plan uses TPC-DS parquet data.
A decision to spill for both phases is taken considering free memory amount, allocated for the concrete operator. For the case when we have large {{planner.width.max_per_node}}, a small memory amount is allocated for HashAgg operator. In this case, spilling will be produced for every batch, and the query is executed very slowly. For the case, when {{planner.width.max_per_node}} is small, spilling does not happen and query executes quickly.
Therefore, except {{planner.width.max_per_node}}, Drill should take into account available memory when calculating operators count.
Also, this problem, perhaps, may be solved at the planning stage, when will be implemented cumulative memory cost usage.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)