この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
Amazon Athenaのクエリ結果をチーム内で共有する場合、クエリ結果を表やグラフ形式でビジュアライズできると便利です。この場合Amazon Athena自身にはビジュアライズの機能がないため、他プロダクトの力を借りる形になります。
弊社ブログでもAmazon QuickSightとTableauでAmazon Athenaのクエリ結果をビジュアライズする方法が紹介されています。
- 【新機能】Amazon QuickSightがAmazon Athenaをサポート! | Developers.IO
- Tableau 10.3新機能:Amazon Athenaへ接続 #tableau | Developers.IO
OSSのプロダクトという観点では、Redashも有力な選択肢になるかと思います。 今回はこのRedashでAmazon Athenaに接続しクエリ結果をビジュアライズする方法をご紹介します。
RedashのAmazon Athenaサポート
Redashのv1.0では、Amazon Athenaに接続するためにRedash本体とは別にProxyが必要でした。
v2.0.0ではデフォルトのクエリランナーとしてAmazon Athenaがサポートされるようになり、Proxyは不要、Redash本体から直接Amazon Athenaにクエリを実行できるようになりました。非常に嬉しいアップデートです。
Redashのセットアップ
今回は手元のmacOS上で、Dockerを使ってRedashの環境をセットアップします。 AWSであればAmazon EC2向けのAMIが用意されていますのでこちらを利用する方法もあります。
環境
- OS : macOS Sierra v10.12.6
- Docker : Community Edition v17.06.0
- Redash : v2.0.0
Redashの依存コンポーネント
Redashは以下のコンポーネントに依存しています。今回はこれら一式をDocker Composeで起動します。
- Redis
- Postgresql
- Nginx
docker-compose.yml
Redashの公式リポジトリにdocker-compose.ymlのサンプルがあるので、これを利用します。
ディレクトリ構成
.
├── .env
├── docker-compose.yml
└── postgres-data
.envファイル
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
POSTGRES_DB=postgres
REDASH_COOKIE_SECRET=veryverysecret;
docker-compose.yml
version: '2'
services:
server:
image: 'redash/redash:latest'
command: server
depends_on:
- postgres
- redis
ports:
- '5000:5000'
environment:
PYTHONUNBUFFERED: 0
REDASH_LOG_LEVEL: INFO
REDASH_REDIS_URL: 'redis://redis:6379/0'
REDASH_DATABASE_URL: 'postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}'
REDASH_COOKIE_SECRET: ${REDASH_COOKIE_SECRET}
REDASH_WEB_WORKERS: 4
ATHENA_ANNOTATE_QUERY: 'false'
worker:
image: 'redash/redash:latest'
command: scheduler
environment:
PYTHONUNBUFFERED: 0
REDASH_LOG_LEVEL: INFO
REDASH_REDIS_URL: 'redis://redis:6379/0'
REDASH_DATABASE_URL: 'postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}'
QUEUES: 'queries,scheduled_queries,celery'
WORKERS_COUNT: 2
ATHENA_ANNOTATE_QUERY: 'false'
redis:
image: 'redis:3.0-alpine'
postgres:
image: 'postgres:9.5.6-alpine'
volumes:
- './postgres-data:/var/lib/postgresql/data'
environment:
- POSTGRES_USER=${POSTGRES_USER}
- POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
- POSTGRES_DB=${POSTGRES_DB}
nginx:
image: 'redash/nginx:latest'
ports:
- '80:80'
depends_on:
- server
links:
- 'server:redash'
以下、サンプルからの変更点です。
- 以下の設定値は.envファイルから読み込む形に変更
- Redashのシークレットキー(セッション情報を暗号化するために利用)
- Postgresqlのユーザー名、パスワード、データベース名
- Postgresqlのデータを永続化するためにホスト(今回はmacOS)のディレクトリをPostgresqlのコンテナの「/var/lib/postPresql/data」にマウント
- Redashのserverコンテナとworkerコンテナの環境変数に
ATHENA_ANNOTATE_QUERY
を追加
ATHENA_ANNOTATE_QUERY
はRedashからCREATE TABLEなどのDDLを発行する場合に必要です。
RedashはDDLの先頭に以下のようなアノテーションを自動的に付加します。
/* Username: yawata.yutaka@example.com, Task ID: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX, Query ID: adhoc, Queue: queries, Query Hash: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX*/
/* */
形式のアノテーションはAmazon AthenaではParse Errorとなるため、ATHENA_ANNOTATE_QUERY
をfalseに設定しアノテーションが付加されないようにします。
SELECT文にはアノテーションは付加されないので、DDLはAmazon Athenaのマネージメントコンソールから実行、RedashからはSELECTのみといった運用であればATHENA_ANNOTATE_QUERY
の設定は不要です。
Redashのデータベースの初期化
Redashを利用する事前準備としてデーベースの初期化が必要です。
PostgresqlとRedisのコンテナを起動して、Redashのcreate_db
コマンドを実行します(Postgresqlのコンテナは「-d」
オプションを付けてバックグラウンドで起動しておきます)。
PostgreSQLコンテナの初回起動時はPostgreSQLのinit処理が実行されます。少し待ってからcreate_db
コマンドを実行しましょう。(手元の環境ではPostgreSQLに接続できるようになるまで15秒程度かかりました。)
$ docker-compose up -d postgres redis
$ docker-compose run --rm server create_db
Redashの起動
データベースの初期化が終わったら、続いてNginxとRedash(server + worker)のコンテナを起動します。
$ docker-compose up -d server worker nginx
2回目以降はdocker-compose up -d
で全コンテナをまとめて起動する方法で問題ありません。
$ docker-compose up -d
管理者ユーザー登録&ログイン
無事Redashのコンテナが起動できたら、Webブラウザで「http://127.0.0.1」にアクセします。 初回起動時に管理者ユーザーの登録を求められます。ID、メールアドレス、パスワード、組織名を入力し「Setup」をクリックします。
以下の画面が表示されればログイン成功です(画面中央下部でRedashのバージョンが確認できます)。
RedashのデータソースにAmazon Athenaを登録
RedashのデータソースとしてAmazon Athenaを登録します。はじめに、Amazon AthenaとS3へのアクセス許可を持ったIAMユーザーを作成します。
IAMユーザーの作成
IAMユーザーを作成し、以下のポリシーを割り当てます。また作成したIAMユーザーのアクセスキー、シークレットキーを控えておきます。
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"athena:BatchGet*",
"athena:Get*",
"athena:List*",
"athena:StartQueryExecution",
"athena:StopQueryExecution"
],
"Resource": [
"*"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetBucketLocation",
"s3:GetObject",
"s3:ListBucket",
"s3:ListBucketMultipartUploads",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload",
"s3:CreateBucket",
"s3:PutObject"
],
"Resource": [
"arn:aws:s3:::aws-athena-query-results-*"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetBucketLocation",
"s3:ListBucket"
],
"Resource": [
"arn:aws:s3:::athena-examples-ap-northeast-1"
]
},
{
"Effect": "Allow",
"Action": [
"s3:GetObject"
],
"Resource": [
"arn:aws:s3:::athena-examples-ap-northeast-1/cloudfront/plaintext/*"
]
}
]
}
aws-athena-query-results-*
はAmazon Athenaがクエリ結果を保存するS3バケットです(デフォルトでは「s3://aws-athena-query-results-<AWSアカウントID>-<リージョン>/」)。
このバケットに対して適当な権限を付与します。
クエリ結果の保存先S3バケットの設定はAmazozn Athenaのマネージメントコンソールの「Settings」から確認できます。
またクエリ対象のデータが格納されているS3バケットに対しても適当な権限を付与する必要があります。上記の例ではathena-examples-ap-northeast-1/cloudfront/plaintext/
がそれに該当します。
データソースの登録
Redashの画面に戻り、RedashのデータソースにAmazon Athenaを登録します。
- 「Data Sourses」ボタンをクリックします。
- 「New Data Sourse」をクリックします。
- 以下を入力/選択します。「Test connection」に成功すれば登録完了です。
- Type(Amazon Athenaを選択)
- Name(データソースにつける任意の名前)
- AWS Region(ap-northeast-1など)
- AWS Access Key
- AWS Secret Key
- S3 Staging Path(Amazon Athenaのクエリ結果の保存先S3バケット)
RedashからAmazon Athenaにクエリを実行
試しにAWSの公式ドキュメントにあるAmazon AthenaのGetting Startedに倣って、RedashからAmazon Athenaにクエリを実行しCloudFrontのサンプルログを分析してみます。
- 「Queries」-「New Query」をクリックします。
- データベースを作成します。
create database redash_sampledb
- テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS redash_sampledb.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-ap-northeast-1/cloudfront/plaintext/';
- OS別のリクエスト数を集計します。
SELECT os,
COUNT(*) COUNT
FROM redash_sampledb.cloudfront_logs
WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05'
GROUP BY os;
- ビジュアライズしてみます。「NEW VISUALIZATION」をクリックします。
- 以下の通り選択し「Save」をクリします。
- Chart Type:「Pie」
- X軸:「OS」
- Y軸:「COUNT」
- パイチャートが追加されました。
- スケジュールを設定することもできます。
- スケジュールは1回/日の時間指定、または「〜分毎」の指定ができます。
- クエリに名前をつけて保存します。
- ダッシュボードも作成できます。(「Dashboards」-「New Dashboard」をクリックします。)
- ダッシュボード名を入力して「Save」をクリックします。
- 「Add Widget」から、ダッシュボードに先ほど作成したパイチャートを追加します。
- クエリ名(request_count_by_os)を選択します。「Choose Visualization」で「Chart」を選択し「Add to Dashboard」をクリックします。
- ダッシュボードにパイチャートが追加されました。
まとめ
公式のDockerイメージを使うと手元の環境で比較的容易にRedashを起動することができます。
またRedashはAmazon Athena以外にも様々なデータソースに対応しているため、チーム内での情報共有はもちろんのこと、デイリーユースのクエリ作成&実行環境としても利用できそうです。