Processamento de consultas recursivas em SQL++ (N1QL)

É extremamente provável que você tenha se deparado com problemas com pesquisas hierárquicas ou passagem de gráficos em seu aplicativo como um desenvolvedor que lida com casos de uso do mundo real. E, por motivos óbvios, você prefere resolvê-los no nível camada de banco de dados e não no lado do cliente.

O que são pesquisas hierárquicas?

A pesquisa hierárquica refere-se ao processo de pesquisa e recuperação de dados de uma estrutura hierárquica, como um ou uma relação pai-filho. Envolve a navegação pelos níveis ou camadas da hierarquia para localizar elementos de dados específicos ou informações relacionadas, por exemplo, organogramas, sistemas de arquivos e árvores de categorias.

Os gráficos são basicamente árvores que podem ter ciclos, portanto, além disso, também poderíamos cobrir casos de uso como localização de caminhos etc., desde que tenhamos configurações adicionais para lidar com ciclos.

Considere um exemplo usando uma coleção de funcionários com uma estrutura hierárquica. Os funcionários são organizados em uma relação gerente-subordinado. Realizaremos operações nessa hierarquia de funcionários usando o Couchbase e UDFs JavaScript para demonstrar a solução.

Indo do Coleção de funcionários que podemos recuperar:

i) nível de funcionário genérico:

ii) hierarquia para cada funcionário:

O que este blog aborda

Você realmente gosta do flexibilidade de bancos de dados NoSQL, e seu modelo de dados subjacente é JSON, e é por isso que você escolheu o Couchbase para suas necessidades de banco de dados (ótima escolha, aliás :-) ).

Agora que você tem o Couchbase como banco de dados, quer resolver o problema mencionado acima. Depois de fazer algumas pesquisas no SQL++ Documentos de referência de idioma você chegou à conclusão de que não há Declaração ou Função no SQL++ para resolver nosso problema, como o graphLookup API no MongoDB ou CTE recursivo em bancos de dados SQL.

Mas Com a infraestrutura existente, os usuários podem emular o CTE recursivo usando UDFs de JavaScript. As seções a seguir demonstrarão como fazer isso e muito mais!

Solução para consultas recursivas

Para implementar essa solução, precisamos criar um UDF em JavaScript que utilize um algoritmo de pesquisa de amplitude em primeiro lugar para percorrer a hierarquia de funcionários. O UDF recebe um consulta de âncora, a consulta recursivae opções de configuração como parâmetros. A consulta âncora recupera o conjunto inicial de funcionários, enquanto a consulta recursiva faz referência aos resultados da iteração anterior usando o parâmetro $1 parâmetro. As opções de configuração permitem a personalização, incluindo critérios de saída antecipada, argumentos para consultas internas, detecção de ciclo, modo de explicação para planejamento de consultas e opções de registro.

Como usar as consultas internas

A consulta de âncora obtém o documentos de nível raiz da coleção de destino.

A consulta recursiva pode usar um JUNTAR com um lado como $1 e a outra como a coleção de destino, para explorar a relação pai-filho e executar a passagem de um nível para outro.

Em nossa função, $1 faz o que o alias do CTE faz no CTE recursivo.

Para entender melhor como usamos o SQL++ (N1QL) em UDFs JavaScript, dê uma olhada em De N1QL para JavaScript e vice-versa.

Agora vamos examinar o processo passo a passo. Em primeiro lugar, O processamento recursivo de consultas realmente precisa ser uma função recursiva?

Não, essa seria uma má escolha, considerando a complexidade de tempo (a mesma funcionalidade em uma abordagem recursiva pode ser exponencial, mas linear em uma abordagem iterativa) e todos os outros problemas, como quadros, etc., que vêm junto com a recursão. Se estiver interessado, dê uma olhada no seguinte artigo sobre a conversão de funções de recursão de cauda em iterativas. Além disso, as UDFs JS têm uma predefinição profundidade máxima de recursão de 128; isso não é preferível para o que estamos tentando fazer.

Para simplificar, qualquer tarefa iterativa faz o seguinte:

Gostaríamos de reconhecer isso artigo por sua abordagem.

Pontos a ponderar

Como podemos manter o expressão de estado e passá-lo como um parâmetro para a consulta (parte recursiva aqui)?

• • Usamos a capacidade do SQL++ de passar parâmetros de consulta dinâmicos e usar consultas parametrizadas para manter o valor do estado em todos os níveis.
  • Usamos $1 na cláusula recursiva para fazer referência aos resultados das iterações anteriores, ou seja, a expressão de estado

