Athena integration problem

Question & Answer
1

Hi,

When trying to query data from athena data source using in order to feed a SPICE dataset, I get an error with the custom sql editor in Quicksight alerting something went wrong and doesn’t provide any other info to investigate.
The query executed fine in athena side with no errors or limitations.
Trying to convert the query to an athena view didn’t fix it.

The only change I did in the current version of the query is adding a WHERE part saying:
date(date_parse("cDate",'%m%d%Y')) >= date_trunc('YEAR', current_date - interval '1' year)

I know athena engine is upgraded to #3, but can be the cause of it? and how can I get more info about it?

1 Like
2

Hi @amico - Can you please test the custom sql in Athena console and if it is working fine, it should work fine in QuickSight as well.

Note - Athena follow the presto syntax, to get the details, you can see the presto documentations.

Regards - Sanjeeb

3

It does run in Athena console.
I still engaging problems in Quicksight custom sql component.

4

Hi @amico - Not sure 100% but can we change parse_date to parse_datetime and verify, see the trino documentation below.

https://trino.io/docs/current/functions/datetime.html

Hi @David_Wong @Koushik_Muthanna - Any Quick advise on the above athena error.

Regards - Sanjeeb

5

Hi @amico ,
1/ Can you paste a screenshot of the error ?
2/ Does the query not run only when you have the where clause ? ( try select count(1) from table and check if that works ?? )

Regards,
Koushik

6

image
image1440×154 8.74 KB

Now I see it doesn’t run also in the current version without the WHERE part.
The weird part is that the dataset is scheduled to be refreshed each day and it executes just fine.

7

@amico,

Do you have permissions to make changes to this dataset ? Are you the owner of this dataset ?

1 Like
8

Hi, yes I do.
Maybe something has been changed in Athena side regarding QS applicative user permissions as a pert of Athena upgrade.

9

Hi @amico

Assuming cDate is a string in the format ‘MMddyyyy’, try to use this version of the query:

date(date_parse(cDate, ‘%m%d%Y’)) >= date_trunc(‘year’, date_add(‘year’, -1, current_date))

Hope it helps,
GL

2 Likes
10

Thank you.
It has been solved by now.
probably some kind of connection/permission problem.