Fala dataholics, no post de hoje falaremos um pouco sobre as System Tables, alguns posts atrás falamos sobre os SHOW COMMANDs e hoje mostraremos a magia das system tables no Databricks.
No post de hoje veremos:
O que são as System Tables
System Tables disponíveis
Habilitando novas system tables (Compute e Billing)
Usando as system tables e exemplos práticos
Consultando todas as tabelas do ambiente
Todas as colunas de cada tabela em formato de lista
Volumetria de tabelas por catálogo e schema
Auditoria do ambiente - Quem alterou meu ambiente?
Quem acessou minha tabela?
Listando todos os clusters (Chega de APIs)
Cluster mais caros - DBUs
Cluster mais caro - USD
Total de USD por mês
Quem já trabalha com Databricks há um tempo sabe o quanto é difícil coletar informações de metadados e informações de monitoramento do ambiente, sendo a maioria das informações necessitando de acessar APIs ou criar scripts customizados com Loops e por aí vai.
As system tables (tabelas de sistema) no Databricks nasceram recentemente e só estão disponíveis para quem utiliza Unity Catalog, se você ainda não usa Unity Catalog está mais que atrasado.
Essas tabelas disponibilizam dados de monitoria do seu ambiente Databricks e metadados sobre seus objetos, se você já está habituado aos SGBDs mais tradicionais já notou que todos eles tem suas system tables, exemplo no SQL Server chamamos de DMVs, essas informações disponibilizadas são realmente incríveis e ricas para monitoramento e exploração do ambiente.
A ideia é disponibilizar todos os dados do seu ambiente via tabelas e não mais ter que despejar em um storage e depois consumir essas informações, realizar tratamentos complexos, como fazemos com a maioria dos logs de monitoramento e auditoria hoje, exemplo utilizando o Azure Diagnostics Setting para salvar dados no Storage, Azure Event Hubs ou Log Analytics.
Embora, continua em construção e nem todos os metadados e logs de monitoramento estão disponíveis, mas acredito que em um futuro breve, teremos muitas informações prontas nessas tabelas de sistema.
As System tables estão em Public Preview, ou seja, todos podem testar, mas ainda não é considerada para produção até entrar em GA (General Availability), apesar da recomendação em produção ser sempre usar features que estão em GA, existem features que não trazem riscos para o ambiente, como as system tables, você poderia utilizar em produção, contudo, comportamentos inesperados poderiam acontecer, grandes mudanças em tabelas e colunas, logo pode te gerar um retrabalho, embora, não te impeça de testar bastante e já planejar projetos em cima delas.
Algumas tabelas continuam em Private Preview, ou seja, somente clientes específicos possuem acesso, nesse post falaremos das opções disponíveis no Azure Databricks.
Aos poucos novas system tables serão adicionadas, a ideia principal é que elas sejam uma fonte única dos nossos monitoramentos, sem mais usar APIs ou métodos complexos para coleta de dados, agora teremos tudo em tabelas com acesso via SQL.
Ainda não temos dados em tempo real, são geralmente atualizados algumas vezes por dia, embora, acredito que muito em breve teremos.
Quanto isso custa? Nada. Contudo, como esta em preview, ainda não temos informações do tempo de retenção, talvez algumas mudanças e configurações serão disponibilizadas na versão GA.
Tabelas disponíveis para uso
Na documentação você pode ver a lista completa das System Tables disponíveis e a explicação de cada uma delas, mostrarei exemplos práticos de utilização para algumas delas.
Nem todas as system tables vem ativas por padrão, algumas delas você precisa habilitar manualmente via API, abaixo um script para habilitar as seguintes tabelas:
billing - Acesso a dados de cobranças dos seus clusters
lineage - Acesso a dados sobre linhagens das tabelas
storage - Acesso a dados de storage, exemplo, PREDICTIVE OPTMIZATION
compute - Acesso a dados dos seus clustersListando schemas disponíveis:
Habilitando as tabelas de billing:
Basta repetir o processo para os demais schemas, note que alguns continuam marcados como UNAVAILABLE, esses não podemos utilizar.
No final deixarei um link de referência da Databricks Labs com diversos exemplos, incluindo outra maneira de habilitar todos schemas em um loop.
Usando as System Tables
Você pode utilizar os metadados para diversas coisas, como documentação, auditoria, monitoramento, etc.
Primeiro ponto a notar é que todos os Catalogos possuem um schema chamado INFORMATION_SCHEMA (Muito familiar aos SGBDs não?!),
Nesse schema temos informações de todos os metadados desse catálogo, como, por exemplo, tabelas, colunas, constraints, tags, views, etc.
Contudo, temos um catálogo master, nele também temos uma INFORMATION_SCHEMA, mas consolidando todos os outros catálogos, isso é sensacional, pois, com uma query você pode ver todo o seu ambiente.
Esse catalogo centraliza as informações em comum de todos os Workspaces, exemplo no schema BILLING você tem informações de todos os WORKSPACES da sua conta, independente da região, isso mesmo, você centraliza tudo num canto só.
Vamos começar as brincadeiras com SQL agora
Listando todas as tabelas do seu ambiente:
-- Todas as tabelas do seu ambienteselect * from system.information_schema.tables where table_owner <> 'System user'
Listando todas as colunas das suas tabelas:
-- Todas as colunas de cada tabela
select c.table_name,array_join(collect_set(column_name), ',') as columns from system.information_schema.columns c
inner join system.information_schema.tables t on c.table_name = t.table_name and c.table_catalog = t.table_catalog
where t.table_owner <> 'System user'
group by all-- Todas as colunas de cada tabela
select c.table_name,array_join(collect_set(column_name), ',') as columns from system.information_schema.columns c
inner join system.information_schema.tables t on c.table_name = t.table_name and c.table_catalog = t.table_catalog
where t.table_owner <> 'System user'
group by all
Quantidade de tabelas no ambiente:
-- Quantidade de tabelas por schema e catalog
select table_catalog,table_schema,count(*) as qtdTables
from system.information_schema.tables where table_owner <> 'System user'
group by all;
Auditoria do seu ambiente:
Aqui nessa tabela você pode saber tudo que acontece no seu ambiente, quem fez quando fez e o que fez.
-- Auditoria do seu ambiente
select * from system.access.audit
Qual último acesso nas suas tabelas:
-- Ultimo acesso nas suas tabelas
select LastAccess.event_time as LastAcces,LastAccess.entity_type,LastAccess.created_by as WhoAccessed,*
from system.information_schema.tables a
LEFT JOIN
LATERAL (select max(b.event_time) as event_time, LAST(b.entity_type) as entity_type, LAST(b.created_by) as created_by
from system.access.table_lineage b where b.target_table_name = a.table_name) as LastAccess
where a.table_owner <> 'System user';
Quem acessou minha tabela?
Com essa tabela você pode ver quem acessou sua tabela e quando, isso é muito útil para poder desabilitar tabelas não usadas.
-- Quem acessou sua tabela e quando?
select * from system.access.table_lineage where target_table_name = 'tbordersliquid'
order by event_time desc;
Listando todos os seus clusters:
Chega de APIs agora você pode ver todos seus clusters nessa tabela.
-- Todos os clusters do ambiente
select cluster_source,count(*) as qtd from system.compute.clusters
group by all
Vamos começar a falar de custos?
-- Cluster mais custoso em DBUs
select b.cluster_name, sum(usage_quantity) as `DBUs Consumed` from system.billing.usage a
inner join system.compute.clusters b on a.usage_metadata.cluster_id = b.cluster_id
where usage_metadata.cluster_id is not null
group by all
order by 2 desc;
Cluster mais custoso em USD:
-- Cluster mais custoso em USD
select b.cluster_name, sum(usage_quantity) as `DBUs Consumed`, (sum(usage_quantity) * max(c.pricing.default)) as TotalUSD
from system.billing.usage a
inner join system.compute.clusters b on a.usage_metadata.cluster_id = b.cluster_id
inner join system.billing.list_prices c on c.sku_name = a.sku_name
where usage_metadata.cluster_id is not null
and usage_start_time between '2023-11-01' and '2023-11-30'
group by all
order by 3 desc;
Total gasto por mês:
-- total em USD por mês
select month(usage_end_time) as mes,sum(usage_quantity) as `DBUs Consumed`, (sum(usage_quantity) * max(c.pricing.default)) as TotalUSD
from system.billing.usage a
inner join system.billing.list_prices c on c.sku_name = a.sku_name
group by all
order by 1 desc
Essa paulada em novembro foi fazendo demo do PREDICTIVE OPTIMIZATION rsrs, não é barato estudar.
Espero que tenha gostado.
Fique bem e até a próxima.
Link script SQL:
Referências: