SQL, co może być prostszego? Każdy z nas może napisać prosty zapytanie — wpisujemy select, wymieniamy potrzebne kolumny, potem od, nazwę tabeli, trochę warunków w gdzie i to wszystko — przydatne dane mamy w kieszeni, niezależnie od tego, jaka baza danych jest aktualnie używana (a może i ). W rezultacie pracę praktycznie z każdym źródłem danych (relacyjnym i nie tylko) można rozważać w kategoriach zwykłego kodu (ze wszystkimi tego konsekwencjami — kontrola wersji, przegląd kodu, analiza statyczna, testy automatyczne i tym podobne). A to dotyczy nie tylko samych danych, schematów i migracji, ale w ogóle całego życia magazynu. W tym artykule porozmawiamy o codziennych zadaniach i problemach związanych z różnymi bazami danych w kontekście "bazy danych jako kod".
Zaczniemy od . Pierwsze starcia typu "SQL vs ORM" zauważono już w .
Mapowanie obiektowo-relacyjne
Zwolennicy ORM tradycyjnie cenią szybkość i prostotę tworzenia, niezależność od bazy danych oraz czystość kodu. Dla wielu z nas kod pracy z bazą danych (a często i sama baza danych)
zwykle wygląda mniej więcej tak…
@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;
}
...Model jest obciążony mądrymi adnotacjami, a gdzieś w tle dzielny ORM generuje i wykonuje tony jakiegoś kodu SQL. Swoją drogą, programiści za wszelką cenę starają się oddzielić swoje bazy danych od siebie, tworząc kilometry abstrakcji, co świadczy o pewnej .
Po drugiej stronie barykady zwolennicy czystego "ręcznie pisanego" SQL zauważają możliwość wyciskania z bazy danych każdej kropli bez dodatkowych warstw i abstrakcji. W rezultacie pojawiają się projekty "data-centric", w których bazą zajmują się specjalnie przeszkolone osoby (zwane "bazistami", "bazowikami", "baza-derkami" itp.), a programiści tylko "wyciągają" gotowe widoki i procedury składowane, nie zagłębiając się w szczegóły.
A co jeśli weźmiemy to, co najlepsze z dwóch światów? Jak to jest zrealizowane w wspaniałym narzędziu o afirmatywnym tytule . Przytoczę kilka zdań z ogólnej koncepcji w moim swobodnym tłumaczeniu, a bardziej szczegółowo z nią można się zapoznać .
Clojure to wspaniały język do tworzenia DSL, ale SQL sam w sobie jest świetnym DSL i nie potrzebujemy kolejnego. Wyrażenia S są piękne, ale tutaj nie wnoszą nic nowego. W efekcie mamy nawiasy dla samych nawiasów. Nie zgadzasz się? Poczekaj do momentu, gdy abstrakcja nad bazą danych zacznie przeciekać, a Ty rozpoczniesz walkę z funkcją. (raw-sql)
I co robić? Pozwólmy, aby SQL pozostał zwykłym SQL — jeden plik na jeden zapytanie:
-- name: users-by-country
select *
from users
where country_code = :country_code… a następnie przeczytaj ten plik, przekształcając go w zwykłą funkcję Clojure:
(defqueries "some/where/users_by_country.sql"
{:connection db-spec})
;;; Utworzono funkcję o nazwie `users-by-country`.
;;; Użyjmy jej:
(users-by-country {:country_code "GB"})
;=> ({:name "Kris" :country_code "GB" ...} ...)Przestrzegając zasady "SQL osobno, Clojure osobno", otrzymujesz:
- Brak zaskoczeń składniowych. Twoja baza danych (jak każda inna) nie odpowiada standardowi SQL w 100% — ale dla Yesql to nie ma znaczenia. Nigdy nie będziesz tracić czasu na polowanie na funkcje o składni odpowiadającej SQL. Nigdy nie będziesz musiał wracać do funkcji. (raw-sql "some (‘funky’ :: SYNTAX)").
- Najlepsze wsparcie edytora. Twój edytor ma już doskonałe wsparcie dla SQL. Utrzymując SQL jako SQL, możesz po prostu go używać.
- Zgodność zespołowa. Twoi DBA mogą czytać i pisać SQL, którego używasz w swoim projekcie Clojure.
- Łatwiejsza konfiguracja wydajności. Musisz zbudować plan dla problematycznego zapytania? To nie problem, gdy Twoje zapytanie jest zwykłym SQL.
- Ponowne użycie zapytań. Przenieś te same pliki SQL do innych projektów, ponieważ to po prostu stary dobry SQL — po prostu podziel się nim.
Moim zdaniem pomysł jest bardzo fajny, a przy tym bardzo prosty, dzięki czemu projekt zyskał wiele. w różnych językach. A my następnie spróbujemy zastosować podobną filozofię oddzielania kodu SQL od reszty dumnie poza ORM.
IDE & DB-menadżery
Zacznijmy od prostej, codziennej czynności. Często musimy wyszukiwać różne obiekty w bazie danych, na przykład znaleźć tabelę w schemacie i zbadać jej strukturę (jakie kolumny, klucze, indeksy, ograniczenia i inne są używane). Od każdej graficznej IDE lub jakiegokolwiek DB-managera oczekujemy przede wszystkim tych umiejętności. Aby było szybko i nie trzeba było czekać pół godziny, aż pojawi się okno z potrzebnymi informacjami (szczególnie przy wolnym połączeniu z zdalną bazą danych), a jednocześnie, aby uzyskane informacje były świeże i aktualne, a nie przestarzałe w pamięci podręcznej. Co więcej, im bardziej złożona i większa baza danych oraz im więcej ich jest, tym trudniejsze staje się to zadanie.
Ale zazwyczaj odstawiam myszkę na bok i po prostu piszę kod. Załóżmy, że trzeba dowiedzieć się, jakie tabele (i jakie mają właściwości) znajdują się w schemacie 'HR'. W większości systemów baz danych można uzyskać potrzebny wynik za pomocą takiego prostego zapytania z information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'Od bazy do bazy zawartość takich tabel referencyjnych różni się w zależności od możliwości każdej bazy danych. Na przykład, dla MySQL z tej samej tablicy można uzyskać specyficzne dla tej bazy dane tabeli:
select table_name
, storage_engine -- Używany 'silnik' ('MyISAM', 'InnoDB' itd.)
, row_format -- Format wiersza ('Fixed', 'Dynamic' itd.)
, ...
from information_schema.tables
where schema = 'HR'Oracle nie obsługuje information_schema, ale ma , i nie ma większych problemów:
select table_name
, pct_free -- Minimalna ilość wolnego miejsca w bloku danych (%)
, pct_used -- Minimalna ilość zajętego miejsca w bloku danych (%)
, last_analyzed -- Data ostatniego zbierania statystyk
, ...
from all_tables
where owner = 'HR'Nie inaczej jest z ClickHouse:
select name
, engine -- Używany 'silnik' ('MergeTree', 'Dictionary' itd.)
, ...
from system.tables
where database = 'HR'Podobne zapytanie można wykonać również w Cassandrze (gdzie są columnfamilies zamiast tabel i keyspace zamiast schematów):
select columnfamily_name
, compaction_strategy_class -- Strategia kompresji
, gc_grace_seconds -- Czas życia danych
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Dla większości innych baz danych również można wymyślić podobne zapytania (nawet w Mongo jest , która zawiera informacje o wszystkich kolekcjach w systemie).
Oczywiście, w ten sposób można uzyskać informacje nie tylko o tabelach, ale o każdym obiekcie. Co jakiś czas dobrzy ludzie dzielą się takim kodem dla różnych baz danych, jak na przykład w serii artykułów na Habra dotyczących "Funkcje do dokumentowania baz danych PostgreSQL" (, , ). Oczywiście, trzymanie całej tej masy zapytań w głowie i ciągłe ich wpisywanie to "nieduża przyjemność", dlatego w mojej ulubionej IDE/edytorze mam wcześniej przygotowany zestaw snippetów dla często używanych zapytań, wystarczy tylko wpisać nazwy obiektów w szablon.
W rezultacie taki sposób nawigacji i wyszukiwania obiektów jest znacznie bardziej elastyczny, oszczędza dużo czasu, pozwala uzyskać dokładnie te informacje i w takim formacie, w jakim są teraz potrzebne (jak opisano na przykład w poście ).
Operacje z obiektami
Po tym, jak znaleźliśmy i przeanalizowaliśmy potrzebne obiekty, nastał czas na zrobienie z nimi czegoś użytecznego. Oczywiście, również nie odrywając palców od klawiatury.
Nie jest tajemnicą, że proste usunięcie tabeli będzie wyglądać w zasadzie identycznie w niemal wszystkich bazach danych:
drop table hr.personsAle tworzenie tabeli jest już bardziej interesujące. Właściwie każda SGBD (w tym wiele NoSQL) w takim czy innym stopniu potrafi używać "create table", a jej główna część będzie się nawet niewiele różnić (nazwa, lista kolumn, typy danych), ale inne szczegóły mogą się znacznie różnić i zależą od wewnętrznej budowy i możliwości konkretnej SGBD. Mój ulubiony przykład — w dokumentacji Oracle same "gołe" BNF’y dla składni "create table" . Inne SGBD mają bardziej skromne możliwości, ale każda z nich także dysponuje wieloma interesującymi i unikalnymi funkcjami przy tworzeniu tabel (, , , ). Mało prawdopodobne, że jakiś graficzny "wizard" z kolejnej IDE (szczególnie uniwersalnej) będzie w stanie w pełni pokryć wszystkie te możliwości, a jeśli nawet to zrobi, to będzie to widok nie dla ludzi o słabych nerwach. W międzyczasie dobrze i na czas napisany operator create table pozwoli bez trudu skorzystać ze wszystkich z nich, zapewniając, że przechowywanie i dostęp do twoich danych będą niezawodne, optymalne i maksymalnie komfortowe.
W wielu systemach zarządzania bazami danych istnieją specyficzne typy obiektów, które są nieobecne w innych. Możemy wykonywać operacje nie tylko na obiektach bazy danych, ale także na samej bazie, na przykład "zabić" proces, zwolnić określoną przestrzeń pamięci, włączyć śledzenie, przełączyć się w tryb "tylko do odczytu" i wiele innych.
A teraz trochę pokreatywujemy
Jednym z najczęściej spotykanych zadań jest stworzenie diagramu z obiektami bazy danych, aby zobaczyć obiekty i ich wzajemne powiązania na ładnym obrazku. Z tym poradzi sobie praktycznie każda graficzna IDE, oddzielne narzędzia "command line", specjalistyczne narzędzia graficzne i modelery. Które coś narysują "jak potrafią", ale wpływ na ten proces można mieć jedynie poprzez kilka parametrów w pliku konfiguracyjnym lub zaznaczenia w interfejsie.
Ale ten problem można rozwiązać znacznie prościej, elastyczniej i elegancko, oczywiście za pomocą kodu. Do rysowania diagramów o dowolnym poziomie skomplikowania mamy kilka specjalistycznych języków znaczników (DOT, GraphML itd.), a do nich całą gamę aplikacji (GraphViz, PlantUML, Mermaid), które potrafią czytać takie instrukcje i wizualizować w najróżniejszych formatach. A informacje o obiektach i ich powiązaniach już wiemy, jak zdobyć.
Podajmy mały przykład, jak to mogłoby wyglądać, z użyciem PlantUML i (po lewej SQL zapytanie, które wygeneruje potrzebną instrukcję dla PlantUML, a po prawej rezultat):
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'A jeśli trochę się postarasz, to na podstawie można uzyskać coś mocno przypominającego prawdziwy diagram ER:
Zapytanie SQL jest o odrobinę bardziej skomplikowane
-- Nagłówek
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
-- Tabele
select format('Table(%s, "%s n informacje o %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
-- Relacje między tabelami
select distinct ccu.table_name || ' "1" --> "0..N" ' || tc.table_name || format(' : "A %s może mieć wiele %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
-- Stopka
select '@enduml' 
Jeśli uważnie się przyjrzysz, to pod maską wiele narzędzi wizualizacyjnych używa podobnych zapytań. Jednak są one zazwyczaj głęboko , nie mówiąc już o jakiejkolwiek ich modyfikacji.
Metryki i monitoring
Przejdźmy do tradycyjnie trudnego tematu — monitorowanie wydajności baz danych. Przypomnę sobie małą prawdziwą historię, którą opowiedział mi "jeden z moich przyjaciół". Na jednym z projektów żył sobie potężny DBA, a mało kto z programistów znał go osobiście, nawet nie widział go nigdy na oczy (mimo że podobno pracował gdzieś w sąsiednim budynku). W godzinie "X", gdy produkcyjny system dużego detalisty ponownie zaczynał "źle się czuć", bez słowa przesyłał zrzuty ekranowe wykresów z Oracle Enterprise Manager, na których starannie zaznaczał krytyczne miejsca czerwonym markerem dla "lepszej czytelności" (co łagodnie mówiąc, mało pomagało). I po tym "zdjęciu" trzeba było leczyć. Przy tym nikt nie miał dostępu do cennego (w obu znaczeniach tego słowa) Enterprise Manager, ponieważ system był skomplikowany i drogi, żeby nie "wpadli w coś programiści i wszystkiego nie zepsuli". Dlatego programiści "empirycznie" znajdowali miejsce i przyczynę zacięć i wydawali poprawkę. Jeśli groźny list od DBA nie przychodził ponownie w najbliższym czasie, wszyscy z ulgą wypuszczali powietrze i wracali do swoich bieżących zadań (do nowego Pisma).
Jednak proces monitorowania może wyglądać bardziej wesoło i przyjaźnie, a co najważniejsze — być dostępny i przejrzysty dla wszystkich. Przynajmniej jego podstawowa część, jako dodatek do głównych systemów monitorowania (które są niewątpliwie przydatne i w wielu przypadkach niezastąpione). Każda SGBD jest gotowa bezpłatnie podzielić się informacjami o swoim aktualnym stanie i wydajności. W tej samej "krwawej" bazie danych Oracle prawie wszystkie informacje o wydajności można uzyskać z widoków systemowych, zaczynając od procesów i sesji, aż po stan buforowego cache'a (na przykład, , sekcja "Monitorowanie"). W PostgreSQL również istnieje cała gama widoków systemowych do , w szczególności takie niezbędne w codziennej pracy każdego DBA, jak , , . W MySQL z tego powodu jest nawet oddzielny schemat . A w MongoDB wbudowany agreguje dane o wydajności w kolekcji systemowej .
W ten sposób, uzbrojony w dowolny program do zbierania metryk (Telegraf, Metricbeat, Collectd), który potrafi wykonywać niestandardowe zapytania SQL, oraz system przechowywania tych metryk (InfluxDB, Elasticsearch, Timescaledb) i wizualizator (Grafana, Kibana), można stworzyć stosunkowo prosty i elastyczny system monitorowania, który będzie ściśle zintegrowany z innymi metrykami systemowymi (uzyskać je można na przykład z serwera aplikacji, systemu operacyjnego itp.). Jak to jest zrealizowane w pgwatch2, gdzie używana jest kombinacja InfluxDB + Grafana oraz zestaw zapytań do widoków systemowych, do których można również .
Podsumowując
I to tylko przybliżony wykaz tego, co można zrobić z naszą bazą danych za pomocą standardowego kodu SQL. Jestem pewien, że można znaleźć jeszcze wiele zastosowań, piszcie w komentarzach. A o tym, jak (a co najważniejsze, dlaczego) można to wszystko zautomatyzować i włączyć do swojego pipeline'u CI/CD, porozmawiamy następnym razem.
Źródło: habr.com
