はじめに
Mysql向けにデータ抽出＋データ挿入を行う機会がありました。
手順はSELECT結果をTSVで書き出し、別DBに挿入するといったシンプルな流れとしました。
※mysqldumpはしたくないけど特定のテーブルのデータを抽出して、別テーブルで使いたい
※Viewの結果をTSVに保存して別テーブルで扱いたい
前提として、本作業はAWS EC2＋AWS RDSで行ったものとなります。
データ抽出
抽出は何度も行う為、shにして再利用しました。
#!/bin/sh
# 抽出結果を/tmp/領域へ書き出し
# 抽出ファイル名は、挿入先のテーブル名に合わせておく
mysql --defaults-extra-file=[conf_file_path] [dbname]-e \
" \
select \
* \
from [table_name] \
where [condition...]
;
" > /tmp/[table_name].tsv
exit 0
データ挿入
データの挿入は直接DB内のコマンド実行
ファイル読み込み権限が付与されていない場合、Mysql接続コマンドでオプション(local_infile)を引き渡ししてそのセッションでのみ有効にしてあげてください。
-- dbにログイン
-- このときLOADデータを行うフラグを付与して接続する(AWSデフォルトユーザには権限はない）
mysql --defaults-extra-file=[conf_file_path] [dbname] --local_infile=1
-- 最新データだけでいいので一度テーブルをtruncate(ここはあくまでその時の運用や用途次第)
-- truncate table [import_table_name];
-- LOAD DATA
-- TSVを前提としたロード
-- FIELDS TERMINATED BY '\t' = tabが区切り文字であることの宣言
-- ENCLOSED BY '"' 文字データをダブルクォーテーションで囲む宣言
-- LINES TERMINATED BY '\n' 改行コードがLFである宣言、 CRなら \r どっちもなら \r\n
-- IGNORE 1 LINES 1行目がヘッダー行である宣言、1行目の取り込みがスキップされる
LOAD DATA LOCAL INFILE '/tmp/[table_name].tsv' INTO TABLE [import_table_name] FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
おわりに
AWS環境であれば、抽出ファイルをS3にアップしておいて、LOAD DATAの際にS3のバケットから読み込むといった事も可能です。
Mysqlの接続情報を直接書くのは微妙なので、confファイル化しておいて読み込む形にしました。
※どの程度までconfに接続情報を書くかは、調整ください。
※パスワードは特殊文字が入っている場合は引用符で囲んでください。
.sample.cnf
[client]
host="DBホスト"
user="DB接続ユーザ"
password="DB接続パスワード"