この記事は Snowflake Advent Calendar 2020 の 3 日目です。

2021-03-22: RANDOM() および RANDSTR() 使用時の実行時間についての補足を追記しました。

こんにちは、Snowflake でサポートエンジニアをやっている @indigo13love です。

SQL (データベース機能) のエキスパートとして、カスタマーケースの対応はもちろん、バグや問題の再現とバグレポート、パフォーマンス改善のためのクエリの書き換え、サンプルデータやユースケースからのクエリのご提案などいろいろやっています。

データベース製品の技術サポートにおいて、ダミーデータの生成は、バグや問題の再現手段としてだけでなく、パフォーマンス検証やベンチマークなどにおいても、非常に有効な手段となります。

もちろんオープンなデータセットをインポートして使用したり、後述のサンプルデータベースを使用するのも一つの手ではありますが、再現したいバグやユースケースに合わせた柔軟なテストデータの生成はそれらではカバーできません。

この記事では、私が実際に日々のサポート業務で使用している、ダミーデータの生成ノウハウを共有したいと思います。

(念のためですが、本記事は所属する組織の公式見解ではなく、個人的なプラクティスの共有になります。)

サンプルデータベースを使用する

ダミーデータの生成について話す前に、Snowflake が提供しているサンプルデータベースについて少しだけ触れたいと思います。

サンプルデータベースは SNOWFLAKE_SAMPLE_DATA という名前のデータベースとして、Snowflake からすべてのアカウントに Data Sharing を使用して、デフォルトで読み取り専用として共有されています。

SNOWFLAKE_SAMPLE_DATA には TPC-DS および TPC-H のロード済みデータが格納されており、TPC-DS については 10 TB から 100 TB 規模の、TPC-H については数百万行規模から最大で数十億行程度のデータセットまで用意されています。

簡単なテスト、データの内容に依存しないテスト、更新を必要としないテストなどであれば、このサンプルデータベースが事足りるかもしれません。

GENERATOR でダミーデータを生成する

もし、任意のデータ型、行数、テーブル定義などに依存するテストを実行したい場合や、データの更新が必要なテストを実行したい場合、サンプルデータベースでは実現できないため、自分でダミーデータを生成する必要があります。

Snowflake では、いくつかのデータ生成関数を用意しており、特に GENERATOR というテーブル関数が重要になります。

GENERATOR は、SELECT 文のカラムリストにしたがって、テーブルとしてクエリできる特定の行数のデータセットを生成します。…といっても、ピンとこないと思うので、下記のクエリを見ていただくのがいいかもしれません。

select 1 from table(generator(rowcount => 10000));

上記のクエリは 1 という値を格納した 1 列の行を 10,000 行生成します。

例えば、これを CTAS (CREATE TABLE AS SELECT) と組み合わせることで、任意のデータセットを格納したテーブルを生成できます。

create or replace table t1 (c1 int) as
select 1 from table(generator(rowcount => 10000));

上記のクエリでは、1 という値を c1 列に格納した行を 10,000 行持っているテーブル t1 を作成しています。

これを、その他のデータ生成関数と組み合わせることで、さまざまなダミーデータを柔軟に作成することができます。ここからいくつか例を紹介したいと思います。

連続する数列

連続する数列を生成するためには、SEQ4 関数を使用します。

SEQ1・SEQ2・SEQ4・SEQ8 の違いは生成される数値のバイト長で(それぞれ 1・2・4・8 バイト)、私は通常 SEQ4 を使用しています。

使い方は簡単で、 SEQ4() を GENERATOR と組み合わせるだけです。

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

これを使用することで、簡単に連番データの生成をすることができます。

ただし、注意点として、これらの関数は単調増加であることは保証されていますが、ギャップがないこと、つまり常に必ず連番であることが保証されていません。

SEQ4 が連番でなくなる最も典型的な例が、並列処理です。Snowflake では XSMALL 以外の仮想ウェアハウスを使用した場合、並列化できる部分は複数のサーバで並列処理されます。

この場合、各サーバがそれぞれ SEQ4 の実行、つまり連番の生成を行いますが、このケースでは多くの場合、SEQ4 はギャップのある(連番でない)データを生成します。

