ウェブアクセラレータでアクセスログのアップロード機能が提供開始されました。
これはウェブアクセラレータのアクセスログをさくらのクラウドのオブジェクトストレージにアップロードしてくれる機能です。
オブジェクトストレージにファイルがあれば clickhouse-localなどを用いてSQLが投げれるはず、ということで早速試してみました。
(※ 免責: 掲載内容は私自身の個人的な見解であり、必ずしも所属する企業や組織の立場、意見を代表するものではありません)
準備1: オブジェクトストレージへアクセスログをアップロードするための設定
まずはマニュアルに従ってオブジェクトストレージとウェブアクセラレータの設定を行います。
設定したらログがアップロードされるまでしばらく待ちます。 (現在の仕様だと1時間ごとにアップロードされるようです)
準備2: SQLを投げるための準備(clickhouse-local)
今回は clickhouse-localを用います。
認証情報の準備(AWSプロファイル)
認証のために~/.aws配下にconfigとcredentialを登録しておきます。
$ vi ~/.aws/config
[default]
region = jp-north-1
output = json
endpoint_url = https://s3.isk01.sakurastorage.jp
$ vi ~/.aws/credentials
[default]
aws_access_key_id = {発行したオブジェクトストレージへのアクセスキーID}
aws_secret_access_key = {発行したオブジェクトストレージのシークレットアクセスキー}
clickhouse-localの実行
今回はdockerで実行します。
$ docker run -it --rm -v $HOME:/root/ clickhouse/clickhouse-server:latest clickhouse-local -m
SQLを投げてみる
以下のようなSQLでアクセスログにクエリすることができます。
SELECT *
FROM s3(
'https://{バケット名}.s3.isk01.sakurastorage.jp/アクセスログのオブジェクトのパス',
'JSONEachRow'
) ;
バケット名がexample、アクセスログのオブジェクトへのパスが sakura-webaccelerator/example.user.webaccel.jp/2024/11/27/access-20241127T13.log.gzの場合は以下のようになります。
SELECT *
FROM s3(
'https://example.s3.isk01.sakurastorage.jp/sakura-webaccelerator/example.user.webaccel.jp/2024/11/27/access-20241127T13.log.gz',
'JSONEachRow'
) ;
なおバケット内にアクセスログしか置いていないなら以下のようにしてもOKです。
SELECT *
FROM s3(
'https://{バケット名}.s3.isk01.sakurastorage.jp/**.gz',
'JSONEachRow'
) ;
利用できるワイルドカードについてはこちらを参照ください。
結果確認
私の環境だと以下のような結果となりました(一部情報を加工してマスクしてます)
┌─time──────────────────────┬─http_host─────────────────┬─scheme─┬─remote_addr───┬─remote_port─┬─remote_user─┬─request───────────────────┬─status─┬─bytes_sent─┬─body_bytes_sent─┬─referer───────────────────────────┬─x_cache─┬─msec───────────┬─request_id───────────────────────┬─user_agent────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 1. │ 2024-11-27T13:28:34+09:00 │ xxx.user.webaccel.jp │ http │ 192.0.2.1 │ 46145 │ │ GET / HTTP/1.1 │ 200 │ 3590 │ 3068 │ │ MISS │ 1732681714.282 │ xxx │ Mozilla/5.0 ... │ 2. │ 2024-11-27T13:28:34+09:00 │ xxx.user.webaccel.jp │ http │ 192.0.2.1 │ 46145 │ │ GET /index.html HTTP/1.1 │ 200 │ 1376 │ 855 │ http://example.user.webaccel.jp/ │ MISS │ 1732681714.518 │ xxx │ Mozilla/5.0 ... │ 3. │ 2024-11-27T13:28:35+09:00 │ xxx.user.webaccel.jp │ http │ 192.0.2.1 │ 46145 │ │ GET / HTTP/1.1 │ 200 │ 3590 │ 3068 │ │ MISS │ 1732681715.550 │ xxx │ Mozilla/5.0 ... │ 4. │ 2024-11-27T13:28:35+09:00 │ xxx.user.webaccel.jp │ http │ 192.0.2.1 │ 46145 │ │ GET /style.css HTTP/1.1 │ 200 │ 1376 │ 855 │ http://example.user.webaccel.jp/ │ MISS │ 1732681715.659 │ xxx │ Mozilla/5.0 ... │ └───────────────────────────┴───────────────────────────┴────────┴───────────────┴─────────────┴─────────────┴───────────────────────────┴────────┴────────────┴─────────────────┴───────────────────────────────────┴─────────┴────────────────┴──────────────────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
DESCRIBEした結果はこんな感じでした。
┌─name────────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
1. │ time │ Nullable(String) │ │ │ │ │ │
2. │ http_host │ Nullable(String) │ │ │ │ │ │
3. │ scheme │ Nullable(String) │ │ │ │ │ │
4. │ remote_addr │ Nullable(String) │ │ │ │ │ │
5. │ remote_port │ Nullable(String) │ │ │ │ │ │
6. │ remote_user │ Nullable(String) │ │ │ │ │ │
7. │ request │ Nullable(String) │ │ │ │ │ │
8. │ status │ Nullable(String) │ │ │ │ │ │
9. │ bytes_sent │ Nullable(String) │ │ │ │ │ │
10. │ body_bytes_sent │ Nullable(String) │ │ │ │ │ │
11. │ referer │ Nullable(String) │ │ │ │ │ │
12. │ x_cache │ Nullable(String) │ │ │ │ │ │
13. │ msec │ Nullable(String) │ │ │ │ │ │
14. │ request_id │ Nullable(String) │ │ │ │ │ │
15. │ user_agent │ Nullable(String) │ │ │ │ │ │
└─────────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
項目の詳細は以下にドキュメントがありますのでこれを見ながら色々すれば良さそうです。
これを使えばアクセス数ランキングを出したり、キャッシュにヒットしなかったリクエストの割合を数えたりと色々できそうです。
アクセス数集計の例:
SELECT COUNT(*) as count, request
FROM s3(
'https://{バケット名}.s3.isk01.sakurastorage.jp/**.gz',
'JSONEachRow'
)
GROUP BY request
ORDER BY count DESC
;
┌─count─┬─request───────────────────┐
1. │ 2 │ GET / HTTP/1.1 │
2. │ 2 │ GET /news/sp.php HTTP/1.1 │
└───────┴───────────────────────────┘
終わりに
ということでウェブアクセラレータのアクセスログをオブジェクトストレージにアップロードしつつSQLを投げてみました。
今回は以上です。
