Google Compute Engineインスタンス環境下でbqコマンドを使ってBigQueryにデータを投入する

しんや

2023.04.24

アライアンス統括部サービスグループのしんやです。

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コマンドを利用出来るようにするための初設定

サービスアカウントキーの作成を実施。

Google Cloud SDK をAmazon Linux 2環境にインストール(yum＋サービスアカウントキーの利用) | DevelopersIO

サービスアカウントキーの認証

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環境に用意した上で、投入対象先として指定しています。

copy-data-into-bigquery.sh

## (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に連携する」手順に関する紹介でした。

今回のエントリも下記同様「既存実施していた処理の置き換え」をメインとした内容となっています。地道にスクリプトを組んだ手順となっていますがもっと良い感じの、よりスマートな選択肢もあるかなと思っていますので今後は「こういう処理をどういう手法で置き換えられるか？」という部分についても展開していければと思います。

Google Compute Engineインスタンス環境下でbqコマンドを使ってBigQueryにデータを投入する

目次

データ連携イメージ(構成図)

Google Cloud:Compute Engine環境の初期設定

インスタンスのrootパスワードを設定

wgetコマンドのインストール

gcloudコマンドのインストール

作業用フォルダの作成

bqコマンドを利用出来るようにするための初設定

サービスアカウントキーの認証

認証内容の確認

実行スクリプトの作成

スケジュール稼働設定

実行(のイメージ)

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

Google Compute Engineインスタンス環境下でbqコマンドを使ってBigQueryにデータを投入する

目次

データ連携イメージ(構成図)

Google Cloud:Compute Engine環境の初期設定

インスタンスのrootパスワードを設定

wgetコマンドのインストール

gcloudコマンドのインストール

作業用フォルダの作成

bqコマンドを利用出来るようにするための初設定

サービスアカウントキーの認証

認証内容の確認

実行スクリプトの作成

スケジュール稼働設定

実行(のイメージ)

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

「【4/17(水)ハイブリッド】クラスメソッドGoogle Cloud Next ’24ふり返り勉強会」に登壇しました

Google Cloud Next’24で発表されたBigQueryのアップデート情報をまとめました

【新機能】BigQuery data canvasを早速触ってみた #GoogleCloudNext

【速報】Google Cloud NextでGemini in BigQueryが追加されました

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