topic Re: Lakehouse to SQL Endpoint keeps giving error in Data Engineering

Lakehouse to SQL Endpoint keeps giving error

ghernandezmf — Tue, 09 Apr 2024 19:47:44 GMT

I am having an issue building delta files through a notebook and having it populate the SQL Endpoint.

Below is my code to create the delta table

SalesDocs_DF.write.format('delta').mode('overwrite').save('Tables/Sales_Inventory')

When I try to convert to SQL Endpoint I get the following Error:

Table uses column mapping which is not supported.

Corrective Action: Recreate the table without column mapping property.

I even tried to query through a data warehouse and I cannot access the delta table I made through a notebook. I am not sure what I am doing wrong here.

Re: Lakehouse to SQL Endpoint keeps giving error

Anonymous — Wed, 10 Apr 2024 10:47:40 GMT

Hi @ghernandezmf ,

Can you please help me understand?
What all types of transformation operations are performed to the dataframe?
If possible please share the screenshot of your issue?

If you can provide me more details, I will try to guide you.

Re: Lakehouse to SQL Endpoint keeps giving error

R1k91 — Wed, 10 Apr 2024 12:38:13 GMT

try to use

SalesDocs_DF.write.format('delta').mode('overwrite').saveAsTable('Sales_Inventory')

Re: Lakehouse to SQL Endpoint keeps giving error

ghernandezmf — Wed, 10 Apr 2024 15:15:48 GMT

I appreciate the help from you all. I atually endedup figuring out how to make it work.

I found the following code through some researchonto it and it ended up working. I think my columns had some spaces or something at the end.

from pyspark.sql.functions import col

def remove_bda_chars_from_columns(df: (

    return  df.select([col(x).alias(x.replace(" ", "_").replace("/", "").replace("%", "pct").replace("(", "").replace(")", "")) for x in df.columns])

SalesDocs_DF = SalesDocs_DF.transform(lambda df: remove_bda_chars_from_columns(df))

Re: Lakehouse to SQL Endpoint keeps giving error

ghernandezmf — Wed, 10 Apr 2024 17:38:15 GMT

Adding on to this for anyone that has a similar issue. This allows me to write a delta table without the column mapping so I can access the table through the SQL Endpoint.

Re: Lakehouse to SQL Endpoint keeps giving error

Anonymous — Thu, 11 Apr 2024 07:45:27 GMT

Hi @ghernandezmf ,

Glad to know that you were able resolve your issue.

Re: Lakehouse to SQL Endpoint keeps giving error

garyvanceTIL — Thu, 25 Apr 2024 12:49:09 GMT

This is awesome! Saved my life today, great solution, thank you!