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.