Uma construção de dados que aparece com frequência em aplicativos de negócios é a estrutura de dados hierárquica. A hierarquia captura a relação pai-filho, muitas vezes entre o mesmo objeto. Por exemplo, a estrutura de uma empresa capta a linha de reporte entre os funcionários. A organização empresarial captura o relacionamento entre as empresas controladoras e as subsidiárias. Hierarquias de território em vendas. Livro de contas em aplicativos financeiros.
Devido à natureza de autorreferência da hierarquia, consultar a estrutura de forma eficiente, juntamente com os dados associados, pode ser um desafio para o RDBMS, especialmente do ponto de vista do desempenho. Neste artigo, discutirei como o RDBMS tradicional lida com consultas hierárquicas. Os desafios com os quais ele tem de lidar e como esse problema pode ser tratado de forma semelhante com o Couchbase N1QL e o Couchbase GSI.
Estrutura hierárquica em aplicativos
O principal motivo para reunir informações em uma estrutura hierárquica é melhorar a compreensão das informações. A estrutura de relatórios da empresa é projetada não apenas para ajudar na forma como a organização é gerenciada, mas também para fornecer uma estrutura para medir e otimizar a eficácia de cada grupo. Em uma grande organização, o desempenho de vendas é geralmente avaliado, não apenas em nível individual, mas também em nível de equipe de vendas. Em resumo, as empresas organizam as informações em uma estrutura hierárquica para que possam ter uma melhor compreensão do desempenho dos negócios. Para atingir esse objetivo, as empresas precisam de um meio eficiente de consultar os dados hierárquicos.
Representação da hierarquia da empresa
Embora o modelo de dados do banco de dados seja capaz de capturar com eficiência a estrutura hierárquica, a dificuldade surge quando você precisa consultar os dados hierárquicos e as informações relacionadas.
Considere este requisito: Obter um valor total do pedido de vendas para todos os representantes de vendas que se reportam a ThomasH - VP de vendas
Embora o modelo de dados seja relativamente simples. A natureza hierárquica da organização de vendas sugere uma estrutura dinâmica inerente na hierarquia de relatórios. AjayW, que lidera o território da Região 1, também gerencia diretamente os membros da equipe de vendas. Por outro lado, na Região2, Liz L gerencia dois gerentes, que, por sua vez, gerenciam a equipe de vendas. Isso é típico na maioria das hierarquias de dados de aplicativos.
Abordagem RDMBS
Para consultar dados hierárquicos, os RDBMS mais estabelecidos, como Oracle e SQLSever, fornecem a construção CONNECT BY / START WITH para permitir que uma única consulta percorra recursivamente a estrutura hierárquica de funcionários.
|
1 2 3 4 5 6 7 8 |
Consulta: Vendas pedidos gerado por membros que relatório para cima para ThomasH-Vendas VP</forte></extensão> SELECIONAR e.EmpID, e.Nome, e.ID do gerente, soma(o.orderVal) DE funcionário e INNER JUNTAR ordem de venda o ON o.EmpID = o.EmpId INICIAR COM EmpID = 101 CONECTAR BY ANTERIOR EmpID = ID do gerente GRUPO BY e.EmpID, e.Nome, e.ID do gerente; |
Embora a consulta acima possa parecer simples, é difícil melhorar o desempenho da consulta com índices devido à natureza recursiva da implementação do CONNECT BY. Por esse motivo, essa técnica não é popular no desenvolvimento de aplicativos para sistemas com grande volume de dados. Em vez disso, os aplicativos corporativos dependem de uma estrutura de objeto pré-reformulada para obter um desempenho de consulta mais previsível. A técnica de hierarquia achatada é descrita na seção N1QL do Couchbase abaixo.
Couchbase N1QL
Para obter o melhor desempenho de consulta, os aplicativos N1QL devem usar a estrutura de hierarquia achatada. A abordagem oferece um desempenho mais previsível, além de aproveitar melhor a GSI do Couchbase. O diagrama abaixo mostra um exemplo da transformação de achatamento de uma estrutura hierárquica de autorreferência, como o objeto employee. Também incluí um trecho de código python abaixo que você pode usar para achatar a estrutura hierárquica. A estrutura achatar_hierarquia usa um objeto JSON hierárquico de autorreferência e gera dois novos objetos no mesmo espaço de chave, mas com valores de tipo diferentes
- O camada_objeto A estrutura funciona com consultas de BI agregadas, em que os resultados da consulta podem ser acumulados para cada nível.
- O nível_do_objeto fornece o mesmo resultado que a cláusula CONNECT BY/START WITH, que retorna todos os objetos filhos de um determinado nó. Esse é o objeto que usaremos em nossa consulta N1QL para fornecer a solução da consulta.
|
1 2 3 4 5 6 7 8 9 10 |
N1QL Consulta: Vendas pedidos gerado por membros que relatório para cima para ThomasH-Vendas VP SELECIONAR e.id,soma(a.valor) DE crm a INNER JUNTAR ( SELECIONAR uhl.id DE crm uhl ONDE uhl.tipo ='_employee_hier_level' E uhl.pai='101') e USO HASH(sonda) ON a.proprietário = e.id ONDE a.tipo='sales_order' GRUPO BY e.id |
Índice GS recomendado:
|
1 |
CRIAR ÍNDICE `crm_employee_hier_level` ON `crm`(`pai`) ONDE (`tipo` = "_employee_hier_level") |
Observações:
- A consulta principal recupera todos os pedidos de vendas no
crmbalde com valor de tipo = 'salesorder' - A consulta executa um HASH JOIN com outra consulta (recurso N1QL 6.5) que recupera todos os IDs de funcionários que se reportam ao user101, ou seja, ThomasH-SalesVP
- Os índices de cobertura adicionais também podem melhorar o desempenho da consulta
- A consulta usa o N1QL 6.5 ANSI JOIN Support for Expression and Subquery Term
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
# Código Python para nivelar um documento JSON no bucket do Couchbase de couchbase.agrupamento importação Aglomerado de couchbase.agrupamento importação PasswordAuthenticator de couchbase.exceções importação NotFoundError de couchbase.balde importação Balde de couchbase.n1ql importação N1QLQuery def achatar_hierarquia(cb,nome do balde,src_doc_type,num_hier_level,node_id_col, col_id_pai): # Exemplo: flatten_hierarchy(cb,args.bucket,'user',4,'id','managerid') # cb - alça de balde de couchbase # bucketname - nome do compartimento para os documentos de origem e de destino # src_doc_type - o valor do tipo do documento de origem # num_hier_level - especifica o número de níveis necessários. Deve ser a profundidade máxima da hierarquia # node_id - o nome do campo no documento para o ID do nó principal # parent_node_id - o campo pai # gen_doc_type = '_'+src_doc_type+'_hier' se (num_hier_level > 1): qstr_ins = 'INSERT into '+nome do balde+' (KEY UUID(), VALUE ndoc) ' qstr_sel = 'SELECT { "type":"'+gen_doc_type+'"' para i em alcance(1,num_hier_level+1): qstr_sel += ', "id+str(i)+'":l'+str(i)+'.'+node_id_col qstr_sel += '} ndoc' qstr_sel_one = ' SELECT '+node_id_col+','+col_id_pai+' FROM '+nome do balde+' WHERE type="'+src_doc_type+'"' para i em alcance(1,num_hier_level+1): se (i==1): qstr_sel += ' FROM ('+qstr_sel_one+') l'+str(i) mais: qstr_sel += ' LEFT OUTER JOIN ('+qstr_sel_one qstr_sel += ') l'+str(i)+' ON l'+str(i-1)+'.'+col_id_pai+' = l'+str(i)+'.'+node_id_col tentar: #q = N1QLQuery(qstring) linhas = cb.n1ql_query(qstr_ins+qstr_sel).executar() exceto Exceção como e: impressão("erro de consulta",e) # gerar conexão por se (num_hier_level > 1): qstr_ins = 'INSERT into '+nome do balde+' (KEY UUID(), VALUE ndoc) ' qstr_sel = 'SELECT {"id":ll.child, "parent":ll.parent, "level":ll.level , "type":"'+gen_doc_type+'_level" } ndoc FROM (' para i em alcance(1,num_hier_level): se (i>1): qstr_sel += 'UNION ALL ' qstr_sel += 'SELECT id'+str(i+1)+' pai, id1 filho,'+str(i)+' nível FROM '+nome do balde+' WHERE type="'+gen_doc_type+'" e id'+str(i+1)+' NÃO É NULO ' qstr_sel += ') ll' tentar: linhas = cb.n1ql_query(qstr_ins+qstr_sel).executar() exceto Exceção como e: impressão("erro de consulta",e) retorno |
Recursos
- Baixar: Faça o download do Couchbase Server 6.5
- Documentação: Couchbase Server 6.5 O que há de novo
- Todos os blogs 6.5
Gostaríamos muito de saber se você gostou dos recursos da versão 6.5 e como ela beneficiará sua empresa no futuro. Compartilhe seu feedback por meio dos comentários ou na seção fórum.
A consulta N1QL com INNER JOIN não funciona mais no CB6 conforme escrito.
[
{
"code": 3000,
"msg": "ANSI JOIN deve ser feito em um espaço de chave. - at where \n Erro ao analisar: erro de tempo de execução: endereço de memória inválido ou desreferência de ponteiro nulo - at where",
"query_from_user": "SELECT uhl.id FROM test uhl \r\nINNER JOIN (SELECT e.id,sum(a.
valor)\r\nFROM test a\r\n WHERE a.type='sales_order' ) e ON a.owner = e.id\r\nwhere uhl.type ='_employee_hier_level'\r\n AND uhl.parent='101'\r\nGROUP BY e.id"}
]
Além disso, a palavra-chave
valorprecisa sercitadoou erro de sintaxe. Algo comoSELECT e.id,sum(a.
valor), (SELECT uhl.id FROM test uhl WHERE uhl.type ='_employee_hier_level'AND uhl.parent='101′) e
DE teste a
WHERE a.type='sales_order' and a.owner = e.id
GROUP BY e.id
funciona, mas não tenho certeza sobre o USE HASH...
A consulta usa um novo recurso do N1QL 6.5 - Suporte ANSI JOIN para Expression Term e Subquery Term. A versão 6.5 está planejada para entrar em beta no final de maio. Se estiver interessado em uma versão inicial para experimentar, entre em contato comigo. Também estou procurando uma maneira de disponibilizar o conjunto de dados.
Obrigado,
-binh
Sugerir alguns dados JSON de amostra também seria bom
Bem, o Oracle ATP e o MS SQL Server também oferecem uma função de hierarquia e um tipo de dados de hierarquia especial. Você poderia esclarecer as diferenças em relação ao CouchBase? O ANSI SQL e/ou o CouchBase adicionam essas palavras-chave do SQL: "CONNECT BY" e "LEVEL"? TIA.
Oi CM27,
A consulta do Couchbase não é compatível com a palavra-chave "CONNECT BY". O recurso de passagem de relacionamento está em nosso plano. Na mesma observação, o CONNECT BY é um recurso específico do Oracle, enquanto o CTE recursivo é a abordagem ANSI para consulta de relacionamento.
-binh