ステージングされたファイルのメタデータのクエリ

Snowflakeは、内部(つまり、Snowflake)ステージ、または外部(Amazon S3、Google Cloud Storage、またはMicrosoft Azure)ステージのファイルに自動的にメタデータを生成します。このメタデータは、次のような仮想列に「格納」され、以下の作業を実行することが可能です。

このトピックの内容:

メタデータ列

現在、次のメタデータ列をクエリしたり、テーブルにコピーしたりできます。

METADATA$FILENAME

現在の行が属するステージングされたデータファイルの名前。そのステージでのデータファイルへのパスが含まれます。

METADATA$FILE_ROW_NUMBER

コンテナのステージングされたデータファイルの各レコードの行番号。

クエリの限界

クエリの例

例1:CSV ファイルのメタデータ列のクエリ

次の例は、複数の CSV データファイル(同じファイル形式)をステージングし、ファイル内のメタデータ列と通常のデータ列をクエリする方法を示しています。

この例では、ファイルが次の名前を持ち、macOS またはLinux環境のルートディレクトリにあると想定しています。

  • /tmp/data1.csv には、2つのレコードが含まれます。

    a|b
    c|d
    
  • /tmp/data2.csv には、2つのレコードが含まれます。

    e|f
    g|h
    

ファイルをステージングおよびクエリするには、次を実行します。

-- Create a file format
CREATE OR REPLACE FILE FORMAT myformat
  TYPE = 'csv' FIELD_DELIMITER = '|';

-- Create an internal stage
CREATE OR REPLACE STAGE mystage1;

-- Stage a data file
PUT file:///tmp/data*.csv @mystage1;

-- Query the filename and row number metadata columns and the regular data columns in the staged file
-- Note that the table alias is provided to make the statement easier to read and is not required
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1 (file_format => myformat) t;

+-------------------+--------------------------+----+----+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1 | $2 |
|-------------------+--------------------------+----+----|
| data2.csv.gz      |                        1 | e  | f  |
| data2.csv.gz      |                        2 | g  | h  |
| data1.csv.gz      |                        1 | a  | b  |
| data1.csv.gz      |                        2 | c  | d  |
+-------------------+--------------------------+----+----+

SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1 t;

+-------------------+--------------------------+-----+------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1  | $2   |
|-------------------+--------------------------+-----+------|
| data2.csv.gz      |                        1 | e|f | NULL |
| data2.csv.gz      |                        2 | g|h | NULL |
| data1.csv.gz      |                        1 | a|b | NULL |
| data1.csv.gz      |                        2 | c|d | NULL |
+-------------------+--------------------------+-----+------+

注釈

この例では、ステージングされたファイルのフィールドを正しく解析するためにファイル形式が必要です。2番目のクエリでは、ファイル形式が省略されているため、 | フィールド区切り文字が無視され、 $1 および $2 の値が返されます。

ただし、ファイル形式がステージ定義に含まれている場合は、SELECT ステートメントから省略できます。詳細については、次の例をご参照ください。

例2:JSON ファイルのメタデータ列のクエリ

この例では、次のオブジェクトを含む JSON データファイルをステージングし、ファイル内のオブジェクトとともにメタデータ列をクエリします。

{"a": {"b": "x1","c": "y1"}},
{"a": {"b": "x2","c": "y2"}}

この例では、ファイルの名前が /tmp/data1.json で、macOS またはLinux環境のルートディレクトリにあると想定しています。

ファイルをステージングおよびクエリするには、次を実行します。

-- Create a file format
CREATE OR REPLACE FILE FORMAT my_json_format
  TYPE = 'json';

-- Create an internal stage
CREATE OR REPLACE STAGE mystage2
  FILE_FORMAT = my_json_format;

-- Stage a data file
PUT file:///tmp/data1.json @mystage2;

-- Query the filename and row number metadata columns and the regular data columns in the staged file
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, parse_json($1) FROM @mystage2/data1.json.gz;

+-------------------+--------------------------+----------------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | PARSE_JSON($1) |
|-------------------+--------------------------+----------------|
| data1.json.gz     |                        1 | {              |
|                   |                          |   "a": {       |
|                   |                          |     "b": "x1", |
|                   |                          |     "c": "y1"  |
|                   |                          |   }            |
|                   |                          | }              |
| data1.json.gz     |                        2 | {              |
|                   |                          |   "a": {       |
|                   |                          |     "b": "x2", |
|                   |                          |     "c": "y2"  |
|                   |                          |   }            |
|                   |                          | }              |
+-------------------+--------------------------+----------------+

例3:メタデータ列のテーブルへのロード

COPY INTO <テーブル> コマンドは、ステージングされたデータファイルからターゲットテーブルへのメタデータのコピーをサポートしています。COPY ステートメントでデータ変換構文(SELECT リスト)を使用します。COPY ステートメントを使用したデータ変換の詳細については、『ロード中のデータの変換 』をご参照ください。

次の例では、 例1:CSV ファイルのメタデータ列のクエリ からテーブルにメタデータ列と通常のデータ列をロードします。

CREATE OR REPLACE TABLE table1 (
  filename varchar,
  file_row_number varchar,
  col1 varchar,
  col2 varchar
);

COPY INTO table1(filename, file_row_number, col1, col2)
  FROM (SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2 FROM @mystage1/data1.csv.gz (file_format => myformat) t);

SELECT * FROM table1;

+--------------+-----------------+------+------+
| FILENAME     | FILE_ROW_NUMBER | COL1 | COL2 |
|--------------+-----------------+------+------|
| data1.csv.gz | 1               | a    | b    |
| data1.csv.gz | 2               | d    | e    |
+--------------+-----------------+------+------+