Re: update specifc rows to DB using sqlContext

[Peyman Mohajerian]

Alternatively, you should be able to write to a new table and use trigger
or some other mechanism to update the particular row. I don't have any
experience with this myself but just looking at this documentation:
https://docs.cloud.databricks.com/docs/latest/databricks_guide/index.html#03%20Data%20Sources/5%20Databases%20%26%20Other%20Data%20Sources/2%20JDBC%20for%20SQL%20Databases.html



On Thu, Aug 11, 2016 at 4:14 AM, Mich Talebzadeh 
wrote:

> in that case one alternative would be to save the new table on hdfs and
> then using some simple ETL load it  into a staging table in MySQL and
> update the original table from staging table
>
> The whole thing can be done in a shell script.
>
> HTH
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 11 August 2016 at 11:52, sujeet jog  wrote:
>
>> I read the table via spark SQL , and perform some  ML activity on the
>> data , and the resultant will be to update some specific columns with the
>> ML improvised result,
>> hence i do not have a option to do the whole operation in MySQL,
>>
>>
>> Thanks,
>> Sujeet
>>
>> On Thu, Aug 11, 2016 at 3:29 PM, Mich Talebzadeh <
>> mich.talebza...@gmail.com> wrote:
>>
>>> Ok it is clearer now.
>>>
>>> You are using Spark as the query tool on an RDBMS table? Read table via
>>> JDBC, write back updating certain records.
>>>
>>> I have not done this myself but I suspect the issue would be if Spark
>>> write will commit the transaction and maintains ACID compliance. (locking
>>> the rows etc).
>>>
>>> I know it cannot do this to a Hive transactional table.
>>>
>>> Any reason why you are not doing the whole operation in MySQL itself?
>>>
>>> HTH
>>>
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * 
>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> *
>>>
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>> any loss, damage or destruction of data or any other property which may
>>> arise from relying on this email's technical content is explicitly
>>> disclaimed. The author will in no case be liable for any monetary damages
>>> arising from such loss, damage or destruction.
>>>
>>>
>>>
>>> On 11 August 2016 at 10:46, sujeet jog  wrote:
>>>
 1 ) using mysql DB
 2 ) will be inserting/update/overwrite to the same table
 3 ) i want to update a specific column in a record, the data is read
 via Spark SQL,

 on the below table which is read via sparkSQL, i would like to update
 the NumOfSamples column .

 consider DF as the dataFrame which holds the records,  registered as
 temporary table MS .

 spark.sqlContext.write.format("jdbc").option("url", url
 ).option("dbtable", "update ms  set NumOfSamples = 20 where 'TimeSeriesID =
 '1000'" As MS ).save

 I believe updating a record via sparkSQL is not supported,  the only
 workaround is to open up a jdbc connection without using spark API's and do
 a direct update ?..

 Sample Ex : -

 mysql> show columns from ms;
 +--+-+--+-+-+---+
 | Field| Type| Null | Key | Default | Extra |
 +--+-+--+-+-+---+
 | TimeSeriesID | varchar(20) | YES  | | NULL|   |
 | NumOfSamples | int(11) | YES  | | NULL|   |
 +--+-+--+-+-+---+


 Thanks,
 Sujeet



 On Tue, Aug 9, 2016 at 6:31 PM, Mich Talebzadeh <
 mich.talebza...@gmail.com> wrote:

> Hi,
>
>
>1. what is the underlying DB, say Hive etc
>2. Is table transactional or you are going to do insert/overwrite
>to the same table
>3. can you do all this in the database itself assuming it is an
>RDBMS
>4. Can you provide the sql or pseudo code for such an update
>
>
> HTH
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> 

Re: update specifc rows to DB using sqlContext

[Mich Talebzadeh]

in that case one alternative would be to save the new table on hdfs and
then using some simple ETL load it  into a staging table in MySQL and
update the original table from staging table

The whole thing can be done in a shell script.

HTH

Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



http://talebzadehmich.wordpress.com


*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.



On 11 August 2016 at 11:52, sujeet jog  wrote:

> I read the table via spark SQL , and perform some  ML activity on the data
> , and the resultant will be to update some specific columns with the ML
> improvised result,
> hence i do not have a option to do the whole operation in MySQL,
>
>
> Thanks,
> Sujeet
>
> On Thu, Aug 11, 2016 at 3:29 PM, Mich Talebzadeh <
> mich.talebza...@gmail.com> wrote:
>
>> Ok it is clearer now.
>>
>> You are using Spark as the query tool on an RDBMS table? Read table via
>> JDBC, write back updating certain records.
>>
>> I have not done this myself but I suspect the issue would be if Spark
>> write will commit the transaction and maintains ACID compliance. (locking
>> the rows etc).
>>
>> I know it cannot do this to a Hive transactional table.
>>
>> Any reason why you are not doing the whole operation in MySQL itself?
>>
>> HTH
>>
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>> On 11 August 2016 at 10:46, sujeet jog  wrote:
>>
>>> 1 ) using mysql DB
>>> 2 ) will be inserting/update/overwrite to the same table
>>> 3 ) i want to update a specific column in a record, the data is read via
>>> Spark SQL,
>>>
>>> on the below table which is read via sparkSQL, i would like to update
>>> the NumOfSamples column .
>>>
>>> consider DF as the dataFrame which holds the records,  registered as
>>> temporary table MS .
>>>
>>> spark.sqlContext.write.format("jdbc").option("url", url
>>> ).option("dbtable", "update ms  set NumOfSamples = 20 where 'TimeSeriesID =
>>> '1000'" As MS ).save
>>>
>>> I believe updating a record via sparkSQL is not supported,  the only
>>> workaround is to open up a jdbc connection without using spark API's and do
>>> a direct update ?..
>>>
>>> Sample Ex : -
>>>
>>> mysql> show columns from ms;
>>> +--+-+--+-+-+---+
>>> | Field| Type| Null | Key | Default | Extra |
>>> +--+-+--+-+-+---+
>>> | TimeSeriesID | varchar(20) | YES  | | NULL|   |
>>> | NumOfSamples | int(11) | YES  | | NULL|   |
>>> +--+-+--+-+-+---+
>>>
>>>
>>> Thanks,
>>> Sujeet
>>>
>>>
>>>
>>> On Tue, Aug 9, 2016 at 6:31 PM, Mich Talebzadeh <
>>> mich.talebza...@gmail.com> wrote:
>>>
 Hi,


1. what is the underlying DB, say Hive etc
2. Is table transactional or you are going to do insert/overwrite
to the same table
3. can you do all this in the database itself assuming it is an
RDBMS
4. Can you provide the sql or pseudo code for such an update


 HTH

 Dr Mich Talebzadeh



 LinkedIn * 
 https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
 *



 http://talebzadehmich.wordpress.com


 *Disclaimer:* Use it at your own risk. Any and all responsibility for
 any loss, damage or destruction of data or any other property which may
 arise from relying on this email's technical content is explicitly
 disclaimed. The author will in no case be liable for any monetary damages
 arising from such loss, damage or destruction.



 On 9 August 2016 at 13:39, sujeet jog  wrote:

> Hi,
>
> Is it possible to update certain columnr records  in DB  from spark,
>
> for example i have 10 rows with 3 columns  which are read 

Re: update specifc rows to DB using sqlContext

[sujeet jog]

I read the table via spark SQL , and perform some  ML activity on the data
, and the resultant will be to update some specific columns with the ML
improvised result,
hence i do not have a option to do the whole operation in MySQL,


Thanks,
Sujeet

On Thu, Aug 11, 2016 at 3:29 PM, Mich Talebzadeh 
wrote:

> Ok it is clearer now.
>
> You are using Spark as the query tool on an RDBMS table? Read table via
> JDBC, write back updating certain records.
>
> I have not done this myself but I suspect the issue would be if Spark
> write will commit the transaction and maintains ACID compliance. (locking
> the rows etc).
>
> I know it cannot do this to a Hive transactional table.
>
> Any reason why you are not doing the whole operation in MySQL itself?
>
> HTH
>
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 11 August 2016 at 10:46, sujeet jog  wrote:
>
>> 1 ) using mysql DB
>> 2 ) will be inserting/update/overwrite to the same table
>> 3 ) i want to update a specific column in a record, the data is read via
>> Spark SQL,
>>
>> on the below table which is read via sparkSQL, i would like to update the
>> NumOfSamples column .
>>
>> consider DF as the dataFrame which holds the records,  registered as
>> temporary table MS .
>>
>> spark.sqlContext.write.format("jdbc").option("url", url
>> ).option("dbtable", "update ms  set NumOfSamples = 20 where 'TimeSeriesID =
>> '1000'" As MS ).save
>>
>> I believe updating a record via sparkSQL is not supported,  the only
>> workaround is to open up a jdbc connection without using spark API's and do
>> a direct update ?..
>>
>> Sample Ex : -
>>
>> mysql> show columns from ms;
>> +--+-+--+-+-+---+
>> | Field| Type| Null | Key | Default | Extra |
>> +--+-+--+-+-+---+
>> | TimeSeriesID | varchar(20) | YES  | | NULL|   |
>> | NumOfSamples | int(11) | YES  | | NULL|   |
>> +--+-+--+-+-+---+
>>
>>
>> Thanks,
>> Sujeet
>>
>>
>>
>> On Tue, Aug 9, 2016 at 6:31 PM, Mich Talebzadeh <
>> mich.talebza...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>>
>>>1. what is the underlying DB, say Hive etc
>>>2. Is table transactional or you are going to do insert/overwrite to
>>>the same table
>>>3. can you do all this in the database itself assuming it is an RDBMS
>>>4. Can you provide the sql or pseudo code for such an update
>>>
>>>
>>> HTH
>>>
>>> Dr Mich Talebzadeh
>>>
>>>
>>>
>>> LinkedIn * 
>>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> *
>>>
>>>
>>>
>>> http://talebzadehmich.wordpress.com
>>>
>>>
>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>> any loss, damage or destruction of data or any other property which may
>>> arise from relying on this email's technical content is explicitly
>>> disclaimed. The author will in no case be liable for any monetary damages
>>> arising from such loss, damage or destruction.
>>>
>>>
>>>
>>> On 9 August 2016 at 13:39, sujeet jog  wrote:
>>>
 Hi,

 Is it possible to update certain columnr records  in DB  from spark,

 for example i have 10 rows with 3 columns  which are read from Spark
 SQL,

 i want to update specific column entries  and write back to DB, but
 since RDD"s are immutable i believe this would be difficult, is there a
 workaround.


 Thanks,
 Sujeet

>>>
>>>
>>
>

Re: update specifc rows to DB using sqlContext

[Mich Talebzadeh]

Ok it is clearer now.

You are using Spark as the query tool on an RDBMS table? Read table via
JDBC, write back updating certain records.

I have not done this myself but I suspect the issue would be if Spark write
will commit the transaction and maintains ACID compliance. (locking the
rows etc).

I know it cannot do this to a Hive transactional table.

Any reason why you are not doing the whole operation in MySQL itself?

HTH


Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



http://talebzadehmich.wordpress.com


*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.



On 11 August 2016 at 10:46, sujeet jog  wrote:

> 1 ) using mysql DB
> 2 ) will be inserting/update/overwrite to the same table
> 3 ) i want to update a specific column in a record, the data is read via
> Spark SQL,
>
> on the below table which is read via sparkSQL, i would like to update the
> NumOfSamples column .
>
> consider DF as the dataFrame which holds the records,  registered as
> temporary table MS .
>
> spark.sqlContext.write.format("jdbc").option("url", url
> ).option("dbtable", "update ms  set NumOfSamples = 20 where 'TimeSeriesID =
> '1000'" As MS ).save
>
> I believe updating a record via sparkSQL is not supported,  the only
> workaround is to open up a jdbc connection without using spark API's and do
> a direct update ?..
>
> Sample Ex : -
>
> mysql> show columns from ms;
> +--+-+--+-+-+---+
> | Field| Type| Null | Key | Default | Extra |
> +--+-+--+-+-+---+
> | TimeSeriesID | varchar(20) | YES  | | NULL|   |
> | NumOfSamples | int(11) | YES  | | NULL|   |
> +--+-+--+-+-+---+
>
>
> Thanks,
> Sujeet
>
>
>
> On Tue, Aug 9, 2016 at 6:31 PM, Mich Talebzadeh  > wrote:
>
>> Hi,
>>
>>
>>1. what is the underlying DB, say Hive etc
>>2. Is table transactional or you are going to do insert/overwrite to
>>the same table
>>3. can you do all this in the database itself assuming it is an RDBMS
>>4. Can you provide the sql or pseudo code for such an update
>>
>>
>> HTH
>>
>> Dr Mich Talebzadeh
>>
>>
>>
>> LinkedIn * 
>> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>> *
>>
>>
>>
>> http://talebzadehmich.wordpress.com
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>> On 9 August 2016 at 13:39, sujeet jog  wrote:
>>
>>> Hi,
>>>
>>> Is it possible to update certain columnr records  in DB  from spark,
>>>
>>> for example i have 10 rows with 3 columns  which are read from Spark
>>> SQL,
>>>
>>> i want to update specific column entries  and write back to DB, but
>>> since RDD"s are immutable i believe this would be difficult, is there a
>>> workaround.
>>>
>>>
>>> Thanks,
>>> Sujeet
>>>
>>
>>
>

Re: update specifc rows to DB using sqlContext

[sujeet jog]

1 ) using mysql DB
2 ) will be inserting/update/overwrite to the same table
3 ) i want to update a specific column in a record, the data is read via
Spark SQL,

