Resolve the ambiguous columns in SQL query

Given the following SQL:

select ename
from emp, dept
where emp.deptid = dept.id

Column ename in the first line is not qualified by table name emp and how can we know which table this column belongs to?

Solution 1: Using table DDL

There are two ways to use the table DDL to eliminate the ambiguity.

• The first way is to put the DDL before the above SQL statement in the same SQL file and the column ename will be linked to the table emp correctly.

create table emp(
    id int,
    ename char(50),
    deptid int
);

create table dept(
    id int,
    dname char(50)
);

• The second way is putting the DDL file under the same folder as the sql files and using the /d flag to explicitly give the path of the DDL file directory.

java -jar gudusoft.dlineage.jar /t oracle /d path_to_sql_file_directory

Solution 2: Using metadata exported from database

Since dlineage v2.2.0 (2022/7/21), This dlineage tool supports /env parameter to accept a metadata json file which includes the metadata exported from a database.

By providing metadata.json that includes the metadata, column ename should be linked to the table emp correctly.

You can use /env to specify a metadata.json like this:

java -jar gudusoft.dlineage.jar /t oracle /f path_to_sql_file /env metadata.json

You can always extract metadata from the database use the sqlflow-ingester tool.