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

Snowflakeは、標準 SQL を使用して、内部(つまり、Snowflake)ステージまたは 名前付き 外部(Amazon S3、Google Cloud Storage、またはMicrosoft Azure)ステージにあるデータファイルのクエリをサポートしています。これは、特にデータをロードする前またはアンロードした後に、ステージングされたファイルの内容を検査/表示するのに役立ちます。

さらに、ステージングされたファイルの メタデータ列 を参照することにより、ステージングされたデータのクエリはファイルに関する追加情報(ファイル名や行番号など)を返すことができます。

Snowflakeは、ステージングされたデータクエリのサポートを利用して、 のロード中に データの変換を有効にします。

注釈

この機能は主に、特にデータのロードおよび/または変換時に単純なクエリのみを実行するためのものであり、データをテーブルにロードしてテーブルでクエリを実行することを意図したもの ありません

このトピックの内容:

クエリの構文とパラメーター

次の構文で SELECT ステートメントを使用して、ステージングされたデータファイルをクエリします。

SELECT [<alias>.]$<file_col_num>[:<element>] [ , [<alias>.]$<file_col_num>[:<element>] , ...  ]
  FROM { <internal_location> | <external_location> }
  [ ( FILE_FORMAT => '<namespace>.<named_file_format>', PATTERN => '<regex_pattern>' ) ]
  [ <alias> ]
Copy

ロード中にデータを変換するための構文については、 COPY INTO <テーブル> をご参照ください。

重要

外部ステージに対して返されるオブジェクトのリストには、1つ以上の「ディレクトリBLOB」が含まれる場合があります。基本的に、スラッシュ文字(/)で終わるパスです。例:

LIST @my_gcs_stage;

+---------------------------------------+------+----------------------------------+-------------------------------+
| name                                  | size | md5                              | last_modified                 |
|---------------------------------------+------+----------------------------------+-------------------------------|
| my_gcs_stage/load/                    |  12  | 12348f18bcb35e7b6b628ca12345678c | Mon, 11 Sep 2019 16:57:43 GMT |
| my_gcs_stage/load/data_0_0_0.csv.gz   |  147 | 9765daba007a643bdff4eae10d43218y | Mon, 11 Sep 2019 18:13:07 GMT |
+---------------------------------------+------+----------------------------------+-------------------------------+
Copy

これらのBLOBは、Googleが提供する他のツールを使用するのではなく、Google Cloud Platform Consoleでディレクトリが作成されるときにリストされます。

オブジェクトリストにディレクトリBLOBが含まれていると、ステージを参照するSELECT ステートメントが失敗する可能性があります。エラーを回避するために、ステージのファイルリストにディレクトリBLOBが含まれる場合、ファイルパターンマッチングを使用して、含めるファイル(つまり PATTERN 句)を識別することをお勧めします。

必須パラメーター

[alias.]$file_col_num[:element] [ , [alias.]$file_col_num[:element] , ...  ]

内部または外部の場所でステージングされたデータファイル内のフィールド/列の明示的なセットを指定します。ここで、

alias

FROM 句で定義されているオプションの「テーブル」エイリアスを指定します(存在する場合)。

file_col_num

ロードするデータを含む(ファイル内の)フィールド/列の位置番号を指定します(最初のフィールドには 1 、2番目のフィールドには 2 など)

element

繰り返し値のパスと要素名を指定します(半構造化データファイル にのみ適用)。

internal_location または external_location

データファイルがステージングされる場所を指定します。

  • internal_location は、データを含むファイルがステージングされるSnowflakeの場所の URI 指定子です。

    @[namespace.]internal_stage_name[/path]

    ファイルは指定された名前付き内部ステージにあります。

    @[namespace.]%table_name[/path]

    ファイルは指定されたテーブルのステージにあります。

    @~[/path]

    ファイルは現在のユーザーのステージにあります。

  • external_location は、データを含むファイルがステージングされる、指定された外部ステージまたは外部ロケーション(つまり、Amazon S3、Google Cloud Storage、またはMicrosoft Azure)の URI 指定子です。

    @[namespace.]external_stage_name[/path]

    ファイルは指定された名前付き外部ステージにあります。

条件:

  • namespace は、内部または外部ステージが存在するデータベースまたはスキーマ、あるいはその両方です。ユーザーセッション内でデータベースとスキーマが現在使用されている場合は、 オプション になります。それ以外の場合は必須です。

  • オプションの path パラメーターは、クエリされるファイルのセットをフォルダープレフィックス下にあるファイルに制限します。 path が指定されていても、パスに明示的に名前が付けられているファイルがない場合、パス内のすべてのデータファイルがクエリされます。

注釈

  • 外部ストレージの場所の URI 文字列(つまり、Amazon S3、Google Cloud Storage、またはMicrosoft Azure)は、一重引用符で囲む 必要があります 。ただし、任意の URI 文字列を一重引用符で囲むことができます。これにより、場所やファイル名にスペースを含む特殊文字を使用できます。例:

    内部:

    '@~/path 1/file 1.csv'

    '@%my table/path 1/file 1.csv'

    '@my stage/path 1/file 1.csv'

  • 「パス」は名前のリテラルプレフィックスであるため、 /.//../ などの相対パス修飾子は文字通りに解釈されます。例:

    S3:

    COPY INTO mytable FROM @mystage/./../a.csv

    これらの COPY ステートメントでは、システムは文字通り ./../a.csv という名前のファイルをストレージの場所で探します。

