【Snowflake】Snowflake ~ テストデータ作成 / generator ~

■ はじめに

 Snowflake の generator() ってメソッドで、
テストデータを簡単に作れるって聞いたので調べてみた。
 調べてみて generator() だけでなく、
他のメソッドと組み合わせる必要があるので、それらもまとめてみる。

 Snowflakeに関して、調べてみたも日本語の情報は
通常皆無なんだが、今回は2件ほど記事(以下のサイト)が見つかった。
とてもいいことだと思った、、、

参考文献
https://dev.classmethod.jp/articles/snowflake-generator/
https://zenn.dev/indigo13love/articles/6c3a3b234e5279

目次

【1】基本
 1)GENERATOR関数
 2)TABLEリテラル
【2】シーケンス
 1)SEQ1 / SEQ2 / SEQ4 / SEQ8関数
 2)ROW_NUMBER関数
【3】乱数・ランダム文字列
 1)UNIFORM関数
 2)RANDOM関数
 3)RANDSTR関数
【4】半構造化データ
 1)OBJECT_CONSTRUCT
【5】サンプル
 例1)テストデータ作成
 例2)既存テーブルにINSERTしてテストデータ追加
 例3)アンロードを利用してテストデータファイル作成

【1】基本

1)GENERATOR関数

https://docs.snowflake.com/ja/sql-reference/functions/generator.html

より抜粋
~~~~~
指定された行数、指定された生成期間(秒単位)、
またはその両方に基づいてデータの行を作成します。
~~~~~

構文

GENERATOR( ROWCOUNT => <count> [ , TIMELIMIT => <sec> ] )

GENERATOR( [ TIMELIMIT => <sec> ] )

--  1 を格納したの行を 10 行生成
SELECT 1 FROM TABLE(GENERATOR(ROWCOUNT => 10));
-- TABLE については、後述「2)TABLEリテラル」を参照

-- テーブル「demo_table」にデータを入れる例
create or replace table demo_table (item1 int) as
select 1 from table(generator(rowcount => 10));

2)TABLEリテラル

* GENERATOR関数は、FROM句の後にTABLEリテラルと併せて使う
 => とりあえず、決まり事だと思ってよさそう
* TABLEリテラルの詳細は、以下の公式ドキュメントを参照。

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

【2】シーケンス

1)SEQ1 / SEQ2 / SEQ4 / SEQ8関数

* 整数値をラウンドアップで返す

https://docs.snowflake.com/ja/sql-reference/functions/seq1.html

select seq8() from table(generator(rowcount => 5));

+--------+
| SEQ8() |
|--------|
|      0 |
|      1 |
|      2 |
|      3 |
|      4 |
+--------+
# 関数名 説明
1 SEQ1 1バイト(-128~127)の値を返す
2 SEQ2 2バイト(-32768~32767)の値を返す
3 SEQ4 4バイト(-2147483648~2147483647)の値を返す
4 SEQ8 8バイト(-9223372036854775807~9223372036854775807)の値を返す

構文

-- 0(デフォルト) : シーケンスはラップアラウンド後も0で継続
-- 1: 表現可能な最小の数で継続(SEQ1の場合、127になったら、-128,-127,...)
SEQ1( [0|1] )

2)ROW_NUMBER関数

使用上の注意
https://docs.snowflake.com/ja/sql-reference/functions/seq1.html

より抜粋
~~~~~~~
必ずしもギャップのないシーケンスを生成するわけではありません。
大量のデータを処理する場合、シーケンスにギャップが生じることがあります。
完全に順序付けられたギャップのないシーケンスが必要な場合は、 
ROW_NUMBER ウィンドウ関数の使用を検討してください。
~~~~~~~

select row_number() over (order by seq4()) 
    from table(generator(rowcount => 5));

【3】乱数・ランダム文字列

1)UNIFORM関数

* 指定された範囲[第1引数:最小、第2引数: 最大]で一様に乱数を返す

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

-- 第1引数/第2引数で指定した値の間 (from-to)
-- 第3引数に指定した内容に基づき、乱数を生成
UNIFORM( <min> , <max> , <gen> )

select uniform(1, 10, random()) from table(generator(rowcount => 5));

--------------------------+
 uniform(1, 10, random()) |
--------------------------+
 6                        |
 4                        |
 7                        |
 9                        |
 4                        |
--------------------------+

2)RANDOM関数

* ランダム関数(そのまんま)
 => 64ビット整数を返す

