How to handle parent child records in Google big query

Question

I have below set of data (Data for conversion)

| ITEM | PARENT_ITEM |
| -----| ------------|
| AA   | AB          |
| AB   | AC          |
| AC   | AD          |
| BA   | BB          |
| BB   | BC          |
| FG   | GG          |

I want to transform above set of data to below set

| ITEM | PARENT_ITEM    | FINAL_PARENT |  
| -----| -------------- | -------------| 
| AA   | AB             | AD           | 
| AB   | AC             | AD           | 
| AC   | AD             | AD           | 
| BA   | BB             | BC           | 
| BB   | BC             | BC           | 
| FG   | GG             | GG           | 

I found out that Big query doesn't support recursive sql. I wrote Stored Procedure but I cant call SP within standard SQL. Can someone help?

There could any level of depth in hierarchy and i dont want to hard code the depth.

Abhijeet

Answer 1

BigQuery Team just introduced Recursive CTE! Hooray!!

With recursive cte you can use below approach

with recursive iterations as (
  select *, parent_item as final_parent from your_table 
  where not parent_item in (
    select item from your_table
  )
  union all 
  select b.*, a.final_parent
  from iterations a join your_table b
  on a.item = b.parent_item
)
select *
from iterations

If applied to sample data in your question - output is

If you want to test it with dummy data - use below example

with recursive your_table AS ( 
  select 'AA' as ITEM, 'AB' as PARENT_ITEM union all 
  select 'AB' as ITEM, 'AC' as PARENT_ITEM union all 
  select 'AC' as ITEM, 'AD' as PARENT_ITEM union all 
  select 'BA' as ITEM, 'BB' as PARENT_ITEM union all 
  select 'BB' as ITEM, 'BC' as PARENT_ITEM 
),iterations as (
  select *, parent_item as final_parent from your_table 
  where not parent_item in (
    select item from your_table
  )
  union all 
  select b.*, a.final_parent
  from iterations a join your_table b
  on a.item = b.parent_item
)
select *
from iterations