Output data to existing Redshift tables using a macro

Question

Hello,

I would like to output data (delete & append) to multiple existing Redshift tables from multiple Excel inputs using a macro. I am having an issue similar to the one below, where Alteryx is not recognizing the table. I'm thinking that it may be an issue with my formatting/syntax, how should the Output Data node look like for this case? Are there any POCs I can reference?

I know that the user I'm logged in as has access to this schema and that the table exists by testing in another Alteryx flow that successfully appended to a singular table rather than using a macro.

The error:

Error: Rename_batch_macro (16): Record #38: Tool #12: Error running DELETE FROM "db_name.schema.table_name": [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42P01] ERROR: relation "schema.table_name" does not exist

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/not-able-to-write-into-postgres-using-upload-to-postgres-macro/td-p/181733

gina6912 · Answer

Do you mean schema.table_name? Assuming you mean in the ODBC admin, yes, the connection is created - however, it is a connection to the database name not the database.schema, not sure if that matters.

I am updating the name in the Output Data node at the bottom, where it says Change File/Table Name to a string that I built before. I believe I cannot use the Data Stream In because this user does not have permissions to delete or create new tables. I am only allowed to append to existing tables.

apathetichell · Answer

Redshift uses a xxxx.xxxx syntax when reading/writing. Is your In-DB configured? I use a Datastream In with a batch macro configured to update the table name that I'm writing to.

Database is configured in your ODBC 64 config.