Az IP-cím alapján történő lokáció egyre nélkülözhetetlenebb lesz a mai adat-éhes folyamataink számára. A MaxMind-nál elérhető egy elég jó minőségű adatbázis, ami ismert IP tartományokhoz geokoordinátákat rendel hozzá. Az adatbázis különböző formákban elérhető, valamint van hozzá néhány eszköz, amivel a lekérdezéseket/feloldásokat el lehet végezni. Viszont azt vettem észre, hogy ezek az eszközök nem minden körülmények közt alkalmazhatók - például olyan helyzetekben, amikor nem tudunk más rendszerekhez integrálódni és/vagy a feldolgozás tisztán egy adatbázison belül zajlik (gondoljatok például egy adattárházra, vagy egy régimódi csalás-megelőző rendszerre). Többnyire Oracle adatbázissal kell dolgoznom, és erre az esetre nem igazán találtam kész eszközt, ezért megosztjuk a publikummal a megoldásunkat. Elérhető a GitHub oldalon: https://github.com/harangp/maxmind-oracle

A probléma

Az adatbázis egy IP-tartományt geolokációját tartalmazza - és nem egy-egy IP cím helyét. Erről a témáról (IP range / subnet) részletesebben itt olvashattok:

A legfontosabb dolog, hogyha megvizsgáljátok a country_blocks.network mezőt, akkor valami hasonlót fogtok látni: 192.168.5.64/26. Ez természetesen egy szöveges reprezentáció, és át kell alakítani ahhoz, hogy dolgozni tudjunk vele.

Egy IP cím több subnet-be is tartozhat - ez teljesen normális. Pl.: ott van az internet-szolgáltató globális alhálózata, az ország alhálózata, a regionális alhálózat, stb. Az a cél, hogy megtaláljuk a legpontosabb egyezőséget, és az ahhoz kapcsolódó koordinátát oldjuk fel. Az nyilvánvaló, hogy szövegeket nem fogunk az adatbázisban összehasonlítani (mindenféle kalkulációkat lekérdezés-időben végrehajtva) mivel ezzel nem lehet hatékony megoldást összeállítani, valami más megoldásra lesz szüksg.

Tábla struktúra

Egy régóta létező (ám kevésbé ismert) Oracle funkciót fogunk felhasználni: a funkció által meghatáozott virtuális oszlop-definíciót (function-based virtual column). Az alapkoncepciója az, hogy létrehozhatunk egy oszlopot, aminek a tartalmát a tábla többi létező oszlopa alapján valamilyen függvény határozza meg. Az tényleges adatot a soron végzett műveletek (létrehozás, módosítás) során automatikusan frissülnek. Ha a függvény determinisztikus (tehát ugyanazokra az bemenetekre mindig ugyanazt a kimenetet adja), akkor felhasználható más célokra is - a mi esetünkben például arra, hogy része legyen egy index-nek.

Karaktereket vagy karakterláncokat összehasonlítani sokkal kevésbé hatékony, mint számokat. Az IPv4 címek tulajdonképpen 32 bit eljőjel-nélküli egész számok (integer), és az egyezőség-vizsgálatnál nagy sebességnövekedést lehet ellérni a modern processzorokon, amik vektor vagy mátrix instrukciókat is tartalmaznak. Egy további előny, hogy számok indexelésekor sokkal kevesebb memóriát illetve tárterületet használ, mintha varchar2 adatokkal tennénk ugyanezt.

A fent említett megközelítéseket felhasználva az alábbi virtuális oszlopokat adjuk hozzá a stardard adatbázis struktúrához:

Mindegyiket a network mezőben lévő szövegből állítjuk elő.

IP cím számmá alakítása

Az IP cím a szokásos 192.168.5.64 formátumában érkezik - ahogy az IP tartomány pedig a 192.168.5.64/26 alakban. Ahhoz, hogy szám-alakká tudjuk alakítani a szöveget, először fel kell darabolni, konvertálni, eltolni, majd össze kell adni a számokat. A regexp-nek köszönhetően, ezt nagyon egyszerűen meg tudjuk tenni, így:

1
2
3
4
5
6
select 
to_number(regexp_substr(ip, '\d+', 1, 1)) * 16777216 +
to_number(regexp_substr(ip, '\d+', 1, 2)) * 65536 +
to_number(regexp_substr(ip, '\d+', 1, 3)) * 256 +
to_number(regexp_substr(ip, '\d+', 1, 4)) as numip
from dual

*természetsen feltételezzük, hogy az átalakítandó szöveg az ip nevű változóban található.

Lekérdezés

Amikor a lekérdezzük az IP címet, a problémánk az, hogy az IP tartomány maszkolva van a (ez a /xx rész a network mezőben, vagy, ha jobban tetszik, ez a significant_bits oszlop tartalma, amit korábban kiszámoltunk) - ez konkrétan azt jelentané, hogy minden egyes rekordnál le kellene generálnunk a maszkolt rekordban található maszk szerint az IP címet, és úgy hasonlítani össze a maszkolt tartománnyal (amit a virtuális oszlopban tárolunk). Ehelyett azt csináljuk, hogy legeneráljuk az összes lehetséges maszkolt IP címet - 32 van belőle - és ezeket kérdezzük le az adatbázisból.

1
2
3
4
select 
bitand(numip, bitand(4294967295 * power(2, rownum-1), 4294967295))
from dual
connect by rownum <= 32

Figyeljünk arra, hogy a numip mező az már numerikus érték, a lekérdezendő IP címet reprezentálja

Innentől a feladatunk rendkívül egyszerű: csak le kell kérdezni azokat a rekordokat, ahol megegyezik a masked_network mező értéke. Csökkenő sorrendbe rendezzük a significant_bits virtuális oszlop alapján, és az első elem fogja kiadni a számunkra értékes eredményt.

Index-elés

Ezzel a megközelítéssel azt tudjuk elérhi, hogy elég csak egyetlen indexet létrehozni a masked_network mezőn, és rendben is vagyunk. Ezzel a technikával a hosszadalmas scan-ek helyett 32 darab index-alapú direkt azonosító lekérdezésel el tudja intézni - ráadásul ezt az adatbázis belül tudja is párhuzamosítani..

Ha ezt az eljárást követjük, akkor egy majdnem optimális megoldást kapunk, amit nagyon jól tud használni magasabb verziószámú Oracle adatbázis. Tapasztalataink szerint, amikor kis méretű adatokon próbálkozol (pl.: részleges import a fejlesztői gépeken) ez jól működik, azonban amikor a teljes adatbázison akarod futtatni, akkor az adatbázis túl akar járni az eszeden full-table-scan-t futtatva, mivel ki kell nyernie valahogy a significant_bits oszlop értékeit. Az Oracle már csak ilyen. Ezért az optimális megközelítés az, hogy a significant_bits mezőt hozzáadjuk az indexhez, és akkor magát az indexet is tudja haszhálni az Oracle a sorrendezéshez.