我正在为我的公司建立一个粗略的数据仓库,我已经成功地将联系人、公司、交易和关联数据从我们的CRM中拉到bigquery中,但是当我通过我们的BI平台将这些数据连接到一个主表中进行分析时,我不断得到错误:
Query exceeded resource limits. This query used 22602 CPU seconds but would charge only 40M Analysis bytes. This exceeds the ratio supported by the on-demand pricing model. Please consider moving this workload to the flat-rate reservation pricing model, which does not have this limit. 22602 CPU seconds were used, and this query must use less than 10200 CPU seconds.
因此,我希望优化我的查询。我已经删除了所有的GROUP BY
和ORDER BY
命令,并尝试使用WHERE
命令进行额外的过滤,但这对我来说似乎不合逻辑,因为它会增加处理需求。
我当前的查询是:
SELECT
coy.company_id,
cont.contact_id,
deals.deal_id,
{another 52 fields}
FROM `{contacts}` AS cont
LEFT JOIN `{assoc-contact}` AS ac
ON cont.contact_id = ac.to_id
LEFT JOIN `{companies}` AS coy
ON CAST(ac.from_id AS int64) = coy.company_id
LEFT JOIN `{assoc-deal}` AS ad
ON coy.company_id = CAST(ad.from_id AS int64)
LEFT JOIN `{deals}` AS deals
ON ad.to_id = deals.deal_id;
供参考,{assoc-contact}
和{assoc-deal}
都是我从关联表中创建的独立视图,以便更容易地将这些表关联到companies表。
还应该注意的是,这个查询偶尔会成功运行,所以我知道它确实工作,它只是失败了大约90%的时间,由于查询太大了。
TLDR;
检查连接键。99%的情况下,问题的原因是组合爆炸。
我不能确定,因为我不能访问底层表的数据,但我会给出一个一般的解决方法,根据我的经验,每次都能找到根本原因。
长回答
调查方法假设你正在连接两个表
SELECT
cols
FROM L
JOIN R ON L.c1 = R.c1 AND L.c2 = R.c2
,你会遇到这个错误。您应该做的第一件事是检查两个表中是否有重复。
SELECT
c1, c2, COUNT(1) as nb
FROM L
GROUP BY c1, c2
ORDER by nb DESC
对于连接中涉及的每个表也是如此。
我打赌你会发现你的连接键是重复的。BigQuery是非常可扩展的,所以在我的经验中,当你有一个连接键在两个表上重复超过100,000次时,就会发生这个错误。这意味着在连接之后,您将拥有100000^2 = 100亿行!!
为什么BigQuery给出这个错误
根据我的经验,这个错误消息意味着你的查询与输入的大小相比计算太多了。如果您在连接每个表有几百万行之后最终有100亿行,那么您得到这个也就不足为奇了。
BigQuery的按需定价模型是基于表中读取的数据量。这意味着人们可能会试图滥用这一点,比如在读取小数据集的同时运行cpu密集型计算。举一个极端的例子,假设有人编写了一个Javascript UDF来挖掘比特币,并在BigQuery
上运行它。SELECT MINE_BITCOIN_UDF()
查询将被计费0美元,因为它不读取任何内容,但将消耗几个小时的谷歌CPU。当然,他们必须为此做点什么。
所以这个比率的存在是为了确保用户不会在处理几个Mb的输入时使用几个小时的cpu来做任何粗略的事情。
其他具有不同定价模式的MPP平台(例如Azure Synapse根据处理的字节数收费,而不是像BQ那样根据读取的字节数收费)可能会毫无抱怨地运行,然后为读取40Mb的表收取10Tb的费用。
注: