You can translate the question and the replies:

Push down Analytical Functions to Google BigQuery

HI Denodo Team. I'm working with a Bigquery connection and I get an error when I define a derived view in "Virtual DataPort", when the query contains an analytic function, such as: ROW_NUMBER() OVER (PARTITION BY ...). **Is it possible to push down Analytic Function to BigQuery?** Here the query: SELECT cod_period AS cod_period, cod_agent_padre AS cod_agent_padre, sum(contr_pesato_new_qpi) AS somma_contr_pesato_new_qpi, sum(val_peso_liv) AS somma_pesi_liv, (sum(contr_pesato_new_qpi)/sum(val_peso_liv)) AS val_indic_new_qpi_mgr, case WHEN (sum(num_e) > 0) THEN 1 ELSE 0 END AS presenza_e, **ROW_NUMBER() OVER (PARTITION BY cod_period ORDER BY (sum(contr_pesato_new_qpi)/sum(val_peso_liv))) AS RowNum** FROM v1_calc_new_qpi GROUP BY cod_period, cod_agent_padre HAVING sum(val_peso_liv) > 0 CONTEXT ('formatted' = 'yes'); And here is the error I get: **"Finished with error: Error executing view: Function row_number is not executable".** By commenting out the ROW_NUMBER() line, the query is executed. "v1_calc_new_qpi" is a derived view that only works with tables into BigQuery Thanks. Regards, Marco
02-11-2023 05:43:17 -0400

1 Answer

Hi, In general, the **row_number()** function, as well as the rest of the [analytical functions]( supported by Denodo, can be [delegated]( by the Virtual Data Port to a data source, but they cannot be executed in the Virtual layer of Denodo. Thus, when a data source does not support the analytic function, this error is thrown. Also, please be noted that in order to push down a function applied over two joined views, both views have to be created over the same datasource, else the delegation will not be successful. As a work around for this scenario, you can either use the Denodo Cache or the Data Movement feature. This way you could force the query to be entirely delegated to a single data source, and thus avoiding the reported error. You could also refer to the [Workaround to Execute Analytic Functions]( documentation for more details. Further, if you would like to get more assistance other than the above-provided details, and if you have valid Support access, then I suggest you to open a new Support case where we could assist you more. Hope this helps!
Denodo Team
09-11-2023 09:35:41 -0500
You must sign in to add an answer. If you do not have an account, you can register here