もし、ダミーデータが必ず連番である必要があり、かつ巨大なデータセットが必要なために大きい仮想ウェアハウスを使用しなければならない場合、ROW_NUMBER ウィンドウ関数を使用することで、確実に連番であることが保証された数列を生成することができます。

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

一点、注意点として、SEQ4 は 0 から始まり、ROW_NUMBER は 1 から始まるため、書き換える際は結果を 1 ズラす必要があります。

ランダムな数列

ダミーデータの数列が連番ではなくランダムであってほしい、つまり乱数列が欲しい場合もあると思います。

その場合、そのものズバリの RANDOM 関数が使用できます。RANDOM はメルセンヌ・ツイスタで生成された 64-bit 符号付き整数を返します。

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

4642114377103456917
-7725011619722890724
2691795399373416883
-6088696439675459443
7782892412181036179

乱数列を一定の範囲に収めたい、例えば 0 から 100 の乱数を生成したい、という場合、いくつかの方法があります。

まずシンプルに ABS で絶対値を取ったり、% で剰余を取ったりする方法です。例えば下記の例では 0 から 99 までの正の乱数のみが生成されます。

select abs(random())%100 from table(generator(rowcount => 5));

また、もうひとつの方法として、ある分布に沿って乱数を生成する関数を使用することもできます。

Snowflake では UNIFORM (一様分布)、NORMAL (正規分布)、ZIPF (Zipf 分布) の 3 種類を用意しています。

試しに Snowsight でチャートにしてみると、サンプル数が少ないのではっきりとはしていませんが、それぞれなんとなく各分布にしたがっているのがわかります。

select uniform(0, 1000, random())
from table(generator(rowcount => 10000));

select round(normal(5000, 1000, random()))
from table(generator(rowcount => 10000));

select zipf(1.1, 10000, random())
from table(generator(rowcount => 10000));

NORMAL や ZIPF はあまり使うこともないかもしれませんが、UNIFORM は ABS と % を使う例よりも直感的に値域の指定ができたり、浮動小数点数で乱数列を得たい場合にも便利なので、覚えておくと使える局面があるかもしれません。

<最小> および <最大>、のいずれか、または両方が浮動小数点数である場合、生成される数値は浮動小数点数です。 <最小> と <最大> の両方が整数の場合、生成される数値も整数になります。

一点、RANDOM はメルセンヌ・ツイスタの性質上、生成にメモリ空間を使用するため、大量行を生成する場合には多くのリソースを使用する可能性があるのでご注意ください。

2021-03-22 追記ここから:

また、CPU リソースについても数億行に対して全行 RANDOM() を実行することになると、行数分の処理を実行する必要が発生してしまうため、実行に長時間かかってしまう可能性があります。

私の手元で実行した結果ではありますが、10 億行で RANDOM() を実行した場合、4XLARGE (128 servers) の仮想ウェアハウスで 7-8 秒ほどかかりました。

より小さい仮想ウェアハウスサイズだと、より時間がかかる結果になるかと思います(線形に増加するわけではありません)。

create or replace transient table t1 (c1 double) as
select random() from table(generator(rowcount => 1024*1024*1024));
-- 1st: 7.68 sec
-- 2nd: 7.56 sec
-- 3rd: 7.67 sec

2021-03-22 追記ここまで:

ランダムな文字列

数値だけでなく文字列が欲しい場合もあるかもしれません。

ランダム文字列生成は多くのデータベースで hacky な方法になりがちですが、Snowflake では RANDSTR 関数でシンプルに任意の長さの英数字からなる文字列を生成できます。

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

1wr2T8sJbs
HgZWZ1p2U8
ZBD2DN6Fwq
WDdvEEPq7Y
SKNUTWYMNe

RANDSTR では最大 16,777,216 文字 (16 MiB) までの文字列を生成できますが、大きな文字列の生成はかなり時間がかかる処理になるため、長さだけが必要なケースでは REPEAT と併用すると、高速に巨大な文字列を生成できます。


select randstr(16*1024*1024, random())
from table(generator(rowcount => 5));
-- 5.68s

select repeat(randstr(16, random()), 1024*1024)
from table(generator(rowcount => 5));
-- 809ms

また、特に文字列の長さやフォーマットにこだわりがない場合、よりシンプルな方法として UUID_STRING を使用して、UUID を生成する方法もあります。

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

