Parent schema and table

[tsinober]

Hi there,
A quick one,
I got the following sql statement:
"select t1.col1,t2.col2,t3.col3 from" + table1 + "t1 join" + table2 + "t2 on t1.id = t2.id join (select col1,col2 from" + table3 +
"where id = 11) t3 on t2.id=t3.id"
I can successfully retrieve the column and table name using sqlglot.
However i was wondering if i can obtain the parent table for each column and if possible the parent schema (not in the case of the sql above). Basically i'm looking for some hierarchical structure that contains it.
An example code would be much appreciated.

Thanks!

[georgesittas]

What does "parent table" mean in this context? Do you mean something like extracting the lineage for a column?

[tsinober]

@georgesittas thanks for the quick response.

I guess so, my goal is to extract an hirarchical structure which contains the schema (if exsits), tables and columns for a singel statement.

[georgesittas]

Could you show me a concrete example of what your use case is?

[tsinober]

Certainly,
"select t1.col1,t2.col2,t3.col3 from" + table1 + "t1 join" + table2 + "t2 on t1.id = t2.id join (select ext_col from" + table3 +
"where id = 11) t3 on t2.id=t3.id"
I would like to get somthing like {table1:[col1,id], table2: [id,col2], table3: [col3, id, ext_col]}

[georgesittas]

I see, thanks for clarifying.

You can't fully solve this problem unless all columns are qualified. If all of them are qualified, then perhaps SQLGlot's scope module could be useful here. I think you'd basically need to traverse all scopes and maintain / update a dict of table name -> set of column names. The way to extract this information is you want to search for all columns in scope (e.g. through the find_all method) and then add them to the dict using their table arg as the key, thus updating the schema as you go.

It might be a bit more nuanced if you have CTEs etc that shadow actual table names, or if you have aliased tables (you need to map the aliases back to the actual / original table names so that the keys in the dict correspond to the real tables).