Modelos de gráficos de consulta com CTE recursivo do Couchbase

As expressões de tabela comum (CTEs) recursivas e o CONNECT BY do Oracle são construções SQL bem conhecidas entre os usuários de RDBMS, facilitando a delegação da exploração de estruturas de dados complexas e interdependentes à camada de banco de dados para aumentar a eficiência do processamento.

Essas construções são essenciais para a consulta de estruturas de dados interdependentes, um requisito comum em vários setores, incluindo finanças, gerenciamento da cadeia de suprimentos, gerenciamento de relacionamento com o cliente (CRM), reservas de viagens e, mais recentemente, redes sociais. Reconhecendo sua importância, todos os principais sistemas de gerenciamento de bancos de dados relacionais (RDBMS), como PostgreSQL, MySQL (a partir da versão 8.0), SQL Server, Oracle e SQLite, oferecem suporte a CTEs recursivos.

Por outro lado, os bancos de dados NoSQL, que são projetados para gerenciar uma série diversificada de modelos de dados, como documentos, valores-chave, colunas amplas e dados de gráficos, priorizam a escalabilidade, a alta disponibilidade, a flexibilidade e o desempenho em sistemas distribuídos. Nesses ambientes, o conceito de CTE, recursivo ou não, não é abordado diretamente. Os usuários geralmente recorrem a soluções especializadas, como bancos de dados de gráficos - Cypher para Neo4J e AQL para ArangoDB, por exemplo - para lidar com estruturas de dados complexas.

O Couchbase se diferencia com o SQL para JSON, oferecendo uma abordagem exclusiva para CTEs recursivos que também ampliam seu suporte a vários modelos. Este blog se aprofundará em três tópicos principais:

1. 1. Como você pode aproveitar um único DBMS como o Couchbase para estruturas de dados complexas para vários casos de uso, da mesma forma que o RDBMS, mas sem a necessidade de um banco de dados mais dedicado. 
   2. O uso da construção Couchase SQL++ para consultar, transformar e projetar essas relações complexas usando uma construção SQL que é familiar aos usuários de RDBMS.
   3. Práticas recomendadas para gerenciar o consumo de recursos com o CTE recursivo.

Caso de uso da lista de materiais

A lista técnica é um componente essencial na fabricação e na engenharia, detalhando as matérias-primas, peças e componentes necessários para fabricar um produto. Ela geralmente tem uma estrutura hierárquica, em que as peças são compostas de outras peças ou materiais.

Este exemplo incluirá os componentes e subcomponentes básicos de um computador desktop.

Digamos que queremos listar todas as peças e subpeças necessárias para montar um computador desktop, juntamente com as quantidades necessárias para cada uma delas.

Essa consulta inicializa a recursão com o item de nível superior (o computador de mesa) e junta recursivamente a tabela de componentes com ela mesma para percorrer a hierarquia, ajustando as quantidades conforme necessário para cada componente e subcomponente.

O resultado listará todas as peças necessárias para o computador de mesa, incluindo a quantidade de cada uma e seu nível hierárquico, o que pode ser útil para entender a estrutura de montagem ou para fins de inventário e pedido.

Explicação

O CTE começa com o Bicycle (onde ParentPartID é NULL) e, em seguida, procura recursivamente todos os componentes e subcomponentes.

A quantidade é ajustada em cada nível para refletir o número total necessário para uma bicicleta.

A coluna Nível, embora não seja estritamente necessária para todas as análises de lista técnica, ajuda a entender a profundidade de cada peça na hierarquia.

Essa abordagem permite uma análise detalhada de todos os materiais e componentes necessários para a fabricação de um produto, o que é essencial para o gerenciamento de estoque, a estimativa de custos e o planejamento da produção nas operações de fabricação.

Caso de uso de rede social

Uma aplicação comum em um caso de uso de rede social é encontrar os graus de conexão entre dois usuários - essencialmente, como os usuários estão conectados por meio de uma cadeia de amigos em comum. Digamos que precisamos determinar o caminho mais curto (em termos de graus de conexão) entre dois usuários em uma rede social. Isso pode ajudar em recursos como a sugestão de amizades ou a compreensão da dinâmica da rede.

Considere que temos os seguintes usuários e como eles são amigos uns dos outros, por exemplo, Alice é amiga de Bob e também de Charlie. Mas Alice não é amiga de Dana.

Nessa rede expandida, os usuários são conectados de várias maneiras, criando vários caminhos pelos quais os usuários podem se conectar.

Vamos encontrar os graus de conexão entre Alice[1] e Frank[6], incluindo os nomes dos usuários ao longo do caminho.

Explicação

• • Caso base: o CTE começa identificando as conexões diretas de Alice (UserID 1), incluindo nomes de usuários para facilitar a leitura.
  • Etapa recursiva: Em seguida, ele encontra recursivamente os amigos dessas conexões, ampliando a pesquisa e monitorando o grau de conexão. Os JOINs garantem que os nomes de usuário sejam incluídos tanto para o usuário inicial quanto para seus amigos em cada etapa.
  • Terminação e filtragem: A recursão continua até encontrar conexões com Frank (ID de usuário 6). A consulta filtra os caminhos que levam a Frank e ordena os resultados pelo grau de conexão para identificar os caminhos mais curtos.

Esta consulta demonstra como rastrear e enumerar caminhos em uma rede social, incluindo nomes de usuários para maior clareza. Ela fornece uma base para análises mais complexas, como a identificação de todas as conexões mútuas ou a exploração de estruturas de rede.

Caso de uso de travessia de rede de gráficos

Para este caso de uso, usarei uma versão resumida dos dados de rota da America Airlines. Observe que isso não é da amostra de viagens do Couchbase. Neste exemplo, usamos a consulta CTE recursiva SQL++ do Couchbase para encontrar todos os voos de LAX para MAD com menos de 2 paradas nesse conjunto de dados de amostra. Observe que esses dados de amostra não se baseiam na amostra de viagemmas uma versão simplificada das rotas da AA para 2008. 

Explicação

• • A rotaCTE começa com todos os voos que partem do LAX.
  • A parte recursiva do routeCTE procura voos que se conectem a partir da última parada na rota atual para outros aeroportos, evitando rotas que retornem ao LAX.
  • A matriz de rota acumula a sequência de códigos de aeroporto para mostrar o caminho percorrido.
  • A consulta gera todas as rotas que terminam em MAD, detalhando os caminhos encontrados.

Práticas recomendadas CTE recursivo

Ao usar CTEs (Common Table Expressions, expressões de tabela comum) recursivas no SQL++, os desenvolvedores devem estar cientes das implicações da natureza recursiva e do custo do processamento da consulta. Aqui estão as práticas recomendadas:

Definir limites para a profundidade da recursão - Sempre defina um limite para a profundidade da recursão para evitar loops infinitos e consumo excessivo de recursos. Use um contador ou uma condição dentro do CTE recursivo para controlar a profundidade da recursão; inclua os limites das opções.

Monitorar o desempenho - Os CTEs recursivos podem consumir muitos recursos. Monitore o desempenho de perto, especialmente em consultas longas ou complexas, e otimize-as conforme necessário. Isso pode envolver a indexação ou a quebra de CTEs excessivamente complexos.

Evite complexidades desnecessárias - Mantenha a lógica dentro da parte recursiva do CTE o mais simples possível. Condições ou cálculos excessivamente complexos podem prejudicar significativamente o desempenho. Verifique se a condição JOIN está correta.

Garantir a estrutura correta dos dados - Verifique se seus dados estão estruturados corretamente para a recursão. Dados incorretos ou malformados podem levar a resultados incorretos ou a consultas ineficientes.

Teste extensivamente - Teste exaustivamente os CTEs recursivos com vários conjuntos de dados, inclusive casos extremos. Isso ajuda a detectar quaisquer problemas com loops infinitos, resultados incorretos ou gargalos de desempenho.

Definir a cota de memória - Defina a cota de memória no nível da solicitação ou do nó para evitar o uso excessivo de memória na consulta recursiva.

Limitações

Os CTEs recursivos são um recurso poderoso do Couchbase SQL++, comumente encontrado em outros RDBMS. Eles permitem a execução de consultas complexas, como percorrer dados hierárquicos e de rede de gráficos ou realizar cálculos iterativos que são difíceis de expressar com o SQL padrão. Entretanto, há limitações e considerações que devem ser levadas em conta ao usar CTEs recursivos. Essas limitações geralmente estão relacionadas ao desempenho, às restrições de sintaxe e à complexidade das consultas. Aqui estão alguns detalhes específicos:

Agregados: Os CTEs recursivos normalmente não permitem funções de agregação (MIN(), MAX(), SUM(), AVG() etc.) ou DISTINCT na parte recursiva do CTE. Essas operações não fazem sentido no contexto da adição recursiva de linhas porque implicam em um conjunto de resultados finais após a resolução de toda a recursão.

Funções da janela: Assim como as funções agregadas, as funções de janela (ROW_NUMBER(), RANK() etc.) geralmente não são usadas na parte recursiva do CTE. Elas são destinadas ao uso em um conjunto de linhas retornadas pela consulta, o que as torna adequadas para o termo não recursivo ou em uma consulta que seleciona a partir do CTE recursivo.

LIMITE / PEDIDO POR: Essas cláusulas não são permitidas no membro recursivo do CTE. O raciocínio é que elas se referem à ordenação do conjunto de resultados finais e não fazem sentido no contexto da construção do conjunto recursivo, em que os resultados intermediários são construídos cumulativamente em cada iteração.

Próximas etapas

• • Saiba mais sobre Couchbase SQL++
  • Mais exemplos de CTE no blog: Processamento de consultas recursivas em SQL++ (N1QL)
  • Comece com um Avaliação gratuita do Couchbase Capella