How to push down "current_date - INTERVAL '1' month " to the TIBCO Data Virtualization's Query Engine for an Apache Hive-2 data source?
Article ID: KB0073355
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 8.3, 8.4 |
Description
The current_date function can be pushed down the TIBCO Data Virtualization's Query engine for an Apache Hive-2 data source. But when it is used with the data type 'INTERVAL', then it can only be pushed down in the following case:
Then the function does not get pushed down with the below message:
==============
No Push Reason: Cannot push selectable because the data source Hive2 does not support the function --current_date - INTERVAL '1' MONTH--
==============
current_date >= current_date - INTERVAL '1' monthHowever, when the same function is used in the below expression:
cast(to_timestamp(date_time_key) as date) >= current_date - INTERVAL '1' month
Then the function does not get pushed down with the below message:
==============
No Push Reason: Cannot push selectable because the data source Hive2 does not support the function --current_date - INTERVAL '1' MONTH--
==============
Issue/Introduction
This article helps to understand how to push down the "current_date -INTERVAL '1' month" function to the TIBCO Data Virtualization's Apache Hive-2 data source when it is used in an expression: cast(to_timestamp(date_time_key) as date) >= current_date - INTERVAL '1' month
Environment
All supported environment
Resolution
To push down the "current_date -INTERVAL '1' month" in the below expression:
The below properties need to be added to the hive2.capabilities file. It's recommended to make these changes in the <adapter>_values.xml file so it won't get overridden by upgrades. If a custom adapter is being used e.g. hive_2_1_1_custom under /conf/adapters/custom, so there is a need to update the hive_2_1_1_custom_values.xml file.
Below is the XML which should be used, this need to be put before the comment section as noted in the <adapter>_values.xml file
==============
expr.interval\ day: INTERVAL '$1' DAY($2)
expr.interval\ hour: INTERVAL '$1' HOUR($2)
expr.interval\ minute: INTERVAL '$1' MINUTE($2)
expr.interval\ second: INTERVAL '$1' SECOND($2, $3)
expr.interval\ day\ to\ hour: INTERVAL '$1' DAY($2) TO HOUR
expr.interval\ day\ to\ minute: INTERVAL '$1' DAY($2) TO MINUTE
expr.interval\ day\ to\ second: INTERVAL '$1' DAY($2) TO SECOND($3)
expr.interval\ hour\ to\ minute: INTERVAL '$1' HOUR($2) TO MINUTE
expr.interval\ hour\ to\ second: INTERVAL '$1' HOUR($2) TO SECOND($3)
expr.interval\ minute\ to\ second: INTERVAL '$1' MINUTE($2) TO SECOND($3)
expr.interval\ year: INTERVAL '$1' YEAR($2)
expr.interval\ year\ to\ month: INTERVAL '$1' YEAR($2) TO MONTH
expr.interval\ month: INTERVAL '$1' MONTH($2)
~interval_year-@timestamp: $1 - cast($2 as timestamp)
@timestamp-~interval_year: cast($1 as timestamp) - $2
~interval_year+@timestamp: $1 + cast($2 as timestamp)
@timestamp+~interval_year: cast($1 as timestamp) + $2
~interval_year-@date: $1 - cast($2 as date)
@date-~interval_year: cast($1 as date) - $2
~interval_year+@date: $1 + cast($2 as date)
@date+~interval_year: cast($1 as date) + $2
~interval_day-@timestamp: $1 - cast($2 as timestamp)
@timestamp-~interval_day: cast($1 as timestamp) - $2
~interval_day+@timestamp: $1 + cast($2 as timestamp)
@timestamp+~interval_day: cast($1 as timestamp) + $2
~interval_day-@date: $1 - cast($2 as date)
@date-~interval_day: cast($1 as date) - $2
~interval_day+@date: $1 + cast($2 as date)
@date+~interval_day: cast($1 as date) + $2
#negative interval
#-~interval_day: (INTERVAL '0' DAY - $1)
#-~interval_year: (INTERVAL '0' YEAR - $1)
CAST(~interval_dayAS@char): LTRIM(TO_CHAR($1), '+')
CAST(~interval_dayAS@varchar): LTRIM(TO_CHAR($1), '+')
CAST(~interval_yearAS@char): LTRIM(TO_CHAR($1), '+')
CAST(~interval_yearAS@varchar): LTRIM(TO_CHAR($1), '+')
==============
"cast(to_timestamp(date_time_key) as date) >= current_date - INTERVAL '1' month"
The below properties need to be added to the hive2.capabilities file. It's recommended to make these changes in the <adapter>_values.xml file so it won't get overridden by upgrades. If a custom adapter is being used e.g. hive_2_1_1_custom under /conf/adapters/custom, so there is a need to update the hive_2_1_1_custom_values.xml file.
Below is the XML which should be used, this need to be put before the comment section as noted in the <adapter>_values.xml file
==============
expr.interval\ day: INTERVAL '$1' DAY($2)
expr.interval\ hour: INTERVAL '$1' HOUR($2)
expr.interval\ minute: INTERVAL '$1' MINUTE($2)
expr.interval\ second: INTERVAL '$1' SECOND($2, $3)
expr.interval\ day\ to\ hour: INTERVAL '$1' DAY($2) TO HOUR
expr.interval\ day\ to\ minute: INTERVAL '$1' DAY($2) TO MINUTE
expr.interval\ day\ to\ second: INTERVAL '$1' DAY($2) TO SECOND($3)
expr.interval\ hour\ to\ minute: INTERVAL '$1' HOUR($2) TO MINUTE
expr.interval\ hour\ to\ second: INTERVAL '$1' HOUR($2) TO SECOND($3)
expr.interval\ minute\ to\ second: INTERVAL '$1' MINUTE($2) TO SECOND($3)
expr.interval\ year: INTERVAL '$1' YEAR($2)
expr.interval\ year\ to\ month: INTERVAL '$1' YEAR($2) TO MONTH
expr.interval\ month: INTERVAL '$1' MONTH($2)
~interval_year-@timestamp: $1 - cast($2 as timestamp)
@timestamp-~interval_year: cast($1 as timestamp) - $2
~interval_year+@timestamp: $1 + cast($2 as timestamp)
@timestamp+~interval_year: cast($1 as timestamp) + $2
~interval_year-@date: $1 - cast($2 as date)
@date-~interval_year: cast($1 as date) - $2
~interval_year+@date: $1 + cast($2 as date)
@date+~interval_year: cast($1 as date) + $2
~interval_day-@timestamp: $1 - cast($2 as timestamp)
@timestamp-~interval_day: cast($1 as timestamp) - $2
~interval_day+@timestamp: $1 + cast($2 as timestamp)
@timestamp+~interval_day: cast($1 as timestamp) + $2
~interval_day-@date: $1 - cast($2 as date)
@date-~interval_day: cast($1 as date) - $2
~interval_day+@date: $1 + cast($2 as date)
@date+~interval_day: cast($1 as date) + $2
#negative interval
#-~interval_day: (INTERVAL '0' DAY - $1)
#-~interval_year: (INTERVAL '0' YEAR - $1)
CAST(~interval_dayAS@char): LTRIM(TO_CHAR($1), '+')
CAST(~interval_dayAS@varchar): LTRIM(TO_CHAR($1), '+')
CAST(~interval_yearAS@char): LTRIM(TO_CHAR($1), '+')
CAST(~interval_yearAS@varchar): LTRIM(TO_CHAR($1), '+')
==============
Feedback
Yes
No
Powered by
