Arquivo da categoria: Códigos

OCI NoSQL Tables

Geralmente temos servidores e storage, e então bancos de dados, e claro, tabelas. No entanto, com o avanço das arquiteturas cloud native existe uma outra abordagem: as tabelas sem bancos de dados e sem servidores (tabelas serverless).

Essas tabelas existem como um serviço gerenciado em diversos provedores de cloud, e somente nos provedores de cloud, pois elas são uma consequência das arquiteturas cloud native. Neste post vou escrever sobre o serviço NoSQL Tables, as tabelas Serverless da Oracle Cloud (OCI).

Vou explicar como elas funcionam, para que servem, casos de uso, e também vou mostrar um exemplo prático para ficar mais claro.

Principais Características

Apesar de você não enxergar os servidores, eles existem, claro! Mas o serviço expõe as tabelas com uma abordagem serverless. Do ponto de vista físico, as NoSQL Tables são tabelas que implementam o conceito de sharding, isto é, os dados são distribuídos de forma horizontal em servidores diferentes. Além disso, os dados de cada servidor também são replicados para outros servidores. Essa arquitetura distribuída, além de promover alta disponibilidade e durabilidade, garante que o tempo de resposta seja, sempre de forma estável e previsível, de poucos milisegundos.

Como os dados são distribuídos e replicados em servidores diferentes, você pode escolher se as leituras serão do tipo consistente ou eventual, e se vai utilizar ACID para as transações: para entender melhor este ponto (caso não conheça), veja este meu outro post onde abordo o Teorema CAP.

Há bastante flexibilidade para escolher o formato dos dados: existe suporte para dados em colunas (schema-full), tal como conhecemos no modelo relacional (ex: integer, string timestamp e Blob), mas adiciona tipos complexos, como Arrays, Records e Map. E claro, não menos importante, também suporta JSON nativamente (schema flexível).

Outras capacidades que também ajudam bastante em diversos casos é a possibilidade de configurar TTL (time-to-live, onde os dados são removidos automaticamente após o tempo que você especificar), criação de índices secundários (inclusive com JSON), e também a possibilidade de usar SQL ou API (com suporte a Spring Data).

Como não existe servidor, e se quer existe um banco de dados (pelo menos em termos de usabilidade), como será que esses serviços são dimensionados? Bom, aqui está a grande diferença: pelo workload! Isto é, você dimensiona uma NoSQL Table informando qual é a demanda de uso.

São três variáveis requeridas quando se cria uma NoSQL Table na OCI:

  • Quantidade máxima de leituras por segundo;
  • Quantidade máxima de escritas por segundo;
  • Espaço em Gbytes.

Essas variáveis podem ser alteradas de forma online enquanto a tabela existir (elástico, para cima ou para baixo, a qualquer tempo).

Nota: o billing deste serviço considera essas três variáveis: existe um preço por read unit, outro para write unit, e outro para gbytes utilizados. Em geral é um serviço considerado barato, e faz parte do free tier, mas é importante estimar o custo para o seu workload.

No final do dia NoSQL Tables é um serviço gerenciado, então operações como provisionamento de infraestrutura, backup, patching, configurações de alta disponibilidade e segurança, enfim, tudo aquilo que é necessário para deixá-lo no ar e disponível é gerenciado pela própria OCI.

Casos de Uso e Utilidade

Por ser simples, permitir um schema flexível, com baixa latência previsível, e ser completamente gerenciado, as tabelas serveless podem ser bastante úteis nos casos de uso abaixo e quaisquer outros casos similares a estes:

  • Perfis de usuário;
  • Eventos de qualquer tipo (usuários, clicks, etc) e IoT;
  • Carrinho de compras;
  • Catálogo de produtos;
  • Cache de dados para web servers;
  • Armazenamento de metadados e parâmetros;
  • Personalização de anúncios;
  • Detecção de fraude em tempo real.

Claro que existem também os casos onde elas não são úteis. Vou colocar abaixo os principais casos onde você deve fugir das tabelas serverless:

  • Migrar um banco de dados legado existente as-is (a não ser que você queira fazer um grande refactoring);
  • Necessita de um modelo de dados mais complexo, que demanda por um schema com muitas tabelas e relacionamentos. Se não for possível colocar tudo em poucas tabelas, ou se não for compatível com workloads do tipo JSON, não use;
  • No caso onde os limites do serviço gerenciado são insuficientes para o seu caso de uso. À propósito, isto vale para qualquer serviço gerenciado.

Na Prática

As NoSQL Tables podem ser usadas de diversas formas: pelo console gráfico da OCI, pela CLI (Command Line Interface), e também pelas SDKs nas diversas liguagens suportadas, como Java, Node.js, Python, .Net e Go.

Vou utilizar a CLI para criá-las, e para isso você precisa configurar a OCI CLI previamente.

Nota: no arquivo de configuração da OCI CLI você pode colocar alguns parâmetros que utiliza com frequência para evitar de repeti-los toda vez que faz uma chamada CLI. Nos exemplos a seguir eu fiz isso para o parâmetro compartment_id, que é obrigátorio e é responsável por agrupar logicamente os recursos que você cria na OCI).

