topic Space in Column names when writing to Hive in Get Started Discussions

Space in Column names when writing to Hive

JohnJustus — Wed, 27 Sep 2023 00:40:14 GMT

All,

I have the following code.
df_Warehouse_Utilization = (

spark.table("hive_metastore.dev_ork.bin_item_detail")
.join(df_DIM_Bins,col('bin_tag')==df_DIM_Bins.BinKey,'right')
.groupby(col('BinKey'))
.agg(count_distinct(when(col('serial_lot_tag')>0,col('serial_lot_tag'))).alias('SerialLotCount'),
count_distinct(when(col('tracking_tag')>0,col('tracking_tag'))).alias('Track Count'),
min(col('activation_time')).alias('OldestStorageTime')
)
.withColumn('BinUsedFlag',when(col('PartNumbers')>0,1).otherwise(0))

)
df_Warehouse_Utilization.write.mode("overwrite").format("delta").saveAsTable("yorkgold.df_DIM_Binsst")
display(df_Warehouse_Utilization)

when I write to table after the above transformation, the following error occurs.
AnalysisException: Found invalid character(s) among ' ,;{}()\n\t=' in the column names of your schema.

The issue is because I have the space in the column name (Track Count, please see the above highlighted), As soon as I rename column as (TrackCount), I am able to write to Hive.
So my question is, space is not allowed? Or is there any other option?

Re: Space in Column names when writing to Hive

daniel_sahal — Thu, 28 Sep 2023 05:50:57 GMT

@JohnJustus You can try using backticks (`) to escape a column names that contains spaces, but the suggestion here is to get rid of spaces or any special characters in a column names.

Re: Space in Column names when writing to Hive

JohnJustus — Sat, 30 Sep 2023 00:44:56 GMT

Thank you Daniel

So I have my code below with back stick but still doesn't work - col('bin_type').alias(`'Bin Type'`),

Can you please correct me?

df_Bins_Step1 = (
spark.table("hive_metastore.dev_fivetranpoc_york.bin_master")
.select(col('tag_number').alias('BinKey'),
col('bin').alias('Bin'),
col('bin_type').alias(`'Bin Type'`),
col('zone').alias('Zone'))
)
display(df_Bins_Step1)

Re: Space in Column names when writing to Hive

KandyKad — Tue, 12 Nov 2024 04:47:41 GMT

Hi,

I have faced this issue a few times. When we are overwriting the dataframes to hive catalog in databricks, it doesn't naturally allow for column names to have spaces or special characters. However, you can add an option statement to bypass that rule and save your dataframes as tables with column names having spaces or special characters.

Solution:

Let's say your dataframe name is 'df'.

The normal pyspark command for saving tables is:

>> df.write.mode("overwrite").format("delta").saveAsTable("catalog.schema_name.table_name")

This will give you the AnalysisException: Found invalid character(s) among ' ,;{}()\n\t=' in the column names of your schema error.

You need to add an 'option statement' - ".option("delta.columnMapping.mode", "name")" to the above command after overwrite, to resolve it.

The final working command would be:

>> df.write.mode("overwrite").option("delta.columnMapping.mode", "name").format("delta").saveAsTable("catalog.schema_name.table_name")

The above edit, resolves the issue for me and the table is perfectly saved even if it has spaces or special characters in the schema.

Hope it works for you as well. If it does, happy to help!!