如何解决BigQuery中GENERATE_ARRAY()生成元素过多的报错问题

报错原因 #

GENERATE_TIMESTAMP_ARRAY生成的时间戳数组元素数量超出BigQuery默认上限（最多100万元素）。从错误信息的时间戳数值来看，你的event_datetime_start和event_datetime_end之间时间跨度极大，按每分钟生成一个时间戳的逻辑，数组长度远超限制。

解决方法 #

1. 过滤超长跨度的脏数据 #

先筛选出时间跨度在合理业务范围内的记录，避免生成过大数组：

select *
, row_number() OVER(PARTITION BY user_id,event_datetime_start,event_datetime_end ORDER BY user_id, event_datetime_start, event_datetime_end,dt_watched) rk
from `blackout_tv_july` a
-- 示例：限制时间跨度不超过30天，可根据业务调整
where timestamp_diff(event_datetime_end, event_datetime_start, DAY) <= 30
cross join unnest(GENERATE_TIMESTAMP_ARRAY(event_datetime_start, datetime_add(event_datetime_end, interval 1 MINUTE), interval 1 MINUTE)) dt_watched

2. 调整数组元素上限（有限制） #

BigQuery支持通过参数提升数组最大长度，上限为1000万元素。如果时间跨度在这个范围内，可使用：

SET max_array_length = 10000000;
select *
, row_number() OVER(PARTITION BY user_id,event_datetime_start,event_datetime_end ORDER BY user_id, event_datetime_start, event_datetime_end,dt_watched) rk
from `blackout_tv_july` a
cross join unnest(GENERATE_TIMESTAMP_ARRAY(event_datetime_start, datetime_add(event_datetime_end, interval 1 MINUTE), interval 1 MINUTE)) dt_watched

3. 放宽时间粒度（业务允许的话） #

如果不需要每分钟的时间粒度，增大时间间隔来减少数组元素：

select *
, row_number() OVER(PARTITION BY user_id,event_datetime_start,event_datetime_end ORDER BY user_id, event_datetime_start, event_datetime_end,dt_watched) rk
from `blackout_tv_july` a
-- 示例：改为每小时生成一个时间戳
cross join unnest(GENERATE_TIMESTAMP_ARRAY(event_datetime_start, datetime_add(event_datetime_end, interval 1 HOUR), interval 1 HOUR)) dt_watched

4. 校验原始数据合法性 #

错误信息中的时间戳数值异常，可能是数据存储格式错误（比如混淆了毫秒/微秒）或脏数据。需要检查event_datetime_start和event_datetime_end的取值是否符合业务逻辑，修正异常数据后再执行SQL。

内容的提问来源于stack exchange，提问作者Jutarut Junchaiyapoom