digraph G {
0 [labelType="html" label="<br><b>AdaptiveSparkPlan</b><br><br>"];
1 [labelType="html" label="<br><b>DeserializeToObject</b><br><br>"];
2 [labelType="html" label="<b>Exchange</b><br><br>shuffle records written: 20<br>local merged chunks fetched: 0<br>shuffle write time total (min, med, max (stageId: taskId))<br>0 ms (0 ms, 0 ms, 0 ms (stage 37517.0: task 71884))<br>remote merged bytes read total (min, med, max (stageId: taskId))<br>0.0 B (0.0 B, 0.0 B, 0.0 B (stage 37519.0: task 71885))<br>local merged blocks fetched: 0<br>corrupt merged block chunks: 0<br>remote merged reqs duration total (min, med, max (stageId: taskId))<br>0 ms (0 ms, 0 ms, 0 ms (stage 37519.0: task 71885))<br>remote merged blocks fetched: 0<br>records read: 20<br>local bytes read total (min, med, max (stageId: taskId))<br>6.1 KiB (2.9 KiB, 3.2 KiB, 3.2 KiB (stage 37519.0: task 71886))<br>fetch wait time total (min, med, max (stageId: taskId))<br>0 ms (0 ms, 0 ms, 0 ms (stage 37519.0: task 71885))<br>remote bytes read total (min, med, max (stageId: taskId))<br>0.0 B (0.0 B, 0.0 B, 0.0 B (stage 37519.0: task 71885))<br>merged fetch fallback count: 0<br>local blocks read: 2<br>remote merged chunks fetched: 0<br>remote blocks read: 0<br>data size total (min, med, max (stageId: taskId))<br>11.9 KiB (0.0 B, 0.0 B, 11.9 KiB (stage 37517.0: task 71884))<br>local merged bytes read total (min, med, max (stageId: taskId))<br>0.0 B (0.0 B, 0.0 B, 0.0 B (stage 37519.0: task 71885))<br>number of partitions: 2<br>remote reqs duration total (min, med, max (stageId: taskId))<br>0 ms (0 ms, 0 ms, 0 ms (stage 37519.0: task 71885))<br>remote bytes read to disk total (min, med, max (stageId: taskId))<br>0.0 B (0.0 B, 0.0 B, 0.0 B (stage 37519.0: task 71885))<br>shuffle bytes written total (min, med, max (stageId: taskId))<br>6.1 KiB (0.0 B, 0.0 B, 6.1 KiB (stage 37517.0: task 71884))"];
subgraph cluster3 {
isCluster="true";
label="WholeStageCodegen (1)\n \nduration: total (min, med, max (stageId: taskId))\n0 ms (0 ms, 0 ms, 0 ms (stage 37517.0: task 71884))";
4 [labelType="html" label="<br><b>Project</b><br><br>"];
}
5 [labelType="html" label="<b>InMemoryTableScan</b><br><br>number of output rows: 20"];
subgraph cluster6 {
isCluster="true";
label="WholeStageCodegen (1)\n \nduration: total (min, med, max (stageId: taskId))\n0 ms (0 ms, 0 ms, 0 ms (stage 37517.0: task 71884))";
7 [labelType="html" label="<br><b>Project</b><br><br>"];
8 [labelType="html" label="<b>Scan JDBCRelation((<br> SELECT<br> rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,<br> rs.networkId AS NetworkID,<br> rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,<br> rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,<br> rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,<br> rs.latest_source, rs.launch_request_name,<br> prof.profile_supplier_name AS direct_supplier_name,<br> prof.profile_supplierUID AS supplier_uid,<br> ou.ou_division, ou.ou_location<br> FROM dbo.Datahub_SCM_Network_Load_Queue inc<br> INNER JOIN dbo.dimscm_response_summary rs<br> ON rs.networkId = inc.NetworkID<br> OUTER APPLY (<br> SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID<br> FROM dbo.dimsupplier_profilemerge p<br> WHERE p.brandid = inc.brand_context_id<br> AND p.supplier_id = inc.company_id<br> ORDER BY p.profile_supplierUID<br> ) prof<br> LEFT JOIN (<br> SELECT<br> d.networkId,<br> STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,<br> STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location<br> FROM (<br> SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value<br> FROM dbo.Datahub_SCM_Network_Load_Queue q<br> INNER JOIN dbo.dimscm_response_summary_ou_attribute oa<br> ON oa.networkId = q.NetworkID<br> WHERE q.status = 'PENDING'<br> AND oa.ou_attribute_id IN ('ou_division', 'ou_location')<br> ) d<br> GROUP BY d.networkId<br> ) ou<br> ON ou.networkId = rs.networkId<br> WHERE inc.status = 'PENDING'<br> AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')<br> AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))<br> ) AS _t) [numPartitions=1] </b><br><br>number of output rows: 0"];
}
1->0;
2->1;
4->2;
5->4;
7->5;
8->7;
}
9
AdaptiveSparkPlan isFinalPlan=true
DeserializeToObject createexternalrow(NetworkID#15205026.toString, brand_context_id#15205022.toString, company_id#15205023.toString, launch_config_ref#15205960.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#15205025, true, false, true), config_name#15205961.toString, config_version#15205962.toString, entity_id#15205029.toString, Direct_Supplier_Name#15205963.toString, SupplierUID#15205964.toString, submission_status#15205030.toString, staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest#15205031, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest_submitted#15205965, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_editable#15205032, true, false, true), launch_date#15205033.toString, expiry_date#15205034.toString, submitted_date#15205035.toString, staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, create_ts#15205036, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, update_ts#15205037, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, create_date#15205038, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, update_date#15205039, true, false, true), source#15205040.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, recurrence_count#15205041, true, false, true), priority#15205042.toString, ... 36 more fields), obj#15207317: org.apache.spark.sql.Row
Exchange RoundRobinPartitioning(2), REPARTITION_BY_NUM, [plan_id=4309032]
Project [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, coalesce(launch_config_ref#15205024, ) AS launch_config_ref#15205960, revision_version#15205025, CASE WHEN (trim(regexp_replace(regexp_replace(config_name#15205027, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(config_name#15205027, , , 1), {2,}, , 1), None) END AS config_name#15205961, CASE WHEN isnotnull(config_version#15205028) THEN cast(config_version#15205028 as string) END AS config_version#15205962, entity_id#15205029, direct_supplier_name#15205078 AS Direct_Supplier_Name#15205963, supplier_uid#15205047 AS SupplierUID#15205964, submission_status#15205030, is_latest#15205031, false AS is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, ... 6 more fields]
WholeStageCodegen (1)
InMemoryTableScan [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, config_name#15205027, config_version#15205028, create_date#15205038, create_ts#15205036, direct_supplier_name#15205078, entity_id#15205029, expiry_date#15205034, is_editable#15205032, is_latest#15205031, latest_source#15205044, launch_config_ref#15205024, launch_date#15205033, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, priority#15205042, recurrence_count#15205041, remarks#15205043, revision_version#15205025, source#15205040, submission_status#15205030, ... 4 more fields]
Project [brand_context_id#15205022, company_id#15205023, launch_config_ref#15205024, revision_version#15205025, NetworkID#15205026, config_name#15205027, config_version#15205028, entity_id#15205029, submission_status#15205030, is_latest#15205031, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ... 4 more fields]
Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#15205022,company_id#15205023,launch_config_ref#15205024,revision_version#15205025,NetworkID#15205026,config_name#15205027,config_version#15205028,entity_id#15205029,submission_status#15205030,is_latest#15205031,is_editable#15205032,launch_date#15205033,expiry_date#15205034,submitted_date#15205035,create_ts#15205036,update_ts#15205037,create_date#15205038,update_date#15205039,source#15205040,recurrence_count#15205041,priority#15205042,remarks#15205043,latest_source#15205044,launch_request_name#15205045,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
WholeStageCodegen (1)
== Physical Plan ==
AdaptiveSparkPlan (13)
+- == Final Plan ==
DeserializeToObject (9)
+- ShuffleQueryStage (8), Statistics(sizeInBytes=11.9 KiB, rowCount=20)
+- Exchange (7)
+- * Project (6)
+- TableCacheQueryStage (5), Statistics(sizeInBytes=8.3 KiB, rowCount=20)
+- InMemoryTableScan (1)
+- InMemoryRelation (2)
+- * Project (4)
+- * Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] (3)
+- == Initial Plan ==
DeserializeToObject (12)
+- Exchange (11)
+- Project (10)
+- InMemoryTableScan (1)
+- InMemoryRelation (2)
+- * Project (4)
+- * Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] (3)
(1) InMemoryTableScan
Output [28]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, config_name#15205027, config_version#15205028, create_date#15205038, create_ts#15205036, direct_supplier_name#15205078, entity_id#15205029, expiry_date#15205034, is_editable#15205032, is_latest#15205031, latest_source#15205044, launch_config_ref#15205024, launch_date#15205033, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, priority#15205042, recurrence_count#15205041, remarks#15205043, revision_version#15205025, source#15205040, submission_status#15205030, submitted_date#15205035, supplier_uid#15205047, update_date#15205039, update_ts#15205037]
Arguments: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, config_name#15205027, config_version#15205028, create_date#15205038, create_ts#15205036, direct_supplier_name#15205078, entity_id#15205029, expiry_date#15205034, is_editable#15205032, is_latest#15205031, latest_source#15205044, launch_config_ref#15205024, launch_date#15205033, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, priority#15205042, recurrence_count#15205041, remarks#15205043, revision_version#15205025, source#15205040, submission_status#15205030, ... 4 more fields]
(2) InMemoryRelation
Arguments: [brand_context_id#15205022, company_id#15205023, launch_config_ref#15205024, revision_version#15205025, NetworkID#15205026, config_name#15205027, config_version#15205028, entity_id#15205029, submission_status#15205030, is_latest#15205031, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ... 4 more fields], CachedRDDBuilder(org.apache.spark.sql.execution.columnar.DefaultCachedBatchSerializer@730ae8bf,StorageLevel(disk, memory, deserialized, 1 replicas),*(1) Project [brand_context_id#15205022, company_id#15205023, launch_config_ref#15205024, revision_version#15205025, NetworkID#15205026, config_name#15205027, config_version#15205028, entity_id#15205029, submission_status#15205030, is_latest#15205031, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ... 4 more fields]
+- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#15205022,company_id#15205023,launch_config_ref#15205024,revision_version#15205025,NetworkID#15205026,config_name#15205027,config_version#15205028,entity_id#15205029,submission_status#15205030,is_latest#15205031,is_editable#15205032,launch_date#15205033,expiry_date#15205034,submitted_date#15205035,create_ts#15205036,update_ts#15205037,create_date#15205038,update_date#15205039,source#15205040,recurrence_count#15205041,priority#15205042,remarks#15205043,latest_source#15205044,launch_request_name#15205045,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
,None)
(3) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [codegen id : 1]
Output [28]: [brand_context_id#15205022, company_id#15205023, launch_config_ref#15205024, revision_version#15205025, NetworkID#15205026, config_name#15205027, config_version#15205028, entity_id#15205029, submission_status#15205030, is_latest#15205031, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, direct_supplier_name#15205046, supplier_uid#15205047, ou_division#15205048, ou_location#15205049]
ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smallint,NetworkID:string,config_name:string,config_version:boolean,entity_id:string,submission_status:string,is_latest:boolean,is_editable:boolean,launch_date:string,expiry_date:string,submitted_date:string,create_ts:timestamp,update_ts:timestamp,create_date:date,update_date:date,source:string,recurrence_count:int,priority:string,remarks:string,latest_source:string,launch_request_name:string,direct_supplier_name:string,supplier_uid:string,ou_division:string,ou_location:string>
(4) Project [codegen id : 1]
Output [28]: [brand_context_id#15205022, company_id#15205023, launch_config_ref#15205024, revision_version#15205025, NetworkID#15205026, config_name#15205027, config_version#15205028, entity_id#15205029, submission_status#15205030, is_latest#15205031, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, CASE WHEN (trim(regexp_replace(regexp_replace(direct_supplier_name#15205046, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(direct_supplier_name#15205046, , , 1), {2,}, , 1), None) END AS direct_supplier_name#15205078, supplier_uid#15205047, ou_division#15205048, ou_location#15205049]
Input [28]: [brand_context_id#15205022, company_id#15205023, launch_config_ref#15205024, revision_version#15205025, NetworkID#15205026, config_name#15205027, config_version#15205028, entity_id#15205029, submission_status#15205030, is_latest#15205031, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, direct_supplier_name#15205046, supplier_uid#15205047, ou_division#15205048, ou_location#15205049]
(5) TableCacheQueryStage
Output [28]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, config_name#15205027, config_version#15205028, create_date#15205038, create_ts#15205036, direct_supplier_name#15205078, entity_id#15205029, expiry_date#15205034, is_editable#15205032, is_latest#15205031, latest_source#15205044, launch_config_ref#15205024, launch_date#15205033, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, priority#15205042, recurrence_count#15205041, remarks#15205043, revision_version#15205025, source#15205040, submission_status#15205030, submitted_date#15205035, supplier_uid#15205047, update_date#15205039, update_ts#15205037]
Arguments: 0
(6) Project [codegen id : 1]
Output [30]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, coalesce(launch_config_ref#15205024, ) AS launch_config_ref#15205960, revision_version#15205025, CASE WHEN (trim(regexp_replace(regexp_replace(config_name#15205027, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(config_name#15205027, , , 1), {2,}, , 1), None) END AS config_name#15205961, CASE WHEN isnotnull(config_version#15205028) THEN cast(config_version#15205028 as string) END AS config_version#15205962, entity_id#15205029, direct_supplier_name#15205078 AS Direct_Supplier_Name#15205963, supplier_uid#15205047 AS SupplierUID#15205964, submission_status#15205030, is_latest#15205031, false AS is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, 2026-10-19 07:30:06.14862 AS ETL_runtime#15205966]
Input [28]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, config_name#15205027, config_version#15205028, create_date#15205038, create_ts#15205036, direct_supplier_name#15205078, entity_id#15205029, expiry_date#15205034, is_editable#15205032, is_latest#15205031, latest_source#15205044, launch_config_ref#15205024, launch_date#15205033, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, priority#15205042, recurrence_count#15205041, remarks#15205043, revision_version#15205025, source#15205040, submission_status#15205030, submitted_date#15205035, supplier_uid#15205047, update_date#15205039, update_ts#15205037]
(7) Exchange
Input [30]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, launch_config_ref#15205960, revision_version#15205025, config_name#15205961, config_version#15205962, entity_id#15205029, Direct_Supplier_Name#15205963, SupplierUID#15205964, submission_status#15205030, is_latest#15205031, is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, ETL_runtime#15205966]
Arguments: RoundRobinPartitioning(2), REPARTITION_BY_NUM, [plan_id=4309032]
(8) ShuffleQueryStage
Output [30]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, launch_config_ref#15205960, revision_version#15205025, config_name#15205961, config_version#15205962, entity_id#15205029, Direct_Supplier_Name#15205963, SupplierUID#15205964, submission_status#15205030, is_latest#15205031, is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, ETL_runtime#15205966]
Arguments: 1
(9) DeserializeToObject
Input [30]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, launch_config_ref#15205960, revision_version#15205025, config_name#15205961, config_version#15205962, entity_id#15205029, Direct_Supplier_Name#15205963, SupplierUID#15205964, submission_status#15205030, is_latest#15205031, is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, ETL_runtime#15205966]
Arguments: createexternalrow(NetworkID#15205026.toString, brand_context_id#15205022.toString, company_id#15205023.toString, launch_config_ref#15205960.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#15205025, true, false, true), config_name#15205961.toString, config_version#15205962.toString, entity_id#15205029.toString, Direct_Supplier_Name#15205963.toString, SupplierUID#15205964.toString, submission_status#15205030.toString, staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest#15205031, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest_submitted#15205965, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_editable#15205032, true, false, true), launch_date#15205033.toString, expiry_date#15205034.toString, submitted_date#15205035.toString, staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, create_ts#15205036, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, update_ts#15205037, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, create_date#15205038, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, update_date#15205039, true, false, true), source#15205040.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, recurrence_count#15205041, true, false, true), priority#15205042.toString, ... 36 more fields), obj#15207317: org.apache.spark.sql.Row
(10) Project
Output [30]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, coalesce(launch_config_ref#15205024, ) AS launch_config_ref#15205960, revision_version#15205025, CASE WHEN (trim(regexp_replace(regexp_replace(config_name#15205027, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(config_name#15205027, , , 1), {2,}, , 1), None) END AS config_name#15205961, CASE WHEN isnotnull(config_version#15205028) THEN cast(config_version#15205028 as string) END AS config_version#15205962, entity_id#15205029, direct_supplier_name#15205078 AS Direct_Supplier_Name#15205963, supplier_uid#15205047 AS SupplierUID#15205964, submission_status#15205030, is_latest#15205031, false AS is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, 2026-10-19 07:30:06.14862 AS ETL_runtime#15205966]
Input [28]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, config_name#15205027, config_version#15205028, create_date#15205038, create_ts#15205036, direct_supplier_name#15205078, entity_id#15205029, expiry_date#15205034, is_editable#15205032, is_latest#15205031, latest_source#15205044, launch_config_ref#15205024, launch_date#15205033, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, priority#15205042, recurrence_count#15205041, remarks#15205043, revision_version#15205025, source#15205040, submission_status#15205030, submitted_date#15205035, supplier_uid#15205047, update_date#15205039, update_ts#15205037]
(11) Exchange
Input [30]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, launch_config_ref#15205960, revision_version#15205025, config_name#15205961, config_version#15205962, entity_id#15205029, Direct_Supplier_Name#15205963, SupplierUID#15205964, submission_status#15205030, is_latest#15205031, is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, ETL_runtime#15205966]
Arguments: RoundRobinPartitioning(2), REPARTITION_BY_NUM, [plan_id=4309005]
(12) DeserializeToObject
Input [30]: [NetworkID#15205026, brand_context_id#15205022, company_id#15205023, launch_config_ref#15205960, revision_version#15205025, config_name#15205961, config_version#15205962, entity_id#15205029, Direct_Supplier_Name#15205963, SupplierUID#15205964, submission_status#15205030, is_latest#15205031, is_latest_submitted#15205965, is_editable#15205032, launch_date#15205033, expiry_date#15205034, submitted_date#15205035, create_ts#15205036, update_ts#15205037, create_date#15205038, update_date#15205039, source#15205040, recurrence_count#15205041, priority#15205042, remarks#15205043, latest_source#15205044, launch_request_name#15205045, ou_division#15205048, ou_location#15205049, ETL_runtime#15205966]
Arguments: createexternalrow(NetworkID#15205026.toString, brand_context_id#15205022.toString, company_id#15205023.toString, launch_config_ref#15205960.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#15205025, true, false, true), config_name#15205961.toString, config_version#15205962.toString, entity_id#15205029.toString, Direct_Supplier_Name#15205963.toString, SupplierUID#15205964.toString, submission_status#15205030.toString, staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest#15205031, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest_submitted#15205965, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_editable#15205032, true, false, true), launch_date#15205033.toString, expiry_date#15205034.toString, submitted_date#15205035.toString, staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, create_ts#15205036, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, update_ts#15205037, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, create_date#15205038, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, update_date#15205039, true, false, true), source#15205040.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, recurrence_count#15205041, true, false, true), priority#15205042.toString, ... 36 more fields), obj#15207317: org.apache.spark.sql.Row
(13) AdaptiveSparkPlan
Output [1]: [obj#15207317]
Arguments: isFinalPlan=true