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

eyecatch_athena

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

Athena JDBCドライバ

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

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

ドライバのダウンロード

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

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

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

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

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

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

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

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

認証(Credentials)

AthenaやAmazon S3バケット等のAWSサービスやリソースにアクセスするには認証情報が必要となります。Javaでの利用を行う場合は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版でした。

athena-sqlworkbench_01

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

athena-sqlworkbench_02

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

athena-sqlworkbench_03

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

athena-sqlworkbench_04

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

athena-sqlworkbench_05

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

athena-sqlworkbench_06

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

athena-sqlworkbench_07

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

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/';

athena-sqlworkbench_08

クエリを実行すると、

athena-sqlworkbench_09

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

athena-sqlworkbench_10

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

athena-sqlworkbench_11

まとめ

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

参考情報: