- Categories:
File functions (AI Functions)
TO_
Constructs a value of type FILE from a file location or from metadata.
Syntax¶
Use one of the following:
TO_FILE( <stage_name>, <relative_path> )
TO_FILE( <file_url> )
TO_FILE( <metadata> )Arguments¶
Specify the file by providing:
- Both
stage_nameandrelative_path file_urlmetadata
Only one of these methods can be used at a time.
stage_nameThe name of the stage where the file is located, as a string, in the form
'@stage_name'.relative_pathThe path to the file on the stage specified by
stage_nameas a string.file_urlA valid stage or scoped file URL as a string.
metadataAn OBJECT containing the required FILE attributes. A FILE must have CONTENT_TYPE, SIZE, ETAG, and LAST_MODIFIED fields. It must also specify the file’s location in one of the following ways:
- Both STAGE and RELATIVE_PATH
- STAGE_FILE_URL
- SCOPED_FILE_URL
返回
A FILE that represents the staged file.
使用说明
在以下情况下引发错误：
- 提供的 URL 无效。
- 文件位于用户无权访问的暂存区。
- 提供的元数据不包含必填 FILE 字段。
示例
使用 TO_
简单使用具有暂存区名称和相对路径的 TO_FILE 函数：
SELECT TO_FILE('@mystage', 'image.png');结果：
+-----------------------------------------------------+
| TO_FILE('@MYSTAGE', 'IMAGE.PNG') |
%-----------------------------------------------------%
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "2859efde6e26491810f619668280a2ce", |
| "LAST_MODIFIED": "Thu, 18 Sep 2025 09:02:00 GMT", |
| "RELATIVE_PATH": "image.png", |
| "SIZE": 23698, |
| "STAGE": "@MYDB.MYSCHEMA.MYSTAGE" |
| } |
+-----------------------------------------------------+具有暂存文件 URL 的 TO_FILE 函数的简单用法：
SELECT TO_FILE(BUILD_STAGE_FILE_URL('@mystage', 'image.png'));结果：
+--------------------------------------------------------------------------------------------------------------------+
| TO_FILE(BUILD_STAGE_FILE_URL('@MYSTAGE', 'IMAGE.PNG')) |
%--------------------------------------------------------------------------------------------------------------------%
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "..." |
| "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.cn/api/files/MYDB/MYSCHEMA/MYSTAGE/image.png" |
| } |
+--------------------------------------------------------------------------------------------------------------------+或者使用暂存区目录内文件中的 FILE_URL：
SELECT TO_FILE(FILE_URL) FROM DIRECTORY(@mystage) LIMIT 1;+--------------------------------------------------------------------------------------------------------------------+
| TO_FILE(FILE_URL) |
%--------------------------------------------------------------------------------------------------------------------%
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "..." |
| "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.cn/api/files/MYDB/MYSCHEMA/MYSTAGE/image.png" |
| } |
+--------------------------------------------------------------------------------------------------------------------+此示例直接将 TO_FILE 函数与作用域文件 URL 一起使用：
SELECT TO_FILE(`https://snowflake.account.snowflakecomputing.cn/api/files/01ba4df2-0100-0001-0000-00040002e2b6/299017/Y6JShH6KjV`);
+------------------------------------------------------------------------------------------------------------------------------------------------+
| TO_FILE(https://snowflake.account.snowflakecomputing.cn/api/files/01ba4df2-0100-0001-0000-00040002e2b6/299017/Y6JShH6KjV |
%------------------------------------------------------------------------------------------------------------------------------------------------%
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "..." |
| "LAST_MODIFIED": "Wed, 11 Dec 2024 20:24:00 GMT", |
| "SCOPED_FILE_URL": "https://snowflake.account.snowflakecomputing.cn/api/files/01ba4df2-0100-0001-0000-00040002e2b6/299017/Y6JShH6KjV", |
| "SIZE": 105859 |
| } |
+-----------------------------------------------------------------------------------------------------------------------------------------------+|这显示了根据包含所需元数据的对象构造 FILE 的示例：
SELECT TO_FILE(OBJECT_CONSTRUCT('STAGE', 'MYSTAGE', 'RELATIVE_PATH', 'image.png', 'ETAG', '<ETAG value>',
'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', '<ETAG value>', 'LAST_MODIFIED', 'WED, 11 DEC 2024 20:24:00 GMT', 'SIZE', 105859, 'CONTENT_TYPE', 'IMAGE/PNG')) |
%----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------%
| { |
| "CONTENT_TYPE": "image/png", |
| "ETAG": "<ETAG value>>" |
| "LAST_MODIFIED": "Wed, 11 Dec 2024 20:24:00 GMT", |
| "RELATIVE_PATH": "image.png", |
| "SIZE": 105859, |
| "STAGE": "@MYDB.MYSCHEMA.MYSTAGE" |
| } |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+将 FILE 添加到表¶
以下示例演示如何创建 FILE 并将其存储在表中，然后使用该列执行各种操作，包括从 Parquet 保存和加载、SnowPipe、数据集、物化视图、动态表，以及使用 Time Travel 克隆。
创建一个包含 FILE 列的表：
CREATE OR REPLACE TABLE sample_table (a INT, f FILE NOT NULL);
DESCRIBE TABLE sample_table;
INSERT INTO sample_table SELECT 1, TO_FILE('@mystage', 'image.png');
INSERT INTO sample_table SELECT 1, TO_FILE('@mystage', relative_path) FROM DIRECTORY('@mystage');
SELECT * FROM sample_table WHERE fl_get_file_type(f) = 'image';将包含 FILE 列的表写入暂存区作为 Parquet 文件并重新加载：
-- Write to stage as Parquet
CREATE OR REPLACE STAGE test_stage_parquet;
CREATE OR REPLACE FILE FORMAT parquet_format
TYPE = 'PARQUET'
USE_LOGICAL_TYPE = TRUE;
COPY INTO @test_stage_parquet/file_copy.parquet FROM sample_table
FILE_FORMAT = (FORMAT_NAME = parquet_format) HEADER = TRUE ->> SELECT "rows_unloaded" FROM $1;
ALTER STAGE test_stage_parquet SET DIRECTORY = (ENABLE=TRUE);
ALTER STAGE test_stage_parquet REFRESH;
-- Read Parquet files back from stage
SELECT * FROM @TEST_STAGE_PARQUET/file_copy.parquet_0_0_0.snappy.parquet(FILE_FORMAT => parquet_format);
SELECT * FROM @TEST_STAGE_PARQUET (PATTERN => '.*.parquet', FILE_FORMAT => parquet_format);从 Parquet 文件创建数据集：
CREATE OR REPLACE DATASET mydataset;
ALTER DATASET mydataset ADD VERSION 'v1' FROM
(SELECT * FROM @TEST_STAGE_PARQUET/file_copy.parquet_0_0_0.snappy.parquet(
FILE_FORMAT => my_parquet_format))
COMMENT = 'test dataset';将 Parquet 文件复制到表中：
CREATE OR REPLACE TABLE t1_copy_parquet (a INT, f OBJECT NOT NULL);
COPY INTO t1_copy_parquet
FROM @test_stage_parquet
FILE_FORMAT = (FORMAT_NAME = parquet_format)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
SELECT FL_GET_STAGE(f), FL_GET_RELATIVE_PATH(f) FROM t1_copy_parquet;创建 Snowpipe：
CREATE OR REPLACE TABLE t1_copy_parquet_snowpipe (f OBJECT NOT NULL);
CREATE OR REPLACE PIPE test_pipe AS
COPY INTO t1_copy_parquet_snowpipe
FROM @test_stage_parquet
FILE_FORMAT = (FORMAT_NAME = my_parquet_format)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
ALTER PIPE TEST_PIPE REFRESH;从表创建物化视图或动态表：
CREATE OR REPLACE MATERIALIZED VIEW MV AS SELECT * FROM SAMPLE_TABLE;
CREATE OR REPLACE DYNAMIC TABLE sample_dynamic_table
WAREHOUSE = my_warehouse
TARGET_LAG = '60 minutes'
AS SELECT f FROM sample_table;将文件存储在表列的数组中：
CREATE OR REPLACE TABLE files_array_table(files ARRAY);
INSERT INTO files_array_table SELECT ARRAY_CONSTRUCT(TO_FILE('@mystage', 'image.png'));
CREATE OR REPLACE TABLE files_array_table_copy(files ARRAY);
INSERT INTO files_array_table_copy SELECT files[0] FROM files_array_table;错误示例
这些示例展示了在使用 TO_FILE 时常见的错误，这些错误会导致函数报错。
以下示例从元数据对象构造 FILE，但省略了必填字段：
SELECT TO_FILE(OBJECT_CONSTRUCT('RELATIVE_PATH', 'image.png', 'ETAG', '<ETAG value>',
'LAST_MODIFIED', 'Wed, 11 Dec 2024 20:24:00 GMT', 'SIZE', 105859, 'CONTENT_TYPE', 'image/png'));Invalid file metadata. Must provide (STAGE and RELATIVE_PATH), SCOPED_FILE_URL, or STAGE_FILE_URL.以下示例类似，但省略了必填的 ETAG 字段。
SELECT TO_FILE(OBJECT_CONSTRUCT('STAGE', 'MYSTAGE', 'RELATIVE_PATH', 'image.png',
'LAST_MODIFIED', 'Wed, 11 Dec 2024 20:24:00 GMT', 'SIZE', 105859, 'CONTENT_TYPE', 'image/png'));Invalid file metadata. Missing required fields: ETAG.以下示例展示了尝试对 FILE 列执行 GROUP BY、ORDER BY 和 CLUSTER BY 的操作，但不受支持，因为 FILE 值无法比较。
SELECT f, count(*) FROM sample_table GROUP BY f;
-- Expressions of type FILE cannot be used as GROUP BY keys
SELECT * FROM sample_table ORDER by f;
-- Expressions of type FILE cannot be used as ORDER BY keys
CREATE OR REPLACE TABLE cluster_to_file (a int, url string) CLUSTER BY (to_file(url));
-- Unsupported type 'FILE' for clustering keys最后一个示例使用了不正确的暂存区名称，特别是名称末尾带有斜杠。Snowflake 已在暂存区名称和相对路径之间自动添加斜杠，所以会产生两个斜杠，导致组合后的暂存区路径不会指定任何文件。
SELECT TO_FILE('@mystage/', 'image.png');Remote file '@mystage//image.png' was not found. There are several potential causes.
The file might not exist. The required credentials may be missing or invalid. If you
are running a copy command, please make sure files are not deleted when they are
being loaded or files are not being loaded into two different tables concurrently
with auto purge option.已知限制
- TO_FILE cannot be used in INSERT INTO TABLE <t> VALUES clause. Use INSERT INTO TABLE <t> SELECT instead.