Full-text search-Busca textual completa.
Hoje os clientes querem ferramentas amigáveis e, que ofereçam uma forma simples de resgatar dados, eles não querem mais decorar códigos. O ideal é oferecer um único local de entrada de dados, como um campo livre, onde o cliente coloca tudo o que deseja, e a aplicação retorna os conteúdos ordenado por aquilo que é mais relevante, e que a resposta seja imediata. Para transpor estes desafios, os principais SGBDs oferecem recursos capazes de apoiar nesta tarefa. É sobre isso que vamos tratar neste documento.
Escopo do trabalho !
Uma introdução do Full-text search(FTS) para os bancos: MS Sqlserver, Postgresql e Oracle.
Qual a motivação para usar o FTS?
Principalmente para melhorar o tempo de resposta das buscas textuais.
A busca textual no banco de dados pode se tornar um problema, dependendo do volume de dados, o que pode afetar o tempo de resposta das consultas. A forma mais comum de realizar uma busca em um campo texto é usando o comando LIKE. O Like é bastante comum a qualquer DB relacional e permite buscar conteúdo exato de texto, realiza a busca de forma sequencial, analisando cada parte do texto, tornando-se custoso, pois consume muito recurso e não é rápido. Mas, e se o conteúdo desejado não for exato, ou, se estiver em pontos separados do texto, ou se não souber exatamente qual é o conteúdo que deseja resgatar. Geralmente, quando se tem uma aplicação que requer muita busca textual, uma boa iniciativa é optar por ferramentas especializadas para tal, por exemplo: Apache Solr, Elasticsearch, Lucene, etc. Antes de seguir neste caminho, vale a pena avaliar se o teu banco de dados não oferece uma solução viável, que lhe de respostas satisfatórias para as buscas textuais, evitando ter mais um produto instalado e, que vai requerer atenção e recurso para mantê-lo. Nosso objetivo é introduzir a busca textual e, que fique claro que há muito a ser explorado, como dicionários (vários idiomas), sinônimos, e palavras irrelevantes (stopwords), por exemplo.
Entendendo um pouco mais.
Podemos dizer que o FTS possui (ou é) um mecanismo de indexação invertida dos dados. Praticamente qualquer dado dentro do banco é apto a ser indexado. As próprias palavras do texto, passam a ser as chaves do índice, e possuem o apontamento de onde elas estão (localização). Para agilizar e economizar espaço, o processo de indexação omite as palavras irrelevantes (stopwords), como por exemplo: artigos (a, o, uma, um,..), pronomes (algum, outro, tudo,..) e preposições (de, aquele, nela,..). Dependendo da característica da aplicação, pode haver controle de relevância, proximidade, parte da palavra, semelhança, sinônimos, etc. Uma implementação de FTS pode ser simples ou bastante complexa, podendo incluir dicionários de idiomas distintos, quando se tratar de uma aplicação que vai alcançar línguas diferentes. Os dicionários podem ir sendo incrementados com palavras conforme o jeito que o operador escreve, a exemplo de quando uma aplicação retorna uma busca textual e apesenta algo como: 'você quis dizer ...'.
MS SQLSERVER
Uma vez o ambiente do SQLServer esteja pronto para o FTS, a tarefa de preparar uma tabela/exibição para as buscas textuais é simples, conforme apresentado na sequência:
Figura 1: Iniciando com o FTS no SQLServer.
Um índice único deverá ser obrigatoriamente escolhido.
Figura 2: Escolhendo um índice único.
As colunas qualificadas para o FTS serão apresentadas. Marcar aquelas desejadas.
Figura 3: Selecionando coluna(s) para o FTS.
Neste ponto será definido a forma de atualização dos dados no FTS.
Figura 4: Selecionando o controle de atualização.
O catálogo tem um papel importante no FTS do SqlServer, poderá criar ou usa um já existente.
Figura 5: Definido o Catálogo.
Uma agenda poderá ser definida de acordo com a estratégia de atualização. A agenda pode ser realizada por uma tabela ou por um catálogo.
Figura 6: Definindo uma agenda.
Figura 7: Resumo da configuração do FTS para uma tabela do SQLServer.
Figura 8: Catálogo e índice do FTS criados.
Uma tabela/exibição do SQLServer poderá ter apenas um índice FTS, porém, campos do tipo permitido, podem ser adicionados e retirados de um índice existente. O catálogo pode ser criado separado e antes da criação do índice, em Armazenamento / Catálogo de texto completo (FTS). Um catálogo poderá conter mais de um índice de FTS.
Para executar as consultas FTS no SQLServer, existe dois predicados, CONTAINS e FREETEXT e duas funções, CONTAINSTABLE e FREETEXTTABLE.
Predicados de FTS são usados na cláusula WHERE da consulta, enquanto as funções retornam tabelas, e portanto, são usadas na cláusula FROM da consulta.
Se uma coluna do tipo varbinary(max), varbinary ou xml tiver um índice FTS, ela poderá ser consultada usando os predicados, CONTAINS e FREETEXT e as funções CONTAINSTABLE e FREETEXTTABLE, como qualquer outra coluna indexada FTS do tipo char, varchar, text, etc.
Vamos a pratica.
-Colunas dsc e tags (colunas indexadas FTS) que contenha a palavra ‘carne’.
select id,titulo from tb_abacate
where CONTAINS ((dsc, tags), 'carne');
-Qualquer coluna indexada FTS que contenha a palavra ‘pimenta’.
select id,titulo from tb_abacate
where CONTAINS ((*), 'pimenta');
-Que contenha 'pimenta' e 'carne ou frango'
select id,titulo from tb_abacate
where CONTAINS ((*), '"pimenta" AND carne OR frango');
-Que tenha ‘tomate’ e classificar peso 1 para ‘pimenta’ e 2 para ‘coentro’.
select id,titulo, dsc from tb_abacate
WHERE CONTAINS(dsc, 'ISABOUT ("tomate*", pimenta WEIGHT(0.1), coentro WEIGHT(0.2))')
-Que tenha ‘carne e coentro’, próximas em até 5 palavras
select id,titulo from tb_abacate
where CONTAINS (dsc, 'NEAR ((carne, coentro), 5)')
-Que tenha 'carne e coentro', nesta ordem, próximas em até 10 palavras.
select id,titulo from tb_abacate
where CONTAINS (dsc, 'NEAR ((coentro, carne), 10, TRUE)')
O predicado FREETEXT, não fornece muitas variações e vem por padrão com pesquisa flexional (Iniciar, Iniciado, Iniciando) e de dicionário de sinônimos (dúvida, incerteza). A sintaxe para usar FREETEXT é exatamente a mesma que CONTAINS.
-Que contenha ‘pimenta e/ou coentro’
select id,titulo from tb_abacate
where FREETEXT ((*), 'pimenta coentro');
-Que contenha ‘pimenta e carne ou frango’
select id,titulo,dsc from tb_abacate
where FREETEXT ((*), '"pimenta" AND carne OR frango');
Agora tratar um pouco de funções:
Sempre que você usa funções de FTS, a tabela de resultados retorna uma coluna-chave (Key) que tem a chave única na qual o índice de FTS é definido e uma coluna de Classificação (Rank), que especifica o 'rank' relativo ao resultado. Assim como nos predicados, no Sqlserver temos duas funções de FTS: CONTAINSTABLE e FREETEXTTABLE, semelhantes aos predicados CONTAINS e FREETEXT, respectivamente.
-Retoma o rank e as colunas das linhas que tiver correspondência das palavras.
SELECT key_tbl.rank, titulo, dsc FROM tb_abacate ab
INNER JOIN
FREETEXTTABLE(tb_abacate, dsc,
'creme leite tomate molho') AS KEY_TBL
ON ab.id = KEY_TBL.[KEY]
ORDER BY RANK;
-Retorna os registros que tiver as palavras e aplica o peso definido no ‘WEIGHT’.
SELECT key_tbl.rank, dsc FROM tb_abacate ab
INNER JOIN
FREETEXTTABLE(tb_abacate, dsc,
'ISABOUT ("frango" WEIGHT(1),"cebola" WEIGHT(0.6),"leite" WEIGHT(0.3)) ') AS KEY_TBL
ON ab.id = KEY_TBL.[KEY]
ORDER BY RANK;
-Usando AND (&) e OR (!).
SELECT key_tbl.rank, titulo, dsc FROM tb_abacate ab
INNER JOIN
FREETEXTTABLE(tb_abacate, dsc, '"pimenta" AND carne OR frango') AS KEY_TBL
ON ab.id = KEY_TBL.[KEY]
ORDER BY RANK;
-Usando o WEIGHT para colocar peso para as palavras.
SELECT key_tbl.rank, dsc FROM tb_abacate ab
INNER JOIN
CONTAINSTABLE(tb_abacate, dsc,
'ISABOUT ("molho" WEIGHT(1),"frango" WEIGHT(0.6),"leite" WEIGHT(0.3)) ') AS KEY_TBL
ON ab.id = KEY_TBL.[KEY]
ORDER BY RANK;
-Busca Infleccional (inicio, inicial, iniciado..)
SELECT key_tbl.rank, titulo, dsc FROM tb_abacate ab
INNER JOIN
CONTAINSTABLE(tb_abacate, dsc,
'FORMSOF(INFLECTIONAL,inicio)') AS KEY_TBL
ON ab.id = KEY_TBL.[KEY]
ORDER BY RANK;
-Palavras próximas.
SELECT key_tbl.rank, titulo, dsc FROM tb_abacate ab
INNER JOIN
CONTAINSTABLE(tb_abacate, dsc,
'NEAR(recheado, frango, porco)') AS KEY_TBL
ON ab.id = KEY_TBL.[KEY]
ORDER BY RANK;
-Usando AND (&) e OR (!).
SELECT key_tbl.rank, titulo, dsc FROM tb_abacate ab
INNER JOIN
CONTAINSTABLE(tb_abacate, dsc, '"pimenta" AND carne OR frango') AS KEY_TBL
ON ab.id = KEY_TBL.[KEY]
ORDER BY RANK;
Relembrando que a diferença entre as duas funções é que CONTAINSTABLE permite executar vários tipos de consultas separadamente, enquanto FREETEXTTABLE por padrão faz uma pesquisa em várias formas, ou seja, inclui pesquisa flexional e também pesquisa de dicionário de sinônimos.
O processo para preparar o MS Sqlserver para uso do FTS é simples e, pode ser visto no nosso blog, nesta publicação: http://tech.azi.com.br/utilizando-full-text-search-para-deixar-suas-consultas/.
POSTGRESQL
No PostgreSQL para usar o FTS, não requer nenhuma instalação adicional, desde a versão 8.3.
O PostgreSQL tem duas funções que praticamente resolve todos os problemas.
- A função “to_tsvector” é para criar uma lista de tokens (o tipo de dados tsvector, onde ‘ts’ significa "pesquisa de texto");
- A função “to_tsquery” é para consultar (lista) o vetor em busca de ocorrências de certas palavras ou frases.
Por exemplo, para criar um vetor para a frase 'ao preservar a natureza, se garante a sobrevivência das novas gerações.', podemos fazer o seguinte:
SELECT to_tsvector('ao preservar a natureza, se garante a sobrevivência das novas gerações.');
Retorna: 'garant':6 'geraçõ':11 'natur':4 'nov':10 'preserv':2 'sobrevivent':8
Vai retornar um vetor em que cada token é um lexema (unidade de significado lexical) com ponteiros (as posições no documento), e onde palavras que carregam pouco significado (irrelevantes, stopwords), como artigos e conjunções (ao, a, se de, etc) são omitidas, como já citado anteriormente.
A função to_tsquery(), aceita uma lista de palavras que serão verificadas em relação ao vetor normalizado com o qual criamos, to_tsvector(), para isso usamos o operador ‘@@’, que será mostrado na sequência.
Vamos aos exemplos:
-Busca a palavra 'peso' na frase, se encontrar retorna verdadeiro.
select to_tsvector('O Abacate é um aliado na perda de peso') @@ to_tsquery('peso');
-Busca por 'pimenta' na coluna 'dsc' de uma tabela.
select to_tsvector(dsc) @@ to_tsquery ('pimenta') from tb_abacate;
-Exemplifica os dois usos de tsquery.
select 'aliado'::tsquery, to_tsquery('aliado');
-Negando palavra 'peso', retorna falso.
select to_tsvector('O Abacate é aliado na perda de peso') @@ to_tsquery('!peso');
-Encontra palavra iniciando com 'abac'.
select to_tsvector('O Abacate é aliado na perda de peso') @@ to_tsquery('abac:*');
-Concatena (titulo, dsc e tags) os lexemas no campo 'doc' e apresenta as linhas que tiver 'leite e pimenta'
SELECT a_id, a_titulo from
(
SELECT tb_abacate.id as a_id,
tb_abacate.titulo as a_titulo,
to_tsvector(tb_abacate.titulo) || ' ' ||
to_tsvector(tb_abacate.dsc) || ' ' ||
to_tsvector(tb_abacate.tags) as doc
FROM tb_abacate
GROUP BY tb_abacate.id,tb_abacate.titulo
) a_search
WHERE a_search.doc @@ to_tsquery('leite & pimenta');
-Um pouco de dicionário.
ALTER TABLE tb_abacate ADD linguagem text NOT NULL DEFAULT('portuguese');
-Compara resultado do tsvector usando três dicionários, da coluna da tabela(portuguese), 'english' e 'simple'.
SELECT to_tsvector(tb_abacate.linguagem::regconfig, tb_abacate.titulo) PT, to_tsvector('english',tb_abacate.titulo) EN,
to_tsvector('simple',tb_abacate.titulo) SM
FROM tb_abacate
-Combinando dicionários, coluna 'linguagem' da tabela com o 'simple'
SELECT
to_tsvector(tb_abacate.linguagem::regconfig, tb_abacate.titulo) || ' ' ||
to_tsvector(tb_abacate.linguagem::regconfig, tb_abacate.dsc) || ' ' ||
to_tsvector('simple', tb_abacate.tags)
FROM tb_abacate
GROUP BY tb_abacate.id,tb_abacate.titulo
-Tira os acentos do resultado da consulta
SELECT unaccent(dsc) as dsc_sem_acento from tb_abacate;
-Dando peso/relevância as colunas.
SELECT a_id, a_titulo, a_dsc from
(
SELECT tb_abacate.id as a_id,
tb_abacate.titulo as a_titulo,
tb_abacate.DSC as a_dsc,
setweight(to_tsvector(tb_abacate.titulo), 'A') ||
setweight(to_tsvector(tb_abacate.dsc), 'C') ||
setweight(to_tsvector(tb_abacate.tags), 'B') as doc
FROM tb_abacate
GROUP BY tb_abacate.id,tb_abacate.titulo
) a_search
WHERE a_search.doc @@ to_tsquery('leite | pimenta | tomate')
ORDER BY ts_rank(a_search.doc, to_tsquery('leite | pimenta | tomate')) DESC;
-Relevância (rank) maior que zero e ordenada.
SELECT ts_rank(to_tsvector(dsc), to_tsquery('"coentro" & leite | pimenta')) as relavancia, titulo FROM tb_abacate
WHERE ts_rank(to_tsvector(dsc), to_tsquery('"coentro" & leite | pimenta')) > 0
ORDER BY ts_rank(to_tsvector(dsc), to_tsquery('"coentro" & leite | pimenta')) DESC;
Otimizando as consultas FTS
-Cria um visão materializada
CREATE MATERIALIZED VIEW mv_abacate AS
SELECT tb_abacate.id as a_id,
tb_abacate.titulo as a_titulo,
tb_abacate.DSC as a_dsc,
setweight(to_tsvector(tb_abacate.titulo), 'A') ||
setweight(to_tsvector(tb_abacate.dsc), 'C') ||
setweight(to_tsvector(tb_abacate.tags), 'B') as doc
FROM tb_abacate
GROUP BY tb_abacate.id,tb_abacate.titulo;
-Cria um índice do tipo GIN
CREATE INDEX idx_fts_abacate ON mv_abacate USING gin(doc);
O Postgres dispõe de vários tipos de índice. O tipo GIN são bons para tratar valores de indexação de matrizes, sendo muito usado em FTS.
-Consulta otimizada com o índice
SELECT a_id, a_titulo
FROM mv_abacate
WHERE mv_abacate.doc @@ to_tsquery('leite | pimenta | tomate')
ORDER BY ts_rank(mv_abacate.doc, to_tsquery('leite | pimenta | tomate')) DESC;
-Exemplo de similaridade. O valor 1 é 100% compatível.
select similarity('sabao', 'sabao'); - Resulta 1
select similarity('sabao', 'sapao'); - Resulta 0,333
select similarity('sabao', 'gaivota'); - Resulta 0
-Apresenta as estatísticas dos lexemas em um vetor FTS usando ts_stat: palavra(lexema), número de documentos e quantidade de ocorrências.
select ts_stat ('select to_tsvector(dsc) from tb_abacate');
-Adicionando a coluna 'doc' do tipo 'tsvector' na tabela.
ALTER TABLE tb_abacate ADD COLUMN doc TSVECTOR;
-Coluna 'doc' recebendo os lexemas de 'titulo, doc e tags'.
UPDATE tb_abacate SET doc = to_tsvector(titulo||' '||dsc||' '||tags);
-Criação de índice para a coluna ‘doc’.
CREATE INDEX idx_doc_vetor_abac ON tb_abacate USING GIN (doc);
-Apresenta uma explanação da execução da consulta.
EXPLAIN ANALYZE SELECT titulo FROM tb_abacate WHERE doc @@ plainto_tsquery('pimenta leite');
-Classificando as colunas. São 4 níveis, podem variar em: D C B A.
UPDATE tb_abacate SET doc = setweight(to_tsvector(coalesce(titulo,'')),'A') ||
setweight(to_tsvector(coalesce(tags,'')),'B') ||
setweight(to_tsvector(coalesce(dsc,'')),'C');
-Após a classificação, junto aos lexemas, além das posições, agora há os pesos, em letras.
SELECT doc FROM tb_abacate WHERE doc @@ to_tsquery('avocado');
Ex.: 'abacat':5A,11C 'abert':32C 'avoc':1A,8B
-Ciando uma função customizada, que alimenta coluna 'doc' com lexemas classificados.
CREATE OR REPLACE FUNCTION fc_doc_abac_upd()
RETURNS trigger AS $$
begin
new.doc := setweight(to_tsvector(coalesce(new.titulo,'')),'A') ||
setweight(to_tsvector(coalesce(new.tags,'')),'B') ||
setweight(to_tsvector(coalesce(new.dsc,'')),'C');
return new;
end $$
LANGUAGE plpgsql;
-Uma trigger pode ser criada para automatizar a atualização dos dados na tabela.
-Destacando resultado na busca.
SELECT ts_headline(dsc,to_tsquery('pimenta'),'StartSel = , StopSel =')
FROM tb_abacate WHERE doc @@ to_tsquery('coentro');
O Postgres oferece ainda uma variedade de outros recursos, que não caberia neste momento, pois estamos tratando de uma visão geral, introdução.
Para saber mais sobre visão materializada, recomendo a leitura da publicação do nosso blog no endereço: http://tech.azi.com.br/visoes-materializadas/
ORACLE:
O Oracle text possui recursos semelhantes aos já apresentados nos bancos tratados anteriormente, por exemplo: possibilita encontrar palavras por proximidade, ordenar resultados por relevância, dicionários, sinônimos, stoplist, etc. O Oracle Text já está pronto para uso para busca textual, não requerendo nenhuma instalação adicional.
O Oracle text possui três tipos de índice para operar com texto, são: context, ctxcat e ctxrule. Deve-se criar um índice apropriado para emitir as consultas CONTAINS, CATSEARCH ou MATCHES na clausula WHERE.
-O CONTEXT é um índice em uma coluna de texto. Use esse índice com o operador CONTAINS na cláusula WHERE de uma instrução SELECT.
-O CTXCAT é um índice combinado em uma coluna de texto e uma ou mais outras colunas. Para esse índice use o operador CATSEARCH na cláusula WHERE de uma instrução SELECT. Este tipo de índice é otimizado para consultas mistas.
-O CTXRULE é um índice em uma coluna contendo um conjunto de consultas. Use esse índice com o operador MATCHES na cláusula WHERE de uma instrução SELECT.
Um índice Oracle text oferece vários parâmetros que podem ser incluídos em sua criação, dependendo da necessidade.
Neste documento serão abordados buscas simples do índice tipo CONTEXT.
-Criando índice CONTEXT para as colunas dsc e tags da tabela.
CREATE INDEX idx_ctxsys_dsc on tb_abacate (dsc) indextype is ctxsys.context;
CREATE INDEX idx_ctxsys_tags on tb_abacate (tags) indextype is ctxsys.context;
-Exemplos de consultas simples:
-Busca por carne e frango até 5 palavras de distância, nesta ordem.
SELECT * FROM TB_ABACATE WHERE CONTAINS(dsc,'near((carne, frango),5, TRUE)') > 0;
-Busca por frango e carne até 3 palavras de distância.
SELECT * FROM TB_ABACATE WHERE CONTAINS(dsc,'near((frango, carne), 3)') > 0;
-Busca por frango ou carne e coentro.
SELECT * FROM TB_ABACATE WHERE CONTAINS(dsc,'"frango" or ("carne" and "coentro")',1) > 0;
-Busca por coentro e frango ou carne.
SELECT * FROM TB_ABACATE WHERE CONTAINS(dsc,'"coentro" and ("frango" | "carne")',1) > 0;
-Busca por texto exato em qualquer ponto do campo.
SELECT * FROM TB_ABACATE WHERE CONTAINS(dsc,'recheado com carne',1) > 0;
-Busca por texto começando com inici%, apresenta o score de cada linha retornada.
SELECT score(1), a.* FROM TB_ABACATE a WHERE CONTAINS(tags,'inici%',1) > 0 order by 1 desc;
-Busca por texto com coentro ou recheado, criado o score para cada linha.
SELECT score(1), a.* FROM TB_ABACATE a WHERE CONTAINS(dsc,'coentro or recheado',1) > 0 order by 1 desc;
-Busca texto que contenha coentro e carne.
SELECT * FROM TB_ABACATE WHERE CONTAINS(dsc,'"coentro" & "carne"') > 0;
-Busca texto que tenha coentro e não tenha frango (MINUS).
SELECT * FROM TB_ABACATE WHERE CONTAINS(dsc,'coentro - frango') > 0;
Finalizando por aqui.
Espero que tenha gostado da nossa jornada pelo mundo do Full-text search ou Busca textual completa.
Esta é uma análise inicial do tema, creio que conforme iniciarmos o uso destes recursos nas aplicações, muitos cenários se abrirão a nossa frente e, o FTS é muito rico em possibilidades.
Referencias:
https://docs.microsoft.com/pt-br/sql/relational-databases/search/full-text-search?view=sql-server-ver15
https://www.postgresql.org/docs/9.5/textsearch.html
https://docs.oracle.com/cd/A91202_01/901_doc/text.901/a90121/csql5.htm#CCREF0105
Inscreva-se no { .aztech }
Receba as últimas postagens enviadas diretamente para sua caixa de entrada
