I have a pipeline where AWS Kinesis Firehose receives data, converts it to parquet-format based on an Athena table and stores it in an S3 bucket based on a date-partition (date_int: YYYYMMdd). Whenever new data is added to the bucket, a lambda is triggered to check if Athena already knows about the partition. Everything seems to be working fine; in Athena I can run a query (see below) and the newest data is received.
Athena query: SELECT * FROM "my_table" WHERE "date_int" >= 20210308
(On the left-hand side of the screen the correct Data Source and Database are selected)
Now I want to visualise the data in Quicksight. I can use either SPICE or direct query, again, all seems to be working fine. However, I have the data partitioned, because I only need datapoints of, say, the last month. In Quicksight I create a new dataset, choose the correct catalog/database/table and click 'Use custom SQL'. Then, when I run the query, I always get an error from the Athena client saying the table couldn't be find. When I look in the network tab, I see the query performed being:
/* QuickSight */SELECT ds.* FROM ( SELECT * FROM "my_table" ) ds LIMIT 0
Then the error message saying:
Table awsdatacatalog.default.my_table does not exist
The strange part is, I didn't say it should be looking at the 'default' database. I select 'awsdatacatalog' as the datasource and 'my_database' as the database. When I try to be more precise and specify the datasource and database in the select statement ("awsdatacatalog.my_database.my_table"), the error message will say "awsdatacatalog.default.awsdatacatalog.my_database.my_table".
Anyone else having the same problem? Is this a bug, or am I just missing something?