オプションのパラメーター

( FILE_FORMAT => 'namespace.named_file_format' )

クエリするステージングされたデータファイルの形式を記述する名前付きファイル形式を指定します。

次の条件の いずれか が当てはまる場合、このパラメーターは任意です。

  • ファイルは、デフォルトの区切り文字: , (フィールド区切り文字として)および改行文字(レコード区切り文字として)を使用して、デフォルトのファイル形式(CSV)でフォーマットされます。

  • ファイルは内部または外部のステージにあり、ステージ定義でファイル形式を説明します。

ユーザーセッションに対する現在の名前空間のファイル形式を参照する場合、形式識別子を囲む単一引用符を省略できます。

それ以外の場合、このパラメーターは 必須 です。詳細については、(このトピックの) ファイル形式 をご参照ください。

namespace は、オプションでテーブルのデータベースまたはスキーマ、あるいはその両方を database_name.schema_name または schema_name の形式で指定します。ユーザーセッション内でデータベースとスキーマが現在使用されている場合は オプション です。それ以外の場合は必須です。

識別子にスペース、特殊文字、または大文字と小文字が混在する場合、文字列全体を二重引用符で囲む必要があります。二重引用符で囲まれた識別子も大文字と小文字が区別されます。

PATTERN => 'regex_pattern'

一致する外部ステージ上のファイル名やパスを指定する、一重引用符で囲まれた正規表現パターン文字列。

Tip

最高のパフォーマンスを得るには、多数のファイルをフィルターするパターンの適用を避けるようにします。

alias

ファイルがステージされる内部/外部の場所の「テーブル」エイリアスを指定します。

ファイル形式

ステージングされたデータファイルを解析するには、そのファイル形式を説明する必要があります。デフォルトのファイル形式は、文字区切りの UTF-8テキストです(つまり、CSV)、フィールド区切り文字としてカンマ文字(,)、レコード区切り文字として改行文字を使用します。ソースデータが別の形式(JSON、Avroなど)の場合は、対応するファイル形式のタイプ(およびオプション)を指定する必要があります。

ファイル形式のオプションを明示的に指定するには、次のいずれかの方法で設定します。

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

名前付きファイル形式またはステージオブジェクトに指定されたファイル形式のオプションとして。名前付きファイル形式/ステージオブジェクトを、 SELECT ステートメントで参照できるようになります。

ステージングされたデータファイルからの列のロード:
  • COPY INTO <テーブル> で直接指定されたファイル形式のオプションとして。

  • 名前付きファイル形式またはステージオブジェクトに指定されたファイル形式のオプションとして。名前付きファイル形式/ステージオブジェクトを、 COPY INTO <テーブル> ステートメントで参照できるようになります。

クエリの例

例1: CSV ファイルの列のクエリ

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

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

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

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

    e|f
    g|h
    
    Copy

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

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

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

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

-- Query the filename and row number metadata columns and the regular data columns in the staged file.
-- Optionally apply pattern matching to the set of files in the stage and optional path.
-- Note that the table alias is provided to make the statement easier to read and is not required.
SELECT t.$1, t.$2 FROM @mystage1 (file_format => 'myformat', pattern=>'.*data.*[.]csv.gz') t;

+----+----+
| $1 | $2 |
|----+----|
| a  | b  |
| c  | d  |
| e  | f  |
| g  | h  |
+----+----+

SELECT t.$1, t.$2 FROM @mystage1 t;

+-----+------+
| $1  | $2   |
|-----+------|
| a|b | NULL |
| c|d | NULL |
| e|f | NULL |
| g|h | NULL |
+-----+------+
Copy

注釈

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

ただし、ファイル形式がステージ定義に含まれている場合は、 SELECT ステートメントから省略できます。 例3: JSON ファイルの要素のクエリ をご参照ください。

例2:ステージングされたデータファイルのクエリ時の関数呼び出し

例1: CSV ファイルの列のクエリ でステージングされた、データファイルの各列にある最初の文字の ASCII コードを取得します。

SELECT ascii(t.$1), ascii(t.$2) FROM @mystage1 (file_format => myformat) t;

+-------------+-------------+
| ASCII(T.$1) | ASCII(T.$2) |
|-------------+-------------|
|          97 |          98 |
|          99 |         100 |
|         101 |         102 |
|         103 |         104 |
+-------------+-------------+
Copy

注釈

ファイル形式がステージ定義に含まれている場合は、 SELECT ステートメントから省略できます。 例3: JSON ファイルの要素のクエリ をご参照ください。

例3: JSON ファイルの要素のクエリ

この例では、次のオブジェクトを含む JSON データファイルをステージングし、ファイル内のオブジェクト内の個々の要素をクエリします。

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

この例では、ファイルの名前が /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 the data file
PUT file:///tmp/data1.json @mystage2;

-- Query the repeating a.b element in the staged file
SELECT parse_json($1):a.b FROM @mystage2/data1.json.gz;

+--------------------+
| PARSE_JSON($1):A.B |
|--------------------|
| "x1"               |
| "x2"               |
+--------------------+
Copy