Com a CLI configurada, veja abaixo como é simples criar uma tabela serverless na OCI. Primeiro vamos especificar os limites da tabela, criando este arquivo table_limits.json:

{
  "maxReadUnits": 2,
  "maxStorageInGBs": 1,
  "maxWriteUnits": 2
}

Com este arquivo como exemplo, nossa tabela permitirá no máximo 2 leituras (maxReadUnits) e 2 escritas (maxWriteUnits) concorrentes, e até 1 GByte de espaço (maxStorageInGBs).

Depois podemos utilizá-lo no comando abaixo:

oci nosql table create --ddl-statement "CREATE TABLE usuarios (id integer, nome string, PRIMARY KEY (SHARD(id)))" --name usuarios --table-limits file://table_limits.json

A tabela chama-se Usuarios, e coloquei apenas duas colunas: id (integer) e nome (string), onde id é a chave primária, e também é a coluna que distribui os dados horizontalmente (shard).

Se eu quisesse utilizar JSON para a coluna nome, bastava substituir string por json na criação da tabela, ou ainda adicionar mais colunas de outros tipos.

Para listar suas tabelas criadas, você pode executar o seguinte comando:

oci nosql table list

Este comando vai retornar um output JSON com a lista de tabelas e suas propriedades.

Apesar de eu não achar apropriado, podemos também manipular e consultar dados pelo OCI CLI. Este comando abaixo insere um registro:

oci nosql query execute --statement "INSERT INTO usuarios (id, nome) VALUES (1,\"Fernando\")"

É possível consultar a tabela com SQL, utilizando este comando:

oci nosql query execute --statement "SELECT * FROM usuarios"

Para remover a tabela, você pode fazer desta forma:

oci nosql table delete --table-name-or-id usuarios

Bom, utilizar a OCI CLI para criar e remover tabelas tudo bem, mas para manipular dados com certeza é melhor utilizar alguma SDK, pois é através dela que você utilizará as tabelas serverless no seu sistema.

Vou utilizar como exemplo a SDK em Python. Veja aqui os requerimentos para instalar os drivers (biblioteca borneo) no seu sistema.

Abaixo vou fazer uma consulta simples pela chave usando API, mas antes preciso importar a biblioteca com os objetos que usaremos, e também fazer a configuração de conexão e autenticação:

from borneo import NoSQLHandle, NoSQLHandleConfig, Regions, Consistency
from borneo.iam import SignatureProvider

handleConfig = NoSQLHandleConfig(Regions.US_ASHBURN_1)
config = handleConfig.set_authorization_provider(SignatureProvider())
config.set_default_compartment('dev')
config.set_consistency(Consistency.EVENTUAL) # ou Consistency.ABSOLUTE
conn = NoSQLHandle(config)

Importei da biblioteca borneo (que utilizamos para manipular as NoSQL Tables) todos os objetos que vou utilizar nos exemplos seguintes.

config é minha configuração de conexão e autenticação, que recebe a região da OCI que usei para criar a tabela (us_ashburn_1) e meus dados de acesso com SignatureProvider(): esse método retorna minhas credenciais que estão configuradas na minha OCI CLI — há várias outras formas de autentição também, veja aqui.

dev é o compartimento onde criei a tabela, e Consistency.EVENTUAL configuro as leituras para serem eventuais (Availability + Partition Tolerance, do Teorema CAP), mas poderia configurar como Consistency.Absolute (Strong Consistency + Partition Tolerance, do Teorema CAP).

O objeto conn (criado a partir de config) é utilizado para as demais operações sobre a tabela.

Bom, vamos então para a consulta simples pela chave usando API:


from borneo import GetRequest

request = GetRequest().set_table_name('usuarios')
request.set_key({'id': 1}) 
result = conn.get(request) 
if result.get_value() is not None:
    print(result)   

Primeiro informei no objeto request qual é o nome da minha tabela, e depois consigo consultá-la informando qual é a chave de busca pelo método set_key. O objeto result recebe o resultado da consulta.

O código abaixo faz a mesma coisa, porém utiliza SQL, e Prepared Statements:

from borneo import QueryRequest, PrepareRequest

sql = 'declare $myKey integer; select * from usuarios where id = $myKey'
request = PrepareRequest().set_statement(sql)
prepStmt = conn.prepare(request).get_prepared_statement()
prepStmt.set_variable('$myKey', 1)
queryRequest = QueryRequest().set_prepared_statement(prepStmt)
while True:
    result = conn.query(queryRequest)
    resultList = result.get_results()
    if queryRequest.is_done():
        break

print(resultList)

PreparedStatements servem para otimizar a execução das instruções SQL em um banco de dados, de forma que faça apenas um parse e múltiplas execuções (ao invés de fazer um parse para cada execução).

Inicialmente criei uma string SQL utilizando $mykey como variável. Depois preparei o statement, e depois fiz a operação de bind, que é colocar o valor na variável $mykey. Por fim a query é executada e o resultado é atribuído ao objeto resultList.

E quanto as transações? Certo! É possível inserir, atualizar e remover dados, normalmente.

Veja abaixo como faço para inserir mais um registro na minha tabela:

from borneo import PutRequest

request = PutRequest().set_table_name('usuarios')
request.set_value({'id': 2, 'nome': 'Paulo'})
result = handle.put(request)
if result.get_version() is not None:
   print('success')

Basta criar um request utilizando o objeto PutRequest, atribuir o valor, e executar com put. Também é possível fazer com SQL, e também é possível fazer várias mudanças com uma simples operação (WriteMultipleRequest).

Neste post eu fiz um overview bem resumido do que é possível fazer com as tabelas serverless. Em outros posts vou mostrar outras capacidades deste serviço. Stay tuned!

Conclusão

As NoSQL Tables são mais robustas do que parecem. Você pode criar tabelas serverless com múltiplos formatos, incluindo JSON e SQL (com ACID e opção de escolha entre consistência forte ou eventual), índices secundários e TTL. É muito fácil de começar, e não há muito o que aprender, pois o serviço é totalmente gerenciado.

Referências

Você pode me seguir neste blog:

Disclaimer: As opiniões expressas neste blog são minhas, e não são necessariamente as mesmas opiniões do meu empregador.


Análise Market Basket

Não sou nenhum Vanderbilt, mas esse trem que vou mostrar faz fumaça!


Estou começando um hobby nesse negócio de câmeras fotográficas, e comprei na internet uma Canon T7i [me falaram que era boa], com uma lente adicional e um flash.

Aí o site vem e me oferece um tal de Difusor Rebatedor Universal, porque quem comprou aquele flash, também comprou esse difusor. What the heck isso faz? Não tenho a mínima… mas era tão barato que [já que tô aqui…] comprei, pois fiquei com peso na consciência de não comprar algo – por míseros reais – que poderá me quebrar um galho depois.

Aproveitei e comprei também um cartão de memória, uma bolsa e um tripé – tudo na base do “quem comprou isso, também comprou aquilo”. 

No final, eles conseguiram me tirar alguns reais além do que eu havia planejado. Se bobear esse difusor estava encalhado no estoque, e eles conseguiram desovar [comigo!]. O lado positivo é que tudo que comprei valeu a pena, e acabei qualificando a compra com cinco estrelas.

Todo mundo saiu feliz.


O fato é que eu gastei mais dinheiro, e eles me venderam produtos que, é importante registrar, EU NUNCA TERIA COMPRADO SE NÃO FOSSEM AS RECOMENDAÇÕES DO SITE.

O site usou um modelo de machine learning, e um dos mais simples! Você também consegue implementá-lo com poucas linhas de código. Há variações e outras abordagens, claro, mas vou lhe mostrar uma delas, bem popular, porém bastante eficiente.

Figura 1 – Exemplo de recomendação de produtos em tempo real

Esse modelo não é somente para recomendação de produtos em tempo real no mundo online. Pode ser usado também para previnir incêndios em florestas, descobrir uma pré-disposição a diabetes, detectar reações adversas a drogas, verificar a efetividade de um treinamento de corrida de rua, analisar estudantes admitidos em universidades, só para citar alguns.

Mas a maioria das aplicações deste modelo tem a ver com vendas mesmo. O caso de uso mais comum no mundo offline é o Market Basket, e pode gerar as seguintes ações para aumentar as vendas: colocar dois produtos na mesma prateleira, então quem comprar um item poderá comprar o outro; descontos promocionais poderiam ser aplicados somente a um dos itens; anúncios e propagandas para um produto podem direcionar a compra de outro produto; dois produtos poderiam ser combinamos em uma única oferta. Essas foram só para citar algumas.

Enfim, o que você puder imaginar nesse sentido de promover uma venda que outrora não ocorreria.

Neste artigo vou utilizar dados reais de um ponto de venda no comércio [uma padaria]. Vou carregar esses dados em um banco de dados, e executar o modelo de machine learning dentro deste mesmo banco de dados. Depois vou construir uma aplicação simples em Python que faz recomendações em tempo real, usando as regras geradas por esse modelo. O modelo de machine learning que usaremos é do tipo Associação de Regras, e o algoritmo utilizado chama-se Apriori. Você poderá reproduzí-lo integralmente com os códigos que vou deixar aqui neste artigo.

Mas há um problema [você realmente achou que ia ser fácil?!]. Esse modelo, apesar de simples, é compute intensive. Isto é, quanto mais dados MELHOR, porém, quanto mais dados MAIS LENTO: eu tenho uma solução para esse problema, e vou comentar sobre isso mais pra frente.

Os Dados de uma Padaria

A padaria não é igual àquela do seu bairro! É uma bakery très chic, crème de la crème, da Europa. Por isso os nomes dos itens vendidos não estão em português.

Fiz o download dos dados de transações desta bakery no site Kaggle: este link lhe coloca direto na página.

O Kaggle, se você não conhece, disponibiliza dados reais de empresas para competições de machine learning e data science. É muito útil pra aprender e treinar.

O arquivo chama-se BreadBasket_DMS.csv. Ele é bem simples e correto, o que nos poupa de fazer data wrangling: são 4 colunas – date, time, transaction e item.

Transaction é um código sequêncial que agrupa os itens vendidos. Por exemplo, se a primeira venda teve apenas um item, este recebe o código 1. Se a segunda venda teve três itens, as próximas três linhas recebem o código 2. Isto é, cada transaction representa a venda de um conjunto de itens, a cestinha de compras de uma pessoa:

