🤖

Snowflake ストリーム機能スマートな処理について

2022/11/12に公開約2,400字

前回記事ではストリーム機能を使って、INSERT,UPDATE,DELETEの動作確認をそれぞれ確認しました。
今回はそれらの動作をスマートに実施する方法を見ていきます。

環境の確認

SALES_RAW_STAGINGの確認

前回記事で最後に実施した状態でID2が欠落している状態です。

SALES_FINAL_TABLEの確認

SALES_RAW_STAGINGと同じ状態なので結果は割愛します。

SELECT * FROM SALES_FINAL_TABLE;

SALES_STREAMの確認

空なので結果は割愛します。

SELECT * FROM SALES_STREAM;    

INSERTしてデータを追加する

前回記事で削除したID2をここで追加します。
※特に深い意味はありません。

INSERT INTO SALES_RAW_STAGING VALUES (2,'Lemon',0.99,1,1);

ストリームオブジェクトの確認
INSERT履歴が格納されていることを確認

SELECT * FROM SALES_STREAM;

スマートにSALES_FINAL_TABLEにMERGEする

ちなみにですが下記SQLでINERT,UPDATE,DELETEすべてに対応できます。

merge into SALES_FINAL_TABLE F 
USING ( SELECT STRE.*,ST.location,ST.employees
        FROM SALES_STREAM STRE
        JOIN STORE_TABLE ST
        ON STRE.store_id = ST.store_id
       ) S
ON F.id=S.id
when matched
    and S.METADATA$ACTION ='DELETE' 
    and S.METADATA$ISUPDATE = 'FALSE'
    then delete                   
when matched
and S.METADATA$ACTION ='INSERT' 
    and S.METADATA$ISUPDATE  = 'TRUE'       
    then update 
    set f.product = s.product,
        f.price = s.price,
        f.amount= s.amount,
        f.store_id=s.store_id
when not matched 
    and S.METADATA$ACTION ='INSERT'
    then insert 
    (id,product,price,store_id,amount,employees,location)
    values
    (s.id, s.product,s.price,s.store_id,s.amount,s.employees,s.location)

SALES_FINAL_TABLEのデータを確認してみると、追加したID2がマージされています。

SELECT * FROM SALES_FINAL_TABLE;

他の操作も見ていきます

UPDATEの動作

'Lemon'を'Lemonade'に更新します。

UPDATE SALES_RAW_STAGING
SET PRODUCT = 'Lemonade'
WHERE PRODUCT ='Lemon'

先ほど実行した複雑なMERGE句を実行します。
そしてSALES_FINAL_TABLEを確認してみる。

SELECT * FROM SALES_FINAL_TABLE;

DELETEの動作

'Lemonade'を削除します。

DELETE FROM SALES_RAW_STAGING WHERE PRODUCT = 'Lemonade';       

再度先ほど実行した複雑なMERGE句を実行します。
そしてSALES_FINAL_TABLEを確認してみる。
'Lemonade'が消えマージされています。

SELECT * FROM SALES_FINAL_TABLE;


マージするSQLは複雑ですが、操作によってSQLを変更しなくてもよいことは素晴らしいです。

Discussion

ログインするとコメントできます