I have to create a Trino table, but some of the columns have the character - in their names.
The columns that are posing a problem are in nested fields, like so :
`root_field` ARRAY(
ROW<`nested_1`:
`nested_2` ROW(
`problematic-field`: ARRAY(varchar)
)
)
)
I tried escaping the problematic column name with double quotes, it let me create the table but when I try to query it I get the following error :
com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Error: : expected at the position 32 of 'struct<reason-text:array<string>>' but '-' is found.
I get this error even when I try to drop or alter the table, and this error also pops up very regularly in the logs because Trino can't update the catalog metadata because of this table.
One other way would be to get those problematic fields out of the nested field, but it would mean editing 6 months worth of data.
Is there an other way to escape a column name with special characters?
Trino version is 360.
Additional info :
I usually create table from spark, by letting Spark infer the schema from data, which poses no problem to use them with either Trino or Spark.
When this table is created from Spark, the query show create table in Trino gives this schema :
CREATE TABLE catalog.schema.table_name( col array(varchar), day date ) WITH ( external_location = 'hdfs://spark-warehouse/schema.db/table_name-__PLACEHOLDER__'', format = 'SEQUENCEFILE', partitioned_by = ARRAY['day'] )
where the schema is wrong, external_location is not the actual path to data (it looks like a path to the metastore instead), and format is wrong as well (actual format is PARQUET)
