TreasureData(HiveQL)でIPアドレスの範囲を展開する

# 背景

- IPアドレスのマスタデータ（レンジ）から、ログに含まれるIPアドレスを突合したかった
- BETWEENなども試したがうまくいかなかった
    - 処理が重くて終わらない

# 利用するデータ

今回は以下のようなデータを利用しました。（簡単のため必要なデータに絞ってあります

## IPマスタデータ

| ip_from | ip_to | attr |
|:--|:--|:--|
| 1.33.205.128 | 1.33.205.135 | A |
| 1.179.141.224 | 1.179.141.239 | B |
| 223.29.57.192 | 223.29.57.255 | C |

テーブル名を `ip_mast` としておきます

## ログデータ

| user_id | ipaddress |
|--:|:--|
| 1 | 223.29.57.10 |
| 2 | 1.33.205.130 |
| 3 | 1.179.141.238 |
| 4 | 223.29.57.192 |

テーブル名を `user_ip` としておきます

# やりたいこと

`ip_mast` と `user_ip` から以下のようなテーブルを作りたい

| user_id | ipaddress | attr |
|--:|:--|:--|
| 1 | 223.29.57.10 |  |
| 2 | 1.33.205.130 | A |
| 3 | 1.179.141.238 | B |
| 4 | 223.29.57.192 | C |


# 処理できなかったクエリ

```SQL
WITH ip AS (
  SELECT
    CAST(ip_from_array[0] AS DOUBLE) * 16777216 +
    CAST(ip_from_array[1] AS DOUBLE) * 65536 +
    CAST(ip_from_array[2] AS DOUBLE) * 256 +
    CAST(ip_from_array[3] AS DOUBLE) as ip_from_decimal,
    CAST(ip_to_array[0] AS DOUBLE) * 16777216 +
    CAST(ip_to_array[1] AS DOUBLE) * 65536 +
    CAST(ip_to_array[2] AS DOUBLE) * 256 +
    CAST(ip_to_array[3] AS DOUBLE) as ip_to_decimal,
    attr
  FROM
  (
    SELECT
      SPLIT(ip_from, '\\.') AS ip_from_array,
      SPLIT(ip_to, '\\.') AS ip_to_array,
      attr
    FROM ip_mast
  ) t0
)

SELECT
  user_ip.user_id,
  user_ip.ipaddress,
  ip.attr
FROM (
  SELECT
    user_id,
    ipaddress,
    CAST(ipaddress[0] AS DOUBLE) * 16777216 +
    CAST(ipaddress[1] AS DOUBLE) * 65536 +
    CAST(ipaddress[2] AS DOUBLE) * 256 +
    CAST(ipaddress[3] AS DOUBLE) as ipaddress_decimal
  FROM user_ip
) as user_ip JOIN ip
WHERE user_ip.ipaddress_decimal BETWEEN ip.ip_from_decimal AND ip.ip_to_decimal
```

FULL OUTER JOINして、数値にしたIPアドレスで絞り込む作戦
が、データ量が多いと全然処理が進まず無事死亡

# 処理できたクエリ

どうにかIPアドレスを範囲から実際のIPアドレスに展開できる方法はないか考えました
救世主、 **TD_NUMERIC_RANGE**の登場です。

数値にしたIPアドレスに対して、fromからtoまで1つずつインクリメントしたARRAYをLATERAL VIEWで展開します

```SQL
WITH ip AS (
  SELECT
    ip_decimal,
    attr
  FROM (
    SELECT
      CAST(ip_from_array[0] AS DOUBLE) * 16777216 +
      CAST(ip_from_array[1] AS DOUBLE) * 65536 +
      CAST(ip_from_array[2] AS DOUBLE) * 256 +
      CAST(ip_from_array[3] AS DOUBLE) as ip_from_decimal,
      CAST(ip_to_array[0] AS DOUBLE) * 16777216 +
      CAST(ip_to_array[1] AS DOUBLE) * 65536 +
      CAST(ip_to_array[2] AS DOUBLE) * 256 +
      CAST(ip_to_array[3] AS DOUBLE) as ip_to_decimal,
      attr
    FROM
    (
      SELECT
        SPLIT(ip_from, '\\.') AS ip_from_array,
        SPLIT(ip_to, '\\.') AS ip_to_array,
        attr
      FROM ip_mast
    ) t0
  ) as t1 LATERAL VIEW
     TD_NUMERIC_RANGE(CAST(ip_from_decimal as int),CAST(ip_to_decimal+1 as int),1) ip as ip_decimal
)


SELECT
  user_ip.user_id,
  user_ip.ipaddress,
  ip.attr
FROM (
  SELECT
    user_id,
    ipaddress,
    CAST(ipaddress[0] AS DOUBLE) * 16777216 +
    CAST(ipaddress[1] AS DOUBLE) * 65536 +
    CAST(ipaddress[2] AS DOUBLE) * 256 +
    CAST(ipaddress[3] AS DOUBLE) as ipaddress_decimal
  FROM user_ip
) as user_ip LEFT JOIN ip
ON user_ip.ipaddress_decimal = ip.ip_decimal
```

それなりのデータ量がありましたが、無事に処理できました

# 参考

- [Exploding multiple arrays at the same time with TD_NUMERIC_RANGE and TD_ARRAY_INDEX](https://support.treasuredata.com/hc/en-us/articles/360001450788-Hive-Performance-Tuning#Exploding%20multiple%20arrays%20at%20the%20same%20time%20with%20TD_NUMERIC_RANGE%20and%20TD_ARRAY_INDEX)
- [HiveQLでピリオドをパースしたいとき](https://qiita.com/toru-takahashi/items/10c56021ca9be955be77)