Passing binding variable in query used in Data Source API

[satish chandra j <js...@gmail.com>]

Hi All,

We have requirement to fetch data from source PostgreSQL database as per a
condition, hence need to pass a binding variable in query used in Data
Source API as below:


var DeptNbr = 10

val dataSource_dF=cc.load("jdbc",Map("url"->"jdbc:postgresql://
10.00.00.000:5432/db_test?user=username&password=password","driver"->"org.postgresql.Driver","dbtable"->"(select*
from schema.table1 where dept_number=DeptNbr) as table1"))


But it errors saying expected ';' but found '='


Note: As it is an iterative approach hence cannot use constants but need to
pass variable to query


If anybody had a similar implementation to pass binding variable while
fetching data from source database using Data Source than please provide
details on the same


Regards,

Satish Chandra

Re: Passing binding variable in query used in Data Source API

[Kevin Mellott <ke...@gmail.com>]

Another alternative that you can consider is to use Sqoop
<http://sqoop.apache.org/> to move your data from PostgreSQL to HDFS, and
then just load it into your DataFrame without needing to use JDBC drivers.
I've had success using this approach, and depending on your setup you can
easily manage/schedule this type of workflow using a tool like Oozie
<http://oozie.apache.org/>.

On Thu, Jan 21, 2016 at 8:34 AM, Todd Nist <ts...@gmail.com> wrote:

> Hi Satish,
>
> You should be able to do something like this:
>
>    val props = new java.util.Properties()
>    props.put("user", username)
>    props.put("password",pwd)
>    props.put("driver", "org.postgresql.Drive")
>    val deptNo = 10
>    val where = Some(s"dept_number = $deptNo")
>    val df = sqlContext.read.jdbc("jdbc:postgresql://
> 10.00.00.000:5432/db_test?user=username&password=password
> <http://10.0.0.0:5432/db_test?user=username&password=password>", "
> schema.table1", Array(where.getOrElse("")), props)
>
> or just add the fillter to your query like this and I believe these should
> get pushed down.
>
>   val df = sqlContext.read
>     .format("jdbc")
>     .option("url", "jdbc:postgresql://
> 10.00.00.000:5432/db_test?user=username&password=password
> <http://10.0.0.0:5432/db_test?user=username&password=password>")
>     .option("user", username)
>     .option("password", pwd)
>     .option("driver", "org.postgresql.Driver")
>     .option("dbtable", "schema.table1")
>     .load().filter('dept_number === $deptNo)
>
> This is form the top of my head and the code has not been tested or
> compiled.
>
> HTH.
>
> -Todd
>
>
> On Thu, Jan 21, 2016 at 6:02 AM, satish chandra j <
> jsatishchandra@gmail.com> wrote:
>
>> Hi All,
>>
>> We have requirement to fetch data from source PostgreSQL database as per
>> a condition, hence need to pass a binding variable in query used in Data
>> Source API as below:
>>
>>
>> var DeptNbr = 10
>>
>> val dataSource_dF=cc.load("jdbc",Map("url"->"jdbc:postgresql://
>> 10.00.00.000:5432/db_test?user=username&password=password","driver"->"org.postgresql.Driver","dbtable"->"(select*
>> from schema.table1 where dept_number=DeptNbr) as table1"))
>>
>>
>> But it errors saying expected ';' but found '='
>>
>>
>> Note: As it is an iterative approach hence cannot use constants but need
>> to pass variable to query
>>
>>
>> If anybody had a similar implementation to pass binding variable while
>> fetching data from source database using Data Source than please provide
>> details on the same
>>
>>
>> Regards,
>>
>> Satish Chandra
>>
>
>

Re: Passing binding variable in query used in Data Source API

[Todd Nist <ts...@gmail.com>]

Hi Satish,

You should be able to do something like this:

   val props = new java.util.Properties()
   props.put("user", username)
   props.put("password",pwd)
   props.put("driver", "org.postgresql.Drive")
   val deptNo = 10
   val where = Some(s"dept_number = $deptNo")
   val df = sqlContext.read.jdbc("jdbc:postgresql://
10.00.00.000:5432/db_test?user=username&password=password
<http://10.0.0.0:5432/db_test?user=username&password=password>", "
schema.table1", Array(where.getOrElse("")), props)

or just add the fillter to your query like this and I believe these should
get pushed down.

  val df = sqlContext.read
    .format("jdbc")
    .option("url", "jdbc:postgresql://
10.00.00.000:5432/db_test?user=username&password=password
<http://10.0.0.0:5432/db_test?user=username&password=password>")
    .option("user", username)
    .option("password", pwd)
    .option("driver", "org.postgresql.Driver")
    .option("dbtable", "schema.table1")
    .load().filter('dept_number === $deptNo)

This is form the top of my head and the code has not been tested or
compiled.

HTH.

-Todd


On Thu, Jan 21, 2016 at 6:02 AM, satish chandra j <js...@gmail.com>
wrote:

> Hi All,
>
> We have requirement to fetch data from source PostgreSQL database as per a
> condition, hence need to pass a binding variable in query used in Data
> Source API as below:
>
>
> var DeptNbr = 10
>
> val dataSource_dF=cc.load("jdbc",Map("url"->"jdbc:postgresql://
> 10.00.00.000:5432/db_test?user=username&password=password","driver"->"org.postgresql.Driver","dbtable"->"(select*
> from schema.table1 where dept_number=DeptNbr) as table1"))
>
>
> But it errors saying expected ';' but found '='
>
>
> Note: As it is an iterative approach hence cannot use constants but need
> to pass variable to query
>
>
> If anybody had a similar implementation to pass binding variable while
> fetching data from source database using Data Source than please provide
> details on the same
>
>
> Regards,
>
> Satish Chandra
>