snowflake cloud data platform - Max LOB size (16777216) exceeded for array_agg

Question

I have a table with about 30k rows and each of them is put in {}

in the end I would like to get it like this:

[
{Objekt1},
{Objekt2}
]

This solution worked well, as we haven't had that many rows. But now we get this limit.

COPY INTO   FROM ( 
SELECT array_agg(*)  FROM  ( 
SELECT   OBJECT_CONSTRUCT( ......   
OBJECT_CONSTRUCT(.....)  )  
from 
(select * from (select  
 REPLACE(parse_json(OFFER):"spec":"im:offerID",'"')::varchar AS ID, 
...,
... )))) )    
FILE_FORMAT = (TYPE = JSON    COMPRESSION = None )   
credentials = 
(aws_key_id=''aws_secret_key='')   
OVERWRITE = TRUE   single = true   
HEADER = FALSE   
max_file_size=267772160

We offer this to some external agency and that style is the only way, they can read it.

Is there another solution? Or a way to go around this problem?

Thanks

question from:https://stackoverflow.com/questions/65901325/max-lob-size-16777216-exceeded-for-array-agg

Answer 1

As you've discovered, there is a hard limit of 16Mb on array_agg (and in a lot of other places in Snowflake e.g. it's the max size for a variant column).

If it is acceptable to create multiple files then you can probably achieve this in a Stored Proc - find some combination of column values that will guarantee that the data in each partition will result in an array_agg size < 16Mb - and then loop through those partitions running a COPY INTO for each one and outputting to a different file each time.

If you have to produce a single file then I can't think of a way of achieving this in Snowflake (though someone else may be able to). If you can process the file once it is written to S3 then it would be straightforward to copy the data to a file as JSON and then edit it to add the '[' and ']' around it