Is there any example of a working public database for testing dbplyr or similar? Current rstudio.com examples broken

[cwright1]

I have too much data for my Shiny app. I need to connect to a remote database - but I'm not super experienced with data bases (basic knowledge of MySQL).

I want to do a simple test to see if I can connect to a remote MySQL database using dbplyr ,pool, RMySQL, etc.

I've been checking out the documentation for these packages, including looking at the videos from rstudio.com published online. The features are great but I'm not getting anywhere with the examples. I think this is because I can't connect to a database to give it a try on.

This is a link that I could've sworn I got working before:
https://shiny.rstudio.com/articles/pool-dplyr.html

But now the database example is broken and will not connect to the db. Virtually all other documentation I come across use examples of reading the database from memory.

Any examples of a database that allows users to read from for testing R database management tools?

[pieterjanvc]

Hi,

I created an example that accesses a publicly available Rfam MySQL database you can play with
https://docs.rfam.org/en/latest/database.html

library(dplyr)
library(dbplyr)
library(DBI)
library(RMariaDB)

myConn = dbConnect(RMariaDB::MariaDB(),
          db = "Rfam", 
          host = "mysql-rfam-public.ebi.ac.uk",
          user = "rfamro",
          port = 4497)

tbl(myConn, "taxonomy") %>% 
  filter(ncbi_id == 10116)
#> # Source:   lazy query [?? x 5]
#> # Database: mysql [rfamro@mysql-rfam-public.ebi.ac.uk:NA/Rfam]
#>   ncbi_id species     tax_string           tree_display_name  align_display_name
#>     <int> <chr>       <chr>                <chr>              <chr>             
#> 1   10116 Rattus nor~ Eukaryota; Metazoa;~ Rattus_norvegicus~ Rattus_norvegicus~

^{Created on 2021-05-12 by the reprex package (v2.0.0)}

Make sure that if you connect to this database you are not on a network that has a strict firewall. They might block connections like this so if it's not working, try using another network of change the firewall settings.

Hope this helps,
PJ