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

Labels:  SRE, 分析基盤 投稿者:adachin

[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


今までの運用と問題点

去年のアドベントカレンダーでもご紹介しましたが、上記のプロフィール閲覧数は深夜に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コマンドを使ってシェルスクリプトにしてみましたが、実際に書いてみると結構シンプルでした。皆さんもぜひ参考にしてみてください!

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


【中途採用】
サービスリードエンジニア
テックリード(アーキテクト)
フロントエンドエンジニア
サーバーサイドエンジニア
業務エンジニア(社内システム基盤・基幹システム)

【インターン・学生バイト】
19新卒対象サマーインターン
エンジニアインターン

その他採用情報

関連記事

ランサーズの社内Wi-Fi環境を改善した話 〜見えない無線との戦い〜

SREチームの上原です。 ランサーズのSREチームでは、社内インフラの整備も担当しています。 今回は社内Wi-Fi環境の改善を行いましたので事例として共有いたします。 社内Wi-Fiが抱えていた課題 ランサーズ社内のWi-Fiは、大きく2つの課題を抱えていまし …

ランサーズに出戻って1年。担当した開発プロジェクトを振り返る。

「ランサーズ Advent Calendar 2018 」23日目の記事です。 こんにちは ランサーズでEngineering Managerをしてます神庭(godgarden)です 残すところ, 今年も数営業日。1年の振り返りをしている方も多いと思います. …

API GatewayとLambdaとGolangで作るサムネイル生成システム

SREチームの金澤です。 ランサーズのサムネイル生成をAPI Gateway + Lambdaのシステムにリニューアルしました。 今回、その内容について書きたいと思います。 以前のサムネイル生成処理 今までのサムネイル生成処理は、Appサーバー内でImageM …