Hierarchy Queries in BigQuery

Question

I have a data from ControlM (scheduler) this way

PREDECESSOR_JOB_ID,SUCCESSOR_JOB_ID
XYZ,ABC
ABC,LMN
ABC,PQR
XYZ,EFG
EFG,STU
STU,TUV
STU,VWX
.
.
.

I saw many solutions where they have started from first node and descended to last node. But that is likely to fan-out with this data. What I am looking for a fan-in solution, where I can start with any end node and come towards first node

In this case the answer that am looking for is

ABC-->XYZ
LMN-->ABC-->XYZ
PQR-->ABC-->XYZ
EFG-->XYZ
STU-->EFG-->XYZ
TUV-->STU-->EFG-->XYZ
VWX-->STU-->EFG-->XYZ

Answer 1

Consider below approach

with recursive iterations as (
  select successor_job_id, predecessor_job_id, 1 pos from your_table
  union all 
  select b.successor_job_id, a.predecessor_job_id, pos + 1 
  from your_table a join iterations b
  on b.predecessor_job_id = a.successor_job_id 
)
select successor_job_id || '-->' || string_agg(predecessor_job_id, '-->' order by pos) as jobs_path
from iterations
where not successor_job_id is null
group by successor_job_id             

if applied to sample data in your question - output is