save DF to JDBC
http://spark.apache.org/docs/latest/sql-programming-guide.html#jdbc-to-other-databases Spark JDBC can read data from JDBC, but can it save back to JDBC? Like to an Oracle database through its jdbc driver. Also looked at SQL Context documentation https://spark.apache.org/docs/1.4.0/api/java/org/apache/spark/sql/SQLContext.html and can't find anything relevant. Thanks! -- Ruslan Dautkhanov
RE: save DF to JDBC
I’ve gotten it to work with SQL Server (with limitations; it’s buggy and doesn’t work with some types/operations). https://spark.apache.org/docs/latest/api/java/org/apache/spark/sql/DataFrameWriter.html is the Java API you are looking for; the JDBC method lets you write to JDBC databases. I haven’t tried Oracle database, but I would expect it to work at least somewhat. From: Ruslan Dautkhanov [mailto:dautkha...@gmail.com] Sent: Monday, October 05, 2015 2:44 PM To: user <user@spark.apache.org> Subject: save DF to JDBC http://spark.apache.org/docs/latest/sql-programming-guide.html#jdbc-to-other-databases Spark JDBC can read data from JDBC, but can it save back to JDBC? Like to an Oracle database through its jdbc driver. Also looked at SQL Context documentation https://spark.apache.org/docs/1.4.0/api/java/org/apache/spark/sql/SQLContext.html and can't find anything relevant. Thanks! -- Ruslan Dautkhanov
Re: save DF to JDBC
Hi Ruslan,
Here is some sample code which writes a DataFrame to a table in a Derby
database:
import org.apache.spark.sql._
import org.apache.spark.sql.types._
val binaryVal = Array[Byte] ( 1, 2, 3, 4 )
val timestampVal = java.sql.Timestamp.valueOf("1996-01-01 03:30:36")
val dateVal = java.sql.Date.valueOf("1996-01-01")
val allTypes = sc.parallelize(
Array(
(1,
1.toLong,
1.toDouble,
1.toFloat,
1.toShort,
1.toByte,
"true".toBoolean,
"one ring to rule them all",
binaryVal,
timestampVal,
dateVal,
BigDecimal.valueOf(42549.12)
)
)).toDF(
"int_col",
"long_col",
"double_col",
"float_col",
"short_col",
"byte_col",
"boolean_col",
"string_col",
"binary_col",
"timestamp_col",
"date_col",
"decimal_col"
)
val properties = new java.util.Properties()
allTypes.write.jdbc("jdbc:derby:/Users/rhillegas/derby/databases/derby1",
"all_spark_types", properties)
Hope this helps,
Rick Hillegas
STSM, IBM Analytics, Platform - IBM USA
Ruslan Dautkhanov <dautkha...@gmail.com> wrote on 10/05/2015 02:44:20 PM:
> From: Ruslan Dautkhanov <dautkha...@gmail.com>
> To: user <user@spark.apache.org>
> Date: 10/05/2015 02:45 PM
> Subject: save DF to JDBC
>
> http://spark.apache.org/docs/latest/sql-programming-guide.html#jdbc-
> to-other-databases
>
> Spark JDBC can read data from JDBC, but can it save back to JDBC?
> Like to an Oracle database through its jdbc driver.
>
> Also looked at SQL Context documentation
> https://spark.apache.org/docs/1.4.0/api/java/org/apache/spark/sql/
> SQLContext.html
> and can't find anything relevant.
>
> Thanks!
>
>
> --
> Ruslan Dautkhanov
Re: save DF to JDBC
Thank you Richard and Matthew.
DataFrameWriter first appeared in Spark 1.4. Sorry, I should have mentioned
earlier, we're on CDH 5.4 / Spark 1.3. No options for this version?
Best regards,
Ruslan Dautkhanov
On Mon, Oct 5, 2015 at 4:00 PM, Richard Hillegas <rhil...@us.ibm.com> wrote:
> Hi Ruslan,
>
> Here is some sample code which writes a DataFrame to a table in a Derby
> database:
>
> import org.apache.spark.sql._
> import org.apache.spark.sql.types._
>
> val binaryVal = Array[Byte] ( 1, 2, 3, 4 )
> val timestampVal = java.sql.Timestamp.valueOf("1996-01-01 03:30:36")
> val dateVal = java.sql.Date.valueOf("1996-01-01")
>
> val allTypes = sc.parallelize(
> Array(
> (1,
> 1.toLong,
> 1.toDouble,
> 1.toFloat,
> 1.toShort,
> 1.toByte,
> "true".toBoolean,
> "one ring to rule them all",
> binaryVal,
> timestampVal,
> dateVal,
> BigDecimal.valueOf(42549.12)
> )
> )).toDF(
> "int_col",
> "long_col",
> "double_col",
> "float_col",
> "short_col",
> "byte_col",
> "boolean_col",
> "string_col",
> "binary_col",
> "timestamp_col",
> "date_col",
> "decimal_col"
> )
>
> val properties = new java.util.Properties()
>
> allTypes.write.jdbc("jdbc:derby:/Users/rhillegas/derby/databases/derby1",
> "all_spark_types", properties)
>
> Hope this helps,
>
> Rick Hillegas
> STSM, IBM Analytics, Platform - IBM USA
>
>
> Ruslan Dautkhanov <dautkha...@gmail.com> wrote on 10/05/2015 02:44:20 PM:
>
> > From: Ruslan Dautkhanov <dautkha...@gmail.com>
> > To: user <user@spark.apache.org>
> > Date: 10/05/2015 02:45 PM
> > Subject: save DF to JDBC
> >
> > http://spark.apache.org/docs/latest/sql-programming-guide.html#jdbc-
> > to-other-databases
> >
> > Spark JDBC can read data from JDBC, but can it save back to JDBC?
> > Like to an Oracle database through its jdbc driver.
> >
> > Also looked at SQL Context documentation
> > https://spark.apache.org/docs/1.4.0/api/java/org/apache/spark/sql/
> > SQLContext.html
> > and can't find anything relevant.
> >
> > Thanks!
> >
> >
> > --
> > Ruslan Dautkhanov
>
