Whitespace in column name in Synapse

I am using Databricks to read data to a Spark Dataframe from Azure Synapse table.

df = spark.read \
          .format("com.databricks.spark.sqldw") \
          .option("url", sql_dw_connection_string) \
          .option("tempDir", temp_dir_url) \
          .option("enableServicePrincipalAuth", "true") \
          .option("query", query) \
          .load()
df.cache()

When I try to cache the Dataframe, I am getting the below error in Databricks.

    com.databricks.spark.sqldw.SqlDWSideException: Azure Synapse Analytics failed to execute the JDBC query produced by the connector. Make sure column names do not include any invalid characters such as ';' or whitespace
Underlying SQLException(s):
  - com.microsoft.sqlserver.jdbc.SQLServerException: 110802;An internal DMS error occurred that caused this operation to fail. Details: Exception: Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.HdfsAccessException, Message: Error occurred while accessing HDFS external file[/sqldw-staging/2023-03-07/07-14-49-790/f030341a-12d7-4ae1-b5e0-81b510a9a9e1/QID68547779_20230307_71451_0.parq.snappy][0]: Java exception raised on call to HdfsBridge_CreateRecordWriter. Java exception message:
HdfsBridge::createRecordWriter - Unexpected error encountered when creating a record writer: IllegalArgumentException: field ended by ';': expected ';' but got 'CODE' at line 9:   optional binary ATC CODE [ErrorCode = 110802] [SQLState = S0001]

The reason being is that in the Azure Synapse table have a column named ATC CODE where this column name have whitespace. I saw some solution in internet to replace whitespace with underscore but as per the project requirement, I am not suppose to replace whitespace with any other character.

Is there any way to resolve this?

As per Microsoft document, Invalid symbols like whitespace or semicolons are now returned by the Azure Synapse connection as an error when it found in a column name.

I also got similar error for column names First Name and Last Name.

• As you don't allow to change the column in database, to workaround this issue you can write a query to select column contain spaces as alias with underscore. Also you can create view for that and select that view in query.

select Id, [First Name] as First_name, [Last Name] as Last_name from demotb

then you will get column name with underscore in place of space as below:

• Then you can replace the _ with space by

from pyspark.sql import functions as F
renamed_df = df.select([F.col(col).alias(col.replace('_', ' ')) for col in df.columns])