続 select結果をcsvでファイル出力
先月、mysqlの into outfile
でレコードをCSVファイルにサクッと取ってた記事を書きました
で、これは知ってるんだけど、今回はこれじゃダメなんだよな〜ってときに、どうしていたか思い出したので、記事書く気になりました
というのも、1. into outfile
を使うと access denied
になる場合も権限によってはあるね
2. mysqlがsshトンネルの先にあって、その先のsshは許可されてないの・・・とか
3. ファイルにしたら巨大過ぎて、そんなの置くスペースないぜ!とか
過去を思い返すと、3. のパターンはあったな〜という思い出です
確かあれはサーバフルリプレイスしてたときに、あのvmが・・・
まぁ、というわけなんですが、手順はこんな感じ
1. mysql
コマンドに -e
つけてSQLを投げる
2. 1. はタブ区切りで出力されるから、 sed
でタブをカンマに変換
3. csvファイルにリダイレクトする
1 . のSQLですが、文字列系はダブルクオーテーションで囲う
select concat('"', hoge, '"') as hoge, hige as hige, concat('"', huga, '"') as huga from hogehoge
ワンライナーで書けるSQLだったら -e
に続けて書いてもいいけど、 concat
とか使ってたらそうも行かないので、SQLファイルにしておく
で、これでファイルを cat
してクエリをmysqlに流し込んで、ファイルにリダイレクトしておく
この時、mysqlはタブ区切りに出力してくれるみたいなので、なんとなく拡張子を tsv
にしてます
mysql -uroot hohoidb -e "`cat hogehoge.sql`" > ./hoge.tsv
2 . 3 . sed
でタブをカンマに変換してリダイレクト
sed -e 's/\t/,/g' ./hoge.tsv > hoge.csv
てな感じでヌルっと出してます
まぁ、だいたいSQL用意してこんな感じでバーンとしてます
mysql -uroot hohoidb -e "`cat hogehoge.sql`" | sed -e 's/\t/,/g' > hogehoge.csv
あ、前回書き忘れたけど、csvをmysqlにインポートするときはこんなSQL使ってます
LOAD DATA LOCAL INFILE '/home/ore/hogehoge.csv' INTO TABLE hogehoge FIELDS TERMINATED BY ',' ENCLOSED BY '"'
自分が完全に管理してるDBだったら、ALTER
やっちゃえ!とかも思うんですが、「別チームんとこだしなー」「申請とかなー」とか思う時、エンジニアだったらあるはず!
そんなときに、サックリ自己解決したいときに使ってます
遅せークエリがボトルネックになってるんだけど、ローカルにデータ欲しいわーって思ったときとかかなぁ
今のところ、この2つの手法でデータが取れなかったことはないかなぁ
こういうのって、結構レガシーな手法で、知ってても自慢できるようなことじゃないんだけど、知らない人は知らない感じなのかなぁ
僕は結構頻繁に使うのですが、知らない人は、思いつかない限り使わないのかなぁと思ったりしてます
これらの気にいってるとこは、 select
しか投げてないで!ってとこ
流石に select
で壊れないっしょっていう安心感があるw
ブログ書いてるの、わりと会社の人にバレた気配がするので、役立ってくれたらうれしいなぁ〜