TO_FILE¶

根据文件位置或元数据构造 FILE 类型的值。

语法¶

使用下列之一：

TO_FILE( <stage_name>, <relative_path> )

TO_FILE( <file_url> )

TO_FILE( <metadata> )

实参¶

通过提供以下内容来指定文件：

Both stage_name and relative_path
file_url
metadata

一次只能使用其中一种方法。

stage_name

文件所在暂存区的名称，以 '@stage_name' 形式的字符串表示。

relative_path

暂存区上文件的路径，由 stage_name 以字符串形式指定。

file_url

字符串形式的有效暂存区或作用域文件 URL。

metadata

An 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:

STAGE 和 RELATIVE_PATH
STAGE_FILE_URL
SCOPED_FILE_URL

返回¶

代表暂存文件的 FILE。

使用说明¶

在以下情况下引发错误：

提供的 URL 无效。
The file is on a stage that the user lacks privileges to access.
提供的元数据不包含必填 FILE 字段。

示例¶

使用 TO_FILE 创建 FILE 对象¶

A simple use of the TO_FILE function with a stage name and relative path:

SELECT TO_FILE('@mystage', 'image.png');

Copy

结果：

+-----------------------------------------------------+
| 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'));

Copy

结果：

+--------------------------------------------------------------------------------------------------------------------+
| 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"   |
| }                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------+

Or use the FILE_URL from a file in the directory of your stage:

SELECT TO_FILE(FILE_URL) FROM DIRECTORY(@mystage) LIMIT 1;

Copy

+--------------------------------------------------------------------------------------------------------------------+
| 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.com/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                                                                                                                               |
| }                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------+|

Copy

这显示了根据包含所需元数据的对象构造 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"                                                                                                                                                                |
| }                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Copy

将 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';

Copy

将包含 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);

Copy

从 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';

Copy

将 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;

Copy

创建 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;

Copy

从表创建物化视图或动态表：

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;

Copy

将文件存储在表列的数组中：

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;

Copy

Examples of errors¶

这些示例展示了在使用 TO_FILE 时常见的错误，这些错误会导致函数报错。

The following example constructs a FILE from a metadata object but omits a required field:

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'));

Copy

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'));

Copy

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

Copy

最后一个示例使用了不正确的暂存区名称，特别是名称末尾带有斜杠。Snowflake 已在暂存区名称和相对路径之间自动添加斜杠，所以会产生两个斜杠，导致组合后的暂存区路径不会指定任何文件。

SELECT TO_FILE('@mystage/', 'image.png');

Copy

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 不能在 INSERT INTO TABLE <t> VALUES 子句中使用。请改用 INSERT INTO TABLE <t> SELECT。