Snowflake METADATA$FILENAME key word not working in snowsql (Unix cmd prompt) but working in snowflake UI

0
votes

Snowflake code

Working in UI

copy into DWR_NA.GTR_STG.BASE_REF_CSV_STG (FILE_NM,ROW_NM,CSV_CONTENT,LOAD_STATUS) from (select METADATA$FILENAME as FILE_NM,METADATA$FILE_ROW_NUMBER as ROW_NM,$1as CSV_CONTENT, 'P' as LOAD_STATUS FROM @DWR_NA.GTR_STG.INT_REF_CSV_UNIX_STG)

Same command not working in Unix snowsql

snowsql -c SF_DWR_connection -d $SFDB -q "copy into DWR_NA.GTR_STG.BASE_REF_CSV_STG (FILE_NM,ROW_NM,CSV_CONTENT,LOAD_STATUS) from (select METADATA$FILENAME as FILE_NM,METADATA$FILE_ROW_NUMBER as ROW_NM,$1 as CSV_CONTENT, 'P' as LOAD_STATUS FROM @DWR_NA.GTR_STG.INT_REF_CSV_UNIX_STG);"

Error Message 000904 (42000): SQL compilation error: error line 1 at position 104 invalid identifier 'METADATA'

I tried the above sql with Internal and External stage, but is not working.

Please suggestion.

1
snowflake-cloud-data-platformsnowsql
As a workaround, can you try IDENTIFIER('METADATA$FILENAME') in place of METADATA$FILENAME – waldente
As it's solved, can you mark the correct answer? – Gokhan Atil

1 Answers

0
votes

It's just about escaping $ characters in double quoted strings. You need write them as "\$" So please try this:

snowsql -c SF_DWR_connection -d $SFDB -q "copy into
 DWR_NA.GTR_STG.BASE_REF_CSV_STG 
(FILE_NM,ROW_NM, CSV_CONTENT,LOAD_STATUS) from 
(select METADATA\$FILENAME as FILE_NM, 
METADATA\$FILE_ROW_NUMBER as ROW_NM, \$1 as CSV_CONTENT, 
'P' as LOAD_STATUS FROM @DWR_NA.GTR_STG.INT_REF_CSV_UNIX_STG);"