sql - 大查询 : How to Avoid "Resources exceeded during query execution." error

Question

我想知道如何避免“执行期间超出资源”错误。关于此的大多数其他问题都涉及 JOIN EACH 或 GROUP EACH BY，但我已经没有使用这些了。如果我在日期或 ABS(HASH(userId)) 中包含 WHERE 子句，则查询有效，但我希望整个数据集可用，然后我将在 Tableau 中进一步过滤它。

如果我删除 t4 查询有效，但我想要最后一列，并且我希望从 event_parameters 字段创建更多列以供以后查询。

作业 ID 是 rhi-localytics-db:job_6MaesvuMK6mP6irmAnrcM9R3cx8 以防万一，谢谢。

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

可以在此处找到有关超出资源的一般建议:
https://stackoverflow.com/a/16579558/1375400

注意添加 EACH通常是资源超出错误的解决方案，而不是原因。 (尽管在某些情况下它可以反过来工作!)

另外，EACH在 GROUP BY 上不再有意义，并且很快将与 JOIN 无关.