Column names cannot contain spaces when saving back to Redshift

[JoshRosen]

Because spark-redshift uses Avro in its write path, it inherits the limitations of Avro's schema validation: we cannot create tables with columns that contain non-letter-or-_ characters, such as spaces or quotes.

In order to work around these limitations, I think that we'd have to use a different set of column names when writing to Avro, then use a JSONPaths file to map those columns back to the original column names.

[l15k4]

Hey, colons and dashes too, I have fields like this c-geo:c3 and getting :

[info]   org.apache.avro.SchemaParseException: Illegal character in: c-geo:c3
[info]   at org.apache.avro.Schema.validateName(Schema.java:1083)

because neither : nor - is supported ...

Spec https://avro.apache.org/docs/current/spec.html#names doesn't say anything about this. Wdyt ?

[JoshRosen]

The Avro spec actually does say something about this (emphasis mine to make things clearer):

Record, enums and fixed are named types. Each has a fullname that is composed of two parts; a name and a namespace. Equality of names is defined on the fullname.

The name portion of a fullname, record field names, and enum symbols must:

• start with [A-Za-z_]
• subsequently contain only [A-Za-z0-9_]

My PR description also notes this:

we cannot create tables with columns that contain non-letter-or-_ characters, such as spaces or quotes.

Starting in version 3.0.0-preview1, you can use the csv tempformat during writes and that format has none of these restrictions on names. In that version, there's even a helpful error message which detects when you've hit this case using AVRO serialization and suggests the CSV tempformat as a workaround:

spark-redshift/src/main/scala/com/databricks/spark/redshift/RedshiftWriter.scala

Line 370 in 6cc49da

if (params.tempFormat == "AVRO") {

I'll mark this issue as "documentation" to remind me to add a section to the README documenting this workaround.

[JamieL22]

Is this fixed yet? (white space in column names)