IPアドレスデータ型、演算SQL

ちょっとした興味で AWS が確保しているパブリックIPを眺めています。比較するのにDBにINSERTでまとめて突っ込んでみるかと思い立ちました。

PostgreSQLには面白い機能があるのを知りました。こんなことできるのは Splunk とか Sumologic とか Apache Metron だけかと思ってました。

Postgres makes working with IPs very easy. It supports an inet column type

Comparing IP Addresses in SQL

上のページにはMySQL,Redshift,SQL Serverも説明されていて分かり易いです。

最も手軽なのはSQLite3なのでさらにGoogleって公式 mailing list のアーカイブと思われるページを見つけました。

MySQL has INET_ATON/INET_NTOA

[sqlite-dev] Adding ipv4 string conversion as built-in functions – Google Groups

このページから C コードをコピペし、SQLiteのUDFを今開発中で70%ぐらい完成したとこです。

sqlite> select inet_ntop_ipv4int(“176.32.64.13”);

GitHub – User Defined Function for SQLite3. It converts IPv4 string<->Int64 representations. Int64->String is still under development.

一段落ついたらまたこのブログに書きます。

本業で良く使う Impala もUDFがあるので移植してみたい。

native code UDFs written in C++, and Java-based Hive UDFs

User-Defined Functions (UDFs) | 6.3.x | Cloudera Documentation

ImpalaではJavaも使えます。

SQLiteでネットワーク分析

BOM explosion などの SQL SELECTクエリーで Recursive CTEを使う例はよく見かけます。

木構造の親子関係

CTE(共通テーブル式)の再帰問い合わせ – ITエンジニア日記 ~NO SKILL, NO LIFE~

グラフDBで扱うようないわゆる友達の友達を辿っていくようなネットワーク分析でCTE使う例をGoogleりましたが意外に少なかったです。

一番参照されているのはJoe Celko御大の以下のページだと思います。完全に理解しきれてないですが結論はSQLは不向きってことになってますし、実際に純粋なRecursive CTEも無さそう。

cliques and equivalence classes are better handled with a graph database than with SQL.

The SQL of Membership: Equivalence Classes & Cliques – Simple Talk

ただ確実にループが無いことが分かっていればSQLで用が済むこともあると思います。最近 sqlite3 でやった例をこのブログに書きました。

uh_connected as (
select * from uh_both
union
select b.node1, exp.node2
from uh_both b, uh_connected exp
where b.node2 = exp.node1)
select node1,group_concat(node2,'|') from uh_connected

GitHubに置いたSQlite3 SQLの一部

これもcommunity detection, クラスター分析って言えるのか??

MariaDBのRecursive CTEのドキュメントは画像がたくさんあって分かりやすいです。Joe Celkoのページでも出てくる無限ループについても書いてありました。

max_recursive_iterations avoids infinite loops.

Recursive Common Table Expressions Overview – MariaDB Knowledge Base