MySQL e Dplyr

Uma das soluções mais simples para trabalharmos com bases de dados grandes em R é, em vez de carregar os dados na memória, conectar o R a uma fonte remota de dados e usar a gramática que aprendemos nos tutoriais anteriores para manipularmos os dados. Veremos a seguir um exemplo para dados em um servidor MySQL, mas ele vale para outros sistemas de gerenciamento de dados como PostgreSQL, MariaDB ou BigQuery.

O MySQL não armazena os dados na memória RAM, mas no disco rígido. Isso expande bastante o limite do tamanho dos dados que conseguiremos gerenciar, sem, no entanto, precisar de outra “gramática” para manipulação de dados.

Antes de prosseguir, faça a instalação do pacote RMySQL. Carregue tal pacote e o dplyr antes de começar.

library(dplyr)
library(RMySQL)

O primeiro passo importante para trabalhar com dados em um servidor MySQL é fazer a conexão com uma base de dados. Vamos supor que temos um banco de dados (que, para o MySQL, significa um conjunto de tabelas, e não apenas uma tabela) chamado “PBF” em um servidor local, ou seja, no próprio computador, e que dentro desse banco de dados existe a tabela “transferencias201701”. O usuário e senha fictícias são, respectivamente, “root” e “pass”. Usamos, então, a função src_mysql para criar um objeto de “conexão”, que chamaremos de “bd_mysql”. Obviamente, o código abaixo não funcionará no seu computador, pois tal servidor não existe.

conexao <- src_mysql(dbname = "PBF", 
                   user = "root",
                   password = "pass")

A seguir, criamos um objeto “tabela”, que mantém a conexão direto com a tabela “transferencias201701” que está no banco de dados “PBF”.

tabela <- tbl(conexao, "pagamentos201701")

Simples, não? A partir deste ponto, basta trabalhar com objeto “tabela” da mesma maneira que trabalhamos data frames até agora. A diferença é que o objeto “tabela” não contém dados, mas representa a conexão a uma tabela em um servidor que contém os dados. A gramática oferecida pelo pacote dplyr funciona normalmente e, quando quisermos, podemos exportar os dados com os métodos acima.

Fake data

Vamos ao exemplo simples com o data frame “fake data”. Os códigos abaixo funcionarão normalmente no seu computador.

Acessaremos servidor remoto de MySQL que contém um banco de dados denominada “mysql_leobarone”. Esta, por sua vez, contém uma tabela também chamada “fake_data” com informações sobre eleitores fictícios em “Fakeland”.

Comecemos estabelecendo a conexão com o servidor e o banco de dados, tal como fizemos anteriormente. A diferença, agora, é que estamos lidando com um servidor remoto e por isso precisamos informar seu “endereço”. Fazemos isso preenchendo os argumentos “host” e “port”, cujos valores foram fornecidos pelos criadores do servidor, além, obviamente, do nome do banco de dados, usuário e senha (“leobarone” e “usp3697560”, respectivamente). Para esta atividade, utilizei o serviço de hospedagem gratuito “db4free.net”.

conexao <- src_mysql(dbname = "mysql_leobarone", 
                   host = "db4free.net", 
                   port = 3306, 
                   user = "leobarone",
                   password = "usp3697560")

Para ver todas as tabelas disponíveis na base de dados com a qual fizemos a conexão usamos:

src_tbls(conexao)

Uma vez conectados com o servidor, fazemos a conexão direta com a tabela “fake_data”, que é a que nos interessa (pode haver inúmeras tabelas em uma base de dados). Vamos chamar o objeto criado de “fake”. Será este objeto que manipularemos com a gramática que aprendemos anteriormente.

fake <- tbl(conexao, "fake_data")

Você pode examinar o conteúdo de voos com as funções que já conhecemos, como head. (Pela natureza do SQL, ele não nos retorna o número de observações, somente a de variáveis - mas isso é normal e você não precisa se preocupar com isso).

Em nosso exercício vamos repetir, utilizando a gramática do dplyr, algumas tarefas que fizemos no tutorial 5 da aula 3 (sem usarmos dplyr ainda) e ver como podemos aplicar os principais verbos do dessa gramática a tabelas em um servidor MySQL.

Por exemplo, se quisermos criar subconjunto dos dados apenas com eleitores independentes, renomeando e selecionando algumas variáveis, e criando uma variável que é a divisão da poupança pela renda.

independentes <- fake %>%
  rename(partido = party, renda = income, poupanca = savings, candidato = candidate) %>%
  select(partido, renda, poupanca, candidato) %>%
  filter(partido == "Independent") %>%
  mutate(renda_poupanca = renda / poupanca)

Note que “independentes” tampouco é um data frame. É, novamente, uma representação do resultado de uma query (ou consulta) em uma tabela no servidor. Para falar a verdade, nem isso é ainda. Por enquanto é uma ordem de execução da query, que só será efetivamente executada se os dados forem requisidos para serem armazenados no seu computador ou impressos no console.

Tradução dplyr-SQL

Podemos traduzir, literalmente, o comando acima para a linguagem de R para SQL como a função show_query

show_query(independentes)

O output, impresso no console, é a query equivalente que deveríamos produzir no MySQL para executar a mesma tarefa.

Tabelas temporárias versus criação de tabelas no MySQL

Quando utilizamos os verbos do dplyr para manipulação de dados em servidor MySQL, todas as consultas são geradas como tabelas temporárias no servidor. Como fazer com que as consultas se tornem tabelas permanentes no servidor?

