BigQuery Query exceeded resource limits. when using joins with OR

Question

I have BigQuery database and see an error after 30 seconds of execution :

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

when using such query:

SELECT
  b.date as Date, 
  SUM(b.revenue) as Revenue
FROM `dataset.a` a
JOIN `dataset.b` b ON b.id = a.low_id OR UPPER(b.id) = a.high_id
WHERE DATE(a.date_and_time) >= DATE('2020-02-01')
AND DATE(a.date_and_time) <= DATE('2020-02-25')

GROUP BY b.date
ORDER BY b.date

I noticed that if I remove grouping and just return b records, it will work and last for 40 seconds. But when I remove b.id = a.low_id or UPPER(b.id) = a.high_id from the JOIN it works and lasts for 3 seconds!

Could you please explain such behaviour? And is it real to make this query work without buying additional slots?

I'm surprised that BigQuery even accepts the query. Please provide sample data and desired results. OR kills query performance. And why do you need to use UPPER() on the id -- but only for one comparison? — Gordon Linoff
@GordonLinoff I cannot show the data, but can describe it: table a contains information about apple and android installs, android_id and apple_id (apple id is stored in upper case). Table b has information about advertising views, it has field advertising_id thet matches android_id field or apple_id field in upper case from table a. Each b recod also has a revenue and date. I want to see revenue by date for specific dates. Each table size is 250 mb — Alex Zaitsev

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-03-03T12:12:12

Try using this trick. It constructs an array out of the two ids and then unnests them. The rest is then just your JOIN:

SELECT b.date as Date, SUM(b.revenue) as Revenue
FROM `dataset.a` a JOIN
     (`dataset.b` bl CROSS JOIN
      UNNEST(ARRAY(a.low_id, a.hi_id)) a_id
     )
      ON bl.id = a.a_id 
WHERE DATE(a.date_and_time) >= DATE('2020-02-01') AND
      DATE(a.date_and_time) <= DATE('2020-02-25')
GROUP BY b.date
ORDER BY b.date

BigQuery Query exceeded resource limits. when using joins with OR

1 Answers