【Snowflake】AmazonS3上のファイルのデータをロードする
sfadmin以前「ローカルファイルのデータロード方法」をご紹介しました。
⇒【Snowflake】ローカルファイルのデータをロードする
今回は「AmazonS3上のファイルのデータロード方法」をご紹介します。
以下のような方におすすめの方法となります。
・AmazonS3上にあるファイルをロードしたい方
・AmazonS3を経由してファイルをロードしたい方
目次
- やりたいこと
- 事前準備
- ファイルの準備
- データロード先の作成
- 外部ステージの作成
- SnowSQLのインストール
- SnowSQLでのファイルロード
- Snowflakeログイン
- ファイルのロード
- ロードしたデータの確認
- WEB インターフェースでのファイルロード
- Snowflakeログイン
- ファイルのロード
- ロードしたデータの確認
- まとめ
1. やりたいこと
AmazonS3に用意したデータファイル（CSVファイル）のデータを
Snowflakeのテーブルへ登録（ロード）したい。
データロードの方法として、
・SnowSQLでコマンドを使用してロードする方法
・WEBインターフェース上で操作してロードする方法
があります。それぞれやってみます。
※WindowsOSでの操作となるため、他のOSでは多少の操作の違いがあります。
2. 事前準備
ファイルの準備
ロードするデータファイルを用意します。
SnowflakeではCSVやJSON、XMLなどの形式のファイルがサポートされています。
今回用意したのは2ファイルです。
1ファイル目：ヘッダー行無し、ダブルクォーテーションで括られたCSVファイル
2ファイル目：ヘッダー行あり、ダブルクォーテーションで括られたCSVファイル
sales_202311.csv
"100001","2023-11-01","100001","0051","1500"
"100002","2023-11-01","100002","0052","2000"
"100003","2023-11-02","100001","0060","800"
"100004","2023-11-03","100003","1020","3200"
sales_202312.csv
"slip_number","sales_date","sales_code","product_code","amount"
"100005","2023-12-01","100001","0053","1000"
"100006","2023-12-01","100002","0060","800"
"100007","2023-12-02","100001","0061","900"
"100008","2023-12-03","100003","1050","4000"
用意したファイルはAmazonS3の任意のバケットにアップロードしておきましょう。
バケット内にフォルダを作成し、その下にアップロードしても構いません。
例）S3バケット「snowflake」に「sales」というフォルダを作成し、そのフォルダ内にファイルをアップロードする
参考）オブジェクトのアップロード - Amazon Simple Storage Service
データロード先の作成
※【Snowflake】ローカルファイルのデータをロードする と同じ内容のため、
既に作成されている場合はこの手順はスキップして構いません。
ファイルをロードする前に、ロード先となる
・データベース
・スキーマ
・テーブル
が必要です。Snowflakeにデータをロードする先を作成します。
データベース
WEBインターフェースでログインし、
左側メニューから データ>データベース を開きます。
画面右側、データベースの作成ボタンをクリックし、任意のデータベースを作成します。
今回は「TRAINING_DB」という名前のデータベースを作成しました。
スキーマ
スキーマを用意します。
今回は、データベースを作成時にデフォルトで作成されるスキーマ「PUBLIC」を使用します。
別のスキーマを使用したい場合、
先ほど作成したデータベースを画面中央のメニューからクリックし、
画面右側、スキーマの作成ボタンをクリックし、任意のスキーマを作成します。
テーブル
テーブルを作成します。
テーブルを作成するためには、SQLを実行する必要があります。
今回は用意したファイルに合わせたテーブルにするため、以下のSQLを用意しました。
テーブル名：SALSE
// Create SALES table
CREATE OR REPLACE TABLE SALES (
slip_number VARCHAR(6)
,sales_date DATE
,sales_code VARCHAR(6)
,product_code VARCHAR(4)
,amount NUMBER
);
左側メニューから ワークシート を開きます。
画面右側、＋ボタンをクリックし、作成されたワークシートに上記SQLを貼り付け、実行ボタンをクリックします。
※データベースとスキーマは前述の手順で作成したものを指定してください
外部ステージの作成
AmazonS3とSnowflake間でファイルのやり取りをする場合、コマンド（SQL）は2通りの方法があります。
①S3バケットのパスを直接指定する方法
②外部ステージを使用する方法
参考）Amazon S3からの一括ロード | Snowflake Documentation
以降のデータロードの手順では①、②の両方を紹介していますが、
以下の理由から「②外部ステージを使用する方法」をおすすめしています。
※「外部ステージ」について
外部ステージを使用することで、よりセキュアなファイルのやり取りが可能となります。
・AWSアクセスキーやシークレットアクセスキーの漏洩がない
・S3バケットへのアクセス、操作権限等の許可を制限できる
・ネットワークトラフィックがパブリックインターネットを通過しない(AWSネットワーク内で完結する）
また、同じパスから定期的にファイルロードする場合も使用することが推奨されています。
なお、②の手順については使用するAmazonS3のバケットがSnowflakeアカウントと同じ AWS のリージョンであることが前提となります。
参考）S3ステージの作成 | Snowflake Documentation
①の方法で進める場合、この手順はスキップして構いません。
②の方法で進める場合、以下を参考にして外部ステージを作成してください。
外部ステージの作成のために、まずは「ストレージ統合」を作成する必要があります。
「ストレージ統合」とは、アクセス管理(IAM)や外部ストレージ(今回はAmazonS3)上の保管場所へのアクセス許可情報をSnowflakeで管理するオブジェクトのことです。
ストレージ統合の保管場所の指定は、S3バケットを指定することはもちろん、S3バケット内の特定フォルダのみを指定して作成することも可能です。
作成手順については、Snowflake公式のドキュメントが非常にわかりやすいです。
ファイルの準備をしたパスのストレージ統合、および外部ステージを以下のドキュメントを参考に作成してください。
オプション1：Amazon S3にアクセスするためのSnowflakeストレージ統合の構成 | Snowflake Documentation
「②外部ステージを使用する方法」のため、今回は上記手順を参考にし、S3バケット内の特定フォルダ（サブフォルダも対象）に対し、参照、更新、削除等が可能なストレージ統合を作成しました。
このストレージ統合に対して作成した外部ステージ「MY_S3_STAGE_BLUE」を使用して、以降の手順を進めていきます。
※今回ロードするファイルはそれぞれファイル形式が異なるため、外部ステージ作成時にファイル形式は指定していません
SnowSQLのインストール
今回は「SnowSQL」という、Snowflakeと接続可能なコマンドラインクライアントを使用したデータロードをご紹介します。
この方法でデータロードをする場合は、SnowSQLをインストールしてください。
インストール方法は以下の公式ドキュメントを参照してください。
SnowSQL （CLI クライアント） | Snowflake Documentation
3. SnowSQLでのファイルロード
Snowflakeログイン
コマンドラインを起動し、Snowflakeへログインします。
SnowSQLのログインコマンドを実行しますが、この際にデータベースやスキーマを選択しておくと、
この後のコマンドで指定する必要がなくなります。
※今回はログイン時に指定し、以後のコマンドで指定しない方法で進めます。
snowsql -a <orgname>-<accountname> -u <username> -d <dbname> -s <schemaname>
参考）SnowSQLを介した接続 | Snowflake Documentation
ファイルのロード
AmazonS3へアップロードしたファイルのデータを、テーブルへロードします。
SnowSQLで「COPY INTO」コマンドを実行します。
「1ファイル目：ヘッダー行無し、ダブルクォーテーションで括られたCSVファイル」をロードするため、以下のファイル形式を指定します。
・ファイル形式：CSV
・オプションで囲まれたフィールド：二重引用符
①S3バケットのパスを直接指定する方法
FROM句に直接ファイルパス、認証情報を指定し、「SALES」テーブルへファイルをロードします。
COPY INTO SALES
FROM s3://<bucket>/<path>/sales_202311.csv credentials=(AWS_KEY_ID='$AWS_ACCESS_KEY_ID' AWS_SECRET_KEY='$AWS_SECRET_ACCESS_KEY')
FILE_FORMAT = (TYPE = csv FIELD_OPTIONALLY_ENCLOSED_BY='"');
※<bucket>と<path>の部分はご自身のS3バケットやフォルダ構成に合わせて書き換えてください
※$AWS_ACCESS_KEY_ID、$AWS_SECRET_ACCESS_KEYの部分はご自身のAWSアカウントの認証情報に書き換えてください
②外部ステージを使用する方法
FROM句に外部ステージを指定し、「SALES」テーブルへファイルをロードします。
COPY INTO SALES
FROM @MY_S3_STAGE_BLUE
PATTERN = '.*sales_202311.csv'
FILE_FORMAT = (TYPE = csv FIELD_OPTIONALLY_ENCLOSED_BY='"') ;
※外部ステージ名（MY_S3_STAGE_BLUE）の部分はご自身の作成した外部ステージ名に書き換えてください
※今回は「sales_202311.csv」のみをロード対象にしていますが、PATTERNオプションで対象は指定できます。 例）'.*.csv'
正常にロードされた場合、このように表示されます。
参考）S3ステージからのデータのコピー | Snowflake Documentation
COPY INTO <テーブル> | Snowflake Documentation
ロードしたデータの確認
「SALES」テーブルにロードされたデータを参照します。
SnowSQLで「SELECT」ステートメントを実行します。
SELECT * FROM SALES;
CSVファイルに記入されていたデータが、「SALES」テーブルに登録されていることが確認できました。
参考）SELECT | Snowflake Documentation
4. WEB インターフェースでのファイルロード
Snowflakeログイン
WEBインターフェースでログインし、
左側メニューから ワークシート を開きます。
ファイルのロード
3. SnowSQLでのファイルロードと同様に「COPY INTO」コマンド（SQL）を使用します。
「2ファイル目：ヘッダー行あり、ダブルクォーテーションで括られたCSVファイル」をロードするため、以下のファイル形式を指定します。
・ファイル形式：CSV
・ヘッダー：最初の行をスキップ
・オプションで囲まれたフィールド：二重引用符
画面右側、＋ボタンをクリックしてワークシートを開きます。
データベースとスキーマに前述の手順で作成したものを指定してください
①S3バケットのパスを直接指定する方法
下記SQLを貼り付け、実行ボタンをクリックします。
COPY INTO SALES
FROM s3://<bucket>/<path>/sales_202312.csv credentials=(AWS_KEY_ID='$AWS_ACCESS_KEY_ID' AWS_SECRET_KEY='$AWS_SECRET_ACCESS_KEY')
FILE_FORMAT = (TYPE = csv FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER = 1);
※<bucket>と<path>の部分はご自身のS3バケットやフォルダ構成に合わせて書き換えてください
※$AWS_ACCESS_KEY_ID、$AWS_SECRET_ACCESS_KEYの部分はご自身のAWSアカウントの認証情報に書き換えてください
②外部ステージを使用する方法
下記SQLを貼り付け、実行ボタンをクリックします。
COPY INTO SALES
FROM @MY_S3_STAGE_BLUE
PATTERN = '.*sales_202312.csv'
FILE_FORMAT = (TYPE = csv FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER = 1);
※外部ステージ名（MY_S3_STAGE_BLUE）の部分はご自身の作成した外部ステージ名に書き換えてください
※今回は「sales_202312.csv」のみをロード対象にしていますが、PATTERNオプションで対象は指定できます。 例）'.*.csv'
正常にロードされた場合、このように表示されます。
ロードしたデータの確認
「SALES」テーブルにロードされたデータを参照します。
左側メニューから ワークシート を開きます。
画面右側、＋ボタンをクリックし、作成されたワークシートに下記SQLを貼り付け、実行ボタンをクリックします。
SELECT * FROM SALES;
※データベースとスキーマは前述の手順で作成したものを指定してください
2ファイル分のデータ（8レコード）が、「SALES」テーブルに登録されていることが確認できました。
5. まとめ
今回は「AmazonS3上のファイルのデータロード方法」をご紹介しました。
初期設定に少し手間がかかりますが、AmazonS3とやりとりするための外部ステージを作成すると、ファイルのロードがかなりスムーズにおこなえます。
ストレージ統合はデータのアンロードの際にも使用できるため、一度作成しておくと便利です。
ぜひ試してみてください。
