snowflake-cloud-data-platformsnowflake-schema

Can we add additional columns to copyinto command while schema evolution is enabled


Is there any restrictions to add additional columns to copyinto command while schema evolution is enabled.

This is in addition to INCLUDE_METADATA columns as provided by snowflake i.e.,fields like METADATA$START_SCAN_TIME,METADATA$START_FILE_NAME Etc.,

I am trying to add more columns like SYSTEM_SOURCE, SYSTEM_MODULE with default values.

My file format is parquet.

COPY INTO DATABASE.SCHEMA.TABLE2
FROM DATABASE.SCHEMA.TABLE1
FILE_FORMAT=DATABASE.SCHEMA.PARQUET_FILE_FORMAT
ON_ERROR=CONTINUE
MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE
INCLUDE_METADATA(_METADATA_FILENAME=META $DATA$FILENAME,_METADATA_FILE_LAST_MODIFIED=_METADATA$FILE_LAST_MODIFIED,RECORD_SOURCE='BUSINESS_UNIT')

Solution

  • INCLUDE_METADATA allows only for predefined values in form of METADATA$field_name.

    An arbitrary value like RECORD_SOURCE='BUSINESS_UNIT' is not supported.


    COPY INTO table:

    INCLUDE_METADATA = ( column_name = METADATA$field [ , column_name = METADATA$field ... ] )

    Definition A user-defined mapping between a target table’s existing columns to its METADATA$ columns. This copy option can only be used with the MATCH_BY_COLUMN_NAME copy option. The valid input for METADATA$field includes the following:

    • METADATA$FILENAME
    • METADATA$FILE_ROW_NUMBER
    • METADATA$FILE_CONTENT_KEY
    • METADATA$FILE_LAST_MODIFIED
    • METADATA$START_SCAN_TIME