Home
%3CLINGO-SUB%20id%3D%22lingo-sub-805676%22%20slang%3D%22en-US%22%3ER%20%2B%20Azure%20Database%20for%20PostgreSQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Database%20for%20PostgreSQL%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%20PostgreSQL%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%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20886px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127042i0904DD6F16FF4260%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22RPostgres.jpg%22%20title%3D%22RPostgres.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%20PostgreSQL%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%20PostgreSQL%20using%20%3CA%20href%3D%22https%3A%2F%2Fcran.r-project.org%2Fweb%2Fpackages%2FRPostgres%2Findex.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERPostgres%3C%2FA%3Epackage%3C%2FLI%3E%0A%3CLI%3ECreate%20databases%20and%20tables%3C%2FLI%3E%0A%3CLI%3ELoad%20data%20from%20dataframe%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%20PostgreSQL%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%22RPostgres%22%2C%20%22tidyverse%22%2C%20%22curl%22%2C%20%22fun%22)%0Aipak(packages)%0A%0A%23%20Create%20Azure%20Database%20for%20PostgreSQL%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%0ApgUserName%20%26lt%3B-%20%22azureuser%22%0ApgPassword%20%26lt%3B-%20random_password(length%20%3D%2012%2C%20replace%20%3D%20FALSE%2C%20extended%20%3D%20FALSE)%0ApgServerName%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%3DpgUserName)%2C%0A%20%20administratorLoginPassword%3Dlist(value%3DpgPassword)%2C%0A%20%20location%3Dlist(value%3Dlocation)%2C%0A%20%20serverName%3Dlist(value%3DpgServerName)%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%2210%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-postgresql%2Fmaster%2Farm-templates%2FExampleWithFirewallRule%2Ftemplate.json%22%0Avm_tpl%20%26lt%3B-%20rg%24deploy_template(%22myNewPostgreSQLServer%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%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%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%20wait%3DTRUE)%0A%0A%23%20Connect%20to%20Azure%20Database%20for%20PostgreSQL%0Acon%20%26lt%3B-%20dbConnect(RPostgres%3A%3APostgres()%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20host%3D%20paste0(pgServerName%2C%20%22.postgres.database.azure.com%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dbname%3D%22postgres%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20user%3Dpaste0(pgUserName%2C%20%22%40%22%2C%20pgServerName)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20password%3DpgPassword)%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(RPostgres%3A%3APostgres()%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20host%3D%20paste0(pgServerName%2C%20%22.postgres.database.azure.com%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dbname%3DirisTableName%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20user%3Dpaste0(pgUserName%2C%20%22%40%22%2C%20pgServerName)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20password%3DpgPassword)%0A%0A%23%20create%20table%20iris%20and%20load%20data%20from%20iris%20dataframe%0AdbCreateTable(con%2C%20irisTableName%2C%20iris)%0AdbAppendTable(con%2C%20irisTableName%2C%20iris)%0AdbReadTable(con%2C%20irisTableName)%0AdbListFields(con%2C%20irisTableName)%0A%0A%23%20query%20iris%20table%20using%20dplyr%0Airistbl%20%26lt%3B-%20tbl(con%2C%20irisTableName)%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%2Fgetting-started-with-postgresql-in-r%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGetting%20started%20with%20PostgreSQL%20in%20R%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.win-vector.com%2Fblog%2F2016%2F02%2Fusing-postgresql-in-r%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EUsing%20PostgreSQL%20in%20R%3A%20A%20quick%20how-to%3C%2FA%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%2Fr-and-postgresql-using-rpostgresql-and-sqldf%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ER%20and%20PostgreSQL%20%E2%80%93%20using%20RPostgreSQL%20and%20sqldf%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-805676%22%20slang%3D%22en-US%22%3E%3CP%3ER%20%2B%20Azure%20Database%20for%20PostgreSQL%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20886px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127040iFE4927679C4E0064%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22RPostgres.jpg%22%20title%3D%22RPostgres.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805676%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epostgresql%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 PostgreSQL and R can be used together for data analysis – PostgreSQL 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.

 

RPostgres.jpg

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

 

  • Create Azure Database for PostgreSQL using AzureRMR package
  • Connect to Azure Database for PostgreSQL using RPostgres package
  • Create databases and tables
  • Load data from dataframe into a table
  • Query data from table using dplyr grammar
  • Visualize data from table using ggplot2
  • Delete table, database and Azure Database for PostgreSQL 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", "RPostgres", "tidyverse", "curl", "fun")
ipak(packages)

# Create Azure Database for PostgreSQL using AzureRMR package
subscriptionId <- "ffffffff-ffff-ffff-ffff-ffffffffffff"
resourceGroup <- "test_group"
location <- "southcentralus"
pgUserName <- "azureuser"
pgPassword <- random_password(length = 12, replace = FALSE, extended = FALSE)
pgServerName <- "testserver"

az <- create_azure_login()
sub <- az$get_subscription(subscriptionId)
rg <- sub$create_resource_group(resourceGroup, location)
parameters <- jsonlite::toJSON(list(
  administratorLogin=list(value=pgUserName),
  administratorLoginPassword=list(value=pgPassword),
  location=list(value=location),
  serverName=list(value=pgServerName),
  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="10"),
  backupRetentionDays=list(value=7),
  geoRedundantBackup=list(value="Disabled")
), auto_unbox=TRUE)
template <- "https://raw.githubusercontent.com/Azure/azure-postgresql/master/arm-templates/ExampleWithFirewallRule/template.json"
vm_tpl <- rg$deploy_template("myNewPostgreSQLServer",
                             template=template,
                             parameters=parameters,
                             wait=TRUE)

# Connect to Azure Database for PostgreSQL
con <- dbConnect(RPostgres::Postgres(),
                 host= paste0(pgServerName, ".postgres.database.azure.com"),
                 dbname="postgres",
                 user=paste0(pgUserName, "@", pgServerName),
                 password=pgPassword)

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

# connect to iris database
con <- dbConnect(RPostgres::Postgres(),
                 host= paste0(pgServerName, ".postgres.database.azure.com"),
                 dbname=irisTableName,
                 user=paste0(pgUserName, "@", pgServerName),
                 password=pgPassword)

# create table iris and load data from iris dataframe
dbCreateTable(con, irisTableName, iris)
dbAppendTable(con, irisTableName, iris)
dbReadTable(con, irisTableName)
dbListFields(con, irisTableName)

# query iris table using dplyr
iristbl <- tbl(con, irisTableName)
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 : 

 

Getting started with PostgreSQL in R

 

Using PostgreSQL in R: A quick how-to

 

R and PostgreSQL – using RPostgreSQL and sqldf