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

2020.12.05

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

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

そこで今回は、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は以下記事を参考に設定済みとなります。

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

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

IAMユーザーの準備

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

IAMユーザーを作成

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

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

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

% 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ユーザーのアクセスキーを作成

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

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

ODBCドライバーの設定

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

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

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

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

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

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

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

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

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

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

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

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

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

Athenaへの接続設定

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

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

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

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

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

image

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

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

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

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

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]をクリックしてセットアップを完了します。 image

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

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

Power BI Desktopから接続してみる

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

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

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

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

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

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

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

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

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

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

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

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

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

補足

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

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.

おわりに

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

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

参考

以上