topic Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables in Data Engineering

Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables

AsfandQ — Sat, 18 Jun 2022 11:02:53 GMT

Hello,

I am trying to write Delta files for some CSV data. When I do

csv_dataframe.write.format("delta").save("/path/to/table.delta")

I get: AnalysisException:

Found invalid character(s) among " ,;{}()\n\t=" in the column names of your

schema.

Having looked up some docs, I expected the following to set the column mapping mode to "name" for all tables which would not cause this error:

spark.conf.set("spark.databricks.delta.defaults.columnMapping.mode", "name")

Running this before invoking `write(...)` does not work and I get the same error.

I have managed to do it in SQL using TBLPROPERTIES with the CREATE TABLE statement like so:

CREATE TABLE table_bronze_csv
  USING CSV
  OPTIONS (path '/path/to/data.csv', 'header' 'true', 'mode' 'FAILFAST');
 
CREATE TABLE table_bronze
  USING DELTA
  TBLPROPERTIES ("delta.columnMapping.mode" = "name")
  AS SELECT * FROM table_bronze;

but am looking for the Python way of doing it.

Thanks

Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables

Hemant — Sat, 18 Jun 2022 17:43:19 GMT

Just pass properties in option:

csv_dataframe.write.format("delta").option("delta.columnMapping.mode","name").save(path)

Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables

AsfandQ — Sat, 18 Jun 2022 18:15:44 GMT

Thanks @Hemant Kumar, I tried exactly what you said, the below is a copy/paste (with sanitized names):

table_bronze.write.format("delta").option("delta.columnMapping.mode", "name").save("/path/to/table.delta")

I got the same error. Is there a bug?

Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables

Hemant — Sat, 18 Jun 2022 18:37:56 GMT

Sorry, I forgot to mention saveastable , try this:

table_bronze.write.format("delta").option("delta.columnMapping.mode", "name").option("path","/path/to/table_bronze").saveAsTable("table_bronze")

Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables

AsfandQ — Sat, 18 Jun 2022 18:54:11 GMT

Thanks that worked. I can now query it with SQL. Can you explain why is it that I have to do saveAsTable with path set in an option? I thought calling `save()` was the way to do this kind of operation.

Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables

Hemant — Sat, 18 Jun 2022 19:36:32 GMT

According to my understanding, it's property of delta table not of a delta file.Thats why, it's not worked while you save it as delta file.

Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tables

Keng_Onn — Fri, 28 Apr 2023 06:06:07 GMT

I was able to save it as a delta file. You need to specify the minReaderVersion and minWriterVersion as well. E.g.

spark_df.write.format("delta").mode('overwrite').option("delta.columnMapping.mode", "name").option('delta.minReaderVersion', '2').option('delta.minWriterVersion', '5').save('/path/to/table')

Reference: Table protocol versioning — Delta Lake Documentation

Re: Delta tables: Cannot set default column mapping mode to "name" in Python for delta tab

Personal1 — Tue, 01 Oct 2024 23:38:58 GMT

I still get the error when I try any method. The column names with spaces are throwing error

[DELTA_INVALID_CHARACTERS_IN_COLUMN_NAMES] Found invalid character(s) among ' ,;{}()\n\t=' in the column names of your schema.

df1.write.format("delta") \ .mode("overwrite") \ .option("mergeSchema", 'true') \ .option("delta.columnMapping.mode", "name") \ .option('delta.minReaderVersion', '2') \ .option('delta.minWriterVersion', '5') \ .option("path","/tmp/spark-delta-table") \ .saveAsTable("`spark-delta-table`")