https://docs.snowflake.com/ja/sql-reference/functions/random.html

3)RANDSTR関数

* 任意の長さの文字列を生成

https://docs.snowflake.com/ja/sql-reference/functions/randstr.html

-- RANDSTR( <length> , <gen> )
-- <length>: 文字列数(今回は、「3」)
select randstr(3, random())
from table(generator(rowcount => 5));

--------------------------+
 randstr(3, random())      |
--------------------------+
 jrE                      |
 jTS                        |
 MCi                        |
 dKS                        |
 Dsj                        |
--------------------------+

【4】半構造化データ

https://zenn.dev/indigo13love/articles/6c3a3b234e5279

1)OBJECT_CONSTRUCT

* 引数から構築された OBJECT を返す
 => JSONファイルを作成する際に使用

https://docs.snowflake.com/ja/sql-reference/functions/object_construct.html

【5】サンプル

例1)テストデータ作成

-- 後は、上述の関数を組み合わせて、自分好みにするだけ
select seq4(), uniform(1, 10, random(12)),  randstr(5, random())
  from table(generator(timelimit => 1)) v;

-- 1sec 秒間実行され、時間枠内で可能な限り多くの行が生成されます。
-- => 結構、作成されるので要調節。

https://docs.snowflake.com/ja/sql-reference/functions/generator.html#usage-notes

例2)既存テーブルにINSERTしてテストデータ追加

insert into sample_table (
  no,
  id,
  name
)
select seq4(), uniform(1, 10, random(12)),  randstr(5, random())
  from table(generator(rowcount => 10));

例3)アンロードを利用してテストデータファイル作成

https://zenn.dev/indigo13love/articles/6c3a3b234e5279#%E3%82%A2%E3%83%B3%E3%83%AD%E3%83%BC%E3%83%89%E3%81%A7%E3%83%80%E3%83%9F%E3%83%BC%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92%E7%94%9F%E6%88%90%E3%81%99%E3%82%8B

が参考になる

アンロードとは?
https://dk521123.hatenablog.com/entry/2022/07/04/172738

より抜粋

~~~~~~~~~
Snowflakeテーブルからデータをアンロード(つまり、エクスポート)
~~~~~~~~~
 => つまり、Snowflakeテーブルからデータをファイル出力すること

例:AWS S3へのダミーファイル出力

-- Step1: 外部ステージを作成する
-- https://docs.snowflake.com/ja/user-guide/data-unload-s3.html#unloading-data-into-an-external-stage  
create or replace stage ext_demo_stage
  url='s3://your-bucket/path/to/'
  storage_integration = s3_int;

-- Step2: ダミーデータ用テーブルを用意
create or replace table demo_dummy_table (
  id int,
  name varchar
) as
select random(), randstr(5, random()), 
from table(generator(rowcount => 5));

-- Step3: アンロードする

-- 3-1) CSVの場合
copy into @ext_demo_stage/output.csv from demo_dummy_table
  file_format = (type = csv)
  single = true;

-- 3-2) JSONの場合
copy into @ext_demo_stage/output.json 
from (select object_construct('id', id, 'name', name) from demo_dummy_table)
  file_format = (type = json)
  single = true;

-- 3-3) Parquetの場合
copy into @ext_demo_stage/output.parquet  from demo_dummy_table
  file_format = (type = parquet)
  single = true;

https://docs.snowflake.com/ja/user-guide/data-unload-considerations.html

補足:COPY INTO
https://docs.snowflake.com/ja/sql-reference/sql/copy-into-location.html

より
~~~~~~~~~~~~~~~~~~
SINGLE = TRUE | FALSE

定義
単一のファイルを生成する(TRUE)か
複数のファイルを生成する(FALSE)かを指定するブール値。 
FALSE の場合、ファイル名プレフィックスを パス に含める必要があります。
~~~~~~~~~~~~~~~~~~

参考文献

https://dev.classmethod.jp/articles/snowflake-generator/
https://zenn.dev/indigo13love/articles/6c3a3b234e5279

関連記事

Snowflake ~ 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ~ 入門編 / Hello world
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ~ 基本編 / データロード ~
https://dk521123.hatenablog.com/entry/2021/11/15/221245
Snowflake ~ データ アンロード ~
https://dk521123.hatenablog.com/entry/2022/07/04/172738
Snowflake ~ ストレージ統合 ~
https://dk521123.hatenablog.com/entry/2022/06/29/221037