AWS IoT SiteWiseゲートウェイで収集した設備機器の稼働データを BigQuery Data Transfer Service で転送して BigQuery と Google データポータルで可視化してみた

以前の記事では、SiteWise ゲートウェイで収集した PLC のデータを QuickSightで可視化するということをやってみました。今回は BI ツールとして「Google データポータル」を試してみたのでご紹介します。

全体の構成

今回、Google データポータルを使うためのに利用した構成は下記のとおりです。

OPC UA サーバーから IoT Analytics データセットの S3 バケットにデータを保存する所までは以前の記事と同様です。

Google データポータルのデータソースは BigQuery

データポータルがサポートしているデータソースは数多くありますが、下記の通り標準では S3 をサポートしていません。

S3 をデータソースとした下記のようなコミュニティコネクタは存在しますが、ユーザーの自己責任による利用となる課題があります。

一方で、データ分析を行う際は、データがあちこちに分散しているよりも同じ DWH に置きたいケースがあります。そのため今回はデータソースとして BigQuery を使うことにしました。
なお、データの保存先が S3 と BigQuery の2箇所になってしまいますが、今回はデータの用途を次のように区別することにして、ストレージが2重になることは許容することにしました。

• BigQuery はデータ分析用
• S3 のデータは AWS 上の他のアプリケーションなどから利用する用途

参考までに、先程紹介したコミュニティコネクタの利用方法については下記の記事などが参考になるかと思います。

BigQuery Data Transfer Service を利用して転送する

SiteWise ゲートウェイで収集したデータを BigQuery に転送する方法はいくつか思いつきますが、今回は自動で定期的に転送が可能な「BigQuery Data Transfer Service」を試してみることにしました。

一番手っ取り早く「お試し」でBigQuery にデータを入れるなら、GCP のコンソールから手動で直接 CSV ファイルをインポートするのが簡単です。（スキーマも自動設定できます）

GCP の事前準備

事前の準備として、下記ページに従って次の設定を済ませておきます。

• プロジェクトの作成と BigQuery API の有効化
• BigQuery Data Transfer Service の有効化
• bigquery.admin アクセス権の付与

AWS IAM の事前準備

「BigQuery Data Transfer Service」が S3 から CSVファイルを取得するために、IAM User を作成してアクセスキーとシークレットアクセスキーを取得しておきます。

権限は今回は下記の通りとしました。 （少し横着しているので必要に応じて、対象リソースを制限するなどしてください）

BigQuery の準備

データセットとテーブルの作成

次に、データを格納する BigQuery のデータセットやテーブルを作成します。
コンソールのメニューから「BigQUery」を選択します。

エクスプローラの画面で、該当プロジェクトから「データセット」を作成します。
（プロジェクトは作成済みとします）

データセットの作成画面が開くので適当な「データセットID」を設定します。
（今回は「sample_dataset」としました）

次に先程作成したデータセットに対してテーブルを作成します。

テーブル作成の画面で、該当の「データセット」を選択して、適当なテーブル名を設定します。
（今回は sample_table としました）

スキーマは S3 にある CSV ファイルの内容に合わせます。今回利用する IoT Analytics の SQL データセットのファイルは下記のような形になります。

"propertyAlias","value","timestamp","quality"
"/Factory/1/turbin/2/RPM","1426336687.0","2022-07-28 06:19:47.355","GOOD"
（以下略）

そのためスキーマは次の通り設定しました。

フィールド名	タイプ	モード
propertyAlias	STRING	NULLABLE
value	FLOAT	NULLABLE
timestamp	TIMESTAMP	NULLABLE
quality	STRING	NULLABLE

以上で テーブルの設定は完了です。

データ転送の設定

S3 に対するデータ転送の設定は基本的に下記のドキュメントのとおりです。

次は「データ転送（BigQuery Data Transfer Service）」の設定を行います。
この設定の中で CSV ファイルの URI を入力するので、事前に確認しておきましょう。

CSV ファイルの URI が確認できたら、データ転送の設定を行っていきます。
メニューより「データ転送」の画面を開きます。

「転送を作成」をクリックします

「ソース」は「Amazon S3」を選択します。

「転送構成名」は分かりやすいものを付けておきましょう。
「スケジュールオプション」についてですが、今回はテストということで「オンデマンド」を選択しました。定期的に自動で実行する場合はプルダウンから実施間隔を指定します。
（最短間隔は 24 時間です。）

次に「転送先の設定」を行います。

• データセット：先程作成したデータセット
• Destination table：先程作成したテーブル
• Amazon S3 URI：事前に確認した CSV ファイルの URI
• Access key ID：事前に作成した IAM User のアクセスキー
• Secret access key：事前に作成した IAM User のシークレットアクセスキー
• FIle Format：CSV

次の「Transfer Options」では、「Header rows to skip」を「1」にセットしてください。
デフォルトでは「0」ですが、デフォルトのままだとエラーになり正常に転送できません。

エラーになる原因は、対象の CSV ファイルのすべてのデータフィールドが文字列のヘッダー行となっているためです。
再掲になりますが IoT Analytics の SQL データセットとして出力される CSV ファイルは下記のようなデータ です。

