Objetivos de Aprendizado
- Accessar uma base de dados com R.
- Rodar queries SQL em R usando
RSQLiteedplyr.- Descrever o comportamento preguiçoso (lazy behavior) do dplyr em dados guardados em uma base fora do R.
- Prototipar queries e recuperar todos os resultados finais.
- Criar queries complexas com uma ou mais tabelas de dados.
- Criar uma base de dados SQLite a partir de arquivos .csv.
Este artigo é uma tradução adaptada do capítulo 5 do curso Data Analysis and Visualization in R for Ecologists. Veja o curso completo aqui. (NT)
Até agora, nós lidamos com pequenos conjuntos de dados que facilmente cabem na memória do seu computador. Mas e quanto às bases de dados que são grandes demais para o seu computador gerenciar? Neste caso, armazenar dados fora do R e organizá-los em uma base de dados pode ser util. Conectar à base de dados permite que você recupere somente os pedaços necessários para a análise que estiver sendo feita.
E melhor ainda, muitos conjuntos de dados estão disponíveis em bases de dados públicas ou privadas. Você pode fazer queries sem ter que baixar os antes.
R pode se conectar com quase todos os tipos de bases de dados existentes. As bases de dados mais comuns tem pacotes de R que permitem a conexão com elas. (e.g., RSQLite, RMySQL, etc). Além disso, o pacote dplyr que você utilizou no capítulo anterior, junto com dbplyr, dá suporte à conexão com bases de dados open source amplamente utilizadas como sqlite, mysql e postgresql, bem como bigquery da Google, e também pode ser extendido para outros tipos bases de dados (uma vinheta vignette no pacote dplyr explica como fazer isso). RStudio criou um um website que contém documentação e as melhores práticas para trabalhar interfaces com várias bases de dados.
Fazer a interface com bases de dados usando dplyr foca em recuperar e analisar bases de dados gerando SQL SELECT, mas não modifica a própria base. dplyr não oferece funções para UPDATE ou DELETE de registros. Se você precisa dessas funcionalidades, será necessário usar pacotes R adicionais (e.g., RSQLite). Aqui demonstraremos como interagir com a base de dados usando dplyr, usando tanto a sintaxe do dplyr quanto a sintaxe do SQL.
Vamos continuar a explorar os dados de surveys com os quais você já se familiarizou nas lições anteriores. Primeiro, vamos instalar o pacote dbplyr:
A base de dados SQLite está em um único arquivo portal_mammals.sqlite gerado durante a lição de SQL. Se você não tem esse arquivo, pode fazer o download do Figshare para o subdiretório data_raw usando:
Podemos apontar o R para esta base de dados usando:
##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union##
## Attaching package: 'dbplyr'## The following objects are masked from 'package:dplyr':
##
## ident, sqlEste comando usa 2 pacotes - dbplyr e dplyr - que ajudam a falar com a base de dados SQLite. DBI não é algo que você usará diretamente como usuário. Ele permite ao R enviar comandos para as bases de dados independente do sistema de gerenciamento de base de dados (SGBD) utilizado. O pacote RSQLite cria uma interface entre o R e as bases de dados SQLite.
Este comando não carrega dados na sessão do R (como a função read_csv() faz). Ao invés disso, ele simplesmente instrui o R a conectar à base de dados SQLite contida no arquivo portal_mammals.sqlite.
Usando uma abordagem similar, você pode conectar a qualquer SGBD suportado pelo R incluindo MySQL, PostgreSQL, BigQuery, etc.
Vamos olhar mais de perto a base de dados mammals:
## src: sqlite 3.35.5 [/home/rog/Documentos/Data Science/R/Rmd - Report/SQLeR/data_raw/portal_mammals.sqlite]
## tbls: plots, species, surveysAssim como um arquivo planihas de cálculo com várias abas, uma base de dados SQLite pode conter múltiplas tabelas. Neste caso três delas estão listadas na linha tbls apresentada acima:
Agora que nós sabemos como conectar a uma base de dados, vamos explorar como trazer os dados dessas tabelas para o R.
Para conectar com tabelas dentro de uma base de dados, você pode usar a função tbl() do dplyr. Esta função pode ser utilizada para enviar queries SQL para a base de dados. Para demonstrar esta funcionalidade, vamos selecionar as colunas “year”, “species_id”, e “plot_id” da tabela surveys:
## # Source: SQL [?? x 3]
## # Database: sqlite 3.35.5 [/home/rog/Documentos/Data Science/R/Rmd -
## # Report/SQLeR/data_raw/portal_mammals.sqlite]
## year species_id plot_id
## <int> <chr> <int>
## 1 1977 NL 2
## 2 1977 NL 3
## 3 1977 DM 2
## 4 1977 DM 7
## 5 1977 DM 3
## 6 1977 PF 1
## 7 1977 PE 2
## 8 1977 DM 1
## 9 1977 DM 1
## 10 1977 PF 6
## # … with more rowsCom esta abordagem você pode usar qualquer query SQL vista na lição de base de dados.
Um dos pontos fortes do dplyr é que a mesma operação pode ser feita usando os verbos do dplyr ao invés de escrever SQL. Primeiro, selecionamos a tabela na qual queremos fazer operações criando o objeto surveys, e então nós usamos a sintaxe padrão do dplyr como se estivesse em um data frame:
## # Source: lazy query [?? x 3]
## # Database: sqlite 3.35.5 [/home/rog/Documentos/Data Science/R/Rmd -
## # Report/SQLeR/data_raw/portal_mammals.sqlite]
## year species_id plot_id
## <int> <chr> <int>
## 1 1977 NL 2
## 2 1977 NL 3
## 3 1977 DM 2
## 4 1977 DM 7
## 5 1977 DM 3
## 6 1977 PF 1
## 7 1977 PE 2
## 8 1977 DM 1
## 9 1977 DM 1
## 10 1977 PF 6
## # … with more rowsNeste caso, o objeto surveys se comporta como um data frame. Várias funções que podem ser usadas com data frames também podem ser usadas em tabelas de uma base de dados. Por exemplo, a função head() pode ser usada para conferir as primeiras 10 linhas de uma tabela:
## # Source: lazy query [?? x 9]
## # Database: sqlite 3.35.5 [/home/rog/Documentos/Data Science/R/Rmd -
## # Report/SQLeR/data_raw/portal_mammals.sqlite]
## record_id month day year plot_id species_id sex hindfoot_length weight
## <int> <int> <int> <int> <int> <chr> <chr> <int> <int>
## 1 1 7 16 1977 2 NL M 32 NA
## 2 2 7 16 1977 3 NL M 33 NA
## 3 3 7 16 1977 2 DM F 37 NA
## 4 4 7 16 1977 7 DM M 36 NA
## 5 5 7 16 1977 3 DM M 35 NA
## 6 6 7 16 1977 1 PF M 14 NA
## 7 7 7 16 1977 2 PE F NA NA
## 8 8 7 16 1977 1 DM M 37 NA
## 9 9 7 16 1977 1 DM F 34 NA
## 10 10 7 16 1977 6 PF F 20 NAA saída do comando head é igual a um data.frame normal: A tabela tem 9 colunas e o comando head() mostra as primeiras 10 linhas. Note que as colunas plot_type, taxa, genus, e species estão faltando. Essas estão agora localizadas nas tabelas plots e species que nós iremos fazer um join em um momento.
Entretanto, algumas funções não funcionam como esperado. Por exemplo, vamos conferir quantas linhas existem no total usando nrow():
## [1] NAIsso é estranho - o R não sabe quantas linhas tem na tabela surveys - ele retorna NA. Você já ter notado que a primeira linha de saída do head() incluía ?? indicando que o número de linhas era desconhecido.
A razão para este comportamento marca um diferença importante entre o uso de dplyr em bases de dados que estão na memória (e.g. carregado na sua sessão de R via read_csv()) e aquelas que provém de uma base de dados. Para entender isso, vamos olhar mais atentamente como o dplyr se comunica com a nossa base de dados SQLite.
Bases de dados relacionais tipicamente utilizam um linguagem com propósito especial, Structured Query Language (SQL), para gerenciar e consultar dados.
Por exemplo, a seguinte query SQL retorna as 10 primeiras linhas da tabela surveys:
Por trás da cena, dplyr:
data frame RPara levantar a cortina, podemos utilizar a função show_query() do dplyr para mostrar quais são os comandos SQL estão sendo enviados à base de dados:
## <SQL>
## SELECT *
## FROM `surveys`
## LIMIT 10A saída mostra a query SQL real enviada para a base de dados; ela coincide com o SELECT construído manualmente acima.
Ao invés de termos que formular as queries em SQL - e ter que trocar e destrocar mentalmente entre a sintaxe do R e do SQL - nós podemos delegar esta tradução para o dplyr. (Você não precisa nem mesmo saber SQL para interagir com a base de dados via dplyr!)
dplyr, por outro lado, não faz o verdadeiro trabalho de subsetting na tabela. Ao invés disso, ele simplesmente manda a query para a base de dados, espera a resposta e nos apresenta o retorno.
Desta forma, o R nunca vê a tabela surveys - e é por isso que ele não pode nos dizer quantas linhas ela contém. Pelo lado bom, isso nos permite trabalhar com grandes conjuntos de dados - mesmo grandes demais para caber na memória do nosso computador.
dplyr pode traduzir muitos tipos de queries diferentes em SQL nos permitindo, por exemplo, select() colunas específicas, filter() linhas, ou unir join tabelas.
Para ver isto em ação, vamos compor algumas queries com dplyr.
Primeiro, vamos selecionar somente as linhas da tabela surveys nas quais o weight é menor que 5 e trazer somente as colunas species_id, sex e weight.
## # Source: lazy query [?? x 3]
## # Database: sqlite 3.35.5 [/home/rog/Documentos/Data Science/R/Rmd -
## # Report/SQLeR/data_raw/portal_mammals.sqlite]
## species_id sex weight
## <chr> <chr> <int>
## 1 PF M 4
## 2 PF F 4
## 3 PF <NA> 4
## 4 PF F 4
## 5 PF F 4
## 6 RM M 4
## 7 RM F 4
## 8 RM M 4
## 9 RM M 4
## 10 RM M 4
## # … with more rowsExecutar esse comando irá retornar uma tabela com 10 linhas e as colunas species_id, sex e weight. Ótimo!
… mas espere, por que somente 10 linhas?
A última linha:
# ... with more rowsindica que existem mais resultados que estão de acordo com o nosso critério de filtro. Por que o R foi preguiçoso e só trouxe 10 resultados?