2024/10/07

AWS IP 範囲 JSON を DuckDB で分析する

2444 Words

2024/10/07 13:31 +0000

これは何

DuckDB を使って AWS が公開している IP 範囲の JSON ファイルを分析してみたメモ

JSON を用意

curl -O https://ip-ranges.amazonaws.com/ip-ranges.json

DuckDB で read_json_auto を使用して、直接 URL から JSON をロードすることも出来る
しかし今回の場合、 JSON を丸ごとロードするのではなく、 JSON に含まれる .prefixes の中身だけロードしたいので、 jq で抽出した prefixes.json を作成して使用する

cat ip-ranges.json | jq ".prefixes" > prefixes.json

DuckDB で JSON をロードする

DuckDB を起動

duckdb

JSON をロードしてテーブルにする

CREATE OR REPLACE TABLE ipv4_prefixes AS
SELECT *
FROM read_json_auto('prefixes.json');

Describe を使用して、テーブルの定義を確認する

D DESCRIBE ipv4_prefixes;
┌──────────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│     column_name      │ column_type │  null   │   key   │ default │  extra  │
│       varchar        │   varchar   │ varchar │ varchar │ varchar │ varchar │
├──────────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ ip_prefix            │ VARCHAR     │ YES     │         │         │         │
│ region               │ VARCHAR     │ YES     │         │         │         │
│ service              │ VARCHAR     │ YES     │         │         │         │
│ network_border_group │ VARCHAR     │ YES     │         │         │         │
└──────────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

適当にいくつかのデータを取得してみる

D SELECT * FROM ipv4_prefixes LIMIT 5;
┌─────────────────┬────────────────┬─────────┬──────────────────────┐
│    ip_prefix    │     region     │ service │ network_border_group │
│     varchar     │    varchar     │ varchar │       varchar        │
├─────────────────┼────────────────┼─────────┼──────────────────────┤
│ 3.4.12.4/32     │ eu-west-1      │ AMAZON  │ eu-west-1            │
│ 3.5.140.0/22    │ ap-northeast-2 │ AMAZON  │ ap-northeast-2       │
│ 15.190.244.0/22 │ ap-east-2      │ AMAZON  │ ap-east-2            │
│ 15.230.15.29/32 │ eu-central-1   │ AMAZON  │ eu-central-1         │
│ 15.230.15.76/31 │ eu-central-1   │ AMAZON  │ eu-central-1         │
└─────────────────┴────────────────┴─────────┴──────────────────────┘

良い感じに取得できているので、もっと高度なクエリを書いていく

リージョンごとの prefix 数を集計してみる

実行するクエリ

SELECT
    region,
    COUNT(*) AS prefix_count
FROM
    ipv4_prefixes
GROUP BY
    region
ORDER BY
    prefix_count DESC;

実行結果

D SELECT
      region,
      COUNT(*) AS prefix_count
  FROM
      ipv4_prefixes
  GROUP BY
      region
  ORDER BY
      prefix_count DESC;
┌────────────────┬──────────────┐
│     region     │ prefix_count │
│    varchar     │    int64     │
├────────────────┼──────────────┤
│ us-east-1      │         1068 │
│ us-west-1      │          491 │
│ us-east-2      │          488 │
│ us-west-2      │          477 │
│ eu-central-1   │          420 │
│ eu-west-1      │          416 │
│ GLOBAL         │          396 │
│ ap-southeast-1 │          338 │
│ ap-northeast-1 │          331 │
│ eu-west-2      │          249 │
│ ap-southeast-2 │          244 │
│ ap-south-1     │          242 │
│ sa-east-1      │          232 │
│ ap-northeast-2 │          192 │
│ eu-south-1     │          170 │
│ ca-central-1   │          162 │
│ eu-north-1     │          159 │
│ eu-west-3      │          148 │
│ ap-northeast-3 │          126 │
│ cn-north-1     │          125 │
│ me-south-1     │          119 │
│ cn-northwest-1 │          113 │
│ af-south-1     │          112 │
│ ap-east-1      │          110 │
│ us-gov-west-1  │          101 │
│ ap-southeast-3 │           93 │
│ eu-south-2     │           91 │
│ me-central-1   │           77 │
│ us-gov-east-1  │           76 │
│ ap-south-2     │           71 │
│ ap-southeast-4 │           68 │
│ il-central-1   │           64 │
│ eu-central-2   │           58 │
│ ca-west-1      │           51 │
│ ap-southeast-5 │           48 │
│ ap-southeast-7 │           35 │
│ mx-central-1   │           34 │
│ ap-east-2      │           29 │
│ eusc-de-east-1 │           17 │
│ ap-southeast-6 │           15 │
├────────────────┴──────────────┤
│ 40 rows             2 columns │
└───────────────────────────────┘

特定リージョンとサービスで利用される prefix を抽出

実行するクエリ

SELECT
    ip_prefix,
    region,
    network_border_group
FROM
    ipv4_prefixes
WHERE
    service = 'API_GATEWAY' AND region = 'ap-northeast-1';

実行結果

D SELECT
      ip_prefix,
      region,
      network_border_group
  FROM
      ipv4_prefixes
  WHERE
      service = 'API_GATEWAY' AND region = 'ap-northeast-1';
