PostgreSQLでテストデータを作成する

笠原辰仁

本記事は2013年のPostgreSQL Advent Calendar の 12/25 の記事です(地味なトピックになってしまいすいません)。PostgreSQLでのテストデータ作成に役立つ機能を紹介します。

はじめに

PostgreSQLを対象としたの性能検証や機能検証を行う際に、開発環境や試験環境でスキーマ(テーブルやインデックス)を作成し、ダミーのデータを投入してSQLのチェックを行うことが多々あるかと思います。単純な機能の正常試験であれば少量のデータ投入で事足りると思いますが、大量のデータに対する検索処理やバッチ処理を試す際は、それなりの量の試験データを生成し、DBに投入する必要があります。

通常、試験データは、例えば専用のジェネレータを作る、実際のデータをマスキングしたものを使う、サンプルとして存在するデータ(郵便番号のデータなど)を利用する、といったことが多いと思います。しかし、手元で簡易的に行う試験などでは、もう少し手軽な方法で試験データを生成したいことがあります。「わざわざデータジェネレータやデータ準備をしたくは無い」が「それなりにまっとうなデータが欲しい」、そんな時には、DB上でSQLや関数を使ってデータを生成することをお勧めします。理由としては

早い：組み込みの関数やSQLの処理は下手なジェネレータプログラムよりも高速
楽　：DB上でデータ生成をしてそのまま投入できるので、COPYなどの手間が無い
柔軟：色々なデータ型、関数、演算子が使えるので多種多様なデータ生成が可能

などが挙げられます。もちろん、非常に大規模であったり、事前に複雑な前処理が必要となる試験データを使う場合には専用にジェネレータを作成した方が良いのですが、一時的に、または簡易的にデータを生成したい場合には下記のテクニックを使ってみてください。

本記事では、目的別に、手軽にPostgreSQLでダミーデータを生成する機能・方法として役立つものをピックアップしてみました。基本的なものが多いですが、これらを組み合わせることで多彩なデータ生成が可能になります。

連番を生成する

まず、基本となる連番生成です。これは大量のデータを生成したい場合、あるいは連番(数値のシーケンス)を生成したい場合に有用です。使う関数はgenerate_seriesです。おそらく、使ったことがある方も多いでしょう。generare_seriesは(開始値, 終了値 [,ステップ値])の引数を取ります。1から1000までの連番が欲しければgenerate_series(1,1000)と実行します。1から1000までの奇数の連番が欲しければgenerate_series(1,1000,2)と実行します。

SELECT generate_series(1,1000);
 generate_series 
-----------------
               1
               2
               3
               4
(中略)
SELECT generate_series(1,1000,2);
 generate_series 
-----------------
               1
               3
               5
               7
               9
(中略)

generate_seriesはデータ生成時はもちろん、仮想的な連番や結合キーとしても使えるため、様々な場面で役に立つ関数です。

generate_seriesの注意点

何かと便利なgenerate_series関数ですが、基本的にいったんメモリ上に全データを生成します。この関数を用いて大量データを生成する場合、ローカルメモリの使用可能量に注意してください。もしあまりメモリに余裕がない場合は数万～数十万件毎に区切ってデータ生成を行うなどの工夫が必要です。さもないと、Out of memory や OOM Killer などのエラーが発生してしまいます。

ランダムな数値を生成する

ランダムな数値はrandom関数で手軽に生成することができます。random関数は0から1までのランダムな数値を出力するので、必要に応じて乗算や剰余を使うと良いです。

SELECT random();
      random       
-------------------
 0.248736051376909

-- 0から99までのランダム数値を得る
SELECT (random() * 10000)::int % 100;
 ?column? 
----------
       46

日付/タイムスタンプデータを生成する

テストデータでは日付やタイムスタンプを扱うことが多いです。日付やタイムスタンプを得る場合は、CURRENT_TIMESTAMPやCURRENT_DATEなどの現在の時刻を返す関数や、それらに対する任意のinterval(時間間隔)型を使うと便利です。intervalはPostgreSQL独自のデータ型で、CURRENT_TIMESTAMP + interval '1 day'などといった形で日付やタイムスタンプ間の差分(間隔)として利用することができます。

SELECT CURRENT_TIMESTAMP - interval '1 week 2 day';
           ?column?            
-------------------------------
 2013-12-09 00:17:25.938258+09

SELECT '2013-12-25'::date - interval '1 day';
      ?column?       
---------------------
 2013-12-24 00:00:00

このinterval型と先ほどのgenerare_seriesを組み合わせて、日付/タイムスタンプの連番を生成することができます。generate_seriesでは開始と終了にタイムスタンプ型、そしてステップにinterval型をセットできるので、日付連番なども手軽に生成できます。

-- 特定の日付開始から終了まで、１日デクリメントしたものを５日分
SELECT 
 generate_series('2013-12-31','2013-12-25', -'1 day'::interval)::date LIMIT 5;
 generate_series 
-----------------
 2013-12-31
 2013-12-30
 2013-12-29
 2013-12-28
 2013-12-27

また、以下の様に、generare_seriesをFROMで連番の集合体として使い、その数値をinterval型にキャストして現在の日付やタイムスタンプへの足し引きを行うこともできます。

-- CURRENT_DATEに1日分を5回インクリメント
SELECT CURRENT_DATE + CAST( i || 'days' AS interval) 
     FROM generate_series(1,5)s(i);
      ?column?       
---------------------
 2013-12-27 00:00:00
 2013-12-28 00:00:00
 2013-12-29 00:00:00
 2013-12-30 00:00:00
 2013-12-31 00:00:00

interval型は秒から年まで幅広い表現が可能なので、様々な日付/タイムスタンプ生成に役立ちます。

ランダムな文字列を生成する

ランダムな文字列を得るにはいくつかの方法があります。便利な方法を2つほど紹介します。

md5関数とclock_timestamp関数の組み合わせで英数字を得る

厳密にはランダムではありませんが、それに近しいデータを生成する方法です。md5関数は、文字列データを引数にとり、16進数のハッシュ値を返します。引数のデータが異なれば異なるハッシュ値が返ります。このハッシュ値は32文字の英数字となり、ランダムな英数字に近しい文字列として扱えます。さらに、引数に"実行時のタイムスタンプ"を返すclock_timestamp関数を与えることで、以下の様に英数字からなる文字列行を簡単に生成できます。

SELECT md5(clock_timestamp()::text) FROM generate_series(1,3);
               md5                
----------------------------------
 5b47cf7b49bd653f438e70db03bae506
 a1d74cf6dde6f9ba4b90f0b61b10a388
 d2c3ddd35e819f3db5a8db1dfc294024

chr関数とrandom関数の組み合わせで文字列を得る

もう一つの方法はchr関数とrandom関数の組み合わせです。chr関数は、文字データのコードポイントを10進数として引数に与えることで、そのコードポイントにあたる文字列を返す関数です。たとえば、UTF8エンコードの環境下で「あ」にあたるコードポイント(¥u3942 = 10進数で12354)をchr関数に与えると「あ」が返ります。random関数を組み合わせると、マルチバイト文字を含むランダムな文字列を生成できます。

-- ひらがなの「あ」を生成
SELECT chr(12354);
 chr 
-----
 あ
 
 -- 日本語のひらがなの範囲からランダムに一文字を抽出した行を3行生成する
 SELECT chr(12353 + (random() * 1000)::int % 83 ) FROM generate_series(1,3);
 chr 
-----
 を
 ぅ
 た

任意の長さの文字列を得る

さらに文字列を集約するstring_agg関数を使うと、任意の長さのハッシュ値やランダムな文字列の生成ができます。イメージとしては、複数行の文字(列)を何かのキーで集約し、それらを結合する形となります。string_agg関数は任意の文字をデリミタとして指定できますが、これは空文字にしてしまうこともできます。以下は、英数字とカタカナの文字列生成を行っている簡単な例です。

-- 64文字(md5関数2つ分の長さ)のハッシュ値からなる英数字の文字列を10行生成
SELECT i, string_agg(str, '') 
   FROM 
   (SELECT md5(clock_timestamp()::text) as str , i
     FROM  generate_series(1,2) length, generate_series(1,10) num(i)
   )t
   GROUP BY i;
  i |                            string_agg                            
----+------------------------------------------------------------------
  1 | 558f14d21e98d8f8e0c6d7861dec059f7f1a4311e6a02d368a7422a244765126
  2 | 9883faed5934ffe17d0c639aa672d4cb587c76d5d99571b8cfd1200980c728dd
  3 | 207f57e9463bb84010814312256a093c5c3be02dc8db79baf8223bbcbac02952
  4 | fb6b287c941a0bd03002e19581d33417e2e91d4bf838af34faa8d05f63c6d2ea
  5 | e8e9250bccc357a736dcb4a8bfead0c4553922a1e9ec86398e6a24c3dd5abab3
  6 | 743efbf60d97b7fd13b6289c02598ff30dcbc0d56584fe794bc0f47fff19bd63
  7 | ecf41345d3c7353bebc5901f84e11825d40492ca38ec581bfa41d6b87cd16e5f
  8 | 0cd30dcde06d4d87fb7b7536cbe30a1cbcd7c5e32da2633801f98d37426ba8e2
  9 | 4ff4eb2a56c50efb2fc80efd63e89b8ec6cd4523111cfd6c8eb1d6a095c342d8
 10 | 6b034ebf113f7763d6cffab479cfa0cefb041bc73155527eef58d2ca035d2b70

-- 20文字のランダムなカタカナ文字列を10行生成
SELECT i, string_agg(str, '') 
   FROM 
   (SELECT chr(12449 + (random() * 1000)::int % 85 ) as str , i 
     FROM  generate_series(1,20) length, generate_series(1,10) num(i)
   )t
   GROUP BY i;
  i |                string_agg                
----+------------------------------------------
  1 | グザカサケァソケピダデヴツソノゴヘトヨガ
  2 | ゥブビヒワェジパヤヂホボヌバパヲカウピコ
  3 | ミニポカバッエミテヒタダヮピガヌプテイヨ
  4 | ケエデンサリダヲロキゲャアチケシッポヵヲ
  5 | ヌゴガヴコシルソァエパホオテアジルミコト
  6 | ポィボニヘクドククゼヒピモヵロジヱヂヘピ
  7 | ハツミコケゾゲンポタハヌキャダゼェプヴヨ
  8 | ゲヘニトレパュギヴメヂナヮヒロゥケラゾス
  9 | ユウビムベヌゲヱゼエズワラセピマヵヴプパ
 10 | ドテマシァヂタァスユヰォヰイテラヴベヤモ

バイナリデータを投入する

最後に、データ生成ではありませんが、バイナリデータ投入の簡単な方法を紹介します。PostgreSQLではbytea型やラージオブジェクトを使ってバイナリデータを格納することが可能です。手元の画像やバイナリファイルをPostgreSQLに投入する場合、色々な方法がありますが、以下の関数を使うことで、DBサーバにあるバイナリファイルを手軽にINSERTできます。

pg_ls_dir関数とpg_read_binary_file関数

PostgreSQLには、DBクラスタ配下にある任意のディレクトリにあるファイルリストを取得する関数(pg_ls_dir)と、DBクラスタ配下にある任意のファイルをバイナリとして読み込む関数(pg_read_binary_file)があります。これらを組み合わせて、DBクラスタ配下に投入したいファイルを適当なディレクトリに入れておき、一括で投入することができます。pg_read_binary_file関数は、オフセットを指定できるので、任意のバイト数のバイナリデータ投入にも便利です。

--DBクラスタ配下の bins(適当に作成)にあるファイルを取得する
SELECT pg_ls_dir ('bins');
         pg_ls_dir          
----------------------------
 postgresql-9.2.6.tar.gz
 postgresql-9.3.2.tar.gz
 postgresql-snapshot.tar.gz

-- binsディレクトリにある上記3つのファイルから先頭1024バイト分をbyteaとして取り込み、テーブルへ投入
INSERT INTO bin_tbl(bin_obj)
 SELECT pg_read_binary_file('bins/'|| files, 0, 1024) 
   FROM (SELECT pg_ls_dir ('bins'))s(files);

なお、pg_read_binary_file関数は、逐次ファイルのopen/closeが発生するため、同じ内容のバイナリデータを大量に投入したい場合は、一旦適当なテーブルに投入し、それをSELECTした結果をINSERTで使うと良いでしょう。

最後に

いかがでしたでしょうか？PostgreSQL上だけでも、色々なデータ生成が可能です。今回は紹介していませんが、Window関数やWITH句、PostgreSQL9.3で強化されたJSON用の関数などを使うと、もっと多岐にわたるデータ生成が可能です。テーブルに投入するだけでなく、テキストファイルなどにCOPY TOすることで、別のアプリケーションのテストで使える電文やテキストデータ生成などにも活用できます。是非PostgreSQLをデータジェネレータとして使ってみてください。

(2013年12月25日公開)