Introduzione a SQL Scripting in Databricks, Parte 2

Nella seconda parte della serie di post sul blog sull'annuncio di SQL Scripting, esamineremo l'attività amministrativa di cui abbiamo discusso in parte uno: come applicare una regola case-insensitive a ogni colonna STRING in una tabella. Analizzeremo quell'esempio passo dopo passo, spiegheremo le funzionalità utilizzate e lo estenderemo oltre una singola tabella per coprire un intero schema.

Puoi anche seguire in questo notebook. 

Modifica della collation di tutti i campi di testo in tutte le tabelle di uno schema

Databricks supporta un'ampia gamma di collation consape delle lingue, case-insensitive e accent-insensitive. È facile utilizzare questa funzionalità per nuove tabelle e colonne. Ma cosa succede se hai un sistema esistente che utilizza upper() o lower() nei predicati ovunque e vuoi ottenere i miglioramenti delle prestazioni associati a una collation nativa case-insensitive semplificando le tue query? Ciò richiederà un po' di programmazione; ora puoi farlo tutto in SQL. 

Utilizziamo il seguente schema di test: 

L'ordinamento si basa sui codepoint ASCII, dove tutte le lettere maiuscole precedono tutte le lettere minuscole. Puoi risolvere questo problema senza aggiungere upper() o lower()?

Istruzioni SQL dinamiche e impostazione delle variabili

Il nostro primo passo è dire alla tabella di cambiare la sua collation predefinita per le colonne aggiunte successivamente. Puoi popolare le tue variabili locali con segnaposto di parametri, che il notebook rileverà automaticamente e aggiungerà widget. Puoi anche usare EXECUTE IMMEDIATE per eseguire un'istruzione ALTER TABLE composta dinamicamente.

Ogni script SQL è costituito da un'istruzione composta BEGIN .. END. Le variabili locali vengono definite prima all'interno di un'istruzione composta, seguita dalla logica.

Questo è solo un insieme di istruzioni lineari. Finora, potresti scrivere tutto questo con le variabili di sessione SQL senza l'istruzione composta. Inoltre, non hai ottenuto molto. Dopotutto, volevi cambiare la collation per le colonne esistenti. Per fare ciò, devi:

• Scoprire tutte le colonne stringa esistenti nella tabella
• Cambiare la collation per ogni colonna

In breve, devi iterare sulla tabella INFORMATION_SCHEMA.COLUMNS.

Loop

SQL Scripting offre quattro modi di iterare e modi per controllare le iterazioni dei loop.

1. LOOP … END LOOP;
   Questo è un loop "infinito".
   Questo loop continuerà fino a quando un'eccezione o un comando esplicito ITERATE o LEAVE non interrompe il loop.
   Discuteremo la gestione delle eccezioni in seguito e faremo riferimento alla documentazione di ITERATE e LEAVE che spiega come controllare i loop.
2. WHILE predicate DO … END WHILE;
   Questo loop verrà eseguito e ri-eseguito finché l'espressione del predicato non risulterà vera o il loop verrà interrotto da un'eccezione, ITERATE o LEAVE.
3. REPEAT … UNTIL predicate END REPEAT;
   A differenza di WHILE, questo loop viene eseguito almeno una volta e ri-eseguito fino a quando l'espressione del predicato non risulta falsa o il loop viene interrotto da un'eccezione, LEAVE o comando ITERATE.
4. FOR query DO …. END FOR;
   Questo loop viene eseguito una volta per ogni riga restituita dalla query, a meno che non venga interrotto in anticipo con un'eccezione, LEAVE o istruzione ITERATE.

Ora, applica il loop FOR al nostro script di collation. La query ottiene i nomi delle colonne di tutte le colonne stringa della tabella. Il corpo del loop modifica la collation di ciascuna colonna a turno:

Verifichiamo che la tabella sia stata aggiornata correttamente:

Fin qui, tutto bene. Il nostro codice è funzionalmente completo, ma dovresti dire a Delta di analizzare le colonne che hai modificato per sfruttare il file skipping. Non vuoi farlo per ogni colonna. Raccoglile tutte insieme e fai il lavoro solo se c'è stata effettivamente una colonna di tipo stringa per cui la collation è stata alterata. Decisioni, decisioni... 

Logica condizionale

SQL Scripting offre tre modi per eseguire istruzioni SQL in modo condizionale.

