[digdag]BigQueryのデータをシェルスクリプトでDBにインポートする方法

ランサーズ Advent Calendar 2019 13日目の記事となります。

SREチームの@adachin0817です。3日目の記事見てくれましたでしょうか！？(以下より)

https://engineer.blog.lancers.jp/2019/12/lancers-tech-night/

今回はBigQueryのデータをシェルスクリプトでDBにインポートする方法をブログしたいと思います。この裏で動いているのがご好評頂いているランサーズの機能であるユーザーレポートのプロフィール閲覧数のデータを表示しています。ユーザーレポートに関しては以下を参考にしてみてください！

https://info.lancers.jp/20847

https://twitter.com/katsuo_fujisaki/status/1144833848087236609?s=20

今までの運用と問題点

構成 (https://engineer.blog.lancers.jp/2018/12/lancers-capybara/)

去年のアドベントカレンダーでもご紹介しましたが、上記のプロフィール閲覧数は深夜にdigdag/embulk(embulk-input-bigquery)でアプリケーションログをviewとして作成し、BigQueryのviewテーブルからRDSへシンクしています。去年ベータ版をリリースし、運用してきましたが、いくつかデータが取れないことがあり、取れる日に一気にdigdagで再シンクをしていました。さすがに手動で対応するには厳しいので、改善案をいくつか上げてみました。

改善案その1

embulk-input-bigqueryプラグインのバージョンアップ

このプラグインをバージョンアップすれば値が取れたり取れなかったりという事象がなくなるのではないのか、とチームで話し合いあったので取り組んだところ、バッチも動いており、Rubyやembulkのバージョンアップをしなければなく、各プロジェクトが問題なく動作するのかということもあり、断念しました。

改善案その2

・BigQueryのデータをdigdagサーバーでcsvにエキスポートし、RDSへインポートする

https://cloud.google.com/bigquery/docs/reference/bq-cli-reference?hl=ja

bqコマンドには様々なフラグ(オプション)が対応しているので今回「改善案その2」を実装してみました。これであればdigdagでシェルスクリプトを実行できるので、代用案として最適かと思われます。

bigquery-to-mysql.sh

#!/bin/bash

$(".common/secrets.sh")
YESTERDAY=$(date +%Y-%m-%d -d '1 days ago')

## views out csv
/home/hoge/google-cloud-sdk/bin/bq query --use_legacy_sql=false --format=csv --max_rows=100000 "SELECT * FROM \`prd-hoge.views.views\` where dt=\"${YESTERDAY}\"" > /tmp/views.csv

## add ,
sed -i '1,2d' /tmp/views.csv
sed -i "s/^/,/g" /tmp/views.csv

# restore
mysql -h "${HOST}" -u hoge -p"${PWD}" -P "${PORT}" hoge -N -e "LOAD DATA LOCAL INFILE '/tmp/views.csv' INTO TABLE views FIELDS TERMINATED BY ','"

## rm profile_views.
rm -f /tmp/views.csv

1. bqコマンドで対象のテーブル(1日前)を/tmpにcsvで吐き出す

--max_rows のデフォルトは100なので100レコードしか取り出せません。明示的に10万レコードを指定しております。

2. sedで2行分削除と置換

3 .DBにリストアし、csvを削除

次はdigdagで動かしてみます。

run.dig

timezone: Asia/Tokyo

schedule:
  daily>: xx:xx:xx

+views:
  !include : 'retry.dig'
  call>: views.dig

retry.dig

_retry:
  limit: 5
  interval: 60
  interval_type: exponential

views.dig

_error:
  sh>: /digdag/post_slack.sh "[${session_time}][${session_id}] DigDag Fail in Bigquery to MySQL views"

+load:
  >: /digdag/bigquery_to_mysql/embulk/bigquery-to-mysql.sh

retry.digを入れることでシェルスクリプトがエラーを起こしても5回再実行されるように設定しています。(以下参考に)

https://blog.adachin.me/archives/10835

確認

今の所、毎日漏れなくプロフィール閲覧数が表示されるようになりました！

まとめ

今回初めてbqコマンドを使ってシェルスクリプトにしてみましたが、実際に書いてみると結構シンプルでした。皆さんもぜひ参考にしてみてください！