はじめに
こんにちは、新卒で入社して3年目のnorth_mkyです。
最近業務でSQLチューニングをする機会があったので、実行計画を読み解く記事を書こう！...と思いたったのですが、記事を書くにあたってサービスのデータベースを使うわけにはもちろんいかないので適度なサンプルデータベースを作成し、 大量のデータを投入する という準備作業を行う必要がでてきました。
今まで大量のデータを入れるという作業はあまりしたことがなかったため、備忘も込めて当初予定していた記事を書く前に大量データの投入について述べたいと思います。
色々探していると、大きくは下記2つが投入方法として出てきました。
- CSVファイルをインポートする方法
- generate_series()関数を使用する方法
1. はpostgresqlが用意しているCOPYコマンドを使用する方法です。公式のお墨付きです。
14.4. データベースへのデータ投入
データベースにデータを初期投入するために、大量のテーブル挿入操作を行う必要がままあります。 本節では、この作業を効率良く行うためのちょっとした提言を示します。
(中略)
単一コマンドですべての行をロードするために一連のINSERTコマンドではなく、COPYを使用してください。 COPYコマンドは行を大量にロードすることに最適化されています。
https://www.postgresql.jp/document/10/html/populate.html
ですが、公式の言葉を押し切って今回私は2. generate_series()関数を使用する方法をおすすめします。
generate_series()関数を使用する方法をおすすめする3つの理由
1. 大量データ投入処理までの準備はなし
両者の作業手順は以下になります。
投入方法1では大量データを生成する→生成したデータをインポートする、というデータ投入前にデータを用意する準備作業が発生しますが、投入方法2ではデータ生成→データインポートの両方の処理をSQL1文で行ってくれるため、投入するまでにかかる準備はありません。generate_series()関数は標準で入っているので拡張モジュールの読み込み等も不要です。
- CSVファイルをインポートする方法
- CSVファイルを生成するスクリプトを作る
- 作成したスクリプトを実行する
- COPYコマンドに適切な引数を与えて実行する
- generate_series()関数を使用する方法
- 大量データを生成するSQLを作る(generate_series()関数を使用)
- SQLを実行する
2. 学習コストがほとんどない
投入方法1のCSVファイル生成スクリプトは自分の好きなやり方で組めばいいのでそこまで時間はかかりませんが、鬼門はCOPYコマンドだと思います。COPYコマンドはおそらく大量データの投入か、既存テーブルの別テーブルへの複製に使うと思いますが、いざ使おうとすると色々お作法に馴染みがなく手間取ってしまいます。
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
ここでoptionは以下のいずれかです。
FORMAT format_name
OIDS [ boolean ]
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
FORCE_NULL ( column_name [, ...] )
ENCODING 'encoding_name'
https://www.postgresql.jp/document/10/html/sql-copy.html
ファイルパスの指定1つにしてもwindowsとlinuxで異なるのはもちろんのこと、投入するカラム値に空白が入っている場合の扱いを指定したりなど、馴染みのない人間にとってはトライアンドエラーで時間がかかります(私は5-10分かかりました)。
一方投入方法2はいつもどおりSQLを作成するだけなので学習コストはほぼなしです。
3. 実行時間がCOPYコマンドと変わらない
これは実測して驚いたのですが、両者とも投入時間はほぼ変わらないという結果になりました。
公式のお墨付きのCOPYコマンドと同等の処理性能で、準備に時間がかからないというので私はこのgenerate_series()関数を使用する方法をおすすめします。
generate_series()関数を使用した大量データ投入方法とは
「顧客テーブルに1000万行のデータを入れる。名前は"ラクス太郎n"にする(n=1...10,000,000)」
上記を満たす大量データ生成SQLは以下になります。
1000万行が入った1GB超のファイルを用意する必要はありません。SQL1文で作成できます。
INSERT INTO customer (id,name)
SELECT
i, format('ラクス太郎%s', i)
FROM
generate_series(1,10000000) as i
;
SELECT部分だけを打つと下記が返ってきます。
?column? | ?column?
----------------+------------------------
1 | ラクス太郎1
2 | ラクス太郎2
...
10000000 | ラクス太郎10000000
連番を生成し、集合として返すgenerate_series()関数を応用すると、このようにその場でテーブルを作るようなことができ、大量データを投入することができます。他にも上述のformat()関数のようにrandom()関数などと組み合わせるといい感じの大量データを手軽に作成することができます。
検証 : COPYコマンド VS generate_series()関数
あるテーブルに1000万行を投入する処理の経過時間を計測しました。
環境
サンプルデータベース
PostgreSQLTutorial.com のサンプルデータベースを使用しました。
チューニングの記事を書く目的だったため、ある程度外部キー制約があったりカラム数があったりするデータベースはないかな、と探していたらすぐにこのデータベースが見つかりました。
結果
両者ともほぼ同じ結果になりました。
- COPYコマンド
dvdrental=# COPY customer (store_id,first_name,last_name,email,address_id,activebool,create_date,last_update,active) FROM '/Users/north_mky/customer.csv' ( delimiter ',', format csv, header true );
COPY 10000000
Time: 556051.126 ms (09:16.051)
- generate_series()関数
dvdrental=# INSERT INTO customer (store_id,first_name,last_name,email,address_id,activebool,create_date,last_update,active)
dvdrental-# SELECT
dvdrental-# 2,
dvdrental-# 'Austin',
dvdrental-# format('Cintron%s', i),
dvdrental-# format('austin.cintron%s@sakilacustomer.org', i),
dvdrental-# 605,
dvdrental-# 't',
dvdrental-# '2006-02-14',
dvdrental-# '2013-05-26 14:49:45.738',
dvdrental-# 1
dvdrental-# FROM
dvdrental-# generate_series(1,10000000) as i
dvdrental-# ;
INSERT 0 10000000
Time: 558479.994 ms (09:18.480)
おわりに
generate_series()関数は大量データの投入に対して、楽に導入できて楽に使える関数です。
テスト時に大量データが必要になった際の手助けになれば幸いです。
◆TECH PLAY
techplay.jp
◆connpass
rakus.connpass.com
