【Snowflake】Snowflake ～ Partition構成のデータを取り込む ～

Snowflake

■ はじめに

　パーティション (Partition) のようなパス構成
（e.g. s3://your-s3-bucket/test/demo/date=20221028/）のデータを
Snowflake で取り込む必要がでてきそうだったので
その方法についてまとめる。

後日談 

* 色々調べてみると、同じようなことを公式ドキュメントでもやってた
 => 一旦、以下の公式ドキュメントを確認した方がいいかも。

https://docs.snowflake.com/ja/sql-reference/sql/create-external-table.html#examples

目次

【１】今回のお題
　１）フォルダ構成例
　２）補足：パーティションについて
【２】対処案
　１）SQL例
【３】使用した技術事項
　１）メタデータ「METADATA$FILENAME」
　２）SPLIT_PART()

【１】今回のお題

* 以下「フォルダ構成例」のように、
　パーティションのような「city=<city_name>」が
　入ったパス構成のものを一回のSQLで吸い出して、
　パーティションの値（今回の場合、<city_name>）も含めて
　Snowflakeのテーブルに入れたい。

１）フォルダ構成例

s3://your-s3-bucket/test/demo/
 + city=tokyo/
    + xxxx1.json.gz
    + xxxx2.json.gz
 + city=osaka/
    + yyyy1.json.gz
    + yyyy2.json.gz
 + city=fukuoka/
    + zzzz1.json.gz
    + zzzz2.json.gz

２）補足：パーティションについて

https://dk521123.hatenablog.com/entry/2020/07/16/224332

でやったように
Spark（Hive）の PARTITIONED BY が Snowflake にあればいいのだが
Snowflakeのステージには、パーティションという概念がそもそもない
 => 以下の公式ドキュメント参照。

https://docs.snowflake.com/ja/sql-reference/sql/create-stage.html

補足：CREATE EXTERNAL TABLE 

* CREATE EXTERNAL TABLE なら「PARTITION BY」はある。

https://docs.snowflake.com/ja/sql-reference/sql/create-external-table.html

【２】対処案

* 対処のポイントとしては以下の通り。
~~~~~
[1] ステージ作成時のURLの指定は、
　パーティション前のパスを指定
 => 指定した配下のファイル全て（ファルダ内でも）
　　取得してくれる。

[2] パーティション部分は、
　METADATA$FILENAMEおよび文字列操作関数
　（今回の場合、SPLIT_PART関数）を使って
　切り出して取得する
~~~~~
 => Snowflake の 文字列操作関数 については、
　　以下の関連記事を参照のこと

Snowflake ～ 文字列操作関連 ～
https://dk521123.hatenablog.com/entry/2022/10/01/000000

１）SQL例

USE DATABASE test_db;
USE SCHEMA test_schema;

CREATE TEMPORARY STAGE IF NOT EXISTS test_stage
  URL = 's3://your-s3-bucket/test/demo/' -- ★ポイント[1]★
  FILE_FORMAT = (TYPE = JSON)
;

CREATE TABLE test_db.test_schema.user_table IF NOT EXISTS (
  user_id VARCHAR,
  user_name VARCHAR,
  city VARCHAR DEFAULT NULL
);

COPY INTO test_db.test_schema.user_table (
  user_id,
  user_name,
  city
)
FROM
(
  SELECT
    $1:user_id,
    $1:user_name,
    SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', -2), '=', -1) AS city, -- ★ポイント[2]★
  FROM
    @test_stage
);

【３】使用した技術事項

１）メタデータ「METADATA$FILENAME」

* ファイル名(＆パス）を取得できる

https://docs.snowflake.com/ja/user-guide/querying-metadata.html#metadata-columns

SQL例 

SELECT
  -- e.g. test/demo/city=tokyo/xxxx1.json.gz
  METADATA$FILENAME AS c1,
FROM
  @test_stage
;

２）SPLIT_PART()

* 指定された文字列を分割し、リクエストされた部分を返す。
* パラメーターが NULL の場合、 NULL が返す

https://docs.snowflake.com/ja/sql-reference/functions/split_part.html
構文 

SPLIT_PART(<string>, <delimiter>, <partNumber>)
-- <string> : 対象テキスト
-- <delimiter> : 分割する区切り文字（'--'のような文字列もOK）
-- <partNumber> : 取得したいインデックス

例１：Partitionの値を取得する経過 

SELECT
  -- e.g. test/demo/city=tokyo/xxxx1.json.gz
  METADATA$FILENAME AS c1,
  -- e.g. city=tokyo
  SPLIT_PART(METADATA$FILENAME, '/', -2) AS c2,
  -- e.g. tokyo
  SPLIT_PART(SPLIT_PART(METADATA$FILENAME, '/', -2), '=', -1) AS city,
FROM
  @test_stage
;

例２：ファイル名取得 

-- 例：s3://your-s3-bucket-name/a1/b1/c1/test_20220901.csv
SELECT
  SPLIT_PART(METADATA$FILENAME, '/', -1) -- ★「test_20220901.csv」
FROM
  @demo_temp2_stage;

関連記事

Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ 基本編 / CREATE・ALTER TABLE ～
https://dk521123.hatenablog.com/entry/2022/09/03/012113
Snowflake ～ 基本編 / ステージ ～
https://dk521123.hatenablog.com/entry/2022/09/01/220643
Snowflake ～ ストレージ統合 ～
https://dk521123.hatenablog.com/entry/2022/06/29/221037
Snowflake ～ 文字列操作関連 ～
https://dk521123.hatenablog.com/entry/2022/10/01/000000