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.
In this article we will learn how to use R to perform the following tasks:
# 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.