spark-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Peyman Mohajerian <mohaj...@gmail.com>
Subject Re: update specifc rows to DB using sqlContext
Date Thu, 11 Aug 2016 15:21:02 GMT
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 <mich.talebzadeh@gmail.com>
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=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> 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 <sujeet.jog@gmail.com> 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.talebzadeh@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=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>
>>>
>>>
>>> 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 <sujeet.jog@gmail.com> 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.talebzadeh@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=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>>>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>>>>>
>>>>>
>>>>>
>>>>> 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 <sujeet.jog@gmail.com> 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
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>

Mime
View raw message