NOT_SUPPORTED: Modifying Hive table rows is only supported for transactional tables

Question

Hello!

I have created the table by Alteryx and now I need to remove specific records from this table in Athena, but when I am trying to do it I get the following error: NOT_SUPPORTED: Modifying Hive table rows is only supported for transactional tables

I am using the following query to do it: DELETE FROM database_name.table_name where pk_id in ('specific record')

Could you please advise how can I do transactional table by Alteryx, so I could remove specific records from it.

Thank you in advance!

max1111 · Answer

seems that Alteryx doesn't support transactional tables, so if you are appending data you can create the table via ddl in db (Athena in my case) updating the properties in the table: 'transactional'='true', but I get another error, not sure how to update the ddl, so I can delete the records from the table.

That is the latest error I get: NOT_SUPPORTED: AWSHiveMetastore does not support ACID tables