ELB配下のApacheで外部はHTTPSにリダイレクトし、内部のサーバのみHTTPで通信させる

やりたい事

server_pptx.png

  • ApacheにELB経由でHTTPでアクセス来たらHTTPSでアクセスするようにクライアントに要求する
  • ApacheにELB経由でHTTPSアクセス来たらELBでHTTPのポートのApacheにアクセスする
  • 他の内部(10.X.X.X)のEC2からHTTPにアクセスが来た場合はそのまま通す

設定

<VirtualHost *:80>
  ServerName xxx

  # http -> https rewrite
  RewriteEngine On
  RewriteCond %{HTTP:X-Forwarded-Port} !^443$
  RewriteCond %{HTTP_USER_AGENT} !^ELB-HealthChecker
  RewriteCond %{REQUEST_URI} !=/server-status
  RewriteCond %{HTTP:X-FORWARDED-FOR} !^$
  RewriteRule ^(.*)?$ https://%{HTTP_HOST}$1 [R=301,L]
  # http -> https rewrite [end]

  ...
</VirtualHost>

上記でいけます。ELB経由でアクセスされる場合は、 %{HTTP:X-FORWARDED-FOR} のHTTPヘッダにクライアントのGlobal IPがついているので、それがない場合は内部と判定しています。

その他の設定は、ヘルスチェックなどを通すためのものです

Redshiftでウィンドウ関数を使ってスコアに応じたカテゴリごとのランキングを出す

概要

Redshiftではウィンドウ関数を使って便利な集計ができます。 （ウィンドウ関数に関してはPostgreSQLウィンドウ関数のTipsを参考にするといいです。）

その記事のウィンドウ関数に手を加えてちょっと便利なことをしてみます。

やりたい事

以下のようなデータがあったときに、教科(subject)ごとにgradeが高い生徒(name)をランキング表示する。 これを使えば、例えばECのサイトで、カテゴリごとに売上が高いX個のアイテムのみを取り出すということもできます。

    name     | subject | grade | last_seen_in_class 
-------------+---------+-------+--------------------
 Michael     | english |     6 | 2014-08-24
 Emily       | math    |     5 | 2014-08-17
 Christopher | math    |     9 | 2014-08-24
 William     | english |     0 | 2014-07-18
 Tyler       | english |     4 | 2014-08-20
 Alexander   | history |    10 | 2014-08-22
 Benjamin    | history |     1 | 2014-08-24
 Jacob       | english |     9 | 2014-08-16
 Matthew     | english |     7 | 2014-08-24
 Emma        | math    |     8 | 2014-08-17
 Ashley      | math    |    10 | 2014-08-16
 Grace       | english |     3 | 2014-08-21
 Alexis      | history |     4 | 2014-08-24
 Victoria    | history |     4 | 2014-08-24
(14 行)

手順

Redshiftで試しています。

テーブル作成

CREATE TEMP TABLE student_subjects (
   name text,
   subject text,
   grade int DEFAULT NULL,
   last_seen_in_class date
);

データの用意

INSERT INTO student_subjects (name, subject, grade, last_seen_in_class) VALUES 
    ('Jacob', 'english', '9', '2014-08-16'), ('Michael', 'english', '6', '2014-08-24'), 
    ('Matthew', 'english', '7', '2014-08-24'), ('Emily', 'math', '5', '2014-08-17'),
    ('Emma', 'math', '8', '2014-08-17'), ('Christopher', 'math', '9', '2014-08-24'),
    ('Ashley', 'math', '10', '2014-08-16'), ('William', 'english', '0', '2014-07-18'),
    ('Grace', 'english', '3', '2014-08-21'), ('Tyler', 'english', '4', '2014-08-20'),
    ('Alexis', 'history', '4', '2014-08-24'), ('Alexander', 'history', '10', '2014-08-22'),
    ('Victoria', 'history', '4', '2014-08-24'), ('Benjamin', 'history', '1', '2014-08-24');

各サブジェクトごとにgradeでランクを出す

SELECT rank() OVER (PARTITION BY subject ORDER BY grade DESC), name, subject, grade
FROM student_subjects
ORDER BY subject, grade DESC;

結果

 rank |    name     | subject | grade 
------+-------------+---------+-------
    1 | Jacob       | english |     9
    2 | Matthew     | english |     7
    3 | Michael     | english |     6
    4 | Tyler       | english |     4
    5 | Grace       | english |     3
    6 | William     | english |     0
    1 | Alexander   | history |    10
    2 | Alexis      | history |     4
    2 | Victoria    | history |     4
    4 | Benjamin    | history |     1
    1 | Ashley      | math    |    10
    2 | Christopher | math    |     9
    3 | Emma        | math    |     8
    4 | Emily       | math    |     5
(14 行)

各サブジェクトごとにgradeのTop3を出す

ウィンドウ関数を使ったSQL内ではwhere句が使えないので入れ子にしてWHEREで取得するrankを制限します。

SELECT
    *
FROM
    (
        SELECT rank() OVER (PARTITION BY subject ORDER BY grade DESC), name, subject, grade
        FROM student_subjects
        ORDER BY subject, grade DESC
    ) as t
WHERE
    rank <= 3

結果

 rank |    name     | subject | grade 
------+-------------+---------+-------
    1 | Jacob       | english |     9
    2 | Matthew     | english |     7
    3 | Michael     | english |     6
    1 | Alexander   | history |    10
    2 | Alexis      | history |     4
    2 | Victoria    | history |     4
    1 | Ashley      | math    |    10
    2 | Christopher | math    |     9
    3 | Emma        | math    |     8
(9 行)

オープンソースのデータ可視化ツールのre:dashでらくらく分析共有

ウェブサービス、ソーシャルゲームなどを運営していると数値データがたまります。 その数値データを分析して、次の施策に活かすのが非常に大事です。

生のデータをMySQLなどを叩いて、数値に出して、Excelにグラフを貼り付けるのもいいですが、毎回やっていると工数がとられます。 ディレクター、エンジニア、だれでも出したい数値が自分で出せるのが理想です。

Tableauなどのツールもありますがいかんせん値段がたかくて手がだせません。 １人ならまだしも、チームで共有したい場合はライセンス料が何十万にもなってしまいます。

そこでオープンソースのre:dashです。（ただSQLを叩く必要あがるというのはありますが）

環境

EC2でAmazon Linuxの最新を作成したものを想定してます。 Amazon Linux AMI 2015.09.1 (HVM), SSD Volume Type - ami-383c1956

re:dash

re:dash

re:dashはRedshiftやMySQLなどのデータソースと連携してそのデータをビジュアライズしてくれます。 re:dash自体Webサーバになっているので、ビジュアライズされたデータが、ブラウザで確認できます。 しかも、re:dashはオープンソースなので無料で構築できます。

しかし、自分でmac上や、EC2のAmazon Linux上に構築してみましたがかなりインストールがつらいです。 nginx, gunicorn, supervisord, celery, redis, postgresqlなど慣れていればいいものの知らないと、構築できないときのデバッグが大変です。 そもそも、Amazon Linuxのスクリプトはあるのですが、すでにそのスクリプトが壊れているのでインストールできません。

UbuntuやDockerなら楽っぽいのですが、自分はAmazon Linux上で構築したかったのです。 そこで、re:dashをforkして、インストールスクリプトを直し、AnsibleでEC2にインストールできるようにしました。

リポジトリ

構成

re:dashはFlaskを使っているので、もし直したかったらpythonでごにょごにょできそうです。 nginxが前にたってますが、別になくてもいい気はします。 re:dashはメインのDBがPostgreSQLです。MySQLはre:dashだけならいらないのですが、データソースとして使いたいのでインストールしてあります。

server_pptx.png

インストール手順

以下の通りやればインストールできます。Ansibleはインストールしておいてください。

クローン
git clone https://github.com/wapa5pow/ansible-redash-amazon-linux

hostsをEC2のものにする。EC2はsshとhttpのポートをあけておく
cd ansible-redash-amazon-linux
vi hosts

your-private-key.pemをEC2のSSH用秘密鍵に設定して以下を実行
ansible-playbook site.yml --private-key=~/.ssh/your-private-key.pem -u ec2-user -i hosts

