1. はじめに
こんにちは、(株)日立製作所、クラウドビジネス推進センタの富田です。
この記事は、Japan APN Ambassador Advent Calendar 2021のエントリになります。
気づけば年の瀬、今年もあっという間の一年でした。思いおこせば昨年の12月、AWS主催のイベントにて「来年は、AIとビッグデータ頑張ります！」と宣言しつつも、日々の業務に追われ、AIもビッグデータも特段触れることもなく今年も終わるかと思っていたのですが、先日「Power BIとRDSの接続に問題ないか教えて欲しい」との問合せが突如、私の部署へ回ってきました。
本番での接続トラブルを抑えるには、基本的にはBIツールもAWS純正のAmazon QuickSightをお使い頂くか、Power BIのデータソースとしてネイティブにサポートされているAmazon Redshiftを使って頂くのがセオリーです。とはいえ、お客様の中には古くから(社内で)使い慣れているPower BIを使い続けたいとのご要望もあり、全てを純正で揃えて貰うというのも難しく、悩ましい課題です。
こういった背景の下、本番への適用を考える上では、構成によりサポートがどこまで可能かの確認は必須なのですが1、本記事では「そもそも技術的に繋がるのか？」といった「技術的接続検証」部分にフォーカスして解説させて頂きます。
AWSからは下記ホワイトペーパが発行されており、基本的なシステム構成については本ドキュメントに沿って構築すれば問題ないはずです。
一点、このホワイトペーパにはインフラ部分の構成しか記載されていないため、ミドルウェア部分も含めもう少し補足説明があると親切です。そこで本記事では、ホワイトペーパの構成ベースに、実際にPower BIを使ったレポート作成や共有の仕方について説明したいと思います。
2. サマリ
- ODBCコネクタを使ってシステム構築を行えば、接続に問題はありませんでした(4章)。
- PostgreSQLコネクタを使うと、オンプレミスデータゲートウェイがうまく対応してないようで接続できませんでした。また、Power BI DesktopでPostgreSQLコネクタを使う場合も、AWS RDSのSSL/TLS証明書の設定が必要です(5章)。
2021.12月執筆時点の情報になります。Power BIも、オンプレミスデータゲートウェイも、継続的に機能追加・改善されていますので、今後はこのような手間も不要になるかもしれません。
3. 全体像
全体図はこんな感じです。Power BIはいくつかのコンポーネントを束ねたスイートになっており、次のような役割分担になっています:
- Power BI Desktop: レポートの作成を行います。データソースがPower BIをインストールしたPCから、ネットワーク上見える(接続できる)所にある必要があります。
- Power BIサービス: Power BI Desktopで作成されたレポートから、ダッシュボードを作成したり、他のメンバと共有したりするためのSaaSサービスです。Power BI Desktopで作成したレポートはファイル(.pbix)として保存できる為、Power BIサービスがなくてもレポートの共有などはファイルを通して可能です。Power BIサービスを使う事で、ダッシュボードを作成したり、モバイルからの利用ができたり、データを定期的に更新するといった事ができるようになります。
- オンプレミスデータゲートウェイ: オンプレミス環境(ここではAWS VPCのプライベートサブネット)のデータと、Power BIサービス間においてセキュリティで保護されたデータ転送を行うためのコンポーネント(ブリッジゲートウェイ)になります。
Power BI Desktop、Power BIサービス、オンプレミスデータゲートウェイはいずれも無償版が提供されており、今回の検証も無償の範囲で実施しています。一点、Power BIサービスの利用には(Azure ADメンバによる)サインアップが必要ですのでご注意下さい。
図は、次の記事を参考に構成しました。
4. ODBCコネクタ活用手順
本章では、ODBCコネクタを活用した手順を説明します。
- レポート作成
- レポート発行
- データ更新
4-1. レポート作成
はじめに、RDSへ接続するためにODBCの設定を行います。ODBCドライバを、下記サイトからダウンロードします。
ダウンロードしてきたインストーラを用いて、ODBCドライバをOSへインストールします。ODBCドライバが無事インストールできたら、設定に移ります。検索ウインドウに「ODBC」と入力し、ODBCセットアップ画面を起動します(64bit版を選択)。ODBCセットアップ画面で[System DNS]タブを選択し、[Add]で「PostgreSQL(Unicode)版」を選びます。
下記RDSの接続情報を入力します:
- Data Source: 好きな名前 (例)「aws-rds」
- Database: DB名 (例)「dvdrental」
- Server: RDSのエンドポイント (例)「<>.<<ランダム>>.ap-northeast-1.rds.amazonaws.com」
- User Name: DBユーザ名 (例)「masteruser」
- Password:DBパスワード (例)「******」
ODBCの設定が完了したら、[Test]でRDSへの接続を確認しておきます。以上でODBCのインストール、セットアップは完了です。
次に、Power BI Desktopを用いてレポートを作成します。Power BI Desktopを起動し、データソースの設定を行います。
先に設定したODBCのData Sourceを選択します。
ODBC経由でRDSからデータが取得されています。Power BI Desktopに取り込むデータ(テーブル)を選択します。Power BI Desktop内には「データセット」というオブジェクトにデータがキャッシュされます。
データセットをベースに、レポートを作成します。
以上で、レポート作成が完了です。
4-2. レポート発行
Power BI Desktopから、Power BIサービスへレポートを発行します。[ホーム]->[発行]をクリックします。Power BIサービスのアカウント情報を入力します。最後に、格納するワークスペースを選択すれば完了です。
ブラウザから、発行されたレポートを確認しましょう。Power BIサービス(SaaS)上でも簡単なレポート編集は可能です。
4-3. データ更新
今回のシステムでは、RDSはインターネット非公開(private subnet)であるため、Power BIサービスとの連携にオンプレミスデータゲートウェイと呼ばれるコンポーネントが必要です。下記のサイトよりダウンロードできます。「個人モード」と「標準モード」がありますが、今回は「標準モード」を使いました。「標準モード」はサービスとして実行されますが、「個人モード」はアプリとして実行され、他ユーザとの共有ができないようです。
オンプレミスデータゲートウェイのインストール、セットアップが完了したら、ブラウザでPower BIサービスへログインし、連携できているか確認できます。
発行されたレポートには、付随する「データセット」があります。このデータセットを更新(RDSと同期)する事で、最新のデータが確認できるようになります。発行直後では、ODBC経由での接続情報(認証情報)が連携できていないため、再設定を行います。
データセットを手動更新してみます、無事更新できました！スケジュール更新の設定も可能です。
5. PostgreSQLコネクタ活用手順
4章の手順により、ODBCコネクタ経由でRDSは使えるのですが、折角なのでPostgreSQLコネクタからRDSへの接続について補足します。(2021.12月時点では)少し工夫が必要のようです。デフォルト状態でRDSへ接続しようとすると「接続できません」とエラーではじかれます。パケットキャプチャで色々試行錯誤の末、どうやらRDSへのSSL/TLS接続で失敗しているようでした。
そこでAWSのドキュメント「SSL/TLS を使用した DB instance への接続の暗号化」に従い、証明書を設定してみます。Windows Serverを使っているので、証明書のフォーマットに「PKCS7」を選択します。後は、Windows Serverの機能を用いて、ダウンロードした証明書(バンドル)をインストールします。証明書は「Trusted Root Certification Authorities」へ置きました。
念のために確認してみます。ちゃんとインストールされています。
この状態で、再度Power BI DesktopのPostgreSQLコネクタを使うと、無事接続できました。
一方、オンプレミスデータゲートウェイがインストールされているOS(Windows Server)に同様に証明書をインストールしても、Power BIサービスからのデータ更新は失敗してしまいました。Power BIサービスを使った「データ更新」が必要であれば、Power BI Desktopと合せてODBCコネクタで統一した方が無難みたいです。
6. まとめ
この記事では、AWSホワイトペーパーにあるRDSとPower BIの接続について試してみました。ODBCを使う事で、レポートの作成、発行、データ更新が一通り可能である事が検証できました。どなたか同じような所でつまづいている方のお役に立てれば幸いです。
- Power BIは、米国Microsoft Corporationの米国およびそのほかの国における商標または登録商標です。
- その他、本資料に記述してある会社名、製品名は、各社の登録商品または商標です。
付録 RDS(PostgreSQL)のサンプルデータ準備
今回は、サンプルデータとしてこちら「DVD rental database」を利用しました↓
(1)データベース作成
#RDSへ接続
Server [localhost]: <<DB名>>.<<ランダム>>.ap-northeast-1.rds.amazonaws.com
Database [postgres]: pgdb
Port [5432]:
Username [postgres]: masteruser
Password for user masteruser:
psql (12.9, server 12.6)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
#dvdrentalという名のデータベース作成
pgdb->create database dvdrental;
pgdb-> \l
List of databases
Name|Owner|Encoding|Collate|Ctype|Access privileges
dvdrental|masteruser|UTF8|en_US.UTF-8|en_US.UTF-8|
(中略)
(6 rows)
(2)データレストア
#サンプルをダウンロードしてきたディレクトリで解凍
unzip dvdrental.zip
#解凍ディレクトリへ移動
cd dvdrental
#PosdgreSQLのレストアコマンド実行
pg_restore -U masteruser -d dvdrental -h <<DB名>>.<<ランダム>>.ap-northeast-1.rds.amazonaws.com ./dvdrental.tar
(3)(レストア済みテーブル)確認
#RDSへ接続
Server [localhost]: <<DB名>>.<<ランダム>>.ap-northeast-1.rds.amazonaws.com
Database [postgres]: dvdrental
Port [5432]:
Username [postgres]: masteruser
Password for user masteruser:
psql (12.9, server 12.6)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
dvdrental-> \dt;
public|actor|table|masteruser
public|address|table|masteruser
public|category|table|masteruser
public|city|table|masteruser
public|country|table|masteruser
public|customer|table|masteruser
public|film|table|masteruser
public|film_actor|table|masteruser
public|film_category|table|masteruser
public|inventory|table|masteruser
public|language|table|masteruser
public|payment|table|masteruser
public|rental|table|masteruser
public|staff|table|masteruser
public|store|table|masteruser
-
MSサポートの話では、(現時点で)Power BIのサポートデータソースとしてリスト化している「PostgreSQL」は、EC2などIaaS上にPostgreSQLシステムを構築したケースを指すとの事です。従って、RDSを使う場合、どの程度(問題切分けなど)サポート可能かは確認が必要になります。 ↩