Come riassumere una tabella di geolocalizzazione unendo blocchi IPv4 di rete contigui
Ogni dispositivo su Internet è indirizzabile in modo univoco tramite un indirizzo IP (protocollo Internet). Lo spazio globale degli indirizzi IP è supervisionato dalla Internet Assigned Numbers Authority (IANA). Tradizionalmente, IANA alloca lo spazio degli indirizzi in blocchi di prefisso /8 per IPv4, che vengono successivamente assegnati ai provider di servizi Internet e ad altre organizzazioni. Esistono vari database per mappare questi blocchi IP ai rispettivi proprietari, insieme alle informazioni sul paese e sulla città di origine.
In qualità di CSIRT nazionale canadese, noi, il Centro canadese per la sicurezza informaticafare molto affidamento sul riferimento a questi database per cercare un determinato IP o migliorare interi set di dati tramite SQL JOIN. Tuttavia, non tutti i casi d’uso necessitano di precisione fino al livello cittadino; a volte sono sufficienti solo le informazioni sul paese.
All’interno di un paese, molti blocchi di rete sono contigui. Il consolidamento di questi in mega blocchi può ridurre significativamente la dimensione di una tabella che mappa mega blocchi in paesi, portando a operazioni JOIN migliorate.
In questo articolo dimostreremo come riassumere una tabella di geolocalizzazione unendo blocchi di rete contigui.
Supponiamo che la nostra tabella di geolocalizzazione contenga i seguenti dati:
+----------+-------+---------+-----------+-----------+
| start_ip | end_ip| country | city | owner |
+----------+-------+---------+-----------+-----------+
| 1 | 2 | ca | Toronto | Telus |
| 3 | 4 | ca | Quebec | Rogers |
| 5 | 8 | ca | Vancouver | Bell |
| 10 | 14 | ca | Montreal | Telus |
| 19 | 22 | ca | Ottawa | Rogers |
| 23 | 29 | ca | Calgary | Videotron |
+----------+-------+---------+-----------+-----------+
Qui, start_ip rappresenta il numero più basso nel blocco di rete e end_ip rappresenta il più grande. Normalmente, questi numeri sono molto più grandi. Ad esempio, il server DNS di Google 8.8.8.8 è rappresentato dal numero 134744072. Utilizziamo semplici valori sintetici a scopo illustrativo.
Per iniziare, facciamo un semplice riassunto. Ad esempio, contando il numero di indirizzi IP assegnati a ciascun Paese. Ciò può essere ottenuto raggruppando i dati per paese e sommando il numero di IP in ciascun blocco di rete.
SELECT
country,
SUM(end_ip - start_ip + 1) as num_ip
FROM
geo_table
GROUP BY
country
Questa affermazione raggruppa le righe per paese e applica a SUM funzione di aggregazione, calcolando il numero totale di IP per ciascun paese. È importante notare che il SUM l’aggregazione è associativa, il che significa che l’ordine in cui si somma non ha importanza, simile alle addizioni in matematica.
+---------+--------+
| country | num_ip |
+---------+--------+
| ca | 24 |
+---------+--------+
Ora analizziamo le complessità legate all’aggregazione di blocchi di rete contigui. Facendo riferimento alla nostra tabella originale, dobbiamo fondere insieme le prime 3 righe. I blocchi 1–2, 3–4, 5–8 dovrebbero risultare nel mega blocco 1–8. Dobbiamo anche fondere le ultime 2 righe. I blocchi 19–22 e 23–29 risultano in 19–29. Il nostro obiettivo è produrre la seguente tabella:
+----------+-------+---------+
| start_ip | end_ip| country |
+----------+-------+---------+
| 1 | 8 | ca |
| 10 | 14 | ca |
| 19 | 29 | ca |
+----------+-------+---------+
Il rilevamento di blocchi contigui richiede informazioni tra le righe e l’ordine delle righe diventa cruciale. Fortunatamente, le funzioni analitiche a finestra forniscono una soluzione offrendo un meccanismo per il riferimento tra record. Queste funzioni, come LEAD E LAGconsentire confronti con i valori delle righe precedenti e successive, facilitando l’identificazione di blocchi IP contigui.
Applichiamo il LEAD E LAG funzioni di finestra sulla nostra tabella. Si noti che all’interno del OVER clausola specifichiamo comunque che i nostri dati dovranno essere raggruppati per Paese PARTITION BY country ma in più specifichiamo l’ordine di questi dati ORDER BY start_ip.
SELECT
*,
LAG(end_ip) OVER (
PARTITION BY country
ORDER BY start_ip) AS prev_end_ip,
LEAD(start_ip) OVER (
PARTITION BY country
ORDER BY start_ip) AS next_start_ip
FROM
geo_table
La tabella risultante view_1 è come segue:
+----------+-------+---------+-------------+---------------+
| start_ip | end_ip| country | prev_end_ip | next_start_ip |
+----------+-------+---------+-------------+---------------+
| 1 | 2 | ca | null | 3 |
| 3 | 4 | ca | 2 | 5 |
| 5 | 8 | ca | 4 | 10 |
| 10 | 14 | ca | 8 | 19 |
| 19 | 22 | ca | 14 | 23 |
| 23 | 29 | ca | 22 | null |
+----------+-------+---------+-------------+---------------+
È fondamentale distinguere tra funzioni di finestra e semplici GROUP BY funzioni. In un OVER() operazione, i risultati di LEAD E LAG vengono aggiunti a ogni riga, fornendo il contesto per le informazioni della riga precedente e successiva. Questo è distinto dalle funzioni in a GROUP BY clausola che riduce un gruppo di righe in un unico risultato di aggregazione.
Ora che abbiamo accesso ai dettagli sia della riga precedente che di quella successiva, possiamo facilitare i confronti tra righe. Questo confronto è fondamentale per identificare blocchi IP contigui, consentendoci di determinare quando fondere insieme i blocchi adiacenti.
Ogni riga può rientrare in uno dei quattro stati:
1) Rimuovi: il blocco è contiguo sia al blocco precedente che a quello successivo.
2) Inizio: il blocco è contiguo solo al blocco successivo.
3) Fine: il blocco è contiguo solo al blocco precedente.
4) Mantieni: il blocco non è contiguo né al blocco precedente né a quello successivo.
Aggiungiamo questo state colonna alla nostra tabella.
SELECT
*,
CASE
WHEN (end_ip = next_start_ip - 1)
AND (start_ip = prev_end_ip + 1) THEN 'remove'
WHEN (end_ip = next_start_ip - 1) THEN 'start'
WHEN (start_ip = prev_end_ip + 1) THEN 'end'
ELSE 'keep'
END AS state
FROM
view_1
Otteniamo quanto segue view_2 risultato:
+----------+-------+---------+-------------+---------------+-------+
| start_ip | end_ip| country | prev_end_ip | next_start_ip | state |
+----------+-------+---------+-------------+---------------+-------+
| 1 | 2 | ca | null | 3 | start |
| 3 | 4 | ca | 2 | 5 | remove|
| 5 | 8 | ca | 4 | 10 | end |
| 10 | 14 | ca | 8 | 19 | keep |
| 19 | 22 | ca | 14 | 23 | start |
| 23 | 29 | ca | 22 | null | end |
+----------+-------+---------+-------------+---------------+-------+
Possiamo procedere a rimuovere le righe comprese tra il blocco iniziale e quello finale, nello specifico quelle identificate con state remove.
SELECT
start_ip,
end_ip,
country,
state
FROM
view_2
WHERE
state IN ('start', 'end', 'keep')
Con il risultato di view_3:
+----------+-------+---------+-------+
| start_ip | end_ip| country | state |
+----------+-------+---------+-------+
| 1 | 2 | ca | start |
| 5 | 8 | ca | end |
| 10 | 14 | ca | keep |
| 19 | 22 | ca | start |
| 23 | 29 | ca | end |
+----------+-------+---------+-------+
Ci stiamo avvicinando al nostro obiettivo! Tutto quello che dobbiamo fare ora è unire i file start E end righe, che contengono il start_ip E end_ip dei mega blocchi che stiamo cercando di produrre. Per raggiungere questo obiettivo, utilizziamo ancora una volta una funzione finestra. Questa volta per andare a prendere il end_ip dal end riga.
SELECT
*,
LEAD(end_ip) OVER (
PARTITION BY country
ORDER BY start_ip) AS next_end_ip
FROM
view_3
Risultato view_4:
+----------+-------+---------+-------+-------------+
| start_ip | end_ip| country | state | next_end_ip |
+----------+-------+---------+-------+-------------+
| 1 | 2 | ca | start | 8 |
| 5 | 8 | ca | end | 14 |
| 10 | 14 | ca | keep | 22 |
| 19 | 22 | ca | start | 29 |
| 23 | 29 | ca | end | null |
+----------+-------+---------+-------+-------------+
Si noti che le righe con state start ora ho un start_ip e un next_end_iple informazioni necessarie per costruire un mega blocco.
Le righe con lo stato end non sono più necessari e possono essere rimossi.
Le righe con lo stato keep hanno già quello corretto end_ip.
Ora possiamo determinare il final_end valore dei megablocchi. Sono possibili due casi:
1) per a start riga, otteniamo il valore finale da next_end_ip.
2) per a keep riga, usiamo semplicemente l’originale end_ip valore.
SELECT
start_ip AS final_start,
CASE
WHEN (state = 'start') THEN next_end_ip
WHEN (state = 'keep') THEN end_ip
ELSE NULL
END AS final_end_ip
FROM
view_4
WHERE
state IN ('start', 'keep')
Raggiungiamo così il nostro obiettivo di fondere blocchi IPv4 contigui in mega blocchi.
+----------+-------+---------+-------+-------------+------------+----------+
| start_ip | end_ip| country | state | next_end_ip | final_start| final_end|
+----------+-------+---------+-------+-------------+------------+----------+
| 1 | 2 | ca | start | 8 | 1 | 8 |
| 10 | 14 | ca | keep | 22 | 10 | 14 |
| 19 | 22 | ca | start | 29 | 19 | 29 |
+----------+-------+---------+-------+-------------+------------+----------+
Mettendo insieme tutte le affermazioni precedenti, otteniamo un’affermazione finale:
SELECT
country,
final_start,
IF(state = 'start', next_end_ip, final_end) AS final_end
FROM (
SELECT
country,
start_ip AS final_start,
end_ip AS final_end,
LEAD(end_ip) OVER (
PARTITION BY country
ORDER BY start_ip) AS next_end_ip
FROM (
SELECT
start_ip,
end_ip,
country,
CASE
WHEN (end_ip = next_start_ip - 1)
AND (start_ip = prev_end_ip + 1) THEN 'remove'
WHEN (end_ip = next_start_ip - 1) THEN 'start'
WHEN (start_ip = prev_end_ip + 1) THEN 'end'
ELSE 'keep'
END AS state
FROM (
SELECT
*,
LAG(end_ip) OVER (
PARTITION BY country
ORDER BY start_ip) AS prev_end_ip,
LEAD(start_ip) OVER (
PARTITION BY country
ORDER BY start_ip) AS next_start_ip
FROM
geo_table
)
WHERE
state IN ('start', 'end', 'keep')
)
)
WHERE
state IN ('start', 'keep')
In conclusione, le funzioni della finestra analitica SQL offrono una struttura solida per l’analisi di dati complessi. Consentono agli utenti di eseguire aggregazioni mantenendo il contesto delle singole righe, facilitando attività come totali parziali, medie e calcoli percentili. Inoltre, queste funzioni svolgono un ruolo cruciale nella classificazione, nell’analisi delle serie temporali e nel rilevamento di anomalie e valori anomali all’interno dei set di dati. Queste funzioni sono risorse indispensabili nel toolkit dei professionisti dei dati.
Le funzioni della finestra analitica sono molto potenti. In questo articolo abbiamo solo scalfito la superficie; ad esempio, non abbiamo utilizzato a window_frame. Una cornice di finestra consente di perfezionare ulteriormente quali righe sono considerate nell’aggregazione. Le cornici delle finestre sono relative alla riga corrente e possono essere basate sul numero di righe o su intervalli di tempo, rendendo queste funzioni indispensabili per un’ampia gamma di analisi. Puoi saperne di più su queste funzionalità nella documentazione di Spark: Spark SQL: operazioni sulle finestre .
Fonte: towardsdatascience.com