Ansibleが失敗する場合は、上記の同じコマンドをもう一度うつと成功します。

re:dashの使い方

ログイン

admin/adminでログインします。

re_dash_Login.png

データソースの追加

re:dashは各種データベースにつなげるのですが、同じインスタンス上にインストールしてあるMySQLを追加するため以下のようにします。

re_dash___Data_Sources.png

re_dash___Data_Sources.png

クエリの作成

データソースを作成したらクエリを作成します。

re_dash___Country_Analytics.png

ビジュアライゼーション

発行したクエリから同じ画面の下のほうにある、「+New Visualization」からグラフなどが作れます。

re_dash___Country_Analytics.png

ダッシュボードの作成

ビジュアライズしたグラフを集めて、ダッシュボードが作れます。ここにまとめておけば定期的なKPI確認が楽そうです。

re_dash___Home.png

re_dash___Home.png

re_dash___国ごとの統計.png

スクリーンショット_2016-01-26_12_13_02.png

線グラフ

SQLクエリ

select date(payment_date), sum(amount) from sakila.payment group by date(payment_date)

グラフ

re_dash___New_Query.png

棒グラフ

SQLクエリ

select * from Country

re_dash___New_Query.png

re_dash___New_Query.png

stackにしないとおかしな値になる

  • re:dashの改善点
    • 人口が多いところから左に並べたい

エリアグラフ

skip

パイチャート

SQLクエリ

select * from Country

グラフ

re_dash___New_Query.png

散布図

SQLクエリ

select * from Country

グラフ re_dash___New_Query.png

コホート

リテンションなどを見れます

スクショはデモページからです

re_dash___Cohort_Example.png

カウンター

目標と実績を比較するのに使えそうです

SQLクエリ

select * from Country

グラフ

re_dash___New_Query.png

re:dashでGoogle Spreadsheetsが扱える仕組み

ここにドキュメントがあるのですが、re:dashではpythonモジュールのgspreadとoauth2clientを使ってGoogle Spreadsheetsからデータをとってきています。

手順概要

  1. Google Developer Consoleでプロジェクトを作成
  2. Service account keyの発行
  3. APIを有効にする
  4. Google Spreadsheetsのデータソースの作成
  5. データセットを用意してGoogle Spreadsheetsにあげる
  6. ビジュアライゼーション

Google Developer Consoleでプロジェクトを作成

re:dashからGoogle Spreadsheetsにつなぐには、Service account keyをGoogle Developer Consoleから発行する必要があります。

まずプロジェクトをつくります。今回はredash-sampleという名前にしました。

Home_-_redash-sample.png

Service account keyの発行

つぎにAPIのメニューで、Service account keyを発行します。 この手順を行うと最終的にjson形式のファイルがダウンロードされます。これはあとから使うので適切なところに保存しておきます。

Home_-_redash-sample.png

Credentials_-_redash-sample.png

Create_service_account_key_-_redash-sample_と_Dash.png

APIを有効にする

つぎにOverviewからDrive APIを探してenalbedにしておきます。

API_Library_-_redash-sample.png

Google Spreadsheetsのデータソースの作成

以下のように作成します

re_dash___Data_Sources.png

データセットを用意してGoogle Spreadsheetsにあげる

平成22年基準消費者物価指数のページの中分類指数（1970年1月～最新月）をcsvでダウンロードしてきてこれをGoogle Spreadsheetにあげます。

その後、以下のようにデータを整形します。A1はYYYYMMのほうが適切かなとおもったのでかえました。

平成22年基準消費者物価指数_-_Google_Sheets.png

そして、これをService account keyのjsonの中にあるemailにシェアします

平成22年基準消費者物価指数_-_Google_Sheets.png

ビジュアライゼーション

Google SpreadsheetのURLが以下の場合とします。

平成22年基準消費者物価指数_-_Google_Sheets.png

そのときに、New Queryで作成する値には、以下をいれます。 「URLにあるkey」と「スプレッドシート番号(0から始まる)」を「|」で区切っていれます。

re_dash___New_Query.png

適当にビジュアライゼーションすると、以下のようになります。 ほうほう、家賃は下がっているのですね。

re_dash___New_Query.png

pivot table

re_dash___Country_Analytics.png

