Power BI DesktopからAmazon AthenaにODBC接続してみた

こんにちは、CX事業本部の若槻です。

MicrosoftのBIツールであるPower BIから、AWSのデータクエリ実行機能であるAmazon Athenaに接続して取得したデータを利用したい場合は、Power BIのデスクトップ版であるPower BI DesktopからODBC（Open Database Connectivity）による接続を行う必要があります。

• Power BI Desktop でジェネリック インターフェイスを使用してデータに接続する - Power BI | Microsoft Docs

そこで今回は、Power BI DesktopからAmazon AthenaにODBC接続してみました。

環境

• PC：Amazon WorkSpaces
  • バンドル：Standard with Windows 10 (PCoIP)
  • コンピューティングタイプ：Value - 1 vCPU、 2 GiB メモリ
• Power BI Desktop：2.87.1061.0 64-bit

やってみた

データソースの準備

今回Power BI Desktopから接続するデータソースとなるGlueデータカタログとAthenaは以下記事を参考に設定済みとなります。

• AWS Glueデータカタログへのテーブルの作成とAthenaでのクエリ実行をAWS CLIでやってみた | Developers.IO

今回は次のようなデータを使用します。

rank,name,attribute
1,hojo,Co
2,sagisawa,Co
3,ichinose,Cu
4,kamiya,Co
5,takagaki,Co

IAMユーザーの準備

Athenaへ接続可能なIAMユーザーを作成します。このユーザーの認証情報はODBCドライバ設定時に使用します。

IAMユーザーを作成

• create-user — AWS CLI 1.18.185 Command Reference

% username=athena-odbc-user
% aws iam create-user --user-name $username

S3とAthenaへのアクセス権限をIAMユーザーに付与

• Attaching an IAM managed policy to an IAM user - AWS Command Line Interface

今回はてっとり早く管理ポリシーのAmazonAthenaFullAccessとAmazonS3FullAccessをアタッチしますが、権限は必要に応じて絞ってください。

% aws iam attach-user-policy --user-name $username --policy-arn arn:aws:iam::aws:policy/AmazonAthenaFullAccess
% aws iam attach-user-policy --user-name $username --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess

IAMユーザーのアクセスキーを作成

• create-access-key — AWS CLI 1.18.190 Command Reference

% aws iam create-access-key --user-name $username

実行結果からアクセスキーIDAccessKeyIdとシークレットアクセスキーSecretAccessKeyの値を控えます。

ODBCドライバーの設定

ここからはPower BI Desktopを使用するWindows端末上での操作となります。（インストールを完了するためにPCの再起動を求められます）

ドライバーのインストール

下記URLにアクセスします。Amazon Athena向けのODBCドライバーのダウンロードページが開きます。

• Connecting to Amazon Athena with ODBC - Amazon Athena

Windows用ODBCドライバーのインストーラー（今回は64bit版）をクリックしてダウンロードし、実行します。

インストールウィザードが開始します。[Next]をクリック。

ライセンス条項を一読しチェックボックスにチェックを入れて同意し、[Next]をクリック。

インストール先に問題がなければ[Next]をクリック。

[Install]をクリックしてインストールを開始します。

UACの許可を求められるので[はい]をクリック。

インストールの完了にPC再起動が必要になるが良いかダイアログで聞かれるので、[OK]をクリック。

セットアップが完了したら[Finish]をクリックしてウィザードを完了させます。

ダイアログでPCを再起動して良いか聞かれるので、[Yes]をクリックして再起動を開始します。

PCが再起動したらODBCドライバのインストールは完了です。

Athenaへの接続設定

インストールしたODBCドライバの管理アプリを起動します。

管理アプリが開きます。Athenaをデータソースとして追加します。[ユーザーDSN]タブで[追加]をクリック。

[データソースの新規作成]ダイアログが開くので、データソースのドライバーとしてSimba Athena ODBC Driverを選択し、[完了]をクリック。

DSN設定ダイアログが開くので、下記を設定し、[Authentication Options]をクリック。

• Data Source Name：適当なデータソース名
• AWS Region：接続先のAthenaのAWSリージョン
• WorkGroup：Athenaへの接続に使用するワークグループ

