BigQueryでCIDRを処理した話

2024年8月28日

笹山雷雅

URLをコピーしました！

こんにちは、PR TIMESでインターンをしている笹山です。

この記事ではBigQueryでCIDRを処理する方法について解説します。

背景

インターンシップ中にアクセスログの分析を担当することになり、その過程でIPアドレスに関連するデータ処理を実施することになりました。

しかし、生のアクセスログをBigQueryで分析する際に問題に直面しました。BigQueryはIPアドレスはサポートしていますが、CIDR表記はサポートしていません。

公式ドキュメントによると：

This function does not support CIDR notation, such as 10.1.2.3/32.

https://cloud.google.com/bigquery/docs/reference/standard-sql/net_functions#netip_from_string より

そのため、アクセスログからCIDRを使ってIPアドレスを調べるためには、CIDR表記を独自に処理する必要があります。

やったこと

CIDRに合致するIPアドレスを全て出力するCLIの作成

CIDRを等価なIPアドレス集合に展開するために、RustでCLIアプリケーションを開発しました。(GitHub)

このアプリケーションは、Clapクレートを利用しており、次のように使用できます。

% bq-cidr --cidr 192.0.0.0/31 -q
(
  '192.0.0.0',
  '192.0.0.1'
)

上記の例のように、このアプリケーションはCIDRをIPアドレス集合に変換し、それをクエリとしてBigQueryで使用するための形式に整えて出力します。

Clapにより、Parser トレイトを実装した構造体のフィールドはコマンドラインオプションとして自動的に認識され、引数の解析もシンプルなArgs::parse()の呼び出しで済みます。

use clap::Parser;
use ipnetwork::Ipv4Network;

/// CIDRをIPアドレスの一覧に変換します
#[derive(Parser, Debug)]
#[command(version, about, long_about = None)]
struct Args {
    /// 一覧を表示させたいCIDRを入れる(IPv4のみ) e.g.: 127.0.0.1/30
    #[arg(long)]
    cidr: String,
    /// BigQuery用のクエリを出力する
    #[arg(short, long, default_value_t = false)]
    query: bool,
}

fn main() {
    let args = Args::parse();

    if let Err(e) = validate_cidr(args.cidr.clone()) {
        eprintln!("Error: {}. CIDRは192.168.0.0/16のように表記してください。", e);
        std::process::exit(1);
    }

    let cidr = args.cidr;
    let is_query = args.query;

    scan_network(cidr, is_query);
}

ヘルプメッセージも以下のようにDocコメントに基づいて自動生成されます。

% bq-cidr --help                
CIDRをIPアドレスの一覧に変換します

Usage: bq-cidr [OPTIONS] --cidr <CIDR>

Options:
      --cidr <CIDR>  一覧を表示させたいCIDRを入れる(IPv4のみ) e.g.: 127.0.0.1/30
  -q, --query        BigQuery用のクエリを出力する
  -h, --help         Print help
  -V, --version      Print version

しかし、VS Codeの統合ターミナルでCLIアプリケーションを実行した際に、デフォルトのスクロールバッファが1000行に限定されていたため、大規模なネットワーク範囲で生成されたIPリストの上部が表示されずに失われる問題が発生しました。

またこの手法では大量のIPアドレスと照合することになるため、非効率的なクエリになります。

BigQuery で CIDR と IP アドレスのマッチングを判定するクエリ

これらの問題を解決するため、最終的にはBigQueryで直接CIDRを処理するためのクエリの記述に方針を転換しました。

CIDR ベースのフィルタリングを行うため、まずは CIDR からネットワークアドレス部とサブネットマスクの長さを抽出する必要があります。

ip_list テーブルにある cidr カラムから、SPLIT 関数を使用してマスク長を取得し、NET.IP_TRUNC 関数を使ってネットワークアドレスを抽出するためのビューを作成しました。

list AS (
  SELECT
    NET.IP_TRUNC( NET.IP_FROM_STRING( SPLIT(cidr, '/')[SAFE_ORDINAL(1)] ),
      CAST(SPLIT(cidr, '/')[SAFE_ORDINAL(2)] AS INT64 ) ) AS IP,
    CAST(SPLIT(cidr, '/')[SAFE_ORDINAL(2)] AS INT64 ) AS mask_length
  FROM
    `pr-times.database.ip_list` )

次に、指定された期間中のアクセスログの IP アドレスがリストに含まれるかどうかを調べるビュー list_ips を以下のようにして作成します。

まず、access_logsとlistの直積集合を作成し、その集合でaccess_logsのIPアドレスをlistのmask_lengthを用いてネットワークアドレスに変換します。このネットワークアドレスがlist.IPと合致するものを選択することで、アクセスログの中で、ip_listのCIDRの範囲内にあるIPアドレスを持つレコードが残ります。最後にDISTINCTで重複を削除し、アクセスログでip_listに存在するIPアドレスの一覧が選択できます。

list_ips AS (
  WITH
    list AS (
  SELECT
    NET.IP_TRUNC( NET.IP_FROM_STRING( SPLIT(cidr, '/')[SAFE_ORDINAL(1)] ),
      CAST(SPLIT(cidr, '/')[SAFE_ORDINAL(2)] AS INT64 ) ) AS IP,
    CAST(SPLIT(cidr, '/')[SAFE_ORDINAL(2)] AS INT64 ) AS mask_length
  FROM
    `pr-times.database.ip_list` )
  SELECT
    DISTINCT log.client_ip AS IP
  FROM
	  `pr-times.access_logs` AS log
	CROSS JOIN
	  `list`
	WHERE
	  NET.IP_TRUNC( NET.IP_FROM_STRING( log.client_ip ), list.mask_length ) = list.IP
	  -- 期間など他の条件を加える
)