と、ここまでやって思ったのですが、Excelのほうが早くて便利なので、Excel使ったほうがいいかもと思いました。 ただ、DownloadしてExcelで開かなくてもWeb上でできるので楽っちゃらくです。

Redshiftのディスクがスペースが予想外に増えたのでVacuumした話

背景

最近分析で使えそうだと思って、個人アカウントでRedshiftを使ってみていろいろ検証してます。

そうすると、運営上、Diskのスペースがどのくらいなのか気になります。

これはWebコンソール上で、Clustersからたどっていきパフォーマンスのタブから確認できます。

Redshift_·_AWS_Console.png

上記は１週間のグラフですがどんどんおおきくなってます。合計50GBくらいになってました。

RedshiftにはMySQLのデータベース内のテーブルをsyncしていれてありますが、MySQLのテーブルの合計は1GBもありません。何が起こったのでしょうか。

問題点

MySQL => Redshiftへのテーブルのsyncをするときに、tmpテーブルを作って、データをいれて、元のテーブルのデータをDELETEで消して、tmpテーブルからインサートしています。syncは1時間ごとです。

SELECT COUNT(*) FROM TABLEとかでみると行数はインサートされた行と同じなのに、以下のコマンドで実行すると何百倍も行数が大きくなってました。

select
  trim(pgdb.datname) as Database,
  trim(pgn.nspname) as Schema,
  trim(a.name) as Table,
  b.mbytes,
  (CAST(b.mbytes as double precision) / 1024) as gbytes,
  (CAST(b.mbytes as double precision) / 1048576) as tbytes,
  a.rows,
  to_char(a.rows, '999,999,999,999,999') as rows_ww,
  to_char(a.rows, '9999,9999,9999,9999') as rows_jp
from (
       select db_id, id, name, sum(rows) as rows
       from stv_tbl_perm a
       group by db_id, id, name
     ) as a
  join pg_class as pgc on pgc.oid = a.id
  join pg_namespace as pgn on pgn.oid = pgc.relnamespace
  join pg_database as pgdb on pgdb.oid = a.db_id
  join (
         select tbl, count(*) as mbytes
         from stv_blocklist
         group by tbl
       ) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name;

http://dev.classmethod.jp/cloud/aws/amazon-redshift-convenient-sqls/#analyze_table_design_1 から引用

調べてみるとVACUUMの項目に以下の記述が。

行の削除や更新で解放された領域を、Amazon Redshift が自動的に再利用することはありません。更新を実行するために、Amazon Redshift は元の行を削除し、更新された行を追加します。そのため、すべての更新では実質的に削除の後で挿入が行われることになります。削除を実行する場合、行には削除のマークが付けられますが、削除されません。クエリプロセッサは、削除された行と削除されていない行をスキャンする必要があるため、削除された行の数が多すぎると不必要な処理が発生します。大規模な削除や更新の後でバキューム処理を実行することで、領域を再利用してクエリのパフォーマンスを向上させる必要があります。

http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Reclaiming_storage_space202.html から引用

VACUUMが必要でした。以下のように実行してみると、50GBくらいあったものが4GBくらいまで小さくなりました。実行時間は5分ほどだったので、cronで定期的にまわせばいいかなと思ってます。

まとめ

  • 大量のDELETEするときはVACUUMを使ってDiskスペースを確保しよう

ちなみに複数のデータベースを使っている場合は、データベースごとにVACUUMする必要がある。

参考

自分のミスによりRoute 53のAレコード切り替えでのサーバ移行で失敗した話

はじめてDNSを使って、運営していサーバを別のサーバに移行しようと思ったときに失敗した話です。

そのサービスではドメインとサーバをRoute 53のAレコードにIPアドレスを登録して運営していました。

server_pptx.png

Route 53でAレコードをIP-AからIP-Bに変えて移行するつもりでした。

しかし！！！

Route 53で実行したあと、サーバBのアクセスログを見るとアクセスは来ているのに、自分のブラウザではサーバAの情報が表示されたままです。 そうです、TTLが切れていなかったのです。概念としては覚えていたのですが、すっかり実際のを確認するのが抜け落ちていました。

Route_53_Management_Console.png

