Google Compute Engineインスタンス環境下でbqコマンドを使ってBigQueryにデータを投入する
アライアンス統括部 サービスグループの しんや です。
DevelopersIOのブログ投稿データを収集・加工・可視化して社内に共有することをここまで実践してきていたのですが、その際に用いている環境にはAmazon Redshiftを活用していました。先日個人的に部署異動(2023年03月01日付けでアライアンス統括部に異動)したのもあり、またデータ連携と可視化の仕組みもよりスムーズに/便利に/広範に連携出来るようにしたいと思い、「社内投稿データ分析環境をBigQueryに載せ替える手順をそれぞれブログにまとめておこう」と思い立ちました。
当エントリではその中から「Google Compute Engineインスタンス環境からbqコマンドを使ってデータをBigQueryに連携する」手順についてまとめておこうと思います。
目次
データ連携イメージ(構成図)
ブログ投稿データを某所から取得し、BigQuery環境に投入するというシンプルな流れです。以前の環境もEC2インスタンスを用いて処理を繋いでいたため、ここでも同様の環境でひとまず動かせるところまで持っていこう！という形にしています。Google Compute Engine環境下で動かす処理も、BugQueryに連携させるためにbqコマンドを活用したものとしています。処理が完了したらSlackに通知を飛ばすのはこちらのエントリと同様です。
Google Cloud:Compute Engine環境の初期設定
実行させたい処理は「bqコマンドによるBigQuery環境へのデータ投入」が主なのですが、そこに至るまでに必要な設定や手順等があったので備忘録として以下に内容を整理しておきます。「管理コンソールによるGoogle Compute Engineインスタンスの作成」以降の内容は以下の通り。
インスタンスのrootパスワードを設定
xxxxxxxx@instance-1$ pwd /home @instance-1$ sudo passwd New password: (指定のパスワードを入力) Retype new password: (指定のパスワードを再入力) passwd: password updated successfully @instance-1$ su - Password: root@instance-1:~#
wgetコマンドのインストール
root@instance-1:/home# sudo apt update root@instance-1:/home# sudo apt-get install --asssume-yes wget root@instance-1:/home# wget --version wget --version GNU Wget 1.21 built on linux-gnu.
gcloudコマンドのインストール
root@instance-1:/home# curl https://sdk.cloud.google.com | bash
curl https://sdk.cloud.google.com | bash
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 443 100 443 0 0 5091 0 --:--:-- --:--:-- --:--:-- 5091
Downloading Google Cloud SDK install script: https://dl.google.com/dl/cloudsdk/channels/rapid/install_google_cloud_sdk.bash
######################################################################## 100.0%
Running install script from: /tmp/tmp.pNT9iy6apd/install_google_cloud_sdk.bash
which curl
curl -# -f https://dl.google.com/dl/cloudsdk/channels/rapid/google-cloud-sdk.tar.gz
################################################################################################################################################### 100.0%
Installation directory (this will create a google-cloud-sdk subdirectory) (/root):
mkdir -p /root
tar -C /root -zxvf /tmp/tmp.YJvcRVzjbb/google-cloud-sdk.tar.gz
google-cloud-sdk/.install/.download/
google-cloud-sdk/.install/core.manifest
google-cloud-sdk/.install/core.snapshot.json
google-cloud-sdk/.install/gcloud-deps.manifest
google-cloud-sdk/.install/gcloud-deps.snapshot.json
google-cloud-sdk/LICENSE
google-cloud-sdk/README
google-cloud-sdk/RELEASE_NOTES
:
:
google-cloud-sdk/platform/ext-runtime/ruby/templates/Dockerfile.template
google-cloud-sdk/platform/ext-runtime/ruby/templates/dockerignore.template
google-cloud-sdk/platform/ext-runtime/ruby/test/runtime_test.py
google-cloud-sdk/rpm/mapping/command_mapping.yaml
google-cloud-sdk/rpm/mapping/component_mapping.yaml
/root/google-cloud-sdk/install.sh
Welcome to the Google Cloud CLI!
WARNING: You appear to be running this script as root. This may cause
the installation to be inaccessible to users other than the root user.
To help improve the quality of this product, we collect anonymized usage data
and anonymized stacktraces when crashes are encountered; additional information
is available at <https://cloud.google.com/sdk/usage-statistics>. This data is
handled in accordance with our privacy policy
<https://cloud.google.com/terms/cloud-privacy-notice>. You may choose to opt in this
collection now (by choosing 'Y' at the below prompt), or at any time in the
future by running the following command:
gcloud config set disable_usage_reporting false
Do you want to help improve the Google Cloud CLI (y/N)? y
This will install all the core command line tools necessary for working with
the Google Cloud Platform.
Beginning update. This process may take several minutes.
Your current Google Cloud CLI version is: 418.0.0
Installing components from version: 418.0.0
┌─────────────────────────────────────────────────────────────────────────────┐
│ These components will be installed. │
├─────────────────────────────────────────────────────┬────────────┬──────────┤
│ Name │ Version │ Size │
├─────────────────────────────────────────────────────┼────────────┼──────────┤
│ BigQuery Command Line Tool │ 2.0.85 │ 1.6 MiB │
│ BigQuery Command Line Tool (Platform Specific) │ 2.0.77 │ < 1 MiB │
│ Bundled Python 3.9 │ 3.9.16 │ 63.4 MiB │
│ Cloud Storage Command Line Tool │ 5.20 │ 15.6 MiB │
│ Cloud Storage Command Line Tool (Platform Specific) │ 5.13 │ < 1 MiB │
│ Google Cloud CLI Core Libraries (Platform Specific) │ 2022.09.20 │ < 1 MiB │
│ Google Cloud CRC32C Hash Tool │ 1.0.0 │ 1.2 MiB │
│ anthoscli │ 0.2.35 │ 68.6 MiB │
│ gcloud cli dependencies │ 2021.04.16 │ < 1 MiB │
└─────────────────────────────────────────────────────┴────────────┴──────────┘
For the latest full release notes, please visit:
https://cloud.google.com/sdk/release_notes
╔════════════════════════════════════════════════════════════╗
╠═ Creating update staging area ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: BigQuery Command Line Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: BigQuery Command Line Tool (Platform Spec... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Bundled Python 3.9 ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Bundled Python 3.9 ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Cloud Storage Command Line Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Cloud Storage Command Line Tool (Platform... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Default set of gcloud commands ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Google Cloud CLI Core Libraries (Platform... ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Google Cloud CRC32C Hash Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: Google Cloud CRC32C Hash Tool ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: anthoscli ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: anthoscli ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Installing: gcloud cli dependencies ═╣
╠════════════════════════════════════════════════════════════╣
╠═ Creating backup and activating new installation ═╣
╚════════════════════════════════════════════════════════════╝
Performing post processing steps...done.
Update done!
WARNING: There are other instances of Google Cloud tools on your system PATH.
Please remove the following to avoid confusion or accidental invocation:
/usr/lib/google-cloud-sdk/bin/anthoscli
/usr/lib/google-cloud-sdk/bin/bq
/usr/lib/google-cloud-sdk/bin/gcloud
/usr/lib/google-cloud-sdk/bin/git-credential-gcloud.sh
/usr/lib/google-cloud-sdk/bin/gcloud-crc32c
/usr/lib/google-cloud-sdk/bin/docker-credential-gcloud
/usr/lib/google-cloud-sdk/bin/gsutil
Modify profile to update your $PATH and enable shell command completion?
Do you want to continue (Y/n)? y
The Google Cloud SDK installer will now prompt you to update an rc file to bring the Google Cloud CLIs into your environment.
Enter a path to an rc file to update, or leave blank to use [/root/.bashrc]:
Backing up [/root/.bashrc] to [/root/.bashrc.backup].
[/root/.bashrc] has been updated.
==> Start a new shell for the changes to take effect.
For more information on how to get started, please visit:
https://cloud.google.com/sdk/docs/quickstarts
root@instance-1:/home/cmdevio/expoted-data#
root@instance-1:/home# gcloud --version
gcloud --version
Google Cloud SDK 416.0.0
alpha 2023.01.30
beta 2023.01.30
bq 2.0.84
bundled-python3-unix 3.9.16
core 2023.01.30
gcloud-crc32c 1.0.0
gsutil 5.19
====
作業用フォルダの作成
root@instance-1:/home# mkdir cmdevio
bqコマンドを利用出来るようにするための初設定
サービスアカウントキーの作成を実施。
サービスアカウントキーの認証
root@cmdevio:/home/cmdevio# gcloud auth activate-service-account \ > xxxxxxxxxxxxxx@xxxxxxxxxxxxxxxxx.iam.gserviceaccount.com \ > --key-file /home/cmdevio/xxxxxxxxxxxxxxxxx.json \ > --project cm-marketing-gcp Activated service account credentials for: [xxxxxxxxxxxxxx@xxxxxxxxxxxxxxxxx.iam.gserviceaccount.com] root@cmdevio:/home/cmdevio#
認証内容の確認
# gcloud auth list
Credentialed Accounts
ACTIVE ACCOUNT
xxxxxxxxxxxxxxxxx-compute@developer.gserviceaccount.com
* xxxxxxxxxxxxxx@xxxxxxxxxxxxxxxxx.iam.gserviceaccount.com
To set the active account, run:
$ gcloud config set account `ACCOUNT`
# gcloud projects list
PROJECT_ID NAME PROJECT_NUMBER
xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxx
実行スクリプトの作成
実行コードとしてシェルスクリプトを用意。下記はそのサンプルコードです。対象テーブルについては「cmdevio」スキーマの「load_wp_posts」というテーブルを所定のBigQuery環境に用意した上で、投入対象先として指定しています。
## (1). 投入データを別環境から取得. wget https://xxxxxxxxxxxxxxxx/xxxxxxxxxxxx/tsv/vt_posts.tsv.gz -O /home/cmdevio/exported-data/vt_posts.tsv.gz ### (2). ロード用テーブルの削除&再作成/データ投入. bq rm -t -f cmdevio.load_wp_posts bq mk --table \ --description "生データ:投稿データ本体" \ cmdevio.load_wp_posts \ post_id:INTEGER,\ post_author:INTEGER,\ post_date:TIMESTAMP,\ post_title:STRING,\ post_status:STRING,\ post_name:STRING,\ post_modified:TIMESTAMP,\ post_type:STRING ## (3). データ投入. gunzip -f /home/cmdevio/exported-data/vt_posts.tsv.gz bq load \ --location=asia-northeast1 \ --source_format=CSV \ --field_delimiter="\t" \ --skip_leading_rows=1 \ --allow_quoted_newlines \ cmdevio.load_wp_posts \ /home/cmdevio/exported-data/vt_posts.tsv \ post_id:INTEGER,\ post_author:INTEGER,\ post_date:TIMESTAMP,\ post_title:STRING,\ post_status:STRING,\ post_name:STRING,\ post_modified:TIMESTAMP,\ post_type:STRING ## (4). 処理完了のSlack通知. curl -X POST 'https://slack.com/api/chat.postMessage' \ -d 'token=xoxp-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' \ -d 'channel=#(Slackチャンネル名)' \ -d 'text=bqコマンドによるデータ投入処理:完了'
(4).の「処理完了のSlack通知」に関しては、下記エントリで作成したSlack APIの情報を活用しています。
スケジュール稼働設定
今回用意した処理と環境は実行タイミングが限られているため(1日1回動けば良い)、Google Compute Engineの起動＆停止スケジュールで稼働時間を制限し、その時間下でcron駆動による処理実行を指定しました。
cronによる実行時間指定：
# crontab -l ---- 10 6 * * * /home/cmdevio/copy-data-into-bigquery.sh
上記実行時間をカバーする形でインスタンスの起動＆停止時間をスケジューリング：
※参考
実行(のイメージ)
これらの設定状況、以下の処理が実行されます。
- GCEインスタンススケジュールにより対象のインスタンスが起動
- GCEインスタンスのcron設定により、所定のシェルスクリプトが実行される
- 所定のシェルスクリプトの実行によりデータがBigQuery環境に連携され、最後にSlack通知が実行される
- GCEインスタンススケジュールにより対象のインスタンスが停止
※メッセージ文言は若干異なりますが、Slackチャンネルへの通知イメージです。
まとめ
という訳で、「Google Compute Engineインスタンス環境からbqコマンドを使ってデータをBigQueryに連携する」手順に関する紹介でした。
今回のエントリも下記同様「既存実施していた処理の置き換え」をメインとした内容となっています。地道にスクリプトを組んだ手順となっていますがもっと良い感じの、よりスマートな選択肢もあるかなと思っていますので今後は「こういう処理をどういう手法で置き換えられるか？」という部分についても展開していければと思います。
