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

データ分析

八幡豊

2017.08.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

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が用意されていますのでこちらを利用する方法もあります。

Setting up a Redash instance · Redash Help Center

環境

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とS３へのアクセス許可を持った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以外にも様々なデータソースに対応しているため、チーム内での情報共有はもちろんのこと、デイリーユースのクエリ作成＆実行環境としても利用できそうです。

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

RedashのAmazon Athenaサポート

Redashのセットアップ

環境

Redashの依存コンポーネント

docker-compose.yml

ディレクトリ構成

.envファイル

docker-compose.yml

Redashのデータベースの初期化

Redashの起動

管理者ユーザー登録＆ログイン

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

IAMユーザーの作成

データソースの登録

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

まとめ

参考

イベント

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

EVENT【4/24（水）リモート】会社員からフリーランスエンジニアへ、変化の時の体験談 [パネルトーク] フリーランストーク#10

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

RedashのAmazon Athenaサポート

Redashのセットアップ

環境

Redashの依存コンポーネント

docker-compose.yml

ディレクトリ構成

.envファイル

docker-compose.yml

Redashのデータベースの初期化

Redashの起動

管理者ユーザー登録＆ログイン

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

IAMユーザーの作成

データソースの登録

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

まとめ

参考

イベント

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS/システムアーキテクト/Android案件特集〜 を開催します

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/19（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

EVENT【4/24（水）リモート】会社員からフリーランスエンジニアへ、変化の時の体験談 [パネルトーク] フリーランストーク#10

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