Deduplicazione dei dati durante l'elaborazione batch

Question

Deduplicazione dei dati durante l'elaborazione batch

#1 da (3 voti)
#2 da (1 voti)
#3 da (0 voti)

1

Ho costantemente lottato per risolvere i problemi di duplicazione dei dati in modo efficiente (memorizzazione di dati da qualsiasi fonte a RDBMS). La mia preoccupazione principale è accelerare gli inserimenti / l'elaborazione in batch.

Scenario: leggo dati da diverse fonti, principalmente in json formato e ho bisogno di elaborarli in modo (input: documenti nosql), dove memorizzo solo diverse righe ¹ una volta nel database. Il numero di insertibles è diverso, ma intorno a 1-10 milioni di righe. La velocità di lettura è veloce, posso leggere più velocemente che posso, su altrettanti discussioni come posso.

¹ Diverso di solito significa determinati campi nell'input il documento dovrebbe essere unico se è lo stesso insieme. Come se io devi salvare i prodotti e avere questi campi:

{
    "manufacturer": "Xy manufacturer",
    "manufacturerReference": "Manufacturer's product id, barcode = identifier",
    "name": "..",
    "price": ...

}

Tutte le combinazioni di manufacturer e manufacturerReference dovrebbe essere rappresentato solo una volta nel set di dati dopo l'elaborazione.

Il mio primo pensiero è stato rapidamente abbandonato, volevo solo creare qualcosa di unico indici nel database e gestiscono eccezioni chiave duplicate, ma la maggior parte del tempo semplicemente non funzionerà. Tutti i database hanno limiti di lunghezza le chiavi univoche (byte di SQL Server 900) e utilizzando varchar(255+) i campi lo riempiono rapidamente, anche riempire i registri con eccezioni sembra una soluzione sciatta.

Quindi, il problema sorge quando voglio salvare i dati. L'ingenuo l'approccio sarebbe:

Cerca di scoprire se l'insertable è un duplicato o meno con una query come select 1 from X where manufacturer=$1 and manufacturerReference=$2
Se non abbiamo una riga esistente, fai l'inserto, altrimenti ritorna.

Questa soluzione può essere molto lenta, quindi avremmo bisogno di indici su tutto il si suppone che siano campi univoci, che possono rendere le dimensioni del datastore aumentare drasticamente con un sacco di righe (anche, definendo indici su anche le grandi righe varchar non dovrebbero essere appropriate).

Questo si è evoluto in un'altra soluzione, in cui utilizzo un hashing appropriato algoritmo (md5 di solito) per unire efficacemente i campi ( fingerprint = md5(manufacturer + manufacturerReference) ) in un singolo campo, che può essere memorizzato in un luogo piccolo (per md5, binary(16) ). Dopo, posso definire indici univoci su fingerprint . Inserimento di file ancora rimangono gli stessi, o continuo a ingoiare gli errori del database, o io fai un controllo dell'esistenza in anticipo.

Il che mi porta a un altro problema, il blocco del database e i deadlock. Fare selezioni e inserimenti nella stessa transazione con più thread contemporaneamente sarà lento ed errato (come ho sperimentato). Le prestazioni che voglio quando elaboro milioni di righe non dovrebbero essere le ore (che attualmente sono).

Come si risolve questo problema (best practice?)? Se dovessi memorizzare i dati in un negozio NoSQL, il mio problema potrebbe essere facilmente risolto (i nuovi inserimenti per lo stesso fingerprint creano solo una nuova versione), ma i target sono MySQL , SQL Server e PostgreSQL di solito .

database unique-data

posta appl3r 22.11.2017 - 20:51

fonte

3 risposte

Leggi altre domande sui tag database unique-data

Come accoppiare gli algoritmi concatenati Quando avere un metodo generico per più attività quando alcune differiscono?

score 3 · Answer 1

Solitamente mi avvicino a ciò inserendo bulk in una tabella di staging e poi utilizzo un'istruzione sql di fusione ( link ) alla tabella di produzione. Nell'istruzione di fusione è possibile utilizzare tutti i tipi di logica per inserire o aggiornare le righe contemporaneamente.

Per la deduplica utilizzo il rownumber ( link ) con partizione su colonne specifiche che devono essere univoche (ovvero la chiave composta). Ignoro tutto ciò che riguarda il rownumber > 1. Se i dati hanno una colonna update-date, puoi avere un ordine nella funzione Rownumber in modo da prendere l'ultima versione nota delle proprietà che non fanno parte della chiave composta.

score 1 · Answer 2

Lo storage è ciò che alla fine ti darà la performance finale dell'inserto

Ad esempio, se si utilizza MySQL, è possibile eseguire InnoDB in modalità di conformità ACID completa, ma occorrerà un minuto per controllare e inserire mezzo milione di righe

La sperimentazione aiuta

Aiuta anche a capire cosa stai chiedendo al server di fare

verifica i duplicati? la lettura è veloce, quindi non è un grosso problema
calcolo di una chiave hash? un po 'più di CPU, ma non così male
cache dell'indice da aggiornare? bene un po 'meno righe al minuto
svuota istantaneamente le operazioni sul disco? ancora meno righe al minuto
vuoi inserire i prodotti uno per uno e mostrare una barra di avanzamento molto accurata? ogni minuto

Altre modalità o un diverso motore di archiviazione possono produrre un conteggio dei record più veloce, ma con meno affidabilità che significa REPAIR TABLE ogni tanto, se succede qualcosa di imbarazzante

Scambiare un po 'di SELECT di velocità per più INSERT velocità è anche un'opzione

conserva i duplicati
inserisci un ID su tutto
SELECT solo il più recente id s quando vengono trovati duplicati

E poi puoi sfoltire i duplicati di volta in volta, o quando hai finito INSERT ing

score 0 · Answer 3

Quello che mi ricorda molti problemi simili che ho affrontato nei miei giorni ETL. E sì, gli RDBMS non sono realmente progettati per questo processo: sono necessari altri strumenti.

Suppongo che tu stia eseguendo l'elaborazione in batch - corretto? Stai usando uno script personalizzato per convertire i documenti di Json in istruzioni di inserimento del database? Se è così, quello che potresti fare è aggiungere un passo di pre-elaborazione, che è qualcosa che ho dovuto fare molte, molte volte.

Ci sono vari modi per farlo, ma qui ci sono un paio di opzioni:

A) Processo in due fasi, utilizzando il database per deduplicare:

Leggi ogni documento, convertilo in un formato delimitato che corrisponde ai campi necessari per creare le istruzioni di inserimento.
Mentre scrivi i record, includi l'impronta digitale come un nuovo campo. Aggiungi il campo delle impronte digitali alla tabella di destinazione (o crea anche una nuova tabella per questo scopo).
Crea un indice univoco in questo campo.
Ora, leggi il file delimitato, converti in istruzioni del database e fai gli inserti. Mentre lo fai, il tuo database rifiuterà qualsiasi duplicato a causa del nuovo indice. Lo svantaggio qui è che devi fare ogni inserto come una transazione discreta (in caso contrario, un duplicato rovinerà il tuo batch).

B) Processo di script puro. Personalmente non mi piace fare affidamento sul database per fare questo lavoro. Scriverò il file delimitato come appena spiegato, ma poi scriverò un altro script per passare attraverso il file delimitato ed estirpare qualsiasi duplicato basato sul tuo hash md5 o sul confronto dei campi, prima che tocchi il database. Una volta terminato, avrai un file "pulito" che potrai caricare in batch nel tuo database, risparmiando ancora più tempo.

La morale della trama è: non cercare di far funzionare il database, ma in realtà non va bene.