Como podemos otimizar?

• • Prepare inicialmente as consultas âncora e recursivas para que possamos reutilizar os planos de consulta no momento da execução.
  • Encontre uma maneira de examinar o plano de consulta (semelhante a um EXPLICAR ) para que possamos gerar índices apropriados para instruções internas (âncoras e recursivas).

A visão geral de alto nível da implementação é a seguinte:

Código para CTE recursivo usando JS UDFs 

Essa é uma implementação muito ingênua para começar a funcionar. Configuramos o Breadth-First-Search estabelecendo o fluxo da ordem de acordo com a visão geral acima. Aqui está o código, de este Gist link:

Como adicionar a função

Aqui está um link para adicionar isso a uma biblioteca JavaScript (por exemplo "mylibrary" (minha biblioteca)) em seu serviço de consulta e um link para criar o UDF a partir da biblioteca adicionada.

Crie o UDF a partir da biblioteca usando o SQL++:

Uso

Argumento 1: Âncora consulta, string
Argumento 2: Recursivo consulta, string (usa $1 (expressão de estado) para se referir aos resultados da iteração anterior)
Argumento 3: Configuração opções, objeto

Todos são obrigatóriomas você pode passar um objeto vazio, ou seja, {} se não estiver usando nenhuma opção de configuração.

Opções de configuração

Saída antecipada

levelLimit(1 a N) - Especifica o nível em que podemos parar. A contagem de níveis começa em 0 para obter resultados de ancoragem, 1 para a primeira iteração/nível, 2 para o segundo, etc.

Argumentos para a consulta interna

anchorArgs - como nomeado, por exemplo,{"arg:1} ou argumentos posicionais, por exemplo, [1] para usar na consulta de âncora.

recursiveArgs - Só podem ser argumentos posicionais, e 0 deve ser reservado para a expressão de estado. Por exemplo: [0, 1] - sempre define o 0º índice ($1 arg) para 0para que possamos usá-lo na cláusula recursiva como expressão de estado.

Detecção de ciclo

cycleFields - Matriz de nomes de campos, por exemplo, ["_from", "_to"]

Explicar

explicar - Verificar o plano de consulta nos logs (sets registro por padrão)

Registros

registro - Exibir registros que ajudam quando se deseja registrar durante o desenvolvimento

Consultas de amostra

1. Contar números de 1 a N - exemplo mais simples de uma consulta que mostra como o CTE recursivo funciona.
    
Faça uma breve pausa para pensar em como você poderia fazer isso no N1QL sem a função que desenvolvemos agora. Isso é possível? (Ou será que acabamos de fazer N1QL Turing completo!)

2. Exemplo de funcionários - referem-se à coleção de funcionários que vimos anteriormente.

A. Encontre o nível organizacional de um funcionário

B. Por funcionário se reporta à hierarquia

Consulta de âncora:

Isso pode parecer muito detalhado ao adicionar um ONDE predicado da consulta externa. We pode usar o anchorArgs opção para mitigar isso!

Argumentos (anchorArgs/recursiveArgs)

Explicar

Fornecer {"explain": true} - Você pode ver o plano de consulta no campo de registro 

Saída antecipada

Conjunto levelLimit como {"levelLimit":2} - se você quiser apenas 2 níveis de recursão

Detecção de ciclo

Sem detecção de ciclo a consulta para encontrar a hierarquia de nível de funcionário será inserida em um loop infinito e travar no tempo limite da função, desperdiçando recursos da CPU.

Considerando que, quando trabalhamos com dados de gráficos, é muito provável que tenhamos ciclos presentes, e é o responsabilidade do chamador da função de definir a detecção de ciclo dos campos apropriados.

Aqui, o "employee_id", "manager_id"  é suficiente para sair no ciclo.

Obrigado por acompanhar esse tópico desafiador. Esperamos que ele revele mais opções para alguns de seus desafios de consulta recursiva!

Referências

• • Converter funções recursivas em funções iterativas (Baeldung)
  • Documentos de referência da linguagem SQL++
  • Referência do graphLookup do MongoDB
  • Referência de CTE recursivo Snowflake
  • Blog: De N1QL para JavaScript e vice-versa - Parte 1
  • Referência de consulta do PostgreSQL, cláusula WITH
  • Documentos de consultas e resultados do SQL++
  • Criando uma biblioteca JavaScript docs
  • Criação de funções definidas pelo usuário (UDF) no Couchbase