查询执行过程中超出Bigquery资源

Question

我有一个每日调度程序来运行Bigquery上的作业，但是，由于内存使用不足，它崩溃了。作业包含来自5个表的最新信息，这意味着我使用了超过(…)从每个表中查询更新的记录需要按5次排序，这消耗了大量的内存使用。是否有通过重构查询来修复错误的有效方法?

下面是简短的代码结构:

CREATE TEMP TABLE main_info AS
WITH orders_1 AS(
select 
* except(rnk)
from(
select
*,
ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) AS rnk
from order_1
)
where rnk = 1
),

orders_2 AS(
select 
* except(rnk)
from(
select
*,
ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) AS rnk
from order_2
)
where rnk = 1
),
orders_3 AS(
select 
* except(rnk)
from(
select
*,
ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) AS rnk
from order_3
)
where rnk = 1
)
SELECT 
*
FROM orders_1 o1
LEFT JOIN orders_2 o2
ON o1.order_id = o2.order_id
LEFT JOIN orders_3 o3
ON o1.order_id = o3.order_id

我期望在限制下减少内存使用。我做了一些研究，发现用row_number()代替…排序)与array_agg()优化性能或创建每个表的临时表，并将其合并?有更好的建议吗?

Answer 1

我不确定这是否能解决您的问题，但我们绝对可以使用QUALIFY来简化您的cte。例如:

SELECT *
FROM order_1
QUALIFY ROW_NUMBER() OVER(order_window) = 1
WINDOW order_window AS (
PARTITION BY order_id
ORDER BY update_time DESC
)

(也使用WINDOW可读性)

这可能有助于消除子查询，但这取决于它是否已经在幕后优化到相同的事情。

其他想法:

• 左连接得到你非常不同的结果，内部连接?如果是这样，你可以预先过滤你的第二个和第三个cte，不包括即将被丢弃的订单id。
• 必须是您创建的临时表吗?或者您可以为每个cte创建完整的表，并分阶段构建它?

Answer 2

创建单独的临时表并合并它们。与初始查询相比，这将减少内存利用率。为了立即释放内存，您可以在适当的步骤删除这些临时表。参考下面的分割:

CREATE TEMP TABLE orders_1 AS(
select 
* except(rnk)
from(
select
*,
ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) AS rnk
from order_1
)
where rnk = 1 );

CREATE TEMP TABLE orders_2 AS(
select 
* except(rnk) 
from(
select
*,
ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) AS rnk
from order_2
)
where rnk = 1 );
CREATE TEMP TABLE orders_3 AS(
select 
* except(rnk)
from(
select
*,
ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY update_time DESC) AS rnk
from order_3
)
where rnk = 1);
CREATE TEMP TABLE main_info AS
SELECT * 
FROM orders_1 o1
LEFT JOIN orders_2 o2
ON o1.order_id = o2.order_id
LEFT JOIN orders_3 o3
ON o1.order_id = o3.order_id;
DROP TABLE orders_1;
DROP TABLE orders_2;
DROP TABLE orders_3;