0a2413aa-64dc-4f79-b58d-d87dc83a546d
7bee6242-549f-45bb-b2f1-9d03f3f11480
0b727a69-1cc2-414a-bcc7-0d80ca100357
2dc1914b-5daf-48ff-b48f-0faee17fdcef
b63ca562-70d9-4e8e-9fa1-8e1572a78442

UUID_STRING も RANDOM と同じくメルセンヌ・ツイスタで生成されているため、乱数としての品質にも問題ありません。

2021-03-22 追記ここから:

RANDSTR() についても RANDOM() と同様に、行数分の処理を実行する必要が発生した場合、実行に長時間かかってしまう可能性があります。

私の手元で実行した結果ではありますが、10 億行に対し RANDSTR() で 100 bytes の文字列を生成した場合、4XLARGE (128 servers) の仮想ウェアハウスで 18-19 秒ほどかかりました。

より小さい仮想ウェアハウスサイズだと、より時間がかかる結果になるかと思います(線形に増加するわけではありません)。

create or replace transient table t1 (c1 varchar) as
select randstr(100, random()) from table(generator(rowcount => 1024*1024*1024));
-- 1st: 18.36 sec
-- 2nd: 18.41 sec
-- 3rd: 18.39 sec

2021-03-22 追記ここまで:

日付/時刻

データ分析ワークロードの多くのケースで、日時や時間枠に基づいた分析を行ったり、データがタイムスタンプを持っていたりするかと思います。

日付や時刻のダミーデータ生成は、基本的に「連続する数列」や「ランダムな数列」の応用です。DATEADD 関数を SEQ4 や RANDOM、そして CURRENT_TIMESTAMP や CURRENT_DATE と組み合わせることで任意の日時を生成します。

例 1: 2020-01-01 から連続する 5 日

select dateadd(day, seq4(), '2020-01-01'::date)
from table(generator(rowcount => 5));

例 2: 2020-01-01 00:00:00 から連続する 5 時間 (ギャップなし)

select dateadd(hour,
               row_number() over (order by true) - 1,
               '2020-01-01T00:00:00Z'::timestamp_ntz)
from table(generator(rowcount => 5));

2020-01-01 00:00:00.000
2020-01-01 01:00:00.000
2020-01-01 02:00:00.000
2020-01-01 03:00:00.000
2020-01-01 04:00:00.000

例 3: 今日から前後 10,000 日以内のランダムな日付

select dateadd(day, random()%10000, current_date)
from table(generator(rowcount => 5));

少々わかりにくいですが、DATEADD は日付 (DATE) だけでなくタイムスタンプにも使用でき、第１引数 (加算する単位) と第 3 引数 (元の値) が日付なら DATE 型を、それ以外のケースでは TIMESTAMP 型^[1]を返します。

もちろん、ここからさらに LAST_DAY で月末日に変換したり、DATE_TRUNC や DATE_PART で部分的に抽出したりすることもできます。

また、(特にメリットはないですが)もうひとつの方法として、UNIX time を生成して、それをタイムスタンプ型に変換する方法もあります。

select to_timestamp_ntz(uniform(0, 1700000000, random()), 0)
from table(generator(rowcount => 5));

2003-05-11 05:55:40.000
2018-01-10 10:43:49.000
2014-12-14 18:29:16.000
1995-11-25 18:22:19.000
2014-05-03 02:48:09.000

半構造化データ / 配列

Snowflake では、データロードのソースとして JSON, Avro, ORC, Parquet, XML をサポートしており、また VARIANT 型にそのまま各フォーマットのオブジェクトを格納することができます。それらのオブジェクトは、あたかも JSON オブジェクトであるかのように、関数や : 演算子を使って SQL から直接操作することができます。

すなわち、半構造化データのダミーデータを作るというのは、GENERATOR でランダムな JSON を生成しよう、という話になります。

JSON の生成には OBJECT_CONSTRUCT 関数を使用します。OBJECT_CONSTRUCT は key と value を交互に引数として渡すことで JSON に変換します。したがって、OBJECT_CONSTRUCT と今までのテクニックを組み合わせることで、ランダムな value を持った JSON 列を生成できます。