Figura 2 – Dataset da Bakery obtido no Kaggle

Em nosso caso, o arquivo já está preparado para o processamento do modelo. Quando aplicar ao seu caso, aos seus dados, você deverá organizá-los de forma semelhante: agrupe itens relacionados, e atribua um código para o modelo saber que alguma vez esses itens ocorreram juntos.

O próximo passo é carregar os dados no banco de dados. Eu vou usar o Oracle XE. Podemos criar uma tabela externa, e através dela carregar os dados para uma tabela dentro do banco:


-- Tabela que armazenaremos as transações
CREATE TABLE transactions
(DT_DIA VARCHAR2(10), DT_HORA VARCHAR2(8), TRANS_ID NUMBER(38), ITEM VARCHAR2(60));

-- Local em disco onde está o arquivo BreadBasket_DMS.csv
CREATE DIRECTORY my_directory AS '/tmp';

-- Tabela externa para carregar as transações
CREATE TABLE transactions_ext
( DT_DIA VARCHAR2(10), DT_HORA VARCHAR2(8), TRANS_ID NUMBER(38), ITEM VARCHAR2(60) )
ORGANIZATION EXTERNAL ( 
TYPE ORACLE_LOADER DEFAULT DIRECTORY my_directory
ACCESS PARAMETERS 
(RECORDS DELIMITED BY '\r\n' 
SKIP 1  FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' AND '"' LRTRIM  )
LOCATION ('BreadBasket_DMS.csv')
);

-- Carga da tabela externa para a tabela de transações
INSERT INTO transactions 
SELECT * FROM transactions_ext ;

COMMIT;

OK, então criei uma tabela chamada transactions, e depois criei um diretório chamado my_directory, que é a forma de falar para o Oracle em qual caminho está nosso arquivo que baixei do Kaggle.

Criei também uma tabela externa chamada transactions_ext, que serve apenas para configurar [delimitação, nome do arquivo, etc] a carga do arquivo para a tabela transactions. No final, o comando insert select coloca os dados da tabela externa, ou seja, do arquivo, na tabela transactions.

Agora vou preparar o algoritmo Apriori. Há dois requisitos:

[1] Criar uma view chamada trans_view com a coluna que agrupa as transações [trans_id], e a coluna com o nome dos itens [item]. Daqui pra frente, vamos utilizar a view ao invés da tabela transactions.

[2] Criar uma tabela de parâmetros para calibrar o algoritmo. Perceba que o nome da tabela de parâmetros pode ser qualquer um, mas o nome das colunas e os datatypes devem ser iguais aos que coloquei no script abaixo.

-- Criamos uma view com o grupo de transações e os itens
CREATE VIEW trans_view AS SELECT trans_id, item FROM transactions;

-- Criamos uma tabela para armazenar os parâmetros do algoritmo
CREATE TABLE parametros
(setting_name VARCHAR2(30), setting_value VARCHAR2(4000));

-- Inserimos os parâmetros na tabela
begin 
 insert into parametros values (DBMS_DATA_MINING.ASSO_MAX_RULE_LENGTH, 3);
 insert into parametros values (DBMS_DATA_MINING.ASSO_MIN_CONFIDENCE, 0.01);
 insert into parametros values (DBMS_DATA_MINING.ASSO_MIN_SUPPORT, 0.01);
 insert into parametros values (DBMS_DATA_MINING.ODMS_ITEM_ID_COLUMN_NAME, 'ITEM');
 commit;
end;
/

É importante entender que o algoritmo Apriori gera regras. Por exemplo, vamos supor que se alguém comprar pão e café, muito provavelmente também comprará manteiga. A parte antecedente, a regra, é “pão e café”, e a parte consequente, aquela que o algoritmo calcula, é a manteiga.

O algoritmo Apriori gera uma lista de regras, cada uma com sua relevância, e também as respectivas consequências, isto é, a associação correspondente [o que ele recomenda]. Para isso existe também uma série de parâmetros que precisam ser calibrados para o algoritmo não gerar muitas regras inúteis [o modelo não considera as regras que não estão de acordo com esses parâmetros]. Alguns desses parâmetros nós inserimos na tabela de parâmetros:

O parâmetro MAX_RULE_LENGTH determina qual é o tamanho da maior regra [até quantos itens ela poderá ter], por exemplo, a compra de “um pão” é uma regra com 1 item, a compra de “um pão e um café” tem 2 itens, e a compra de “um pão, um café e um bolo” tem 3 itens. MIN_CONFIDENCE é o porcentual mínimo de confiança da regra, isto é, são as chances de alguém que comprou pão também comprar café. MIN_SUPPORT determina a popularidade mínima dos itens da regra no conjunto total de dados. Por exemplo, se a regra “pão e café” participou de 10 transações num total de 100, o suporte desta regra é 10%. ITEM_ID_COLUMN_NAME é o nome da coluna que corresponde aos itens.

Eu utilizei 3 para max rule length, e 1% para min confidence e support. Isso significa que qualquer regra que não estiver de acordo com esses parâmetros, não será considerada no conjunto de regras que o modelo vai gerar.

1% é um valor baixo – normalmente você poderia utilizar de 10% a 30% para o mínimo de confiança e suporte. Mas como não temos muitos dados, decidi colocar um valor baixo. Assim eu gero mais regras, e decido qual é a restrição ideal mais pra frente quando embutir as regras na aplicação, como veremos mais adiante.

Obs: com muitos dados é importante não gerar muitas regras, às vezes inúteis [baixa confiança e pouco suporte podem gerar associações irrelevantes]. Esse algoritmo é caro em termos de processamento, como comentei antes.

Para uma explicação mais aprofundada sobre o algoritmo Apriori e seus parâmetros [que não é o objetivo deste artigo], você pode ver neste link.

Dados carregados, parâmetros calibrados. Agora vou criar o modelo. Como já falei, o modelo de machine learning utiliza o algoritmo Apriori para gerar as regras, com base nos dados, e nos parâmetros.


BEGIN 
DBMS_DATA_MINING.CREATE_MODEL(
model_name            => 'MODELO_PADARIA',
mining_function       =>  DBMS_DATA_MINING.ASSOCIATION,
data_table_name       => 'TRANS_VIEW',
case_id_column_name   => 'TRANS_ID',
target_column_name    =>  NULL,
settings_table_name   => 'PARAMETROS');
END;
/

A criação do modelo é feita através de uma API no banco de dados, chamada DBMS_DATA_MINING. Primeiro temos que dar um nome ao modelo [para identificá-lo e utilizá-lo na aplicação] com o atributo MODEL_NAME. Temos que selecionar o algoritmo no atributo MINING_FUNCTION, que em nosso caso é ASSOCIATION [este modelo utiliza o algoritmo Apriori]. No atributo DATA_TABLE_NAME temos que informar o nome da view que criamos anteriormente. No atributo CASE_ID_COLUMN_NAME temos que informar qual é a coluna da view que agrupa as transações [a cesta de compras]. O atributo TARGET_COLUMN_NAME só é usado para modelos supervisionados, e como ASSOCIATION é não-supervisionado podemos deixar nulo. SETTINGS_TABLE_NAME é o nome da tabela de parâmetros que definimos anteriormente.

Bom, agora que o modelo está criado, vamos ver o resultado [as regras criadas]. Uma observação importante é que podemos gravar as regras em uma tabela, ou podemos consultar direto de uma view de resultado, chamada DM$VR + Nome do Modelo, que em nosso caso é DM$VRMODELO_PADARIA, criada automaticamente quando criamos o modelo.

Você pode recriar o modelo com uma peridiocidade, por exemplo, uma vez por dia, ou uma vez por semana, ou mês. Depende muito do caso uso, se os dados tem muita variação ou não.

Vamos ver os resultados. Não se assuste com essa query abaixo. Eu explico.


SELECT list_of_antecedents, consequent, lift, confidence, support 
FROM (
   SELECT ROW_NUMBER() OVER (PARTITION BY rule_id ORDER BY ant_items.piece) as row_number,
          LISTAGG(ant_items.item, ' AND ') WITHIN GROUP (ORDER BY ant_items.piece) OVER (PARTITION BY RULE_ID) as list_of_antecedents,
          consequent,
          support, confidence, lift
   FROM (SELECT rule_id,
                antecedent, 
                consequent_name as consequent, 
                round(to_number(rule_support)*100,4) as support, 
                round(to_number(rule_confidence)*100,4) as confidence, 
                round(to_number(rule_lift),4) as lift
         FROM DM$VRMODELO_PADARIA) rules,
                   XMLTABLE ('/itemset/item'  PASSING rules.ANTECEDENT
                   COLUMNS item varchar2(30)  PATH 'item_name',
                           piece for ordinality ) ant_items
         WHERE lift > 1 AND confidence > 10 AND support > 1
   ORDER BY confidence DESC, support DESC
) WHERE row_number = 1

As linhas mais importantes são as 13 e 17. Na linha 13, temos a view que guarda os resultados da criação do modelo, isto é, onde estão as nossas regras e vários indicadores para cada uma delas [support, confidence, lift, e mais alguns outros]. Na linha 17, eu coloco as restrições, por exemplo, somente as regras com lift maior que 1, confidence maior que 10%, e support maior que 1%.

Normalmente alguns itens muito frequentes [support é maior] participam de regras com itens menos frequentes [support é menor]. Por exemplo: pão aparece em 70 de 100 transações, enquanto que manteiga aparece em 5 de 100. Certamente haverá uma regra pão e manteiga, e outra manteiga e pão. Quando há regras assim com o suporte dos itens tão discrepantes, isso pode acabar inflando o indicador de confiança [no meu exemplo, quando alguém comprar manteiga]. Desta forma, podemos usar um indicador adicional, o Lift, que nos mostra o quão provável é a compra da manteiga se alguém comprar o pão. Se for maior que 1, é provável que compre. Se for 1, é indiferente. E se for menor que 1, é improvável que compre. Por isso na query filtrei lift maior que 1.

A query realmente é o que está entre as linhas 7 e 17. Todo o entorno eu fiz para melhorar o output, e colocá-lo depois na aplicação simples que fiz em Python. Quando uma regra tem mais de um item, a linha se repete na view. Por isso usei LISTAGG e concatenei com um AND para ficar algo como “Bread AND Coffee”. E a função analítica ROW_NUMBER() utilizei para retornar só uma linha, a primeira, pois “Bread AND Coffee” iria retornar duas vezes. Se você executar esse código SQL aos poucos, primeiro o que está entre as linhas 7 e 17, e depois o entorno, vai entender melhor o que estou dizendo.

Esse é o resultado da query:

Figura 3 – Resultado da query no SQL Developer

Em uma aplicação, quando alguém seleciona por exemplo, Toast, você recomenda Coffee. Há 70% de confiança de que essa regra seja verdadeira.

Nota: todos esses códigos até aqui poderiam ter sido feitos no SQL Developer, com a feature Data Miner: é como um wizard que lhe permite implementar os algoritmos de machine learning com drag & drop.

Não Pare! É uma Vergonha Você Chegar até Aqui e Não Colocar esse Código na sua Aplicação

Vamos colocar esse código em uma aplicação simples para simular como seria.

Veja. Esse é só um blog. Não pense que vou usar algum design pattern com altos requintes de polimorfismo, um framework robusto, uma infraestrutura de kubernetes, microserviço, e tal… vai no Python script mode mesmo. Você vai entender!


import sys
import cx_Oracle

# itemsList é uma lista de itens que o usuário informa
# se for mais que um, um AND é incluído para cada par de itens
itemsList = ''
for args in sys.argv[1:]:
    if len(itemsList) == 0:
        itemsList = args
    else:
        itemsList = itemsList + ' AND ' + args

# Conecta no banco de dados Oracle
conn = cx_Oracle.connect("demouser", "demopass", "localhost/XE")
cur = conn.cursor()

# É a vez daquela query que retorna os resultados
# Mas desta vez estou filtrando com itemsList

sql = """SELECT rownum, consequent FROM (
       SELECT ROW_NUMBER() OVER (PARTITION BY rule_id ORDER BY ant_items.piece) as row_number,
          LISTAGG(ant_items.item, ' AND ')
          WITHIN GROUP (ORDER BY ant_items.piece)
             OVER (PARTITION BY RULE_ID) as list_of_antecedents,
          consequent, support, confidence, lift
   FROM (SELECT rule_id,
                antecedent, 
                consequent_name as consequent, 
                round(to_number(rule_support)*100,4) as support, 
                round(to_number(rule_confidence)*100,4) as confidence, 
                round(to_number(rule_lift),4) as lift
         FROM DM$VRANALISE_ASSOCIACAO) rules,
                   XMLTABLE ('/itemset/item'  PASSING rules.ANTECEDENT
                   COLUMNS item varchar2(30)  PATH 'item_name',
                           piece for ordinality ) ant_items
         WHERE lift > 1 AND confidence > 10 AND support > 1
   ORDER BY confidence DESC, support DESC
) WHERE row_number = 1 and list_of_antecedents = :antecedentList"""

# Executa a query e mostra os itens que devemos recomendar
cur.execute(sql, antecedentList=itemsList)
res = cur.fetchall()
print(res)

A aplicação app.py recebe um argumento que é a lista de antecedentes. Poderia ser por exemplo um clique do usuário, ou os produtos que ele coloca num carrinho de compras. A aplicação retornar uma lista de consequentes por ordem de confiança.

Veja alguns exemplos de execução deste código:

Quando o usuário entra com Coffee, a aplicação recomenda Cake. Quando o usuário entra com Bread e Coffee, a aplicação recomenda primeiro Pastry, e depois Cake. Quando o usuário entra com Tea, a aplicação recomenda Cake e depois Sandwich. E quando entra com Cake, a aplicação recomenda primeiro Coffee, depois Tea, e depois Hot Chocolate.

Essa é a vantagem de ter os algoritmos de machine learning dentro do banco de dados. Você aproveita os recursos do servidor do banco, que entrega bastante performance, e também a linguagem SQL, que é muito fácil de usar.

O Erro Mais Caro da Sua Vida É Não Usar Python ou SQL pra Arrumar Toda Aquela Bagunça

Conhecer Python ou SQL para fazer data wrangling é igual gravidez: quanto antes você souber, melhor!


Boa parte do trabalho de um cientista de dados, e de um engenheiro de machine learning, é arrumar os dados. Você precisa organizar tudo antes de começar a explorar, e colocar os algoritmos pra máquina aprender.

Eu conheço SQL há muito tempo, e já fiz cada transformação que até a Álgebra Relacional dúvida!

Não é só a linguagem em si, mas o banco de dados também ajuda. Por exemplo, no Oracle você pode criar colunas virtuais, trocar partes da tabela com dados por partes sem dados, inserir em várias tabelas diferentes ao mesmo tempo em que lê a partir de uma query, utilizar funções PL/SQL que podem executar com paralelismo pipeline, persistir em vários formatos de dados, enfim, é uma miríade de capacidades de transformação, tanto para a performance, como para a funcionalidade.

Mais tarde eu aprendi como fazer essa arrumação com Python – já tendo conhecido outras linguagens, como Java por exemplo. Neste post vou comparar brevemente como é fazer esse trabalho de wrangling com SQL e Python [através de uma biblioteca com essa finalidade].

Definitivamente SQL e Python são as melhores ferramentas não-Nutella [sem o apoio de uma ferramenta de transformação própria pra isso] para limpar e arrumar dados.


Meu primeiro contato com Pandas ocorreu há mais ou menos um ano. Confesso que achei que estava re-construindo a roda. Praticamente tudo que eu fazia com essa biblioteca em Python eu acreditava que conseguia fazer em SQL com muito mais simplicidade. Bom, primeiro eu explico o que é o Pandas.

Pandas é uma biblioteca open-source em Python para análise de dados.

Pandas basicamente é composto por um objeto chamado Dataframe [uma planilha!], e este dispõe de vários métodos com uma boa usabilidade para manipular dados. Minha primeira impressão ao utilizar o dataframe foi a de utilizar o Excel em forma de código, mas com muito mais performance.

Para instalar, como todo pacote Python, basta executar o comando abaixo no sistema operacional [Terminal no Mac ou Linux, e Command Window no Windows]:

pip install pandas

Para utilizar, em um notebook [Jupyter ou Zeppelin] ou em um arquivo Python .py:

import pandas as pd
meuDataFrame = pd.read_csv('arquivo.csv')
meuDataFrame.describe()

O código acima importa o pacote Pandas, carrega um arquivo csv no dataframe meuDataFrame, e faz um describe nos dados. Describe() é fantástico! Ele retorna uma tabela com uma série de dados estatísticos para cada coluna, como count, avg, sum, stddev, min, max e percentis. Essa primeira exploração é bem interessante com Pandas em relação ao SQL. Mas a vantagem do Pandas começa e termina aqui. Quase que todo o resto do trabalho de wrangling [limpeza dos dados] eu achei muito mais simples e mais rápido com SQL, por isso a sensação de estar reinventando a roda.

Limpar e transformar dados sobre grandes volumes requer critério. Cada minuto pensando na estratégia de implementação equivale a horas de processamento.

A manipulação dos dados em si com dataframes consiste em criar dataframes a partir de dataframes, sendo que as cópias vão transformando os dados aos poucos, com colunas novas a mais, ou a menos. Há também agregações [groupby], filtros [query], unicidade [unique], ordenações [sort_values], e merges e joins [este último me lembra…. SQL!]. É necessário registrar também que há uma miríade de funções estatísticas para utilizar que são bem interessantes. 

Em Python com Pandas:

# Adicionar nova coluna somando 10 sobre uma coluna existente
meuDataFrame['nova_coluna'] = meuDataFrame['col1'] + 10

# Somar
meuDataFrame.col1.sum()

# Filtrar coluna maior que 100
meuDataFrame.query('(col1 > 100)')

# Eliminar duplicidade
meuDataFrame.col1.unique()

# Ordenar
meuDataFrame.sort_values(by='col1', ascending=False)

Em SQL:

# Adicionar nova coluna somando 10 sobre uma coluna existente
SELECT a.*, a.col1 + 10 as nova_coluna FROM tab a

# Somar
SELECT SUM(col1) FROM tab

# Filtrar coluna maior que 100
SELECT * FROM tab WHERE col1 > 100

# Eliminar duplicidade
SELECT DISTINCT col1 FROM tab

# Ordenar
SELECT * FROM tab ORDER BY col1 DESC

Pandas tem também uma excelente integração com o pacote Numpy para processamento de arrays e matrizes multidimencionais, e o Matplotlib, para visualização de gráficos

A documentação do Pandas está aqui neste link.

Pandas é facil, mas SQL é mais human-readable, na minha opinião. Com SQL a manipulação de dados é extremamente trivial. Ao invés de criar dataframes a partir de outros dataframes para incluir, transformar ou remover colunas, você faz subquery ou transforma na própria cláusula SELECT. Todas as APIs que você pode imaginar para trabalhar um dado existem nos sistemas gerenciadores de banco de dados que suportam o SQL ANSI. Desde funções descritivas simples, como SUM ou COUNT, até funções mais complexas como as Regressões Lineares, Testes de Hipótese e Análises de Variância. Adicionalmente, existem também as funções analíticas [esta última me lembra… Pandas!] e as de machine learning. Com Pandas, bom, para machine learning, teria que utilizar algum outro pacote [como scikit-learn, por exemplo].

Uma Conversa Franca sobre Não Perder Tempo

No final do dia eu prefiro SQL, mas depende.

Eu prefiro Pandas quando manipulo arquivos que não precisam persistir por muito tempo [análises pontuais] e não precisam de integração com outras fontes de dados, e não precisa de tanta performance.

Por exemplo: costumo fazer análises de infraestrutura com centenas de servidores, cada um com centenas métricas, e depois de tomar as conclusões, apago tudo – criar um banco, criar as tabelas, carregar os dados, e só então explorar os dados leva tempo… é melhor fazer esse tipo de análise com Pandas.

Quando preciso de um acabamento mais enterprise, isto é, algo que vai existir por mais tempo, com grande volume de dados, que vai ser utilizado por outras pessoas, que será reutilizado, compartilhado, que precisa de segurança, e claro, extrema performance e escalabilidade, não há dúvidas que SQL, na minha opinião, é melhor.

Tente conhecer os dois: SQL e Pandas. Use Pandas quando for mais conveniente, do contrário use SQL. Você escala mais conhecendo ambos.

3 Funções Super Poderosas para Transformar Dados no Oracle

Se você já se deparou alguma vez com a necessidade de desnormalizar um relacionamento entre duas ou mais tabelas para criar uma lista de strings, ou criar um mapeamento para popular uma estrutura de grafo ou documento JSON, ou ainda converter do Relacional para JSON, você precisa conhecer as funções LISTAGG, JSON_OBJECT e JSON_ARRAYAGG.

Como exemplo considere as seguintes tabelas:

ALUNO (id, nome)
DISCIPLINA (id, nome)
ALUNO_DISCIPLINA (id_aluno, id_disciplina)

A tabela ALUNO_DISCIPLINA é basicamente a tabela de ligação do muitos-para-muitos entre ALUNO e DISCIPLINA, já que um aluno pode cursar várias disciplinas, e cada disciplina pode ter muitos alunos.

Então a seguinte consulta retorna:

SELECT a.nome AS nome_aluno,
       b.nome AS nome_disciplinas
FROM aluno A,
     disciplina B,
     aluno_disciplina C
WHERE a.id = c.id_aluno
  AND b.id = c.id_disciplina
ORDER BY a.nome, b.nome

Transforma os Valores de Linhas Diferentes em uma String separada por Algum Delimitador

Para desnormalizar, basta agrupar pelas colunas que devemos manter como identificador [no meu exemplo nome do aluno] e depois na função LISTAGG coloque a coluna que queremos desnormalizar e o delimitador [neste caso, vírgula]. WITHIN GROUP permite ordenar o resultado:

SELECT a.nome AS nome_aluno,
       LISTAGG(b.nome, ',') WITHIN GROUP (ORDER BY b.nome) AS lista_disciplinas
FROM aluno A,
     disciplina B,
     aluno_disciplina C
WHERE a.id = c.id_aluno
  AND b.id = c.id_disciplina
GROUP BY a.nome ORDER BY a.nome

Mais simples do que parece, certo?! Se você quiser transformar dados no modelo Relacional para um modelo de Grafos, pode usar LISTAGG para ligar as arestas aos nós com apenas uma query. Há outras variantes do LISTAGG na documentação, neste link. No Oracle ela está disponível a partir da versão 11g, e há similares em outras tecnologias.

Na versão 19c foi incluída a opção DISTINCT no LISTAGG, para o caso de você querer eliminar os valores duplicados que são listados com LISTAGG. Veja este exemplo onde eu projeto o nome do departamento e listo ao lado, separado por vírgula, todos os cargos a partir da tabela de funcionários. Como vários funcionários podem ter o mesmo cargo, ele poderá se repetir na lista. Então com distinct nós eliminamos os valores duplicados:

Console do Oracle Live SQL

Transforma Relacional em JSON, usando Atributos Simples ou com Arrays

Voltando ao exemplo dos estudantes, ao invés de apenas desnormalizar, vamos também converter para JSON, e aninhar as disciplinas, pois quero montar um Documento com os alunos e seus respectivos cursos:

SELECT JSON_OBJECT('id'          VALUE a.id,
                   'nome'        VALUE a.nome,
                   'disciplinas' VALUE JSON_ARRAYAGG(b.nome order by b.nome)) JSON_DOCUMENT
FROM aluno A,
     disciplina B,
     aluno_disciplina C
WHERE a.id = c.id_aluno
  AND b.id = c.id_disciplina
GROUP BY a.id, a.nome ORDER BY a.nome

JSON_OBJECT transforma uma coluna em um atributo JSON em um documento, e JSON_ARRAYAGG cria um array para um atributo. Temos como resultado um documento JSON com os códigos dos alunos, nomes e um array com as disciplinas que cada um cursa. Há uma variedade de funções de conversão para JSON no Oracle. Especificamente a JSON_ARRAYAGG você pode verificar a referência neste link. JSON_ARRAYAGG está disponível a partir da versão 12c.

A partir da versão 19c do a função JSON_OBJECT foi simplificada. Basta listar as colunas separadas por vírgula, ou então colocar asterisco para retornar todas as colunas. Veja:

Console do Oracle Live SQL

Para criar um array com JSON_ARRAYAGG utilizando a nova forma do JSON_OBJECT podemos fazer como no exemplo abaixo. Eu retorno o departamento com o nome de todos os funcionários:

Console do Oracle Live SQL

Agora Faz o Contrário: Transforma do JSON para o Relacional

E para converter de JSON para Relacional, utilize NESTED sobre a coluna que representa o documento JSON e as colunas que você quer projetar [também somente na versão 19c]. Eu fiz uma sub-query utilizando o exemplo acima [transformando de Relacional para JSON] e a query superior faz o inverso, transformando o JSON no Relacional:

Console do Oracle Live SQL

Utilizei o Oracle XE 18c para fazer os testes, e o SQL Developer como front-end. Todos são gratuitos e estão disponíveis para download. Para os comandos que funcionam no 19c, utilizei Oracle SQL Live direto na internet [no momento que estou escrevendo este post, a versão 19c está disponível somente no SQL Live]. Update: você pode criar uma conta no free tier da Oracle Cloud (OCI) e utilizar gratuitamente o Oracle Autonomous Database.