Zenn
ちゅらデータ株式会社Publicationへの投稿
❄️

Frosty Friday Week120 Basic Synthetic Data

Gaku Tashiro＠ちゅらデータエンジニア
2024/12/11に公開
Snowflake
frostyfriday
tech

こんにちは！　がく＠ちゅらデータエンジニアです。

https://qiita.com/advent-calendar/2024/frostyfriday

こちらの11日目の記事になります。

Frosty Friday Week121 Basic Synthetic Data

Synthetic Data！合成データです。
シンセサイザーってありますけど、あれも「合成」ってことなのかな・・・

合成データって何？

Snowflakeでは2024年10月28日にパブリックプレビューになった機能です。

https://zenn.dev/taro_cccmkhd/articles/2aa501bd841128

ちょうど、CCCMKのtaroさんが、11月にZennでブログに記載されています。
すごく簡単に言うと
「実データの統計的特性を保持しながら、人工的に生成された新しいデータセット」（taroさんのブログから引用）

実際、本番データを開発環境などで使うのは、セキュリティ観点でNGなことが多いと思います。
ただ本番データ相当のダミーデータを作り、それで開発・・・となると結構手間がかかりると思います。

Pythonで実装してみたり、生成AIで生成したり・・・・
カラムに入るデータ形式やPKが・・・・などのテーブル定義があればある程度出来ますが、統計的にも値の範囲などもそれなりに本番に近いデータを作るのは結構な手間だと思います。

Week120のチャレンジ内容

スタートアップスクリプト

CREATE OR REPLACE VIEW WEB_SALES as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_SALES
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_RETURNS as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_RETURNS
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_SITE as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_SITE
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_PAGE as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_PAGE
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_ITEM as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.ITEM
LIMIT 5000
);

次に、GENERATE_SYNTHETIC_DATA() を使用して上記のビューの上に偽のテーブルのセットを作成します。成功すると、次のような結果が得られます。

実行結果は、

select 
    ws.*,
    wr.*,
    wp.*,
    wsi.*,
    wi.*
from frosty_db.week_120.web_sales ws
left join frosty_db.week_120.web_returns wr
    on ws.ws_web_page_sk = wr.wr_web_page_sk
    and ws.ws_item_sk = wr.wr_item_sk
left join frosty_db.week_120.web_page wp
    on ws.ws_web_page_sk = wp.wp_web_page_sk
left join frosty_db.week_120.web_site wsi
    on ws.ws_web_site_sk = wsi.web_site_sk
left join frosty_db.week_120.web_item wi
    on ws.ws_item_sk = wi.i_item_sk;

これが動くはず
合成データの生成を楽しんでください！！

ってお題です。

やっていこう

環境の設定

use role sysadmin;
use warehouse gaku_wh;

use database frosty_friday;
create or replace schema week120;

いつも通り実行環境を設定します。スキーマもweek120で作成。

そしてスタートアップスクリプトを動かしていきます。

スタートアップスクリプト

CREATE OR REPLACE VIEW WEB_SALES as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_SALES
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_RETURNS as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_RETURNS
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_SITE as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_SITE
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_PAGE as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.WEB_PAGE
LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_ITEM as (
SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.ITEM
LIMIT 5000
);

合成データの元となるテーブル／ビューを確認

select count(*) from WEB_SALES;


5000件あることを確認

https://docs.snowflake.com/en/sql-reference/stored-procedures/generate_synthetic_data
こちらはリファレンス。文法とかオプションの記載があるので、こちらを見るのが良さそう。
こちらでは単一のテーブルについて適用する例が載ってます。

https://docs.snowflake.com/en/user-guide/synthetic-data
こちらはユーザガイド。使用例が載っています。
複数のテーブルを使った例が載っています。

input_table, output_tableは、完全修飾名（<database>.<schema>.<view>）で行く必要があるみたいです。
※リファレンスには、相対名でもいいとありますが、うまく動かなさそう。（2024年12月現在）

