Snowflake にて内部 STAGE と外部 STAGE にて LIST した際の NAME 列の値が異なる仕様の調査結果

概要

Snowflake にて内部 STAGE と外部 STAGE にて LIST した際の NAME 列の値が異なる仕様の調査結果を共有します。内部 STAGE と外部 STAGE にて LIST でした際の NAME 列に下記のようにデータが表示され、name がURL形式で返るようです。

事前準備

ストレージ統合の作成

データベースを作成

CREATE OR REPLACE DATABASE stage_test_01;
CREATE OR REPLACE SCHEMA stage_test_01.SCHEMA_01;

USE DATABASE stage_test_01;
USE SCHEMA SCHEMA_01;

STAGE の作成

CREATE STAGE INTERNAL_STAGE_01;

CREATE STAGE AZURE_STAGE_01
    URL = 'azure://snowflakewithstorage.blob.core.windows.net/snowflake/'
    STORAGE_INTEGRATION = AZURE_STORAGE_INT;

-- 1. 内部ステージに3ファイルを作成
COPY INTO @INTERNAL_STAGE_01/stage_list_name_check/batch_001/orders.csv
FROM (SELECT 1 AS ORDER_ID, 'Alice' AS CUSTOMER_NAME)
FILE_FORMAT = (TYPE = CSV COMPRESSION = NONE)
SINGLE = TRUE
OVERWRITE = TRUE;

COPY INTO @INTERNAL_STAGE_01/stage_list_name_check/batch_002/nested/orders.csv
FROM (SELECT 2 AS ORDER_ID, 'Bob' AS CUSTOMER_NAME)
FILE_FORMAT = (TYPE = CSV COMPRESSION = NONE)
SINGLE = TRUE
OVERWRITE = TRUE;

COPY INTO @INTERNAL_STAGE_01/stage_list_name_check/root.csv
FROM (SELECT 3 AS ORDER_ID, 'Carol' AS CUSTOMER_NAME)
FILE_FORMAT = (TYPE = CSV COMPRESSION = NONE)
SINGLE = TRUE
OVERWRITE = TRUE;

-- 2. 同じファイル構成を外部ステージにコピー
COPY FILES
INTO @AZURE_STAGE_01/stage_list_name_check/
FROM @INTERNAL_STAGE_01/stage_list_name_check/
DETAILED_OUTPUT = TRUE;

データ確認

内部 STAGE

-- 内部ステージ
LIST @INTERNAL_STAGE_01/stage_list_name_check/;

SELECT "name"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "name";

-- 内部ステージ
LIST @INTERNAL_STAGE_01/stage_list_name_check/;

name	size	md5	last_modified
internal_stage_01/stage_list_name_check/batch_001/orders.csv	16	78ae698ef2e2db9415a35e900e1016ed	Fri, 9 Oct 2026 11:26:18 GMT
internal_stage_01/stage_list_name_check/batch_002/nested/orders.csv	16	a08536df9300cb04255fd12cc68d4ef2	Fri, 9 Oct 2026 11:26:19 GMT
internal_stage_01/stage_list_name_check/root.csv	16	1bd9eef7cff85ac04f4dc0be200e7324	Fri, 9 Oct 2026 11:26:19 GMT

外部 STAGE

-- 外部ステージ
LIST @AZURE_STAGE_01/stage_list_name_check/;

SELECT "name"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "name";

LIST @AZURE_STAGE_01/stage_list_name_check/

name	size	md5	last_modified
azure://snowflakewithstorage.blob.core.windows.net/snowflake/stage_list_name_check/batch_001	0		Fri, 9 Oct 2026 11:29:00 GMT
azure://snowflakewithstorage.blob.core.windows.net/snowflake/stage_list_name_check/batch_001/orders.csv	8	17eddd5f80201ce58aa2023ac9cc5205	Fri, 9 Oct 2026 11:29:00 GMT
azure://snowflakewithstorage.blob.core.windows.net/snowflake/stage_list_name_check/batch_002	0		Fri, 9 Oct 2026 11:29:00 GMT
azure://snowflakewithstorage.blob.core.windows.net/snowflake/stage_list_name_check/batch_002/nested	0		Fri, 9 Oct 2026 11:29:00 GMT
azure://snowflakewithstorage.blob.core.windows.net/snowflake/stage_list_name_check/batch_002/nested/orders.csv	6	7f3848bf6d0f8fa1e3417cbb2d7b60ec	Fri, 9 Oct 2026 11:29:00 GMT
azure://snowflakewithstorage.blob.core.windows.net/snowflake/stage_list_name_check/root.csv	8	22578e8400c251081d9d8bdbc3665684	Fri, 9 Oct 2026 11:29:00 GMT