[Authentication Options]が開くのでAthenaへ接続するための認証情報を指定します。[Authentication Type]でIAM Credentialsを選択し、[User]で先程控えたアクセスキーID、[Password]でシークレットアクセスキーを指定し、[Ok]をクリック。

[Test]をクリックしてAthenaへの接続テストを開始します。

下記のようにダイアログでSuccessfully connected to data source!と表示されれば接続成功です。[OK]をクリックしてダイアログを閉じます。

ちなみに、シークレットアクセスキーが誤っている場合は接続テストの結果は以下のようになります。

FAILED!

[Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 21, HTTP Response Code: 400, Exception Name: InvalidSignatureException, Error Message: The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details. [Execution ID: ]

IAMユーザーのS3バケットへのアクセス権限が足りない場合は接続テストの結果は以下のようになります。クエリ結果がS3バケットに作成できずエラーとなっています。（下記はAmazonS3FullAccessポリシーがアタッチされていない場合）

FAILED!

[Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Error Message: Access denied when writing to location: s3://<バケット名>/query-result/b6b3c208-9fa4-4b8f-9f00-4d4ca7a46331.txt [Execution ID: b6b3c208-9fa4-4b8f-9f00-4d4ca7a46331]

[OK]をクリックしてセットアップを完了します。

データソースが追加されました。[OK]をクリックして管理アプリを終了します。

これでODBCドライバでのAthenaへの接続設定ができました。

Power BI Desktopから接続してみる

先程作成したODBCドライバのデータソースを使用してPower BI DesktopからAthenaに接続してみます。

Power BI Desktopを起動します。[ホーム]タブで[データを取得]をクリック。

[データを取得]ダイアログで[その他] - [ODBC]を選択し、[接続]をクリック。

[ODBC からインポート]ダイアログが開きます。[データソース名]で先程作成したデータソースを選択し、[OK]をクリック。

[ODBC ドライバー]ダイアログが開きます。[既定またはカスタム]タブを開き、[接続]をクリック。

[ナビゲーター]ダイアログが開きます。ODBC (dsn=<今回作成したdsn名>) - AWSDataCatalog - <データベース名>を開き、分析したいテーブル名のチェックボックスにチェックを入れて選択。するとデータが取得されてプレビューされます。[読み込み]をクリック。

Athenaからのデータの読み込みが行われ、完了すると[フィールド]にテーブルとフィールドが追加されます。[視覚化]で今回は積み上げ横棒グラフを選択します。

フィールドをセットすると、Athenaから取得したデータをグラフで可視化できました。

データソースの更新を反映する

データソース（S3上のファイル）が更新され、下記データが追加されたとします。

rank,name,attribute
6,takamori,Pa
7,sakuma,Cu
8,nitta,Co
9,ogata,Cu
10,hayami,Co

Power BI Desktopで更新したいデータソースを開き、[フィールド]でテーブル名の右横をクリックし、メニューで[データの更新]をクリック。

するとデータの更新が行われ、データソースの更新がグラフに反映されました。

補足

クエリ実行結果の保存場所について

Athenaによるクエリ実行結果の保存場所は、通常はODBCドライバでAthenaで接続する際はURLのS3OutputLocationプロパティに指定した場所となりますが、今回はAthenaのワーキンググループでOverride client-side settingsオプションを有効にしているためワーキンググループの既定の保存場所が使われます。よってドライバセットアップ時の保存場所の指定場所は不要でした。

The query result location that Athena uses is determined by a combination of workgroup settings and client-side settings. Client-side settings are based on how you run the query.

If you use the ODBC or JDBC drivers to run queries, the S3OutputLocation property specified in the connection URL determines the client-side setting.

Each workgroup configuration has an Override client-side settings option that can be enabled. When this option is enabled, the workgroup settings take precedence over the applicable client-side settings when an IAM principal associated with that workgroup runs the query.

• Working with Query Results, Output Files, and Query History - Amazon Athena

おわりに

Power BI DesktopからAmazon AthenaにODBC接続してみました。

今回の方法によりデータレイクの仕組みはAWS上にあるが、BIツールはPower BIを使いたい、という場合に対応できそうで良かったです。

参考

• How to Connect to an ODBC Data Source From Power B... - Microsoft Power BI Community
• AWS AthenaでODBC接続が可能になりました | Developers.IO

以上