TO_FILE¶
Parses an input and returns a value of type FILE.
Syntax¶
Use one of the following:
TO_FILE( <varchar_expression> )
TO_FILE( <variant_expression> )
Arguments¶
varchar_expressionThe argument must be a valid stage or scoped file URL.
variant_expressionThe argument must be an OBJECT with all the required FILE metadata.
Returns¶
The function returns a value of type FILE.
Usage notes¶
Issues an error if the input STRING is not a valid URL.
Issues an error if the input STRING is a URL on a stage that the user has no permissions to access.
Issues an error if the input OBJECT doesn’t contain the required FILE fields.
Examples¶
This shows a simple use of the TO_FILE function with VARCHAR data representing a stage file URL:
select to_file(build_stage_file_url('@mystage', 'image.png'));
+--------------------------------------------------------------------------------------------------------------------+
| TO_FILE(BUILD_STAGE_FILE_URL('@MYSTAGE', 'IMAGE.PNG')) |
|--------------------------------------------------------------------------------------------------------------------|
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "85522e3bd0e4bbb930237f886db3bcee", | # pragma: allowlist secret
| "LAST_MODIFIED": "Wed, 11 Dec 2024 20:24:00 GMT", |
| "RELATIVE_PATH": "image.png", |
| "SIZE": 105859, |
| "STAGE": "@MYDB.MYSCHEMA.MYSTAGE", |
| "STAGE_FILE_URL": "https://snowflake.account.snowflakecomputing.com/api/files/MYDB/MYSCHEMA/MYSTAGE/image.png" |
| } |
+--------------------------------------------------------------------------------------------------------------------+
Or use the FILE_URL from directory table of your stage:
select to_file(file_url) from directory(@mystage) limit 1;
+--------------------------------------------------------------------------------------------------------------------+
| TO_FILE(FILE_URL) |
|--------------------------------------------------------------------------------------------------------------------|
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "85522e3bd0e4bbb930237f886db3bcee", | # pragma: allowlist secret
| "LAST_MODIFIED": "Wed, 11 Dec 2024 20:24:00 GMT", |
| "RELATIVE_PATH": "image.png", |
| "SIZE": 105859, |
| "STAGE": "@MYDB.MYSCHEMA.MYSTAGE", |
| "STAGE_FILE_URL": "https://snowflake.account.snowflakecomputing.com/api/files/MYDB/MYSCHEMA/MYSTAGE/image.png" |
| } |
+--------------------------------------------------------------------------------------------------------------------+
This shows a simple use of the TO_FILE function with VARCHAR data representing a scoped file URL:
select to_file(build_scoped_file_url('@mystage', 'image.png'));
+------------------------------------------------------------------------------------------------------------------------------------------------+
| TO_FILE(BUILD_SCOPED_FILE_URL('@MYSTAGE', 'IMAGE.PNG')) |
|------------------------------------------------------------------------------------------------------------------------------------------------|
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "85522e3bd0e4bbb930237f886db3bcee", | # pragma: allowlist secret
| "LAST_MODIFIED": "Wed, 11 Dec 2024 20:24:00 GMT", |
| "SCOPED_FILE_URL": "https://snowflake.account.snowflakecomputing.com/api/files/01ba4df2-0100-0001-0000-00040002e2b6/299017/Y6JShH6KjV", |
| "SIZE": 105859 |
| } |
+-----------------------------------------------------------------------------------------------------------------------------------------------+|
This shows a simple use of the TO_FILE function with a VARIANT data representing FILE metadata:
select to_file(object_construct('STAGE', 'MYSTAGE', 'RELATIVE_PATH', 'image.png', 'ETAG', '85522e3bd0e4bbb930237f886db3bcee', 'LAST_MODIFIED', 'Wed, 11 Dec 2024 20:24:00 GMT', 'SIZE', 105859, 'CONTENT_TYPE', 'image/png'));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TO_FILE(OBJECT_CONSTRUCT('STAGE', 'MYSTAGE', 'RELATIVE_PATH', 'IMAGE.PNG', 'ETAG', '85522E3BD0E4BBB930237F886DB3BCEE', 'LAST_MODIFIED', 'WED, 11 DEC 2024 20:24:00 GMT', 'SIZE', 105859, 'CONTENT_TYPE', 'IMAGE/PGN')) | # pragma: allowlist secret
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "85522e3bd0e4bbb930237f886db3bcee", | # pragma: allowlist secret
| "LAST_MODIFIED": "Wed, 11 Dec 2024 20:24:00 GMT", |
| "RELATIVE_PATH": "image.png", |
| "SIZE": 105859, |
| "STAGE": "@MYDB.MYSCHEMA.MYSTAGE" |
| } |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Known limitations of TO_FILE()¶
TO_FILE() cannot be used in INSERT INTO TABLE <t> VALUES clause. Please use INSERT INTO TABLE <t> SELECT instead
TO_FILE() cannot work with stages with double quoted stage names e.g. @”MYsTAgE”