Machine learning com R e PostgreSQL

Neste tutorial, iremos mostrar como rodar kmeans e floresta aleatória, ou qualquer ou modelo estatístico ou de machine learning no PostgreSQL chamando o R. Com extensão PL/R isso é perfeitamente possível, simples e fácil.

José de Jesus Filho https://rpg.consudata.com.br
08-09-2021

Neste tutorial, iremos mostrar como rodar um modelo estatístico ou de machine learning no PostgreSQL chamando o R. Para tanto, usaremos a extensão PL/R. Se você quiser saber mais sobre a extensão PL/R no PostgreSQL, acesse este tutorial ou o guia do usuário.

Um desafio a ser enfrentado é que modelos estatísticos e de machine learning geralmente retornam uma lista do R com uma classe atribuída. No entanto, o PostgreSQL não reconhece listas do R.

Para esse problema há duas soluções possíveis. Uma delas é usar o pacote broom, que converte grande parte dos resultados de modelos em dataframe. A limitação dessa abordagem é que terminamos por preservar somente as informações mais relevantes, dada a estrutura retangular de dataframes.

A outra, que acho mais elegante e flexível, é armazenar o modelo em formato json ou, melhor ainda, em jsonb no PostgreSQL. Nesse sentido, o pacote jsonlite vem a calhar, uma vez que, com ele, podemos converter listas para json e enviar para o PostgreSQL. Eventualmente, bastará trazer o json do PostgreSQL para o R e transformá-lo em lista novamente. A desvantagem em usar json é a eventual perda de precisão em números, uma vez que json é baseado em texto.

Neste tutorial, iremos mostrar como rodar kmeans e floresta aleatória. Naturalmente, você pode usar outros modelos, pois a lógica é a mesma. Usarei kmeans porque um amigo e ex-aluno trouxe o desafio dias atrás. Depois disso, adotarei o mesmo método para floresta aleatória a fim de mostrar sua flexibilidade.

Assumindo que você tem uma conexão com o PostgreSQL, vamos enviar o dataframe USArrests do pacote datasets para o PostgreSQL.

DBI::dbWriteTable(conn,"us_arrests", USArrests)

Se você já instalou a extensão PL/R, não se esqueça de criá-la no banco:

DBI::dbExecute(conn,"create extension plr")

Agora vamos criar uma função no PostgreSQL para rodar kmeans no R.


