SQL, co by mohlo být jednodušší? Každý z nás může napsat jednoduchou žádost – napíšeme vybrat, pak vypište požadované sloupce od, název tabulky, některé podmínky v kde a to je vše – užitečná data máme v kapse a (téměř) bez ohledu na to, který DBMS je v tu dobu pod kapotou (nebo možná ). V důsledku toho lze práci s téměř jakýmkoli zdrojem dat (relačním a ne tak) posuzovat z pohledu běžného kódu (se vším, co to znamená – kontrola verzí, kontrola kódu, statická analýza, autotesty a to je vše). A to platí nejen pro data samotná, schémata a migrace, ale obecně pro celou životnost úložiště. V tomto článku budeme hovořit o každodenních úkolech a problémech práce s různými databázemi pod optikou „databáze jako kód“.
A začněme hned od . První bitvy typu „SQL vs ORM“ byly zaznamenány již v r .
Objektově-relační mapování
Příznivci ORM tradičně oceňují rychlost a snadnost vývoje, nezávislost na DBMS a čistý kód. Pro mnohé z nás je kód pro práci s databází (a často i databází samotnou)
většinou to vypadá nějak takhle...
@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 je ověšen chytrými anotacemi a někde v zákulisí statečný ORM generuje a spouští tuny nějakého SQL kódu. Mimochodem, vývojáři se ze všech sil snaží izolovat od své databáze kilometry abstrakcí, což o něčem svědčí .
Na druhé straně barikády vyznavači čistého „ručně vyrobeného“ SQL zaznamenávají schopnost vymáčknout ze svého DBMS všechnu šťávu bez dalších vrstev a abstrakcí. V důsledku toho se objevují „data-centrické“ projekty, kde jsou do databáze zapojeni speciálně vyškolení lidé (jsou také „základníci“, jsou také „základníci“, jsou také „basdenáři“ atd.) a vývojáři stačí „vytáhnout“ připravené pohledy a uložené procedury, aniž bychom zacházeli do detailů.
Co kdybychom měli to nejlepší z obou světů? Jak se to dělá v úžasném nástroji s život potvrzujícím názvem . Ve svém volném překladu uvedu pár řádků z obecného konceptu a vy se s ním můžete seznámit podrobněji .
Clojure je skvělý jazyk pro vytváření DSL, ale samotný SQL je skvělý DSL a nepotřebujeme další. S-výrazy jsou skvělé, ale nepřidávají zde nic nového. V důsledku toho dostaneme závorky kvůli závorkám. Nesouhlasím? Poté počkejte na okamžik, kdy začne prosakovat abstrakce nad databází a začnete s funkcí bojovat (raw-sql)
Tak co bych měl dělat? Ponechme SQL jako běžný SQL – jeden soubor na požadavek:
-- name: users-by-country
select *
from users
where country_code = :country_code... a pak si přečtěte tento soubor a přeměňte jej na běžnou funkci Clojure:
(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" ...} ...)Dodržováním zásady „sám o sobě SQL, sám o sobě Clojure“ získáte:
- Žádné syntaktické překvapení. Vaše databáze (jako každá jiná) není 100% kompatibilní se standardem SQL – to ale u Yesql nevadí. Nikdy nebudete ztrácet čas hledáním funkcí s ekvivalentní syntaxí SQL. Už se nikdy nebudete muset vracet k nějaké funkci (raw-sql "some('funky'::SYNTAX)")).
- Nejlepší podpora editoru. Váš editor již má vynikající podporu SQL. Uložením SQL jako SQL jej můžete jednoduše použít.
- Týmová kompatibilita. Vaši správci databází mohou číst a zapisovat SQL, který používáte ve svém projektu Clojure.
- Jednodušší ladění výkonu. Potřebujete sestavit plán pro problematický dotaz? To není problém, když je váš dotaz běžný SQL.
- Opětovné použití dotazů. Přetáhněte tytéž soubory SQL do jiných projektů, protože je to prostě staré SQL – stačí je sdílet.
Nápad je to podle mě velmi cool a zároveň velmi jednoduchý, díky čemuž si projekt mnohé získal v různých jazycích. A příště se pokusíme aplikovat podobnou filozofii oddělení SQL kódu od všeho ostatního daleko za ORM.
Správci IDE a DB
Začněme jednoduchým každodenním úkolem. Často musíme hledat nějaké objekty v databázi, například najít tabulku ve schématu a studovat její strukturu (jaké sloupce, klíče, indexy, omezení atd. jsou použity). A od jakéhokoli grafického IDE nebo malého DB-manageru v první řadě očekáváme přesně tyto schopnosti. Aby to bylo rychlé a nemuseli jste čekat půl hodiny, než se vykreslí okno s potřebnými informacemi (zejména při pomalém připojení ke vzdálené databázi), a zároveň byly přijaté informace čerstvé a relevantní, a ne mezipaměti. Navíc, čím je databáze složitější a větší a čím je větší počet, tím je to obtížnější.
Ale většinou zahodím myš a píšu jen kód. Řekněme, že potřebujete zjistit, které tabulky (a s jakými vlastnostmi) jsou obsaženy ve schématu „HR“. Ve většině DBMS lze požadovaného výsledku dosáhnout pomocí tohoto jednoduchého dotazu z information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'V jednotlivých databázích se obsah těchto referenčních tabulek liší v závislosti na možnostech jednotlivých DBMS. A například pro MySQL ze stejné referenční knihy můžete získat parametry tabulky specifické pro tento DBMS:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'Oracle nezná information_schema, ale ano a nevznikají žádné velké problémy:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse není výjimkou:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Něco podobného lze udělat v Cassandře (která má columnfamilies místo tabulek a keyspaces místo schémat):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'U většiny ostatních databází můžete také přijít s podobnými dotazy (dokonce i Mongo , která obsahuje informace o všech kolekcích v systému).
Samozřejmě tímto způsobem můžete získat informace nejen o tabulkách, ale obecně o jakémkoli objektu. Čas od času laskaví lidé sdílejí takový kód pro různé databáze, jako například v sérii článků habra „Funkce pro dokumentaci PostgreSQL databází“ (, , ). Mít celou tu horu dotazů v hlavě a neustále je psát je samozřejmě velká radost, takže v mém oblíbeném IDE/editoru mám předpřipravenou sadu úryvků pro často používané dotazy a zbývá jen napsat názvy objektů do šablony.
Díky tomu je tento způsob navigace a vyhledávání objektů mnohem flexibilnější, šetří spoustu času a umožňuje získat přesně ty informace v podobě, v jaké je nyní třeba (jak je například popsáno v příspěvku ).
Operace s předměty
Poté, co jsme našli a prostudovali potřebné předměty, je čas s nimi udělat něco užitečného. Samozřejmě také bez sundání prstů z klávesnice.
Není žádným tajemstvím, že pouhé smazání tabulky bude vypadat stejně v téměř všech databázích:
drop table hr.personsAle s vytvořením tabulky se to stává zajímavější. Téměř každý DBMS (včetně mnoha NoSQL) může „vytvořit tabulku“ v té či oné podobě a její hlavní část se bude dokonce mírně lišit (název, seznam sloupců, datové typy), ale další podrobnosti se mohou dramaticky lišit a závisí na interní zařízení a možnosti konkrétního DBMS. Můj oblíbený příklad je, že v dokumentaci Oracle existují pouze „nahé“ BNF pro syntaxi „vytvořit tabulku“. . Jiné DBMS mají skromnější možnosti, ale každý z nich má také mnoho zajímavých a jedinečných funkcí pro vytváření tabulek (, , , ). Je nepravděpodobné, že by některý grafický „čaroděj“ z jiného IDE (zejména univerzálního) dokázal všechny tyto schopnosti plně pokrýt, a i když ano, nebude to žádná podívaná pro slabé povahy. Zároveň správně a včas napsané prohlášení vytvořit tabulku vám umožní je všechny snadno používat, úložiště a přístup k vašim datům budou spolehlivé, optimální a co nejpohodlnější.
Mnoho DBMS má také své vlastní specifické typy objektů, které nejsou dostupné v jiných DBMS. Navíc můžeme provádět operace nejen s databázovými objekty, ale i se samotným DBMS, například „zabít“ proces, uvolnit část paměti, povolit trasování, přepnout do režimu „pouze pro čtení“ a mnoho dalšího.
Teď si trochu nakreslíme
Jedním z nejběžnějších úkolů je sestavit diagram s databázovými objekty a vidět objekty a spojení mezi nimi na krásném obrázku. Umí to téměř každé grafické IDE, samostatné nástroje „příkazového řádku“, specializované grafické nástroje a modeláři. Něco vám nakreslí „jak nejlépe umí“ a tento proces můžete trochu ovlivnit jen pomocí pár parametrů v konfiguračním souboru nebo zaškrtávacích políček v rozhraní.
Tento problém lze ale vyřešit mnohem jednodušeji, flexibilněji a elegantněji a samozřejmě s pomocí kódu. Pro vytváření diagramů jakékoli složitosti máme několik specializovaných značkovacích jazyků (DOT, GraphML atd.), a pro ně celou řadu aplikací (GraphViz, PlantUML, Mermaid), které dokážou takové pokyny přečíst a vizualizovat v různých formátech. . No, už víme, jak získat informace o objektech a souvislostech mezi nimi.
Zde je malý příklad toho, jak by to mohlo vypadat s použitím PlantUML a (vlevo je SQL dotaz, který vygeneruje požadovanou instrukci pro PlantUML, a vpravo výsledek):
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 když se trochu snažíte, tak na základě můžete získat něco velmi podobného skutečnému ER diagramu:
SQL dotaz je trochu složitější
-- Шапка
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'
Pokud se podíváte pozorně, pod pokličkou mnoho vizualizačních nástrojů také používá podobné dotazy. Je pravda, že tyto požadavky jsou obvykle hluboké , nemluvě o jejich úpravě.
Metriky a monitorování
Přejděme k tradičně složitému tématu – sledování výkonu databáze. Vzpomínám si na malý skutečný příběh, který mi vyprávěl „jeden z mých přátel“. Na jiném projektu žil jistý mocný DBA a málokterý z vývojářů ho osobně znal, nebo ho někdy osobně viděl (nehledě na to, že podle pověstí pracoval někde ve vedlejší budově) . V hodině „X“, kdy se dukční systém velkého prodejce začal znovu „cítit špatně“, tiše odeslal screenshoty grafů z Oracle Enterprise Manager, na kterých pečlivě zvýraznil kritická místa červenou značkou pro „srozumitelnost“ ( tohle, mírně řečeno, moc nepomohlo). A na základě této „fotokartičky“ jsem musel ošetřit. Zároveň nikdo neměl přístup k drahocennému (v obou smyslech toho slova) Enterprise Manageru, protože systém je složitý a drahý, najednou „vývojáři o něco narazí a všechno rozbijí“. Proto vývojáři „empiricky“ našli umístění a příčinu brzd a vydali opravu. Pokud by hrozivý dopis od DBA v nejbližší době znovu nedorazil, pak by si všichni oddechli a vrátili se ke svým dosavadním úkolům (až do nového Dopisu).
Proces monitorování však může vypadat zábavněji a přátelsky, a co je nejdůležitější, přístupný a transparentní pro každého. Alespoň jeho základní část, jako doplněk k hlavním monitorovacím systémům (které jsou jistě užitečné a v mnoha případech nenahraditelné). Jakýkoli DBMS je volně a zcela zdarma sdílet informace o svém aktuálním stavu a výkonu. Ve stejné „krvavé“ databázi Oracle DB lze ze systémových pohledů získat téměř jakékoli informace o výkonu, od procesů a relací až po stav mezipaměti (např. , sekce "Monitorování"). Postgresql má také celou řadu systémových pohledů , zejména ty, které jsou v každodenním životě každého DBA nepostradatelné, jako např , , . MySQL pro to má dokonce samostatné schéma. . A Vestavěný v Mongo agreguje data o výkonu do systémové kolekce .
Vyzbrojeni nějakým druhem sběrače metrik (Telegraf, Metricbeat, Collectd), který může provádět vlastní SQL dotazy, úložištěm těchto metrik (InfluxDB, Elasticsearch, Timescaledb) a vizualizérem (Grafana, Kibana), můžete získat poměrně snadno a flexibilní monitorovací systém, který bude úzce integrován s dalšími celosystémovými metrikami (získanými např. z aplikačního serveru, z OS atd.). Jako například v pgwatch2, který používá kombinaci InfluxDB + Grafana a sadu dotazů na systémové pohledy, ke kterým lze také přistupovat .
Celkem
A to je pouze přibližný výčet toho, co lze s naší databází dělat pomocí běžného SQL kódu. Určitě najdete mnohem více využití, napište do komentářů. A o tom, jak (a hlavně proč) to vše zautomatizovat a zahrnout do vašeho CI/CD potrubí, si povíme příště.
Zdroj: www.habr.com