1. Logica If-then-else. La sintassi è semplice:
   IF predicate THEN … ELSEIF predicate THEN … ELSE …. END IF;
   Naturalmente, puoi avere qualsiasi numero di blocchi ELSEIF opzionali e l'eventuale blocco ELSE è anch'esso opzionale.
2. Una semplice istruzione CASE
   Questa istruzione è la versione SQL Scripting dell'espressione case semplice.
   CASE expression WHEN option THEN … ELSE … END CASE;
   Una singola esecuzione di un'espressione viene confrontata con diverse opzioni e la prima corrispondenza determina quale insieme di istruzioni SQL deve essere eseguito. Se nessuna corrisponde, verrà eseguito il blocco ELSE opzionale.
3. Una CASE cercata
   Questa istruzione è la versione SQL Scripting dell'espressione case cercata.
   CASE WHEN predicate THEN …. ELSE … END CASE;
   Il blocco THEN viene eseguito per la prima delle condizioni che restituisce true. Se nessuna corrisponde, viene eseguito il blocco ELSE opzionale. 

Per il nostro script di collation, un semplice IF THEN END IF sarà sufficiente. Devi anche raccogliere l'insieme di colonne a cui applicare ANALYZE e utilizzare alcune funzioni di ordine superiore per produrre l'elenco delle colonne:

Annidamento

Quello che hai scritto finora funziona per le singole tabelle. E se volessi operare su tutte le tabelle di uno schema? SQL Scripting è completamente componibile. Puoi annidare istruzioni composte, istruzioni condizionali e cicli all'interno di altre istruzioni SQL Scripting.

Quindi, quello che farai qui è duplice:

1. Aggiungere un ciclo FOR esterno per trovare tutte le tabelle all'interno di uno schema utilizzando INFORMATION_SCHEMA.TABLES. Come parte di questo, devi sostituire i riferimenti alla variabile del nome della tabella con i riferimenti ai risultati della query del ciclo FOR. 
2. Aggiungere un blocco composto annidato per spostare la variabile dell'elenco delle colonne all'interno del ciclo FOR esterno. Non puoi dichiarare una variabile direttamente nel corpo del ciclo FOR; non aggiunge un nuovo scope. Questa è principalmente una decisione legata allo stile di codifica, ma avrai una ragione più seria per un nuovo scope.

Questo errore ha senso. Hai diversi modi per procedere:

1. Filtra i tipi di tabella non supportati, come le viste, nella query information_schema. Il problema è che ci sono numerosi tipi di tabella e ne vengono aggiunti di nuovi occasionalmente.
2. Gestisci le viste. È un’ottima idea. Chiamiamola la tua assegnazione per casa.
3. Tollera la condizione di errore

Gestione delle eccezioni

Una funzionalità chiave di SQL Scripting è la capacità di intercettare e gestire le eccezioni. I gestori di condizioni sono definiti nella sezione di dichiarazione di un'istruzione composta e si applicano a qualsiasi istruzione all'interno di tale istruzione composta, incluse le istruzioni annidate. Puoi gestire condizioni di errore specifiche per nome, specifici SQLSTATE che gestiscono diverse condizioni di errore o tutte le condizioni di errore. All'interno del corpo del gestore di condizioni, puoi utilizzare l'istruzione GET DIAGNOSTICS per recuperare informazioni sull'eccezione in gestione ed eseguire qualsiasi script SQL ritieni appropriato, come registrare l'errore in un log o eseguire una logica alternativa a quella fallita. Puoi quindi SIGNAL una nuova condizione di errore, RESIGNAL la condizione originale, o semplicemente uscire dall'istruzione composta in cui è definito il gestore e continuare con l'istruzione successiva.

Nel nostro script, vogliamo saltare qualsiasi istruzione per la quale l'istruzione ALTER TABLE DEFAULT COLLATION non è stata applicata e registrare il nome dell'oggetto.

Sopra, hai sviluppato uno script amministrativo interamente in SQL. Puoi anche scrivere script ELT e trasformarli in Job. SQL Scripting è uno strumento veramente potente che dovresti sfruttare.  

Cosa fare dopo

Sia che tu sia un utente Databricks esistente o che provenga da un altro prodotto, SQL Scripting è una funzionalità che dovresti utilizzare. SQL Scripting segue lo standard ANSI ed è pienamente compatibile con OSS Apache Spark™. SQL Scripting è descritto in dettaglio nella Documentazione di SQL Scripting | Databricks. 

Puoi anche usare questo notebook per vederlo tu stesso. 

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale