CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athenaamazon-quicksight
JanJetze
JanJetze

Reputation: 13

AWS QuickSight custom SQL query on Athena

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?

Upvotes: 1

Views: 4917

Answers (2)

Arkadiusz Turlewicz
Arkadiusz Turlewicz

Reputation: 1

In my case the problem was how to use double quotes. They have to be around keywords, not the whole path.

For example:

SELECT * FROM AWSDataCatalog."athena-example1"."employees-table"

Upvotes: 0

Tanya Madaan
Tanya Madaan

Reputation: 41

It worked for me by using datasource.database_name.table_name. Try using SELECT * FROM awsdatacatalog.my_database.my_table

Upvotes: 4

Related Questions