查询失败，错误为RESOURCE_EXHAUSTED(ORDER BY而不ORDER BY)

问题描述

我有一个下面的查询表，运行在一个大表(〜60GB)上.我认为WITH OFFSET是引擎盖下的某种ORDER BY吗?欢迎任何想法来解决这个问题.

I have a query of the below form, running on a large table (~60GB). I figure the WITH OFFSET is some sort of ORDER BY under the hood? Any ideas how to heal this problem are welcome.

我收到以下错误:作业xxx失败，出现错误RESOURCE_EXHAUSTED:查询执行期间超出了资源:无法在分配的内存中执行查询.高峰使用:限制的112％.内存使用大户:ORDER BY操作:98％其他/未分配:2％；职位编号:xxx

I get the following error:Job xxx failed with error RESOURCE_EXHAUSTED: Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 112% of limit. Top memory consumer(s): ORDER BY operations: 98% other/unattributed: 2% ; JobID: xxx

WITH
  test_data AS (
  SELECT
    CAST(CURRENT_TIMESTAMP() as DATETIME) datetime,
    'xyz' AS grp,
    '["7f9f98fh9g4ef393d3h5", "chg3g33f26949hg6067d", "g477e5973ec04g7c3232", "0de1ec83304d761he786", "3c1h1f153530g90g35c2", "946637g145h48322686f"]' AS ids
  UNION ALL
  SELECT
    CAST(CURRENT_TIMESTAMP() as DATETIME) datetime,
    'abc' AS grp,
    '["7f9f98fh9g4ef393d3h5", "chg3g33fdsfsdfs49hg6067d", "g477e5973ec04g7c3232", "0de1ec83304d761he786", "3c1h1f153530g90g35c2", "946637g145h48322686f"]' AS ids ),

  as_list AS (
    SELECT
      datetime,
      grp,
      id,
      pos
    FROM
     test_data, UNNEST(SPLIT(REGEXP_REPLACE(JSON_EXTRACT(ids,'$'), r'[\[\]\"]', ''), ',')) AS id WITH OFFSET AS pos)

SELECT
  *
FROM
  as_list
ORDER BY
  grp, datetime

推荐答案

要解决该问题，只需删除ORDER BY或添加LIMIT

To address that issue just either remove ORDER BY or add LIMIT

这篇关于查询失败，错误为RESOURCE_EXHAUSTED(ORDER BY而不ORDER BY)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！