we are getting the "Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 100% of the limit. Top memory consumer(s): aggregate functions and GROUP BY clauses: 100%" error when running the following query in Bigquery.
"
SELECT
lh_shopify_purchase_behaviour_cohort_base.customer_id AS lh_shopify_purchase_behaviour_cohort_base_customer_id,
lh_shopify_purchase_behaviour_cohort_base.cltv_for AS lh_shopify_purchase_behaviour_cohort_base_cltv_for_1,
lh_shopify_purchase_behaviour_cohort_base.channel AS lh_shopify_purchase_behaviour_cohort_base_channel_1,
lh_shopify_purchase_behaviour_cohort_base.discount_responsiveness AS lh_shopify_purchase_behaviour_cohort_base_discount_responsiveness,
lh_shopify_purchase_behaviour_cohort_base.cohort AS lh_shopify_purchase_behaviour_cohort_base_cohort,
CASE WHEN COUNT(lh_shopify_purchase_behaviour_cohort_base.actual_cltv ) <= 10000 THEN (ARRAY_AGG(lh_shopify_purchase_behaviour_cohort_base.actual_cltv IGNORE NULLS ORDER BY lh_shopify_purchase_behaviour_cohort_base.actual_cltv LIMIT 10000)[OFFSET(CAST(FLOOR(COUNT(lh_shopify_purchase_behaviour_cohort_base.actual_cltv ) * 0.5 - 0.0000001) AS INT64))] + ARRAY_AGG(lh_shopify_purchase_behaviour_cohort_base.actual_cltv IGNORE NULLS ORDER BY lh_shopify_purchase_behaviour_cohort_base.actual_cltv LIMIT 10000)[OFFSET(CAST(FLOOR(COUNT(lh_shopify_purchase_behaviour_cohort_base.actual_cltv ) * 0.5) AS INT64))]) / 2 ELSE APPROX_QUANTILES(lh_shopify_purchase_behaviour_cohort_base.actual_cltv ,1000)[OFFSET(500)] END AS lh_shopify_purchase_behaviour_cohort_base_t_actual_cltv_1,
CASE WHEN COUNT(lh_shopify_purchase_behaviour_cohort_base.predicted_cltv ) <= 10000 THEN (ARRAY_AGG(lh_shopify_purchase_behaviour_cohort_base.predicted_cltv IGNORE NULLS ORDER BY lh_shopify_purchase_behaviour_cohort_base.predicted_cltv LIMIT 10000)[OFFSET(CAST(FLOOR(COUNT(lh_shopify_purchase_behaviour_cohort_base.predicted_cltv ) * 0.5 - 0.0000001) AS INT64))] + ARRAY_AGG(lh_shopify_purchase_behaviour_cohort_base.predicted_cltv IGNORE NULLS ORDER BY lh_shopify_purchase_behaviour_cohort_base.predicted_cltv LIMIT 10000)[OFFSET(CAST(FLOOR(COUNT(lh_shopify_purchase_behaviour_cohort_base.predicted_cltv ) * 0.5) AS INT64))]) / 2 ELSE APPROX_QUANTILES(lh_shopify_purchase_behaviour_cohort_base.predicted_cltv ,1000)[OFFSET(500)] END AS lh_shopify_purchase_behaviour_cohort_base_t_projected_cltv_1
FROM LH_Shopify_Purchase_Behaviour_Cohort_Base AS lh_shopify_purchase_behaviour_cohort_base
WHERE (lh_shopify_purchase_behaviour_cohort_base.cltv_for ) IN ('05 Months', '36 Months')
GROUP BY
1,
2,
3,
4,
5
ORDER BY
6 DESC
LIMIT 5000
"
The query runs fine we remove the Order By clause from it. Now the issue is that the query is automatically generated from the looker ML and we can't remove the Order By clause from it manually. So, it is failing to give the records.
So, is there any way to like increasing the capacity of Bigquery so that this issue get resolve?
We explored on forums but they are asking to update the query, but we will not be able to update the query as it is automatically created by Looker ML.
We are checking if this issue is because of Bigquery capacity or not.