データのアンロードに関する考慮事項

このトピックでは、ベストプラクティス、一般的なガイドライン、およびテーブルからデータをアンロードするための重要な考慮事項について説明します。 COPY INTO <場所> コマンドを使用して、Snowflakeテーブルからファイルへのデータの段階的なエクスポートを簡素化することを目的としています。

このトピックの内容:

空の文字列と NULL 値

空の文字列は、長さがゼロまたは文字のない文字列ですが、 NULL 値はデータがないことを表します。CSV ファイルでは、 NULL 値は通常、2つの連続する区切り文字(例: ,,)で表され、フィールドにデータが含まれていないことを示します。ただし、文字列値を使用して NULL (例: null)または任意の一意の文字列を示すことができます。空の文字列は通常、引用符で囲まれた空の文字列(例: ,'',)で表され、文字列にゼロ文字が含まれていることを示します。

次のファイル形式オプションを使用すると、データをアンロードまたはロードするときに空の文字列と NULL 値を区別できます。これらのファイル形式の詳細については、 CREATE FILE FORMAT をご参照ください。

FIELD_OPTIONALLY_ENCLOSED_BY = '文字' | NONE

このオプションを使用して、一重引用符(')、二重引用符(")、または NONEで、指定された文字内でストリングを囲みます。

データをアンロードする際は、文字列値を引用符で囲む必要はありません。COPY INTO ロケーションコマンドは、 EMPTY_FIELD_AS_NULL オプションを FALSEに設定して、引用符で囲まずに空の文字列値をアンロードできます。EMPTY_FIELD_AS_NULL オプションが TRUE (これは禁止されている)の場合、空の文字列と NULL 値は出力ファイルで区別できません。

フィールドにこの文字が含まれる場合、同じ文字を使用してエスケープします。たとえば、値が二重引用符で、フィールドに文字列 "A" が含まれる場合、次のように二重引用符をエスケープします。 ""A""

デフォルト: NONE

EMPTY_FIELD_AS_NULL = TRUE | FALSE
  • テーブルから空の文字列データをアンロードする場合は、次のオプションのいずれかを選択します。

    • 推奨 :出力 CSV ファイルの NULLs と空の文字列を区別するために、 FIELD_OPTIONALLY_ENCLOSED_BY オプションを設定して文字列を引用符で囲みます。

    • FIELD_OPTIONALLY_ENCLOSED_BY オプションを NONE (デフォルト)に設定して文字列フィールドを囲まないまま、 EMPTY_FIELD_AS_NULL 値を FALSE に設定して空の文字列を空のフィールドとしてアンロードします。

      重要

      このオプションを選択する場合は、 NULL_IF オプションを使用して NULL データの置換文字列を指定し、出力ファイルの NULL 値と空の文字列を区別します。後で出力ファイルからデータをロードすることを選択した場合は、データファイル内の NULL 値を識別するために同じ NULL_IF 値を指定します。

  • データをテーブルにロードする場合は、このオプションを使用して、入力ファイルの空のフィールドに SQL NULL を挿入するかどうかを指定します。FALSE に設定すると、Snowflakeは空のフィールドを対応する列タイプにキャストしようとします。空の文字列が、データ型 STRINGの列に挿入されます。他の列タイプの場合、 COPY コマンドはエラーを生成します。

デフォルト: TRUE

NULL_IF = ( '文字列1' [ , '文字列2' ... ] )

テーブルからデータをアンロードするとき:Snowflakeは、 SQL NULL 値をリストの最初の値に変換します。NULL として解釈する値を注意して指定してください。たとえば、別のシステムによって読み取られるファイルにデータをアンロードする場合は、そのシステムによって NULL として解釈される値を指定してください。

デフォルト: \\N (つまり、 NULL、 ESCAPE_UNENCLOSED_FIELD 値が \\ (デフォルト)であると想定)

例:引用符を含むデータのアンロードとロード

次の例では、データのセットが null_empty1 テーブルからユーザーのステージにアンロードされます。次に、出力データファイルを使用して、データを null_tempty2 テーブルにロードします。

-- Source table (:code:`null_empty1`) contents
+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

-- Create a file format that describes the data and the guidelines for processing it
create or replace file format my_csv_format
  field_optionally_enclosed_by='0x27' null_if=('null');

-- Unload table data into a stage
copy into @mystage
  from null_empty1
  file_format = (format_name = 'my_csv_format');

-- Output the data file contents
1,'null','NULL value'
2,'','Empty string'

-- Load data from the staged file into the target table (:code:`null_empty2`)
copy into null_empty2
    from @mystage/data_0_0_0.csv.gz
    file_format = (format_name = 'my_csv_format');

select * from null_empty2;

+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

例:引用符を含まないデータのアンロードとロード

次の例では、データのセットが null_empty1 テーブルからユーザーのステージにアンロードされます。次に、出力データファイルを使用して、データを null_tempty2 テーブルにロードします。

-- Source table (:code:`null_empty1`) contents
+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

-- Create a file format that describes the data and the guidelines for processing it
create or replace file format my_csv_format
  empty_field_as_null=false null_if=('null');

-- Unload table data into a stage
copy into @mystage
  from null_empty1
  file_format = (format_name = 'my_csv_format');

-- Output the data file contents
1,null,NULL value
2,,Empty string

-- Load data from the staged file into the target table (:code:`null_empty2`)
copy into null_empty2
    from @mystage/data_0_0_0.csv.gz
    file_format = (format_name = 'my_csv_format');

select * from null_empty2;

+---+------+--------------+
| i | V    | D            |
|---+------+--------------|
| 1 | NULL | NULL value   |
| 2 |      | Empty string |
+---+------+--------------+

単一ファイルへのアンロード

デフォルトでは、 COPY INTO ロケーションステートメントは並列操作を活用するために、テーブルデータを一連の出力ファイルに分離します。各ファイルの最大サイズは、 MAX_FILE_SIZE コピーオプションを使用して設定されます。デフォルト値は 16000000 (16 MB)ですが、より大きなファイルに対応するために拡大することができます。Amazon S3、Google Cloud Storage、またはMicrosoft Azureステージの場合、サポートされる最大ファイルサイズは5 GB です。

データを単一の出力ファイルにアンロードするには(パフォーマンスの低下という潜在的なコストで)、ステートメントに SINGLE = true コピーオプションを指定します。オプションで、パス内のファイルの名前を指定できます。

注釈

COMPRESSION オプションがtrueに設定されている場合は、出力ファイルを圧縮解除できるように、圧縮方法に適切なファイル拡張子を持つファイル名を指定します。たとえば、 GZIP 圧縮方式が指定されている場合は、 GZ ファイル拡張子を指定します。

たとえば、 mytable テーブルデータを名前付きステージ内の myfile.csv という名前の単一ファイルにアンロードします。大きなデータセットに対応するために、 MAX_FILE_SIZE の上限を拡大ます。

copy into @mystage/myfile.csv.gz from mytable
file_format = (type=csv compression='gzip')
single=true
max_file_size=4900000000;

JSON へのリレーショナルテーブルのアンロード

COPY コマンドと組み合わせた OBJECT_CONSTRUCT 関数を使用して、リレーショナルテーブルの行を単一の VARIANT 列に変換し、行をファイルにアンロードできます。

例:

-- Create a table
CREATE OR REPLACE TABLE mytable (
 id number(8) NOT NULL,
 first_name varchar(255) default NULL,
 last_name varchar(255) default NULL,
 city varchar(255),
 state varchar(255)
);

-- Populate the table with data
INSERT INTO mytable (id,first_name,last_name,city,state)
 VALUES
 (1,'Ryan','Dalton','Salt Lake City','UT'),
 (2,'Upton','Conway','Birmingham','AL'),
 (3,'Kibo','Horton','Columbus','GA');

-- Unload the data to a file in a stage
COPY INTO @mystage
 FROM (SELECT OBJECT_CONSTRUCT('id', id, 'first_name', first_name, 'last_name', last_name, 'city', city, 'state', state) FROM mytable)
 FILE_FORMAT = (TYPE = JSON);

-- The COPY INTO location statement creates a file named data_0_0_0.json.gz in the stage.
-- The file contains the following data:

{"city":"Salt Lake City","first_name":"Ryan","id":1,"last_name":"Dalton","state":"UT"}
{"city":"Birmingham","first_name":"Upton","id":2,"last_name":"Conway","state":"AL"}
{"city":"Columbus","first_name":"Kibo","id":3,"last_name":"Horton","state":"GA"}

複数の列を持つParquetへのリレーショナルテーブルのアンロード

COPY ステートメントへの入力として SELECT ステートメントを使用して、リレーショナルテーブルのデータを複数列のParquetファイルにアンロードできます。SELECT ステートメントは、アンロードされたファイルに含めるリレーショナルテーブルの列データを指定します。 HEADER = TRUE コピーオプションを使用して、出力ファイルに列ヘッダーを含めます。

たとえば、 mytable テーブルの3つの列(idnamestart_date)の行を、命名形式 myfile.parquet の1つ以上のファイルにアンロードします。

COPY INTO @mystage/myfile.parquet FROM (SELECT id, name, start_date FROM mytable)
  FILE_FORMAT=(TYPE='parquet')
  HEADER = TRUE;

明示的に数値列をParquetデータ型に変換する

COPY INTO <場所> ステートメント内のクエリは、特定のSnowflakeテーブル列をクエリしてアンロードするための、 SELECT ステートメントの構文とセマンティクスをサポートしています。 CAST 、 :: 関数を使用して数値列のデータを特定のデータ型に変換し、アンロードされたデータのParquetデータ型を明示的に選択します。

次のテーブルは、Snowflakeの数値データ型をParquetの物理データ型と論理データ型にマップしています。

Snowflake論理データ型

Parquet物理データ型

Parquet論理データ型

TINYINT

INT32

INT(8)

SMALLINT

INT32

INT(16)

INT

INT32

INT(32)

BIGINT

INT64

INT(64)

FLOAT

FLOAT

N/A

DOUBLE

DOUBLE

N/A

次の例は、アンロードされた各列の数値データを異なるデータ型に変換して、Parquetファイル内のデータ型を明示的に選択する、 COPY INTO <場所> ステートメントを示しています。

COPY INTO @mystage
FROM (SELECT CAST(C1 AS TINYINT) ,
             CAST(C2 AS SMALLINT) ,
             CAST(C3 AS INT),
             CAST(C4 AS BIGINT) FROM mytable)
FILE_FORMAT=(TYPE=PARQUET);