+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Explain String | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Estimated Per-Host Requirements: Memory=2.67GB VCores=8 | | | | 101:EXCHANGE [UNPARTITIONED] | | | limit: 100 | | | hosts=3 per-host-mem=unavailable | | | tuple-ids=77,76 row-size=293B cardinality=0 | | | | | 63:HASH JOIN [RIGHT SEMI JOIN, PARTITIONED] | | | hash predicates: user_uuid = customer_uuid, user_uuid = customer_uuid | | | runtime filters: RF000 <- customer_uuid | | | limit: 100 | | | hosts=3 per-host-mem=0B | | | tuple-ids=77,76 row-size=293B cardinality=0 | | | | | |--100:EXCHANGE [HASH(customer_uuid,customer_uuid)] | | | | hosts=3 per-host-mem=0B | | | | tuple-ids=77,76 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=77,76 row-size=293B cardinality=0 | | | | | | | |--98: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 | | | | | | | | | |--97: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 | | | | | | | | | | | |--95: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 | | | | | | | | | 96: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 | | | | | | | | | |--94: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: RF015 <- segment_id | | | | | hosts=3 per-host-mem=463B | | | | | tuple-ids=46,47N row-size=53B cardinality=9989985 | | | | | | | | | |--93:EXCHANGE [BROADCAST] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=51 row-size=4B cardinality=105 | | | | | | | | | | | 92:AGGREGATE [FINALIZE] | | | | | | group by: segment_id | | | | | | hosts=3 per-host-mem=10.00MB | | | | | | tuple-ids=51 row-size=4B cardinality=105 | | | | | | | | | | | 91: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-b', 'ca889-static-segment-b') | | | | | 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 | | | | | | | | | |--90: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: RF015 -> 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=77,76 row-size=293B cardinality=0 | | | | | | | |--89:EXCHANGE [HASH(customer_uuid)] | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=77,76 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=77,76 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=77,76 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=77 row-size=277B cardinality=73 | | | | | | | | | 87:EXCHANGE [HASH(customer_uuid)] | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=74,73 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=74,73 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=74,73 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=74 row-size=276B cardinality=731 | | | | | | | | | 86:EXCHANGE [HASH(customer_uuid)] | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=29N,71,70,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: RF005 <- a.date_key_date, RF004 <- customer_uuid | | | | | hosts=3 per-host-mem=55.52KB | | | | | tuple-ids=29N,71,70,0 row-size=276B cardinality=731 | | | | | | | | | |--85:EXCHANGE [HASH(customer_uuid,a.date_key_date)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=71,70,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=71,70,0 row-size=212B cardinality=731 | | | | | | | | | | | |--83: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=71,70 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=71 row-size=180B cardinality=731 | | | | | | | | | | | 82:EXCHANGE [HASH(customer_uuid)] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=68,67 row-size=180B cardinality=731 | | | | | | | | | | | 13:SELECT | | | | | | predicates: count() = 1 | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=68,67 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=68,67 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=68 row-size=172B cardinality=731 | | | | | | | | | | | 81:EXCHANGE [HASH(customer_uuid,trunc(from_utc_timestamp(scd_start, 'EDT'), 'dd'))] | | | | | | hosts=3 per-host-mem=0B | | | | | | tuple-ids=65,64 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=65,64 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=65,64 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=65 row-size=164B cardinality=7310 | | | | | | | | | | | 80: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 | | | | | | | | | | | |--79: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 | | | | | | | runtime filters: RF012 <- b.tenant_key | | | | | | | hosts=3 per-host-mem=14B | | | | | | | tuple-ids=6,7 row-size=261B cardinality=7310 | | | | | | | | | | | | | |--78: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 != '' | | | | | | runtime filters: RF012 -> a.tenant_key | | | | | | 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 | | | | | | | | | | | |--77: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 | | | | | | | | | 84: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 | | | | | | | | | 76: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 | | | | | | | | | 75: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: RF006 <- a.customer_key | | | | | hosts=3 per-host-mem=1.30MB | | | | | tuple-ids=17,18N,19N,21,22 row-size=179B cardinality=284941 | | | | | | | | | |--74: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: RF007 <- a.customer_subscription_session | | | | | | hosts=3 per-host-mem=2.01MB | | | | | | tuple-ids=21,22 row-size=127B cardinality=9754 | | | | | | | | | | | |--73: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: RF009 <- b.tenant_key | | | | | | | hosts=3 per-host-mem=14B | | | | | | | tuple-ids=24,25 row-size=261B cardinality=7310 | | | | | | | | | | | | | |--72: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: RF009 -> 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 | | | | | | | | | | | |--71: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: RF004 -> a.customer_uuid, RF007 -> 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 | | | | | | | | | |--70: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 | | | | | | | | | |--69: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: RF005 -> DEFAULT.gb_format_datetime(a.date_key), RF006 -> 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 | | | | | | | 88:EXCHANGE [HASH(customer_uuid)] | | | | hosts=3 per-host-mem=0B | | | | tuple-ids=80,79 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=80,79 row-size=164B cardinality=921 | | | | | | | |--68:EXCHANGE [HASH(customer_subscription_session)] | | | | | hosts=3 per-host-mem=0B | | | | | tuple-ids=80,79 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=80,79 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=80,79 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=80 row-size=156B cardinality=9210 | | | | | | | | | 66: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: RF003 <- b.tenant_key | | | | | hosts=3 per-host-mem=14B | | | | | tuple-ids=35,36 row-size=156B cardinality=9210 | | | | | | | | | |--65: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: RF003 -> 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 | | | | | | | 67: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: RF002 <- b.tenant_key | | | | hosts=3 per-host-mem=14B | | | | tuple-ids=40,41 row-size=261B cardinality=7310 | | | | | | | |--64: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: RF002 -> 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 | | | | | 99: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 (891, 372, 746, 552, 507, 814, 779, 682, 502, 49, 853, 732, 156, 479, 773, 145, 296, 124, 800, 624, 353, 849, 360, 608, 259, 341, 861, 724, 53, 321, 396, 12, 989, 67, 257, 991, 335, 841, 531, 237, 412, 587, 73, 727, 175, 475, 696, 943, 914, 35, 809, 842, 304, 976, 278, 983, 362, 716, 979, 384, 282, 118, 553, 297, 129, 633, 654, 220, 58, 436, 763, 1022, 731, 942, 539, 285, 760, 714, 315, 102, 823, 93, 476, 435, 458, 957, 901, 858, 99, 532, 778, 357, 992, 55, 896, 521, 725, 234, 508, 97, 318, 669, 927, 956, 415, 806, 986, 319, 770, 324, 426, 165, 186, 693, 697, 610, 256, 856, 504, 501, 743, 344, 776, 812, 397, 9, 254, 295, 854, 265, 200, 949, 429, 580, 689, 667, 954, 232, 263, 323, 1013, 565, 465, 157, 527, 922, 107, 899, 48, 37, 663, 642, 452, 988, 238, 651, 604, 367, 430, 996, 1002, 546, 328, 968, 710, 755, 453, 972, 275, 333, 912, 1000, 880, 584, 695, 723, 729, 719, 419, 801, 593, 18, 562, 536, 673, 400, 865, 411, 516, 925, 796, 631, 636, 136, 380, 74, 52, 153, 510, 158, 496, 293, 1006, 273, 830, 950, 217, 199, 767, 414, 850, 506, 154, 905, 859, 851, 533, 444, 4, 870, 393, 143, 43, 65, 839, 416, 557, 189, 816, 600, 187, 473, 802, 785, 307, 330, 152, 977, 303, 431, 1016, 251, 383, 121, 495, 373, 486, 170, 555, 694, 382, 1005, 1009, 417, 85, 149, 130, 57, 762, 509, 545, 513, 753, 822, 781, 451, 225, 50, 346, 933, 931, 181, 543, 21, 112, 1010, 518, 209, 945, 691, 921, 741, 38, 913, 7, 206, 573, 418, 29, 706, 941, 42, 916, 68, 358, 964, 742, 1012, 832, 151, 887, 759, 470, 201, 818, 83, 262, 626, 219, 1004, 103, 123, 394, 864, 386, 698, 276, 751, 286, 937, 445, 995, 782, 582, 245, 581, 370, 361, 895, 108, 690, 570, 583, 965, 900, 920, 281, 167, 494, 795, 249, 316, 291, 221, 657, 332, 658, 756, 106, 351, 433, 398, 355, 679, 338, 868, 889, 640, 852, 446, 661, 19, 677, 512, 467, 932, 427, 609, 449, 137, 987, 81, 685, 890, 815, 637, 100, 554, 820, 410, 212, 519, 766, 176, 607, 621, 940, 790, 884, 563, 109, 6, 402, 101, 579, 930, 169, 909, 236, 659, 540, 299, 227, 337, 831, 811, 871, 535, 290, 171, 556, 162, 747, 443, 119, 998, 804, 128, 173, 548, 735, 239, 388, 492, 406, 688, 840, 1007, 141, 127, 354, 577, 204, 326, 574, 385, 164, 590, 863, 886, 253, 22, 629, 174, 526, 904, 675, 974, 771, 948, 834, 578, 356, 619, 805, 734, 300, 885, 828, 737, 692, 15, 939, 125, 34, 586, 718, 420, 138, 387, 183, 845, 79, 703, 550, 455, 879, 560, 601, 836, 569, 959, 46, 936, 188, 168, 139, 478, 534, 934, 376, 634, 258, 618, 786, 235, 1021, 971, 78, 671, 178, 966, 379, 133, 775, 391, 902, 493, 589, 47, 638, 897) | | runtime filters: RF000 -> 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 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+