3

Team,

I have a query to discuss: Table to file creation in snowflake

We have used the max file size with the compression, but at some times the data which is retrieving and size it is storing in the file getting exceeds. But we remove the SINGLE=TRUE there are multiple files getting generated and takes up lot time to fix and merge the files. We need to know a step to create the file if the maximum file size exceeds??

copy into @SNOWFLAKE_AZURE_STAGE/data/load/30jan/etl_file_20210223.dat.csv.gz 
                        from DB.SOURCE_TABLE 
                        file_format = (
                            type = csv
                            COMPRESSION = 'gzip'
                            field_delimiter = '|'
                            field_optionally_enclosed_by = NONE
                            empty_field_as_null = FALSE
                            RECORD_DELIMITER = ''
                            escape='None' 
                        )
                        OVERWRITE = TRUE
                        MAX_FILE_SIZE = 5368706371
                        SINGLE = TRUE
                        HEADER = True;
jarlh
  • 42,561
  • 8
  • 45
  • 63
Rocky1989
  • 369
  • 8
  • 28
  • Can you use folders ... then process the folder in same fashion as you would the files ...just that you look inside and process all the files in that folder ? – Adrian White Feb 02 '23 at 07:55
  • @adrian, got it, but the requirement is to generate a single file. – Rocky1989 Feb 02 '23 at 08:35
  • Does the single file size exceed the limit set for Azure storage? If not, why not just remove the MAX_FILE_SIZE parameter from your copy statement? I'm not sure why you have this if you want to create files greater than this size? – NickW Feb 02 '23 at 09:28
  • I think there's a hard limit on file sizes -> enforced automatically via breaking the file up into chunks of 5368706371 or less. The SINGLE=TRUE can force the single file however it's still adhering to the 5368706371 limit -> throwing an error if ignored. – Adrian White Feb 02 '23 at 18:46

0 Answers0