Databricks Notebook error : Analysis Exception with multiple datasets

[vijaykumarbotla]

Hi All,

I am getting below error when trying to execute the code.

AnalysisException: Column Is There a PO#17748 are ambiguous. It's probably because you joined several Datasets together, and some of these Datasets are the same. This column points to one of the Datasets but Spark is unable to figure out which one. Please alias the Datasets with different names via `Dataset.as` before joining them, and specify the column using qualified name, e.g. `df.as("a").join(df.as("b"), $"a.id" > $"b.id")`. You can also set spark.sql.analyzer.failAmbiguousSelfJoin to false to disable this check.

Below is the code :

reguhjoin = reguhjoin.join(bseg_4j_c2, reguhjoin.conc2 == bseg_4j_c2['Concatenate 2'], how='left').select(reguhjoin["*"], bseg_4j_c2["Is There a PO"])

the code is working fine in lower environment and when we try to migrate to PRD we are getting above error, Please suggest.

[lucasrocha]

Hello @vijaykumarbotla , I hope you're doing well.

This is probably because both DataFrames contain a column with the same name, and Spark is unable to determine which one you are referring to in the select statement.

To resolve this issue, you can use the alias method to give each DataFrame a unique alias, and then use the qualified column name (i.e., aliasName.columnName) in the select statement. Here's an example of how you can modify your code to fix the issue:

reguhjoin = reguhjoin.alias("reguhjoin")
bseg_4j_c2 = bseg_4j_c2.alias("bseg_4j_c2")

reguhjoin_joined = reguhjoin.join(bseg_4j_c2, reguhjoin.conc2 == bseg_2['Concatenate 2'], how='left')
reguhjoin_joined_selected = reguhjoin_joined.select(reguhjoin_joined["reguhjoin.*"], bseg_4j_c2["Is There a PO"])

If possible please test the provided code, in case it doesn't work, please let me know the difference between the environments you are running this code.

Best regards,
Lucas Rocha