BigQuery: How to Avoid "Resources exceeded during query execution." error

Question

I'm wondering how I can avoid the "resources exceeded during execution" error. Most of the other questions on about this involve JOIN EACH or GROUP EACH BY, but I'm already not using those. If I include the WHERE clause on the date or the ABS(HASH(userId)), then the query works, but I'd like to have the entire data set available and then I'm going to filter it further in Tableau.

If I remove t4 the query works, but I want that last column, and I'll want even more columns created out of the event_parameters field for later queries.

Job ID was rhi-localytics-db:job_6MaesvuMK6mP6irmAnrcM9R3cx8 in case that helps, Thanks.

SELECT
    t1.userId as userId,
    t1.event_time AS event_time,
    t1.Diamond_Balance as Diamond_Balance,
    t2.Diamond_Change as Diamond_Change,
    t3.Gold_Balance as Gold_Balance,
    t4.Gold_Change as Gold_Change
FROM (
    SELECT
        userId,
        event_time,
        INTEGER(event_parameters.Value) AS Diamond_Balance,
    FROM
        FLATTEN([game_data], event_parameters)
    WHERE
        event_name LIKE 'Currency'
        AND event_parameters.Name = 'Diamond_Balance'
        -- and date(event_time) > '2015-09-11'
        -- AND ABS(HASH(userId) % 5)  = 0
    GROUP BY
        userId,
        event_time,
        Diamond_Balance ) AS t1
INNER JOIN (
    SELECT
        userId,
        event_time,
        INTEGER(event_parameters.Value) AS Diamond_Change,
    FROM
        FLATTEN([game_data], event_parameters)
    WHERE
        event_name LIKE 'Currency'
        AND event_parameters.Name = 'Diamond_Change'
        AND INTEGER(event_parameters.Value ) < 14000
        AND INTEGER(event_parameters.Value ) > -14000
        -- and date(event_time) > '2015-09-11'
        -- AND ABS(HASH(userId) % 5)  = 0

    GROUP BY
        userId,
        event_time,
        Diamond_Change ) AS t2
ON
    t1.userId = t2.userId
    AND t1.event_time = t2.event_time
INNER JOIN (
    SELECT
        userId,
        event_time,
        event_parameters.Value AS Gold_Balance,
    FROM
        FLATTEN([game_data], event_parameters)
    WHERE
        event_name LIKE 'Currency'
        AND event_parameters.Name = 'Gold_Balance'
        -- and date(event_time) > '2015-09-11'
        -- AND ABS(HASH(userId) % 5)  = 0

    GROUP BY
        userId,
        event_time,
        Gold_Balance ) AS t3
ON
    t1.userId = t3.userId
    AND t1.event_time = t3.event_time
INNER JOIN (
    SELECT
        userId,
        event_time,
        INTEGER(event_parameters.Value) AS Gold_Change,
    FROM
        FLATTEN([game_data], event_parameters)
    WHERE
        event_name LIKE 'Currency'
        AND event_parameters.Name = 'Gold_Change'
        -- and date(event_time) > '2015-09-11'
        -- AND ABS(HASH(userId) % 5)  = 0
    GROUP BY
        userId,
        event_time,
        Gold_Change ) AS t4
ON
    t1.userId = t4.userId
    AND t1.event_time = t4.event_time

Answer 1

General advice on resources exceeded can be found here: https://stackoverflow.com/a/16579558/1375400

Note that adding EACH is generally the solution to, rather than the cause of, a resources exceeded error. (Though there are cases where it can work the other way around!)

Also, EACH is no longer meaningful on GROUP BY, and will shortly be irrelevant on JOIN.

Answer 2

I think you should be able to do all your logic in just one simple "scan".
No joins at all!
Something like below. Just idea - but has somechances to work as is :)

SELECT
    userId,
    event_time,
    MAX(CASE WHEN event_parameters.Name = 'Diamond_Balance' 
            THEN INTEGER(event_parameters.Value) END) AS Diamond_Balance,
    MAX(CASE WHEN event_parameters.Name = 'Diamond_Change' AND INTEGER(event_parameters.Value ) BETWEEN -14000 AND 14000 
            THEN INTEGER(event_parameters.Value)) END AS Diamond_Change,
    MAX(CASE WHEN event_parameters.Name = 'Gold_Balance' 
            THEN INTEGER(event_parameters.Value) END) AS Gold_Balance,
    MAX(CASE WHEN event_parameters.Name = 'Gold_Change' 
            THEN INTEGER(event_parameters.Value) END) AS Gold_Change
FROM
    FLATTEN([game_data], event_parameters)
WHERE
    event_name LIKE 'Currency'
GROUP BY
    userId,
    event_time