Drop/Create Tables in Athena

Question

Hi,

I have a sql script which runs each morning to drop and create tables in Athena, but I'd like to replace this with a scheduled WF. Is there a way designer can do this? I wondered if I could just connect In-DB, write a short and simple SQL statement and then drop a different table, like below, but it just errored.

Select Test.* From Test
DROP TABLE Test2;

Is there a way in Designer of creating/dropping tables from Athena?

Thanks!

Barry_Cooper · Answer

Sorry, should have said, that also gets much the same error

Connect In-DB (3)	Error SQLPrepare: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 130, HTTP Response Code: 400, Exception Name: InvalidRequestException, Error Message: line 1:30: mismatched input 'drop'. Expecting: '(', 'LATERAL', 'UNNEST', <identifier> [Execution ID: ]

jonnyrask · Answer

With a little bit of googling - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Issue-while-connection-with-AWS-Athena/td-p/431764

This isn't an Alteryx Designer issue

Sorry didn't full read through error. Your SQL doesn't make any sense.

Just do the drop statement

DROP TABLE Test2;

Barry_Cooper · Answer

Hi,

it was

Connect In-DB (2)	Error SQLPrepare: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 130, HTTP Response Code: 400, Exception Name: InvalidRequestException, Error Message: line 5:1: mismatched input 'drop'. Expecting: ')', ',', '.', 'AS', 'CROSS', 'EXCEPT', 'FOR', 'FULL', 'GROUP', 'HAVING', 'INNER', 'INTERSECT', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'OFFSET', 'ORDER', 'RIGHT', 'TABLESAMPLE', 'UNION', 'WHERE', <identifier> [Execution ID: ]