Google BigQuery exceeds limits

0
votes

I am using Google BigQuery to pull data from Google Ads and manipulate it (joins and selects).

Some queries fail and i get this error message:

Query exceeded resource limits. 298447.48512848176 CPU seconds were used, and this query must use less than 175300.0 CPU seconds.

Can i somehow raise the limits? I don't want to simplify the query.

one of the used queries:

select distinct
        arbs.ExternalCustomerId as AccountId,
        arbs.CampaignId,
        campaign.CampaignName,
        arbs.AdGroupId,
        ad.AdGroupName,
        ar.CriterionId as AgeID,
        ar.Criteria as AgeRange,
        arbs.Date,
        arbs.Impressions,
        arbs.Clicks,
        arbs.Cost/1000000 as Cost_LocalCurrency,
        arcs.AllConversions,
        arcs.ConversionTypeName
from google_adwords_all_reports.p_AgeRangeBasicStats_1636228512 arbs
  left join google_adwords_all_reports.p_AgeRangeConversionStats_1636228512 arcs on arbs.CriterionId = arcs.CriterionId and arbs.Date = arcs.Date and arbs.ExternalCustomerId= arcs.ExternalCustomerId and arbs.CampaignId = arcs.CampaignId
  inner join google_adwords_all_reports.p_AgeRange_1636228512 ar on arbs.CriterionId = ar.CriterionId and arbs.ExternalCustomerId = ar.ExternalCustomerId and arbs.CampaignId = ar.CampaignId
  inner join google_adwords_all_reports.p_AdGroup_1636228512 ad on arbs.AdGroupId = ad.AdGroupId
  inner join google_adwords_all_reports.p_Campaign_1636228512 campaign on arbs.CampaignId = campaign.CampaignId and arbs.ExternalCustomerId = campaign.ExternalCustomerId
 where not ar.Status = 'REMOVED'

And isn't Flat-rate pricing plan expensive? as i understood it will be 1500$? compared our current on demand pricing of 25$. Or do i missunderstand something?

additionally i create with this query duplicates. is this due to the segmentation in google ads?

Thank you for your help already

1
google-bigquery
Welcome, please share an example of your query, it can likely be optimised to work around quotas. - Ben P

1 Answers

0
votes

In order to mitigate the error appearance, that affecting some of your queries, you might concern the two options:

  • Follow Biqquery best practices guidelines for optimizing query performance. You may find there query optimization steps affording decent computation resource utilization;
  • Switching from On-demand pricing plan to Flat-rate one, acquiring Bigquery Reservations mechanism and adjusting Slot pool size according to your needs.

As @ Ben P mentioned, you can add the query example to get more efforts from the community.