出題にあるSQLから、下記のようにJoin Keyになりそうなので、それを設定していきます。

-- ws_web_page_sk : wr_web_page_sk
-- ws_item_sk     : wr_item_sk
-- ws_web_page_sk ; wp_web_page_sk
-- ws_web_site_sk : web_site_sk
-- ws_item_sk     : i_item_sk
CALL SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA

CALL SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA({
'datasets':[
{
'input_table': 'frosty_friday.week120.WEB_SALES',
'output_table': 'frosty_friday.week120.WEB_SALES_SYNTHETIC',
'columns': {'ws_web_page_sk': {'join_key': True}, 'ws_item_sk': {'join_key': True}, 'ws_web_site_sk': {'join_key': True}}
}
, {
'input_table': 'frosty_friday.week120.WEB_RETURNS',
'output_table': 'frosty_friday.week120.WEB_RETURNS_SYNTHETIC',
'columns': {'wr_web_page_sk': {'join_key': True}, 'wr_item_sk': {'join_key': True}}
}
, {
'input_table': 'frosty_friday.week120.WEB_SITE',
'output_table': 'frosty_friday.week120.WEB_SITE_SYNTHETIC',
'columns': {'web_site_sk': {'join_key': True}}
}
, {
'input_table': 'frosty_friday.week120.WEB_PAGE',
'output_table': 'frosty_friday.week120.WEB_PAGE_SYNTHETIC',
'columns': {'wp_web_page_sk': {'join_key': True}}
}
, {
'input_table': 'frosty_friday.week120.WEB_ITEM',
'output_table': 'frosty_friday.week120.WEB_ITEM_SYNTHETIC',
'columns': {'i_item_sk': {'join_key': True}}
}
]
, 'privacy_filter': false
, 'replace_output_tables':True
});

Warehouse size : XS
で、1分半ほどで、合成データが生成されました。

チャレンジにある確認SQLを動かしてみる

-- SYNTHENIC DATA joined
select 
    ws.*,
    wr.*,
    wp.*,
    wsi.*,
    wi.*
from WEB_SALES_SYNTHETIC ws
left join WEB_RETURNS_SYNTHETIC wr
    on ws.ws_web_page_sk = wr.wr_web_page_sk
    and ws.ws_item_sk = wr.wr_item_sk
left join WEB_PAGE_SYNTHETIC wp
    on ws.ws_web_page_sk = wp.wp_web_page_sk
left join WEB_SITE_SYNTHETIC wsi
    on ws.ws_web_site_sk = wsi.web_site_sk
left join WEB_ITEM_SYNTHETIC wi
    on ws.ws_item_sk = wi.i_item_sk;

SQLの実行は出来たのですが・・・

  • WEB_RETURNS_SYNTHETICは突合0件
    • WEB_RETURNはOriginalの方も突合0件なので問題なし（？）
  • WEB_ITEM_SYNTHETICは突合0件
    • WEB_ITEMはオリジナルの方は突合はしていたので、うまく行ってない

まとめ

2024年10月にパブリックプレビューになった合成データ（GENERATE_SYNTHETIC_DATA）についてのチャレンジでした。
合成データは、本番データを元に、開発用のデータを作る機能です。
まぁ、本番データにアクセスして・・・ってのがそもそも難しい場合もあるかなぁとも思いますが、できるだけ本番データに近いデータで開発やQAなどもやりたいですよね。

まだプレビュー機能だから、バグがあるかもしれません。（今回では5番目のビューが突合してくれない）
GAになったらまた試してみようと思います。

解法はGitに公開しています。

https://github.com/gakut12/Frosty-Friday/blob/main/week120_basic_generate_synthetic_data/week120.sql

ちゅらデータ株式会社
ちゅらデータ株式会社Publication

沖縄に「最高に面白い仕事」を作る IT ベンチャーです！ エンジニア積極採用中！！

Discussion