このラボでは、Elastic Load Balancing ログデータを分析します。ログデータは、Amazon S3 バケット内に、以下のさまざまな形式で保存されています。
Amazon Athena からデータにアクセスし、データに対してクエリを実行するために、テーブル定義を作成します。その後、各ストレージ形式の効率を比較します。
目標 このラボを完了すると、以下の操作を実行できるようになります。
所要時間 このラボは、修了までに30 分かかります。
注意
シナリオ
このラボでは、Elastic Load Balancing サービスで生成されたウェブログデータを分析します。データは、米国東部 (バージニア) リージョンの Amazon S3 バケットに保存されています。
ウェブログデータは、解釈の難しいデータです。スペース区切りで、それぞれのフィールドは長い文字列から抽出する必要があります。
2015-01-01T00:00:00.022719Z elb_demo_005 244.218.91.244:2255 172.36.231.239:443 0.000878 0.000803 0.000891 200 200 0 1886 "GET https://www.example.com/jobs/376 HTTP/1.1" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.111 Safari/537.36" DHE-RSA-AES128-SHA TLSv1.2
このデータを解読するには、[正規表現] を使用して関連データを抽出する必要があります。
([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' )
このような複雑な形式である場合、通常 ETL (抽出、変換、ロード) プロセスを経てからデータベースにロードさせる必要があります。しかし Amazon Athena では、データがファイルから読み込まれると同時に解釈される "Schema on Read" が適用できます。これにより、複雑なデータをデータベースに保存されているデータのようにクエリ処理できます。
また、Amazon Athena では、[複数のファイルから同時にデータを読み込む] こともできます。例えば、このラボで使用されるログデータは、2015 年 1 月分のもので、以下のように 31 のサブディレクトリ (1 日あたり 1 つのサブディレクトリ) の中に、186 のファイルとして保存されています。
581201753 raw/year=2015/month=1/day=1/part-r-00000.txt
569297436 raw/year=2015/month=1/day=1/part-r-00001.txt
518914338 raw/year=2015/month=1/day=1/part-r-00002.txt
484349071 raw/year=2015/month=1/day=1/part-r-00003.txt
516079109 raw/year=2015/month=1/day=1/part-r-00004.txt
475690426 raw/year=2015/month=1/day=1/part-r-00005.txt
各ファイルには、およそ 500 MB の非圧縮データが含まれています。Amazon Athena では、自動的にサブディレクトリがスキャンされ、データが検索されます。
複数のファイルからのデータの読み込み作業が、Amazon Athena クラスターで並列的に分散して行われるため、単一のファイルからのデータ読み込みよりもはるかに早くなります。これは、自動的に行われ、設定はまったく不要です。
Qwiklabs画面にある [ラボを開始] をクリックして、ラボを起動します。
Qwiklabs画面の左側に表示されている[コンソールを開く] をクリックします。
Qwiklabs画面の左側に表示されている認証情報を使用して AWS マネジメントコンソールにサインインします。
このラボの間は、リージョンを変更しないでください。
Amazon Athena はインタラクティブなクエリサービスで、Amazon S3 のデータを標準 SQL を使用して簡単に分析できます。Athena はサーバーレスなので、セットアップや管理が必要なインフラストラクチャはありません。料金は、実行するクエリに対してのみ発生します。Amazon S3 にあるデータを指定して、スキーマを定義し、標準的な SQL を使ってデータのクエリを開始するだけです。
このラボではまた、以下のタイプのストレージの利点についても取り上げます。
[圧縮データ] は、ストレージコストが低く、転送されるデータが少ないため転送速度が高速です。また、Amazon Athena の料金は、Amazon S3 からスキャンされるデータ量を基に算出されるため、処理コストが低いという利点があります。 [パーティション分割されたデータ] は、階層状になったサブディレクトリに保存されているため、クエリに関連しないデータファイルはスキップされます。このラボでは、年、月、日でパーティション分割されたデータを使用し、1 日分のクエリの実行のために、全データセットではなく、1 つのサブディレクトリのみを読み込みます。これには、低い処理コスト、クエリの高速化という利点があります。
この最初のタスクでは、[未加工のテキスト形式] で保存されたデータを使用します。[外部テーブル] を定義します。つまり、データは既に存在していて、データの解釈のための [スキーマ] を定義するだけで済むということになります。[正規表現] によって、Amazon Athena がどのようにデータを解釈すべきかが定義されます。
[1][AWS マネジメントコンソール] で、[サービス] から [Athena] をクリックします。
[2][Get Started] をクリックします。
これが表示された場合は、 チュートリアルを閉じます。
Query Editor の上に表示されている「set up a query result location in Amazon S3.」のリンクをクリックします。
Settings画面が表示されるので、Qwiklab画面の左に表示されている「S3Bucket」の値をコピーし、Query result location に入力します。
[Save] をクリックします。
ラボ用のデータベース[lab] がすでに存在している場合は、データベースを削除します。この場合、次のコマンドをコピーします。
DROP DATABASE IF EXISTS lab;
コピーしたコマンドを [Query Editor] に貼り付けます (既存のテキストをすべて置き換えます)。
[Run Query] をクリックします。[Run Query] ボタンがアクティブになっていない場合は、CTRL+ENTER で実行できます。
ラボ用のデータベースを作成します。以下のコマンドを実行してラボデータベースを作成します。
CREATE DATABASE lab;
新しいデータベース定義が作成されます。[Database] プルダウンで、[lab] を選択します。これにより、新しいデータベースに作成されたテーブルを表示するコンソールを設定します。
これで、ログデータを含む外部テーブルが作成されます。テーブルが Amazon S3 からデータを取得するように設定されます。
以下のコマンドを実行して外部テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS lab.elb_logs_raw (
request_timestamp string,
elb_name string,
request_ip string,
request_port int,
backend_ip string,
backend_port int,
request_processing_time double,
backend_processing_time double,
client_response_time double,
elb_response_code string,
backend_response_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
url string,
protocol string,
user_agent string,
ssl_cipher string,
ssl_protocol string )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES ( 'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' )
LOCATION 's3://aws-tc-largeobjects/AWS-200-BIG/v3.1/lab-2-athena/raw/';
新規の [elb_logs_raw] テーブル定義は、左のナビゲーションパネルの [TABLES] に表示されます。
テーブルリストの [elb_logs_raw] をクリックします。
作成したテーブル定義と一致する列のリストが表示されます。
テーブル名の右側にある ドットをクリックして、[Preview table] を選択します。
最初の 10 行が表示されます。[実行時間] と [スキャンされたデータ] のクエリメトリクスは[New Query] ボタンの右側にも表示されます。後のステップで、これらのタイミングを使用していきます。
S3 に格納されたログファイルから抽出された列を調べます。列には、IP アドレス、レスポンスコード、送信バイト数、URL、ブラウザ情報が含まれています。
以下のクエリを実行し、2015 年 1 月 1 日に受信した、HTTP および HTTPS リクエスト数をページが正常に提供されたかどうかを示すレスポンスコードによりグループ化して、集計します。
SELECT
elb_response_code,
count(*) AS count
FROM elb_logs_raw
WHERE request_timestamp LIKE '2015-01-01%'
GROUP BY elb_response_code
ORDER BY elb_response_code;
後で比較するために、[実行時間] と [スキャンされたデータ] をメモしておきます。表記は以下のようになるはずです。
(Run Time: 17.69 seconds、Data scanned: 90.74 GB)
このクエリの実行によって、たった 1 日分のデータのリクエストだったにもかかわらず、[合計 90 GB のデータがスキャンされた] ことに注目してください。このラボの最後に、パーティション分割を使用して、このクエリをより効率的に実行します。
このタスクでは、[gzip 形式] で保存されているデータを使用します。未加工のデータファイルでは、各ファイルは約 500 MB であるのに対して、gzip 形式のデータファイルでは、各ファイルは約 70 MB (元のサイズの 15%) となります。ここに挙げるコマンドは、前のセクションと同一ですが、別のソースディレクトリが指定されています。
以下のクエリを実行し、圧縮されたログファイルから外部テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS lab.elb_logs_compressed (
request_timestamp string,
elb_name string,
request_ip string,
request_port int,
backend_ip string,
backend_port int,
request_processing_time double,
backend_processing_time double,
client_response_time double,
elb_response_code string,
backend_response_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
url string,
protocol string,
user_agent string,
ssl_cipher string,
ssl_protocol string )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES ( 'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' )
LOCATION 's3://aws-tc-largeobjects/AWS-200-BIG/v3.1/lab-2-athena/compressed/';
新規の [elb_logs_compressed] テーブル定義は、左のナビゲーションパネルの [TABLES] リストに表示されます。
前と同じクエリを実行しますが、今回は圧縮されたデータを使用します。
SELECT
elb_response_code,
count(*) AS count
FROM elb_logs_compressed
WHERE request_timestamp LIKE '2015-01-01%'
GROUP BY elb_response_code
ORDER BY elb_response_code;
後で比較するために、実行時間とスキャンされたデータをメモしておきます。表記は以下のようになるはずです。
(Run time: 20.75 秒、Data scanned: 13.08 GB)
このクエリでは、データが gzip 形式に圧縮されていたため、[スキャンされたデータは、わずか 13 GB になった] ことに注目してください。このクエリを実行するコストを 85% 削減できたのは、Amazon S3 から読み出されるデータ量が少なくなったためです。
このタスクでは、前のセクションと同じデータを使用します。しかしテーブル定義には [partition by] ステートメントが含まれていて、年、月、日で分割されたサブディレクトリにデータが分散されていることが Amazon Athena に伝えられます。これにより Amazon Athena は [WHERE] 句に基づいて、特定のサブディレクトリにクエリを制限できます。
以下のクエリを実行し、パーティション分割されたログファイルから外部テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS lab.elb_logs_partitioned (
request_timestamp string,
elb_name string,
request_ip string,
request_port int,
backend_ip string,
backend_port int,
request_processing_time double,
backend_processing_time double,
client_response_time double,
elb_response_code string,
backend_response_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
url string,
protocol string,
user_agent string,
ssl_cipher string,
ssl_protocol string )
PARTITIONED BY (year int, month int, day int)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1','input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$' )
LOCATION 's3://aws-tc-largeobjects/AWS-200-BIG/v3.1/lab-2-athena/compressed/';
このクエリには、[PARTITION BY] 句があり、データが年、月、日で分割されたディレクトリにパーティション分割されていることが、Athena に伝えられることを確認します。
新規の [elb_logs_raw] テーブル定義は、左のナビゲーションパネルの [TABLES] リストに表示されます。
パーティション化されたデータを使用する前に、Athena は、既存のディレクトリ構造をスキャンして、既存のパーティションを見つける必要があります。
次のクエリを実行してディスクをスキャンし、パーティションを Athena メタデータに読み込ませます。
MSCK REPAIR TABLE lab.elb_logs_partitioned;
検出されてメタストアに追加されたパーティションのリストが表示されます。
パーティション化されたデータに対して以下のクエリを実行します。
SELECT
elb_response_code,
count(*) AS count
FROM elb_logs_partitioned
WHERE year=2015
AND month=1
AND day=1
GROUP BY elb_response_code
ORDER BY elb_response_code;
このバージョンのクエリでは、テーブルの作成に使用された [PARTITIONED BY] ステートメントで、同じ形式で日付を指定します。
後で比較するために、実行時間とスキャンされたデータをメモしておきます。表記は以下のようになるはずです。
(Run time: 20.24 秒、Data scanned: 432.46 MB)
このクエリでは、2015 年 1 月 1 日のデータが保存された 1 つのパーティションのみを読み込むため、[スキャンされたデータは、わずか 432 MB になった] ことに注目してください。このクエリを実行するコストを 12 分の 1 に削減できたのは、Amazon S3 からスキャンされるデータ量が少なくなったためです。
質問 : スキャンされたデータ量がはるかに少ないにもかかわらず、[実行時間] が、前のクエリとほぼ同じなのはなぜでしょうか。
回答 : Amazon Athena は、[高度に並列] で、多くの入力ファイルを同時に処理できます。しかし、1 日分のクエリによってスキャンされたパーティションに含まれていて、Amazon Athena によって並行処理されたのは、わずか 6 ファイルでした。それに比べ、前のテーブルでは 186 ファイルが並行してスキャンされました。データが多ければ多いほど、より長い実行時間がかかるわけではない理由がこれです。
このタスクでは、列形式の [Apache Parquet] 形式で保存されているデータを使用します。これによって、幾つかの列のみを参照する場合には、非常に高速なクエリが可能となります。Parquet では、[述語のプッシュダウン] もサポートされていて、これによって WHERE 句からのパラメータが、ディスクから読まれるコードに「プッシュダウン」され、フィルタリングをしてから結果をネットワークに送信することが可能となります。この結果、クエリ処理がより高速になり、ディスクアクセスを減少させることが可能です。
以下のクエリを実行し、Parquet 形式で格納されたデータから外部テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS lab.elb_logs_parquet (
request_timestamp string,
elb_name string,
request_ip string,
request_port int,
backend_ip string,
backend_port int,
request_processing_time double,
backend_processing_time double,
client_response_time double,
elb_response_code string,
backend_response_code string,
received_bytes bigint,
sent_bytes bigint,
request_verb string,
url string,
protocol string,
user_agent string,
ssl_cipher string,
ssl_protocol string )
PARTITIONED BY (year int, month int, day int)
STORED AS PARQUET
LOCATION 's3://aws-tc-largeobjects/AWS-200-BIG/v3.1/lab-2-athena/parquet/'
tblproperties ("parquet.compress"="SNAPPY");
このクエリには、[STORED AS PARQUET] 句が含まれていて、これにより Athena にそのデータが Parquet ファイル形式で保存されていることが伝えられることに注目してください。
新規の [elb_logs_parquet] テーブル定義は、左のナビゲーションパネルの [TABLES] に表示されます。
このクエリを実行してディスクをスキャンし、パーティションを Athena メタデータに読み込ませます。
MSCK REPAIR TABLE lab.elb_logs_parquet;
Parquet データファイルに対して同じクエリを実行します。
SELECT
elb_response_code,
count(*) AS Count
FROM elb_logs_parquet
WHERE year=2015
AND month=1
AND day=1
GROUP BY elb_response_code
ORDER BY elb_response_code;
後で比較するために、実行時間とスキャンされたデータをメモしておきます。表記は以下のようになるはずです。
(Run time: 5.31 秒、Data scanned: 1.84 MB)
このクエリでは、パーティション分割されたデータの [1 列] のみを読み込むため、[スキャンされたデータは、わずか 1.8 MB になった] ことに注目してください。最初の未加工のクエリでスキャンされたデータ量は 90 GB でした。
日別にグループ化された、テーブル内の行数をカウントするため、以下のクエリを実行します。
SELECT
year,
month,
day,
count(*) as Count
FROM elb_logs_parquet
GROUP BY year, month, day
ORDER BY year, month, day;
[Data scanned]がゼロであることに注目してください。これは、Parquet では、各ファイルに保存されているレコードの数が保持されていて、[データを一切スキャンする必要がなく]、その数が取得されるためです。さらに、データはパーティション分割されているため、Amazon Athena では、レコード数のパーティションごとのグループ化が可能です。したがって、このクエリは料金はかかりません。もちろん、実際に特定の値がスキャンされる必要がある場合 (例えば、WHERE 句がレスポンスコードに含まれている場合) などには、それにかかる費用が発生します。
各データ形式の結果の要約は以下のようになります (実際の結果はわずかに変化する場合があります)。
| Data Format | Run time | Data scanned |
|---|---|---|
| Raw | 22 s | 90.73 GB |
| Compressed | 24 s | 13 GB |
| Compressed & Partitioned | 20 s | 432 MB = 0.432 GB |
| Parquet with Snappy compression | 5 s | 1.8 MB = 0.0018 GB |
Amazon Athena の利用料金は、スキャンされるバイト数に基づいていて、端数はメガバイト単位に切り上げられ、クエリごとに最小 10 MBとなります。[CREATE TABLE、ALTER TABLE、DROP TABLE] などの、データ定義言語 (DDL) ステートメント、パーティションを管理するステートメント、正常に実行されなかったクエリに対しては課金されません。キャンセルされたクエリは、スキャンされたデータ量に基づいて課金されます。
Amazon Athena では、実行されたクエリと、クエリの結果が記録されます。
画面の上部にある [Saved Queries] タブをクリックします。
このページから、頻繁に使用されるクエリをクエリエディタにすばやく読み込むことが可能です。
画面の上部にある [History] タブをクリックします。
このページでは、Amazon Athena で実行されたクエリが、結果 (成功/失敗)、実行時間、スキャンされたデータと共に表示されます。
[Action] 列の [Download results] をクリックして、1 つの SELECT クエリの出力をダウンロードします。このとき、出力のコピーを含む CSV ファイルがダウンロードされます。各クエリからの出力ファイルは、自動的に Amazon S3 バケットに保存されます。
このタスクでは、今後必要のないテーブルを削除します。これにより、Athena のメタデータがクリーンな状態に保たれます。
画面上部の [Query Editor] タブをクリックします。
以下のクエリを実行して、ラボデータベースを消去します。
DROP DATABASE lab CASCADE;
これでテーブルは削除されます。
お疲れ様でした。 このラボを完了しました。以下の手順に従って、ラボ環境をクリーンアップします。