i have parquet files stored in s3 in columnar format : like :
sample file : s3://new-analytics-pipeline-stg/ad-minute_/year=2025/month=08/day=02/hour=01/minute=01/202508020101.parquet
i want to query athena connected to s3 via Grafana dashboard.
I have log files in parquet format in s3 written every minute to hive-style partitioned folders like :
sample file : s3:///stats-minute_/year=2025/month=08/day=02/hour=01/minute=01/202508020101.parquet
the stats-minute_ table is partitioned by : year,month,day,hour,minute
in Grafana panels, there is time range global column which has ranges like:
last 5 minutes
last 30 mninutes
last 1 hr
last 24 hr
7 days .. etc
eg : if i select last 24 hours, the Grafana query should automatically query only those
year, month ,day,hour and minute partitions which fall into last 24 hour range.
eventually i want table output with 2 columns as :
1>timestamp in yyyy-mm-dd hh:mm:ss format
2> Aggregated column (sum/max,min … etc) ,
assuming last 24 hours is selected in grafana global variable time range
how should i write query such that i could to fetch data as fast as possible.
cuurently i am trying to write query like :
SELECT ts_gen,
max(sessions) AS “Max sessions”
FROM " acr_log_store".“stats-minute”
WHERE year BETWEEN CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__from:date:iso}’) AS VARCHAR), 1, 4) AS VARCHAR) AND CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__to:date:iso}’) AS VARCHAR), 1, 4) AS VARCHAR)
AND month BETWEEN CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__from:date:iso}’) AS VARCHAR), 6, 2) AS VARCHAR) AND CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__to:date:iso}’) AS VARCHAR), 6, 2) AS VARCHAR)
AND day BETWEEN CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__from:date:iso}’) AS VARCHAR), 9, 2) AS VARCHAR) AND CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__to:date:iso}’) AS VARCHAR), 9, 2) AS VARCHAR)
AND hour BETWEEN CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__from:date:iso}’) AS VARCHAR), 12, 2) AS VARCHAR) AND CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__to:date:iso}’) AS VARCHAR), 12, 2) AS VARCHAR)
AND minute BETWEEN CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__from:date:iso}’) AS VARCHAR), 15, 2) AS VARCHAR) AND CAST(SUBSTR(CAST(from_iso8601_timestamp(‘${__to:date:iso}’) AS VARCHAR), 15, 2) AS VARCHAR)
GROUP BY 1
ORDER BY 1