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

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新卒対象サマーインターン
エンジニアインターン

その他採用情報

関連記事

ランサーズの分析基盤(capybara)と運用について紹介

さあて!!Lancers(ランサーズ) Advent Calendar 2018 がついに始まりました。1日目は @numanomanuさんの「誰も教えてくれない、サービスを終了する技術〜 SPA で作ったサービスを閉じる時にやるべきこと 〜」 2日目は!!! …

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

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

ランサーズで脆弱性スキャナVulsを導入しました!!

5月にランサーズのSREチームに入社しました@adachin0817(あだちん)と申します。 さてさて皆さん以下のブログをお読みになられましたか? https://engineer.blog.lancers.jp/2018/06/5267/ SREチームの発足 …