+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Explain String | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Estimated Per-Host Requirements: Memory=2.67GB VCores=8 | | | | 105:EXCHANGE [UNPARTITIONED] | | | hosts=2 per-host-mem=unavailable | | | tuple-ids=65N,80,79 row-size=511B cardinality=0 | | | | | 65:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED] | | | hash predicates: user_uuid = customer_uuid | | | runtime filters: RF000 <- customer_uuid | | | hosts=2 per-host-mem=0B | | | tuple-ids=65N,80,79 row-size=511B cardinality=0 | | | | | |--104:EXCHANGE [HASH(customer_uuid)] | | | | hosts=3 per-host-mem=0B | | | | tuple-ids=80,79 row-size=293B cardinality=0 | | | | | | | 63:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED] | | | | hash predicates: user_uuid = customer_uuid, user_uuid = customer_uuid | | | | runtime filters: RF001 <- customer_uuid | | | | hosts=3 per-host-mem=0B | | | | tuple-ids=80,79 row-size=293B cardinality=0 | | | | | | | |--102:EXCHANGE [HASH(customer_uuid,customer_uuid)] | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=80,79 row-size=293B cardinality=0 | | | | | | | | | 62:HASH JOIN [NULL AWARE LEFT ANTI JOIN, BROADCAST] | | | | | hash predicates: customer_uuid = a.customer_uuid | | | | | hosts=3 per-host-mem=854.43MB | | | | | tuple-ids=80,79 row-size=293B cardinality=0 | | | | | | | | | |--100:EXCHANGE [BROADCAST] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=46,47N,58,54,55 row-size=158B cardinality=5155253 | | | | | | | | | | | 59:HASH JOIN [INNER JOIN, PARTITIONED] | | | | | | hash predicates: a.customer_key = a.customer_key | | | | | | hosts=3 per-host-mem=120.67MB | | | | | | tuple-ids=46,47N,58,54,55 row-size=158B cardinality=5155253 | | | | | | | | | | | |--99:EXCHANGE [HASH(a.customer_key)] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=54,55 row-size=76B cardinality=4540666 | | | | | | | | | | | | | 56:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | | | hosts=3 per-host-mem=40B | | | | | | | tuple-ids=54,55 row-size=76B cardinality=4540666 | | | | | | | | | | | | | |--97:EXCHANGE [BROADCAST] | | | | | | | | hosts=1 per-host-mem=0B | | | | | | | | tuple-ids=55 row-size=12B cardinality=3 | | | | | | | | | | | | | | | 55:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | | predicates: tenant_id = 0 | | | | | | | table stats: 8 rows total | | | | | | | column stats: all | | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | | tuple-ids=55 row-size=12B cardinality=3 | | | | | | | | | | | | | 54:SCAN HDFS [DATABASE_1_CORE.TABLE_1 a, RANDOM] | | | | | | partitions=7/7 files=103 size=2.91GB | | | | | | table stats: 13621996 rows total | | | | | | column stats: all | | | | | | hosts=3 per-host-mem=1.84GB | | | | | | tuple-ids=54 row-size=64B cardinality=13621996 | | | | | | | | | | | 98:EXCHANGE [HASH(a.customer_key)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=46,47N,58 row-size=82B cardinality=5155253 | | | | | | | | | | | 58:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | hash predicates: a.segment_instance_key = a.segment_instance_key | | | | | | hosts=3 per-host-mem=624.40KB | | | | | | tuple-ids=46,47N,58 row-size=82B cardinality=5155253 | | | | | | | | | | | |--96:EXCHANGE [BROADCAST] | | | | | | | hosts=1 per-host-mem=0B | | | | | | | tuple-ids=58 row-size=29B cardinality=20049 | | | | | | | | | | | | | 57:SCAN HDFS [DATABASE_1_CORE.TABLE_4_instance a, RANDOM] | | | | | | partitions=1/1 files=1 size=16.60MB | | | | | | predicates: a.segment_instance_status = 'Refreshed' | | | | | | table stats: 120293 rows total | | | | | | column stats: all | | | | | | hosts=1 per-host-mem=416.00MB | | | | | | tuple-ids=58 row-size=29B cardinality=20049 | | | | | | | | | | | 53:HASH JOIN [LEFT SEMI JOIN, BROADCAST] | | | | | | hash predicates: a.segment_id = segment_id | | | | | | runtime filters: RF016 <- segment_id | | | | | | hosts=3 per-host-mem=463B | | | | | | tuple-ids=46,47N row-size=53B cardinality=9989985 | | | | | | | | | | | |--95:EXCHANGE [BROADCAST] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=51 row-size=4B cardinality=105 | | | | | | | | | | | | | 94:AGGREGATE [FINALIZE] | | | | | | | group by: segment_id | | | | | | | hosts=3 per-host-mem=10.00MB | | | | | | | tuple-ids=51 row-size=4B cardinality=105 | | | | | | | | | | | | | 93:EXCHANGE [HASH(segment_id)] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=51 row-size=4B cardinality=105 | | | | | | | | | | | | | 52:AGGREGATE [STREAMING] | | | | | | | group by: a.segment_id | | | | | | | hosts=3 per-host-mem=10.00MB | | | | | | | tuple-ids=51 row-size=4B cardinality=105 | | | | | | | | | | | | | 51:SCAN HDFS [DATABASE_1_CORE.TABLE_4 a, RANDOM] | | | | | | partitions=1/1 files=4 size=183.03MB | | | | | | predicates: a.segment_name IN ('ca889-dynamic-segment-a', 'ca889-static-segment-a') | | | | | | table stats: 116802 rows total | | | | | | column stats: all | | | | | | hosts=3 per-host-mem=1.42GB | | | | | | tuple-ids=49 row-size=42B cardinality=105 | | | | | | | | | | | 50:HASH JOIN [LEFT OUTER JOIN, BROADCAST] | | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | | other predicates: b.tenant_id = 0 OR b.tenant_id IS NULL | | | | | | hosts=3 per-host-mem=14B | | | | | | tuple-ids=46,47N row-size=53B cardinality=201036547 | | | | | | | | | | | |--92:EXCHANGE [BROADCAST] | | | | | | | hosts=1 per-host-mem=0B | | | | | | | tuple-ids=47 row-size=12B cardinality=1 | | | | | | | | | | | | | 49:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | predicates: b.tenant_key = 1 | | | | | | table stats: 8 rows total | | | | | | column stats: all | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | tuple-ids=47 row-size=12B cardinality=1 | | | | | | | | | | | 48:SCAN HDFS [DATABASE_1_CORE.TABLE_3 a, RANDOM] | | | | | partitions=110748/110748 files=110748 size=38.95GB | | | | | predicates: trunc(from_utc_timestamp(default.gb_format_datetime(a.segment_instance_execution_date_key, a.segment_instance_execution_time_key), 'EDT'), 'dd') >= trunc(from_utc_timestamp(now(), 'EDT'), 'dd') - INTERVAL 30 days, trunc(from_utc_timestamp(default.gb_format_datetime(a.segment_instance_execution_date_key, a.segment_instance_execution_time_key), 'EDT'), 'dd') <= trunc(from_utc_timestamp(now(), 'EDT'), 'dd') - INTERVAL 1 days, a.tenant_key = 1 | | | | | runtime filters: RF016 -> a.segment_id | | | | | table stats: 4924284343 rows total | | | | | column stats: all | | | | | hosts=3 per-host-mem=96.00MB | | | | | tuple-ids=46 row-size=41B cardinality=201036547 | | | | | | | | | 61:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED] | | | | | hash predicates: customer_uuid = customer_uuid | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=80,79 row-size=293B cardinality=0 | | | | | | | | | |--91:EXCHANGE [HASH(customer_uuid)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=80,79 row-size=293B cardinality=0 | | | | | | | | | | | 37:SELECT | | | | | | predicates: datediff(trunc(from_utc_timestamp(now(), 'EDT'), 'dd'), date_key_date) >= 7, if(greatest(6, 2 * customer_number_of_slots + 2) > ifnull(count(*), 0), 1, 0) = 1, datediff(trunc(from_utc_timestamp(now(), 'EDT'), 'dd'), date_key_date) <= 59, lead(date_key_date, 1, NULL) - INTERVAL 1 days IS NULL | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=80,79 row-size=293B cardinality=0 | | | | | | | | | | | 36:ANALYTIC | | | | | | functions: lead(date_key_date, 1, NULL) | | | | | | partition by: customer_uuid | | | | | | order by: date_key_date ASC | | | | | | window: ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=80,79 row-size=293B cardinality=73 | | | | | | | | | | | 35:SORT | | | | | | order by: customer_uuid ASC NULLS FIRST, date_key_date ASC | | | | | | hosts=3 per-host-mem=16.00MB | | | | | | tuple-ids=80 row-size=277B cardinality=73 | | | | | | | | | | | 89:EXCHANGE [HASH(customer_uuid)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=77,76 row-size=277B cardinality=73 | | | | | | | | | | | 34:SELECT | | | | | | predicates: if(if(greatest(6, 2 * customer_number_of_slots + 2) > ifnull(count(*), 0), 1, 0) = lag(if(greatest(6, 2 * aa.customer_number_of_slots + 2) > ifnull(bb.garments_on_rack, 0), 1, 0), 1, NULL), 1, 0) != 1 | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=77,76 row-size=277B cardinality=73 | | | | | | | | | | | 33:ANALYTIC | | | | | | functions: lag(if(greatest(6, 2 * customer_number_of_slots + 2) > ifnull(count(*), 0), 1, 0), 1, NULL) | | | | | | partition by: aa.customer_uuid | | | | | | order by: date_key_date ASC | | | | | | window: ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=77,76 row-size=277B cardinality=731 | | | | | | | | | | | 32:SORT | | | | | | order by: customer_uuid ASC NULLS FIRST, date_key_date ASC | | | | | | hosts=3 per-host-mem=16.00MB | | | | | | tuple-ids=77 row-size=276B cardinality=731 | | | | | | | | | | | 88:EXCHANGE [HASH(customer_uuid)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=29N,74,73,0 row-size=276B cardinality=731 | | | | | | | | | | | 31:HASH JOIN [RIGHT OUTER JOIN, PARTITIONED] | | | | | | hash predicates: cu.customer_uuid = customer_uuid, default.gb_format_datetime(fcsd.date_key) = a.date_key_date | | | | | | runtime filters: RF006 <- a.date_key_date, RF005 <- customer_uuid | | | | | | hosts=3 per-host-mem=55.52KB | | | | | | tuple-ids=29N,74,73,0 row-size=276B cardinality=731 | | | | | | | | | | | |--87:EXCHANGE [HASH(customer_uuid,a.date_key_date)] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=74,73,0 row-size=212B cardinality=731 | | | | | | | | | | | | | 16:NESTED LOOP JOIN [INNER JOIN, BROADCAST] | | | | | | | predicates: a.date_key_date >= from_utc_timestamp(scd_start, 'EDT'), a.date_key_date < ifnull(lead(scd_start_edt, 1, NULL), from_utc_timestamp(now(), 'EDT')) | | | | | | | hosts=3 per-host-mem=15.62KB | | | | | | | tuple-ids=74,73,0 row-size=212B cardinality=731 | | | | | | | | | | | | | |--85:EXCHANGE [BROADCAST] | | | | | | | | hosts=1 per-host-mem=0B | | | | | | | | tuple-ids=0 row-size=16B cardinality=1000 | | | | | | | | | | | | | | | 00:SCAN HDFS [DATABASE_1_CORE.TABLE_6 a, RANDOM] | | | | | | | partitions=1/1 files=1 size=521.09KB | | | | | | | predicates: a.date_key_date <= from_utc_timestamp(now(), 'EDT'), a.date_key_date >= trunc(from_utc_timestamp(now(), 'EDT'), 'dd') - INTERVAL 60 days | | | | | | | table stats: 10000 rows total | | | | | | | column stats: all | | | | | | | hosts=1 per-host-mem=304.00MB | | | | | | | tuple-ids=0 row-size=16B cardinality=1000 | | | | | | | | | | | | | 15:ANALYTIC | | | | | | | functions: lead(from_utc_timestamp(scd_start, 'EDT'), 1, NULL) | | | | | | | partition by: customer_uuid | | | | | | | order by: from_utc_timestamp(scd_start, 'EDT') ASC | | | | | | | window: ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=74,73 row-size=196B cardinality=731 | | | | | | | | | | | | | 14:SORT | | | | | | | order by: customer_uuid ASC NULLS FIRST, from_utc_timestamp(scd_start, 'EDT') ASC | | | | | | | hosts=3 per-host-mem=16.00MB | | | | | | | tuple-ids=74 row-size=180B cardinality=731 | | | | | | | | | | | | | 84:EXCHANGE [HASH(customer_uuid)] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=71,70 row-size=180B cardinality=731 | | | | | | | | | | | | | 13:SELECT | | | | | | | predicates: count() = 1 | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=71,70 row-size=180B cardinality=731 | | | | | | | | | | | | | 12:ANALYTIC | | | | | | | functions: count() | | | | | | | partition by: customer_uuid, scd_start_date_edt | | | | | | | order by: scd_start DESC | | | | | | | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=71,70 row-size=180B cardinality=731 | | | | | | | | | | | | | 11:SORT | | | | | | | order by: customer_uuid ASC NULLS FIRST, trunc(from_utc_timestamp(scd_start, 'EDT'), 'dd') ASC NULLS FIRST, scd_start DESC | | | | | | | hosts=3 per-host-mem=16.00MB | | | | | | | tuple-ids=71 row-size=172B cardinality=731 | | | | | | | | | | | | | 83:EXCHANGE [HASH(customer_uuid,trunc(from_utc_timestamp(scd_start, 'EDT'), 'dd'))] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=68,67 row-size=172B cardinality=731 | | | | | | | | | | | | | 10:SELECT | | | | | | | predicates: if(customer_number_of_slots = lag(customer_number_of_slots, 1, NULL), 1, 0) != 1 | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=68,67 row-size=172B cardinality=731 | | | | | | | | | | | | | 09:ANALYTIC | | | | | | | functions: lag(customer_number_of_slots, 1, NULL) | | | | | | | partition by: customer_uuid | | | | | | | order by: scd_start ASC | | | | | | | window: ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=68,67 row-size=172B cardinality=7310 | | | | | | | | | | | | | 08:SORT | | | | | | | order by: customer_uuid ASC NULLS FIRST, scd_start ASC | | | | | | | hosts=3 per-host-mem=16.00MB | | | | | | | tuple-ids=68 row-size=164B cardinality=7310 | | | | | | | | | | | | | 82:EXCHANGE [HASH(a.customer_uuid)] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=3,4 row-size=164B cardinality=7310 | | | | | | | | | | | | | 07:HASH JOIN [LEFT SEMI JOIN, BROADCAST] | | | | | | | hash predicates: a.customer_subscription_session = a.customer_subscription_session | | | | | | | hosts=3 per-host-mem=2.01MB | | | | | | | tuple-ids=3,4 row-size=164B cardinality=7310 | | | | | | | | | | | | | |--81:EXCHANGE [BROADCAST] | | | | | | | | hosts=3 per-host-mem=0B | | | | | | | | tuple-ids=6,7 row-size=261B cardinality=7310 | | | | | | | | | | | | | | | 06:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | | | | hosts=3 per-host-mem=14B | | | | | | | | tuple-ids=6,7 row-size=261B cardinality=7310 | | | | | | | | | | | | | | | |--80:EXCHANGE [BROADCAST] | | | | | | | | | hosts=1 per-host-mem=0B | | | | | | | | | tuple-ids=7 row-size=12B cardinality=1 | | | | | | | | | | | | | | | | | 05:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | | | predicates: tenant_id = 0, b.tenant_key = 1 | | | | | | | | table stats: 8 rows total | | | | | | | | column stats: all | | | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | | | tuple-ids=7 row-size=12B cardinality=1 | | | | | | | | | | | | | | | 04:SCAN HDFS [DATABASE_1_CORE.TABLE_1 a, RANDOM] | | | | | | | partitions=1/7 files=25 size=798.03MB | | | | | | | predicates: a.scd_end IS NULL, a.customer_type_key = 0, a.service_state != 'PAUSED', NOT a.customer_marketing_channel LIKE '%blogger%', a.tenant_key = 1, a.customer_subscription_session != '' | | | | | | | table stats: 13621996 rows total | | | | | | | column stats: all | | | | | | | hosts=3 per-host-mem=1.84GB | | | | | | | tuple-ids=6 row-size=249B cardinality=65790 | | | | | | | | | | | | | 03:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | | | hosts=3 per-host-mem=14B | | | | | | | tuple-ids=3,4 row-size=164B cardinality=50452 | | | | | | | | | | | | | |--79:EXCHANGE [BROADCAST] | | | | | | | | hosts=1 per-host-mem=0B | | | | | | | | tuple-ids=4 row-size=12B cardinality=1 | | | | | | | | | | | | | | | 02:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | | predicates: tenant_id = 0, b.tenant_key = 1 | | | | | | | table stats: 8 rows total | | | | | | | column stats: all | | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | | tuple-ids=4 row-size=12B cardinality=1 | | | | | | | | | | | | | 01:SCAN HDFS [DATABASE_1_CORE.TABLE_1 a, RANDOM] | | | | | | partitions=7/7 files=103 size=2.91GB | | | | | | predicates: a.tenant_key = 1, a.customer_subscription_session != '' | | | | | | table stats: 13621996 rows total | | | | | | column stats: all | | | | | | hosts=3 per-host-mem=1.84GB | | | | | | tuple-ids=3 row-size=152B cardinality=454067 | | | | | | | | | | | 86:EXCHANGE [HASH(cu.customer_uuid,default.gb_format_datetime(fcsd.date_key))] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=29 row-size=64B cardinality=284941 | | | | | | | | | | | 78:AGGREGATE [FINALIZE] | | | | | | output: count:merge(*) | | | | | | group by: fcsd.date_key, cu.customer_uuid | | | | | | hosts=3 per-host-mem=19.13MB | | | | | | tuple-ids=29 row-size=64B cardinality=284941 | | | | | | | | | | | 77:EXCHANGE [HASH(fcsd.date_key,cu.customer_uuid)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=29 row-size=64B cardinality=284941 | | | | | | | | | | | 30:AGGREGATE [STREAMING] | | | | | | output: count(*) | | | | | | group by: a.date_key, a.customer_uuid | | | | | | hosts=3 per-host-mem=19.13MB | | | | | | tuple-ids=29 row-size=64B cardinality=284941 | | | | | | | | | | | 29:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | hash predicates: a.customer_key = a.customer_key | | | | | | runtime filters: RF007 <- a.customer_key | | | | | | hosts=3 per-host-mem=1.30MB | | | | | | tuple-ids=17,18N,19N,21,22 row-size=179B cardinality=284941 | | | | | | | | | | | |--76:EXCHANGE [BROADCAST] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=21,22 row-size=127B cardinality=9754 | | | | | | | | | | | | | 28:HASH JOIN [LEFT SEMI JOIN, BROADCAST] | | | | | | | hash predicates: a.customer_subscription_session = a.customer_subscription_session | | | | | | | runtime filters: RF008 <- a.customer_subscription_session | | | | | | | hosts=3 per-host-mem=2.01MB | | | | | | | tuple-ids=21,22 row-size=127B cardinality=9754 | | | | | | | | | | | | | |--75:EXCHANGE [BROADCAST] | | | | | | | | hosts=3 per-host-mem=0B | | | | | | | | tuple-ids=24,25 row-size=261B cardinality=7310 | | | | | | | | | | | | | | | 27:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | | | | runtime filters: RF010 <- b.tenant_key | | | | | | | | hosts=3 per-host-mem=14B | | | | | | | | tuple-ids=24,25 row-size=261B cardinality=7310 | | | | | | | | | | | | | | | |--74:EXCHANGE [BROADCAST] | | | | | | | | | hosts=1 per-host-mem=0B | | | | | | | | | tuple-ids=25 row-size=12B cardinality=1 | | | | | | | | | | | | | | | | | 26:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | | | predicates: tenant_id = 0, b.tenant_key = 1 | | | | | | | | table stats: 8 rows total | | | | | | | | column stats: all | | | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | | | tuple-ids=25 row-size=12B cardinality=1 | | | | | | | | | | | | | | | 25:SCAN HDFS [DATABASE_1_CORE.TABLE_1 a, RANDOM] | | | | | | | partitions=1/7 files=25 size=798.03MB | | | | | | | predicates: a.scd_end IS NULL, a.customer_type_key = 0, a.service_state != 'PAUSED', NOT a.customer_marketing_channel LIKE '%blogger%', a.tenant_key = 1 | | | | | | | runtime filters: RF010 -> a.tenant_key | | | | | | | table stats: 13621996 rows total | | | | | | | column stats: all | | | | | | | hosts=3 per-host-mem=1.84GB | | | | | | | tuple-ids=24 row-size=249B cardinality=65790 | | | | | | | | | | | | | 24:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | | | hosts=3 per-host-mem=40B | | | | | | | tuple-ids=21,22 row-size=127B cardinality=454067 | | | | | | | | | | | | | |--73:EXCHANGE [BROADCAST] | | | | | | | | hosts=1 per-host-mem=0B | | | | | | | | tuple-ids=22 row-size=12B cardinality=3 | | | | | | | | | | | | | | | 23:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | | predicates: tenant_id = 0 | | | | | | | table stats: 8 rows total | | | | | | | column stats: all | | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | | tuple-ids=22 row-size=12B cardinality=3 | | | | | | | | | | | | | 22:SCAN HDFS [DATABASE_1_CORE.TABLE_1 a, RANDOM] | | | | | | partitions=7/7 files=103 size=2.91GB | | | | | | predicates: a.customer_key != 0 | | | | | | runtime filters: RF005 -> a.customer_uuid, RF008 -> a.customer_subscription_session | | | | | | table stats: 13621996 rows total | | | | | | column stats: all | | | | | | hosts=3 per-host-mem=1.84GB | | | | | | tuple-ids=21 row-size=115B cardinality=1362200 | | | | | | | | | | | 21:HASH JOIN [LEFT OUTER JOIN, BROADCAST] | | | | | | hash predicates: b.tenant_key = c.tenant_key | | | | | | other predicates: c.tenant_id = 0 OR c.tenant_id IS NULL | | | | | | hosts=3 per-host-mem=106B | | | | | | tuple-ids=17,18N,19N row-size=52B cardinality=28602294 | | | | | | | | | | | |--72:EXCHANGE [BROADCAST] | | | | | | | hosts=1 per-host-mem=0B | | | | | | | tuple-ids=19 row-size=12B cardinality=8 | | | | | | | | | | | | | 19:SCAN HDFS [DATABASE_1_CORE.TABLE_5 c, RANDOM] | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | table stats: 8 rows total | | | | | | column stats: all | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | tuple-ids=19 row-size=12B cardinality=8 | | | | | | | | | | | 20:HASH JOIN [LEFT OUTER JOIN, BROADCAST] | | | | | | hash predicates: a.customer_key = b.customer_key | | | | | | hosts=3 per-host-mem=17.15MB | | | | | | tuple-ids=17,18N row-size=40B cardinality=28602294 | | | | | | | | | | | |--71:EXCHANGE [BROADCAST] | | | | | | | hosts=3 per-host-mem=0B | | | | | | | tuple-ids=18 row-size=12B cardinality=1362200 | | | | | | | | | | | | | 18:SCAN HDFS [DATABASE_1_CORE.TABLE_1 b, RANDOM] | | | | | | partitions=7/7 files=103 size=2.91GB | | | | | | predicates: b.customer_key != 0 | | | | | | table stats: 13621996 rows total | | | | | | column stats: all | | | | | | hosts=3 per-host-mem=80.00MB | | | | | | tuple-ids=18 row-size=12B cardinality=1362200 | | | | | | | | | | | 17:SCAN HDFS [DATABASE_1_CORE.TABLE_2 a, RANDOM] | | | | | partitions=60/1324 files=292 size=6.28GB | | | | | predicates: a.store_key = 1, a.customer_key != 0, a.closet_state_key = 2 | | | | | runtime filters: RF006 -> default.gb_format_datetime(a.date_key), RF007 -> a.customer_key | | | | | table stats: 7276532361 rows total | | | | | column stats: all | | | | | hosts=3 per-host-mem=608.00MB | | | | | tuple-ids=17 row-size=28B cardinality=28602294 | | | | | | | | | 90:EXCHANGE [HASH(customer_uuid)] | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=83,82 row-size=164B cardinality=921 | | | | | | | | | 47:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED] | | | | | hash predicates: a.customer_subscription_session = customer_subscription_session | | | | | hosts=3 per-host-mem=54.12KB | | | | | tuple-ids=83,82 row-size=164B cardinality=921 | | | | | | | | | |--70:EXCHANGE [HASH(customer_subscription_session)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=83,82 row-size=164B cardinality=921 | | | | | | | | | | | 43:SELECT | | | | | | predicates: datediff(trunc(from_utc_timestamp(now(), 'EDT'), 'dd'), trunc(from_utc_timestamp(scd_start, 'EDT'), 'dd')) < 10 OR datediff(trunc(from_utc_timestamp(now(), 'EDT'), 'dd'), trunc(from_utc_timestamp(scd_start, 'EDT'), 'dd')) > 30, count() = 1 | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=83,82 row-size=164B cardinality=921 | | | | | | | | | | | 42:ANALYTIC | | | | | | functions: count() | | | | | | partition by: customer_uuid, customer_subscription_session | | | | | | order by: scd_start ASC | | | | | | window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=83,82 row-size=164B cardinality=9210 | | | | | | | | | | | 41:SORT | | | | | | order by: customer_uuid ASC NULLS FIRST, customer_subscription_session ASC NULLS FIRST, scd_start ASC | | | | | | hosts=3 per-host-mem=16.00MB | | | | | | tuple-ids=83 row-size=156B cardinality=9210 | | | | | | | | | | | 68:EXCHANGE [HASH(a.customer_uuid,a.customer_subscription_session)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=35,36 row-size=156B cardinality=9210 | | | | | | | | | | | 40:HASH JOIN [INNER JOIN, BROADCAST] | | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | | runtime filters: RF004 <- b.tenant_key | | | | | | hosts=3 per-host-mem=14B | | | | | | tuple-ids=35,36 row-size=156B cardinality=9210 | | | | | | | | | | | |--67:EXCHANGE [BROADCAST] | | | | | | | hosts=1 per-host-mem=0B | | | | | | | tuple-ids=36 row-size=12B cardinality=1 | | | | | | | | | | | | | 39:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | | partitions=1/1 files=1 size=4.40KB | | | | | | predicates: tenant_id = 0, b.tenant_key = 1 | | | | | | table stats: 8 rows total | | | | | | column stats: all | | | | | | hosts=1 per-host-mem=32.00MB | | | | | | tuple-ids=36 row-size=12B cardinality=1 | | | | | | | | | | | 38:SCAN HDFS [DATABASE_1_CORE.TABLE_1 a, RANDOM] | | | | | partitions=1/7 files=25 size=798.03MB | | | | | predicates: a.tenant_key = 1, a.customer_subscription_session != '' | | | | | runtime filters: RF004 -> a.tenant_key | | | | | table stats: 13621996 rows total | | | | | column stats: all | | | | | hosts=3 per-host-mem=1.84GB | | | | | tuple-ids=35 row-size=144B cardinality=82890 | | | | | | | | | 69:EXCHANGE [HASH(a.customer_subscription_session)] | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=40,41 row-size=261B cardinality=7310 | | | | | | | | | 46:HASH JOIN [INNER JOIN, BROADCAST] | | | | | hash predicates: a.tenant_key = b.tenant_key | | | | | runtime filters: RF003 <- b.tenant_key | | | | | hosts=3 per-host-mem=14B | | | | | tuple-ids=40,41 row-size=261B cardinality=7310 | | | | | | | | | |--66:EXCHANGE [BROADCAST] | | | | | | hosts=1 per-host-mem=0B | | | | | | tuple-ids=41 row-size=12B cardinality=1 | | | | | | | | | | | 45:SCAN HDFS [DATABASE_1_CORE.TABLE_5 b, RANDOM] | | | | | partitions=1/1 files=1 size=4.40KB | | | | | predicates: tenant_id = 0, b.tenant_key = 1 | | | | | table stats: 8 rows total | | | | | column stats: all | | | | | hosts=1 per-host-mem=32.00MB | | | | | tuple-ids=41 row-size=12B cardinality=1 | | | | | | | | | 44:SCAN HDFS [DATABASE_1_CORE.TABLE_1 a, RANDOM] | | | | partitions=1/7 files=25 size=798.03MB | | | | predicates: a.customer_subscription_session != '', a.scd_end IS NULL, a.customer_type_key = 0, a.service_state != 'PAUSED', NOT a.customer_marketing_channel LIKE '%blogger%', a.tenant_key = 1 | | | | runtime filters: RF003 -> a.tenant_key | | | | table stats: 13621996 rows total | | | | column stats: all | | | | hosts=3 per-host-mem=1.84GB | | | | tuple-ids=40 row-size=249B cardinality=65790 | | | | | | | 101:EXCHANGE [HASH(user_uuid,user_uuid)] | | | | hosts=3 per-host-mem=0B | | | | tuple-ids=62 row-size=140B cardinality=527 | | | | | | | 60:SCAN HDFS [DATABASE_2.TABLE_7, RANDOM] | | | partitions=1/1 files=18 size=3.93GB | | | predicates: lower(app_key) = 'bucketid', CAST(`data` AS INT) IN (425, 915, 758, 520, 8, 59, 967, 88, 218, 368, 599, 615, 182, 705, 311, 457, 807, 757, 82, 558, 224, 738, 364, 963, 650, 375, 255, 522, 666, 620, 622, 289, 450, 893, 72, 898, 325, 674, 463, 474, 75, 472, 592, 345, 665, 687, 146, 514, 389, 243, 733, 260, 1, 595, 515, 808, 442, 64, 715, 749, 392, 538, 401, 670, 114, 952, 63, 728, 185, 468, 407, 910, 702, 784, 11, 374, 432, 960, 928, 726, 202, 305, 491, 39, 745, 1008, 466, 288, 810, 190, 95, 878, 484, 13, 140, 752, 283, 591, 441, 575, 585, 40, 644, 867, 769, 268, 261, 826, 439, 195, 678, 117, 222, 611, 843, 488, 635, 829, 772, 919, 213, 764, 739, 277, 588, 547, 793, 155, 462, 984, 721, 594, 872, 191, 503, 194, 660, 456, 481, 707, 572, 768, 302, 525, 676, 365, 646, 597, 96, 223, 301, 252, 242, 559, 352, 184, 271, 765, 350, 105, 860, 862, 454, 312, 740, 314, 564, 91, 61, 947, 1001, 537, 833, 774, 656, 322, 668, 709, 160, 340, 980, 92, 787, 788, 530, 147, 197, 122, 680, 744, 722, 438, 377, 180, 825, 308, 894, 876, 66, 3, 306, 408, 56, 166, 110, 294, 875, 89, 568, 777, 280, 754, 794, 717, 313, 116, 267, 104, 881, 27, 541, 649, 208, 961, 10, 500, 628, 664, 86, 343, 334, 30, 994, 482, 789, 244, 469, 683, 80, 44, 803, 888, 216, 28, 76, 877, 203, 708, 523, 144, 911, 938, 342, 978, 792, 848, 229, 90, 625, 1015, 490, 924, 421, 855, 115, 982, 652, 404, 71, 359, 447, 701, 483, 641, 524, 544, 487, 198, 511, 405, 317, 111, 179, 371, 287, 873, 614, 999, 990, 460, 1024, 1023, 761, 16, 54, 944, 505, 298, 528, 605, 233, 395, 846, 632, 69, 2, 378, 630, 327, 799, 363, 844, 230, 973, 603, 750, 331, 866, 309, 241, 993, 1014, 783, 926, 349, 264, 835, 272, 797, 869, 918, 131, 246, 903, 874, 87, 837, 712, 847, 471, 923, 704, 215, 32, 134, 51, 598, 612, 627, 390, 422, 150, 120, 94, 713, 461, 20, 399, 459, 207, 567, 424, 929, 817, 214, 135, 730, 662, 736, 780, 163, 480, 602, 211, 413, 617, 981, 857, 485, 655, 310, 798, 228, 970, 906, 616, 686, 23, 142, 193, 36, 409, 681, 498, 14, 347, 955, 623, 561, 448, 339, 210, 62, 205, 403, 819, 958, 499, 248, 648, 98, 1011, 711, 953, 946, 274, 892, 279, 643, 813, 477, 883, 226, 231, 26, 908, 549, 329, 576, 596, 720, 1003, 269, 292, 551, 969, 266, 1017, 497, 428, 366, 192, 423, 935, 284, 196, 126, 434, 645, 1020, 320, 148, 5, 77, 41, 838, 172, 489, 132, 975, 517, 437, 45, 542, 159, 24, 464, 440, 606, 827, 962, 653, 348, 70, 161, 177, 791, 84, 1018, 240, 951, 882, 821, 613, 699, 336, 1019, 985, 700, 33, 247, 31, 571, 60, 997, 270, 25, 684, 917, 113, 748, 369, 672, 824, 566, 907, 381, 529, 17, 647, 639, 250) | | | runtime filters: RF001 -> user_uuid | | | table stats: 33654608 rows total | | | column stats: all | | | hosts=3 per-host-mem=264.00MB | | | tuple-ids=62 row-size=140B cardinality=527 | | | | | 103:EXCHANGE [HASH(user_uuid)] | | | hosts=2 per-host-mem=0B | | | tuple-ids=65 row-size=218B cardinality=1180157 | | | | | 64:SCAN HDFS [DATABASE_2.TABLE_8, RANDOM] | | partitions=1/1 files=2 size=183.74MB | | runtime filters: RF000 -> user_uuid | | table stats: 1180157 rows total | | column stats: all | | hosts=2 per-host-mem=704.00MB | | tuple-ids=65 row-size=218B cardinality=1180157 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+