DBI::dbExecute(conn,"

CREATE OR REPLACE FUNCTION kmeans(text, centers integer[], iter_max integer default 10, nstart integer default 1, algorithm text default 'Hartigan-Wong', trace boolean default false) RETURNS jsonb AS
    
$$


df <- pg.spi.exec(arg1)


modelo <- kmeans(df, 
                 centers,
                 iter_max,
                 nstart,
                 algorithm,
                 trace
                  )

class(modelo)<- "list"

modelo <- jsonlite::serializeJSON(modelo)

return(modelo)
    
$$
    language plr

")

Na primeira linha, criamos a função kmeans com a mesma quantidade de argumentos da função kmeans do R base. A única diferença é que o primeiro argumento é do tipo charactere, enquanto a função kmeans do R aceita qualquer objeto que possa ser convertido para matriz, seja um vetor, um dataframe ou a própria matriz. Acontece que a PL/R tem uma função, pg.spi.exec() que aceita uma query do PostgreSQL como argumento, de modo que, se dermos um SELECT, o R irá receber esse argumento como um tabela.

Note na segunda linha que informamos jsonb como resposta. Em seguida, usamos $$ para indicar que iniciaremos o corpo da função.

Em seguida, passamos os demais argumentos contidos na própria função kmeans do R, com seus valores padrão (default). Na primeira linha do corpo, chamamos a função pg.spi.exec(arg1) para converter a query num dataframe. Particularmente, acho essa função fantástica, pois no fundo podemos inserir nela qualquer query que resulte numa tabela.

Como o modelo tem classe “kmeans”, precisamos substituí-la por “list”, do contrário, o jsonlite não saberá como converter para json. Por fim, convertemos a lista que contêm o modelo para json com a função serializeJSON do pacote jsonlite, em vez de toJSON, a fim de preservar os atributos.

Ao receber a resposta da função, o PostgreSQL irá automaticamente convertê-la para jsonb. Nós iremos chamá-la de volta para o R a fim de que possamos verificar se o resultado pode ser usado como objeto kmeans. Naturalmente, você pode criar uma tabela no PostgreSQL e populá-la com o resultado.

df <- DBI::dbGetQuery(conn,"select kmeans('select * from us_arrests',array [4]) as modelo")

modelo <- jsonlite::unserializeJSON(df$modelo)

class(modelo) <- 'kmeans'

Agora é só imprimir para ver o resultado:

modelo
K-means clustering with 4 clusters of sizes 12, 20, 14, 4

Cluster means:
     Murder  Assault UrbanPop     Rape
1 11.766667 257.9167 68.41667 28.93333
2  4.270000  87.5500 59.75000 14.39000
3  8.214286 173.2857 70.64286 22.84286
4 11.950000 316.5000 68.00000 26.70000

Clustering vector:
       Alabama         Alaska        Arizona       Arkansas 
             1              1              4              3 
    California       Colorado    Connecticut       Delaware 
             1              3              2              1 
       Florida        Georgia         Hawaii          Idaho 
             4              3              2              2 
      Illinois        Indiana           Iowa         Kansas 
             1              2              2              2 
      Kentucky      Louisiana          Maine       Maryland 
             2              1              2              4 
 Massachusetts       Michigan      Minnesota    Mississippi 
             3              1              2              1 
      Missouri        Montana       Nebraska         Nevada 
             3              2              2              1 
 New Hampshire     New Jersey     New Mexico       New York 
             2              3              1              1 
North Carolina   North Dakota           Ohio       Oklahoma 
             4              2              2              3 
        Oregon   Pennsylvania   Rhode Island South Carolina 
             3              2              3              1 
  South Dakota      Tennessee          Texas           Utah 
             2              3              3              2 
       Vermont       Virginia     Washington  West Virginia 
             2              3              3              2 
     Wisconsin        Wyoming 
             2              3 

Within cluster sum of squares by cluster:
[1]  6705.907 19263.760  9136.643  2546.350
 (between_SS / total_SS =  89.4 %)

Available components:

[1] "cluster"      "centers"      "totss"        "withinss"    
[5] "tot.withinss" "betweenss"    "size"         "iter"        
[9] "ifault"      

Floresta aleatória

Para finalizar, vamos criar mais uma função e rodar floresta aleatória. Uma vez que o princípio é o mesmo, iremos usar somente os argumentos fórmula e data. Se você quiser, pode ir adiante e adicionar os demais argumentos, fique à vontade. Usaremos o pacote ranger. Portanto, é preciso tê-lo instalado, além do dplyr para converter todas as variáveis de texto em fatores.


DBI::dbExecute(conn,"

create or replace function rf(formula text, query text) returns jsonb AS

$$ 

df <- pg.spi.exec(query)

df <- dplyr::mutate_if(df,is.character, as.factor)

modelo <- ranger::ranger(formula, data = df)

class(modelo) <- "list"

modelo <- jsonlite::serializeJSON(modelo)

return(modelo)

$$
language plr

")

Agora é só chamar a função para ter o seu modelo em jsonb no PostgreSQL.

modelo <- DBI::dbGetQuery("select rf('Species ~ .', 'table iris'))

Caso queira visualizar o modelo no R, basta seguir os mesmos passos:

library(ranger)

modelo <- jsonlite::unserializeJSON(modelo$rf)
class(modelo) <- "ranger"
modelo
Ranger result

Call:
 ranger::ranger(Species ~ ., data = iris) 

Type:                             Classification 
Number of trees:                  500 
Sample size:                      150 
Number of independent variables:  4 
Mtry:                             2 
Target node size:                 1 
Variable importance mode:         none 
Splitrule:                        gini 
OOB prediction error:             4.00 %