Importa librerie Python, manipola e genera tabelle SQL e altro ancora, il tutto senza uscire dal server SQL.
20 ore fa
Nell'ambito di questo progetto, affrontiamo la sfida di gestire 37.000 nomi di società provenienti da due origini distinte. La complessità risiede nella potenziale discrepanza tra il modo in cui le società identiche vengono elencate in queste fonti.
L'obiettivo di questo articolo è insegnarti a eseguire Python in modo nativo all'interno del server Microsoft SQL. Per utilizzare componenti aggiuntivi e librerie esterne, nonché eseguire ulteriori elaborazioni sulle tabelle risultanti con SQL.
Ecco la strategia che seguirò durante la creazione degli algoritmi:
- Blocco — Dividere i set di dati in blocchi o gruppi più piccoli in base ad attributi comuni per ridurre la complessità computazionale nel confronto dei record. Restringe lo spazio di ricerca e migliora l'efficienza nelle attività di ricerca di somiglianza.
- Pre-elaborazione — Pulizia e standardizzazione dei dati grezzi per prepararli all'analisi mediante attività come la conversione di lettere minuscole, la rimozione della punteggiatura e l'eliminazione delle parole stop. Questo passaggio migliora la qualità dei dati e riduce il rumore.
- Applicazione del modello di ricerca per somiglianza — Applicazione di modelli per calcolare la somiglianza o la distanza tra coppie di record basati su rappresentazioni tokenizzate. Ciò aiuta a identificare le coppie simili, utilizzando parametri come la somiglianza del coseno o la distanza di modifica, per attività come il collegamento dei record o la deduplicazione.
Blocco
I miei set di dati sono altamente sproporzionati: ho 1.361.373 entità in una tabella e solo 37.171 nomi di società nella seconda tabella. Se provo a trovare una corrispondenza sulla tabella non elaborata, l'algoritmo impiegherebbe molto tempo per farlo.
Per bloccare le tabelle, dobbiamo vedere quali caratteristiche comuni ci sono tra 2 set di dati. Nel mio caso le aziende sono tutte legate a progetti interni. Quindi farò quanto segue:
- Estrarre il nome distinto della società e il codice del progetto dalla tabella più piccola.
- Sfoglia i codici del progetto e prova a trovarli nella tabella più grande.
- Mappa tutti i fondi per quel progetto e toglili dal grande tavolo.
- Ripeti per il prossimo progetto!
In questo modo, ridurrò il set di dati di grandi dimensioni a ogni iterazione, assicurandomi anche che la mappatura sia rapida grazie a un set di dati più piccolo e filtrato a livello di progetto.
Ora filtrerò entrambe le tabelle in base al codice del progetto, in questo modo:
Con questo approccio, la nostra tabella piccola ha solo 406 righe per il progetto “ABC” da mappare, mentre la tabella grande ha 15.973 righe da mappare. Questa è una grande riduzione rispetto alla tabella grezza.
Struttura del programma
Questo progetto consisterà in funzioni Python e SQL su server SQL; ecco un breve schizzo di come funzionerà il programma per avere una comprensione più chiara di ogni passaggio:
Esecuzione del programma:
- La stampa del codice del progetto in un ciclo è la versione più semplice di questa funzione:
Diventa subito evidente che il cursore SQL utilizza troppe risorse. In breve, questo accade perché i cursori operano a livello di riga e attraversano ogni riga per eseguire un'operazione.
Maggiori informazioni sul motivo per cui i cursori in SQL sono inefficienti ed è meglio evitarli possono essere trovate qui: https://stackoverflow.com/questions/4568464/sql-server-temporary-tables-vs-cursors (risposta 2)
Per aumentare le prestazioni, utilizzerò tabelle temporanee e rimuoverò il cursore. Ecco la funzione risultante:
Ora sono necessari circa 3 secondi per progetto per selezionare il codice del progetto e i dati dalla grande tabella di mappatura, filtrati per quel progetto.
A scopo dimostrativo mi concentrerò solo su 2 progetti, tuttavia tornerò a eseguire la funzione su tutti i progetti quando la farò in produzione.
La funzione finale con cui lavoreremo è simile alla seguente:
Preparazione della tabella di mappatura
Il passo successivo è preparare i dati per le funzioni di pre-elaborazione e mappatura di Python, per questo avremo bisogno di 2 set di dati:
- I dati filtrati per codice di progetto dalla tabella di mappatura di grandi dimensioni
- I dati filtrati per codice progetto dalla tabella Piccole Imprese
Ecco come appare la funzione aggiornata con i dati di 2 tabelle selezionate:
Importante: le funzioni Python in SQL accettano solo 1 ingresso tabella. Assicurati di inserire i tuoi dati in un file tavolo unico e largo prima di inserirlo in una funzione Python in SQL.
Come risultato di questa funzione, otteniamo i progetti, i nomi delle società e le fonti per ciascun progetto.
Ora siamo pronti per Python!
Python in SQL Server, tramite sp_execute_external_scriptconsente di eseguire il codice Python direttamente in SQL Server.
Consente l'integrazione delle funzionalità di Python nei flussi di lavoro SQL con scambio di dati tra SQL e Python. Nell'esempio fornito, viene eseguito uno script Python, creando un DataFrame panda dai dati di input.
Il risultato viene restituito come un singolo output.
Quant'è fico!
Ci sono alcune cose importanti da notare sull'esecuzione di Python in SQL:
- Le stringhe sono definite da virgolette doppie (“), non da virgolette singole (‘). Assicurati di verificarlo soprattutto se stai utilizzando espressioni regex, per evitare di perdere tempo nel tracciamento degli errori
- È consentito solo 1 output, quindi il tuo codice Python risulterà in 1 tabella in output
- Puoi utilizzare le istruzioni print per il debug e vedere i risultati stampati nella scheda “Messaggi” all'interno del tuo server SQL. Così:
Librerie Python in SQL
In SQL Server diverse librerie sono preinstallate e sono facilmente accessibili. Per visualizzare l'elenco completo di queste librerie è possibile eseguire il seguente comando:
Ecco come apparirà l'output:
Tornando alla nostra tabella generata, ora possiamo abbinare i nomi delle società da diverse fonti utilizzando Python. La nostra procedura Python prenderà in considerazione la tabella lunga e genererà una tabella con le entità mappate. Dovrebbe mostrare la corrispondenza che ritiene più probabile dalla tabella di mappatura grande accanto a ciascun record della tabella aziendale piccola.
Per fare ciò, aggiungiamo prima una funzione Python alla nostra procedura SQL. Il primo passo è semplicemente inserire il set di dati in Python, lo farò con un set di dati di esempio e poi con i nostri dati, ecco il codice:
Questo sistema ci consente di inserire entrambe le nostre tabelle nella funzione Python come input, quindi stampa entrambe le tabelle come output.
Pre-elaborazione in Python
Per far corrispondere le nostre stringhe in modo efficace, dobbiamo condurre alcune pre-elaborazione in Python, questo include:
- Rimuovi accenti e altri caratteri speciali specifici della lingua
- Rimuovi gli spazi bianchi
- Rimuovi la punteggiatura
Il primo passaggio verrà eseguito con le regole di confronto in SQL, mentre gli altri 2 saranno presenti nella fase di preelaborazione della funzione Python.
Ecco come appare la nostra funzione con la preelaborazione:
Il risultato sono 3 colonne, una con il nome dell'azienda in maiuscolo minuscolo e senza lettere spaziali, la seconda colonna è la colonna del progetto e la terza colonna è la fonte.
Corrispondenza di stringhe in Python
Qui dobbiamo essere creativi poiché siamo piuttosto limitati nel numero di librerie che possiamo utilizzare. Pertanto, identifichiamo innanzitutto come vorremmo che fosse il nostro output.
Vogliamo abbinare i dati provenienti dalla sorgente 2 ai dati della sorgente 1. Pertanto, per ciascun valore della sorgente 2, dovremmo avere un insieme di valori corrispondenti dalla sorgente 1 con punteggi per rappresentare la vicinanza della corrispondenza.
Noi useremo librerie integrate Python in primo luogo, per evitare la necessità di importare librerie e quindi semplificare il lavoro.
La logica:
- Passa attraverso ogni progetto
- Crea una tabella con i fondi per fonte, dove la fonte 1 è la tabella grande con i dati di mappatura e 2 è il set di dati iniziale dell'azienda
- Seleziona i dati dal piccolo set di dati in un array
- Confronta ogni elemento nell'array risultante con ogni elemento nel grande frame di dati di mappatura
- Restituisci i punteggi per ciascuna entità
Il codice:
Ed ecco l'output finale:
In questa tabella abbiamo il nome di ogni azienda, il progetto a cui appartiene e la fonte, sia che provenga dalla tabella di mappatura grande o dalla tabella delle piccole aziende. Il punteggio a destra indica la metrica di somiglianza tra il nome dell'azienda dalla fonte 2 e la fonte 1. È importante notare che l'azienda4, che proviene dalla fonte 2, avrà sempre un punteggio di corrispondenza compreso tra 1 e 100%, poiché è essere confrontato con se stesso.
L'esecuzione di script Python all'interno di SQL Server tramite Machine Learning Services è una funzionalità potente che consente analisi nel database e attività di machine learning. Questa integrazione consente l'accesso diretto ai dati senza la necessità di spostarli, ottimizzando in modo significativo le prestazioni e la sicurezza per le operazioni ad uso intensivo di dati.
Tuttavia, ci sono limitazioni di cui essere consapevoli. L'ambiente supporta a ingresso singoloche potrebbe limitare la complessità delle attività che possono essere eseguite direttamente nel contesto SQL. Inoltre, solo a è disponibile un set limitato di librerie Pythonche potrebbero richiedere soluzioni alternative per determinati tipi di analisi dei dati o attività di apprendimento automatico non supportate dalle librerie predefinite. Inoltre, gli utenti devono esplorare le complessità dell'ambiente SQL Server, come la spaziatura complessa nelle query T-SQL che includono codice Python, che può essere fonte di errori e confusione.
Nonostante queste sfide, esistono numerose applicazioni in cui l'esecuzione di Python in SQL Server è vantaggiosa:
1. Pulizia e trasformazione dei dati – Python può essere utilizzato direttamente in SQL Server per eseguire attività complesse di preelaborazione dei dati, come la gestione dei dati mancanti o la normalizzazione dei valori, prima di ulteriori analisi o report.
2. Analisi predittiva – La distribuzione di modelli di machine learning Python direttamente all'interno di SQL Server consente previsioni in tempo reale, come l'abbandono dei clienti o le previsioni di vendita, utilizzando dati di database in tempo reale.
3. Analisi avanzata — Le capacità di Python possono essere sfruttate per eseguire sofisticate analisi statistiche e data mining direttamente sul database, aiutando nei processi decisionali senza la latenza del trasferimento dei dati.
4. Reporting e visualizzazione automatizzati — Gli script Python possono generare visualizzazioni di dati e report direttamente dai dati di SQL Server, consentendo aggiornamenti e dashboard automatizzati.
5. Operazionalizzare i modelli di machine learning – Integrando Python in SQL Server, i modelli possono essere aggiornati e gestiti direttamente all'interno dell'ambiente del database, semplificando il flusso di lavoro operativo.
In conclusione, sebbene l'esecuzione di Python in SQL Server presenti alcune sfide, apre anche numerose possibilità per migliorare e semplificare l'elaborazione, l'analisi e la modellazione predittiva dei dati direttamente all'interno dell'ambiente del database.
PS per vedere altri miei articoli, puoi seguirmi su LinkedIn qui: https://www.linkedin.com/in/sasha-korovkina-5b992019b/
Fonte: towardsdatascience.com