┌──────────────────┬────────────────┬──────────────────────┐
│    ip_prefix     │     region     │ network_border_group │
│     varchar      │    varchar     │       varchar        │
├──────────────────┼────────────────┼──────────────────────┤
│ 18.180.88.0/23   │ ap-northeast-1 │ ap-northeast-1       │
│ 3.112.162.0/23   │ ap-northeast-1 │ ap-northeast-1       │
│ 3.112.96.160/27  │ ap-northeast-1 │ ap-northeast-1       │
│ 35.73.115.128/25 │ ap-northeast-1 │ ap-northeast-1       │
│ 35.75.130.0/24   │ ap-northeast-1 │ ap-northeast-1       │
│ 35.75.131.0/26   │ ap-northeast-1 │ ap-northeast-1       │
│ 35.77.112.0/22   │ ap-northeast-1 │ ap-northeast-1       │
│ 35.77.124.0/23   │ ap-northeast-1 │ ap-northeast-1       │
└──────────────────┴────────────────┴──────────────────────┘

特定の IP アドレスが含まれる prefix を抽出する

文字列である IP アドレスを比較するため数値に変換する MACRO を定義する

CREATE OR REPLACE MACRO ip_to_int(ip) AS (
    (CAST(SPLIT_PART(ip, '.', 1) AS BIGINT) << 24) +
    (CAST(SPLIT_PART(ip, '.', 2) AS BIGINT) << 16) +
    (CAST(SPLIT_PART(ip, '.', 3) AS BIGINT) << 8) +
    CAST(SPLIT_PART(ip, '.', 4) AS BIGINT)
);

IP アドレスを比較しつつ抽出する VIEW を定義する

CREATE OR REPLACE VIEW ipv4_prefix_ranges AS
SELECT
    ip_prefix,
    region,
    service,
    network_border_group,
    ip_to_int(SPLIT_PART(ip_prefix, '/', 1)) AS ip_start,
    ip_to_int(SPLIT_PART(ip_prefix, '/', 1)) + (1 << (32 - CAST(SPLIT_PART(ip_prefix, '/', 2) AS INT))) - 1 AS ip_end
FROM
    ipv4_prefixes;

特定 IP アドレスが含まれる prefix を検索するクエリを定義

SELECT
    *
FROM
    ipv4_prefix_ranges
WHERE
    ip_to_int('18.180.88.7') BETWEEN ip_start AND ip_end;

実行結果

D CREATE OR REPLACE MACRO ip_to_int(ip) AS (
      (CAST(SPLIT_PART(ip, '.', 1) AS BIGINT) << 24) +
      (CAST(SPLIT_PART(ip, '.', 2) AS BIGINT) << 16) +
      (CAST(SPLIT_PART(ip, '.', 3) AS BIGINT) << 8) +
      CAST(SPLIT_PART(ip, '.', 4) AS BIGINT)
  );
D CREATE OR REPLACE VIEW ipv4_prefix_ranges AS
  SELECT
      ip_prefix,
      region,
      service,
      network_border_group,
      ip_to_int(SPLIT_PART(ip_prefix, '/', 1)) AS ip_start,
      ip_to_int(SPLIT_PART(ip_prefix, '/', 1)) + (1 << (32 - CAST(SPLIT_PART(ip_prefix, '/', 2) AS INT))) - 1 AS ip_end
  FROM
      ipv4_prefixes;
D SELECT
      *
  FROM
      ipv4_prefix_ranges
  WHERE
      ip_to_int('18.180.88.7') BETWEEN ip_start AND ip_end;
┌────────────────┬────────────────┬─────────────┬──────────────────────┬───────────┬───────────┐
│   ip_prefix    │     region     │   service   │ network_border_group │ ip_start  │  ip_end   │
│    varchar     │    varchar     │   varchar   │       varchar        │   int64   │   int64   │
├────────────────┼────────────────┼─────────────┼──────────────────────┼───────────┼───────────┤
│ 18.180.0.0/15  │ ap-northeast-1 │ AMAZON      │ ap-northeast-1       │ 313786368 │ 313917439 │
│ 18.180.0.0/15  │ ap-northeast-1 │ EC2         │ ap-northeast-1       │ 313786368 │ 313917439 │
│ 18.180.88.0/23 │ ap-northeast-1 │ API_GATEWAY │ ap-northeast-1       │ 313808896 │ 313809407 │
└────────────────┴────────────────┴─────────────┴──────────────────────┴───────────┴───────────┘

感想

ちょっと大きめな JSON を分析するのに DuckDB は便利に使えて良い感じ
- JSON データからテーブル定義は自動的に行なってくれる
  - あとは欲しい情報を取得する SQL をよしなに書けば良い
- メモリに乗り切る量のデータであれば使い捨てれる
  - 乗り切らない場合は Persistent Database として扱うためにファイルとして保存が必要になる
    - https://duckdb.org/docs/api/cli/overview.html#in-memory-vs-persistent-database
  - まぁこれもそれほど手間ではない
ただ SQL 知識は必要になるので、そこはもっと鍛えていきたい感じがある
- GenAI をベースに考えられるので、学習ハードルは結構下がっている印象
- LeetCode とか SQL Academy をチマチマやっていきたい
  - https://leetcode.com/problemset/database/
  - https://sql-academy.org/

試した環境

% sw_vers
ProductName:            macOS
ProductVersion:         14.7
BuildVersion:           23H124
% jq --version
jq-1.7.1
% duckdb -version
v1.1.1 af39bd0dcf