DataBricks/Spark: How to create and overwrite/append to table with periods in the name?

Viewed 207

Some raw data that I want to capture in delta tables have periods in the column names.

My strategy had been to create a table that uses the backticks like so:

CREATE TABLE TestMe (
        testMeKey bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
        id bigint,
        rev bigint,
        `System.WorkItemType` string,
        sourceFile string
        )
         USING DELTA
         OPTIONS (PATH "/mnt/TestMe")

Then when I get new data, to do something like this:

spark.read.format("parquet") \
    .load("/mnt/TestMeData/") \
    .withColumn("sourceFile", input_file_name()) \
    .write.option("mergeSchema", "true") \
    .format("delta").mode("overwrite") \
    .save("/mnt/TestMe")

The problem is, Spark throws name match errors for the columns with periods in them, saying:

Cannot resolve column name System.WorkItemType

I have tried to manually recode the dataframe column names, with something like this, but this also fails:

l1 = [col for col in df.columns]
l1 = [f'`{x}`' if ('.' in x) else x for x in l1]
# make the names match
df = df.toDF(*l1)

How can I create a table that is consistent with source data if it contains characters like periods? I would rather not alter the table and recode periods to underscores or something, but rather accept the data as-is.

0 Answers
Related