How to replace aliases in a query

[AdrianBZG]

Hi,

Is it possible to remove aliases in a query automatically with sqlglot?

For example, given query:

SELECT T3.born_state FROM department AS T1 JOIN management AS T2 ON T1.department_id = T2.department_id JOIN head AS T3 ON T2.head_id = T3.head_id WHERE T1.name = 'Treasury' INTERSECT SELECT T3.born_state FROM department AS T1 JOIN management AS T2 ON T1.department_id = T2.department_id JOIN head AS T3 ON T2.head_id = T3.head_id WHERE T1.name = 'Homeland Security'

I want it to rewrite the query to not have any "AS".

Thanks.

[barakalon]

Here is a naive approach:

from sqlglot import parse_one, exp

q = """
SELECT
  T3.born_state
FROM department AS T1
JOIN management AS T2
  ON T1.department_id = T2.department_id
JOIN head AS T3
  ON T2.head_id = T3.head_id
WHERE
  T1.name = 'Treasury'
INTERSECT
SELECT
  T3.born_state
FROM department AS T1
JOIN management AS T2
  ON T1.department_id = T2.department_id
JOIN head AS T3
  ON T2.head_id = T3.head_id
WHERE
  T1.name = 'Homeland Security'
"""

ast = parse_one(q)
for alias in ast.find_all(exp.Alias):
    alias.replace(alias.this)
for alias in ast.find_all(exp.TableAlias):
    alias.pop()
for column in ast.find_all(exp.Column):
    column.set("table", None)

print(ast.sql(pretty=True))

SELECT
  born_state
FROM department
JOIN management
  ON department_id = department_id
JOIN head
  ON head_id = head_id
WHERE
  name = 'Treasury'
INTERSECT
SELECT
  born_state
FROM department
JOIN management
  ON department_id = department_id
JOIN head
  ON head_id = head_id
WHERE
  name = 'Homeland Security'

Now, of course, the result is not a valid query. This leaves ambiguous columns (e.g. department_id). It also can change the names of output columns, which would break any subqueries.

To do this safely, you'd want to use traverse_scope. This returns Scope objects in "depth-first postorder". This means it starts at the leaves of the query tree.

Scope has methods for tracing query "sources" to all their columns (see Scope.selected_sources and Scope.source_columns. You'd need to remove aliases from sources and rename the tables of columns as you go.

And if you want to remove out column aliases and you might have subqueries, you'd need to propagate name changes up the scope tree.

You can look in sqlglot.optimizer for examples of using scope. Happy to chat more if you need help.