Vamos trabalhar com um servidor fictício, pois não temos permissão para gerar tabelas no servidor que utilizamos como exemplo no tutorial. Vamos supor que temos uma tabela “pagamentos201701” na nossa base de dados “PBF” e que tal tabela contém uma variável “UF” para unidades da federação:

Ao produzir o comando acima, na prática, nada aconteceu. A execução da query só ocorrerá quando tentarmos trazer a tabela para a memória (“fetch”) ou explicitarmos que ela deve ser computada.

Se quisermos trazer os dados para a memória, utilizamos a função collect.

independentes_df <- collect(independentes)

Ao usar o comando collect, a query é executada no servidor e os dados enviados ao R. Note, agora, que independentes_df é um data frame em seu workspace.

O caminho inverso – subir ao servidor uma tabela – é feito com a função copy_to. Como estamos tod@s utilizando o mesmo servidor e a tabela “independentes_tbl” já existe, você receberá uma mensagem de erro.

copy_to(dest = conexao, df = independentes_df, name = "independentes_tbl")

No entanto, copy_to não geram uma nova tabela no servidor. Para que uma nova tabela seja gerada, é preciso definir o argumento “temporary” como “FALSE” (o padrão é “TRUE”). Novamente, você receberá uma mensagem de erro, pois a tabela já existe.

copy_to(dest = conexao, df = independentes_df, name = "independentes_tbl", temporary = F)

Para executar a query no servidor sem que precisemos trazer a tabela e reenviá-la devemos usar a função compute, que também tem o argumento “temporary”.

compute(minha_query, name = "pagamentos201701_es", temporary = FALSE)

Sem definir “temporary” como “FALSE”, a query será executada e a tabela gerada será temporária, apenas.

Outras bases de dados

A conexão a outras bases de dados, MariaDb, SQLite, PostgreSQL e BigQuery, feita exatamente do mesmo modo que fizemos a conexão com um servidor de MySQL. Para as bases citadas usamos, respectivamente, as funções scr_mysql (sim, para MariaDB a função é a mesma que para MySQL), scr_sqlite, scr_postgres e scr_bigquery com os mesmos 5 argumentos que vimos acima: dbname, host, port, user e password.

Os verbos de manipulação de dados do pacote dplyr funcionam normalmente para tabelas nesses servidores.

Um exemplo com mais de uma tabela

Vamos seguir com outro exemplo de manipulação de dados num servidor MySQL para trabalhar com mais de uma tabela e reforçar o que vimos acima. Vamos utilizar agora dados que estão no servidor do site “Datacamp”:

Comecemos com a conexão:

conexao <- src_mysql(dbname = "tweater", 
                   host = "courses.csrrinzqubik.us-east-1.rds.amazonaws.com", 
                   port = 3306, 
                   user = "student",
                   password = "datacamp")

src_tables fornece a listagem de tabelas na base de dados.

src_tbls(conexao)

Nessa base de dados há 3 tabelas com informações provenientes de uma rede social: “users”, que é uma tabela de usuários; “tweats” com informações sobre postagens de usuários; e “comments”, com comentários às postagens por outros usuários.

Se você sabe algo de SQL, pode usar a função tbl permite fazer queries em linguagem SQL. Por exemplo.

tbl(conexao, sql("SELECT * FROM comments WHERE user_id > 4"))

Mas queremos evitar o uso da linguagem SQL. Com tbl, criamos um objeto de tabela que seja manipulável com as funções do dplyr, sem, no entanto, importá-la. Vamos fazer isso para as três tabelas da base de dados que estamos usando:

comments <- tbl(conexao, "comments")
tweats <- tbl(conexao, "tweats")
users <- tbl(conexao, "users")

Repetindo a query acima, em que selecionamos na tabela comments apenas as linhas com user_id > 4:

filter(comments, user_id > 4)

A partir de agora, as funções de manipulação de dados do dplyr são aplicáveis aos novos objetos criados para representar as tabelas que estão no servidor. Por exemplo, vamos renomear a variável “id” em tweats para “tweat_id” e fazer um left join entre comments e tweats por “tweat_id”:

tweats2 <- rename(tweats, tweat_id = id)
tabela_join <- left_join(tweats2, comments, "tweat_id")
head(tabela_join)

Note que “tweats2” é uma tabela gerada no servidor de SQL e não está na memória RAM de nosso computador.

Novamente, podemos traduzir a query de R para SQL:

show_query(tabela_join)

As funções da gramática do dplyr – filter, select, rename, mutate, group_by, summarize, left_join, right_join, inner_join, full_join e etc – funcionam normalmente à tabelas remotas e facilitam demais a manipulação. Um exemplo tolo, porém completo, usando o operador %>% ( pipe, que permite omitir o primeiro argumento de cada função e nos poupa de repetir o nome da tabela diversas vezes) para ilustrar a aplicação de diversas funções do dplyr ao mesmo tempo:

tweats <- tweats %>% 
  rename(tweat_id = id) %>%              # renomeia variavel
  mutate(post = toupper(post)) %>%       # post em letras maiusculas
  left_join(comments, "tweat_id") %>%    # left join con tabela "comments"
  select(tweat_id, post, message) %>%    # mantem apenas 3 variaveis
  filter(substr(message, 
                nchar(message), 
                nchar(message)) == "!")  # filtra por comentarios que terminam em !
head(tweats, 7)

Uma maneira simples de trazer à memória de seu computador a tabela gerada a partir da query, com as.data.frame importamos a tabela como data frame:

tabela <- as.data.frame(tweats)

Note que “tabela”, diferentemente de “tweats”, é um data frame no seu workspace.