Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
Impala 2.7.0
Description
The summary from impala-shell has the correct column width but the debug page and query profile as it wrong.
Printed summary
Operator #Hosts Avg Time Max Time #Rows Est. #Rows Peak Mem Est. Peak Mem Detail --------------------------------------------------------------------------------------------------------------------------------------------- 134:MERGING-EXCHANGE 1 14.340ms 14.340ms 14.15K 1 0 -1.00 B UNPARTITIONED 79:SORT 14 4.139ms 5.334ms 14.15K 1 24.60 MB 16.00 MB 78:HASH JOIN 14 57.735ms 86.686ms 14.15K 1 3.15 MB 5.39 KB INNER JOIN, PARTITIONED |--133:EXCHANGE 14 880.431us 1.965ms 34.10K 226 0 0 HASH(i_item_sk,s_store_name... | 131:AGGREGATE 14 57.564ms 81.155ms 34.10K 226 3.07 MB 10.00 MB FINALIZE | 130:EXCHANGE 14 600.681us 1.332ms 34.10K 226 0 0 HASH(i_product_name,i_item_... | 77:AGGREGATE 14 55.710ms 67.794ms 34.10K 226 22.82 MB 10.00 MB STREAMING | 76:HASH JOIN 14 10.261ms 12.416ms 34.10K 226 14.04 MB 88.00 B INNER JOIN, BROADCAST | |--129:EXCHANGE 14 10.290us 12.702us 20 20 0 0 BROADCAST | | 58:SCAN HDFS 1 78.110ms 78.110ms 20 20 52.00 KB 16.00 MB tpcds_1000_parquet.income_b... | 75:HASH JOIN 14 12.293ms 15.766ms 34.10K 226 14.03 MB 88.00 B INNER JOIN, BROADCAST | |--128:EXCHANGE 14 11.264us 24.672us 20 20 0 0 BROADCAST | | 57:SCAN HDFS 1 76.672ms 76.672ms 20 20 45.00 KB 16.00 MB tpcds_1000_parquet.income_b... | 74:HASH JOIN 14 79.140ms 89.086ms 34.10K 226 147.18 MB 104.84 KB INNER JOIN, BROADCAST | |--127:EXCHANGE 14 5.248ms 8.971ms 34.10K 226 0 0 BROADCAST | | 73:HASH JOIN 1 52.643ms 52.643ms 34.10K 226 2.20 MB 440.00 B INNER JOIN, PARTITIONED | | |--126:EXCHANGE 1 58.040us 58.040us 1.50K 1.50K 0 0 HASH(p_promo_sk) | | | 52:SCAN HDFS 1 47.190ms 47.190ms 1.50K 1.50K 136.00 KB 16.00 MB tpcds_1000_parquet.promotion | | 125:EXCHANGE 1 4.142ms 4.142ms 34.12K 226 0 0 HASH(ss_promo_sk) | | 72:HASH JOIN 1 52.082ms 52.082ms 34.12K 226 2.16 MB 3.22 KB INNER JOIN, PARTITIONED | | |--124:EXCHANGE 1 30.498us 30.498us 1.00K 1.00K 0 0 HASH(s_store_sk) | | | 48:SCAN HDFS 1 51.661ms 51.661ms 1.00K 1.00K 136.00 KB 48.00 MB tpcds_1000_parquet.store | | 123:EXCHANGE 1 4.109ms 4.109ms 34.18K 226 0 0 HASH(ss_store_sk) | | 71:HASH JOIN 1 93.041ms 93.041ms 34.18K 226 140.20 MB 91.04 KB INNER JOIN, BROADCAST | | |--122:EXCHANGE 1 2.554ms 2.554ms 34.34K 226 0 0 BROADCAST | | | 70:HASH JOIN 1 126.984ms 126.984ms 34.34K 226 140.19 MB 89.09 KB INNER JOIN, BROADCAST | | | |--121:EXCHANGE 1 11.404ms 11.404ms 34.66K 226 0 0 BROADCAST | | | | 69:HASH JOIN 15 554.955ms 597.158ms 34.66K 226 155.62 MB 85.21 KB INNER JOIN, BROADCAST | | | | |--120:EXCHANGE 15 139.771ms 162.697ms 348.40K 226 0 0 BROADCAST | | | | | 68:HASH JOIN 14 472.765ms 521.521ms 348.40K 226 150.31 MB 63.24 KB INNER JOIN, BROADCAST | | | | | |--119:EXCHANGE 14 139.580ms 178.877ms 349.51K 228 0 0 BROADCAST | | | | | | 67:HASH JOIN 13 526.272ms 574.366ms 349.51K 228 163.14 MB 58.61 KB INNER JOIN, BROADCAST | | | | | | |--118:EX... 13 136.403ms 168.681ms 445.58K 230 0 0 BROADCAST | | | | | | | 66:HAS... 13 540.040ms 620.432ms 445.58K 230 163.13 MB 53.88 KB INNER JOIN, BROADCAST | | | | | | | |--117... 13 47.330ms 94.680ms 448.26K 232 0 0 BROADCAST | | | | | | | | 65:... 1 466.089ms 466.089ms 448.26K 232 291.11 MB 53.01 KB INNER JOIN, BROADCAST | | | | | | | | |--... 1 26.570ms 26.570ms 464.25K 237 0 0 BROADCAST | | | | | | | | | ... 1 453.688ms 453.688ms 464.25K 237 291.09 MB 52.04 KB INNER JOIN, BROADCAST | | | | | | | | | ... 1 69.422ms 69.422ms 469.74K 242 0 0 BROADCAST | | | | | | | | | ... 12 440.777ms 477.026ms 469.74K 242 155.39 MB 47.51 KB INNER JOIN, BROADCAST | | | | | | | | | ... 12 24.728ms 30.438ms 480.94K 251 0 0 BROADCAST | | | | | | | | | ... 15 61.388ms 123.807ms 480.94K 251 2.06 MB 3.21 KB INNER JOIN, BROADCAST | | | | | | | | | ... 15 20.865us 44.990us 365 373 0 0 BROADCAST | | | | | | | | | ... 1 78.821ms 78.821ms 365 373 1.74 MB 32.00 MB tpcds_1000_parquet.date_dim d1 | | | | | | | | | ... 15 10.534ms 17.537ms 480.94K 1.23K 2.04 MB 31.65 KB INNER JOIN, PARTITIONED | | | | | | | | | ... 15 8.234us 10.558us 255 6.12K 0 0 HASH(i_item_sk) | | | | | | | | | ... 1 246.947ms 246.947ms 255 6.12K 17.74 MB 160.00 MB tpcds_1000_parquet.item | | | | | | | | | ... 15 3.690ms 5.813ms 480.94K 60.84K 0 0 HASH(ss_item_sk) | | | | | | | | | ... 15 66.203ms 108.140ms 480.94K 60.84K 9.04 MB 1.28 MB INNER JOIN, BROADCAST | | | | | | | | | ... 15 1.569ms 3.370ms 255 30.42K 0 0 BROADCAST | | | | | | | | | ... 15 58.325ms 101.483ms 255 30.42K 2.33 MB 10.00 MB FINALIZE | | | | | | | | | ... 15 124.990us 195.540us 3.83K 304.20K 0 0 HASH(cs_item_sk) | | | | | | | | | ... 15 52.133ms 84.228ms 3.83K 304.20K 1.57 MB 10.00 MB STREAMING | | | | | | | | | ... 15 30.672ms 38.234ms 126.04K 1.44B 2.30 MB 281.98 MB INNER JOIN, PARTITIONED | | | | | | | | | ... 15 729.220us 1.079ms 126.04K 144.00M 0 0 HASH(cr_item_sk,cr_order_nu... | | | | | | | | | ... 15 2s217ms 2s403ms 126.04K 144.00M 24.05 MB 80.00 MB tpcds_1000_parquet.catalog_... | | | | | | | | | ... 15 532.690us 976.360us 126.52K 1.44B 0 0 HASH(cs_item_sk,cs_order_nu... | | | | | | | | | ... 15 8s688ms 9s458ms 126.52K 1.44B 115.70 MB 216.00 MB tpcds_1000_parquet.catalog_... | | | | | | | | | ... 15 3s476ms 5s463ms 480.94K 2.88B 600.82 MB 968.00 MB tpcds_1000_parquet.store_sales | | | | | | | | | ... 12 37.500ms 47.297ms 470.19K 12.00M 87.84 MB 336.00 MB tpcds_1000_parquet.customer | | | | | | | | | ... 1 4.292ms 4.292ms 7.20K 7.20K 325.25 KB 32.00 MB tpcds_1000_parquet.househol... | | | | | | | | 54:... 1 4.197ms 4.197ms 7.20K 7.20K 325.25 KB 32.00 MB tpcds_1000_parquet.househol... | | | | | | | 50:SCA... 13 8.748ms 10.286ms 396.68K 1.92M 9.31 MB 32.00 MB tpcds_1000_parquet.customer... | | | | | | 51:SCAN HDFS 13 8.000ms 9.134ms 385.51K 1.92M 9.75 MB 32.00 MB tpcds_1000_parquet.customer... | | | | | 55:SCAN HDFS 14 23.260ms 26.448ms 338.47K 6.00M 60.15 MB 80.00 MB tpcds_1000_parquet.customer... | | | | 40:SCAN HDFS 15 349.890ms 692.737ms 34.69K 288.00M 157.90 MB 48.00 MB tpcds_1000_parquet.store_re... | | | 46:SCAN HDFS 1 11.934ms 11.934ms 3.65K 73.05K 1.30 MB 32.00 MB tpcds_1000_parquet.date_dim d2 | | 47:SCAN HDFS 1 14.991ms 14.991ms 3.65K 73.05K 1.30 MB 32.00 MB tpcds_1000_parquet.date_dim d3 | 56:SCAN HDFS 14 72.130ms 81.181ms 34.02K 6.00M 27.32 MB 80.00 MB tpcds_1000_parquet.customer... 132:EXCHANGE 14 491.423us 1.008ms 24.20K 226 0 0 HASH(i_item_sk,s_store_name... 105:AGGREGATE 14 76.615ms 108.156ms 24.20K 226 3.07 MB 10.00 MB FINALIZE 104:EXCHANGE 14 391.886us 679.618us 24.20K 226 0 0 HASH(i_product_name,i_item_... 38:AGGREGATE 14 65.677ms 77.150ms 24.20K 226 19.07 MB 10.00 MB STREAMING 37:HASH JOIN 14 15.478ms 42.183ms 24.20K 226 14.04 MB 88.00 B INNER JOIN, BROADCAST |--103:EXCHANGE 14 12.520us 20.038us 20 20 0 0 BROADCAST | 19:SCAN HDFS 1 115.348ms 115.348ms 20 20 52.00 KB 16.00 MB tpcds_1000_parquet.income_b... 36:HASH JOIN 14 17.017ms 33.390ms 24.20K 226 14.03 MB 88.00 B INNER JOIN, BROADCAST |--102:EXCHANGE 14 9.123us 12.426us 20 20 0 0 BROADCAST | 18:SCAN HDFS 1 102.458ms 102.458ms 20 20 52.00 KB 16.00 MB tpcds_1000_parquet.income_b... 35:HASH JOIN 14 58.787ms 65.031ms 24.20K 226 147.12 MB 104.84 KB INNER JOIN, BROADCAST |--101:EXCHANGE 14 2.523ms 4.202ms 24.20K 226 0 0 BROADCAST | 34:HASH JOIN 1 65.130ms 65.130ms 24.20K 226 2.20 MB 440.00 B INNER JOIN, PARTITIONED | |--100:EXCHANGE 1 37.784us 37.784us 1.50K 1.50K 0 0 HASH(p_promo_sk) | | 13:SCAN HDFS 1 45.626ms 45.626ms 1.50K 1.50K 136.00 KB 16.00 MB tpcds_1000_parquet.promotion | 99:EXCHANGE 1 8.029ms 8.029ms 24.22K 226 0 0 HASH(ss_promo_sk) | 33:HASH JOIN 1 88.982ms 88.982ms 24.22K 226 2.16 MB 3.22 KB INNER JOIN, PARTITIONED | |--98:EXCHANGE 1 51.526us 51.526us 1.00K 1.00K 0 0 HASH(s_store_sk) | | 09:SCAN HDFS 1 5s006ms 5s006ms 1.00K 1.00K 136.00 KB 48.00 MB tpcds_1000_parquet.store | 97:EXCHANGE 1 6.454ms 6.454ms 24.27K 226 0 0 HASH(ss_store_sk) | 32:HASH JOIN 1 111.091ms 111.091ms 24.27K 226 140.20 MB 91.04 KB INNER JOIN, BROADCAST | |--96:EXCHANGE 1 1.330ms 1.330ms 24.38K 226 0 0 BROADCAST | | 31:HASH JOIN 1 124.697ms 124.697ms 24.38K 226 140.19 MB 89.09 KB INNER JOIN, BROADCAST | | |--95:EXCHANGE 1 8.602ms 8.602ms 24.61K 226 0 0 BROADCAST | | | 30:HASH JOIN 15 572.259ms 639.848ms 24.61K 226 154.12 MB 85.21 KB INNER JOIN, BROADCAST | | | |--94:EXCHANGE 15 142.834ms 164.119ms 347.50K 226 0 0 BROADCAST | | | | 29:HASH JOIN 14 483.742ms 552.014ms 347.50K 226 149.24 MB 63.24 KB INNER JOIN, BROADCAST | | | | |--93:EXCHANGE 14 122.844ms 142.403ms 348.59K 228 0 0 BROADCAST | | | | | 28:HASH JOIN 13 619.385ms 695.750ms 348.59K 228 163.14 MB 58.61 KB INNER JOIN, BROADCAST | | | | | |--92:EXCHANGE 13 142.083ms 169.737ms 443.71K 230 0 0 BROADCAST | | | | | | 27:HASH JOIN 13 603.295ms 661.738ms 443.71K 230 163.13 MB 53.88 KB INNER JOIN, BROADCAST | | | | | | |--91:EXC... 13 35.267ms 48.617ms 446.42K 232 0 0 BROADCAST | | | | | | | 26:HAS... 1 699.730ms 699.730ms 446.42K 232 291.11 MB 53.01 KB INNER JOIN, BROADCAST | | | | | | | |--90:... 1 163.688ms 163.688ms 462.31K 237 0 0 BROADCAST | | | | | | | | 25:... 1 538.250ms 538.250ms 462.31K 237 291.09 MB 52.04 KB INNER JOIN, BROADCAST | | | | | | | | |--... 1 75.754ms 75.754ms 467.99K 242 0 0 BROADCAST | | | | | | | | | ... 12 459.680ms 526.770ms 467.99K 242 155.39 MB 47.51 KB INNER JOIN, BROADCAST | | | | | | | | | ... 12 22.755ms 26.740ms 479.26K 251 0 0 BROADCAST | | | | | | | | | ... 15 77.278ms 198.217ms 479.26K 251 2.06 MB 3.21 KB INNER JOIN, BROADCAST | | | | | | | | | ... 15 16.645us 26.590us 366 373 0 0 BROADCAST | | | | | | | | | ... 1 112.620ms 112.620ms 366 373 1.74 MB 32.00 MB tpcds_1000_parquet.date_dim d1 | | | | | | | | | ... 15 11.670ms 31.031ms 479.26K 1.23K 2.04 MB 31.65 KB INNER JOIN, PARTITIONED | | | | | | | | | ... 15 13.580us 18.432us 255 6.12K 0 0 HASH(i_item_sk) | | | | | | | | | ... 1 4s888ms 4s888ms 255 6.12K 17.80 MB 160.00 MB tpcds_1000_parquet.item | | | | | | | | | ... 15 3.571ms 6.205ms 479.26K 60.84K 0 0 HASH(ss_item_sk) | | | | | | | | | ... 15 80.040ms 175.920ms 479.26K 60.84K 9.04 MB 1.28 MB INNER JOIN, BROADCAST | | | | | | | | | ... 15 1.810ms 2.440ms 255 30.42K 0 0 BROADCAST | | | | | | | | | ... 15 72.292ms 151.343ms 255 30.42K 2.33 MB 10.00 MB FINALIZE | | | | | | | | | ... 15 149.292us 639.058us 3.83K 304.20K 0 0 HASH(cs_item_sk) | | | | | | | | | ... 15 65.681ms 143.268ms 3.83K 304.20K 1.79 MB 10.00 MB STREAMING | | | | | | | | | ... 15 70.895ms 118.759ms 126.04K 1.44B 2.30 MB 281.98 MB INNER JOIN, PARTITIONED | | | | | | | | | ... 15 661.534us 1.142ms 126.04K 144.00M 0 0 HASH(cr_item_sk,cr_order_nu... | | | | | | | | | ... 15 6s345ms 6s620ms 126.04K 144.00M 22.63 MB 80.00 MB tpcds_1000_parquet.catalog_... | | | | | | | | | ... 15 1.869ms 2.254ms 692.42K 1.44B 0 0 HASH(cs_item_sk,cs_order_nu... | | | | | | | | | ... 15 9s583ms 10s558ms 692.42K 1.44B 119.01 MB 216.00 MB tpcds_1000_parquet.catalog_... | | | | | | | | | ... 15 3s302ms 4s744ms 479.26K 2.88B 727.31 MB 968.00 MB tpcds_1000_parquet.store_sales | | | | | | | | | ... 12 37.213ms 51.806ms 467.02K 12.00M 87.78 MB 336.00 MB tpcds_1000_parquet.customer | | | | | | | | 14:... 1 6.700ms 6.700ms 7.20K 7.20K 325.25 KB 32.00 MB tpcds_1000_parquet.househol... | | | | | | | 15:SCA... 1 4.234ms 4.234ms 7.20K 7.20K 325.25 KB 32.00 MB tpcds_1000_parquet.househol... | | | | | | 11:SCAN HDFS 13 7.048ms 9.927ms 394.22K 1.92M 9.83 MB 32.00 MB tpcds_1000_parquet.customer... | | | | | 12:SCAN HDFS 13 7.208ms 14.765ms 383.59K 1.92M 9.56 MB 32.00 MB tpcds_1000_parquet.customer... | | | | 16:SCAN HDFS 14 21.560ms 25.539ms 337.45K 6.00M 59.64 MB 80.00 MB tpcds_1000_parquet.customer... | | | 01:SCAN HDFS 15 131.040ms 237.120ms 24.63K 288.00M 172.58 MB 48.00 MB tpcds_1000_parquet.store_re... | | 07:SCAN HDFS 1 15.208ms 15.208ms 3.65K 73.05K 1.30 MB 32.00 MB tpcds_1000_parquet.date_dim d2 | 08:SCAN HDFS 1 14.832ms 14.832ms 3.65K 73.05K 1.30 MB 32.00 MB tpcds_1000_parquet.date_dim d3 17:SCAN HDFS 14 70.438ms 75.132ms 24.15K 6.00M 27.68 MB 80.00 MB tpcds_1000_parquet.customer...
TPC-DS Q64
with cs_ui as
(select cs_item_sk
,sum(cs_ext_list_price) as sale,sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit) as refund
from catalog_sales
,catalog_returns
where cs_item_sk = cr_item_sk
and cs_order_number = cr_order_number
group by cs_item_sk
having sum(cs_ext_list_price)>2*sum(cr_refunded_cash+cr_reversed_charge+cr_store_credit)),
cross_sales as
(select i_product_name product_name
,i_item_sk item_sk
,s_store_name store_name
,s_zip store_zip
,ad1.ca_street_number b_street_number
,ad1.ca_street_name b_street_name
,ad1.ca_city b_city
,ad1.ca_zip b_zip
,ad2.ca_street_number c_street_number
,ad2.ca_street_name c_street_name
,ad2.ca_city c_city
,ad2.ca_zip c_zip
,d1.d_year as syear
,d2.d_year as fsyear
,d3.d_year s2year
,count(*) cnt
,sum(ss_wholesale_cost) s1
,sum(ss_list_price) s2
,sum(ss_coupon_amt) s3
FROM store_sales
,store_returns
,cs_ui
,date_dim d1
,date_dim d2
,date_dim d3
,store
,customer
,customer_demographics cd1
,customer_demographics cd2
,promotion
,household_demographics hd1
,household_demographics hd2
,customer_address ad1
,customer_address ad2
,income_band ib1
,income_band ib2
,item
WHERE ss_store_sk = s_store_sk AND
ss_sold_date_sk = d1.d_date_sk AND
ss_customer_sk = c_customer_sk AND
ss_cdemo_sk= cd1.cd_demo_sk AND
ss_hdemo_sk = hd1.hd_demo_sk AND
ss_addr_sk = ad1.ca_address_sk and
ss_item_sk = i_item_sk and
ss_item_sk = sr_item_sk and
ss_ticket_number = sr_ticket_number and
ss_item_sk = cs_ui.cs_item_sk and
c_current_cdemo_sk = cd2.cd_demo_sk AND
c_current_hdemo_sk = hd2.hd_demo_sk AND
c_current_addr_sk = ad2.ca_address_sk and
c_first_sales_date_sk = d2.d_date_sk and
c_first_shipto_date_sk = d3.d_date_sk and
ss_promo_sk = p_promo_sk and
hd1.hd_income_band_sk = ib1.ib_income_band_sk and
hd2.hd_income_band_sk = ib2.ib_income_band_sk and
cd1.cd_marital_status <> cd2.cd_marital_status and
i_color in ('coral','sienna','orange','salmon','ghost','red') and
i_current_price between 79 and 79 + 10 and
i_current_price between 79 + 1 and 79 + 15
group by i_product_name
,i_item_sk
,s_store_name
,s_zip
,ad1.ca_street_number
,ad1.ca_street_name
,ad1.ca_city
,ad1.ca_zip
,ad2.ca_street_number
,ad2.ca_street_name
,ad2.ca_city
,ad2.ca_zip
,d1.d_year
,d2.d_year
,d3.d_year
)
select cs1.product_name
,cs1.store_name
,cs1.store_zip
,cs1.b_street_number
,cs1.b_street_name
,cs1.b_city
,cs1.b_zip
,cs1.c_street_number
,cs1.c_street_name
,cs1.c_city
,cs1.c_zip
,cs1.syear
,cs1.cnt
,cs1.s1 as s11
,cs1.s2 as s21
,cs1.s3 as s31
,cs2.s1 as s12
,cs2.s2 as s22
,cs2.s3 as s32
,cs2.syear
,cs2.cnt
from cross_sales cs1,cross_sales cs2
where cs1.item_sk=cs2.item_sk and
cs1.syear = 2000 and
cs2.syear = 2000 + 1 and
cs2.cnt <= cs1.cnt and
cs1.store_name = cs2.store_name and
cs1.store_zip = cs2.store_zip
order by cs1.product_name
,cs1.store_name
,cs2.cnt
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Resolved
-