I have athena table that is partitioned by date and I need to create a parameter in QUicksight of type ‘date’ and use that in SPICE Custom SQL Query to load the data based on the given date value in the parameter but it seems like Quicksight does not allow to use parameters in the SPICE Query. Can anyone please help me here to load my data based on the dynamic date entry that will be used in the Custom SQL Query.
Hi @Nikhitha ,
For SPICE you can set up an incremental query and then schedule it. See here and scroll down to the ’ Incrementally refreshing a dataset on a schedule’ section. Note that you don’t use a parameter, but the SPICE ‘look back window’
If you prefer to use a direct query then you can use the Athena data functions, for instance:
select * from v_monthly_sales where sales_date = current_date
Hope that helps.
ws
Hello @Nikhitha ,
Dataset parameters in a SPICE dataset are not currently supported. They are in Direct Query mode though.
Could you modify your dataset from SPICE to Direct Query and test it out? If not, a SPICE dataset should be able to retrieve data very quickly even if there are no dataset parameters being applied.
I will create a feature request for enabling dataset parameters in a SPICE dataset in the meantime. Thank you.
Hi @Asem and @wstevens01 , Thanks for your reply. I have to use SPICE only. SPICE dataset is able to retrieve the data, but the timeout exception is coming due to underlying DataSource (Mine is Athena) is having Query timeout limit so I had to use partitions in my SQL Query to easily fetch the data. To do so, I have to pass dynamic date value for my partition column in the where clause and tried to use parameter, but as @Asem said, it is not supported as of now but as @wstevens01 suggested I used the ‘current date’ Athena function in my SPICE query itself and it is working as expected. Thanks!
Hello Asem,
Any update on this new capability ?
I mean Dataset Parameters working with a SPICE Dataset ?
Thanks for your help.
JP