Getting Bigquery resources exceeded error in LookerML

[MMLafleur]

Hi, 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?

[ms4446]

The error you're encountering in Google BigQuery, "Resources exceeded during query execution," typically occurs when the query demands more resources than are available in your current BigQuery sl*t allocation. Here are some strategies to address this issue:

Sl*t Commitments

Sl*t commitments in BigQuery allow you to reserve a fixed amount of processing capacity for a predetermined price, ideal for predictable, high-volume workloads. These commitments ensure consistent performance and cost predictability. They are available in various types, including on-demand, flex, and annual, each catering to different needs. Flex Sl*ts, for instance, offer short-term, flexible commitments, perfect for handling sporadic spikes in data processing needs.

Autoscaling

BigQuery's approach to handling fluctuating workloads differs from traditional autoscaling in cloud services. Its on-demand pricing model scales automatically, charging based on the amount of data each query processes. This dynamic model ensures you only pay for what you use, providing flexibility and cost-efficiency for varying workloads.

Increasing Capacity

To enhance your BigQuery capacity, consider these strategies:

• Purchase Sl*t Commitments: Suitable for predictable, high-volume workloads, sl*t commitments offer guaranteed capacity. Choose a commitment type that aligns with your workload patterns.

• Optimize Queries: Improve query efficiency to reduce resource consumption. This can involve:

  • Specifying only necessary columns instead of using SELECT *.
  • Filtering data early in the query with WHERE clauses.
  • Employing window functions for complex calculations.

• Data Partitioning and Clustering: Partitioning divides large tables into manageable segments, reducing data scanned per query. Clustering sorts data within these partitions, enhancing query efficiency.

• Materialized Views: Use materialized views for frequently executed, complex queries. They store pre-computed results for quick retrieval.

• Monitoring and Adjusting Capacity: Regularly monitor query performance using BigQuery's tools and metrics. Adjust sl*t allocations or query strategies based on these insights.

• BigQuery Reservations: For more granular control over sl*t allocation and cost management, BigQuery Reservations can be a valuable tool, especially for organizations with complex needs across various projects and departments.

Consulting Documentation

Always refer to the official Google Cloud BigQuery documentation for the latest information. Cloud services evolve rapidly, and staying informed about new features and best practices is crucial for effective resource management in BigQuery.

[MMLafleur]

Thank you for the help @ms4446 . We will check this out.