Funzionalità segreta di SQL Server: esegui Python e componenti aggiuntivi in ​​modo nativo in SQL Server |  di Sasha Korovkina |  Maggio 2024

 | Intelligenza-Artificiale

Importa librerie Python, manipola e genera tabelle SQL e altro ancora, il tutto senza uscire dal server SQL.

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.

fotografato da Christin Hume SU Unsplash

Ecco la strategia che seguirò durante la creazione degli algoritmi:

  1. 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.
  2. 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.
  3. 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:

  1. Estrarre il nome distinto della società e il codice del progetto dalla tabella più piccola.
  2. Sfoglia i codici del progetto e prova a trovarli nella tabella più grande.
  3. Mappa tutti i fondi per quel progetto e toglili dal grande tavolo.
  4. 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.

Un semplice script per estrarre il codice progetto e il nome del fondo distinti.

Ora filtrerò entrambe le tabelle in base al codice del progetto, in questo modo:

Un esempio di codice di tabelle filtrate in base al codice del progetto.

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:

Struttura del programma. Immagine creata dall'autore.

Esecuzione del programma:

  • La stampa del codice del progetto in un ciclo è la versione più semplice di questa funzione:
Codice per stampare ricorsivamente i nomi delle aziende.

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:

Una funzione per selezionare tutti i valori dalla grande tabella di mappatura in base al codice del progetto.

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:

Ho commentato la definizione della funzione per rendere più semplice il debug del codice e impostare un limite sui primi 2 progetti

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:

  1. I dati filtrati per codice di progetto dalla tabella di mappatura di grandi dimensioni
  2. I dati filtrati per codice progetto dalla tabella Piccole Imprese

Ecco come appare la funzione aggiornata con i dati di 2 tabelle selezionate:

Selezione della tabella piccole aziende e della tabella mappatura grande dal database.

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.

Tabelle con fonti

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!

Un semplice esempio da https://learn.microsoft.com/en-us/sql/machine-learning/tutorials/quickstart-python-create-script?view=sql-server-ver16

Ci sono alcune cose importanti da notare sull'esecuzione di Python in SQL:

  1. 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
  2. È consentito solo 1 output, quindi il tuo codice Python risulterà in 1 tabella in output
  3. Puoi utilizzare le istruzioni print per il debug e vedere i risultati stampati nella scheda “Messaggi” all'interno del tuo server SQL. Così:
Immagine creata dall'autore.

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:

Codice per recuperare tutte le librerie Python disponibili in SQL

Ecco come apparirà l'output:

Puoi importare questi pacchetti proprio come faresti in un normale script Python (import…). Immagine creata dall'autore.

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.

Supponendo che l'Azienda 1.1 sia la corrispondenza più vicina all'Azienda 1, l'output dovrebbe assomigliare a quello sopra. Immagine creata dall'autore.

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:

Codice che inserisce i dati nel database: entrambe le tabelle sono presenti nella funzione Python.

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:

  1. Rimuovi accenti e altri caratteri speciali specifici della lingua
  2. Rimuovi gli spazi bianchi
  3. 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.

Struttura della tabella di output. Immagine creata dall'autore.

Noi useremo librerie integrate Python in primo luogo, per evitare la necessità di importare librerie e quindi semplificare il lavoro.

La logica:

  1. Passa attraverso ogni progetto
  2. 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
  3. Seleziona i dati dal piccolo set di dati in un array
  4. Confronta ogni elemento nell'array risultante con ogni elemento nel grande frame di dati di mappatura
  5. Restituisci i punteggi per ciascuna entità

Il codice:

Codice per mappare i dati dal set di dati di grandi dimensioni a un piccolo sottoinsieme di dati. Tieni presente di utilizzare i tuoi join e la tua struttura dati.

Ed ecco l'output finale:

Si tratta di dati inventati per dimostrare il risultato, tuttavia la struttura dovrebbe essere identica per il tuo set di dati. Immagine generata dall'autore.

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

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *