Is there a way to pass the date filter from QuickSight to Athena table, so that the table queries S3 using the date passed from QuickSight.
Something like:
The following will be part of Athena table or View SQL.
WHERE day={FILTER COMING FORM QUICKSIGHT} AND hour={FILTER COMING FORM QUICKSIGHT}
@GeorgeS - When you choose the Direct Query Method, Quicksight automatically passes the parameter values into the base SQLs where clause to dynamically filter the data. I have tried to provide some relevant snapshots explaining the scenario below. Hope this helps!
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Thank you @sagmukhe, on your snapshot, there was already a filter which came with the base query.
My question is, on the Athena table base query instead of a filter can I put placeholders like Athena question mark placeholders (parameterized queries), then the values/parameters be provided by QuickSight at runtime?
@GeorgeS - I put those filters just to minimize my initial dataset. You can ignore them. As far as filtering using parameters are concerned, the point that I tried to make that, you don’t need to put placeholders. Based on the parameter selection QuickSight automatically will add those parameters on the runtime in the as WHERE clause (e.g. eventtime filter in my previous example). I redesigned the report now to have no filters in my base sql and propagated all the filters in report parameters. Now you can see all the parameters are being added dynamically run time. In case you want to minimize data at the first run, put some default values. Hope this helps!
Thank you @sagmukhe you made it very clear and answered my question.
Am I correct to then conclude that, the entire data source that the Athena table points to is scanned at the creation of the dashboard, then only filtered data is scanned on subsequent queries that use filters in report parameters.
@GeorgeS - That’s right. The Last Query scanned only 3.47 MB data whereas the size of the entire dataset is way more than that. See the snippet below:
