ちょっと話題の記事

S3にフラットに配置してしまったログも大丈夫！シンボリックリンクを利用してスキャン範囲を絞ってAthenaからクエリする

にしざわ

2018.05.22

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

西澤です。私のようにHadoop等の利用経験の無い人間が、Athenaを始めて利用する際に敷居が高いのが、パーティションの取り扱いです。先日、下記ブログを書いたところ、新しい学びがあり、他のケースでも活用できないかというところを試して上手く動作させることができたので、ご紹介したいと思います。

S3バケット内のファイル一覧(S3 Inventory)をAthenaを利用して分析する

フラットに配置されたファイル群をAthenaで扱う場合の課題

S3バケット内のファイル群に対して、一気にクエリすることができるAthenaですが、スキャン範囲が課金対象となり、且つ、パフォーマンスの観点からも、パーティション分割して利用することが推奨されています。

Amazon Athenaのパーティションを理解する #reinvent

ところが、パーティション分割をしたり、スキャン範囲を限定する為には、ファイル群は階層構造となっている必要があり、フラットに配置したファイル群をスキャンする場合には、パーティションを利用することもできず、原則全スキャンとなってしまいます（列指向フォーマットの話は今回しません）。例えば、CloudFrontログは指定したS3バケットのパスにフラットに配置され続けます。一定期間運用したCloudFrontのログをAthenaからスキャンする為には、本来クエリする必要の無い古い日時のログまで全スキャンすることになってしまい、お財布的にもパフォーマンス的にも無駄が大きくなってしまうという問題がありました。

そこで、後で困ることが無いように、無理矢理ログを階層化するようなスクリプトをしかけておく等の対処を行ったケースもありました。

CloudFrontのログをAthenaで読むためにリネームするLambda用Pythonスクリプト書いてみた

今回この課題を簡単にクリアできる方法を見つけました。

シンボリックリンクを利用したAthenaテーブルの作り方

今回のテストとしては、CloudFrontログを想定して、下記のようなファイル群を用意しました。出力は省略しましたが、実際にはもっとたくさんのログを配置しました。

$ aws s3 ls s3://cloudfront-log-test-bucket/
:::
2018-05-22 10:15:10 2680 XXXX0000XXXX00.2018-05-19-02.0c3f7850.gz
2018-05-22 10:15:10 480 XXXX0000XXXX00.2018-05-19-05.302686fb.gz
2018-05-22 10:15:10 730 XXXX0000XXXX00.2018-05-19-06.3eb4b67c.gz
2018-05-22 10:15:10 473 XXXX0000XXXX00.2018-05-19-07.2ab2e7ea.gz
2018-05-22 10:15:10 486 XXXX0000XXXX00.2018-05-19-08.51bb045c.gz
2018-05-22 10:15:10 484 XXXX0000XXXX00.2018-05-19-11.8f5877af.gz
2018-05-22 10:15:10 477 XXXX0000XXXX00.2018-05-19-17.09147609.gz
2018-05-22 10:15:10 469 XXXX0000XXXX00.2018-05-19-18.1ba65994.gz
2018-05-22 10:15:10 488 XXXX0000XXXX00.2018-05-19-18.d49e0623.gz
2018-05-22 10:00:27 563 XXXX0000XXXX00.2018-05-20-09.4035b6c3.gz
2018-05-22 10:00:27 435 XXXX0000XXXX00.2018-05-20-10.0fa2569a.gz
2018-05-22 10:00:27 792 XXXX0000XXXX00.2018-05-20-10.eaed67d9.gz
2018-05-22 10:00:27 468 XXXX0000XXXX00.2018-05-20-12.5b278a9a.gz
2018-05-22 10:00:27 584 XXXX0000XXXX00.2018-05-20-13.3a38d341.gz
2018-05-22 10:00:27 435 XXXX0000XXXX00.2018-05-20-13.e61fff03.gz
2018-05-22 10:00:27 483 XXXX0000XXXX00.2018-05-20-14.4f57349f.gz
2018-05-22 10:00:27 470 XXXX0000XXXX00.2018-05-20-15.8bd4debf.gz
2018-05-22 10:00:27 435 XXXX0000XXXX00.2018-05-20-15.98979882.gz
2018-05-22 10:00:27 652 XXXX0000XXXX00.2018-05-20-16.e2db6ba4.gz
2018-05-22 10:00:27 434 XXXX0000XXXX00.2018-05-20-17.6a2539c1.gz
2018-05-22 10:00:27 562 XXXX0000XXXX00.2018-05-20-18.8774fe78.gz
2018-05-22 10:00:27 483 XXXX0000XXXX00.2018-05-20-19.9af7b6b3.gz
2018-05-22 10:00:27 469 XXXX0000XXXX00.2018-05-20-22.2efc10b0.gz
2018-05-22 10:00:27 560 XXXX0000XXXX00.2018-05-20-22.a53e0ba9.gz
2018-05-22 10:00:27 436 XXXX0000XXXX00.2018-05-20-23.93f78d71.gz
2018-05-22 10:00:27 1437 XXXX0000XXXX00.2018-05-21-00.14b9fa77.gz
2018-05-22 10:00:27 716 XXXX0000XXXX00.2018-05-21-00.3391e29a.gz
2018-05-22 10:00:27 563 XXXX0000XXXX00.2018-05-21-00.4c690fb2.gz
2018-05-22 10:00:27 1410 XXXX0000XXXX00.2018-05-21-00.c9cf6644.gz
2018-05-22 10:00:27 462 XXXX0000XXXX00.2018-05-21-01.4dfb8b5e.gz
2018-05-22 10:00:28 1513 XXXX0000XXXX00.2018-05-21-02.664dfbb4.gz
2018-05-22 10:00:27 1419 XXXX0000XXXX00.2018-05-21-02.cb0d67dc.gz
2018-05-22 10:00:28 435 XXXX0000XXXX00.2018-05-21-03.5c5dd0bb.gz
2018-05-22 10:00:28 1290 XXXX0000XXXX00.2018-05-21-04.ec8276ee.gz
2018-05-22 10:00:28 1738 XXXX0000XXXX00.2018-05-21-04.f476fcda.gz
2018-05-22 10:00:28 668 XXXX0000XXXX00.2018-05-21-05.5f872132.gz
2018-05-22 10:00:28 463 XXXX0000XXXX00.2018-05-21-05.79dcf8b7.gz
2018-05-22 10:00:28 1537 XXXX0000XXXX00.2018-05-21-06.fdcc3798.gz
2018-05-22 10:00:28 883 XXXX0000XXXX00.2018-05-21-07.63608c5f.gz
2018-05-22 10:00:28 475 XXXX0000XXXX00.2018-05-21-07.ca7cce9a.gz
2018-05-22 10:00:28 561 XXXX0000XXXX00.2018-05-21-08.62d8c211.gz
2018-05-22 10:00:28 1513 XXXX0000XXXX00.2018-05-21-08.acca5dc1.gz
2018-05-22 10:00:28 1417 XXXX0000XXXX00.2018-05-21-09.9d882dc7.gz
2018-05-22 10:00:28 470 XXXX0000XXXX00.2018-05-21-10.255a2e39.gz
2018-05-22 10:00:28 1420 XXXX0000XXXX00.2018-05-21-10.284b0dec.gz
2018-05-22 10:00:28 852 XXXX0000XXXX00.2018-05-21-10.9d7d6458.gz
2018-05-22 10:00:28 470 XXXX0000XXXX00.2018-05-21-10.b8a02517.gz
2018-05-22 10:00:28 551 XXXX0000XXXX00.2018-05-21-11.06d26337.gz
2018-05-22 10:00:28 480 XXXX0000XXXX00.2018-05-21-12.af47ef1d.gz
2018-05-22 10:00:28 462 XXXX0000XXXX00.2018-05-21-12.d3db0b3c.gz
2018-05-22 10:00:28 676 XXXX0000XXXX00.2018-05-21-13.fe18a29b.gz
2018-05-22 10:00:28 463 XXXX0000XXXX00.2018-05-21-14.fcb732cb.gz
2018-05-22 10:00:28 1094 XXXX0000XXXX00.2018-05-21-15.162d5146.gz
2018-05-22 10:00:28 473 XXXX0000XXXX00.2018-05-21-16.922178a6.gz
2018-05-22 10:00:28 568 XXXX0000XXXX00.2018-05-21-17.9f38d1cd.gz
2018-05-22 10:00:28 571 XXXX0000XXXX00.2018-05-21-18.08444659.gz
2018-05-22 10:00:28 469 XXXX0000XXXX00.2018-05-21-21.97bac5ec.gz
2018-05-22 10:00:28 1861 XXXX0000XXXX00.2018-05-21-22.4bcb19e7.gz
2018-05-22 10:00:28 467 XXXX0000XXXX00.2018-05-21-23.03c56b25.gz

フラットに配置したファイルからそのままAthenaテーブルを作成する

何も考えずに、Athenaテーブルを作るとこんな形になると思います。

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  requestip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  useragent STRING,
  querystring STRING,
  cookie STRING,
  resulttype STRING,
  requestid STRING,
  hostheader STRING,
  requestprotocol STRING,
  requestbytes BIGINT,
  timetaken FLOAT,
  xforwardedfor STRING,
  sslprotocol STRING,
  sslcipher STRING,
  responseresulttype STRING,
  httpversion STRING,
  filestatus STRING,
  encryptedfields INT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://cloudfront-log-test-bucket/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

Amazon CloudFront ログのクエリ - Amazon Athena

こちらに対してクエリを実行すると、下記のようになります。今回のテストでは、ログの量がたいしたことないので、特に問題にはなりませんが、クエリを発行する度に全スキャンとなってしまいます。

Apache Hive互換のマニフェストファイル(symlink.txt)を用意する

ここで、今回ご紹介したいシンボリックリンクを作成する方法です。以下のような別バケットを用意します。Hiveフォーマットでパーティション化できるように、dt=YYYY-MM-DDという形式でフォルダを作成し、中に特定の日付のログだけをsymlink.txtとして格納しておきます。準備はこれだけでOKです。今回はパーティションを1つしか用意していませんが、もちろんもっとたくさんのパーティションを用意してももちろん問題ありません。

$ aws s3 ls s3://athena-symlink-test-bucket --recursive
2018-05-22 10:08:54 0 dt=2018-05-20/
2018-05-22 10:20:38 1168 dt=2018-05-20/symlink.txt

$ cat symlink.txt
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-09.4035b6c3.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-10.0fa2569a.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-10.eaed67d9.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-12.5b278a9a.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-13.3a38d341.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-13.e61fff03.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-14.4f57349f.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-15.8bd4debf.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-15.98979882.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-16.e2db6ba4.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-17.6a2539c1.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-18.8774fe78.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-19.9af7b6b3.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-22.2efc10b0.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-22.a53e0ba9.gz
s3://cloudfront-log-test-bucket/XXXX0000XXXX00.2018-05-20-23.93f78d71.gz

シンボリックリンクを利用してパーティション化されたAthenaテーブルを作成する

それでは、このシンボリックリンクを利用してAthenaテーブルを作成してみましょう。LOCATIONには、実体が無いはずのシンボリックリンクが配置されたパスを指定しつつ、STORED AS INPUTFORMAT、OUTPUTFORMATを指定することで、上手く動作させることができました。ハイライトした行とテーブル名以外には差異はありません。

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs_parted (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  requestip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  useragent STRING,
  querystring STRING,
  cookie STRING,
  resulttype STRING,
  requestid STRING,
  hostheader STRING,
  requestprotocol STRING,
  requestbytes BIGINT,
  timetaken FLOAT,
  xforwardedfor STRING,
  sslprotocol STRING,
  sslcipher STRING,
  responseresulttype STRING,
  httpversion STRING,
  filestatus STRING,
  encryptedfields INT
)
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
OUTPUTFORMAT  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION 's3://athena-symlink-test-bucket/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

テーブルが作成できたら、パーティションをロードしてみます。想定通りに動いているようです。

それではこちらに対してクエリを実行してみましょう。パーティションを複数用意した場合には、WHERE句でdt = 'YYYY-MM-DD'のように絞れば、パーティションごとにクエリが可能です。

今回やりたかった、スキャン範囲を絞った形でのクエリに成功しました！！

まとめ

これまでは、フルスキャン課金が怖い一方で、一部のファイルだけを移動したりする手間をかけることも煩わしいために、Athenaを試すことに踏み切れないケースもあったように思います。今回ご紹介したシンボリックリンクを利用する方法でこの課題が解決できることがわかったので、もっともっと気軽にAthenaを試すことができるようになるのではないかと思います。ぜひ試してみていただければと思います。

どこかの誰かのお役に立てば嬉しいです。

S3にフラットに配置してしまったログも大丈夫！シンボリックリンクを利用してスキャン範囲を絞ってAthenaからクエリする

フラットに配置されたファイル群をAthenaで扱う場合の課題

シンボリックリンクを利用したAthenaテーブルの作り方

フラットに配置したファイルからそのままAthenaテーブルを作成する

Apache Hive互換のマニフェストファイル(symlink.txt)を用意する

シンボリックリンクを利用してパーティション化されたAthenaテーブルを作成する

まとめ

イベント

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

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

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

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

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

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

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

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

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

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

S3にフラットに配置してしまったログも大丈夫！シンボリックリンクを利用してスキャン範囲を絞ってAthenaからクエリする

フラットに配置されたファイル群をAthenaで扱う場合の課題

シンボリックリンクを利用したAthenaテーブルの作り方

フラットに配置したファイルからそのままAthenaテーブルを作成する

Apache Hive互換のマニフェストファイル(symlink.txt)を用意する

シンボリックリンクを利用してパーティション化されたAthenaテーブルを作成する

まとめ

イベント

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

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

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

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

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

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

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

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

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

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

関連記事

Invalid principal in policyが出たら確認したい！IAMロール・IAMユーザーが削除されるとPrincipal要素のARNがプリンシパルIDに置き換わります

S3ライフサイクルルールの削除ルールでフォルダを残したい

Glueクローラーのデータソースとして別AWSアカウントのS3バケットを指定したい

How to Fetch Latest Objects from AWS S3 and Store Locally using Python

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

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

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