はじめに
私は文系卒プログラミング未経験で開発部署に配属された、社会人2年目の新米エンジニアです。
「まだまだ高度な技術的な記事なんて書けない、、、だけどポエム的な記事も新卒1年目の子がいい記事を書いてくれているので2年目の自分が何か書けることなんてあるかなあ」と避けていました。
ですが、自分が会社内の活動でQiita部なるものに入っていること、弊社の去年のアドベントカレンダーの以下の記事などに背中を押されて今回本記事を書くことにしてみました。
その技術記事、価値あります
100万件データ作ろう
自分が担当していたお客様からの問い合わせを調査するために、100万件ほどマスタデータを作成する必要がありました。
初めは「ただデータを100万件作ればいいだけね」と軽く考えていたのですが、意外と沼にはまってしまったのでその方法をTips的に書き残してみます。
挫折した方法たち
方法その1 画面からマスタを100万件登録する
お客様から問い合わせいただいたデータを自社製品の画面から直接登録するという一番シンプルな方法です。
不可能ではないですが、100万件を手入力で登録するのはあまりにも現実できてないので却下。
方法その2 100万件分のデータのdatファイルを作成し、ツールでデータベースに取り込む
まずdatファイルの作成自体が大変でしたが、コピー＆ペーストを繰り返しながら力技で一旦10万件分のデータのdatファイルを作成しました。
このファイルを適宜中身を書き換えてデータベースに取り込む作業を10回繰り返せば100万件だ！！と思っていました。
しかし、データベースにdatファイルを取り込むツールの特性上1件1件insert文を実行しているため合計10万回もSQLを実行することになり、ツールが固まってしまいました。
この方法も挫折。
方法その3 製品のCSV取込機能を使う
既に方法その2でdatファイルは作成済みであり、自社製品の機能でCSVファイルをバッチ処理で取り込み、登録する機能が存在しているためそれを利用しました。
しかし、10万件一気に取り込むと1回のバッチ処理にかなり時間がかかるためこちらの方法も挫折。
ROWNUMを使ってみた方法
ROWNUMについて
ROWNUMを使うことで実行したSQLの取得結果に対して連番を振ることができます。
例えば以下のようなmembersテーブルがあったとします。
| name | age | id |
|---|---|---|
| 田中太郎 | 22 | 1 |
| 山田花子 | 24 | 2 |
| 佐藤二郎 | 23 | 3 |
| 鈴木光一 | 20 | 4 |
| 小林優子 | 25 | 5 |
SELECT ROWNUM, name, age, id FROM membersを実行すると、
| ROWNUM | name | age | id |
|---|---|---|---|
| 1 | 田中太郎 | 22 | 1 |
| 2 | 山田花子 | 24 | 2 |
| 3 | 佐藤二郎 | 23 | 3 |
| 4 | 鈴木光一 | 20 | 4 |
| 5 | 小林優子 | 25 | 5 |
このように連番が振られます。
以下のようにWHERE句に指定するとレコード数を制限して連番を取得することもできます。
SELECT ROWNUM, name, age, id FROM members WHERE ROWNUM <= 3
| ROWNUM | name | age | id |
|---|---|---|---|
| 1 | 田中太郎 | 22 | 1 |
| 2 | 山田花子 | 24 | 2 |
| 3 | 佐藤二郎 | 23 | 3 |
実際にROWNUMを使って100万件データを作った方法
上記のmembersテーブルに100万件データを作ると仮定します
membersテーブルはidカラムをキーとしているため、idが異なればname、ageに関しては同じ値でも一意と見なされる、とします。
- なるべくデータ件数が多い既存テーブルを探す
例えば以下のようなgoodsテーブルを既存テーブルを例とします。
goodsテーブルには100万件データが存在しています。
| id | product_name | price |
|---|---|---|
| 0000001 | りんご | 100 |
| 0000002 | バナナ | 150 |
| 0000003 | みかん | 300 |
| ⋮ | ||
| 0999999 | いちご | 350 |
| 1000000 | メロン | 500 |
- 既存テーブルを利用して、100万件のレコードを作成したいテーブルにINSERTする
goodsテーブルから100万件を取得し、ROWNUMを使ってidを連番で割り振りINSERTします。
INSERT INTO members (name, age, id) SELECT '田中太郎', 22, ROWNUM FROM goods WHERE ROWNUM <= 1000000
| name | age | id |
|---|---|---|
| 田中太郎 | 22 | 1 |
| 田中太郎 | 22 | 2 |
| 田中太郎 | 22 | 3 |
| ⋮ | ||
| 田中太郎 | 22 | 999999 |
| 田中太郎 | 22 | 1000000 |
これでmembersテーブルに100万件データを作ることができました。
nameとageは同じ値になっていますが、idがキーであるためすべてのデータが一意となります。
イメージとしてはgoodsテーブルから100万件分のデータの箱だけもらい、
そこにmembersテーブルにINSERTするための必要な情報を詰めるイメージです。
1回のSQL実行で済むため、挫折したいくつかの方法に比べて時間もそこまでかかりませんでした。
おわりに
今回は大量のデータを作るには、というとてもシンプルなテーマをSQLによって実現した方法を書いてみました。
内容自体はごく簡単ですが、若手にとっては意外とシンプルなことに思えても実現方法が思いつかないこともあるかと思います。
私自身のあるあるなのですが、思いつかないと「えいっ」と力技で乗り切ってしまうことが多々ありました。
しかし、重要なのは何かを実現するための方法を調べたり実践したりするという試行錯誤を重ねて、後に楽をするための努力を初めに惜しまないことだと思います。
本記事がそんな最初の努力の一助になれば幸いです。