Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- %dep
- z.reset() // clean up
- z.load("/data/extraJarFiles/postgresql-9.4.1208.jar") // load a jdbc driver for postgresql
- %spark
- // This code loads some data from a PostGreSql DB with the help of a JDBC driver.
- // The JDBC driver is stored on the Zeppelin server, the necessary Code is transfered to the Spark Workers and the workers build the connection with the DB.
- //
- // The connection between table and data source is "lazy". So the data will only be loaded in the case that an action need them.
- // With the current script means this the DB is queried twice. ==> Q: How can I keep a RDD in Mem or on disk?
- import org.apache.spark.SparkContext
- import org.apache.spark.SparkContext._
- import org.apache.spark.SparkConf
- import org.apache.spark.rdd.JdbcRDD
- import java.sql.Connection
- import java.sql.DriverManager
- import java.sql.ResultSet
- import org.apache.spark.sql.hive._
- import org.apache.spark.sql._
- val url = "jdbc:postgresql://10.222.22.222:5432/myDatabase"
- val username = "postgres"
- val pw = "geheim"
- Class.forName("org.postgresql.Driver").newInstance // activating the jdbc driver. The jar file was loaded inside of the %dep block
- case class RowClass(Id:Integer, Col1:String , Col2:String) // create a class with possible values
- val myRDD = new JdbcRDD(sc, // SparkContext sc
- () => DriverManager.getConnection(url,username,pw), // scala.Function0<java.sql.Connection> getConnection
- "select * from tab1 where "Id">=? and "Id" <=? ", // String sql Important: we need here two '?' for the lower/upper Bounds vlaues
- 0, // long lowerBound = start value
- 10000, // long upperBound, = end value that is still included
- 1, // int numPartitions = the area is spitted into x sub commands.
- // e.g. 0,1000,2 => first cmd from 0 ... 499, second cmd from 500..1000
- row => RowClass(row.getInt("Id"),
- row.getString("Col1"),
- row.getString("Col2"))
- )
- myRDD.toDF().registerTempTable("Tab1")
- // --- improved methode (not working at the moment)----
- val prop = new java.util.Properties
- prop.setProperty("user",username)
- prop.setProperty("password",pw)
- val tab1b = sqlContext.read.jdbc(url,"tab1",prop) // <-- not working
- tab1b.show
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement