RubyPdf Blog

How to Connect Spark with PostgreSQL

After installing SparkR, Let’s try to import data from PostgreSQL,

#tell the path of Spark
Sys.setenv(SPARK_HOME="/Users/steven/Applications/spark2")
.libPaths(c(file.path(Sys.getenv("SPARK_HOME"), "R", "lib"), .libPaths()))
 
library(SparkR)
 
d.pg="org.postgresql:postgresql:9.4.1209.jre7"
sc <- sparkR.session(master="local[2]",sparkPackages=c(d.pg))
 
url<-"jdbc:postgresql://localhost:5432/steven?user=postgres&password="
driver<-"org.postgresql.Driver"
df.pg <- read.jdbc(source="jdbc", url=url, tableName ="public.mtcars",driver=driver)
 
printSchema(df.pg)
 
collect(df.pg)
#createOrReplaceTempView(df.pg,"mtcars")
#sql("select * from mtcars")
 
sparkR.session.stop()

#tell the path of Spark

Sys.setenv(SPARK_HOME="/Users/steven/Applications/spark2")

.libPaths(c(file.path(Sys.getenv("SPARK_HOME"), "R", "lib"), .libPaths()))

library(SparkR)

d.pg="org.postgresql:postgresql:9.4.1209.jre7"

sc <- sparkR.session(master="local[2]",sparkPackages=c(d.pg))

url<-"jdbc:postgresql://localhost:5432/steven?user=postgres&password="

driver<-"org.postgresql.Driver"

df.pg <- read.jdbc(source="jdbc", url=url, tableName ="public.mtcars",driver=driver)

printSchema(df.pg)

collect(df.pg)

#createOrReplaceTempView(df.pg,"mtcars")

#sql("select * from mtcars")

sparkR.session.stop()

the Easiest Way to Install SparkR

Install the last version R Dowload and install Spark（you can choose 1.6.2 or 2.0.0, it is also ok to keep both） Install RStudio(optional) Tell R where spark is installed

Sys.setenv(SPARK_HOME="/Users/steven/Applications/spark2")
.libPaths(c(file.path(Sys.getenv("SPARK_HOME"), "R", "lib"), .libPaths()))
library(SparkR)

Sys.setenv(SPARK_HOME="/Users/steven/Applications/spark2")

.libPaths(c(file.path(Sys.getenv("SPARK_HOME"), "R", "lib"), .libPaths()))

library(SparkR)

Enjoy it( Spark 2.0.0 example)

sc <- sparkR.session(master="local[2]")
df <- createDataFrame(iris)   #R data.frame to Spark DataFrame
createOrReplaceTempView(df,"iris") #register Spark DataFrame as temp view
x<-sql("select * from iris")# test Spark SQL
nrow(x)#
summary(x)

collect(df)#Spark DataFrame to R data.frame
sparkR.session.stop()

sc <- sparkR.session(master="local[2]")

df <- createDataFrame(iris) #R data.frame to Spark DataFrame

createOrReplaceTempView(df,"iris") #register Spark DataFrame as temp view

x<-sql("select * from iris")# test Spark SQL

nrow(x)#

summary(x)

collect(df)#Spark DataFrame to R data.frame

sparkR.session.stop()

reference, 最简便的SparkR安装方法

How to Get the Row numbers of all tables in PostgreSQL Database

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
  ORDER BY n_live_tup DESC;

SELECT schemaname,relname,n_live_tup

FROM pg_stat_user_tables

ORDER BY n_live_tup DESC;

Recently, I imported 25 Excels into PostgreSQL separate tables with R, after imported, I was told there existed two excel files …