Bharath Vissapragada has posted comments on this change. ( http://gerrit.cloudera.org:8080/12221 )
Change subject: [PROTOTYPE] IMPALA-5872: Test case builder for query planner ...................................................................... Patch Set 1: The following example exports a testcase from an 8-node cluster and replays the plan on my local mini cluster (1 node). On 8-node cluster (tpcds1000 SF, q30) [xxxxxxxx:21000] default> use tpcds_1000_parquet; Query: use tpcds_1000_parquet [xxxxxxxx:21000] tpcds_1000_parquet> export testcase into outfile 'hdfs:///tmp/' with customer_total_return as (select wr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, sum(wr_return_amt) as ctr_total_return from web_returns ,date_dim ,customer_address where wr_returned_date_sk = d_date_sk and d_year =2002 and wr_returning_addr_sk = ca_address_sk group by wr_returning_customer_sk ,ca_state) select c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ,c_last_review_date,ctr_total_return from customer_total_return ctr1 ,customer_address ,customer where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 from customer_total_return ctr2 where ctr1.ctr_state = ctr2.ctr_state) and ca_address_sk = c_current_addr_sk and ca_state = 'IL' and ctr1.ctr_customer_sk = c_customer_sk order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ,c_last_review_date,ctr_total_return limit 100; Query: export testcase into outfile 'hdfs:///tmp/' with customer_total_return as (select wr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, sum(wr_return_amt) as ctr_total_return from web_returns ,date_dim ,customer_address where wr_returned_date_sk = d_date_sk and d_year =2002 and wr_returning_addr_sk = ca_address_sk group by wr_returning_customer_sk ,ca_state) select c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ,c_last_review_date,ctr_total_return from customer_total_return ctr1 ,customer_address ,customer where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 from customer_total_return ctr2 where ctr1.ctr_state = ctr2.ctr_state) and ca_address_sk = c_current_addr_sk and ca_state = 'IL' and ctr1.ctr_customer_sk = c_customer_sk order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ,c_last_review_date,ctr_total_return limit 100 Query submitted at: 2019-01-11 14:20:57 (Coordinator: http://xxxxxxxx:25000) Query progress can be monitored at: http://xxxxxxxx:25000/query_plan?query_id=a74384f0af33b8ca:ca1342c000000000 +-----------------------------------------------------------------------------------------------------+ | Test case data output path | +-----------------------------------------------------------------------------------------------------+ | hdfs://xxxxxxxx:8020/tmp/impala-testcase-data-1dc27e8a-a293-48bb-b91e-8384550cebed | +-----------------------------------------------------------------------------------------------------+ WARNINGS: Impala does not have READ_WRITE access to path 'hdfs://xxxxxxxx:8020/' Fetched 1 row(s) in 1.46s [xxxxxxxx:21000] tpcds_1000_parquet> [user@host ~]$ ls -lSh impala-testcase-data-1dc27e8a-a293-48bb-b91e-8384550cebed -rw-r--r-- 1 user user 503K Jan 11 14:22 impala-testcase-data-1dc27e8a-a293-48bb-b91e-8384550cebed Copy the file hdfs://xxxxxxxx:8020/tmp/impala-testcase-data-1dc27e8a-a293-48bb-b91e-8384550cebed into a target cluster. -- Load the testcase on the local cluster. (the target cluster doesn't contain these Dbs/tables, so they are imported) [localhost:21000] default> load testcase from 'hdfs:///tmp/impala-testcase-data-1dc27e8a-a293-48bb-b91e-8384550cebed'; Query: load testcase from 'hdfs:///tmp/impala-testcase-data-1dc27e8a-a293-48bb-b91e-8384550cebed' +----------------------------------------------+ | summary | +----------------------------------------------+ | 1 db(s), 11 table(s) and 0 view(s) imported. | +----------------------------------------------+ Fetched 1 row(s) in 1.07s [localhost:21000] tpcds_1000_parquet> show tables in tpcds_1000_parquet; Query: show tables in tpcds_1000_parquet +------------------+ | name | +------------------+ | customer | | customer_address | | date_dim | | web_returns | +------------------+ Fetched 4 row(s) in 0.00s [localhost:21000] tpcds_1000_parquet> [localhost:21000] tpcds_1000_parquet> set EXPLAIN_LEVEL=3; EXPLAIN_LEVEL set to 3 [localhost:21000] tpcds_1000_parquet> set PLANNER_DEBUG_MODE=true; PLANNER_DEBUG_MODE set to true [localhost:21000] tpcds_1000_parquet> explain with customer_total_return as (select wr_returning_customer_sk as ctr_customer_sk ,ca_state as ctr_state, sum(wr_return_amt) as ctr_total_return from web_returns ,date_dim ,customer_address where wr_returned_date_sk = d_date_sk and d_year =2002 and wr_returning_addr_sk = ca_address_sk group by wr_returning_customer_sk ,ca_state) select c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ,c_last_review_date,ctr_total_return from customer_total_return ctr1 ,customer_address ,customer where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2 from customer_total_return ctr2 where ctr1.ctr_state = ctr2.ctr_state) and ca_address_sk = c_current_addr_sk and ca_state = 'IL' and ctr1.ctr_customer_sk = c_customer_sk order by c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag ,c_birth_day,c_birth_month,c_birth_year,c_birth_country,c_login,c_email_address ,c_last_review_date,ctr_total_return limit 100; here is a snippet of the explain plan | F08:PLAN FRAGMENT [RANDOM] hosts=7 instances=7 <===== | Per-Host Resources: mem-estimate=52.95MB mem-reservation=20.94MB thread-reservation=2 runtime-filters-memory=3.00MB | | DATASTREAM SINK [FRAGMENT=F11, EXCHANGE=28, HASH(wr_returning_addr_sk)] | | | mem-estimate=0B mem-reservation=0B thread-reservation=0 | | 11:HASH JOIN [INNER JOIN, BROADCAST] | | | hash predicates: wr_returned_date_sk = d_date_sk | | | fk/pk conjuncts: wr_returned_date_sk = d_date_sk | | | runtime filters: RF012[bloom] <- d_date_sk | | | mem-estimate=1.94MB mem-reservation=1.94MB spill-buffer=64.00KB thread-reservation=0 | | | tuple-ids=7,8 row-size=24B cardinality=12290653 | | | in pipelines: 08(GETNEXT), 09(OPEN) | | | | | |--27:EXCHANGE [BROADCAST] | | | mem-estimate=16.00KB mem-reservation=0B thread-reservation=0 | | | tuple-ids=8 row-size=8B cardinality=373 | | | in pipelines: 09(GETNEXT) | | | | | 08:SCAN HDFS [tpcds_1000_parquet.web_returns, RANDOM] | | partitions=2185/2185 files=2185 size=6.50GB | | runtime filters: RF010[bloom] -> wr_returning_addr_sk, RF012[bloom] -> wr_returned_date_sk | | stored statistics: | | table: rows=71997522 size=6.50GB | | partitions: 2185/2185 rows=71997522 | | columns: all | | extrapolated-rows=disabled max-scan-range-rows=3239259 | | mem-estimate=48.00MB mem-reservation=16.00MB thread-reservation=1 | | tuple-ids=7 row-size=16B cardinality=71997522 | | in pipelines: 08(GETNEXT) | | | | F09:PLAN FRAGMENT [RANDOM] hosts=1 instances=1 | | Per-Host Resources: mem-estimate=32.00MB mem-reservation=512.00KB thread-reservation=2 | | DATASTREAM SINK [FRAGMENT=F08, EXCHANGE=27, BROADCAST] | | | mem-estimate=0B mem-reservation=0B thread-reservation=0 | | 09:SCAN HDFS [tpcds_1000_parquet.date_dim, RANDOM] | | partitions=1/1 files=1 size=2.15MB | | predicates: d_year = CAST(2002 AS INT) | | stored statistics: | | table: rows=73049 size=2.15MB | | columns: all | | extrapolated-rows=disabled max-scan-range-rows=73049 | | parquet statistics predicates: d_year = CAST(2002 AS INT) | | parquet dictionary predicates: d_year = CAST(2002 AS INT) | | mem-estimate=32.00MB mem-reservation=512.00KB thread-reservation=1 | | tuple-ids=8 row-size=8B cardinality=373 | | in pipelines: 09(GETNEXT) | | | | F10:PLAN FRAGMENT [RANDOM] hosts=7 instances=7 <==== | | Per-Host Resources: mem-estimate=48.00MB mem-reservation=4.00MB thread-reservation=2 | | DATASTREAM SINK [FRAGMENT=F11, EXCHANGE=29, HASH(ca_address_sk)] | | | mem-estimate=0B mem-reservation=0B thread-reservation=0 | | 10:SCAN HDFS [tpcds_1000_parquet.customer_address, RANDOM] | | partitions=1/1 files=7 size=111.28MB | | stored statistics: | | table: rows=6000000 size=111.28MB | | columns: all | | extrapolated-rows=disabled max-scan-range-rows=858070 | | mem-estimate=48.00MB mem-reservation=4.00MB thread-reservation=1 | | tuple-ids=9 row-size=18B cardinality=6000000 | | in pipelines: 10(GETNEXT) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Fetched 353 row(s) in 0.39s -- To view, visit http://gerrit.cloudera.org:8080/12221 To unsubscribe, visit http://gerrit.cloudera.org:8080/settings Gerrit-Project: Impala-ASF Gerrit-Branch: master Gerrit-MessageType: comment Gerrit-Change-Id: Iec83eeb2dc5136768b70ed581fb8d3ed0335cb52 Gerrit-Change-Number: 12221 Gerrit-PatchSet: 1 Gerrit-Owner: Bharath Vissapragada <bhara...@cloudera.com> Gerrit-Reviewer: Bharath Vissapragada <bhara...@cloudera.com> Gerrit-Reviewer: Impala Public Jenkins <impala-public-jenk...@cloudera.com> Gerrit-Comment-Date: Fri, 11 Jan 2019 22:33:41 +0000 Gerrit-HasComments: No