on the below table which is read via sparkSQL, i would like to update the
NumOfSamples column .

consider DF as the dataFrame which holds the records,  registered as
temporary table MS .

spark.sqlContext.write.format("jdbc").option("url", url ).option("dbtable",
"update ms  set NumOfSamples = 20 where 'TimeSeriesID = '1000'" As MS ).save

I believe updating a record via sparkSQL is not supported,  the only
workaround is to open up a jdbc connection without using spark API's and do
a direct update ?..

Sample Ex : -

mysql> show columns from ms;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| TimeSeriesID | varchar(20) | YES  | | NULL|   |
| NumOfSamples | int(11) | YES  | | NULL|   |
+--+-+--+-+-+---+


Thanks,
Sujeet



On Tue, Aug 9, 2016 at 6:31 PM, Mich Talebzadeh 
wrote:

> Hi,
>
>
>1. what is the underlying DB, say Hive etc
>2. Is table transactional or you are going to do insert/overwrite to
>the same table
>3. can you do all this in the database itself assuming it is an RDBMS
>4. Can you provide the sql or pseudo code for such an update
>
>
> HTH
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn * 
> https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> *
>
>
>
> http://talebzadehmich.wordpress.com
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
> On 9 August 2016 at 13:39, sujeet jog  wrote:
>
>> Hi,
>>
>> Is it possible to update certain columnr records  in DB  from spark,
>>
>> for example i have 10 rows with 3 columns  which are read from Spark SQL,
>>
>> i want to update specific column entries  and write back to DB, but since
>> RDD"s are immutable i believe this would be difficult, is there a
>> workaround.
>>
>>
>> Thanks,
>> Sujeet
>>
>
>

Re: update specifc rows to DB using sqlContext

[Mich Talebzadeh]

Hi,


   1. what is the underlying DB, say Hive etc
   2. Is table transactional or you are going to do insert/overwrite to the
   same table
   3. can you do all this in the database itself assuming it is an RDBMS
   4. Can you provide the sql or pseudo code for such an update


HTH

Dr Mich Talebzadeh



LinkedIn * 
https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
*



http://talebzadehmich.wordpress.com


*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.



On 9 August 2016 at 13:39, sujeet jog  wrote:

> Hi,
>
> Is it possible to update certain columnr records  in DB  from spark,
>
> for example i have 10 rows with 3 columns  which are read from Spark SQL,
>
> i want to update specific column entries  and write back to DB, but since
> RDD"s are immutable i believe this would be difficult, is there a
> workaround.
>
>
> Thanks,
> Sujeet
>

update specifc rows to DB using sqlContext

[sujeet jog]

Hi,

Is it possible to update certain columnr records  in DB  from spark,

for example i have 10 rows with 3 columns  which are read from Spark SQL,

i want to update specific column entries  and write back to DB, but since
RDD"s are immutable i believe this would be difficult, is there a
workaround.


Thanks,
Sujeet