SONICMOOV Googleページ

19万件のsqlレコードをバルクインサート形式に整形して追加するtips

19万件のsqlレコードをバルクインサート形式に整形して追加するtips

  • このエントリーをはてなブックマークに追加

「このアイテムをユーザー全員に付与したい」という要望がたまに出るのですが、その時にユーザーIDだけ変更したsqlをユーザーの人数分insertしないといけないというインフラ(運用?)の泥臭いお仕事の一つが発生すると思うのですが、それを効率的に、要領よく、なるべく人の手が入らないように(ミスらないように、手早く)する方法、作業記録をまとめておきます。

vimやLinuxコマンドを使用できる環境が前提です。

挿入するsql

例として挿入するsql文は以下。

INSERT INTO `hoge`.`fuge` (`id`, `user_id`, `foo_id`, `created_at`, `updated_at`) VALUES
(NULL , '1', '3', now(), now());

「hoge」データベースの「fuge」カラムに「foo_id = 3」のsqlを登録者数分だけinsertする。というものです。これを19万件insertするという簡単なお仕事。

今回はバルクインサートを使い、500件まとまりのinsert分を作り、ファイルに保存してあとから見返せるようにもしておこうと思います。

手順

19万行分作成

まずはinsertする内容を作成するためにvimを開いて、以下の内容をペースト

(NULL , '1', '3', now(), now()),

ここでvimのコマンド

続きを読む

yy
190000p

で19万の複製が完了、次にユーザーIDに連番を振ります。

自分はvimrcに以下の設定がしてあるので

nnoremap  co :ContinuousNumber 
vnoremap  co :ContinuousNumber 
command! -count -nargs=1 ContinuousNumber let c = col('.')|for n in range(1, ?-line('.'):1)|exec 'normal! j' . n . <q>|call cursor('.', c)|endfor

参考: Vimで番号を順番につける方法 | MBA-HACK

縦に並んだ数字に連番を振る事が出来ます。
vimに貼付けた19万行の一番上に移動して連番を振りたい数字に移動、連番を振ってあげます。

gg
f1
190000co

これで19万のinsertすべきsqlの中身が出来ました。
「insert.sql」とでもして保存しておきましょう
これを500件まとまりのバルクインサートになるように整形します。

バルクインサート形式に整形

整形するためにはLinuxコマンドの「split」と「sed」を使用します。

保存した「insert.sql」のファイルの階層に移動し、

mkdir split
split -l 500 insert.sql split/
ls split/

これで500行区切りのファイル郡が作成されます。
次にバルクインサート形式にファイル内を編集します

cd split
sed -i "1s/^/INSERT INTO `hoge`.`fuge` (`id`, `user_id`, `foo_id`, `created_at`, `updated_at`) VALUES\n/" *
sed -i "501s/,$/\n;/" *

確認のために以下のコマンドを実行

grep VALUES -L *
grep ';' -L *

出力がなければOK
19万1行とか500区切りだと飛び出る分のインサートの場合は最終行の「;」が置換されていないので、ファイルを開いて編集しておいてください。

これでファイル一つ一つの内容がバルクインサートで登録出来る形式になりました。

sqlのインサート

次にファイル内のsql文をコマンドライン上から実行します。
今回はmysqlにインサートします。別途コマンドラインからmysqlに接続出来るように、mysqlのオプションを読み替えてください。

cat * | mysql -t

参考: MySQLのSQLをシェルから実行したい – Qiita [キータ]

これで19万件のsqlが実行されました。
作業記録として作成したsqlファイル群をtarで固めてしかるべき所に保存しておけば作業記録にもなるかと思われます。お疲れさまでした。

  • このエントリーをはてなブックマークに追加

記事作成者の紹介

cube(システムエンジニア)

ソーシャルアプリ事業部でシステムエンジニアをやっています。cubeです。cubeというニックネームは主に会社で使っていて、個人でのネット上のハンドルネームはsoramugiで主に活動しています。

関連するSONICMOOVのサービス

システムエンジニア募集中!

×

SNSでも情報配信中!ぜひご登録ください。

×

SNSでも
情報配信中!
SONICMOOV Facebookページ SONICMOOV Twitter SONICMOOV Googleページ
システムエンジニア募集中!

新着の記事

mautic is open source marketing automation