You can translate the question and the replies:

Query impala not working when run over VQL vdp client.

i have query VQL like this: ``` select nmtkp, FORMATDATE('yyyyMM',cast(tglapprove as timestamp)) as blnlApprove, COUNT(distinct(sku)) as JlhBarang,SUM(price) as HargaTotal from dbmart.facttrans a where GETYEAR(cast(tgltrans as date))=2020 and jnsklaimreg='1' group by nmtkp, FORMATDATE('yyyyMM',cast(tglapprove as timestamp)) ``` query is running but so long and finish with timeout error i check query that denodo running in impala, show like this: ``` SELECT `t0`.`nmtkp`, `t0`.`sku`, `t0`.`price`, `t0`.`tglapprove` FROM `dbmart`.`facttrans` `t0` WHERE ((YEAR(CAST( `t0`.`tgltrans` AS timestamp )) = 2020) AND (`t0`.`jnsklaimreg` = '1')) ``` when i run direct with impala HUE like this: ``` select nmtkp, FROM_TIMESTAMP(tglapprove,'yyyyMM') as blnlBeban, COUNT(distinct(sku)) as JlhBarang, SUM(price) as HargaTotal from dbmart.facttrans where YEAR(cast(tgltrans as timestamp))=2020 and jnsklaimreg='1' group by nmtkp, FROM_TIMESTAMP(tglapprove,'yyyyMM') ``` result loaded fast and success. why did it happen? am i wrong running query with VQL VDP Client?
13-09-2020 00:37:00 -0400

2 Answers

Hi, Based on your explanation, the reason for slowness would be because the aggregation happens in the Denodo Virtual Layer meaning, the query is not completely delegated to the impala datasource. i.e the group by operation happens in Denodo because the **FORMATDATE** would not have delegated to the impala datasource for the pattern specified in your query. When this aggregation process exceeds the query timeout limit, the query finishes with the timeout error. Generally, in order to delegate this function **FORMATDATE** to a database, the execution engine translates the pattern to the equivalent one in the underlying database. If the database does not support the pattern, the execution engine will execute the function instead of delegating it to the database. Hence, you could modify the pattern for [FORMATDATE]( like below: > FORMATDATE('yyyyMMdd',cast(tglapprove as timestamp)) and then you could apply a [substring]( function to extract the string in the required format. Hope this helps!
Denodo Team
14-09-2020 06:54:56 -0400
thanks for your answer. exactly, i see the execution trace and see cause is no grouping delegate. i want aggregate by month and year. nice idea to suggest using substring. i tried it and the result come success and fast. thanks denodo team
14-09-2020 07:54:58 -0400
You must sign in to add an answer. If you do not have an account, you can register here