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: 64<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 30047.0: task 64611))<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 30049.0: task 64612))<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 30049.0: task 64612))<br>remote merged blocks fetched: 0<br>records read: 64<br>local bytes read total (min, med, max (stageId: taskId))<br>13.5 KiB (6.7 KiB, 6.9 KiB, 6.9 KiB (stage 30049.0: task 64613))<br>fetch wait time total (min, med, max (stageId: taskId))<br>0 ms (0 ms, 0 ms, 0 ms (stage 30049.0: task 64612))<br>remote bytes read total (min, med, max (stageId: taskId))<br>0.0 B (0.0 B, 0.0 B, 0.0 B (stage 30049.0: task 64612))<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>36.8 KiB (0.0 B, 0.0 B, 36.8 KiB (stage 30047.0: task 64611))<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 30049.0: task 64612))<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 30049.0: task 64612))<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 30049.0: task 64612))<br>shuffle bytes written total (min, med, max (stageId: taskId))<br>13.5 KiB (0.0 B, 0.0 B, 13.5 KiB (stage 30047.0: task 64611))"];
subgraph cluster3 {
isCluster="true";
label="WholeStageCodegen (1)\n \nduration: total (min, med, max (stageId: taskId))\n1 ms (0 ms, 0 ms, 1 ms (stage 30047.0: task 64611))";
4 [labelType="html" label="<br><b>Project</b><br><br>"];
}
5 [labelType="html" label="<b>InMemoryTableScan</b><br><br>number of output rows: 64"];
subgraph cluster6 {
isCluster="true";
label="WholeStageCodegen (1)\n \nduration: total (min, med, max (stageId: taskId))\n0 ms (0 ms, 0 ms, 0 ms (stage 30047.0: task 64611))";
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#12896491.toString, brand_context_id#12896487.toString, company_id#12896488.toString, launch_config_ref#12897425.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#12896490, true, false, true), config_name#12897426.toString, config_version#12897427.toString, entity_id#12896494.toString, Direct_Supplier_Name#12897428.toString, SupplierUID#12897429.toString, submission_status#12896495.toString, staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest#12896496, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest_submitted#12897430, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_editable#12896497, true, false, true), launch_date#12896498.toString, expiry_date#12896499.toString, submitted_date#12896500.toString, staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, create_ts#12896501, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, update_ts#12896502, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, create_date#12896503, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, update_date#12896504, true, false, true), source#12896505.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, recurrence_count#12896506, true, false, true), priority#12896507.toString, ... 36 more fields), obj#12898782: org.apache.spark.sql.Row
Exchange RoundRobinPartitioning(2), REPARTITION_BY_NUM, [plan_id=4044293]
Project [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, coalesce(launch_config_ref#12896489, ) AS launch_config_ref#12897425, revision_version#12896490, CASE WHEN (trim(regexp_replace(regexp_replace(config_name#12896492, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(config_name#12896492, , , 1), {2,}, , 1), None) END AS config_name#12897426, CASE WHEN isnotnull(config_version#12896493) THEN cast(config_version#12896493 as string) END AS config_version#12897427, entity_id#12896494, direct_supplier_name#12896543 AS Direct_Supplier_Name#12897428, supplier_uid#12896512 AS SupplierUID#12897429, submission_status#12896495, is_latest#12896496, false AS is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, ... 6 more fields]
WholeStageCodegen (1)
InMemoryTableScan [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, config_name#12896492, config_version#12896493, create_date#12896503, create_ts#12896501, direct_supplier_name#12896543, entity_id#12896494, expiry_date#12896499, is_editable#12896497, is_latest#12896496, latest_source#12896509, launch_config_ref#12896489, launch_date#12896498, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, priority#12896507, recurrence_count#12896506, remarks#12896508, revision_version#12896490, source#12896505, submission_status#12896495, ... 4 more fields]
Project [brand_context_id#12896487, company_id#12896488, launch_config_ref#12896489, revision_version#12896490, NetworkID#12896491, config_name#12896492, config_version#12896493, entity_id#12896494, submission_status#12896495, is_latest#12896496, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ... 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#12896487,company_id#12896488,launch_config_ref#12896489,revision_version#12896490,NetworkID#12896491,config_name#12896492,config_version#12896493,entity_id#12896494,submission_status#12896495,is_latest#12896496,is_editable#12896497,launch_date#12896498,expiry_date#12896499,submitted_date#12896500,create_ts#12896501,update_ts#12896502,create_date#12896503,update_date#12896504,source#12896505,recurrence_count#12896506,priority#12896507,remarks#12896508,latest_source#12896509,launch_request_name#12896510,... 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=36.8 KiB, rowCount=64)
+- Exchange (7)
+- * Project (6)
+- TableCacheQueryStage (5), Statistics(sizeInBytes=25.1 KiB, rowCount=64)
+- 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#12896491, brand_context_id#12896487, company_id#12896488, config_name#12896492, config_version#12896493, create_date#12896503, create_ts#12896501, direct_supplier_name#12896543, entity_id#12896494, expiry_date#12896499, is_editable#12896497, is_latest#12896496, latest_source#12896509, launch_config_ref#12896489, launch_date#12896498, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, priority#12896507, recurrence_count#12896506, remarks#12896508, revision_version#12896490, source#12896505, submission_status#12896495, submitted_date#12896500, supplier_uid#12896512, update_date#12896504, update_ts#12896502]
Arguments: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, config_name#12896492, config_version#12896493, create_date#12896503, create_ts#12896501, direct_supplier_name#12896543, entity_id#12896494, expiry_date#12896499, is_editable#12896497, is_latest#12896496, latest_source#12896509, launch_config_ref#12896489, launch_date#12896498, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, priority#12896507, recurrence_count#12896506, remarks#12896508, revision_version#12896490, source#12896505, submission_status#12896495, ... 4 more fields]
(2) InMemoryRelation
Arguments: [brand_context_id#12896487, company_id#12896488, launch_config_ref#12896489, revision_version#12896490, NetworkID#12896491, config_name#12896492, config_version#12896493, entity_id#12896494, submission_status#12896495, is_latest#12896496, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ... 4 more fields], CachedRDDBuilder(org.apache.spark.sql.execution.columnar.DefaultCachedBatchSerializer@730ae8bf,StorageLevel(disk, memory, deserialized, 1 replicas),*(1) Project [brand_context_id#12896487, company_id#12896488, launch_config_ref#12896489, revision_version#12896490, NetworkID#12896491, config_name#12896492, config_version#12896493, entity_id#12896494, submission_status#12896495, is_latest#12896496, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ... 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#12896487,company_id#12896488,launch_config_ref#12896489,revision_version#12896490,NetworkID#12896491,config_name#12896492,config_version#12896493,entity_id#12896494,submission_status#12896495,is_latest#12896496,is_editable#12896497,launch_date#12896498,expiry_date#12896499,submitted_date#12896500,create_ts#12896501,update_ts#12896502,create_date#12896503,update_date#12896504,source#12896505,recurrence_count#12896506,priority#12896507,remarks#12896508,latest_source#12896509,launch_request_name#12896510,... 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#12896487, company_id#12896488, launch_config_ref#12896489, revision_version#12896490, NetworkID#12896491, config_name#12896492, config_version#12896493, entity_id#12896494, submission_status#12896495, is_latest#12896496, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, direct_supplier_name#12896511, supplier_uid#12896512, ou_division#12896513, ou_location#12896514]
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#12896487, company_id#12896488, launch_config_ref#12896489, revision_version#12896490, NetworkID#12896491, config_name#12896492, config_version#12896493, entity_id#12896494, submission_status#12896495, is_latest#12896496, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, CASE WHEN (trim(regexp_replace(regexp_replace(direct_supplier_name#12896511, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(direct_supplier_name#12896511, , , 1), {2,}, , 1), None) END AS direct_supplier_name#12896543, supplier_uid#12896512, ou_division#12896513, ou_location#12896514]
Input [28]: [brand_context_id#12896487, company_id#12896488, launch_config_ref#12896489, revision_version#12896490, NetworkID#12896491, config_name#12896492, config_version#12896493, entity_id#12896494, submission_status#12896495, is_latest#12896496, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, direct_supplier_name#12896511, supplier_uid#12896512, ou_division#12896513, ou_location#12896514]
(5) TableCacheQueryStage
Output [28]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, config_name#12896492, config_version#12896493, create_date#12896503, create_ts#12896501, direct_supplier_name#12896543, entity_id#12896494, expiry_date#12896499, is_editable#12896497, is_latest#12896496, latest_source#12896509, launch_config_ref#12896489, launch_date#12896498, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, priority#12896507, recurrence_count#12896506, remarks#12896508, revision_version#12896490, source#12896505, submission_status#12896495, submitted_date#12896500, supplier_uid#12896512, update_date#12896504, update_ts#12896502]
Arguments: 0
(6) Project [codegen id : 1]
Output [30]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, coalesce(launch_config_ref#12896489, ) AS launch_config_ref#12897425, revision_version#12896490, CASE WHEN (trim(regexp_replace(regexp_replace(config_name#12896492, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(config_name#12896492, , , 1), {2,}, , 1), None) END AS config_name#12897426, CASE WHEN isnotnull(config_version#12896493) THEN cast(config_version#12896493 as string) END AS config_version#12897427, entity_id#12896494, direct_supplier_name#12896543 AS Direct_Supplier_Name#12897428, supplier_uid#12896512 AS SupplierUID#12897429, submission_status#12896495, is_latest#12896496, false AS is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, 2026-10-18 07:30:07.191883 AS ETL_runtime#12897431]
Input [28]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, config_name#12896492, config_version#12896493, create_date#12896503, create_ts#12896501, direct_supplier_name#12896543, entity_id#12896494, expiry_date#12896499, is_editable#12896497, is_latest#12896496, latest_source#12896509, launch_config_ref#12896489, launch_date#12896498, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, priority#12896507, recurrence_count#12896506, remarks#12896508, revision_version#12896490, source#12896505, submission_status#12896495, submitted_date#12896500, supplier_uid#12896512, update_date#12896504, update_ts#12896502]
(7) Exchange
Input [30]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, launch_config_ref#12897425, revision_version#12896490, config_name#12897426, config_version#12897427, entity_id#12896494, Direct_Supplier_Name#12897428, SupplierUID#12897429, submission_status#12896495, is_latest#12896496, is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, ETL_runtime#12897431]
Arguments: RoundRobinPartitioning(2), REPARTITION_BY_NUM, [plan_id=4044293]
(8) ShuffleQueryStage
Output [30]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, launch_config_ref#12897425, revision_version#12896490, config_name#12897426, config_version#12897427, entity_id#12896494, Direct_Supplier_Name#12897428, SupplierUID#12897429, submission_status#12896495, is_latest#12896496, is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, ETL_runtime#12897431]
Arguments: 1
(9) DeserializeToObject
Input [30]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, launch_config_ref#12897425, revision_version#12896490, config_name#12897426, config_version#12897427, entity_id#12896494, Direct_Supplier_Name#12897428, SupplierUID#12897429, submission_status#12896495, is_latest#12896496, is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, ETL_runtime#12897431]
Arguments: createexternalrow(NetworkID#12896491.toString, brand_context_id#12896487.toString, company_id#12896488.toString, launch_config_ref#12897425.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#12896490, true, false, true), config_name#12897426.toString, config_version#12897427.toString, entity_id#12896494.toString, Direct_Supplier_Name#12897428.toString, SupplierUID#12897429.toString, submission_status#12896495.toString, staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest#12896496, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest_submitted#12897430, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_editable#12896497, true, false, true), launch_date#12896498.toString, expiry_date#12896499.toString, submitted_date#12896500.toString, staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, create_ts#12896501, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, update_ts#12896502, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, create_date#12896503, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, update_date#12896504, true, false, true), source#12896505.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, recurrence_count#12896506, true, false, true), priority#12896507.toString, ... 36 more fields), obj#12898782: org.apache.spark.sql.Row
(10) Project
Output [30]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, coalesce(launch_config_ref#12896489, ) AS launch_config_ref#12897425, revision_version#12896490, CASE WHEN (trim(regexp_replace(regexp_replace(config_name#12896492, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(config_name#12896492, , , 1), {2,}, , 1), None) END AS config_name#12897426, CASE WHEN isnotnull(config_version#12896493) THEN cast(config_version#12896493 as string) END AS config_version#12897427, entity_id#12896494, direct_supplier_name#12896543 AS Direct_Supplier_Name#12897428, supplier_uid#12896512 AS SupplierUID#12897429, submission_status#12896495, is_latest#12896496, false AS is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, 2026-10-18 07:30:07.191883 AS ETL_runtime#12897431]
Input [28]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, config_name#12896492, config_version#12896493, create_date#12896503, create_ts#12896501, direct_supplier_name#12896543, entity_id#12896494, expiry_date#12896499, is_editable#12896497, is_latest#12896496, latest_source#12896509, launch_config_ref#12896489, launch_date#12896498, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, priority#12896507, recurrence_count#12896506, remarks#12896508, revision_version#12896490, source#12896505, submission_status#12896495, submitted_date#12896500, supplier_uid#12896512, update_date#12896504, update_ts#12896502]
(11) Exchange
Input [30]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, launch_config_ref#12897425, revision_version#12896490, config_name#12897426, config_version#12897427, entity_id#12896494, Direct_Supplier_Name#12897428, SupplierUID#12897429, submission_status#12896495, is_latest#12896496, is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, ETL_runtime#12897431]
Arguments: RoundRobinPartitioning(2), REPARTITION_BY_NUM, [plan_id=4044266]
(12) DeserializeToObject
Input [30]: [NetworkID#12896491, brand_context_id#12896487, company_id#12896488, launch_config_ref#12897425, revision_version#12896490, config_name#12897426, config_version#12897427, entity_id#12896494, Direct_Supplier_Name#12897428, SupplierUID#12897429, submission_status#12896495, is_latest#12896496, is_latest_submitted#12897430, is_editable#12896497, launch_date#12896498, expiry_date#12896499, submitted_date#12896500, create_ts#12896501, update_ts#12896502, create_date#12896503, update_date#12896504, source#12896505, recurrence_count#12896506, priority#12896507, remarks#12896508, latest_source#12896509, launch_request_name#12896510, ou_division#12896513, ou_location#12896514, ETL_runtime#12897431]
Arguments: createexternalrow(NetworkID#12896491.toString, brand_context_id#12896487.toString, company_id#12896488.toString, launch_config_ref#12897425.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#12896490, true, false, true), config_name#12897426.toString, config_version#12897427.toString, entity_id#12896494.toString, Direct_Supplier_Name#12897428.toString, SupplierUID#12897429.toString, submission_status#12896495.toString, staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest#12896496, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_latest_submitted#12897430, true, false, true), staticinvoke(class java.lang.Boolean, ObjectType(class java.lang.Boolean), valueOf, is_editable#12896497, true, false, true), launch_date#12896498.toString, expiry_date#12896499.toString, submitted_date#12896500.toString, staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, create_ts#12896501, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Timestamp), toJavaTimestamp, update_ts#12896502, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, create_date#12896503, true, false, true), staticinvoke(class org.apache.spark.sql.catalyst.util.DateTimeUtils$, ObjectType(class java.sql.Date), toJavaDate, update_date#12896504, true, false, true), source#12896505.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, recurrence_count#12896506, true, false, true), priority#12896507.toString, ... 36 more fields), obj#12898782: org.apache.spark.sql.Row
(13) AdaptiveSparkPlan
Output [1]: [obj#12898782]
Arguments: isFinalPlan=true