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

16496 ワード
hive IPアドレス TreasureData hive テキストリンク

背景

  • 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

処理できなかったクエリ

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で展開します

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

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

参考

Finally文ブロックの実行
[セットトップ]C++Coding Standard