S3（外部ステージ）からSnowflakeにデータをインポートする手順について
Snowflakeに外部ステージからデータをインポートする方法については公式ドキュメントも充実しているし、他の方のQiitaやブログもたくさんありますが、自身での整理も含め書いていきたいと思います！
CSVデータのインポート手順
■S3のディレクトリをSnowflakeのステージに上げる
create or replace stage csv_stage
url=S3のURL
CREDENTIALS=(AWS_KEY_ID='キー' AWS_SECRET_KEY='キー')
ENCRYPTION=(TYPE='AWS_SSE_KMS' KMS_KEY_ID = 'aws/key');
-
create or replace stage csv_stageでステージ名はわかりやすい任意の名前を設定します。 -
url=s3://~~~~にはCSVデータが格納されているURLを指定します。
■ステージのファイルをチェックする
下記クエリでステージのファイル一覧を確認できます。
list @csv_stage;
-
list @csv_stageの@に続けて上記で作成したステージ名を指定します。
■データのカラム内容をチェック
下記クエリでステージにあるファイルの内容をチェックできます。
これでカラムをチェックし、インポート用のテーブルを作成すると良いと思います。
select
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15
from @csv_stage/20240001_0000.csv.gz
limit 10;
- select句で
$1, $2と記述すると、１カラム目、2カラム目といった形でselectできます。存在しないカラム番号まで記述しても問題なくクエリできます。（空のカラムが出力されるだけ） -
from @csv_stage/20240001_0000.csv.gzのように、ステージにある個別ファイルを指定可能です。 -
limitなど通常のクエリを実行可能です。
■インポート用のテーブルを作成する。
上記のとおりカラム内容をチェックしたら、それに従ってインポート先のテーブルを用意します。
CREATE OR REPLACE TABLE gps_log_tabale (
user_id varchar,
log_time TIMESTAMP_NTZ(9),
latitude float,
longitude float
);
■ステージのデータをテーブルにインポートする。
作成したテーブルにステージからデータをインポートします。
copy into gps_log_tabale
from @csv_stage
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP SKIP_HEADER=1)
-
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP SKIP_HEADER=1)でファイルタイプ、圧縮形式、ヘッダーをスキップするかを設定します。 - ダブルクオーテーション囲いのCSVの場合はファイルフォーマットに
FIELD_OPTIONALLY_ENCLOSED_BY = '"'を設定します。FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP SKIP_HEADER=1 FIELD_OPTIONALLY_ENCLOSED_BY = '"')
-- 別パターン1
COPY INTO gps_log_table
FROM @csv_stage
PATTERN='.*hoge.*\.csv\.gz'
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP SKIP_HEADER=1);
-
PATTERN='.*hoge.*\.csv\.gz’のようにファイルパターンをPATTERN=に正規表現で設定することで、パターンにマッチしたファイルのみをインポートすることも可能。
copy into gps_log_table(
user_id,
log_time,
latitude,
longitude
)
from (
select $1, $2, $3, $4, $5, $6, $7, $8, $9
from @csv_stage
)
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP SKIP_HEADER=1);
- from句においてステージのデータをクエリすることも可能です。
- 特定のカラムが不要なとき、インポート先のテーブルに合わせてカラムの順序を変更したい時などに利用できると思われます。
QGIS等で作成したShape等の地理空間データのインポート（GeoJSONのインポート）
以下の記事を参考にさせていただきました！
想定するシチュエーションとしては、「QGISで分析対象エリアのポリゴンを作成し、それをインポートしたい。」を考えています。
■インポート用のGeoJSONデータの準備
Snowflakeにインポートできるファイル形式はGeoJSONです。そのため、Shapeファイル等の場合にはGeoJSON形式に変換が必要です。変換にはQGISのエクスポート機能を利用します。
↓QGISでの変換手順
Snowflakeでデータインポート用のテーブルを作成する
※GeoJSONはS3に保存し、そのS3をステージにあげておきます。
-- コピー用のテーブルを作成
CREATE OR REPLACE TEMPORARY TABLE airport_geojson_raw (
src variant
);
-- 実テーブルを作成
CREATE OR REPLACE TABLE airport_polygon (
id number,
name varchar,
geom geometry
);
- GeoJSONを一時的にコピーしておくテーブルを作成します。
- 実テーブルのカラム構成はもとのGeoJSONのkeyとジオメトリ用のカラムを作成します。
ステージからコピー用テーブルにGeoJSONをコピーする
-- ステージからコピー用テーブルにgeojsonをコピー
copy into
airport_geojson_raw
from
@geojson_stage/airport_4326.geojson
FILE_FORMAT = (TYPE = 'JSON')
;
- ステージからコピー用テーブルにGeoJSONをコピーします。
- この段階ではJSONがまるっとコピーされており、key_value等には分かれていない。
コピー用テーブルから実テーブルにインサート
-- コピー用テーブルから実テーブルにインサート
INSERT INTO airport_polygon
SELECT
value:properties.id::number AS id,
value:properties.Name::varchar AS name,
value:geometry AS geom
FROM
airport_geojson_raw
, LATERAL FLATTEN(INPUT => src:features)
;
- QGIS等で作成していた時のフィールド値は
value:properties.フィールド名で取り出せます。上記の例ではQGIS上でidとNameというフィールドを作っており、それらのフィールド値を取り出しています。 - ジオメトリは
value:geometryで取り出せます。
SRIDを設定
-- SRIDの設定
UPDATE airport_polygon
SET geom = ST_SetSRID(geom, 4326);
- ジオメトリにSRIDを設定します。
不要なテーブルを削除
-- インポート用に利用したテーブルの削除
drop table airport_geojson_raw;
- 最後に、インポートに利用したコピー用テーブルを削除します。