Snowflakeで ダミーデータを作ろう

Snowflakeでダミーデータを作る際の小ネタ集です。参考になれば幸いです。

Transcript

1. Snowflakeで ダミーデータを作ろう 2024/11/22 KAG 福田陽一
2. 自己紹介 • 名前 : 福田陽一 • 役職 : Data Engineer / Infrastructure Engineer 2024/11/22
3. ダミーデータ、必要ですよね？ • ダミーデータ、あるいはテストデータと呼ばれるもの • 動作確認をするための何らかのデータ • 検証(システムテスト等)のために本番データを模倣したデータ • パフォーマンスチェック、負荷試験目的の大量データ • いざ作ろうとすると… • 結構面倒くさい • 泥臭い作業が多い • ダミーデータを作るのは… • 大事なタスク • 結構テクいことをやる • データエンジニアリングのうち (と思っている) 2024/11/22
4. どうやって作ってますか？ • Snowflake上で直接INSERT INTO、CTAS • ファイル(CSV等)からインポート • dbt (dbt seed、dbt run)で投入 • Python, Snowparkでロジックを組んで投入 • …etc 2024/11/22
5. Snowflake上で使える手法 • 関数を組み合わせてダミーデータを生成する • GENERATOR (https://docs.snowflake.com/ja/sql-reference/functions/generator) • 指定された数だけ行を生成 • UNIFORM (https://docs.snowflake.com/ja/sql-reference/functions/uniform) • 一様分布の疑似乱数を生成 • RANDOM (https://docs.snowflake.com/ja/sql-reference/functions/random) • ランダムな数値を生成 • RANDSTR (https://docs.snowflake.com/ja/sql-reference/functions/randstr) • ランダムな文字列を生成 • シーケンス系 (https://docs.snowflake.com/ja/sql-reference/functions/seq1) • 連番を生成 • etc… • 主に ジェネレーター、乱数生成系 (数字、文字列、分布)、連番等の関数を組み合わせる 2024/11/22
6. SQLの例 • 組み込み関数を組み合わせて使ってみる 2024/11/22
7. SQLの例 • 組み込み関数だけではできない機能を実現する • 例 : RANDSTR は大文字小文字英数しか出現しないので、記号を含めた文字列を生成できるようにしてみた 2024/11/22
8. SQLの例 • 半構造データを活用する • 値のバリエーションを配列で定義しておき、ランダムで1つ選出する 2024/11/22
9. データのランダム制の考慮 • 満遍なくデータを出力したい場合 • UNIFORM(一様分布)に任せる 2024/11/22
10. データのランダム制の考慮 • 標準偏差を使って配列の要素の中から選択させている • 配列の番号からはみ出さないように上限・下限も制御 2024/11/22 • データの出現に偏りを持たせたい場合(1) • NORMAL(標準偏差)
11. データのランダム制の考慮 2024/11/22 • ZIPF(s, N, gen) • sが大きいほど上位が頻出する • Nに指定可能な数値は1〜16,777,215のため 配列の要素0が選ばれるようにするには -1が必要 • データの出現に偏りを持たせたい場合(2) • ZIPF(Zipf分布、ジップの法則)
12. データの出現確率を任意で制御したい • UNIFORMと条件分岐(CASE-WHEN)の合わせ技 2024/11/22 • 閾値を任意に設定することで選出される確率を制御 • 記述は短めだが、大量のデータや、カラム数が多いときつい
13. データの出現確率を任意で制御したい • 条件分岐(WHEN)を使わず実現する方法 2024/11/22 半構造データで選択肢と 各要素の割合を定義しておく
14. データの出現確率を任意で制御したい • 各属性の当選番号を累積和で決めておいて、UNIFORMで抽選番号を発行している 2024/11/22
15. データの出現確率を任意で制御したい 2024/11/22 • 各属性の当選番号を累積和で決めておいて、UNIFORMで抽選番号を発行している 1 2 3
16. データの出現確率を任意で制御したい 2024/11/22 • 各属性の当選番号を累積和で決めておいて、UNIFORMで抽選番号を発行している join 4
17. 最近できるようになった手法 • generate_synthetic_data (https://docs.snowflake.com/en/sql-reference/stored-procedures/generate_synthetic_data) • 元テーブルを参考に合成データを作成する 2024/11/22 INPUT OUTPUT
18. 最近できるようになった手法 2024/11/22 • generate_synthetic_data (https://docs.snowflake.com/en/sql-reference/stored-procedures/generate_synthetic_data) • オプションパラメータを指定して、redactedになる列を操作したり、類似行を削除できたりする OUTPUT