-2
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

error show

GENERATE_ARRAY(1658598950677000, 3317172759513000, 1) produced too many elements

Jutarut Junchaiyapoom
  • 37
  • 1
  • 5
  • Hi @Jutarut Junchaiyapoom , If my answer addressed your question, please consider accepting and upvoting it. If not, let me know so that I can improve my answer.Accepting an answer will help the community members with their research as well. – Shipra Sarkar Aug 24 '22 at 05:06

1 Answers1

0

There is no predefined limit for GENERATE_ARRAY but from the error it seems to be that this is occurring due to the large size of the array which is incurring out of memory error at runtime. The larger the array, the more likely to hit the runtime error. The exact number varies depending on the query which you are running. There is a 100 MB limit on the size of array that has been put to prevent accidentally writing very heavy CPU-bound queries.

For your requirement, you can split the array. For example :

SELECT ARRAY_CONCAT(GENERATE_TIMESTAMP_ARRAY(parameters), GENERATE_TIMESTAMP_ARRAY(parameters))...

Shipra Sarkar
  • 1,385
  • 3
  • 10