CODEWITHSUNDEEP
sqlgoogle-bigquery
Daniel Ziv
Daniel Ziv

Reputation: 5

How to Generate incredibly large arrays in sql

When trying to generate a large array using the following command

GENERATE_ARRAY(1467331200, 1530403201, 15)

I'm getting the following error:

google.api_core.exceptions.BadRequest: 400 GENERATE_ARRAY(1467331200, 1530403201, 15) produced too many elements

Is there a way to generate an array of said size?

Upvotes: 0

Views: 1391

Answers (1)

Christopher
Christopher

Reputation: 941

There is a limit on the number of result elements up to 1048575.

Test: bq query --dry_run --nouse_legacy_sq "[replace query below]"
Query: select GENERATE_ARRAY(1, 1048575) as test_array;
Output: Query successfully validated. Assuming the tables are not modified, running this query will process 0 bytes of data.

Query: select GENERATE_ARRAY(1, 1048576) as test_arr;
Output: GENERATE_ARRAY(1, 1048576, 1) produced too many elements

There's no mention of this limit in the documentation so I suggest that you either send a documentation feedback on the page or file a feature request to increase the limit or if possible remove the limit.

Possible workaround is to concat the array. Example: SELECT ARRAY_CONCAT(GENERATE_ARRAY(1,1048575), GENERATE_ARRAY(1,1048575))...

Upvotes: 3

Related Questions