当我不使用JOIN时，'太大以致JOIN'错误 [英] 'Too large to JOIN' error when I'm not using JOIN

问题描述

select 
CASE 
    WHEN .....
    ELSE .....
END AS carrier,
count(vehicle_id) as cnt
from test.vehicle_info 
WHERE vehicle_id NOT IN(select hardware_id 
                        from TABLE_DATE_RANGE(test.gps32_,DATE_ADD(CURRENT_TIMESTAMP(), -6,     'DAY'),DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
group by carrier
order by cnt

我得到这个错误：

And I got this error:

Query Failed
Error: Table too large for JOIN. Consider using JOIN EACH. For more details, please see https://developers.google.com/bigquery/docs/query-reference#joins
Job ID: red-road-574:job_e2o6sBjO9Dt5QrU_cRM2VHSRTso

是什么原因以及如何解决它？

What was the reason and how to solve it?

推荐答案

@霍布斯的猜测是正确的。使用 WHERE ... IN ... 和ANTIJOIN >）被实现为JOIN操作。解决这些限制的方法是使用加入EACH来自己重写为连接。即：

@Hobbs's guess above is correct. SEMIJOIN (using WHERE ... IN ...) and ANTIJOIN (using WHERE ... NOT IN ...) are implemented as JOIN operations. The way to work around these restrictions is to rewrite as a join yourself, using join EACH. That is:

select 
CASE 
    WHEN .....
    ELSE .....
END AS carrier,
count(vi.vehicle_id) as cnt
from test.vehicle_info vi
LEFT OUTER JOIN EACH (select hardware_id FROM TABLE_DATE_RANGE(...)) hi
ON vi.vechicle_id = hi.hardware_id
WHERE hi.hardware_id is NULL
group by carrier
order by cnt

这篇关于当我不使用JOIN时，'太大以致JOIN'错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！