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/10/25 13:45:00 UTC
[jira] [Resolved] (DRILL-6069) Hash agg operator requires large
memory amount when planner.width.max_per_node is large
[ https://issues.apache.org/jira/browse/DRILL-6069?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Volodymyr Vysotskyi resolved DRILL-6069.
----------------------------------------
Resolution: Fixed
Fix Version/s: 1.15.0
Fixed in DRILL-6566
> 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
> Priority: Major
> Fix For: 1.15.0
>
>
> 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
(v7.6.3#76005)