■ はじめに
https://dk521123.hatenablog.com/entry/2023/12/07/060129
の続き。 今回は、Delete and Insert について、深堀っていく
目次
【1】構文 【2】サンプル
【1】構文
{{ config( materialized='incremental', unique_key=['user_id' , ...], incremental_strategy='delete+insert' ) }}
【2】サンプル
[0] サンプルテーブル定義
DROP TABLE IF EXISTS daily_sample_purchase_history; CREATE TABLE daily_sample_purchase_history ( purchase_id VARCHAR NOT NULL, item_name VARCHAR NOT NULL, number INT NOT NULL, created_at DATE NOT NULL );
[1] 初期データ (1回目)
INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y001', 'Apple', 2, '2023-12-01'); INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y002', 'Melon', 5, '2023-12-01'); SELECT * FROM daily_sample_purchase_history; ~~~~~~ "purchase_id","item_name","number","created_at" "Y001","Apple","2","2023-12-01" "Y002","Melon","5","2023-12-01" ~~~~~~
[3] モデル「models/full_sample_purchase_history.sql」定義 (1回目)
{{ config( materialized='incremental', unique_key=['created_at'], incremental_strategy='delete+insert' ) }} WITH updated_purchase_history AS ( SELECT purchase_id AS purchase_id, item_name AS item_name, number AS number, created_at::DATE AS created_at FROM {{ source('demo_source', 'daily_sample_purchase_history') }} ) SELECT * FROM updated_purchase_history
[4] dbt run (1回目)
dbt run
[5] 確認
SELECT * FROM full_sample_purchase_history; ~~~~~~ "purchase_id","item_name","number","created_at" "X001","Apple","2","2023-12-01" "X002","Melon","5","2023-12-01" ~~~~~~
[6] 追加データ投入 (2回目)
-- 1回目のデータをクリア Truncate table daily_sample_purchase_history; -- 更新データ INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y001', 'Orange', 12, '2023-12-01'); INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y003', 'Pineapple', 21, '2023-12-01'); SELECT * FROM daily_sample_purchase_history; ~~~~~~ "purchase_id","item_name","number","created_at" "Y001","Orange","12","2023-12-01" "Y003","Pineapple","21","2023-12-01" ~~~~~~
[7] dbt run (2回目)
dbt run
[8] 確認
SELECT * FROM full_sample_purchase_history ORDER BY purchase_id; ~~~~~~ "purchase_id","item_name","number","created_at" "Y001","Orange","12","2023-12-01" "Y003","Pineapple","21","2023-12-01" ~~~~~~
[9] 追加データ投入 (3回目)
-- 2回目のデータをクリア Truncate table daily_sample_purchase_history; -- 更新データ INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y001', 'Mango', 23, '2023-12-02'); INSERT INTO daily_sample_purchase_history (purchase_id, item_name, number, created_at) VALUES ('Y005', 'Banana', 34, '2023-12-02'); SELECT * FROM daily_sample_purchase_history; ~~~~~~ "purchase_id","item_name","number","created_at" "Y001","Mango","23","2023-12-02" "Y005","Banana","34","2023-12-02" ~~~~~~
[10] dbt run (3回目)
dbt run
[11] 確認
SELECT * FROM full_sample_purchase_history ORDER BY purchase_id; ~~~~~~ ~~~~~~
関連記事
dbt ~ 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2023/06/30/000000
dbt ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2023/05/30/151003
dbt ~ 基本編 / Source ~
https://dk521123.hatenablog.com/entry/2023/12/08/111012
dbt ~ 更新 / 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2023/12/07/060129
dbt ~ 更新 / Update or Insert / Insert ~
https://dk521123.hatenablog.com/entry/2023/12/19/224453
dbt ~ 更新 / DROP + CTAS ~
https://dk521123.hatenablog.com/entry/2023/12/04/000000