staging - Snowflake filenaming while using copy into location instead of data_UUID to specific naming.csv - Stack Overflow

Below copy into creates file in s3 with filename data_17658djhgu167658.csv Need input on naming files with specific contractid.csv when unloading data to s3.TIA

COPY INTO @DEV_TEST/TEST_AMOUNT/LOANS/2024/11/Q4_2024_TEST/
                                FROM (
                                SELECT 
                                        *
                                FROM DEV.TEST_DATA )
                                PARTITION BY ('contractid' || ContractID)
                                FILE_FORMAT=(
                                    TYPE = CSV
                                    ESCAPE = NONE
                                    ESCAPE_UNENCLOSED_FIELD = NONE
                                    COMPRESSION = NONE
                                    NULL_IF='NULL'
                                )
                                HEADER = TRUE
                                
                                MAX_FILE_SIZE = 5000000000
                                
                                ;```

Below copy into creates file in s3 with filename data_17658djhgu167658.csv Need input on naming files with specific contractid.csv when unloading data to s3.TIA

COPY INTO @DEV_TEST/TEST_AMOUNT/LOANS/2024/11/Q4_2024_TEST/
                                FROM (
                                SELECT 
                                        *
                                FROM DEV.TEST_DATA )
                                PARTITION BY ('contractid' || ContractID)
                                FILE_FORMAT=(
                                    TYPE = CSV
                                    ESCAPE = NONE
                                    ESCAPE_UNENCLOSED_FIELD = NONE
                                    COMPRESSION = NONE
                                    NULL_IF='NULL'
                                )
                                HEADER = TRUE
                                
                                MAX_FILE_SIZE = 5000000000
                                
                                ;```

• snowflake-cloud-data-platform
• staging

Share Improve this question Follow asked Nov 18, 2024 at 18:01 gayathrigayathri 4377 bronze badges 2

• There are several examples in SO, you can read this answer => stackoverflow/questions/61597017/… – samhita Commented Nov 19, 2024 at 10:20
• This is from documentation => docs.snowflake/en/sql-reference/sql/copy-into-location The optional path parameter specifies a folder and filename prefix for the file(s) containing unloaded data. If a filename prefix is not included in path or if the PARTITION BY parameter is specified, the filenames for the generated data files are prefixed with data_. – samhita Commented Nov 19, 2024 at 10:21

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

There is the parameter "Single=True" that would remove the UUID part from the file that is being unloaded to S3 using the "copy into" command. https://docs.snowflake/en/sql-reference/sql/copy-into-location

but for the custom filename, I think you will need a Stored Proc to achieve that during the file unload operation.