In line with the question here (Dremio with reflection on updated (append) dataset) I wanted to ask the following:
I’m referencing the following structure:
And within each date folder I have the same parquet file.
I want to be able to query for the most recent date with below query which works when setting it up. However when adding new folders it doesn’t take into account the new folders when I rerun the query. Somehow dremio fails to recognize them. I’m not running any reflection.
SELECT rundate,variable,“value”,source,dir1 as dealkey,dir0 as filetimestamp FROM mergeperdealkey WHERE dir0 IN (
SELECT TO_CHAR(timeconverted,‘YYYYMMDDHH24MISS’) FROM (
SELECT to_timestamp(timestring,‘YYYYMMDDHH24MISS’) as timeconverted FROM (SELECT DISTINCT(dir0) as “timestring” FROM mergeperdealkey) ORDER BY timeconverted desc LIMIT 1