0

I'm running out of memory with my query on Google BigQuery. I have to calculate multiple window functions like running sums over multiple different time frames. My data mainly consists of an id (string), a value (number), a type ('in' or 'out', could be converted to bool is needed) and a timestamp.

I read that there is no way to increase memory per slot, so the only way to be able to execute the query is to cut it into smaller pieces that can be sent to different slots. A way to do this is to use GROUP BY or OVER (PARTITION BY ...) but I have no idea how I could rewrite my query to make use of it.

I have some calculations that need to use PARTITION BY but for others, I want to calculate the total overall, for example:

Imagine a have a large table (> 1 billion rows) where I want to calculate a rolling sum over all values for different time frames, independent of id.

WITH data AS (
  SELECT * 
  FROM UNNEST([
    STRUCT
    ('A' as id,1 as value, 'out' as type, 1 as time), 
    ('A', -1, 'in', 2),
    ('B', 2, 'out', 2),
    ('C', 1, 'out', 3),
    ('B', -1, 'in', 4),
    ('A', 2, 'out', 4),
    ('C', 5, 'out', 5),
    ('B', 3, 'out', 6),
    ('A', 1, 'out', 6),
    ('A', -4, 'in', 6),
    ('C', -3, 'in', 7)
  ])
)
SELECT 
  id
, value
, type
, time
, SUM(value) OVER (ORDER BY time RANGE UNBOUNDED PRECEDING) as total
, SUM(value) OVER (ORDER BY time RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) as total_last_day
, SUM(value) OVER (ORDER BY time RANGE BETWEEN 3 PRECEDING AND 2 PRECEDING) as total_prev_day
FROM data

How could I split this query to make use of PARTITION BY or GROUP BY in order to fit within the memory limits?

smaica
  • 723
  • 2
  • 11
  • 26
  • maybe silly to ask but anyway - does it work if you have only `total` in your query and remove `total_last_day` and `total_prev_day` ? – Mikhail Berlyant Sep 30 '22 at 20:39
  • also - what is the real data type of `time`? is it timestamp or datetime or date? or is it really int64? knowing this would make a difference. – Mikhail Berlyant Sep 30 '22 at 21:14
  • In my original data time is timestamp, but I can easily convert this to int64 by `TIMESTAMP_DIFF(time,'0001-01-01 00:00:00',SECOND) AS timestamp_to_int` – smaica Oct 01 '22 at 07:18

2 Answers2

1

Try below approach - I think it has good chances to resolve your issue

SELECT *
FROM data
JOIN (
  SELECT time
  , SUM(time_value) OVER (ORDER BY time RANGE UNBOUNDED PRECEDING) as total
  , SUM(time_value) OVER (ORDER BY time RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) as total_last_day
  , SUM(time_value) OVER (ORDER BY time RANGE BETWEEN 3 PRECEDING AND 2 PRECEDING) as total_prev_day
  FROM (
    SELECT time, SUM(value) time_value
    FROM data
    GROUP BY time
  )
)
USING (time)

if applied to sample data in your question - output is

enter image description here

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Great, thanks Mikhail, I'll give it a try and let you know if it works on my data. – smaica Oct 01 '22 at 07:21
  • Perfect! I tested the approach and it works just fine. The results are the same, I don't get any memory issues. Also, it's much faster than before. Great! It's a pleasure to learn from your input, thanks. – smaica Oct 01 '22 at 10:37
0

Window functions with specially order by partition by etc are in general very heavy and using it on big data can take long.

It looks like your expected result is keying of of your id in your sample query.

There are a few things you can check:

see if your source data can be "cluster by" id so processing will be faster to start with.

If that does not work, then see adding following style of filters will work or not:

select ...
where mod(farm_fingerprint(id), 5) = 0

store it in a table and keep appending next mods (=1 to 4) to that table. "%5=0" mod is given as a sample, you have to experiment with it knowing your source data. Here using mod will split your source data into 5 smaller buckets to work with so you have to append it all later. That way amount of data needed for BQ internal memory will be less and it might process results in its limits.

If any of above ideas work, you can all do that in a sql script so you can create temp tables and work with those.

Pratik Patil
  • 612
  • 1
  • 5
  • 18