Chamando o R do PostgreSQL

Por José de Jesus Filho em 26/06/2020

Introdução

Este tutorial irá mostrar como instalar o R e o PostgreSQL num mesmo servidor Ubuntu 18.04 ou 16.04. Em seguida, falaremos sobre como chamar o R a partir do Postgres.

Farei uma série de tutoriais. Este primeiro é voltado para aqueles com familiaridade com funções do R e que gostariam de rodá-las no PostgreSQL, sem necessariamente conhecer muito SQL.

Num próximo, iremos mostrar como criar queries e declarações do PostgreSQL do R, ou seja, será mais voltado para quem tem familiaridade com o SQL, mas não necessariamente versada em R.

Os tutoriais posteriores serão voltados para aqueles com bastante familiaridade em tidyverse e que gostariam de realizar as mesmas coisas em SQL. Igualmente, servirão para aqueles que sabem manipular dados em SQL, mas gostariam de fazer as mesmas coisas no R.

Em futuros tutoriais, falaremos quando compensa iniciar no PostgreSQL e terminar no R. Por exemplo, quando é mais vantajoso dar um join no R em vez de fazê-lo no PostgreSQL e vice-versa.

Instalando o PostgreSQL

A primeira coisa a fazer é atualizar os pacotes do sistema:

sudo apt update
sudo apt -y install vim bash-completion wget
sudo apt -y upgrade

Feito isso, dê um reboot:

sudo reboot

Importe a chave GPG:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Adicione a chave GPG ao systema:

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Atualize os pacotes do sistema e instale os pacotes do PostgreSQL necessários. Os dois primeiros são necessários para rodar o PostgreSQL, os três últimos são necessários para instalar o plr, o RPostgres e outras extensões. Falaremos deles mais adiante.

sudo apt update
sudo apt install postgresql-12 postgresql-client-12 postgresql-server-dev-all libpq-dev postgresql-contrib

Instalação do R

sudo echo "deb https://cloud.r-project.org/bin/linux/ubuntu `lsb_release -cs`-cran40/" | sudo tee -a /etc/apt/sources.list

Adicionar a chave GPG

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys E298A3A825C0D65DFD57CBB651716619E084DAB9

Instale o R

sudo apt-get update
sudo apt-get install -y r-base r-base-dev 

Instale também o git

sudo apt install git

Agora use o git para clonar o plr. Não importa o local onde você irá cloná-lo. Desde que você tenha seguido fielmente os passos anteriores, tudo terminará bem.

git clone https://github.com/postgres-plr/plr

Feito isso, entre no diretório plr e rode os seguintes comandos para instalá-lo como extensão.

cd plr
USE_PGXS=1 make
USE_PGXS=1 make install

Criando uma base de dados

Vamos para o Postgres a fim de criar uma base de dados.

sudo -u postgres psql
CREATE DATABASE datasets;

Além disso, você deve incluir a extensão plr na base de dados recentemente criada:

\c datasets -- conectar-se à base
CREATE EXTENSION plr;
\q -- sair do psql

De volta ao R.

R

Instale os pacotes RPostgres e broom

Instalando esses dois pacotes é suficiente para instalar também outras dependências como o DBI e o dplyr, as quais igualmente usaremos.

install.packages(c("RPostgres","broom"))

Connexão do R ao Postgres

Eventualmente, você terá de autorizar a conexão local. Vá para o arquivo:

vim /etc/postgresql/12/main/pg_hba.conf

E altere a seguinte linha de:

local all all peer

Para:

local all all trust

Colocando uma tabela na base de dados

Admitindo que você ainda se encontra no R, estamos em condições de incluir uma tabela na base de dados.

Primeiramente, vamos conectar-nos à base:

conn <- DBI::dbConnect(RPostgres::Postgres(), dbname="datasets")

Estou admidindo com o código acima que você está usando o R na mesma máquina do Postgres, usando o usuário postgres e dispensou o uso de senha para conexão local.

Vamos enviar o dataframe mtcars para a base de dados. O exemplo do mtcars não é muito feliz porque ele poderia ser chamado do próprio R quando rodado no Postgres, mas apenas a título de exemplo, iremos assumir que ele seja qualquer outro data.frame.

DBI::dbWriteTable(conn,"tabela", mtcars)

De volta ao Postgres

Mostraremos num próximo tutorial como realizar os procedimentos a seguir sem sair do R, mas o propósito deste tutorial é justamente ilustrar como podemos chamar o R do Postgres. Assim, faremos tudo no Postgres mesmo.

Voltando para o shell, vamos conectar-nos à base datasets:

sudo -u postgres psql datasets

Verifique se a tabela chamada “tabela” se encontra na base de dados:

\d+ -- ou 
\d+ tabela

Preparando o terreno

Vamos criar uma tabela que servirá de referência para receber os resultados de uma regressão linear. Veja que as colunas são as mesmas do tibble retornado pela função tidy do pacote broom, com a diferença de que os pontos foram substituídos pelo sublinhado e os nomes das colunas passados para o português.

create table modelo (termo text, estimativa float8, erro_padrao float8, estatistica float8, p_valor float8);

Criando uma função plr

Enfim estamos em condições de criar uma função no PostgreSQL que chama o R para rodar uma regressão linear em uma tabela contida no próprio Postgres:

CREATE OR REPLACE FUNCTION lm_teste() RETURNS SETOF modelo AS
$$ 
base <<- pg.spi.exec('select mpg, wt, qsec, am  from tabela')
df <- lm(mpg ~ wt + qsec + factor(am), data=base)
df <- broom::tidy(df)
names(df) <- c('termo','estimativa','erro_padrao','estatistica','p_valor')
df <- dplyr::mutate_at(df,dplyr::vars(2:5), ~round(.,2))
return(df)
$$
language 'plr';

Note que o esqueleto da função é o mesmo para qualquer outra função do PostgreSQL. A diferença é que, para importar um objeto da base de dados para nossa função, devemos usar a função pg.spi.exec.

Rodando a regressão linear

Agora ficou fácil. Basta chamar a função e ver os resultados:

select * from lm_teste();
datasets=# select * from lm_teste();
    termo    | estimativa | erro_padrao | estatistica | p_valor
-------------+------------+-------------+-------------+---------
 (Intercept) |       9.62 |        6.96 |        1.38 |    0.18
 wt          |      -3.92 |        0.71 |       -5.51 |       0
 qsec        |       1.23 |        0.29 |        4.25 |       0
 factor(am)1 |       2.94 |        1.41 |        2.08 |    0.05
(4 rows)

Vantagens

Eu apontaria duas principais vantagens em usar o plr:

1 - Uma vez que a base se encontra no PostgreSQL, você não precisa mais transferi-la para o R a fim de rodar o modelo e retornar o resultado ao PostgreSQL. Essa viagem dos dados torna-se dispensável. No exemplo mostrado, porém, a base irá para uma sessão do R de qualquer forma. Veremos como solucionar isso em tutoriais futuros.

2 - Você pode continuar trabalhando no R, enquanto seu modelo roda no PostgreSQL. Se o modelo tomar horas, este se torna um problema menor.

comments powered by Disqus

Nossa Newsletter

Uma vez por semana enviamos um e-mail para você não perder nenhum post da Curso-R. Avisamos também sempre que abrimos uma nova turma.