topic Re: Invalid characters in column name in Data Engineering

Invalid characters in column name

WynanddB — Wed, 19 Jun 2024 11:02:19 GMT

I get the following error com.databricks.sql.transaction.tahoe.DeltaAnalysisException: [DELTA_INVALID_CHARACTERS_IN_COLUMN_NAMES] Found invalid character(s) among ' ,;{}()\n\t=' in the column names of your schema.

It's a new instance of databricks and I've checked the CSV headers. They are all valid with no special characters in the column names.

This is my code

device_path = "dbfs:/mnt/dblakehouse/RawLanding/ysoft/device"

(spark.readStream

.format("cloudFiles")

.option("cloudFiles.format", "csv")

.option("cloudFiles.inferColumnTypes", "true")

.option("cloudFiles.schemaLocation", f"{device_path}/checkpointLocation")

.load(f"{device_path}/")

.writeStream

.option("checkpointLocation", f"{device_path}/checkpointLocation")

.option("mergeSchema", "true")

.outputMode("append")

.toTable("printing_poc01.bronze.smartq_devices")

)

Re: Invalid characters in column name

Rishabh-Pandey — Wed, 19 Jun 2024 11:38:57 GMT

Steps to Debug and Resolve

Check for Hidden Characters:
- Sometimes, non-visible or control characters can cause issues. It's useful to print out the headers and inspect them closely or use a tool to reveal hidden characters.
- You can use Python or a text editor to load and print the column names to check for any hidden characters.
Explicitly Specify the Schema:
- Instead of relying on the schema inference, manually specify the schema. This approach can help bypass issues with inferred column names.
Inspect the First Batch of Data:
Sanitize Column Names:
- If there are invalid characters, you can rename the columns to valid names programmatically:
Check for Delta Lake Configuration:
- Ensure that Delta Lake configurations and settings are correct and there are no conflicting options that might affect schema validation.
CSV Reader Settings:
- Double-check the options being passed to the CSV reader, such as handling of delimiters, escape characters, etc., to ensure they don't cause issues with column names.

Re: Invalid characters in column name

jacovangelder — Wed, 19 Jun 2024 11:52:20 GMT

My guess is you have a new line character (\n) in one of the CSV header columns. You don't very easily spot them. Have you checked for that? You can also try .option("header","true") so Spark doesn't think of your header as content. Might also want to set the delimiter right using .option("delimiter", "<delimiter char here>")

Good luck

Re: Invalid characters in column name

WynanddB — Wed, 19 Jun 2024 12:13:23 GMT

I might have to specify the schema. Have done all the other options. Thanks for responding.

Re: Invalid characters in column name

WynanddB — Wed, 19 Jun 2024 12:15:16 GMT

Hi I've checked for the new line. Will try specifying the delimiter. Thanks for responding