Home
%3CLINGO-SUB%20id%3D%22lingo-sub-805692%22%20slang%3D%22en-US%22%3ER%20%2B%20Azure%20Database%20for%20MySQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805692%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fmysql%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Database%20for%20MySQL%3C%2FA%3Eand%20%3CA%20href%3D%22https%3A%2F%2Fwww.r-project.org%2Fabout.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ER%3C%2FA%3Ecan%20be%20used%20together%20for%20data%20analysis%20%E2%80%93%20MySQL%20as%20database%20engine%20and%20R%20as%20statistical%20tool.%20When%20dealing%20with%20large%20datasets%20that%20potentially%20exceed%20the%20memory%20of%20your%20machine%20it%20is%20recommended%20to%20push%20the%20data%20into%20database%20engine%2C%20where%20you%20can%20query%20the%20data%20in%20smaller%20digestible%20chunks.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20837px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127045i85878439FA4BB323%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22RMySQL.jpg%22%20title%3D%22RMySQL.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20this%20article%20we%20will%20learn%20how%20to%20use%20R%20to%20perform%20the%20following%20tasks%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ECreate%20Azure%20Database%20for%20MySQL%20using%20%3CA%20href%3D%22https%3A%2F%2Fcran.r-project.org%2Fweb%2Fpackages%2FAzureRMR%2Findex.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzureRMR%3C%2FA%3Epackage%3C%2FLI%3E%0A%3CLI%3EConnect%20to%20Azure%20Database%20for%20MySQL%20using%20%3CA%20href%3D%22https%3A%2F%2Fcran.r-project.org%2Fweb%2Fpackages%2FRMySQL%2Findex.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERMySQL%3C%2FA%3Eand%20%3CA%20href%3D%22https%3A%2F%2Fcran.r-project.org%2Fweb%2Fpackages%2FRMariaDB%2Findex.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERMariaDB%3C%2FA%3Epackage%3C%2FLI%3E%0A%3CLI%3ECreate%20databases%20and%20tables%3C%2FLI%3E%0A%3CLI%3ELoad%20data%20from%20csv%20file%20into%20a%20table%3C%2FLI%3E%0A%3CLI%3EQuery%20data%20from%20table%20using%20%3CA%20href%3D%22https%3A%2F%2Fdplyr.tidyverse.org%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Edplyr%20grammar%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3EVisualize%20data%20from%20table%20using%20%3CA%20href%3D%22https%3A%2F%2Fggplot2.tidyverse.org%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eggplot2%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3EDelete%20table%2C%20database%20and%20Azure%20Database%20for%20MySQL%20server%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%3E%23%20Install%20and%20load%20required%20packages%0Aipak%20%26lt%3B-%20function(pkg)%7B%0A%20%20new.pkg%20%26lt%3B-%20pkg%5B!(pkg%20%25in%25%20installed.packages()%5B%2C%20%22Package%22%5D)%5D%0A%20%20if%20(length(new.pkg))%20%0A%20%20%20%20install.packages(new.pkg%2C%20dependencies%20%3D%20TRUE)%0A%20%20sapply(pkg%2C%20require%2C%20character.only%20%3D%20TRUE)%0A%7D%0Apackages%20%26lt%3B-%20c(%22AzureRMR%22%2C%20%22RMySQL%22%2C%20%22RMariaDB%22%2C%20%22tidyverse%22%2C%20%22curl%22%2C%20%22ggplot2%22%2C%20%22fun%22)%0Aipak(packages)%0A%0A%23%20Create%20Azure%20Database%20for%20MySQL%20using%20AzureRMR%20package%0AsubscriptionId%20%26lt%3B-%20%22ffffffff-ffff-ffff-ffff-ffffffffffff%22%0AresourceGroup%20%26lt%3B-%20%22test_group%22%0Alocation%20%26lt%3B-%20%22southcentralus%22%0AmysqlUserName%20%26lt%3B-%20%22azureuser%22%0AmysqlPassword%20%26lt%3B-%20random_password(length%20%3D%2012%2C%20replace%20%3D%20FALSE%2C%20extended%20%3D%20FALSE)%0AmysqlServerName%20%26lt%3B-%20%22testserver%22%0A%0Aaz%20%26lt%3B-%20create_azure_login()%0Asub%20%26lt%3B-%20az%24get_subscription(subscriptionId)%0Arg%20%26lt%3B-%20sub%24create_resource_group(resourceGroup%2C%20location)%0Aparameters%20%26lt%3B-%20jsonlite%3A%3AtoJSON(list(%0A%20%20administratorLogin%3Dlist(value%3DmysqlUserName)%2C%0A%20%20administratorLoginPassword%3Dlist(value%3DmysqlPassword)%2C%0A%20%20location%3Dlist(value%3Dlocation)%2C%0A%20%20serverName%3Dlist(value%3DmysqlServerName)%2C%0A%20%20skuCapacity%3Dlist(value%3D2)%2C%0A%20%20skuFamily%3Dlist(value%3D%22Gen5%22)%2C%0A%20%20skuName%3Dlist(value%3D%22GP_Gen5_2%22)%2C%0A%20%20skuSizeMB%3Dlist(value%3D5120)%2C%0A%20%20skuTier%3Dlist(value%3D%22GeneralPurpose%22)%2C%0A%20%20version%3Dlist(value%3D%225.7%22)%2C%0A%20%20backupRetentionDays%3Dlist(value%3D7)%2C%0A%20%20geoRedundantBackup%3Dlist(value%3D%22Disabled%22)%0A)%2C%20auto_unbox%3DTRUE)%0Atemplate%20%26lt%3B-%20%22https%3A%2F%2Fraw.githubusercontent.com%2FAzure%2Fazure-mysql%2Fmaster%2Farm-templates%2FExampleWithFirewallRule%2Ftemplate.json%22%0Avm_tpl%20%26lt%3B-%20rg%24deploy_template(%22myNewMySQLServer%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20template%3Dtemplate%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20parameters%3Dparameters%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20wait%3DTRUE)%0A%0A%23%20Connect%20to%20Azure%20Database%20for%20MySQL%0AcertName%20%26lt%3B-%20%22BaltimoreCyberTrustRoot.crt%22%0Adownload.file(url%20%3D%20%22https%3A%2F%2Fwww.digicert.com%2FCACerts%2FBaltimoreCyberTrustRoot.crt.pem%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20destfile%20%3D%20certName%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20mode%3D'wb')%0A%23%20Using%20RMariaDB%3A%3AMariaDB()%20instead%20of%20RMySQL%3A%3AMySQL()%20due%20to%20a%20bug%20related%20to%20SSL%20in%20RMySQL%20package%0Acon%20%26lt%3B-%20dbConnect(RMariaDB%3A%3AMariaDB()%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20host%3D%20paste0(mysqlServerName%2C%20%22.mysql.database.azure.com%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dbname%3D%22mysql%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20user%3Dpaste0(mysqlUserName%2C%20%22%40%22%2C%20mysqlServerName)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20password%3DmysqlPassword%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ssl.ca%3DcertName%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20client.flag%3D2048)%0A%0A%23%20create%20iris%20database%0AirisTableName%20%26lt%3B-%20%22iris%22%0AdbSendQuery(con%2C%20paste(%22CREATE%20DATABASE%22%2C%20irisTableName))%0A%0A%23%20connect%20to%20iris%20database%0Acon%20%26lt%3B-%20dbConnect(RMariaDB%3A%3AMariaDB()%2C%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20host%3D%20paste0(mysqlServerName%2C%20%22.mysql.database.azure.com%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dbname%3D%22iris%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20user%3Dpaste0(mysqlUserName%2C%20%22%40%22%2C%20mysqlServerName)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20password%3DmysqlPassword%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ssl.ca%3DcertName%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20client.flag%3D2048)%0A%0A%23%20create%20table%20iris%20and%20load%20data%20from%20a%20csv%20file%0Awrite.table(iris%2C%20file%3D%22iris.csv%22%2C%20sep%3D%22%2C%22%2C%20row.names%20%3D%20FALSE%2C%20quote%3DFALSE)%0AdbWriteTable(con%2C%20irisTableName%2C%20%22iris.csv%22)%0AdbReadTable(con%2C%20irisTableName)%0AdbListFields(con%2C%20irisTableName)%0A%0A%23%20query%20iris%20table%20using%20dplyr%0Airistbl%20%26lt%3B-%20tbl(con%2C%20%22iris%22)%0Airistbl%20%25%26gt%3B%25%20%0A%20%20group_by(Species)%20%25%26gt%3B%25%20%0A%20%20summarize(count%3Dn())%0A%0A%23%20show%20the%20query%20string%20for%20dplyr%20%0Airistbl%20%25%26gt%3B%25%20%0A%20%20group_by(Species)%20%25%26gt%3B%25%20%0A%20%20summarize(count%3Dn())%20%25%26gt%3B%25%20%0A%20%20show_query()%0A%0A%23%20visualize%20data%20using%20ggplot%0AirisTableData%20%26lt%3B-%20dbReadTable(con%2C%20irisTableName)%0Aggplot(data%3DirisTableData%2C%20aes(x%20%3D%20Sepal.Length%2C%20y%20%3D%20Sepal.Width))%20%2B%0A%20%20geom_point(aes(color%3DSpecies%2C%20shape%3DSpecies))%20%2B%0A%20%20xlab(%22Sepal%20Length%22)%20%2B%20%0A%20%20ylab(%22Sepal%20Width%22)%20%2B%0A%20%20ggtitle(%22Sepal%20Length%20vs%20Width%22)%0A%0A%23%20Cleanup%0AdbRemoveTable(con%2C%20irisTableName)%0AdbSendQuery(con%2C%20paste(%22DROP%20DATABASE%22%2C%20irisTableName))%0AdbDisconnect(con)%0Arg%24delete(confirm%3DFALSE)%0Arm(list%20%3D%20ls(all.names%20%3D%20TRUE))%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EREFERENCES%20%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.r-bloggers.com%2Fmysql-and-r%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMySQL%20and%20R%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-805692%22%20slang%3D%22en-US%22%3E%3CP%3ER%20%2B%20Azure%20Database%20for%20MySQL%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20837px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127044i616EFAEC884579E1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22RMySQL.jpg%22%20title%3D%22RMySQL.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805692%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ecreate%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Edatabase%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMySQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Er%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evisualize%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Azure Database for MySQL and R can be used together for data analysis – MySQL as database engine and R as statistical tool. When dealing with large datasets that potentially exceed the memory of your machine it is recommended to push the data into database engine, where you can query the data in smaller digestible chunks.

RMySQL.jpg

In this article we will learn how to use R to perform the following tasks:

 

  • Create Azure Database for MySQL using AzureRMR package
  • Connect to Azure Database for MySQL using RMySQL and RMariaDB package
  • Create databases and tables
  • Load data from csv file into a table
  • Query data from table using dplyr grammar
  • Visualize data from table using ggplot2
  • Delete table, database and Azure Database for MySQL server
# Install and load required packages
ipak <- function(pkg){
  new.pkg <- pkg[!(pkg %in% installed.packages()[, "Package"])]
  if (length(new.pkg)) 
    install.packages(new.pkg, dependencies = TRUE)
  sapply(pkg, require, character.only = TRUE)
}
packages <- c("AzureRMR", "RMySQL", "RMariaDB", "tidyverse", "curl", "ggplot2", "fun")
ipak(packages)

# Create Azure Database for MySQL using AzureRMR package
subscriptionId <- "ffffffff-ffff-ffff-ffff-ffffffffffff"
resourceGroup <- "test_group"
location <- "southcentralus"
mysqlUserName <- "azureuser"
mysqlPassword <- random_password(length = 12, replace = FALSE, extended = FALSE)
mysqlServerName <- "testserver"

az <- create_azure_login()
sub <- az$get_subscription(subscriptionId)
rg <- sub$create_resource_group(resourceGroup, location)
parameters <- jsonlite::toJSON(list(
  administratorLogin=list(value=mysqlUserName),
  administratorLoginPassword=list(value=mysqlPassword),
  location=list(value=location),
  serverName=list(value=mysqlServerName),
  skuCapacity=list(value=2),
  skuFamily=list(value="Gen5"),
  skuName=list(value="GP_Gen5_2"),
  skuSizeMB=list(value=5120),
  skuTier=list(value="GeneralPurpose"),
  version=list(value="5.7"),
  backupRetentionDays=list(value=7),
  geoRedundantBackup=list(value="Disabled")
), auto_unbox=TRUE)
template <- "https://raw.githubusercontent.com/Azure/azure-mysql/master/arm-templates/ExampleWithFirewallRule/template.json"
vm_tpl <- rg$deploy_template("myNewMySQLServer",
                               template=template,
                               parameters=parameters,
                               wait=TRUE)

# Connect to Azure Database for MySQL
certName <- "BaltimoreCyberTrustRoot.crt"
download.file(url = "https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem",
              destfile = certName,
              mode='wb')
# Using RMariaDB::MariaDB() instead of RMySQL::MySQL() due to a bug related to SSL in RMySQL package
con <- dbConnect(RMariaDB::MariaDB(), 
                 host= paste0(mysqlServerName, ".mysql.database.azure.com"),
                 dbname="mysql",
                 user=paste0(mysqlUserName, "@", mysqlServerName),
                 password=mysqlPassword,
                 ssl.ca=certName,
                 client.flag=2048)

# create iris database
irisTableName <- "iris"
dbSendQuery(con, paste("CREATE DATABASE", irisTableName))

# connect to iris database
con <- dbConnect(RMariaDB::MariaDB(), 
                 host= paste0(mysqlServerName, ".mysql.database.azure.com"),
                 dbname="iris",
                 user=paste0(mysqlUserName, "@", mysqlServerName),
                 password=mysqlPassword,
                 ssl.ca=certName,
                 client.flag=2048)

# create table iris and load data from a csv file
write.table(iris, file="iris.csv", sep=",", row.names = FALSE, quote=FALSE)
dbWriteTable(con, irisTableName, "iris.csv")
dbReadTable(con, irisTableName)
dbListFields(con, irisTableName)

# query iris table using dplyr
iristbl <- tbl(con, "iris")
iristbl %>% 
  group_by(Species) %>% 
  summarize(count=n())

# show the query string for dplyr 
iristbl %>% 
  group_by(Species) %>% 
  summarize(count=n()) %>% 
  show_query()

# visualize data using ggplot
irisTableData <- dbReadTable(con, irisTableName)
ggplot(data=irisTableData, aes(x = Sepal.Length, y = Sepal.Width)) +
  geom_point(aes(color=Species, shape=Species)) +
  xlab("Sepal Length") + 
  ylab("Sepal Width") +
  ggtitle("Sepal Length vs Width")

# Cleanup
dbRemoveTable(con, irisTableName)
dbSendQuery(con, paste("DROP DATABASE", irisTableName))
dbDisconnect(con)
rg$delete(confirm=FALSE)
rm(list = ls(all.names = TRUE))

 

REFERENCES : 

 

MySQL and R