TuneCoreの売上データCSVをS3に格納してAthenaのクエリをRe:dashのデータソースにして可視化する

2017/06/05 2017/06/06

先日参加しましたAWS Summit Tokyo 2017で、
[JapanTaxi] Athena 指向アナリティクス〜真面目に手を抜き価値を得よ〜(AWS Summit Tokyo 2017)を聞いてきました、
ので、S3->Athen->Re:dashの構成をやってみたくなりましたのでやってみました。

TuneCoreという国内外の音楽配信サービスのディストリビューターとして一括で仲介してくれるサービスに個人的にお世話になっておりまして、その月次レポートをS3->Athen->Re:dashで可視化してみました。

S3の設定

特に設定するところはありません。
バケットを作成して、TuneCoreからダウンロード出来るCSVをアップロードしました。
CSVは1行目とフッタにあるコメント行を削除してShift-JISからUTF-8に変換しました。

Athenaの設定

[Add Table]からウィザードを使ってCSV選択で1カラムだけのテーブル作成のSQLを生成して、そこにカラムを手書きで足しました。

こんなSQLになりました。

CREATE EXTERNAL TABLE IF NOT EXISTS tunecore8bangai.tunecorelist (
  `application_month` string ,
  `achievement_month` string,
  `store` string,
  `service` string,
  `country` string,
  `artist` string,
  `release` string,
  `music` string,
  `jan_upc` string,
  `isrc` string,
  `label` string,
  `sales_format` string,
  `unit_selling_price` double,
  `sales_currency` string,
  `sales_revenue_unit_price` double,
  `request_count` int,
  `sales_revenue_sum` double 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION 's3://bucket-name/'
TBLPROPERTIES ('has_encrypted_data'='true');

CREATE EXTERNAL TABLE IF NOT EXISTS tunecore8bangai.tunecorelist (

`application_month` string ,

`achievement_month` string,

`store` string,

`service` string,

`country` string,

`artist` string,

`release` string,

`music` string,

`jan_upc` string,

`isrc` string,

`label` string,

`sales_format` string,

`unit_selling_price` double,

`sales_currency` string,

`sales_revenue_unit_price` double,

`request_count` int,

`sales_revenue_sum` double

)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

WITH SERDEPROPERTIES (

'serialization.format' = ',',

'field.delim' = ','

) LOCATION 's3://bucket-name/'

TBLPROPERTIES ('has_encrypted_data'='true');

Re:dash用のIAMの作成

Re:dash Help Center Amazon Athena Setupに準じて設定します。

アタッチするポリシーは
AWSQuicksightAthenaAccess
と
次のポリシーです。
「my-bucket」はAthenaの[Setting] – [Query result location]を設定しました。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::my-bucket/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::my-bucket"
            ]
        }
    ]
}

{

"Version": "2012-10-17",

"Statement": [

{

"Effect": "Allow",

"Action": [

"s3:GetObject"

"Resource": [

"arn:aws:s3:::my-bucket/*"

]

{

"Effect": "Allow",

"Action": [

"s3:GetBucketLocation",

"s3:ListBucket"

"Resource": [

"arn:aws:s3:::my-bucket"

]

}

]

}

Re:dashの設定

Re:dashのバージョンは1.0.3+b2850です。

ここからは手間取りましたが、最終的にこちらの2サイトの通りにやって出来ました。
ありがとうございます。

[Docker] ubuntu 14.04/16.04にDockerをインストール

Redash での Amazon Athena 連携に関して

Re:dashサーバにDockerをインストール

ubuntuは14.04です。

Athena ProxyサーバをDockerで起動するためにRe:dashサーバ(ubuntu)にDockerをインストールします。

$ sudo apt-get update
$ sudo apt-get install apt-transport-https ca-certificates
$ sudo apt-key adv \
               --keyserver hkp://ha.pool.sks-keyservers.net:80 \
               --recv-keys 58118E89F3A912897C070ADBF76221572C52609D
$ echo "deb https://apt.dockerproject.org/repo ubuntu-trusty main" | sudo tee /etc/apt/sources.list.d/docker.list
$ sudo apt-get update
$ sudo apt-get install linux-image-extra-$(uname -r) linux-image-extra-virtual
$ sudo apt-get update
$ sudo apt-get install docker-engine

$ sudo apt-get update

$ sudo apt-get install apt-transport-https ca-certificates

$ sudo apt-key adv \

--keyserver hkp://ha.pool.sks-keyservers.net:80 \

--recv-keys 58118E89F3A912897C070ADBF76221572C52609D

$ echo "deb https://apt.dockerproject.org/repo ubuntu-trusty main" | sudo tee /etc/apt/sources.list.d/docker.list

$ sudo apt-get update

$ sudo apt-get install linux-image-extra-$(uname -r) linux-image-extra-virtual

$ sudo apt-get update

$ sudo apt-get install docker-engine

Re:dashサーバでredash-amazon-athena-proxyを起動

$ git clone https://github.com/getredash/redash-amazon-athena-proxy.git
$ cd redash-amazon-athena-proxy
$ sudo docker build .
$ sudo docker images

$ git clone https://github.com/getredash/redash-amazon-athena-proxy.git

$ cd redash-amazon-athena-proxy

$ sudo docker build .

$ sudo docker images

IMAGE IDを確認して次のコマンドで使用

$ sudo docker run -d --name redash-amazon-athena-proxy -p 4567:4567 <IMAGE ID>

1 2	$ sudo docker run -d --name redash-amazon-athena-proxy -p 4567:4567 <IMAGE ID>

Re:dashの .envに環境変数を追加

※環境にもよりますがイメージからRe:dashを作っている場合は、 /opt/redash/.env

export ATHENA_PROXY_URL=http://localhost:4567/query
export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.athena

export ATHENA_PROXY_URL=http://localhost:4567/query

export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.athena

Re:dashでAthenaをデータソースに設定

Re:dashのデータソースでAmazon Athenaが選択出来るようなりました。

Name : 任意
Type : Amazon Athena
AWS Secret Key : 事前に作成したIAMユーザのシークレットキー
AWS Region : Athenaのリージョン
AWS Access key : 事前に作成したIAMユーザのアクセスキー
S3 Staging Path : Athenaの[Setting] – [Query result location]

これでクエリーが実行出来て、ビジュアライゼーションが作成出来て、ダッシュボードに設定出来ます。

ストア別とか、曲別とか、一覧とか作ってみて、、、、、
出来た！

最後までお読みいただきましてありがとうございました！

「AWS認定資格試験テキスト＆問題集　AWS認定ソリューションアーキテクト - プロフェッショナル改訂第2版」という本を書きました。

「AWS認定資格試験テキスト AWS認定クラウドプラクティショナー改訂第3版」という本を書きました。

「AWS認定資格試験テキスト　AWS認定AIプラクティショナー」という本を書きました。

「ポケットスタディ AWS認定デベロッパーアソシエイト［DVA-C02対応］」という本を書きました。

「要点整理から攻略するAWS認定ソリューションアーキテクト-アソシエイト」という本を書きました。

「AWSではじめるLinux入門ガイド」という本を書きました。

@yamamanx

開発ベンダー5年、ユーザ企業システム部門通算9年、ITインストラクター5年目でプロトタイプビルダーもやりだしたSoftware Engineerです。
質問はコメントかSNSなどからお気軽にどうぞ。
出来る限りなるべく答えます。

このブログの内容/発言の一切は個人の見解であり、所属する組織とは関係ありません。
このブログは経験したことなどの共有を目的としており、手順や結果などを保証するものではありません。
ご参考にされる際は、読者様自身のご判断にてご対応をお願いいたします。

また、勉強会やイベントのレポートは自分が気になったことをメモしたり、聞いて思ったことを書いていますので、登壇者の意見や発表内容ではありません。

- AWS, BI athena, AWS, re:dash, s3

: Lambda関数で自分自身の環境変数を更新する

Twitterでツイート検索するAPIを試してみるでツイートの取得を重複させない …

: ブログの画像を別アカウントのS3に移動するためにIAMロールでクロスアカウントアクセス

ずっと先延ばしにしていたのですが、このブログの画像はEC2から直接配信しています …

: CloudWatchエージェントがEC2サービスエンドポイントのデュアルスタックに対応したのでIPv6だけでカスタムメトリクス送信できた！

CloudWatchエージェントがEC2サービスデュアルスタックエンドポイントに …

: Amazon S3アクセスポイント経由からアクション可能なポリシー

S3アクセスポイントを使うことで、複雑で長いバケットポリシーを避け、アクセスポイ …

: API Gateway Lambdaプロキシ統合で渡されるリクエストを確認しました

API Gatewayの統合リクエストでLambdaを指定するときにプロキシ統合 …

: AWS X-Ray SDK for PythonをOrganizations組織内にLambda Layersで共有しました

ローカルでパッケージ作成インストールコマンドはこちらAWS X-Ray SDK …

: Amazon Q Developer for CLIでAWS Diagram MCP Serverを使ってAWSアイコンを使った図を書いてもらいました

MacにインストールしているAmazon Q Developer for CLI …

: Lambdaバージョンとエイリアスとトリガー

Lambdaのバージョン、エイリアスにはそれぞれ別のトリガーが設定できます。上 …

: CloudFrontディストリビューションを別アカウントへ移動する

すでにCNAMEを設定しているCloudFrontディストリビューションを、別ア …

: Route53でドメインを新規取得してDNSレコードを設定する

Elastic IPをAWSで発行しているのですから、DNSの設定も同じようにマ …

PREV: AWS Summit Tokyo 2017 聴講したセッションのメモ
NEXT: 「gusuku Meetup OSAKA Vol.1」に参加させていただきました

TuneCoreの売上データCSVをS3に格納してAthenaのクエリをRe:dashのデータソースにして可視化する

S3の設定

Athenaの設定

Re:dash用のIAMの作成

Re:dashの設定

Re:dashサーバにDockerをインストール

Re:dashサーバでredash-amazon-athena-proxyを起動

Re:dashの .envに環境変数を追加

Re:dashでAthenaをデータソースに設定

関連記事