Apresentando Expressões de Tabela Comum Recursivas para Databricks

As Expressões de Tabela Comum Recursivas (CTEs) agora são suportadas no Databricks. Isso traz uma maneira nativa de expressar loops e travessias em SQL, útil para trabalhar com dados hierárquicos e estruturados em gráficos. Essas capacidades estão alinhadas com o padrão SQL e seguem padrões familiares usados em plataformas como Teradata. As CTEs Recursivas fazem parte do padrão SQL há muito tempo, portanto, serão familiares aos clientes que estão migrando de armazéns de dados legados. Databricks também contribuiu com o suporte a CTEs Recursivas para Apache Spark™, tornando-o totalmente open source.

A Databricks usa a sintaxe SQL ANSI padrão para CTEs recursivas, incluindo a RECURSIVE palavra-chave.

Este pequeno recurso aumenta significativamente as capacidades expressivas do SQL, tornando-o teoricamente Turing completo - o que significa que ele pode realizar qualquer cálculo que um computador pode fazer. As CTEs recursivas permitem soluções compostas que antes exigiam código procedural, como Python ou ferramentas externas.

CTEs Recursivas agora estão disponíveis na Public Preview DBSQL 2025.20 e Databricks Runtime 17.0 (em breve no Pipelines Declarativos Lakeflow). Neste blog, exploraremos como as CTEs recursivas funcionam - e como elas podem ajudar a resolver problemas do mundo real usando apenas SQL.

Principais características do suporte a CTE recursiva

O suporte do Databricks para CTE recursivo inclui:

• Traversal de estruturas semelhantes a árvores e gráficos, como organogramas, pastas e redes de roteamento
• Totalmente de código aberto e integrado ao Apache Spark™
• Salvaguardas integradas para recursão infinita (100 passos, 1M linhas)
• Salvaguardas personalizáveis usando MAX RECURSION LEVEL
• Suporte para recursão infinita controlada usando LIMIT

CTEs recursivos funcionam bem tanto com sistemas tradicionais que armazenam dados hierárquicos em tabelas normalizadas quanto com dados provenientes de aplicações modernas que geram hierarquias JSON/XML flexíveis. Veja exemplos abaixo de cada um, incluindo RCTEs aproveitando o tipo de dados Variant para hierarquias JSON.

Além disso, o suporte para CTEs recursivas simplifica as migrações de sistemas de banco de dados legados. Teradata e Postgres são dois exemplos de sistemas cuja sintaxe é idêntica, enquanto sistemas como Oracle, que usam a sintaxe CONNECT BY, são facilmente convertidos.

Como funcionam as CTEs recursivas

CTEs recursivas são expressões de tabela comuns definidas com a RECURSIVE palavra-chave. Elas consistem em duas partes combinadas usando UNION ALL:

1. Um subconsulta de caso base — isso é executado uma vez e inicia a recursão
2. Um subconsulta de passo recursivo — isso se refere à própria CTE e é aplicado repetidamente para construir novas linhas.

A execução começa com a consulta base. Em seguida, em cada iteração, a etapa recursiva é executada usando a saída da etapa anterior. Isso continua até que nenhuma nova linha seja produzida.

Para evitar que a recursão infinita consuma recursos excessivos, o Databricks impõe dois limites de segurança: um profundidade máxima de recursão de 100 passos e um limite de linhas de 1 milhão. Se qualquer um desses limites for excedido, a consulta falha com um erro.

Se você está confiante de que sua recursão requer mais de 100 passos para produzir todos os resultados, você pode substituir o nível máximo usando a dica MAX RECURSION LEVEL:

 Para mais detalhes, consulte a documentação.

"Na bp Supply Trading and Shipping - Market Risk, entender a hierarquia do portfólio em todas as unidades de negócios é crucial para o funcionamento eficiente de nosso negócio. Ao substituir nosso código legado por CTEs recursivas no Databricks SQL, reduzimos uma etapa de preparação de dados hierárquicos de ~6 minutos para ~30 segundos, o que é uma melhoria de 12 vezes." — Dharmik Prajapati, Engenheiro de Software da bp

Exemplos de resolução de tarefas iterativas usando CTEs Recursivas

Navegue por dados de árvore e hierarquia: Encontrando materiais necessários usando uma Lista de Materiais

Na indústria de manufatura, cada peça fabricada requer um conjunto de componentes para construir. Cada componente pode ser dividido em um conjunto menor de peças individuais. O conjunto completo de todas as peças é chamado de Lista de Materiais (BOM). 

Uma BOM geralmente forma uma estrutura semelhante a uma árvore - ou mais geralmente, um gráfico acíclico direcionado (DAG). Neste exemplo, olhamos para as partes de uma bicicleta, que simplificaremos assumindo uma estrutura de árvore, onde cada componente é usado em exatamente um pai. 

Suponha que queremos calcular quantas matérias-primas são necessárias para construir uma bicicleta. Considere a seguinte BOM:

Cada linha descreve um componente, a parte maior a qual pertence, e quantos componentes são necessários para montar uma unidade do pai.

A CTE recursiva começa com um objetivo: construir uma bicicleta. Esse é o caso base. Em cada etapa recursiva, quebramos os componentes em seus subcomponentes. Por exemplo, uma bicicleta inclui um quadro, um conjunto de transmissão e duas rodas. Cada roda, por sua vez, consiste em um pneu e 32 raios. A estrutura recursiva fica clara à medida que quebramos as partes em pedaços menores.

Uma vez que expandimos totalmente a hierarquia, filtramos os componentes intermediários (pais) para reter apenas os materiais brutos necessários para a montagem.

Esta consulta calcula a quantidade total de cada material base necessário para construir uma bicicleta:

Busca de caminhos baseada na travessia de gráficos: Encontrando todas as rotas de voo de uma cidade

Vamos examinar um problema usando uma estrutura de dados de gráfico. Um gráfico consiste em um conjunto de nós conectados por arestas. É usado para representar relações ou conexões entre pares de elementos. Resolver um problema de gráfico costumava exigir Python, lógica de script complicada ou uma biblioteca externa. Agora, as consultas recursivas tornam isso simples. 

Um problema típico de estrutura de grafo é a viagem de avião: quais aeroportos posso alcançar usando uma série de voos? Suponha que temos o seguinte conjunto de voos que existem em um dia:

Cada voo é dado os códigos IATA de sua origem e destino, juntamente com os horários de partida e chegada.

Suponha que uma pessoa chegue ao aeroporto BEG às 8 da manhã e queira encontrar todas as rotas de viagem possíveis que ela pode fazer naquele dia.

Isso é naturalmente apresentado como um problema iterativo. Cada vez que descobrimos uma nova cidade que podemos alcançar, encontramos todos os voos que partem de lá depois do nosso horário de chegada. Por esse motivo, na CTE recursiva, mantemos o controle do horário de chegada em cada aeroporto.  

Isso produz o conjunto de todos os aeroportos acessíveis, juntamente com o número necessário e conjunto de voos.

Esta consulta pode ajudar os usuários a explorar todos os destinos acessíveis considerando as restrições de horário, apoiando aplicações como planejamento de viagens, roteamento de pacotes ou logística de transporte.

No exemplo anterior, definimos os nomes das colunas na WITH RECURSIVE ... AS (...) cláusula. Aqui, nós os definimos na consulta âncora em vez disso. Ambas as abordagens são válidas em CTEs recursivas no Databricks.

Percorrer dados semi-estruturados e não estruturados: Encontrar todos os funcionários armazenados em um arquivo JSON

Sistemas tradicionais geralmente armazenam dados hierárquicos em tabelas rígidas e normalizadas. Enquanto isso, aplicações modernas geram frequentemente hierarquias flexíveis em JSON/XML. A combinação da Databricks de CTEs recursivas com o VARIANT tipo permite que você migre esses padrões de dados de maneira contínua, permitindo que você consulte tanto dados normalizados tradicionais quanto estruturas flexíveis JSON/XML em um único sistema.

Neste exemplo, nos é dada uma hierarquia de empresa (relativamente pequena). Mas em vez de uma tabela totalmente estruturada, nos é dada na forma de um JSON:

Suponha que queremos uma lista de todos os funcionários e seus títulos em uma tabela. Os campos das pessoas na empresa não seguem o mesmo esquema: alguns têm subordinados diretos, enquanto outros não; alguns têm sua localização, alguns não! Com o uso do VARIANT datatype no Databricks, todas as suas necessidades comuns podem ser usadas dentro de um CTE recursivo para explorar completamente a estrutura aninhada do JSON, enquanto suas diferenças podem ser ignoradas.

O caso base da recursão são os dados JSON completos do funcionário raiz, que inclui uma lista de seus subordinados. Em cada passo recursivo, a consulta processa os dados de cada subordinado e repete o processo até chegar a um funcionário sem subordinados.

Aqui está a consulta recursiva para este exemplo:

Embora todos os CTEs aqui estejam sob um WITH RECURSIVE bloco, apenas aquele com recursão real é tratado como recursivo. O Databricks é inteligente o suficiente para detectar quais precisam de recursão - mesmo que você marque todos!

A saída da consulta:

Como começar

Comece com CTEs recursivas lendo a documentação. 

Para saber mais sobre o Databricks SQL, visite nosso site ou leia a documentação. Você também pode conferir o tutorial do produto para Databricks SQL. Se você deseja migrar seu armazém existente para um armazém de dados sem servidor, de alto desempenho, com uma ótima experiência do usuário e custo total mais baixo, então o Databricks SQL é a solução — experimente gratuitamente.

(This blog post has been translated using AI-powered tools) Original Post