SQLite sem like, mas dando match
Usando full text search (FTS5) no sqlite3, ao invés de like
Ilustração de Annie Ruygt
O problema sou eu
Tenho dados estáticos e quero fazer buscas por trechos de palavras. Não é uma base gigantesca, não estamos falando de big data.
Uma base simples, do Código Brasileiro de Ocupação, o famoso CBO, que classifica as profissões, nas bases de dados do governo federal.
Para preencher formulários, com busca incremental, é necessária uma API que retorne rápido bons resultados para entradas incrementais. Outra questão, é ter em um banco de dados de pessoas, uma certa padronização das funções em que trabalham, para facilitar buscas e dar mais sentido a agregações.
Para essa quantidade de dados, um like até que resolve. Mas então fico pensando como não é ecologicamente correto fazer uma busca dessas no banco inteiro toda vez que se recebe uma nova string.
O sqlite3 (qualquer servidor de dados que entenda SQL) é rápido o suficiente pra fazer essas buscas, mas com o modo esbanjador de recursos ativado.
Pegando os arquivos
No site do Ministério do Trabalho temos um seção dedicada ao CBO, em que se pode pegar um arquivo zip com os dados. Precisamos do arquivo "Estrutura CBO CSV".
Dentro dele, teremos algo assim:
CBO2002 - Familia.csv
CBO2002 - Grande Grupo.csv
CBO2002 - Ocupacao.csv
CBO2002 - PerfilOcupacional.csv
CBO2002 - Sinonimo.csv
CBO2002 - SubGrupo.csv
CBO2002 - SubGrupo Principal.csv
Os arquivos tem esse nome horrível e conteúdo em ISO-8859-1:
CBO2002 - Familia.csv: ISO-8859 text, with CRLF, LF line terminators
CBO2002 - Grande Grupo.csv: ISO-8859 text, with CRLF, LF line terminators
CBO2002 - Ocupacao.csv: ISO-8859 text, with CRLF, LF line terminators
CBO2002 - PerfilOcupacional.csv: ISO-8859 text, with CRLF, LF line terminators
CBO2002 - Sinonimo.csv: ISO-8859 text, with CRLF, LF line terminators
CBO2002 - SubGrupo.csv: ISO-8859 text, with CRLF, LF line terminators
CBO2002 - SubGrupo Principal.csv: ISO-8859 text, with CRLF, LF line terminators
Pra facilitar, fiz um tratamento nos dados com esse script:
from pathlib import Path
PREFIX = 'CBO2002 - '
p = Path('.')
for file in p.glob(f'{PREFIX}*.csv'):
_, new_name = file.name.split(PREFIX)
new_name = new_name.replace(' ', '_')
new_name = new_name.lower()
print(file.name, '->', new_name)
with file.open('r', encoding='ISO-8859-1') as f:
data = f.read()
with open(new_name, 'w', encoding='UTF-8') as f:
f.write(data)
Resultando nesses arquivos:
familia.csv
grande_grupo.csv
ocupacao.csv
perfilocupacional.csv
sinonimo.csv
subgrupo.csv
subgrupo_principal.csv
Que, para nossa alegria, agora estão em UTF-8:
familia.csv: Unicode text, UTF-8 text
grande_grupo.csv: Unicode text, UTF-8 text
ocupacao.csv: Unicode text, UTF-8 text
perfilocupacional.csv: Unicode text, UTF-8 text
sinonimo.csv: Unicode text, UTF-8 text
subgrupo.csv: Unicode text, UTF-8 text
subgrupo_principal.csv: Unicode text, UTF-8 text
Recurso especial no SQLite3
Assim como em outras engines, o SQLite3 fornece uma extensão que provê o recurso de fazer buscas por trechos de palavras, em uma estrutura preprocessada (Full Text Search), indexada (Reverse Index) de forma a tornar essa busca mais eficiente: SQLite3 FTS5.
Primeira providência é saber se temos essa extensão:
$ sqlite3 cbo.db 'PRAGMA compile_options;' | grep -i fts
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
Oba, sem problemas aqui. Caso queira testar e não encontre essa opção (difícil, pois a partir da versão 3.9.0, de 2015, vem por padrão), basta seguir as instruções de compilação.
Usando, enfim, o SQLite3
Os arquivos que interessam aqui são os que de ocupações e sinônimos, que são exatamente isso que dizem :).
Para poder fazer as pesquisas, precisamos carregar os dados de uma forma especial. Será criada uma tabela virtual para as pesquisas, e a engine cuidará dos pormenores práticos. Mais uma vez, um programa em Python para fazer essa nobre tarefa:
import sqlite3
import unicodedata
con = sqlite3.connect('cbo.db')
curr = con.cursor()
def ingest(filename, tipo):
with open('ocupacao.csv') as f:
for line in f:
data = line.replace('\n', '')
cbo, cargo_acentuado = data.split(';')
cargo = unicodedata.normalize('NFD', cargo_acentuado)
cargo = cargo.encode('ascii', 'ignore')
print(cbo, cargo_acentuado, cargo)
curr.execute('INSERT INTO ocupacoes (cbo, cargo, cargo_acentuado, tipo) VALUES (?,?,?,?)', (cbo, cargo, cargo_acentuado, tipo))
con.commit()
curr.execute('CREATE VIRTUAL TABLE IF NOT EXISTS ocupacoes USING fts5(cbo UNINDEXED, cargo, cargo_acentuado, tipo UNINDEXED, tokenize=\'trigram\');')
ingest('ocupacao.csv', 'ocupacao')
ingest('sinonimo.csv', 'sinonimo')
O primeiro passo é criar a tabela virtual:
CREATE VIRTUAL TABLE IF NOT EXISTS ocupacoes
USING fts5(
cbo UNINDEXED,
cargo,
cargo_acentuado,
tipo UNINDEXED,
tokenize='trigram');
Perceba os campos marcados como UNINDEXED. Como sugere a notação, eles não estarão disponíveis para pesquisa. Note-se também que os dados são salvos com acentuação, sem acentuação. Isso é para pesquisar sem se preocupar com acentuação, e ainda poder voltar os resultados escritos de forma correta.
Uma parte fundamental é o parâmetro tokenize, que recebe o valor trigram, responsável por nos permitir buscas a partir de três caracteres. Outras opções permitem apenas busca por palavras inteiras.
Feito isso, insiramos os dados, como costumeiro.
Eu tinha em mente subir os dados na tabela virtual e em outra tabela, para poder comparar o uso de recursos, mas temos os dados acessíveis em uma tabela real:
sqlite3 cbo.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> .tables
ocupacoes ocupacoes_content ocupacoes_docsize
ocupacoes_config ocupacoes_data ocupacoes_idx
sqlite> .schema ocupacoes_content
CREATE TABLE IF NOT EXISTS 'ocupacoes_content'(id INTEGER PRIMARY KEY, c0, c1, c2, c3);
sqlite> SELECT * FROM ocupacoes_content LIMIT 10;
1|CODIGO|TITULO|TITULO|ocupacao
2|010105|Oficial general da aeronautica|Oficial general da aeronáutica|ocupacao
3|010110|Oficial general do exercito|Oficial general do exército|ocupacao
4|010115|Oficial general da marinha|Oficial general da marinha|ocupacao
5|010205|Oficial da aeronautica|Oficial da aeronáutica|ocupacao
6|010210|Oficial do exercito|Oficial do exército|ocupacao
7|010215|Oficial da marinha|Oficial da marinha|ocupacao
8|010305|Praca da aeronautica|Praça da aeronáutica|ocupacao
9|010310|Praca do exercito|Praça do exército|ocupacao
10|010315|Praca da marinha|Praça da marinha|ocupacao
A primeira linha da resposta é uma vergonha. Usando o módulo CSV do Python, isso seria facilmente resolvido.
Comparando as formas de pesquisa
Uma pesquisa normal com like (procurando "desenvolvedor de sistemas"), poderia ser algo assim (credo, não acredito que escrevi esse query):
sqlite> SELECT c0, c1, c2 FROM ocupacoes_content WHERE c1 LIKE '%desen%sist%' ORDER BY c1;
212405|Analista de desenvolvimento de sistemas|Analista de desenvolvimento de sistemas
212405|Analista de desenvolvimento de sistemas|Analista de desenvolvimento de sistemas
317110|Desenvolvedor de sistemas de tecnologia da informacao (tecnico)|Desenvolvedor de sistemas de tecnologia da informação (técnico)
317110|Desenvolvedor de sistemas de tecnologia da informacao (tecnico)|Desenvolvedor de sistemas de tecnologia da informação (técnico)
142510|Gerente de desenvolvimento de sistemas|Gerente de desenvolvimento de sistemas
142510|Gerente de desenvolvimento de sistemas|Gerente de desenvolvimento de sistemas
Já uma busca usando os recursos corretos :), pode ser algo assim:
sqlite> SELECT cbo, tipo, cargo_acentuado, rank FROM ocupacoes WHERE ocupacoes MATCH '{cargo}:desen sist' ORDER BY rank DESC;
317110|ocupacao|Desenvolvedor de sistemas de tecnologia da informação (técnico)|-7.23465736090208
317110|sinonimo|Desenvolvedor de sistemas de tecnologia da informação (técnico)|-7.23465736090208
212405|ocupacao|Analista de desenvolvimento de sistemas|-9.04498744725366
212405|sinonimo|Analista de desenvolvimento de sistemas|-9.04498744725366
142510|ocupacao|Gerente de desenvolvimento de sistemas|-9.1408897903197
142510|sinonimo|Gerente de desenvolvimento de sistemas|-9.1408897903197
Observe que a tabela virtual tem um mágico campo rank.
Medindo
Temos tudo que precisamos: dados e meios de pesquisa diferentes, para medir e comparar. Como a preocupação principal aqui é uso de recursos, optei por uma ferramente muito interessante: perf. Mediremos instruções e condicionais, especialmente, para saber como vão as coisas em termos de processamento.
Os parâmetros para realizar essa medição foram:
perf stat \
--repeat 1000 \
--event task-clock:u,cycles:u,instructions:u,branches:u \
COMANDO_A_MEDIR > /dev/null
O que é redirecionado para /dev/null são as saídas do comando a medir.
Para a busca com LIKE, rolou esse resultado:
perf stat --repeat 1000 --event task-clock:u,cycles:u,instructions:u,branches:u sqlite3 cbo.db "SELECT c0, c1, c2 FROM ocupacoes_content WHERE c1 LIKE '%desen%sist%' ORDER BY c1;" > /dev/null
Performance counter stats for 'sqlite3 cbo.db SELECT c0, c1, c2 FROM ocupacoes_content WHERE c1 LIKE '%desen%sist%' ORDER BY c1;' (1000 runs):
4.025,52 msec task-clock:u # 838,798 CPUs utilized ( +- 0,93% )
4.486.277.785 cycles:u # 2,392 GHz ( +- 0,91% )
7.259.950.764 instructions:u # 3,21 insn per cycle ( +- 0,91% )
1.419.992.765 branches:u # 757,083 M/sec ( +- 0,91% )
0,0047992 +- 0,0000567 seconds time elapsed ( +- 1,18% )
Já para o MATCH, rolou essa resposta:
perf stat --repeat 1000 --event task-clock:u,cycles:u,instructions:u,branches:u sqlite3 cbo.db "SELECT cbo, tipo, cargo_acentuado FROM ocupacoes WHERE ocupacoes MATCH '{cargo}:desen sist' ORDER BY rank DESC;" > /dev/null
Performance counter stats for 'sqlite3 cbo.db SELECT cbo, tipo, cargo_acentuado FROM ocupacoes WHERE ocupacoes MATCH '{cargo}:desen sist' ORDER BY rank DESC;' (1000 runs):
2.732,01 msec task-clock:u # 776,632 CPUs utilized ( +- 0,92% )
2.813.593.248 cycles:u # 2,122 GHz ( +- 0,91% )
3.130.459.757 instructions:u # 2,23 insn per cycle ( +- 0,91% )
594.198.882 branches:u # 448,092 M/sec ( +- 0,91% )
0,0035178 +- 0,0000376 seconds time elapsed ( +- 1,07% )
Observe que os números para o FTS5 são bem melhores, embora nada de extraordinário, mas fico feliz pela possibilidade de uma boa economia de recursos.
Finalizando
Um texto que me ajudou com o perf, foi esse (além do manual - man perf e man perf-stat):
https://pt.linux-console.net/?p=1477
E não poderia deixar de mencionar que esse texto foi feito com ❤️amor❤️ numa madrugada entre sexta e sábado, com aquele silêncio urbano quebrado apenas por algumas músicas de Miles Davis.