有没有一种方法可以增加BigQuery查询的分配内存？

Question

我有一张大表（约5900万行，7.1 GB），已按我想要的方式排序，并且我想查询此表并为每行获取一个row_number()。 不幸的是，我收到以下错误信息：

在查询执行期间超过了资源限制：无法在分配的内存中执行查询。

有没有办法在BigQuery中增加分配的内存？

这是我的查询语句，我不知道如何简化它，但如果您有任何建议，我会接受的。

SELECT
  row_number() over() as rowNumber,
  game,
  app_version,
  event_date,
  user_pseudo_id,
  event_name,
  event_timestamp,
  country,
  platform
FROM
`mediation_time_BASE`

以下是完整的错误信息：

在查询执行过程中资源超标：指定内存无法执行此查询。峰值使用率：限制的146%。最消耗内存的操作：分析OVER()语句：98%；其他/未指定：2%。

编辑： 这里的查询表示事件开始和结束的列表，我需要将开始事件与其结束链接起来，因此我遵循此提示：https://www.interfacett.com/blogs/how-to-use-values-from-previous-or-next-rows-in-a-query-in-sql-server/ 为此，我需要使用row_number（）获取行，并将这个子查询分成两个部分（一个是事件开始，另一个是事件结束），然后将它们连接在一起，并按照下面的方式得到每个事件的开始和结束时间（其中subquery表示带有row_number()的查询）：

SELECT
   (case when lead(inter.rowNumber) OVER(ORDER BY inter.rowNumber) - inter.rownumber =1
          then lead(inter.rowNumber) OVER(ORDER BY inter.rowNumber)
          else inter.rownumber end) as rowNumber,
    min(inter_success.rowNumber) as rowNumber_success,
    inter.game,
    inter.app_version,
    inter.event_date,
    inter.user_pseudo_id,
    inter.event_timestamp as event_start,
    min(inter_success.event_timestamp) as event_end,
    inter_success.event_name as results
FROM
    (SELECT * FROM `subquery` where event_name = 'interstitial_fetch') as inter INNER JOIN 
    (SELECT * FROM `subquery` where event_name = 'interstitial_fetch_success') as inter_success
            ON inter.rowNumber < inter_success.rowNumber and inter.game= inter_success.game and inter.app_version = inter_success.app_version and inter.user_pseudo_id = inter_success.user_pseudo_id 
GROUP BY inter.rowNumber,inter.game,inter.app_version,inter.event_date,inter.user_pseudo_id,inter.event_timestamp,inter_success.event_name

这在小数据集上运行良好，但对于5900万行的数据集就会出现问题...

- Sophie Hamelin

---

如果删除 row_number() over() as rowNumber，会发生什么？ - Cloud Ace Wenyuan Jiang

它可以工作，但我的问题是我需要每一行的字段行号。这个查询将被保存为一个表，并将在更复杂的查询中调用，其中我需要行号。 - Sophie Hamelin

你能否更清楚地解释一下为什么需要下一个查询的行号？这代表某种排序还是只是行的ID？ - Stewart_R

你说的“已按我想要的顺序排序”是什么意思？在关系表中存储的数据并没有以任何特定的顺序存储。 - Felipe Hoffa

必须使用行号吗？GENERATE_UUID() 怎么样？ - Cloud Ace Wenyuan Jiang

Generate_UUID() 对我无效，因为我需要一个整数来连接子查询，具体取决于行号（上方或下方）的值。 - Sophie Hamelin

Answer 1

TL;DR: 你不需要增加BigQuery的内存。

为了回答这个问题，你需要了解BigQuery的工作原理。BigQuery依赖于被称为“slots”的执行器机器。这些slot都是相同类型的，并且具有有限的内存。

现在，许多操作将数据分割到多个slot之间（例如GROUP BY），每个slot对数据的一部分进行减少，并将结果向上发送到执行树中。

一些操作必须在单个机器上执行（例如SORT和OVER），请参见here。当你的数据超出了slot的内存时，你会遇到描述的错误。因此，你真正需要做的是将slot类型更改为更高内存的机器。不幸的是，这是不可能的。你将不得不遵循查询最佳实践，以避免对太多数据进行单个slot操作。

有一件事可能会对你有所帮助，那就是使用PARTITIONS计算OVER()，这样每个分区将被发送到不同的机器。 看这个例子。另一件通常有帮助的事情是如果你还没有这样做，可以转向标准SQL。

- gidutz

---

使用 partition by ... 解决了我的问题！ - Canovice

Answer 2

根据官方文档，您需要请求增加预订的插槽数量...

按需定价每个项目的最大并发插槽数为2,000。按需查询的默认插槽数量在单个项目中共享给所有查询。通常，如果一次处理的查询少于100 GB，则不太可能使用全部2,000个插槽。

要检查您正在使用多少插槽，请参阅使用Stackdriver监控BigQuery。如果您需要超过2,000个插槽，请联系您的销售代表，以讨论是否需要使用固定费率定价。

有关插槽的详细信息，请参见1。请求更多内存的过程在此处2。

- Paddy Popeye

Answer 3

• 如果要增加项目中的BigQuery插槽，您可能需要联系Google Cloud支持或购买预留实例。

• 我假设您在使用with子句作为子查询，而子查询会耗尽内存。我提出的解决方案是创建一个过期表，该表将在几天后自动过期，语法如下：

  OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 5 DAY))

• 采用这种方法，我想将查询结果的5900万行插入到一个过期表中，将使用更少的插槽。请用过期表名称替换您随后的子查询。

• 为了避免对过期表进行计费，您可以在所有相关查询执行完毕后将其删除。

- Hil Liao