RedashでAmazon Athenaのクエリ結果をビジュアライズする

eyecatch_athena

Amazon Athenaのクエリ結果をチーム内で共有する場合、クエリ結果を表やグラフ形式でビジュアライズできると便利です。この場合Amazon Athena自身にはビジュアライズの機能がないため、他プロダクトの力を借りる形になります。

弊社ブログでもAmazon QuickSightとTableauでAmazon Athenaのクエリ結果をビジュアライズする方法が紹介されています。

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_DB}/postgres'
      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_DB}/postgres'
      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ファイルから読み込む形に変更
    • クッキーのシークレットキー
    • 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」をクリックします。

Athena+Redash-1

以下の画面が表示されればログイン成功です(画面中央下部でRedashのバージョンが確認できます)。

Athena+Redash-2

RedashのデータソースにAmazon Athenaを登録

RedashのデータソースとしてAmazon Athenaを登録します。はじめに、Amazon AthenaとS3へのアクセス許可を持ったIAMユーザーを作成します。

IAMユーザーの作成

IAMユーザーを作成し、以下のポリシーを割り当てます。また作成したIAMユーザーのアクセスキー、シークレットキーを控えておきます。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "athena:BatchGetQueryExecution",
                "athena:GetQueryExecution",
                "athena:GetQueryResults",
                "athena:ListQueryExecutions",
                "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のマネージメントコンソールから確認できます。

Athena+Redash-5

Athena+Redash-6

またクエリ対象のデータが格納されているS3バケットに対しても適当な権限を付与する必要があります。上記の例ではathena-examples-ap-northeast-1/cloudfront/plaintext/がそれに該当します。

データソースの登録

Redashの画面に戻り、RedashのデータソースにAmazon Athenaを登録します。

  • 「Data Sourses」をクリック

Athena+Redash-3

  • 「New Data Source」をクリック

Athena+Redash-4

  • 以下を入力/選択します。「Test connection」に成功すれば登録完了です。
    • Type(Amazon Athenaを選択)
    • Name(データソースにつける任意の名前)
    • AWS Region(ap-northeast-1など)
    • AWS Access Key
    • AWS Secret Key
    • S3 Staging Path(Amazon Athenaのクエリ結果の保存先S3バケット)

Athena+Redash-7

Athena+Redash-8

RedashからAmazon Athenaにクエリを実行

試しにAWSの公式ドキュメントにあるGetting Startedに倣ってRedashからAmazon Athenaにクエリを実行してみます。

  • 「Queries」-「New Query」

Athena+Redash-9

  • データベースを作成します。
create database redash_sampledb

Athena+Redash-10

  • テーブルを作成します。
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/';

Athena+Redash-14

  • 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;

Athena+Redash-15

Athena+Redash-16

  • ビジュアライズしてみます。

Athena+Redash-17

  • 以下の通り選択し「Save」をクリします。
    • Chart Type ; 「Pie」
    • X軸 : 「OS」
    • Y軸 : 「COUNT」

Athena+Redash-18

  • パイチャートが追加されました。

Athena+Redash-19

  • スケジュールを設定することもできます。

Athena+Redash-19-2

  • スケジュールは1回/日の時間指定、または「〜分毎」の指定ができます。

Athena+Redash-19-3

Athena+Redash-19-4

- クエリに名前をつけて保存します。

Athena+Redash-20

  • ダッシュボードも作成できます。(「Dashboards」-「New Dashboard」)

Athena+Redash-21

  • ダッシュボード名を入力して「Save」をクリックします。

Athena+Redash-22

  • 「Add Widget」から、ダッシュボードに先ほど作成したパイチャートを追加します。

Athena+Redash-23

  • クエリ名(request_count_by_os)を選択します。「Choose Visualization」で「Chart」を選択し「Add to Dashboard」をクリックします。

Athena+Redash-24

  • ダッシュボードにパイチャートが追加されました。

Athena+Redash-25

まとめ

公式のDockerイメージを使うと手元の環境で比較的容易にRedashを起動することができます。

またRedashはAmazon Athena以外にも様々なデータソースに対応しているため、チーム内での情報共有はもちろんのこと、デイリーユースのクエリ作成&実行環境としても利用できそうです。

参考