CODEWITHSUNDEEP
pythondatabricksazure-databricksdatabricks-autoloader
FUUUUUUUVK
FUUUUUUUVK

Reputation: 31

How to deal with invalid character(s) in column names when using databricks autoloader for csv?

I am attempting to setup a databricks autoloader stream to read a large amount of csv files, however I get the error Found invalid character(s) among " ,;{}()\n\t=" in the column names of your schema. due to the .csv column names containing spaces. The message suggests enabling column mapping by setting table property 'delta.columnMapping.mode' to 'name' and refers me to this docs page, however I cannot see a way to implement this.

This is the code for setting up the stream:

stream = spark.readStream.format("cloudFiles")\
        .option('cloudFiles.format', 'csv')\
        .option('cloudFiles.schemaLocation', delta_loc)\
        .option("rescuedDataColumn", "_rescued_data")\
        .option('header', 'true')\
        .option('delimiter', '|')\
        .option('pathGlobFilter', f"*{file_code}*.csv")\
        .load(data_path)

Upvotes: 3

Views: 4632

Answers (2)

Ted M.
Ted M.

Reputation: 392

The message suggests enabling column mapping by setting table property 'delta.columnMapping.mode' to 'name' and refers me to this docs page, however I cannot see a way to implement this.

Try .option("delta.columnMapping.mode", "name")

Upvotes: 0

Chris de Groot
Chris de Groot

Reputation: 362

We have this issue in a couple cases so we do this in the reader:
.transform(lambda df: remove_bda_chars_from_columns(df))

And the UDF is:

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])

Upvotes: 4

Related Questions