select object_construct(
    'id', seq4(),
    'name', randstr(5, random()),
    'created_on', dateadd(day, seq4(), current_date)
)
from table(generator(rowcount => 5));

{ "created_on": "2020-12-01", "id": 0, "name": "oNJZy" }
{ "created_on": "2020-12-02", "id": 1, "name": "BJMXr" }
{ "created_on": "2020-12-03", "id": 2, "name": "FlTsl" }
{ "created_on": "2020-12-04", "id": 3, "name": "P6HBA" }
{ "created_on": "2020-12-05", "id": 4, "name": "6Bq2c" }

また、ARRAY 型の配列も同様に ARRAY_CONSTRUCT で生成することができます。

select array_construct(random(), random(), random())
from table(generator(rowcount => 5));

[ 8680970101044244136, 4283106811340900843, 2160590676128017690 ]
[ 8329080864161423560, -5590322697473072830, 4891845112381891499 ]
[ 1415781874385804898, 8737604051502932556, 4146994137723065153 ]
[ -5985832832624457399, -5796670710760659220, -6670610383755230096 ]
[ 8017007492269647876, -2386473292964359183, -7227676377378775435 ]

ただ、この方法だと長めの配列が欲しいときに配列の要素を大量に列挙しなければいけないため、あまりうれしくありません。

そこで、少々トリッキーな方法ですが、GENERATOR で普通にランダムな列を生成しつつ、それを SEQ4 で GROUP BY して ARRAY_AGG で集約することで、関数呼び出しの大量列挙を避けつつ、ROWCOUNT と % の数値を調節することで、任意の要素数の配列を任意の行数得ることができます。

select array_agg(randstr(5, random()))
from table(generator(rowcount => 5*10))
group by seq4() % 5;

[ "pBaob", "nJTr4", "oMZXc", "2VfYG", "UxVrf", "HYvNA", "NoOXH", "rZjuW", "QgxL6", "MocOj" ]
[ "nuO5V", "bXzCx", "DoqoR", "ToHIU", "WCR4W", "swsj7", "P37WO", "2zCz3", "UyhUQ", "d3Vcb" ]
[ "3cr1X", "wwUWL", "hzSBK", "ACzte", "FulSY", "Hoz7P", "zshLl", "mIBEg", "VT35g", "iGV7y" ]
[ "TroZK", "aVwHK", "mGh0k", "Ws2wu", "6l24r", "KMnN9", "g2SH1", "GIQl6", "rqfvF", "aaYT0" ]
[ "MLhKh", "BKvsI", "bF0vv", "cgXkk", "CfNmo", "b8ZtO", "RXzvS", "R3267", "2u7JL", "43UZo" ]

地理空間データ

Snowflake では GEOGRAPHY 型をサポートしており、OpenGIS 規格に沿った形で地理空間データをサポートしています。

複雑な Polygon の生成は難しい^[2]ですが、Point や Line、三角形の Polygon 程度であれば UNIFORM を ST_MAKEPOINT や ST_MAKELINE と組み合わせることで生成できます。

例 1: Point の生成

select st_makepoint(
    uniform(-180::double, 180::double, random()),
    uniform(-90::double, 90::double, random())
)
from table(generator(rowcount => 5));

POINT(-16.946529937 -44.258352856)
POINT(12.814917263 -26.215177968)
POINT(-49.151588047 -56.807603018)
POINT(34.187797174 -40.811209291)
POINT(7.54177329 -78.017244151)

例 1: Line の生成

select st_makeline(
    st_makepoint(
        uniform(-180::double, 180::double, random()),
        uniform(-90::double, 90::double, random())
    ),
    st_makepoint(
        uniform(-180::double, 180::double, random()),
        uniform(-90::double, 90::double, random())
    )
)
from table(generator(rowcount => 5));

LINESTRING(-89.864733313 -14.159250384,-117.380104394 20.00561626)
LINESTRING(169.866981397 -48.577885967,91.575365834 -37.963195212)
LINESTRING(-2.419908815 -49.839891839,-41.172153204 49.744190409)
LINESTRING(52.317779399 -35.764351365,-33.979916908 30.451721099)
LINESTRING(-107.604728439 34.128998264,-93.281766583 -80.221939242)