Route 53では上記のように確認できます。図では180秒になってます。 そのときのドメインのTTLは1時間に設定したままになってました。 ブラウザがサーバAのドメインをキャッシュしているため、最大1時間待たないと切り替わらなかったのです。

１時間は長すぎると思うのですが、他のサービスはどれくらいにしているのでしょうか。調べてみました。

ドメイン TTL
yahoo.co.jp 300
google.co.jp 300
qiita.com 60

ちなみにRoute 53のAレコードのALIASに設定するときのELBのTTLは60秒になってました。

まとめ

ドメインのAレコードのIPは切り替えるときにTTLも確認して適切な長さにする。

AWSをはじめて扱う人へのQ&A

AWSをはじめてさわる人がサービスがありすぎてどこから手をつけたらいいのかわからない問題があるので、情報をQ&A方式でまとめてみました。

サービスがあるすぎるのですがはじめはどれを勉強すればいいですか？

Webコンソールにログインすると多くのサービスがありどれから手をつけていいかわかりませんorz.. この中で最初に目をつけるべきは EC2, S3, RDS です。

aws_top.png

サービス名 説明
EC2 仮想サーバ。sshでログインできてApacheなどインストールしてWebサーバなどとして使う事が多い
S3 静的ファイルを保存する。image/js/cssなどをおいて、ブラウザから静的ファイルはこちらを参照する場合もある。CDNのバックエンドとして使うことも一般的。
RDS MySQLなどのマネージドデータベース。マネージドという意味は障害が起きたときなど自動的にフェイルオーバーしてくれるので運用が楽。またスケールアップもGUIで出来る。
ElastiCache Redis/memcahcedなどが使える。これもマネージド。

MySQL, RedisなどはEC2の上にたてる方法もありますが、fail overなど自分で各種やらなければいけないので運用コストを減らしたい場合は、RDS, ElastiCacheを使うといいと思います。

EC2, S3, RDS, ElastiCacheはわかりましたが次はどこをみればいいですか？

サービス名 説明
VPC ネットワークを区切りたい時やファイヤーウォールの設定などはここ
IAM (Identity & Access Management) 各種ユーザのアクセス権限などの設定はここ
Route 53 独自にDNSを設定したいときはここ
CloudFront CDNを利用したくなったときはここ
Elastic Beanstalk 自動的にサーバがスケールアウトし、デプロイも楽にしたかったらここ

クラウドデザインパターンってありますか？

あります

ここにはクラウドで想定されるさまざまな問題に対処できるアーキテクチャがのってます。

AWS-CloudDesignPattern.png

ドキュメントはどこを見ればいいですか？

公式のドキュメントが充実していていいと思います。 たとえばEC2だとリンクからいけます。

チュートリアルも充実しているので自分で実際に試してみるといいと思います。

内部実装など深い情報を知りたいときはどうすればいいですか？

ここに各種情報がありますが、AWS Black Beltシリーズが詳しいです。

最新の情報を取得するにはどうすればいいでしょうか？

日本語の場合は、Amazon Web Services ブログを、英語の場合はAWS Official Blogがあります。

AWSで費用を見積もりたいのですがどのようにすればいいでしょうか？

AWSの月間費用見積もり がリンクからできるのでここで簡易的な費用見積もりができます。

Amazon_Web_Services_Simple_Monthly_Calculator.png

AWSの障害情報はどこから見ればいいですか？

ここから見れます。

AWS_Service_Health_Dashboard_-_Dec_27__2015_PST.png

各リージョンからのlatencyを調べたい

http://aws-latency.altaircp.com/

資格はありますか？

AWS 認定プログラム

AWS Webコンソールでよくアクセスするサービスのショートカットを作りたい

EC2_Management_Console.png

Macで特定のディレクトリ以下で変更されたファイルをリアルタイムで表示する

用途

既存のファイルが多すぎて、バッチなどを走らせたときに、どのファイルが更新されたり作られたかわからないときに使うといい

方法

fswatchを入れて以下をたたくとよろし

fswatch -0 . | xargs -0 -n 1 -I {} echo {}

応用

  • fswatch を使えば、ソースコードが更新されたときに再起動するなどもできる。