SQL, was könnte einfacher sein? Jeder von uns kann eine einfache Anfrage schreiben – wir tippen wählen, listen Sie dann die erforderlichen Spalten auf für, Tabellenname, einige Bedingungen in woher und das ist alles – nützliche Daten stecken in unserer Tasche und (fast) unabhängig davon, welches DBMS sich gerade unter der Haube befindet (oder vielleicht). ). Daher kann die Arbeit mit fast jeder Datenquelle (relational und nicht relational) aus der Sicht von gewöhnlichem Code betrachtet werden (mit allem, was dazu gehört – Versionskontrolle, Codeüberprüfung, statische Analyse, Autotests und mehr). Und das gilt nicht nur für die Daten selbst, Schemata und Migrationen, sondern generell für die gesamte Lebensdauer des Speichers. In diesem Artikel werden wir über alltägliche Aufgaben und Probleme bei der Arbeit mit verschiedenen Datenbanken unter dem Gesichtspunkt „Datenbank als Code“ sprechen.
Und fangen wir gleich von vorne an . Die ersten Kämpfe vom Typ „SQL vs. ORM“ gab es schon damals .
Objektrelationale Zuordnung
ORM-Anhänger legen traditionell Wert auf Geschwindigkeit und einfache Entwicklung, Unabhängigkeit vom DBMS und sauberen Code. Für viele von uns ist der Code für die Arbeit mit der Datenbank (und oft auch die Datenbank selbst)
Normalerweise sieht es so aus...
@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;
}
...Das Modell ist mit cleveren Anmerkungen versehen, und irgendwo hinter den Kulissen generiert und führt ein tapferes ORM Unmengen von SQL-Code aus. Übrigens versuchen Entwickler ihr Bestes, sich mit kilometerlangen Abstraktionen von ihrer Datenbank zu isolieren, was auf einiges hindeutet .
Auf der anderen Seite der Barrikaden bemerken Anhänger von reinem „handgemachtem“ SQL die Möglichkeit, den gesamten Saft aus ihrem DBMS ohne zusätzliche Ebenen und Abstraktionen herauszupressen. Als Ergebnis entstehen „datenzentrierte“ Projekte, bei denen speziell geschulte Personen an der Datenbank beteiligt sind (sie sind auch „Basicisten“, sie sind auch „Basicisten“, sie sind auch „Basdener“ usw.) und die Entwickler Sie müssen lediglich die vorgefertigten Ansichten und gespeicherten Prozeduren „abrufen“, ohne auf Details einzugehen.
Was wäre, wenn wir das Beste aus beiden Welten hätten? Wie dies in einem wunderbaren Werkzeug mit einem lebensbejahenden Namen geschieht . Ich werde in meiner kostenlosen Übersetzung ein paar Zeilen aus dem allgemeinen Konzept wiedergeben, damit Sie sich detaillierter damit vertraut machen können .
Clojure ist eine coole Sprache zum Erstellen von DSLs, aber SQL selbst ist eine coole DSL, und wir brauchen keine andere. S-Ausdrücke sind großartig, aber sie fügen hier nichts Neues hinzu. Als Ergebnis erhalten wir Klammern um der Klammern willen. Stimme nicht zu? Warten Sie dann den Moment ab, in dem die Abstraktion über die Datenbank zu lecken beginnt und Sie anfangen, mit der Funktion zu kämpfen (raw-sql)
Und was machen? Belassen wir SQL als normales SQL – eine Datei pro Anfrage:
-- name: users-by-country
select *
from users
where country_code = :country_code... und dann diese Datei lesen und sie in eine reguläre Clojure-Funktion umwandeln:
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; A function with the name `users-by-country` has been created.
;;; Let's use it:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)Durch die Einhaltung des Prinzips „SQL allein, Clojure allein“ erhalten Sie:
- Keine syntaktischen Überraschungen. Ihre Datenbank ist (wie jede andere auch) nicht zu 100 % mit dem SQL-Standard kompatibel – für Yesql spielt das jedoch keine Rolle. Sie werden nie Zeit damit verschwenden, nach Funktionen mit SQL-äquivalenter Syntax zu suchen. Sie müssen nie zu einer Funktion zurückkehren (raw-sql "some('funky'::SYNTAX)")).
- Beste Editor-Unterstützung. Ihr Editor verfügt bereits über eine hervorragende SQL-Unterstützung. Indem Sie SQL als SQL speichern, können Sie es einfach verwenden.
- Teamkompatibilität. Ihre DBAs können das SQL lesen und schreiben, das Sie in Ihrem Clojure-Projekt verwenden.
- Einfachere Leistungsoptimierung. Müssen Sie einen Plan für eine problematische Abfrage erstellen? Dies ist kein Problem, wenn es sich bei Ihrer Abfrage um reguläres SQL handelt.
- Abfragen wiederverwenden. Ziehen Sie dieselben SQL-Dateien per Drag-and-Drop in andere Projekte, da es sich lediglich um altes SQL handelt – geben Sie es einfach weiter.
Meiner Meinung nach ist die Idee sehr cool und gleichzeitig sehr einfach, wodurch das Projekt viele gewonnen hat in verschiedenen Sprachen. Als nächstes werden wir versuchen, eine ähnliche Philosophie anzuwenden, bei der SQL-Code von allem anderen weit über das ORM hinaus getrennt wird.
IDE- und DB-Manager
Beginnen wir mit einer einfachen Alltagsaufgabe. Oft müssen wir nach einigen Objekten in der Datenbank suchen, zum Beispiel eine Tabelle im Schema finden und ihre Struktur untersuchen (welche Spalten, Schlüssel, Indizes, Einschränkungen usw. verwendet werden). Und von jeder grafischen IDE oder einem kleinen DB-Manager erwarten wir zunächst einmal genau diese Fähigkeiten. Damit es schnell geht und Sie nicht eine halbe Stunde warten müssen, bis ein Fenster mit den erforderlichen Informationen angezeigt wird (insbesondere bei einer langsamen Verbindung zu einer Remote-Datenbank) und gleichzeitig die empfangenen Informationen aktuell und relevant sind, und nicht zwischengespeicherter Müll. Darüber hinaus ist dies umso schwieriger, je komplexer und größer die Datenbank und je größer ihre Anzahl ist.
Aber normalerweise werfe ich die Maus weg und schreibe einfach Code. Nehmen wir an, Sie müssen herausfinden, welche Tabellen (und mit welchen Eigenschaften) im Schema „HR“ enthalten sind. In den meisten DBMS kann das gewünschte Ergebnis mit dieser einfachen Abfrage von information_schema erreicht werden:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Der Inhalt solcher Referenztabellen variiert von Datenbank zu Datenbank und hängt von den Fähigkeiten des jeweiligen DBMS ab. Und zum Beispiel für MySQL können Sie aus demselben Nachschlagewerk Tabellenparameter abrufen, die für dieses DBMS spezifisch sind:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle kennt information_schema nicht, aber es hat es , und es treten keine großen Probleme auf:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse ist keine Ausnahme:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Etwas Ähnliches kann in Cassandra durchgeführt werden (das Spaltenfamilien anstelle von Tabellen und Schlüsselräume anstelle von Schemas enthält):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Für die meisten anderen Datenbanken können Sie auch ähnliche Abfragen erstellen (sogar Mongo hat dies getan). , die Informationen über alle Sammlungen im System enthält).
Natürlich können Sie auf diese Weise nicht nur Informationen zu Tabellen, sondern zu jedem Objekt im Allgemeinen erhalten. Von Zeit zu Zeit teilen nette Leute solchen Code für verschiedene Datenbanken, wie zum Beispiel in der Habra-Artikelreihe „Funktionen zur Dokumentation von PostgreSQL-Datenbanken“ (, , ). Natürlich macht es so viel Spaß, diesen ganzen Berg an Abfragen im Kopf zu behalten und sie ständig einzugeben, daher habe ich in meiner Lieblings-IDE bzw. meinem Lieblingseditor einen vorgefertigten Satz von Snippets für häufig verwendete Abfragen, und jetzt muss ich nur noch die eingeben Objektnamen in die Vorlage einfügen.
Dadurch ist diese Art der Navigation und Suche nach Objekten wesentlich flexibler, spart viel Zeit und ermöglicht es Ihnen, genau die Informationen in der Form zu erhalten, in der sie jetzt benötigt werden (wie z. B. im Beitrag beschrieben). ).
Operationen mit Objekten
Nachdem wir die notwendigen Objekte gefunden und untersucht haben, ist es an der Zeit, etwas Nützliches damit zu tun. Natürlich auch ohne die Finger von der Tastatur zu nehmen.
Es ist kein Geheimnis, dass das einfache Löschen einer Tabelle in fast allen Datenbanken gleich aussieht:
drop table hr.personsAber mit der Erstellung der Tabelle wird es interessanter. Fast jedes DBMS (einschließlich vieler NoSQL-Datenbanken) kann in der einen oder anderen Form „Tabellen erstellen“, und der Hauptteil davon wird sich sogar geringfügig unterscheiden (Name, Liste der Spalten, Datentypen), andere Details können sich jedoch erheblich unterscheiden und hängen davon ab internes Gerät und Funktionen eines bestimmten DBMS. Mein Lieblingsbeispiel ist, dass es in der Oracle-Dokumentation nur „nackte“ BNFs für die „Create Table“-Syntax gibt . Andere DBMS verfügen über bescheidenere Funktionen, aber jedes von ihnen verfügt auch über viele interessante und einzigartige Funktionen zum Erstellen von Tabellen (, , , ). Es ist unwahrscheinlich, dass ein grafischer „Assistent“ einer anderen IDE (insbesondere einer universellen) alle diese Fähigkeiten vollständig abdecken kann, und selbst wenn dies möglich ist, wird es kein Spektakel für schwache Nerven sein. Gleichzeitig eine korrekt und zeitgerecht verfasste Stellungnahme Tabelle erstellen ermöglicht es Ihnen, sie alle einfach zu nutzen und die Speicherung und den Zugriff auf Ihre Daten zuverlässig, optimal und so komfortabel wie möglich zu gestalten.
Außerdem verfügen viele DBMS über ihre eigenen spezifischen Objekttypen, die in anderen DBMS nicht verfügbar sind. Darüber hinaus können wir Operationen nicht nur an Datenbankobjekten, sondern auch am DBMS selbst durchführen, beispielsweise einen Prozess „abbrechen“, Speicherbereich freigeben, die Ablaufverfolgung aktivieren, in den „Nur-Lese“-Modus wechseln und vieles mehr.
Jetzt lasst uns ein wenig zeichnen
Eine der häufigsten Aufgaben besteht darin, ein Diagramm mit Datenbankobjekten zu erstellen und die Objekte und Verbindungen zwischen ihnen in einem schönen Bild zu sehen. Nahezu jede grafische IDE, separate „Befehlszeilen“-Dienstprogramme, spezielle grafische Tools und Modellierer können dies tun. Sie werden „so gut sie können“ etwas für Sie zeichnen, und Sie können diesen Prozess nur mit Hilfe einiger Parameter in der Konfigurationsdatei oder Kontrollkästchen in der Benutzeroberfläche ein wenig beeinflussen.
Aber dieses Problem lässt sich viel einfacher, flexibler und eleganter lösen, und das natürlich mit Hilfe von Code. Um Diagramme beliebiger Komplexität zu erstellen, verfügen wir über mehrere spezialisierte Auszeichnungssprachen (DOT, GraphML usw.) und dafür über eine ganze Reihe von Anwendungen (GraphViz, PlantUML, Mermaid), die solche Anweisungen lesen und in verschiedenen Formaten visualisieren können . Nun, wir wissen bereits, wie wir Informationen über Objekte und Verbindungen zwischen ihnen erhalten.
Hier ist ein kleines Beispiel, wie dies mit PlantUML und aussehen könnte (Links ist eine SQL-Abfrage, die die erforderliche Anweisung für PlantUML generiert, und rechts ist das Ergebnis):
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'Und wenn Sie es ein wenig versuchen, dann basierend auf Sie können etwas erhalten, das einem echten ER-Diagramm sehr ähnlich ist:
Die SQL-Abfrage ist etwas komplizierter
-- Шапка
select '@startuml
!define Table(name,desc) class name 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
-- Таблицы
select format('Table(%s, "%s n information about %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
-- Связи между таблицами
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s may haven many %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
-- Подвал
select '@enduml'
Wenn Sie genau hinschauen, verwenden viele Visualisierungstools unter der Haube auch ähnliche Abfragen. Es stimmt, diese Bitten sind in der Regel tiefgründig , ganz zu schweigen von deren Modifikation.
Metriken und Überwachung
Kommen wir zu einem traditionell komplexen Thema – der Überwachung der Datenbankleistung. Ich erinnere mich an eine kleine wahre Geschichte, die mir „einer meiner Freunde“ erzählt hat. Bei einem anderen Projekt lebte ein gewisser mächtiger DBA, und nur wenige der Entwickler kannten ihn persönlich oder hatten ihn jemals persönlich gesehen (trotz der Tatsache, dass er Gerüchten zufolge irgendwo im Nebengebäude arbeitete). Zur Stunde „X“, als sich das Produktionssystem eines großen Einzelhändlers erneut „schlecht anzufühlen“ begann, schickte er stillschweigend Screenshots von Grafiken aus dem Oracle Enterprise Manager, auf denen er zur „Verständlichkeit“ kritische Stellen sorgfältig mit einem roten Marker markierte ( das hat, gelinde gesagt, nicht viel geholfen). Und anhand dieser „Fotokarte“ musste ich behandeln. Gleichzeitig hatte niemand Zugriff auf den (im doppelten Sinne des Wortes) wertvollen Enterprise Manager, weil Das System sei komplex und teuer, plötzlich „stolpern die Entwickler über etwas und machen alles kaputt.“ Daher haben die Entwickler „empirisch“ den Ort und die Ursache der Bremsen ermittelt und einen Patch veröffentlicht. Wenn der Drohbrief des DBA in naher Zukunft nicht noch einmal eintreffen würde, würden alle aufatmen und zu ihren aktuellen Aufgaben zurückkehren (bis zum neuen Brief).
Aber der Überwachungsprozess kann unterhaltsamer und freundlicher aussehen und vor allem für alle zugänglich und transparent sein. Zumindest der grundlegende Teil, als Ergänzung zu den wichtigsten Überwachungssystemen (die sicherlich nützlich und in vielen Fällen unersetzlich sind). Jedem DBMS steht es frei und absolut kostenlos, Informationen über seinen aktuellen Zustand und seine Leistung weiterzugeben. In derselben „blutigen“ Oracle-Datenbank können fast alle Informationen zur Leistung aus Systemansichten abgerufen werden, von Prozessen und Sitzungen bis hin zum Status des Puffercaches (z. B. , Abschnitt „Überwachung“). Postgresql verfügt auch über eine ganze Reihe von Systemansichten für , insbesondere solche, die im täglichen Leben eines jeden DBA unverzichtbar sind, wie z , , . MySQL verfügt hierfür sogar über ein eigenes Schema. . A In Mongo eingebaut fasst Leistungsdaten in einer Systemsammlung zusammen .
Wenn Sie also mit einer Art Metriksammler (Telegraf, Metricbeat, Collectd), der benutzerdefinierte SQL-Abfragen durchführen kann, einem Speicher dieser Metriken (InfluxDB, Elasticsearch, Timescaledb) und einem Visualisierer (Grafana, Kibana) ausgestattet sind, können Sie ziemlich einfach eine Lösung erhalten und ein flexibles Überwachungssystem, das eng mit anderen systemweiten Metriken integriert wird (z. B. vom Anwendungsserver, vom Betriebssystem usw.). Dies geschieht beispielsweise in pgwatch2, das die Kombination InfluxDB + Grafana und eine Reihe von Abfragen an Systemansichten verwendet, auf die ebenfalls zugegriffen werden kann .
Insgesamt
Und dies ist nur eine ungefähre Liste dessen, was mit unserer Datenbank unter Verwendung von regulärem SQL-Code gemacht werden kann. Ich bin mir sicher, dass Sie noch viele weitere Verwendungsmöglichkeiten finden werden, schreiben Sie in die Kommentare. Und wir werden darüber sprechen, wie (und vor allem warum) Sie das alles automatisieren und beim nächsten Mal in Ihre CI/CD-Pipeline integrieren können.
Source: habr.com