Amazon AthenaでIPレンジを使ってアクセスログを集計したい

運用しているウェブアプリケーションへの、Cloudflare経由のリクエスト数はどれくらいあるかな、と調べたくなった。
そのさい、ちょっと試行錯誤することになったので記しておく。

CloudflareのIPレンジはこのあたりに記されているので、これと合致するかを調べていけばよさそう。

以降、アクセスログはS3に置いていて、hostカラムにリクエスト元IPが格納されていて、Amazon AthenaからSELECTできるものとする。
また、実際には日付での絞り込みをおこなわないと、全範囲の探索になってしまうので注意。

is_subnet_of

Prestoの0.233以降だと、is_subnet_ofという関数が用意されていて、IPレンジ内にIPがあるかを判定できるようだった。

Add IP address functions ip_subnet_min(), ip_subnet_max(), ip_subnet_range(), and is_subnet_of().
https://prestodb.io/docs/current/release/release-0.233.html

ただ、Athenaで動いているPrestoは0.217なので、この技は使えない。

Presto 0.217 に基づく Athena エンジンバージョン 2 では、以下の変更が導入されました。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/engine-versions-reference.html

対象のIPを列挙する

単純に対象IPの範囲をすべて展開すればWHERE INでマッチできるのでは、と考えた。RubyだとIPAddrクラスがあったのでこれで範囲の計算ができる。
docs.ruby-lang.org

require 'ipaddr'

print <<"SQL".chomp
SELECT * FROM logs.access_log
WHERE host IN (
SQL

print [
  '173.245.48.0/20',
  '103.21.244.0/22',
  '103.22.200.0/22',
  '103.31.4.0/22',
  '141.101.64.0/18',
  '108.162.192.0/18',
  '190.93.240.0/20',
  '188.114.96.0/20',
  '197.234.240.0/22',
  '198.41.128.0/17',
  '162.158.0.0/15',
  '104.16.0.0/13',
  '104.24.0.0/14',
  '172.64.0.0/13',
  '131.0.72.0/22',
].map{|range|
  IPAddr.new(range).to_range.map{|ip| "'#{ip.to_s}'" }
}.flatten.join(',')

print ')'

生成されたSQLはこんな感じ。

SELECT * FROM logs.access_log
WHERE host IN ('173.245.48.0','173.245.48.1','173.245.48.2','173.245.48.3','173.245.48.4','173.245.48.5',(略)

理論上はこれで探せそうだけど、生成されたクエリが24MBになってしまう。Athenaの1クエリの長さの上限は262KBくらいなので、100分の1に圧縮する必要がある。

The maximum allowed query string length is 262144 bytes, where the strings are encoded in UTF-8
https://docs.aws.amazon.com/athena/latest/ug/service-limits.html

IPレンジにマッチする正規表現を作る

IPレンジは前方一致で探せるので正規表現に変換できるのでは、と考えた。
regexp_trieというライブラリがあったので使ってみる。gfxさんありがとう。
github.com

require 'ipaddr'
require 'regexp_trie'

print <<"SQL".chomp
SELECT * FROM logs.access_log
WHERE regexp_like(host, '
SQL

list = [
  '173.245.48.0/20',
  '103.21.244.0/22',
  '103.22.200.0/22',
  '103.31.4.0/22',
  '141.101.64.0/18',
  '108.162.192.0/18',
  '190.93.240.0/20',
  '188.114.96.0/20',
  '197.234.240.0/22',
  '198.41.128.0/17',
  '162.158.0.0/15',
  '104.16.0.0/13',
  '104.24.0.0/14',
  '172.64.0.0/13',
  '131.0.72.0/22',
].map{|range|
  IPAddr.new(range).to_range.map{|ip| ip.to_s }
}.flatten

print RegexpTrie.union(list).to_s.gsub('[0123456789]', '\\d')

print "')"

こんな調子で長大な正規表現が生成される。[0123456789]は\dと同じなので置換することでちょっと短縮を試みたりした。

SELECT * FROM logs.access_log
WHERE regexp_like(host, '(?-mix:1(?:7(?:3\.245\.(?:4(?:8\.(?:1(?:(?:0\d?|1\d?|2\d?|3\d?|4\d?|5\d?|6\d?|7\d?|8\d?|9\d?))?(略)

これでも1.1MBでうまくいかない。250KBずつ4回くらいに分割して実行してもいいのだけど、やりたいことはたいして難しくないのに4回SELECTすることになるのはつらい。

IPレンジの下限と上限を指定して探す

Athenaではis_subnet_ofみたいな関数は使えないけどIPADDRESS型は使える、ということをid:mangano-itoに教えてもらえた。このとおり、IPアドレスとしての大小比較ができる。

select CAST('8.8.8.8' AS IPADDRESS) > CAST('8.8.8.10' AS IPADDRESS) AS col1, '8.8.8.8' > '8.8.8.10' as col2
col1	col2
false	true

下限と上限を指定して条件を生成して、ORでくっつければよさそう。

require 'ipaddr'

print <<"SQL".chomp
SELECT * FROM logs.access_log
WHERE (
SQL

print [
  '173.245.48.0/20',
  '103.21.244.0/22',
  '103.22.200.0/22',
  '103.31.4.0/22',
  '141.101.64.0/18',
  '108.162.192.0/18',
  '190.93.240.0/20',
  '188.114.96.0/20',
  '197.234.240.0/22',
  '198.41.128.0/17',
  '162.158.0.0/15',
  '104.16.0.0/13',
  '104.24.0.0/14',
  '172.64.0.0/13',
  '131.0.72.0/22',
].map{|range|
  from = IPAddr.new(range).to_range.first.to_s
  to = IPAddr.new(range).to_range.last.to_s
  "(CAST(host AS IPADDRESS) >= IPADDRESS '#{from}' AND CAST(host AS IPADDRESS) <= IPADDRESS '#{to}')"
}.flatten.join(' OR ')

print ")"

実行するとこのとおりで、1751バイトに納まったことで、無事Athenaにクエリを投げて結果を取り出すことができた。

SELECT * FROM logs.access_log
WHERE ((CAST(host AS IPADDRESS) >= IPADDRESS '173.245.48.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '173.245.63.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '103.21.244.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '103.21.247.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '103.22.200.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '103.22.203.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '103.31.4.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '103.31.7.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '141.101.64.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '141.101.127.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '108.162.192.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '108.162.255.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '190.93.240.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '190.93.255.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '188.114.96.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '188.114.111.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '197.234.240.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '197.234.243.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '198.41.128.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '198.41.255.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '162.158.0.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '162.159.255.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '104.16.0.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '104.23.255.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '104.24.0.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '104.27.255.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '172.64.0.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '172.71.255.255') OR (CAST(host AS IPADDRESS) >= IPADDRESS '131.0.72.0' AND CAST(host AS IPADDRESS) <= IPADDRESS '131.0.75.255'))