この記事は GMOアドマーケティングAdvent Calendar 2022 7日目の記事です。

こんにちは、GMOアドマーケティングのM.Hと申します。

今回は、GCPのBigQueryに用意されているNet関数を駆使しながら、クエリ文のみでCIDRに含まれるIPアドレスを検索する方法について書いていきたいと思います。

CIDRとは

そもそもCIDRとは、クラスレスアドレッシング(Classless Inter-Domain Routing)の略語であり、「サイダー」と読みます。クラスレスアドレッシングはIPアドレスの管理方式の一つであり、スラッシュ「/」を境にしてIPアドレスとサブネットマスクを表現します。
一般にIPアドレスは一連の連続したIPを含むブロック単位で使われることが多く、そういった範囲を示すためにCIDR表記が重宝されます。たとえば各モバイルキャリア会社が持っているIPアドレスは公式にCIDR表記で公開されています。

IPv4アドレスとIPv6アドレス

IPv4アドレスは32ビットのアドレス長であり、IPv6アドレスは128ビットのアドレス長です。32ビットであればBigQueryではINT64としても保持できるため公式のNet関数でサポートされていますが、IPv6アドレスはオーバーフローを引き起こしてしまうため、BYTES型として変換することになります。

今回使うデータ

本記事では、KDDI株式会社が公開しているグローバルモバイルアドレスIPの範囲がCIDR表記なのでこれを使用していきます。アドレスはIPv4、IPv6が混在しています。 CIDR情報のみ必要のため、スキーマは以下のようなシンプルな形でテーブルを作成しました。

実行SQL

早速以下のSQLクエリをBigQueryで叩いてみましょう。先述のグローバルモバイルアドレスのCIDR情報は carrier テーブルに格納されています。判定したいIPアドレスは今回は配列として1つずつ指定し、FROM句の部分でUNNESTしてカラム ip として展開しています。

SELECT
  *
FROM
  UNNEST(
    ["1.2.3.4",
     "5.6.7.8",
     "182.248.112.155",
     "106.132.0.0",
     "2001:0268:98ff:ffff:ffff:ffff:ffff:ffff",
     "2001:db8:0:0:3456::"
    ]
  ) AS ip
LEFT JOIN
  carrier
ON
  CASE
    WHEN
      BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(SPLIT(cidr,"/")[ OFFSET (0)]))
    THEN
      REGEXP_CONTAINS(
        TO_HEX((NET.SAFE_IP_FROM_STRING(ip) ^ NET.SAFE_IP_FROM_STRING(SPLIT(cidr,"/")[OFFSET(0)]))
          >> (BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) * 8 - CAST(SPLIT(cidr,"/")[ OFFSET (1)] AS INT64))
        ),
        "[^0]+"
      ) = FALSE
END
ORDER BY
  ip

SELECT

FROM

UNNEST(

["1.2.3.4",

"5.6.7.8",

"182.248.112.155",

"106.132.0.0",

"2001:0268:98ff:ffff:ffff:ffff:ffff:ffff",

"2001:db8:0:0:3456::"

]

) AS ip

LEFT JOIN

carrier

CASE

WHEN

BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(SPLIT(cidr,"/")[ OFFSET (0)]))

THEN

REGEXP_CONTAINS(

TO_HEX((NET.SAFE_IP_FROM_STRING(ip) ^ NET.SAFE_IP_FROM_STRING(SPLIT(cidr,"/")[OFFSET(0)]))

>> (BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) * 8 - CAST(SPLIT(cidr,"/")[ OFFSET (1)] AS INT64))

"[^0]+"

) = FALSE

END

ORDER BY

このSQLクエリを実行すると以下の結果になります

ip カラムにあるIPアドレスに対応するCIDRがあればjoinしてその組み合わせを表示し、なければ cidr カラムはnullになります。1つのIPアドレスに対応するCIDRが複数ある場合はその全ての組み合わせが表示されています。
IPv6のアドレス 2001:0268:98ff:ffff:ffff:ffff:ffff:ffff は 2001:268:9800::/40 、 2001:268:9000::/36 の両方に含まれているためどちらもその組み合わせが結果として出てきます。

具体的に何をしているのか

使用関数

NET.SAFE_IP_FROM_STRING ：STRING型で表されているIPアドレスをバイナリ形式であるBYTES型に変換します。IPv4かIPv6かは問われません。また、入力が無効とされた場合でもエラーを返さずNULLを返します。
BYTE_LENGTH ：BYTES型に変換したIPアドレスの値の長さを取得しています。IPv4であれば4、IPv6であれば16です。
TO_HEX ：BYTES型で表されるシーケンスを16進数のSTRINGに変換しています。
REGEXP_CONTAINS ：文字列が指定した正規表現を含んでいる場合にTRUEを返します。

クエリの流れ

全体的にはLEFT JOIN句を用いて、あるIPアドレスがCIDR表記されているアドレス範囲に含まれているかをON句で判定しています。

16行目：CASE句でまずBYTES型に変換されたアドレス情報の長さが一致しているか判定しています。IPv4形式ならIPv4で表されるCIDRだけ見ます。これがないとバイト長が異なる値で比較することになりエラーとなります。
21行目：BYTES型に変換されたアドレス同士で排他的論理和を取っています、サブネットマスク外で立っているビットが異なれば、確実に範囲外となりますのでこれを判断するために演算します。
22行目：先ほど演算した結果をサブネットマスクを表すビット1の個数分右シフトします。これでCIDRの範囲外となるビットだけが残ります。IPv4は32ビット長、IPv6は128ビット長のため両方に対応できるようにしています。
20行目： REGEXP_CONTAINS では、最終的に残ったビット情報を TO_HEX で16進数表記の文字列に直し、それに「0」以外の文字がないかを判定しています。0以外の文字が一切入っていなければサブネットマスク外で一致するため、そのIPアドレスは対応する cidr カラムに含まれていると判定できます。

さいごに

本記事ではGCPのBigQueryだけで、「あるIPアドレスが与えられたCIDR表記のIPアドレスの範囲に含まれているか」ということをIPv4にIPv6の両方の形式に対応できるようにクエリ文を書くという内容でした。
元々IPv4形式のアドレスだけ前提で判定するクエリ文はNet関数である IPV4_TO_INT64 を用いることで簡単に書けますが、IPv6用の関数が用意されていないため自前でなるべく単純にまとめられるよう書いてみました。誰かのお役に立てれば幸いです。

参考

BigQueryのNet関数レファレンス https://cloud.google.com/bigquery/docs/reference/standard-sql/net_functions

明日はT.Mさんによる「ファシリテーションで意識していること」に関しての記事です。
引き続き、GMOアドマーケティング Advent Calendar 2022 をお楽しみください！
■学生インターン募集中！
https://note.gmo-ap.jp/n/nc42c8a60afaf

■エンジニア採用ページはこちら！
https://note.gmo-ap.jp/n/n02cbeb6edb0d

■GMOアドパートナーズ公式noteはこちら！
https://note.gmo-ap.jp/

M.H

CIDRとは

IPv4アドレスとIPv6アドレス

今回使うデータ

実行SQL

具体的に何をしているのか

使用関数

クエリの流れ

さいごに

参考

おすすめ

Railsのタグ機能でN+1問題解消

Professional Scrum Master ⅠとⅡに合格しました

background-imageを活用して、アップする画像を1個で済ませたい