AthenaのJDBCドライバを使ってS3のデータにSQL Workbench経由でアクセスする #reinvent #athena

AWS re:Invent 2016で発表された新サービス『Amazon Athena』。発表以後、弊社ブログエントリでも早速検証エントリが上がってきています。下記エントリではJDBC接続をプログラムベースで実践していましたが、JDBCドライバを使えばSQLのツールからでもアクセスを行う事が可能です。そこで当エントリではAWSの公式ドキュメントでも展開されているSQLツール『SQL Workbench』を使ってそのアクセス設定方法について試してみたいと思います。

• Amazon Athena に JDBC 接続する ｜ Developers.IO

Athena JDBCドライバ

Amazon AthenaではJDBCドライバが提供されており、このドライバを使う事で、SQL Workbench等の一般的なサードパーティツールを使ってAthenaに接続し、Athenaに対してクエリを実行する事が出来るようになります。

AWS管理コンソールでAthenaに対して複数のクエリを実行する事は出来ませんが、JDBCドライバを使うと複数のクエリを実行するスクリプトを組む事が出来ます。デフォルトでは、アカウントから同時に5つのクエリを実行する事が出来ます。上限緩和申請でこの制限値を増やす事は出来るようです。

ドライバのダウンロード

JDBCドライバの入手先URLは以下となります。

• https://s3.amazonaws.com/athena-downloads/drivers/AthenaJDBC41-1.0.0.jar

今回はAWS CLIを使って以下の形でローカル端末にダウンロードしてきました。

$ aws s3 cp s3://athena-downloads/drivers/AthenaJDBC41-1.0.0.jar /Users/xxxxxxxx/Desktop/

JDBC URLフォーマット及びクラス名

JDBC接続の際のフォーマットは以下の形式となります。

また、現在利用(設定)可能なリージョンはus-east-1とus-west-2のみとなっているようです。

jdbc:awsathena://athena.REGION.amazonaws.com:443

また、JDBCドライバクラス名はcom.amazonaws.athena.jdbc.AthenaDriverとなります。

認証(Credentials)

AthenaやAmazon S3バケット等のAWSサービスやリソースにアクセスするには認証情報が必要となります。Javaでの利用を行う場合はAWSCredentialsProviderインタフェースを使用します。詳細は下記エントリをご参照ください。

• Java Code Example com.amazonaws.auth.AWSCredentialsProvider

SQL Workbench等のBIツールでの利用の場合、AWSアクセスキーとAWSシークレットアクセスキーを、ユーザー名とパスワードとしてJDBCプロパティに設定する方法があります。

JDBCドライバ 設定オプションについて

JDBCドライバの設定では、以下の値について設定が可能となっています。

プロパティ名	説明	デフォルト値	必須
s3_staging_dir	クエリ実行結果が書き込まれるS3の場所。JDBCドライバはAthenaに結果を読みとるために問い合わせを行い、ユーザーにデータ行を戻すよう要求します。	N/A	Yes
aws_credentials_provider_class	AWSCredentialsProviderインタフェースを実装する認証プロバイダクラス名	N/A	No
aws_credentials_provider_arguments	資格プロバイダコンストラクタの引数をカンマ区切りの値として指定します。	N/A	No
max_error_retries	JDBCクライアントがAthenaに要求を試みる最大リトライ回数を指定。	10	No
connection_timeout	接続確認切断前にAthenaへの接続を確立するための最大時間(ミリ秒単位)。	10,000	No
socket_timeout	Athenaにデータを送信するためにソケットを待機する最大時間(ミリ秒単位)	10,000	No
retry_base_delay	Athenaへの再試行の試行間の最小遅延量(ミリ秒単位)	100	No
retry_max_backoff_time	Athenaに接続しようとする再試行の最大遅延時間(ミリ秒単位)	100	No
log_path	DBCドライバ・ログのローカル・パス。 ログ・パスが指定されていない場合、ログ・ファイルは作成されません	N/A	No
log_level	JDBCドライバ・ログのログ・レベル。 有効な値は以下の通り。 INFO DEBUG WARN ERROR ALL OFF FATAL TRACE	N/A	No

Amazon AthenaへのSQL Workbench経由でのJDBCドライバ接続:実践

まずはツールの入手。下記サイトからダウンロードします。今回検証したのはMac OSX版でした。

• SQL Workbench/J -  Home

ダウンロードしたら起動。

起動時に接続プロファイルの設定画面が起動しますので、画面左下の『Manage Drivers』をクリック。

ドライバ管理画面に進みますので、左上の新規作成ボタンを押下後、任意の名称設定(Name)、ダウンロードしたJDBCドライバを選択、Sample URLをルールに則った語りで記載し、[OK]押下。

ユーザー名とパスワードの部分にAWSアクセスキーとAWSシークレットアクセスキーを設定し、[Extended properties]を押下。

オプション設定のうち、必須となっているs3_staging_dirについての準備を行います。まずはバケットを作成。

そして[Extended properties]で設定する項目にProperty:Valueのペアで値を登録しておきます。

これで準備完了。テーブル定義を行い、

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
   `Date` DATE,
   Time STRING,
   Location STRING,
   Bytes INT,
   RequestIP STRING,
   Method STRING,
   Host STRING,
   Uri STRING,
   Status INT,
   Referrer STRING,
   os STRING,
   Browser STRING,
   BrowserVersion STRING
   ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
   WITH SERDEPROPERTIES (
   "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
   ) LOCATION 's3://athena-examples/cloudfront/plaintext/';

クエリを実行すると、

以下の様な形でバケット配下に結果が出力されました。

ファイル群のうち、拡張子CSVのファイルを確認してみるとちゃんと結果が出力されていました。

まとめ

という訳で、JDBCドライバを使ってツール経由でS3のデータをクエリ実行してみたエントリでした。S3のデータをこんな形で楽々見られるというのはお手軽感があって良いですね。リリース内容によるとQuickSightとも連動するという事らしい(現時点ではドキュメントに関連する記述はありませんでした)ので、ぜひとも今後に期待したいところですね。こちらからは以上です。

参考情報：

• Accessing Amazon Athena with JDBC — User Guide