时间戳

首页 > 解决方案 > 运行查询时出错 查询执行期间超出资源

sql - 运行查询时出错 查询执行期间超出资源

问题描述

当我在 GBQ 中运行该查询时，我收到此错误：

运行查询时出错

查询执行期间超出资源：没有足够的资源用于查询计划 - 子查询太多或查询太复杂.. at [1:1]

下面的代码不是我开发的，所以我很难重写它。如果你能帮助我让它再次运行，我会听听。

execute immediate (select concat('create or replace table `ssmdw-208309.temp_piwik_and_ga.005_visitor_union` as ',query) from (select string_agg(CONCAT(' select substr(generate_uuid(),25,12) as rowid, ',columns,' from `piwik-272210.piwik.',table_name,'`'),'\nUnion all\n')  query FROM (
  SELECT
    table_column.table_name,
    STRING_AGG(CASE
        WHEN x.column_name IS NULL THEN CONCAT('null as ',table_column.column_name)
      ELSE
      table_column.column_name
    END
      ,', '
    ORDER BY
      table_column.column_name) columns
  FROM (
    SELECT
      tables.table_name,
      columns.column_name
    FROM (
      SELECT
        DISTINCT column_name
      FROM
        `piwik-272210.piwik.INFORMATION_SCHEMA.COLUMNS` a where table_name like 'visitor_%') columns
    FULL OUTER JOIN (
      SELECT
        DISTINCT table_name
      FROM
        `piwik-272210.piwik.INFORMATION_SCHEMA.COLUMNS` b where table_name like 'visitor_%') tables
    ON
      (1=1)) table_column
  LEFT JOIN
    `piwik-272210.piwik.INFORMATION_SCHEMA.COLUMNS` x
  ON
    (x.table_name = table_column.table_name
      AND x.column_name = table_column.column_name)
  GROUP BY
    1 )))

标签： sqlgoogle-bigquery

解决方案

尽可能避免使用“order by”子句。“排序依据”尝试将结果拉到一个通常会导致此问题的节点中。

推荐阅读