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

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

データ分析

データ分析

#re:dash
#Amazon Athena
#AWS
八幡豊

八幡豊

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

環境

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

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

Redash Version

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

参考

Share this article

facebook logohatena logotwitter logo

関連記事

Docker Desktop for Apple siliconでRedashを動作させてみた

Docker Desktop for Apple siliconでRedashを動作させてみた

User avatar
haoyayoi
2021.11.02
getredash/redashがローカル環境でError code 139と出て起動しなくなっていたので取り敢えず暫定復旧させてみた

getredash/redashがローカル環境でError code 139と出て起動しなくなっていたので取り敢えず暫定復旧させてみた

User avatar
haoyayoi
2021.10.01
Redashに環境変数での設定変更が反映されていることをgunicornで確認してみた

Redashに環境変数での設定変更が反映されていることをgunicornで確認してみた

User avatar
haoyayoi
2021.07.13
RedashでScheduler更新時に発生するエラー「There’s already an active RQ scheduler」について原因を調べて解消してみた

RedashでScheduler更新時に発生するエラー「There’s already an active RQ scheduler」について原因を調べて解消してみた

User avatar
haoyayoi
2021.07.09
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.