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.
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:
modeloK-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" 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"
modeloRanger 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 %