AWS Athena query appending results to table?

Question

I have an Amazon Athena table that I created with the following syntax:

CREATE TABLE IF NOT EXISTS table1 ( string1 string, string2 string)
LOCATION 's3://xyz/abc'

I then populated the table via

INSERT INTO table1 (string1, string2)
values ('1','2'),('3','4')...

This all worked just fine. However, when I query the table via

select count(*)
from table1

I get a number. If I run this query again, the count has gone up. Running it again, and the count goes up again. I think what is happening is that the results of the query (and maybe the query itself) are somehow being saved to the table. Where is the setting for this and how do I stop it from happening? I don't mind if a table stores the query and result information, just so long as it is not table1.

John Rotenstein · Answer

It would appear that the location you specified for the table storage is the same as the configured query result location.

This would cause the results of every SELECT to be stored in the same location, therefore 'appending' data to the table.

You should keep those two locations separate.

AWS Athena query appending results to table?

1 Answers