例 1: Polygon (三角形) の生成

with points as (
    select
        st_makepoint(
            uniform(-180::double, 180::double, random()),
            uniform(-90::double, 90::double, random())
        ) p1,  
        st_makepoint(
            uniform(-180::double, 180::double, random()),
            uniform(-90::double, 90::double, random())
        ) p2,  
        st_makepoint(
            uniform(-180::double, 180::double, random()),
            uniform(-90::double, 90::double, random())
        ) p3,
        p1 p4
    from table(generator(rowcount => 5))
)
select
    st_makepolygon(st_makeline(st_makeline(st_makeline(p1, p2), p3), p4)) triangle
from points;

POLYGON((-149.104559816 64.629630964,-44.688332 -4.306041059,-24.03110117 59.078712769,-149.104559816 64.629630964))
POLYGON((-27.741557103 26.573670018,-117.8420856 20.228614702,-109.999327983 84.757515837,-27.741557103 26.573670018))
POLYGON((140.717711834 40.163761228,-103.724276934 -40.434937411,-178.602834394 35.365922968,140.717711834 40.163761228))
POLYGON((54.696919975 42.65943683,90.261418239 68.794342402,-144.256231762 87.359614285,54.696919975 42.65943683))
POLYGON((-150.224319861 59.758189591,101.626680151 -67.101897127,74.634496871 72.222139679,-150.224319861 59.758189591))

より複雑なポリゴンや地理的に意味のあるデータがほしい場合には、geojson.io のようなサービスで生成するか、オープンデータとして公開されているデータセットを使用するのがいいでしょう。

例えば US State Boundaries のような Shapefile で公開されているデータであれば、ogr2ogr のようなツールを使って GeoJSON に変換することで、簡単にロードすることができます。

$ unzip tl_2017_us_state.zip
$ cd tl_2017_us_state
$ ogr2ogr -f geojsonseq tl_2017_us_state.json tl_2017_us_state.shp
$ snowsql

create or replace stage usb file_format = (type = json);

put file:///path/to/tl_2017_us_state/tl_2017_us_state.json @usb;

create or replace table usb (geo geography) as
select to_geography($1) from @usb;

ogr2ogr の GeoJSONSeq フォーマットは FeatureCollection を Feature ごとの NDJSON (New-line Delimited JSON) として処理(出力)してくれるので、NDJSON を前提として Snowflake の JSON サポートとの噛み合わせがよく、加工なしでそのまま取り込むことができます。

アンロードでダミーファイルを生成する

COPY や Snowpipe、External Table などのテストをする場合、テストデータとして、何らかのデータが入ったダミーファイルが必要になるときがあります。

CSV や JSON のようなテキストフォーマットであれば、ある程度力技でもなんとかなりますが、Parquet のようなバイナリフォーマットで生成するのはわりと手間がかかります。

そんなときは COPY INTO (location) を使ってテーブル上に生成したダミーデータを任意のフォーマットでアンロードすると、簡単にダミーファイル (CSV, JSON, Parquet) を生成することができます。

今回は S3 を例にとって解説していきます。

1. Storage Integration を作成する

まず、Snowflake と S3 を接続するための認証設定として Storage Integration を作成します。

下記のドキュメントに沿って、IAM Role のセットアップと Storage Integration の作成をします。

この Storage Integration は今後も使い回せるので、一度作っておくと後々便利です。

2. External Stage を作成する

1 で作成した Storage Integration を使って External Stage を作ります。

create or replace stage ext_stage
url='s3://bucket/path/to/' storage_integration = s3_int;

これでデータを S3 にアンロードする準備が整いました。

3. 各フォーマットでアンロードしてみる

まずは適当にダミーデータを準備します。

create or replace table t1 (c1 int, c2 varchar) as
select random(), randstr(5, random())
from table(generator(rowcount => 5));

-3575117791276232213    7caLB
-5004731039917445916    iNfPc
4732933873610976325     xl4HV
-4374258347654189769    tK2lY
-5291271558552445042    k42RA

このダミーデータをいろいろなフォーマットでアンロードしてみましょう。

例 1: CSV

copy into @ext_stage from t1
file_format = (type = csv) single = true;

