SQL, o que poderia ser mais simples? Cada um de nós pode escrever uma solicitação simples - digitamos selecionar, liste as colunas obrigatórias e, em seguida, da, nome da tabela, algumas condições em onde e isso é tudo - dados úteis estão em nosso bolso e (quase) independentemente de qual SGBD está oculto naquele momento (ou talvez ). Como resultado, trabalhar com quase qualquer fonte de dados (relacional ou não) pode ser considerado do ponto de vista do código comum (com tudo o que isso implica - controle de versão, revisão de código, análise estática, autotestes e isso é tudo). E isso se aplica não apenas aos dados em si, esquemas e migrações, mas em geral a toda a vida útil do armazenamento. Neste artigo falaremos sobre tarefas cotidianas e problemas de trabalho com vários bancos de dados sob as lentes de “banco de dados como código”.
E vamos começar desde . As primeiras batalhas do tipo "SQL vs ORM" foram notadas em .
Mapeamento objeto-relacional
Os defensores do ORM tradicionalmente valorizam a velocidade e a facilidade de desenvolvimento, a independência do SGBD e o código limpo. Para muitos de nós, o código para trabalhar com o banco de dados (e muitas vezes com o próprio banco de dados)
geralmente é algo assim...
@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;
}
...O modelo é repleto de anotações inteligentes e, em algum lugar nos bastidores, um valente ORM gera e executa toneladas de código SQL. A propósito, os desenvolvedores estão tentando ao máximo se isolar de seu banco de dados com quilômetros de abstrações, o que indica alguns .
Do outro lado das barricadas, os adeptos do SQL puro “feito à mão” observam a capacidade de extrair todo o suco de seu SGBD sem camadas e abstrações adicionais. Como resultado, surgem projetos “centrados em dados”, onde pessoas especialmente treinadas estão envolvidas no banco de dados (eles também são “basicistas”, também são “basicistas”, também são “basdeners”, etc.), e os desenvolvedores basta “puxar” as views e os procedimentos armazenados já prontos, sem entrar em detalhes.
E se tivéssemos o melhor dos dois mundos? Como isso é feito em uma ferramenta maravilhosa com um nome que afirma a vida . Darei algumas linhas do conceito geral em minha tradução livre, e você poderá conhecê-lo com mais detalhes .
Clojure é uma linguagem legal para criar DSLs, mas o SQL em si é uma DSL legal e não precisamos de outra. As expressões S são ótimas, mas não acrescentam nada de novo aqui. Como resultado, obtemos colchetes por colchetes. Não concordo? Então espere o momento em que a abstração do banco de dados comece a vazar e você comece a lutar com a função (sql bruto)
Então, o que eu deveria fazer? Vamos deixar o SQL como SQL normal - um arquivo por solicitação:
-- name: users-by-country
select *
from users
where country_code = :country_code... e então leia este arquivo, transformando-o em uma função normal do 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" ...} ...)Ao aderir ao princípio "SQL por si só, Clojure por si só", você obtém:
- Sem surpresas sintáticas. Seu banco de dados (como qualquer outro) não é 100% compatível com o padrão SQL - mas isso não importa para o Yesql. Você nunca perderá tempo procurando funções com sintaxe equivalente a SQL. Você nunca terá que retornar a uma função (raw-sql "some('funky'::SYNTAX)")).
- Melhor suporte ao editor. Seu editor já possui excelente suporte SQL. Ao salvar SQL como SQL você pode simplesmente usá-lo.
- Compatibilidade da equipe. Seus DBAs podem ler e escrever o SQL que você usa em seu projeto Clojure.
- Ajuste de desempenho mais fácil. Precisa criar um plano para uma consulta problemática? Isso não é um problema quando sua consulta é SQL normal.
- Reutilizando consultas. Arraste e solte esses mesmos arquivos SQL em outros projetos porque é simplesmente um SQL antigo - basta compartilhá-lo.
Na minha opinião a ideia é muito legal e ao mesmo tempo muito simples, graças à qual o projeto ganhou muitos em vários idiomas. E a seguir tentaremos aplicar uma filosofia semelhante de separar o código SQL de tudo o mais além do ORM.
Gerenciadores de IDE e banco de dados
Vamos começar com uma tarefa simples do dia a dia. Muitas vezes temos que procurar alguns objetos no banco de dados, por exemplo, encontrar uma tabela no esquema e estudar sua estrutura (quais colunas, chaves, índices, restrições, etc. são utilizadas). E de qualquer IDE gráfico ou de um pequeno gerenciador de banco de dados, em primeiro lugar, esperamos exatamente essas habilidades. Para que seja rápido e você não precise esperar meia hora até que seja desenhada uma janela com as informações necessárias (principalmente com uma conexão lenta a um banco de dados remoto), e ao mesmo tempo, as informações recebidas sejam atualizadas e relevantes, e não lixo armazenado em cache. Além disso, quanto mais complexa e maior for a base de dados e quanto maior for o seu número, mais difícil será fazê-lo.
Mas geralmente jogo o mouse fora e apenas escrevo o código. Digamos que você precise descobrir quais tabelas (e com quais propriedades) estão contidas no esquema “HR”. Na maioria dos SGBDs, o resultado desejado pode ser alcançado com esta consulta simples do information_schema:
select table_name
, ...
from information_schema.tables
where schema = 'HR'De banco de dados para banco de dados, o conteúdo dessas tabelas de referência varia dependendo das capacidades de cada SGBD. E, por exemplo, para MySQL, no mesmo livro de referência você pode obter parâmetros de tabela específicos para este SGBD:
select table_name
, storage_engine -- Используемый "движок" ("MyISAM", "InnoDB" etc)
, row_format -- Формат строки ("Fixed", "Dynamic" etc)
, ...
from information_schema.tables
where schema = 'HR'A Oracle não conhece o information_schema, mas possui , e não surgem grandes problemas:
select table_name
, pct_free -- Минимум свободного места в блоке данных (%)
, pct_used -- Минимум используемого места в блоке данных (%)
, last_analyzed -- Дата последнего сбора статистики
, ...
from all_tables
where owner = 'HR'ClickHouse não é exceção:
select name
, engine -- Используемый "движок" ("MergeTree", "Dictionary" etc)
, ...
from system.tables
where database = 'HR'Algo semelhante pode ser feito no Cassandra (que possui famílias de colunas em vez de tabelas e espaços-chave em vez de esquemas):
select columnfamily_name
, compaction_strategy_class -- Стратегия сборки мусора
, gc_grace_seconds -- Время жизни мусора
, ...
from system.schema_columnfamilies
where keyspace_name = 'HR'Para a maioria dos outros bancos de dados, você também pode criar consultas semelhantes (até o Mongo tem , que contém informações sobre todas as coleções do sistema).
Claro, desta forma você pode obter informações não apenas sobre tabelas, mas sobre qualquer objeto em geral. De tempos em tempos, pessoas gentis compartilham esse código para diferentes bancos de dados, como, por exemplo, na série de artigos habra “Funções para documentar bancos de dados PostgreSQL” (, , ). Claro, manter toda essa montanha de consultas em minha cabeça e digitá-las constantemente é um prazer, então no meu IDE/editor favorito eu tenho um conjunto pré-preparado de trechos para consultas usadas com frequência, e tudo o que resta é digitar o nomes de objetos no modelo.
Com isso, esse método de navegação e busca de objetos é muito mais flexível, economiza muito tempo e permite obter exatamente as informações na forma em que agora são necessárias (como, por exemplo, descrito no post ).
Operações com objetos
Depois de encontrarmos e estudarmos os objetos necessários, é hora de fazer algo útil com eles. Naturalmente, também sem tirar os dedos do teclado.
Não é nenhum segredo que simplesmente excluir uma tabela terá a mesma aparência em quase todos os bancos de dados:
drop table hr.personsMas com a criação da mesa fica mais interessante. Quase qualquer SGBD (incluindo muitos NoSQL) pode “criar tabela” de uma forma ou de outra, e a parte principal dela será até um pouco diferente (nome, lista de colunas, tipos de dados), mas outros detalhes podem diferir dramaticamente e depender do dispositivo interno e capacidades de um SGBD específico. Meu exemplo favorito é que na documentação da Oracle existem apenas BNFs “naked” para a sintaxe “create table” . Outros SGBDs possuem capacidades mais modestas, mas cada um deles também possui muitos recursos interessantes e exclusivos para a criação de tabelas (, , , ). É improvável que qualquer “assistente” gráfico de outro IDE (especialmente um universal) seja capaz de cobrir totalmente todas essas habilidades e, mesmo que consiga, não será um espetáculo para os fracos de coração. Ao mesmo tempo, uma declaração escrita correta e oportuna criar mesa permitirá que você use todos eles facilmente, tornando o armazenamento e o acesso aos seus dados confiáveis, ideais e tão confortáveis quanto possível.
Além disso, muitos SGBDs possuem seus próprios tipos específicos de objetos que não estão disponíveis em outros SGBDs. Além disso, podemos realizar operações não apenas em objetos de banco de dados, mas também no próprio SGBD, por exemplo, “matar” um processo, liberar alguma área de memória, habilitar rastreamento, mudar para o modo “somente leitura” e muito mais.
Agora vamos desenhar um pouco
Uma das tarefas mais comuns é construir um diagrama com objetos de banco de dados e ver os objetos e as conexões entre eles em uma bela imagem. Quase qualquer IDE gráfico, utilitários de “linha de comando” separados, ferramentas gráficas especializadas e modeladores podem fazer isso. Eles desenharão algo para você “da melhor maneira que puderem”, e você poderá influenciar um pouco esse processo apenas com a ajuda de alguns parâmetros no arquivo de configuração ou caixas de seleção na interface.
Mas esse problema pode ser resolvido de forma muito mais simples, flexível e elegante e, claro, com a ajuda de código. Para criar diagramas de qualquer complexidade, temos diversas linguagens de marcação especializadas (DOT, GraphML etc), e para elas toda uma série de aplicativos (GraphViz, PlantUML, Mermaid) que podem ler tais instruções e visualizá-las em uma variedade de formatos. . Bom, já sabemos como obter informações sobre objetos e conexões entre eles.
Aqui está um pequeno exemplo de como isso poderia ser, usando PlantUML e (à esquerda está uma consulta SQL que irá gerar a instrução necessária para PlantUML, e à direita está o resultado):
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 você tentar um pouco, então com base em você pode obter algo muito semelhante a um diagrama ER real:
A consulta SQL é um pouco mais complicada
-- Шапка
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'
Se você olhar de perto, muitas ferramentas de visualização também usam consultas semelhantes. É verdade que estes pedidos são geralmente profundamente , sem mencionar qualquer modificação deles.
Métricas e monitoramento
Vamos passar para um tópico tradicionalmente complexo - monitoramento de desempenho de banco de dados. Lembro-me de uma pequena história verdadeira que me foi contada por “um dos meus amigos”. Em outro projeto vivia um certo DBA poderoso, e poucos desenvolvedores o conheciam pessoalmente ou já o tinham visto pessoalmente (apesar do fato de que, segundo rumores, ele trabalhava em algum lugar do prédio vizinho). Na hora “X”, quando o sistema de produção de um grande varejista começou a “se sentir mal” mais uma vez, ele silenciosamente enviou capturas de tela de gráficos do Oracle Enterprise Manager, nas quais destacou cuidadosamente os locais críticos com um marcador vermelho para “compreensibilidade” ( isso, para dizer o mínimo, não ajudou muito). E com base nesse “cartão fotográfico” tive que tratar. Ao mesmo tempo, ninguém tinha acesso ao precioso (em ambos os sentidos da palavra) Enterprise Manager, porque o sistema é complexo e caro, de repente “os desenvolvedores tropeçam em algo e quebram tudo”. Portanto, os desenvolvedores encontraram “empiricamente” a localização e a causa dos freios e lançaram um patch. Se a carta ameaçadora do DBA não chegasse novamente num futuro próximo, todos respirariam aliviados e retornariam às suas tarefas atuais (até a nova Carta).
Mas o processo de monitorização pode parecer mais divertido e amigável e, o mais importante, acessível e transparente para todos. Pelo menos a sua parte básica, como complemento aos principais sistemas de monitorização (que são certamente úteis e em muitos casos insubstituíveis). Qualquer SGBD é livre e absolutamente gratuito para compartilhar informações sobre seu estado e desempenho atuais. No mesmo “maldito” banco de dados Oracle, quase todas as informações sobre desempenho podem ser obtidas a partir de visualizações do sistema, desde processos e sessões até o estado do cache do buffer (por exemplo, , seção "Monitoramento"). O Postgresql também possui várias visualizações do sistema para , em especial aqueles que são indispensáveis no dia a dia de qualquer DBA, como , , . O MySQL ainda possui um esquema separado para isso. . Um integrado no Mongo agrega dados de desempenho em uma coleção de sistema .
Assim, munido de algum tipo de coletor de métricas (Telegraf, Metricbeat, Collectd) que pode realizar consultas SQL personalizadas, um armazenamento dessas métricas (InfluxDB, Elasticsearch, Timescaledb) e um visualizador (Grafana, Kibana), você pode obter uma solução bastante fácil. e um sistema de monitoramento flexível que estará intimamente integrado com outras métricas de todo o sistema (obtidas, por exemplo, do servidor de aplicativos, do sistema operacional, etc.). Como, por exemplo, isso é feito no pgwatch2, que utiliza a combinação InfluxDB + Grafana e um conjunto de consultas às visualizações do sistema, que também podem ser acessadas .
No total
E esta é apenas uma lista aproximada do que pode ser feito com nosso banco de dados usando código SQL normal. Tenho certeza que você encontrará muitos outros usos, escreva nos comentários. E falaremos sobre como (e o mais importante, por que) automatizar tudo isso e incluí-lo em seu pipeline de CI/CD na próxima vez.
Fonte: habr.com