LoginSignup
21
18

More than 5 years have passed since last update.

@tuki(Tuki)inAmazon Web Services / プロフェッショナルサービス

【DB2】 SQL一発で100万件のテストデータを生成＆投入する

Last updated at Posted at 2016-11-15

はじめに

DB2を使っているときに、「内容は問わない（キーとなる列のIDだけ付いていればよい）から大量のレコードを素早くデータベースに投入したい」ときのTipsです。

TPC-CやTPC-Hのデータを使う手もありますが、dbgenのようなデータ生成プログラムをダウンロードしてコンパイルするのも手間がかかります。今回のやり方は、数分で簡単なデータを生成する方法として重宝しています。

手順

共通表式を使って任意の数のレコードをSELECT結果として取得する

SQLの規格には「共通表式」という、SQLの表現力を強化する上で大変便利な機能があります。その共通表式を応用して、指定したレコード数を返却するように書くことができます。

たとえば下のSQLでは、"with"句で"temp"という共通表を定義しており、"as"以降の括弧の中で列をひとつだけ持つ表を記述しています。

select.sql
with temp(a) as ( values(1) union all select a+1 from temp where a<5)
select 
    a, 
    current timestamp
from temp

このSQLをDB2に投入してみると、以下のように5件のレコードが返却されます。SQLの"with"句に含まれる"where a<5"で指定した回数分だけ"union all"で再帰的に参照してレコード数を増幅するような表現になっています。

$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 10.5.6
 SQL authorization ID   = DB2V105
 Local database alias   = SAMPLE

$ db2 "with temp(a) as ( values(1) union all select a+1 from temp where a<5)
> select
>     a,
>     current timestamp
> from temp"

A           2
----------- --------------------------
          1 2016-11-15-18.00.11.489419
          2 2016-11-15-18.00.11.489419
          3 2016-11-15-18.00.11.489419
          4 2016-11-15-18.00.11.489419
          5 2016-11-15-18.00.11.489419

  5 record(s) selected.

SELECTから得られた結果を直接データベースに投入する（少量）

件数が少ないうちはSELECTの結果をそのままINSERT文に投入する"INSERT FROM SELECT"が使えます。前述したSELECTをINSERTの入力にする場合は、以下のように書きます。

insert_from_select.sql
connect to sample;

create table t1 (c1 int,c2 timestamp);

insert into t1
with temp(a) as ( values(1) union all select a+1 from temp where a<5)
select 
    a, 
    current timestamp
from temp;

select count(*) from t1;

INSERT INTO <表> <SELECT文>とINSERTの後ろにSELECTをそのまま続けて書くだけです。
この例では、以下のような流れで処理をしています。
　- データベースへ接続
　- INSERT対象の表を作成
　- INSERTを実行
　- 件数をカウント

ファイルでSQLを与える場合、ステートメントの終わりには";"で、区切りを表すのがDB2の（デフォルトの）お作法なので、省略しないように注意してください。

$ db2 -tvf insert_from_select.sql
connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 10.5.6
 SQL authorization ID   = DB2V105
 Local database alias   = SAMPLE

create table t1 (c1 int,c2 timestamp)
DB20000I  The SQL command completed successfully.

insert into t1 with temp(a) as ( values(1) union all select a+1 from temp where a<5) select a, current timestamp from temp
DB20000I  The SQL command completed successfully.

select count(*) from t1

1
-----------
          5

  1 record(s) selected.

SELECTから得られた結果を直接データベースに投入する（大量）

タイトルのように大量のレコードをまとめて投入したい場合は、SQLによるINSERTでは速度も遅くデータベースのトランザクション・ログがパンクする危険もあります。
そんなときに役立つのが、SELECT結果をLOADユーティリティに直接投入するテクニックです。

load_from_cursor.sql
connect to sample;

create table t2 (c1 int,c2 int,c3 char(20), c4 timestamp, c5 char(100));

declare c1 cursor for
with temp(a) as ( values(1) union all select a+1 from temp where a<1000000)
select 
    a, 
    cast(rand() * 1000000 as int),  
    cast(rand() * 1000000 as char(20)),
    current timestamp,
    repeat('char string ',8)
from temp;

load from c1 of cursor replace into t2;

select count(*) from t2;

こんどの例ではdeclare c1 cursorというステートメントが登場しました。これは、"カーソル"を宣言するためのステートメントです。カーソルの詳細についてはWikipediaなどを見ていただくとして。SELECTステートメントに対するポインター（参照）のようなものだと思ってください。
ここでは"c1"という名前で100万件を返却するSELECTを定義していて、そのC1カーソルをload from c1という指定でLOADユーティリティの入力にしています。つまり、100万件のSELECTをクライアント側で受け取ってから投入するのではなく、ポインターとしてLOADユーティリティが直接読み取り、そのままデータベースへの入力として利用しています。

動かしてみると、以下のようになります。LOADユーティリティからのメッセージがたくさん出ていますが、要するにSELECTによって生成された100万件のデータが、LOADユーティリティによってデータベースに投入されたことがわかると思います。

$ db2 -tvf load_from_cursor.sql
connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 10.5.6
 SQL authorization ID   = DB2V105
 Local database alias   = SAMPLE


create table t2 (c1 int,c2 int,c3 char(20), c4 timestamp, c5 char(100))
DB20000I  The SQL command completed successfully.

declare c1 cursor for with temp(a) as ( values(1) union all select a+1 from temp where a<1000000) select a, cast(rand() * 1000000 as int), cast(rand() * 1000000 as char(20)), current timestamp, repeat('char string ',8) from temp
DB20000I  The SQL command completed successfully.

load from c1 of cursor replace into t2
SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3039W  The memory available to LOAD for DATA BUFFER prohibits full LOAD
parallelism.  Load parallelism of "7" will be used

SQL1193I  The utility is beginning to load data from the SQL statement " with
temp(a) as ( values(1) union all select a+1 from temp where ...".

SQL3500W  The utility is beginning the "LOAD" phase at time "11/15/2016
20:08:06.922900".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "1000000" rows were read from
the input file.

SQL3519W  Begin Load Consistency Point. Input record count = "1000000".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "11/15/2016
20:08:14.059412".


Number of rows read         = 1000000
Number of rows skipped      = 0
Number of rows loaded       = 1000000
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 1000000


select count(*) from t2

1
-----------
    1000000

  1 record(s) selected.

手元の環境で動かしてみると、一昔前のサーバーでも10秒ぐらいで完了しました。複雑なデータの作成はなかなか難しいですが、ちょこっとしたテストデータの作成には便利です。

（おまけ）PostgreSQLだとどうなるか

どうやらPostgreSQLでも共通表式（共通テーブル式、CTE）が使える模様なので、どこまで同じようにできるか試してみました。PostgresSQLはあまり詳しくないので、もっといい方法があったら是非教えてください。MySQLも、8.0ではCTEに対応する様子ですね。そのうち試してみます。

PostgreSQLの場合は、再帰的な参照を可能にする場合には"RECURSIVE"というキーワードをWITH句に追加する必要があるようです。そのほか、現在時刻を返すためのキーワードを"CURRENT TIMESTAMP"から"CURRENT_TIMESTAMP"に変えています。

with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<5)
select a,
       current_timestamp
 from temp;'

これをPostgreSQLに投入すると、DB2と同じように5件の結果セットが戻ってきました。

$ sudo  -u postgres -i psql -c 'with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<5)
> select a,
>        current_timestamp
>  from temp;'
 a |              now
---+-------------------------------
 1 | 2016-11-16 13:58:50.305138+00
 2 | 2016-11-16 13:58:50.305138+00
 3 | 2016-11-16 13:58:50.305138+00
 4 | 2016-11-16 13:58:50.305138+00
 5 | 2016-11-16 13:58:50.305138+00
(5 rows)

INSERT FROM SELECTもできました。

$ sudo  -u postgres -i psql -c '
> create table t2 (c1 int,c2 timestamp);
>
> insert into t2
>  with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<5)
>  select a,
>        current_timestamp
>  from temp;'
INSERT 0 5

SELECTすると、共通表式で指定した5件のデータが格納されています。

$ sudo  -u postgres -i psql -c 'select * from t2;'
 c1 |             c2
----+----------------------------
  1 | 2016-11-16 14:04:51.065885
  2 | 2016-11-16 14:04:51.065885
  3 | 2016-11-16 14:04:51.065885
  4 | 2016-11-16 14:04:51.065885
  5 | 2016-11-16 14:04:51.065885
(5 rows)

PostgreSQLのほうは、INSERT FROM SELECTだけで100万件が投入できてしまいました。

$ sudo  -u postgres -i psql -c '
> create table t3 (c1 int,c2 timestamp);
>
> insert into t3
>  with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<1000000)
>  select a,
>        current_timestamp
>  from temp;'

INSERT 0 1000000

確かに100万件入ってそうな様子

$ sudo  -u postgres -i psql -c 'select * from t3 order by c1 desc fetch first 10 rows only'
   c1    |             c2
---------+----------------------------
 1000000 | 2016-11-16 14:07:13.553718
  999999 | 2016-11-16 14:07:13.553718
  999998 | 2016-11-16 14:07:13.553718
  999997 | 2016-11-16 14:07:13.553718
  999996 | 2016-11-16 14:07:13.553718
  999995 | 2016-11-16 14:07:13.553718
  999994 | 2016-11-16 14:07:13.553718
  999993 | 2016-11-16 14:07:13.553718
  999992 | 2016-11-16 14:07:13.553718
  999991 | 2016-11-16 14:07:13.553718
(10 rows)
21
18
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
21
18