$ gzcat data
-3575117791276232213,7caLB
-5004731039917445916,iNfPc
4732933873610976325,xl4HV
-4374258347654189769,tK2lY
-5291271558552445042,k42RA

例 2: JSON

copy into @ext_stage
from (select object_construct('c1', c1, 'c2', c2) from t1)
file_format = (type = json) single = true;

$ gzcat data
{"c1":-3575117791276232213,"c2":"7caLB"}
{"c1":-5004731039917445916,"c2":"iNfPc"}
{"c1":4732933873610976325,"c2":"xl4HV"}
{"c1":-4374258347654189769,"c2":"tK2lY"}
{"c1":-5291271558552445042,"c2":"k42RA"}

例 3: Parquet

copy into @ext_stage from t1
file_format = (type = parquet) single = true;

$ parquet-tools meta data
file:        file:/path/to/data
creator:     parquet-cpp version 1.5.1-SNAPSHOT

file schema: schema
--------------------------------------------------------------------------------
_COL_0:      OPTIONAL INT64 O:DECIMAL R:0 D:1
_COL_1:      OPTIONAL BINARY O:UTF8 R:0 D:1

row group 1: RC:5 TS:236 OFFSET:4
--------------------------------------------------------------------------------
_COL_0:       INT64 SNAPPY DO:4 FPO:60 SZ:130/126/0.97 VC:5 ENC:RLE,PLAIN,PLAIN_DICTIONARY ST:[min: -5291271558552445042, max: 4732933873610976325, num_nulls: 0]
_COL_1:       BINARY SNAPPY DO:230 FPO:288 SZ:106/105/0.99 VC:5 ENC:RLE,PLAIN,PLAIN_DICTIONARY ST:[min: 7caLB, max: xl4HV, num_nulls: 0]

$ parquet-tools head data
_COL_0 = -3575117791276232213
_COL_1 = 7caLB

_COL_0 = -5004731039917445916
_COL_1 = iNfPc

_COL_0 = 4732933873610976325
_COL_1 = xl4HV

_COL_0 = -4374258347654189769
_COL_1 = tK2lY

_COL_0 = -5291271558552445042
_COL_1 = k42RA

ダミーデータがちゃんとそれぞれのファイルに入っていることがわかります。

今回は SINGLE = true を指定して単一ファイルにアンロードされるようにしていますが、デフォルトでは自動的に複数のファイルに分割してアンロードされます。

SINGLE = true の副作用でファイル名から拡張子がなくなっていますが、通常はちゃんと圧縮形式とデータフォーマットを含む拡張子が付与されます。

また、アンロードされたファイルはデフォルトで圧縮されており、Parquet の場合は Snappy、CSV/JSON の場合は GZIP が自動的に使用されます。この圧縮形式は COMPRESSION オプションで変更することが可能です。

また、SINGLE オプションが無効の場合、MAX_FILE_SIZE オプションを使用することで、分割数をある程度^[3]コントロールすることができます。

まとめ

GENERATOR は他のデータベース製品にはあまり見られない強力なデータ生成機能です。

GENERATOR を使えば、INSERT INTO t1 SELECT * FROM t1; を連打して倍々にしていったり、UNION ALL を大量に並べてテーブルを再現したりするような非効率かつ時間のかかるダミーデータ生成や、外部で CSV を生成するなどの Snowflake 外での作業をする必要がなくなるので、ぜひ有効活用していただきたいです。

ちなみに GENERATOR は Snowflake に入社して 2 番目に感動した SQL 文で、1 番は SELECT ... FROM ... {AT|BEFORE} です。

脚注

Snowflake では TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ の 3 種類のタイムスタンプ型があり、TIMESTAMP は TIMESTAMP_TYPE_MAPPING パラメータにしたがって、いずれかにマッピングされます(デフォルトは TIMESTAMP_NTZ) ↩︎
OpenGIS の規格上、Polygon のエッジは交差してはいけない(実際エラーになる)ので、ただ Point を大量に生成すればいいのではなく、交差しないような点を生成しなければいけません。 ↩︎
アンロード時の並列数(スレッド数)は Snowflake が自動的にコントロールするため、指定することができず、また MAX_FILE_SIZE はあくまで最大値なので、それよりも大幅に小さいファイルが生成されることもあります。 ↩︎

Discussion