ランサーズ等のサービスを開発・運用する中で得た知識やノウハウを紹介しています。

thumbnail

Labels:  DevOps, MySQL 投稿者:seiji

データ調査を効率化!SQLとシェルを使って、データを自由に操作する方法

はじめまして。
4月からランサーズのエンジニアチームにジョインしました大平です。
好きな言語はPerl, ShellScript, JavaScriptです。
プロンプトはbashですが、個人的なShellScriptはzshです。

データ調査をしている時に、あと少しで欲しいデータが取れそうだけど、取れない。。。
そんなもどかしい経験ありませんか。そういった時どんな解決手段をとっていますか?

  1. 複雑なSQLを書いてがんばる
  2. サービスで使っているプログラミング言語を介して
  3. SQLとExcelの合わせ技でがんばる
  4. 手作業(気合い)でがんばる

色々なアプローチがあると思いますが、今回はそういった時に役立つかも知れないオススメな方法をご紹介したいと思います!

シェルからMySQLをナンパする方法

シェルスクリプトからMySQLにアクセスする

mysqlコマンドさえ使えれば標準的なUNIXコマンドを組み合わせるだけでも強力なツールとなります。
MySQLにログインせずにSQLを発行する方法といえば下記のいずれかですね。
今回はパスワード設定等をしていない体で書きますが、適宜、読み替えて頂ければと思います。

$ mysql -e ‘SELECT 1, 2, 3 FROM dual;’
$ mysql < /path/to/foo.sql

実行結果

+—+—+—+
| 1 | 2 | 3 |
+—+—+—+
| 1 | 2 | 3 |
+—+—+—+

前者はeオプションを用いてインラインでSQLを発行できるため変数を用いることが可能。
後者はプロダクトで使っているSQLファイルを流用することが可能。
以降はインライン版のみで記述していきます。

抽出したデータを整形する

しかし、mysqlコマンドで抽出しただけでは先頭2行がカラム名と罫線になっていたりして使いづらいですね。
そこでヘッダを出力しないようにNオプションを指定します。

$ mysql -N -e ‘SELECT 1, 2 from dual;’

実行結果

+—+—+
| 1 | 2 |
+—+—+

Nオプションを指定することでヘッダの出力を制限できました。
罫線が消えるといい感じにデータを扱いやすくなるのでsオプションを指定します

$ mysql -N -s -e ‘SELECT 1, 2 from dual;’

実行結果

1 2

実行結果を順次処理を行う

データがいい感じにとれたところで、これを処理していきます。
取得したデータをパイプでwhileに渡すだけです。

$ mysql -N -s -e ‘SELECT 1, 2 from dual;’ | while read line ; do echo “${line}” ; done

実行結果

1 2

dualから取得しているので、1件しかありませんが実際にはループしています。
ここまでくればカラム毎に処理をするだけですね。
データはtsvになっているので、そのまま配列に代入します。

$ mysql -N -s -e ‘SELECT 1, 2 from dual;’ | while read line ; do rs=($line) ; echo “${rs[0]} / ${rs[1]}” ; done

実行結果

1 / 2

レコード数、オートインクリメント値を変数に代入する

単にレコード数をとりたい場合などは実行結果をそのまま代入するだけです。
(dualのレコード数ってなんで1なんだろう。。)

foo_count=$(mysql -N -s -e ‘SELECT COUNT(*) FROM dual;’)

実行結果

1 / 2

実用にあたって

今回はDBから読み込んだデータを変数に代入するところまでとなります。
シェルからDBを利用することで短いコードで実現できて、色々と使い所はあるかと思います。

一方でSQLインジェクションに加え、コマンドインジェクションにも注意する必要が出てきますが、シェル自体は行ベースのデータとの相性が非常に良いのでRDBMSの処理には非常に適しています。
それでは快適なCLIライフをenjoy!!

ランサーズではサービスを成長させてくれるエンジニア、デザイナーを募集しています!
ご興味がある方は、以下URLよりご応募ください。

PHPエンジニア
Rubyエンジニア
サーバーサイドエンジニア
SREエンジニア
フロントエンドエンジニア
その他採用情報

関連記事

PHP, CakePHPバージョンアップに向けてCIで品質を担保・向上していく

こんにちは、エンジニアの上野です。 今回はソース例や実際のサンプルソースを交えながら 「PHPバージョンアップに向けてCIで品質を担保・向上していく」と題してlancers.jp本体に導入しているCircleCIでの取り組みについてご紹介します。 導入背景 P …

AWS
AWS セキュリティ対策 〜 操作履歴を CloudTrail で残してみた

こんにちわ。こじまです。 ランサーズでは,AWS を利用してシステムを運用しています。 コンプライアンスの監査,トラブルシューティング,そして,セキュリティ分析を実施するために AWS の操作履歴を残すことは必須です。   今日は,AWS 操作履歴を …

thumbnail
ランサーズの仕事を機械学習で分類する – 2. 分かち書き –

分かち書きとは 分かち書きとは、テキストを空白区切りにしたものです。 テキストを機械学習させるには前段階としてテキストを分かち書き状態にする必要があります。 なんで休みなく働いている人が偉いの? このテキストの分かち書き状態は下記の通りです。単語の意味ごとに空 …