[Amazon](500310) Invalid operation: syntax error at or near ".";

[cfeduke]

When using SaveMode.Overwrite (which I honestly haven't checked to see if its supported) like the following code:

.toDF().write
      .format("com.databricks.spark.redshift")
      .option("jdbcdriver", "com.amazon.redshift.jdbc41.Driver")
      .option("url", redshiftJdbcUrl)
      .option("dbtable", "pf.some_table_here")
      .option("tempdir", tempDirBucket)
      .mode(SaveMode.Overwrite)
      .save()

I encounter:

Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: syntax error at or near ".";
        ... 80 more

(Lost the rest of the stack trace in a closed screen session but I believe the error can be reproduced by specifying the schema name with the table name and using SaveMode.Overwrite.)

Using SaveMode.Append is a work around (I just truncate the table prior to saving).

[cfeduke]

Here we go:

java.sql.SQLException: [Amazon](500310) Invalid operation: syntax error at or near ".";
        at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
        at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getReadyForQuery(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
        at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getReadyForQuery(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getOperationMetadata(Unknown Source)
        at com.amazon.redshift.client.PGClient.prepareStatement(Unknown Source)
        at com.amazon.redshift.dataengine.PGBatchQueryExecutor.<init>(Unknown Source)
        at com.amazon.redshift.dataengine.PGJDBCIDataEngine.prepareBatch(Unknown Source)
        at com.amazon.redshift.dataengine.PGJDBCIDataEngine.prepare(Unknown Source)
        at com.amazon.jdbc.common.SPreparedStatement.<init>(Unknown Source)
        at com.amazon.jdbc.jdbc41.S41PreparedStatement.<init>(Unknown Source)
        at com.amazon.redshift.core.jdbc41.PGJDBC41PreparedStatement.<init>(Unknown Source)
        at com.amazon.redshift.core.jdbc41.PGJDBC41ObjectFactory.createPreparedStatement(Unknown Source)
        at com.amazon.jdbc.common.SConnection.prepareStatement(Unknown Source)
        at com.databricks.spark.redshift.RedshiftWriter.withStagingTable(RedshiftWriter.scala:96)
        at com.databricks.spark.redshift.RedshiftWriter.saveToRedshift(RedshiftWriter.scala:281)
        at com.databricks.spark.redshift.DefaultSource.createRelation(DefaultSource.scala:104)
        at org.apache.spark.sql.execution.datasources.ResolvedDataSource$.apply(ResolvedDataSource.scala:170)
        at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:146)

On the AWS Redshift console, under loads I see as Status: completed:

COPY pf.stg_some_table_staging_693140401 FROM 's3://somebucket-redshift-spark/87b26d70-17e1-43b5-b40a-5b002b244ad7/' CREDENTIALS '' FORMAT AS AVRO 'auto' DATEFORMAT 'YYYY-MM-DD HH:MI:SS'

[JoshRosen]

spark-redshift does support SaveMode.Overwrite and, in fact, has some fancy machinery to make sure that it's done in a safe way using a staging table. I noticed that your dbtable value contains a period / dot: pf.some_table_here; it's likely that we're not properly escaping or quoting somewhere.

[JoshRosen]

Looking a little closer at the stacktrace, my hunch is that the ALTER TABLE command is failing because the re-named table name is qualified with the schema because we append a suffix to params.table to derive the new table name:

        conn.prepareStatement(
          s"""
             | BEGIN;
             | ALTER TABLE $table RENAME TO $backupTable;
             | ALTER TABLE $tempTable RENAME TO $table;
             | DROP TABLE $backupTable;
             | END;
           """.stripMargin.trim).execute()

In order to handle this properly, I think that we should parse params.table into a case class which has separate fields for the table name and schema portions of the full name, then use the proper portions when constructing the ALTER TABLE transaction.

[JoshRosen]

I now have a patch to fix this one, too: #102