"propertyAlias","value","timestamp","quality"
"/Factory/1/turbin/2/RPM","1426336687.0","2022-07-28 06:19:47.355","GOOD"
（以下略）

この場合、BigQuery は最初の行がヘッダーであると認識しないため、事前に設定したスキーマと異なるというエラーが発生します。
これを回避する為に、ヘッダー行だけをスキップさせる必要があります。

これは下記のドキュメントに記載があります。

「転送先の設定」で「保存」をクリックしたら設定完了です。
初めての場合は、下記のような画面が出るかもしれないので、適切なアカウントを選択しましょう。

続けて「BigQuery Data Transfer Service」の信頼を尋ねられるので「許可」を選択しておきましょう。

転送の実行

これで準備ができたので「今すぐ転送を実行」をクリックして転送を開始します。

次のような画面が出たら「OK」をクリックして転送スタートです。

転送の状況は該当のジョブを選択することで確認することができます。

次のように転送に成功した旨のメッセージが確認できればOKです。

転送されたデータの確認

正しくデータがテーブルに入っているか確認してみましょう。
メニューから「SQL ワークスペース」を選択します。

つぎのようなクエリを実行してデータが取得できることを確認します。

SELECT * FROM `iot-analytics-test-357807`.sample_dataset.sample_table LIMIT 1000

データポータルで可視化する

最後に BigQuery のデータをデータポータルで可視化してみます。
https://datastudio.google.com/ にアクセスしてデータポータルで「データソース」を作成します。

BigQuery をクリックします。

該当のデータが格納されているテーブルを「プロジェクト」「データセット」「テーブル」の順に選択して特定して「接続」します。

BigQuery の該当テーブルに接続できたら次のような画面が開きます。
ここでは、必要に応じてデータポータルで表示する際のデータタイプやデフォルトの集計種別を指定します。

今回は propertyAlias と value の2箇所を修正しました。
propertyAlias は URL ではなく製造機器のデータ名になるので「タイプ」を「テキスト」に変更します。
また可視化したいデータ値である value の「デフォルトの集計値」を「平均値」に変更しました。ここは分析したい内容に応じて変更してください。

修正できたら「レポートを作成」をクリックします。

次のような画面が出たら「レポートに追加」をクリックして先に進みます。

次のようなレポート画面が開くので、要件に応じた表示となるように変更していきます。

以前試した QuickSight の時と同じようなグラフにしみました。

今回は １つの CSV しかデータとして使っていないのでプロットされるデータ点数が少ないですが、1分間のデータの平均値が折れ線グラフとして可視化できています。

継続的にデータ転送する場合

今回は、1つの CSV ファイルを明示的に指定して、オンデマンドで転送しましたが「BigQuery Data Transfer Service」では、最短 24 時間間隔で継続的に転送を行うことができます。
その場合は、S3 URI の指定にワイルドカードを使うことで、継続的に生成される IoT Analytics の CSV ファイルを転送対象にすることができます。

しかしながら、IoT Analytics のデータは継続的に貯まり続けるので、このままでは BigQuery に重複したデータがどんどんインポートされてしまいます。
「BigQuery Data Transfer Service」を使う場合は、BigQuery 側で重複排除するか、転送対象となる S3 上のデータは常に増分データだけになるように工夫する必要がある点に注意しましょう。

下記は参考記事です。

BigQuery Data Transfer Service でハマった点

今回の作業では、個人的なハマリポイントが大きく2箇所ありました。

ハマりポイント1つ目

１つ目は既に上記の作業中に紹介したものですが、「BigQuery Data Transfer Service」の転送設定で「Header rows to skip」を1にセットする 点です。

デフォルト設定時のエラーの意味を正しく解釈できず、原因の特定に時間がかかりましたが、ドキュメントを見直すことで解消することができました。
転送するファイルの種類だけでなく、ファイルの中身の各フォーマットについてもしっかり確認しておきましょう。

ハマりポイント2つ目

２つ目は、転送対象の S3 URI が正しくても「No files found matching: "s3://...」というメッセージが出てデータ転送できなかった点です。

下記のログにあるとおり何も BigQuery に転送されていません。この場合、エラーにならないので転送ジョブのステータスは「成功」となる点にも注意です。

原因は2つ目のログにヒントがありました。
スクリーンショットにある「転送開始」の時刻は「2022年8月1日 3:44 UTC」となっています。
一方で2つ目のログには「2022-07-31T20:34:18-07:00 より以前の更新があったファイルを転送する」と書かれています。ログにある「2022-07-31T20:34:18-07:00」を UTC にすると「2022年8月1日 3:34:18 UTC」なので、「ファイルの更新時間が転送開始の時刻より10分以上後」のファイルが転送の対象になる、ということが分かります。

これを踏まえて、新規に S3 上に設置したファイルを転送したい場合は、ファイルがバケットに置かれてから短くても 10 分待機してから開始すると上手く転送できます。

これは下記のドキュメントにも記載がありました。

また、下記も合わせてご確認ください。

最後に

今回初めて BigQuery と データポータルを使ったので、上記のような点にハマったりして最初は上手くできませんでしたが、なんとか初歩的なことができるようになったかと思います。

データポータルは無料で利用できるので、もう少し深堀りして活用していきたいと思います。

以上です。

参考リンク