SQL, cosa può essere più semplice? Ognuno di noi può scrivere una semplice query — digitando select, elencando le colonne necessarie, poi da, il nome della tabella, qualche condizione in where e voilà — i dati utili sono nella nostra tasca, quasi indipendentemente dal tipo di DBMS che stiamo utilizzando nel backend (o forse non è nemmeno un DBMS) ). Di conseguenza, il lavoro con praticamente qualsiasi fonte di dati (relazionali e non) può essere considerato dal punto di vista di un normale codice (con tutte le conseguenze — controllo delle versioni, revisione del codice, analisi statica, test automatici e tutto il resto). E questo riguarda non solo i dati stessi, gli schemi e le migrazioni, ma in generale l'intera vita del database. In questo articolo parleremo delle attività quotidiane e dei problemi che comporta lavorare con vari DB sotto il focus "database as code".
E iniziamo subito con . I primi scontri del tipo "SQL vs ORM" furono notati già nella .
Il mapping oggetto-relazionale
I sostenitori dell'ORM tradizionalmente apprezzano la velocità e la semplicità di sviluppo, l'indipendenza dal DBMS e la pulizia del codice. Per molti di noi, il codice per lavorare con il database (e spesso anche il database stesso)
di solito appare più o meno così…
@Entity
@Table(name = "stock", catalog = "maindb", uniqueConstraints = {
@UniqueConstraint(columnNames = "STOCK_NAME"),
@UniqueConstraint(columnNames = "STOCK_CODE") })
public class Stock implements java.io.Serializable {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "STOCK_ID", unique = true, nullable = false)
public Integer getStockId() {
return this.stockId;
}
...Il modello è ricco di annotazioni intelligenti, mentre dietro le quinte il valoroso ORM genera ed esegue tonnellate di codice SQL. A proposito, gli sviluppatori cercano in tutti i modi di isolarsi dal loro database attraverso chilometri di astrazioni, il che indica una certa .
Dall'altra parte della barricata, i sostenitori del "SQL fatto a mano" sottolineano la possibilità di spremere ogni goccia dal proprio DBMS senza strati e astrazioni aggiuntivi. Questo porta alla nascita di progetti "data-centric", dove a curare il database sono persone appositamente formate (chiamate anche "database specialisti", "database engineers", "database developers" ecc.), mentre agli sviluppatori rimane solo il compito di "tirare" le viste e le procedure già pronte, senza entrare nei dettagli.
E se prendessimo il meglio di entrambi i mondi? Come è stato fatto in questo straordinario strumento con un nome che dà fiducia, . Riporto un paio di frasi dalla concettualizzazione generale in una mia libera traduzione, e per una panoramica più dettagliata, si può approfondire .
Clojure è un linguaggio fantastico per creare DSL, ma SQL è già di per sé un fantastico DSL, e non abbiamo bisogno di un altro. Le S-espressioni sono meravigliose, ma qui non aggiungono nulla di nuovo. In definitiva, otteniamo parentesi per il semplice fatto di avere parentesi. Non sei d'accordo? Aspetta solo il momento in cui l'astrazione sul DB inizierà a perdere colpi, e tu inizierai a combattere con la funzione (raw-sql)
E ora? Lasciamo SQL com'è, un semplice SQL: un file per una query:
-- name: users-by-country
select *
from users
where country_code = :country_code… e poi leggi questo file, trasformandolo in una normale funzione Clojure:
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; È stata creata una funzione con il nome `users-by-country`.
;;; Utilizziamola:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)Adottando il principio "SQL separato, Clojure separato", ottieni:
- Nessuna sorpresa sintattica. Il tuo database (come qualsiasi altro) non è conforme al 100% allo standard SQL — ma per Yesql non è un problema. Non perderai mai tempo a cercare funzioni con una sintassi equivalente a SQL. Non dovrai mai tornare alla funzione (raw-sql "some (‘funky’ :: SYNTAX)")).
- Miglior supporto dell'editor. Il tuo editor già ha un ottimo supporto per SQL. Mantenendo SQL come SQL, puoi semplicemente utilizzarlo.
- Compatibilità con il team. I tuoi DBA possono leggere e scrivere SQL che usi nel tuo progetto Clojure.
- Impostazione delle prestazioni più semplice. Devi costruire un piano per una query problematica? Non è un problema quando la tua query è un semplice SQL.
- Riutilizzo delle query. Puoi trascinare questi stessi file SQL in altri progetti, perché è solo un vecchio buon SQL — basta condividerlo.
A mio avviso, l'idea è molto interessante e allo stesso tempo molto semplice, il che ha portato il progetto ad avere molti in diversi linguaggi. E noi cercheremo di applicare una filosofia simile di separazione del codice SQL da tutto il resto, molto oltre l'ORM.
IDE & gestori di DB
Iniziamo con un compito quotidiano semplice. Spesso dobbiamo cercare oggetti nel database, ad esempio, trovare una tabella in uno schema e studiarne la struttura (quali colonne, chiavi, indici, vincoli e altro vengono utilizzati). E da qualsiasi IDE grafica o anche da un DB manager modesto, ci aspettiamo in primis queste capacità. Perché sia veloce e non dobbiamo attendere mezz'ora affinché appaia la finestra con le informazioni necessarie (soprattutto con una connessione lenta a un database remoto), e nel contempo, le informazioni ottenute devono essere fresche e pertinenti, non un vecchio materiale memorizzato in cache. E più complesso e grande è il database e maggiore è il numero di essi, più difficile diventa fare ciò.
Ma di solito metto il mouse da parte e scrivo semplicemente il codice. Supponiamo che sia necessario scoprire quali tabelle (e con quali proprietà) sono contenute nello schema "HR". Nella maggior parte dei DBMS, è possibile ottenere il risultato desiderato con una semplice query da information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Da un database all'altro, il contenuto di tali tabelle di riferimento varia a seconda delle capacità di ogni DBMS. E, ad esempio, per MySQL, dallo stesso catalogo è possibile ottenere parametri specifici per questo DBMS della tabella:
select table_name
, storage_engine -- Motore utilizzato ("MyISAM", "InnoDB" ecc.)
, row_format -- Formato della riga ("Fixed", "Dynamic" ecc.)
, ...
from information_schema.tables
where schema = 'HR'Oracle non supporta information_schema, ma ha invece , e non ci sono grandi problemi:
select table_name
, pct_free -- Percentuale minima di spazio libero nel blocco dati (%)
, pct_used -- Percentuale minima di spazio utilizzato nel blocco dati (%)
, last_analyzed -- Data dell'ultimo raccolto di statistiche
, ...
from all_tables
where owner = 'HR'Non è da meno ClickHouse:
select name
, engine -- Motore utilizzato ("MergeTree", "Dictionary" ecc.)
, ...
from system.tables
where database = 'HR'Qualcosa di simile può essere fatto anche in Cassandra (dove ci sono columnfamilies invece di tabelle e keyspace al posto degli schemi):
select columnfamily_name
, compaction_strategy_class -- Strategia di compattazione
, gc_grace_seconds -- Tempo di vita dei dati spazzatura
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Per la maggior parte degli altri DB, è possibile pensare a query simili (anche in Mongo c'è , che contiene informazioni su tutte le collezioni nel sistema).
Naturalmente, in questo modo è possibile ottenere informazioni non solo sulle tabelle, ma su qualsiasi oggetto. Periodicamente, persone gentilissime condividono questo tipo di codice per vari DB, come ad esempio nella serie di articoli di Habr "Funzioni per la documentazione dei database PostgreSQL" (, , ). Naturalmente, tenere a mente tutta questa montagna di query e digitarle continuamente è un "piacere" piuttosto relativo, quindi nel mio IDE/editor preferito ho un insieme di snippet preparati in anticipo per le query usate frequentemente, e resta solo da inserire i nomi degli oggetti nel modello.
Di conseguenza, questo modo di navigare e cercare oggetti è molto più flessibile, fa risparmiare molto tempo e consente di ottenere proprio le informazioni necessarie e nel formato richiesto (come spiegato nel post ).
Operazioni con gli oggetti
Dopo aver trovato e studiato gli oggetti necessari, è il momento di fare qualcosa di utile con essi. Naturalmente, senza staccare le dita dalla tastiera.
Non è un segreto che la semplice eliminazione di una tabella appare praticamente identica in quasi tutti i DB:
drop table hr.personsMa la creazione di una tabella è già più interessante. Quasi tutti i DBMS (compresi molti NoSQL) sanno in un modo o nell'altro come usare "create table", e la parte principale di questo comando sarà poco diversa (nome, elenco delle colonne, tipi di dati), ma gli altri dettagli possono differire ampiamente e dipendono dalla struttura interna e dalle funzionalità specifiche di ciascun DBMS. Il mio esempio preferito è che nella documentazione Oracle solo le "strutture" BNF per la sintassi di "create table" . Altri DBMS hanno funzionalità più modeste, ma ognuno di essi ha anche molte caratteristiche interessanti e uniche per la creazione di tabelle (, , , ). È improbabile che qualche "wizard" grafico di un altro IDE (soprattutto se universale) possa coprire completamente tutte queste capacità, e se lo fa, sarà uno spettacolo non per i deboli di cuore. D'altra parte, un operatore ben scritto create table permetterà di sfruttare facilmente tutte queste funzionalità, rendendo l'archiviazione e l'accesso ai vostri dati affidabili, ottimali e il più confortevoli possibile.
In molte SGBD esistono anche tipi di oggetti specifici, che mancano in altre SGBD. Possiamo eseguire operazioni non solo sugli oggetti del database, ma anche sullo stesso SGBD, ad esempio "terminare" un processo, liberare una certa area di memoria, attivare il tracing, passare alla modalità "sola lettura" e molto altro.
Ora facciamo un po' di disegno
Una delle attività più comuni è costruire un diagramma con gli oggetti del database, vedere in un'immagine bella gli oggetti e le relazioni tra di essi. Questo può essere fatto praticamente da qualsiasi IDE grafica, da utilità "da riga di comando" specifiche, strumenti grafici specializzati e modellatori. Questi strumenti fanno un po' di disegno "come sanno", e si può influenzare questo processo solo usando alcuni parametri nel file di configurazione o spuntando opzioni nell'interfaccia.
Tuttavia, questo problema può essere risolto in modo molto più semplice, flessibile ed elegante, e naturalmente con l'uso di codice. Per costruire diagrammi di qualsiasi complessità, abbiamo immediatamente diversi linguaggi di markup specializzati (DOT, GraphML, ecc.), e a essi si affiancano numerose applicazioni (GraphViz, PlantUML, Mermaid), che possono leggere tali istruzioni e visualizzarle in vari formati. Già sappiamo come ottenere le informazioni sugli oggetti e sulle relazioni tra di essi.
Facciamo un piccolo esempio di come potrebbe apparire, utilizzando PlantUML e (a sinistra la query SQL che genererà l'istruzione necessaria per PlantUML, a destra il risultato):
select '@startuml'||chr(10)||'hide methods'||chr(10)||'hide stereotypes' union all
select distinct ccu.table_name || ' --|>' ||
tc.table_name as val
from table_constraints as tc
join key_column_usage as kcu
on tc.constraint_name = kcu.constraint_name
join constraint_column_usage as ccu
on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and tc.table_name ~ '.*' union all
select '@enduml'E se si presta un po' di attenzione, sulla base si può ottenere qualcosa di molto simile a un vero diagramma ER:
La query SQL è un po' più complessa
-- Intestazione
select '@startuml
!define Table(nome,desc) class nome as "desc" << (T,#FFAAAA) >>
!define primary_key(x) <b>x</b>
!define unique(x) <color:green>x</color>
!define not_null(x) <u>x</u>
hide methods
hide stereotypes'
union all
-- Tabelle
select format('Table(%s, "%s n informazioni su %s") {'||chr(10), table_name, table_name, table_name) ||
(select string_agg(column_name || ' ' || upper(udt_name), chr(10))
from information_schema.columns
where table_schema = 'public'
and table_name = t.table_name) || chr(10) || '}'
from information_schema.tables t
where table_schema = 'public'
union all
-- Relazioni tra le tabelle
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s può avere molti %s"', ccu.table_name, tc.table_name)
from information_schema.table_constraints as tc
join information_schema.key_column_usage as kcu on tc.constraint_name = kcu.constraint_name
join information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
where tc.constraint_type = 'FOREIGN KEY'
and ccu.constraint_schema = 'public'
and tc.table_name ~ '.*'
union all
-- Feed
select '@enduml' 
Se si guarda attentamente, si vedrà che molti strumenti di visualizzazione utilizzano query simili. Tuttavia, queste query sono solitamente profondamente , per non parlare di qualsiasi modifica.
Metriche e monitoraggio
Passiamo a un tema tradizionalmente complesso: il monitoraggio delle prestazioni del database. Vorrei ricordare una piccola storia vera, raccontata da "un mio amico". In un progetto, viveva un potente DBA, e pochi sviluppatori lo conoscevano di persona o lo avevano mai visto di persona (nonostante si dicesse che lavorasse, a quanto pare, in un edificio vicino). Al momento "X", quando il sistema di produzione di un grande rivenditore iniziava a "star male" per l'ennesima volta, lui silenziosamente inviava screenshot dei grafici dall'Enterprise Manager di Oracle, evidenziando con un evidenziatore rosso i punti critici per "chiarezza" (cosa che, per usare un eufemismo, aiutava poco). E così bisognava trovare una soluzione basandosi su questa "fotogallery". Nessuno aveva accesso all'Enterprise Manager, prezioso (in entrambi i sensi della parola), poiché il sistema era complesso e costoso, e c'era il timore che "gli sviluppatori potessero combinare guai e rompere tutto". Così gli sviluppatori individuavano, attraverso un approccio "empirico", la causa dei rallentamenti e lanciavano una patch. Se non arrivava nuovamente una lettera minacciosa dal DBA nel frattempo, tutti tiravano un sospiro di sollievo e tornavano ai loro compiti attuali (fino alla prossima Letterina).
Ma il processo di monitoraggio può apparire più divertente e amichevole, e soprattutto — accessibile e trasparente per tutti. Anche solo la sua parte base, come integrazione ai sistemi di monitoraggio principali (che sono senza dubbio utili e in molti casi indispensabili). Qualsiasi DBMS è pronto a condividere liberamente e gratuitamente informazioni sul proprio stato attuale e sulle prestazioni. Nella stessa "terribile" Oracle DB, è possibile ottenere praticamente qualsiasi informazione sulle prestazioni da viste di sistema, dai processi e sessioni fino allo stato della cache dei buffer (ad esempio, , sezione "Monitoraggio"). In Postgresql ci sono anche molte viste di sistema per , in particolare quelle indispensabili nella vita quotidiana di qualsiasi DBA, come , , . In MySQL è stata persino creata una specifica schema . In Mongo, il aggregando dati sulle prestazioni in una collezione di sistema .
Pertanto, armati di un qualsiasi raccoglitore di metriche (Telegraf, Metricbeat, Collectd), che può eseguire query SQL personalizzate, di un deposito per queste metriche (InfluxDB, Elasticsearch, Timescaledb) e di un visualizzatore (Grafana, Kibana), è possibile ottenere un sistema di monitoraggio piuttosto semplice e flessibile, che sarà strettamente integrato con altre metriche di sistema (ottenute, ad esempio, dal server delle applicazioni, dal sistema operativo, ecc.). Come, ad esempio, è stato fatto in pgwatch2, dove viene utilizzata la combinazione InfluxDB + Grafana e un insieme di query alle viste di sistema, a cui è possibile anche .
Totale
E questo è solo un elenco approssimativo di ciò che si può fare con il nostro database tramite codice SQL standard. Sono sicuro che si possono trovare molte altre applicazioni, scrivetelo nei commenti. E parleremo di come (e, soprattutto, perché) automatizzare tutto questo e includerlo nel proprio pipeline CI/CD la prossima volta.
Fonte: habr.com
