Hi guys,
I create a dataset with this query
select machineid as machinecode, timestamp, l3dspmaxvalue, t041, t040, t038, t043, t044, t039, t042, t030,
date_parse(substring(timestamp,1, 19),'%Y-%m-%d %H:%i:%s') as timestamp_parsed,
year,month,day,
lag(timestamp) over (partition by machinecode,year,month,day order by timestamp asc) as prev_timestamp,
speed, activealarms, t001, t002, t003, t004, t005, t006
from prod_datalake.machinesmetrics
my datasource has more than 165 milions records and it take a very long time to be executed (more than 60seconds). I would like to add filters on the dashboard not in the dataset. Is it correct?
My environment is this
S3 → Glue → Athena → Quicksight.
Within Glue I already create a partition index, without any improvements. I partially solve my problem creating a SPICE dataset, but its size is 50giga limited to 120 days instead otherwise I receive a SQL_EXCEPTION (timeout) while executing the first full refresh.
How can I improve its performance?
Best regards
Enrico