MySql in a container

November 19, 2016
By

(This article was first published on R – Win-Vector Blog, and kindly contributed to R-bloggers)

I have previously written on using containerized PostgreSQL with R. This show the steps for using containerized MySQL with R.

As a consulting data scientist I often have to debug and rehearse work away from the clients actual infrastructure. Because of this it is useful to be able to spin up disposable PostgreSQL or MySQL work environments. I have already written on how to do this for PostgreSQL, and here are our notes on how to do this for MySQL.

First make sure you have a current version of Docker installed and running on your system. Then launch a MySQL image with the standard MySQL communication port (3306) bound to your host machine’s network interface by typing the following at the command line (such as the shell in OSX, some instructions can be found here):

docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=passwd -d mysql/mysql-server:5.6

Now from the R side of things you can use the package RMySQL to connect to the database.

library('RMySQL')
library('dplyr')
mysql <- src_mysql('mysql','127.0.0.1',3306,'root','passwd')

And we are ready to work dplyr examples such as these:

library('nycflights13')

# dplyr/mysql connector seems to error out on NA, overwrite them for now
# submitted issue: https://github.com/hadley/dplyr/issues/2259
flts <- flights
for(ci in colnames(flts)) {
  napos <- is.na(flts[[ci]])
  if(any(napos)) {
    if(is.numeric(flts[[ci]])) {
      flts[[ci]][napos] <- NaN
    }
    if(is.character(flts[[ci]])) {
      flts[[ci]][napos] <- ''
    }
  }
}

flights_mysql <- copy_to(mysql, flts, temporary = FALSE, indexes = list(
  c("year", "month", "day"), "carrier", "tailnum"))

flights_mysql

## Source:   query [?tap_a=5644-dce66f&tap_s=10907-287229"https://ninazumel.com/">Nina Zumel‘s article Using PostgreSQL in R: A quick how-to.

To leave a comment for the author, please follow the link and comment on their blog: R – Win-Vector Blog.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Sponsors

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)