Bigquery resources exceeded during query execution - optimization

Question

I have got a problem with this query.

SELECT event_date, country, COUNT(*) AS sessions,
       AVG(length) AS average_session_length
  FROM (

SELECT country, event_date, global_session_id,
       (MAX(event_timestamp) - MIN(event_timestamp))/(60 * 1000 * 1000) AS length
  FROM (
SELECT user_pseudo_id,
       event_timestamp,
       country,
       event_date,
       SUM(is_new_session) OVER (ORDER BY user_pseudo_id, event_timestamp) AS global_session_id,
       SUM(is_new_session) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS user_session_id
  FROM (
       SELECT *,
              CASE WHEN event_timestamp - last_event >= (30*60*1000*1000) 
                     OR last_event IS NULL 
                   THEN 1 ELSE 0 END AS is_new_session
         FROM (
              SELECT user_pseudo_id,
                     event_timestamp,
                     geo.country,
                     event_date,
                     LAG(event_timestamp,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS last_event
                FROM `xxx.events*`
              ) last
       ) final
       ) session
 GROUP BY global_session_id, country, event_date

       ) agg
WHERE length >= (10/60)
group by country, event_date

Google Cloud Console gives that error

Resources exceeded during query execution: The query could not be executed in the allotted memory.

I know that it is probably a problem with OVER clauses, but I do not have idea how to edit query to get the same results. I would be thankful for some help. Thank you guys!

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-12-19T15:08:57

If I had to guess, it is this line:

  SUM(is_new_session) OVER (ORDER BY user_pseudo_id, event_timestamp) AS global_session_id,

I would recommend changing the code so the "global" session id is really local to each user:

  SUM(is_new_session) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS global_session_id,

If you adjust the query and this basically works, then the resource problem is fixed. The next step is to figure out how to get the global id that you want. The simplest solution is to use a local id for each user.

Bigquery resources exceeded during query execution - optimization

1 Answers