We have an impala table that is partitioned by as year=yyyy/month=mm/day=dd/hour=hh. One of the client applications can send select queries to it with a from and a to date in dd/mm/yyyy format.
Now, for eg. if the from date is set to say 01/11/2019 and to date is set to 29/02/2020, in order to use the partitions, the imapla SQL should be something like:
select * from table where (year = 2019 AND month in (11, 12)) OR (year = 2020 AND month in (1,2))
Is there a way in impala SQL to convert the dates to the right partitions above?
Unfortunately the client in question is a BI tool that does not allow programming the logic above.
So, Solution is -
WHERE
CAST( concat (CAST(table_year AS string), CAST(table_month AS string)) AS BIGINT)
BETWEEN
CAST( concat (CAST(YEAR(from_dt) AS string), CAST(MONTH(from_dt) AS string)) AS BIGINT) AND
CAST( concat (CAST(YEAR(to_dt) AS string), CAST(MONTH(to_dt) AS string)) AS BIGINT)
Basically, concatenate year and month to make a string and then convert it to an integer to use between function. This will work form any date to any date range. Since you have partition on month and year, this filter will follow partition and work just fine.