Actually you are correct. It will be considered a non logged operation that probably they (the DBAs) won't allow it in production. The only option for the thread owner is to perform smaller batches with frequent commits in MSSQL
Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com On 20 April 2016 at 21:52, Strange, Nick <nick.stra...@fmr.com> wrote: > Nologging means no redo log is generated (or minimal redo). However undo > is still generated and the transaction will still be rolled back in the > event of an issue. > > > > Nick > > > > *From:* Mich Talebzadeh [mailto:mich.talebza...@gmail.com] > *Sent:* Wednesday, April 20, 2016 4:08 PM > *To:* Andrés Ivaldi > *Cc:* user @spark > *Subject:* Re: Spark SQL Transaction > > > > Well Oracle will allow that if the underlying table is in NOLOOGING mode :) > > > > mtale...@mydb12.mich.LOCAL> create table testme(col1 int); > > Table created. > > mtale...@mydb12.mich.LOCAL> *alter table testme NOLOGGING;* > > Table altered. > > mtale...@mydb12.mich.LOCAL> insert into testme values(1); > > 1 row created. > > > Dr Mich Talebzadeh > > > > LinkedIn > *https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > > > > On 20 April 2016 at 20:45, Andrés Ivaldi <iaiva...@gmail.com> wrote: > > I think the same, and I don't think reducing batches size improves speed > but will avoid loosing all data when rollback. > > > > > > Thanks for the help.. > > > > > > On Wed, Apr 20, 2016 at 4:03 PM, Mich Talebzadeh < > mich.talebza...@gmail.com> wrote: > > yep. I think it is not possible to make SQL Server do a non logged > transaction. Other alternative is doing inserts in small batches if > possible. Or write to a CSV type file and use Bulk copy to load the file > into MSSQL with frequent commits like every 50K rows? > > > > > > > Dr Mich Talebzadeh > > > > LinkedIn > *https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > > > > On 20 April 2016 at 19:42, Andrés Ivaldi <iaiva...@gmail.com> wrote: > > Yes, I know that behavior , but there is not explicit Begin Transaction in > my code, so, maybe Spark or the same driver is adding the begin > transaction, or implicit transaction is configured. If spark is'n adding a > Begin transaction on each insertion, then probably is database or Driver > configuration... > > > > On Wed, Apr 20, 2016 at 3:33 PM, Mich Talebzadeh < > mich.talebza...@gmail.com> wrote: > > > You will see what is happening in SQL Server. First create a test table > called testme > > > > 1> use tempdb > 2> go > 1> create table testme(col1 int) > 2> go > > -- Now explicitly begin a transaction and insert 1 row and select from > table > 1> *begin tran* > 2> insert into testme values(1) > 3> select * from testme > 4> go > (1 row affected) > col1 > ----------- > 1 > > -- That value col1=1 is there > > -- > > (1 row affected) > > -- Now rollback that transaction meaning in your case by killing your > Spark process! > > -- > 1> rollback tran > 2> select * from testme > 3> go > col1 > ----------- > > (0 rows affected) > > > > -- You can see that record has gone as it rolled back! > > > > > Dr Mich Talebzadeh > > > > LinkedIn > *https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > > > > On 20 April 2016 at 18:42, Andrés Ivaldi <iaiva...@gmail.com> wrote: > > Sorry I'cant answer before, I want to know if spark is the responsible to > add the Begin Tran, The point is to speed up insertion over losing data, > Disabling Transaction will speed up the insertion and we dont care about > consistency... I'll disable te implicit_transaction and see what happens. > > > > thanks > > > > On Wed, Apr 20, 2016 at 12:09 PM, Mich Talebzadeh < > mich.talebza...@gmail.com> wrote: > > Assuming that you are using JDBC for putting data into any ACID compliant > database (MSSQL, Sybase, Oracle etc), you are implicitly or explicitly > adding BEGIN TRAN to INSERT statement in a distributed transaction. MSSQL > does not know or care where data is coming from. If your connection > completes OK a COMMIT TRAN will be sent and that will tell MSQL to commit > transaction. If yoy kill Spark transaction before MSSQL receive COMMIT > TRAN, the transaction will be rolled back. > > > > The only option is that if you don't care about full data getting to > MSSQL,to break your insert into chunks at source and send data to MSSQL in > small batches. In that way you will not lose all data in MSSQL because of > rollback. > > > > 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 > > > > > > On 20 April 2016 at 07:33, Mich Talebzadeh <mich.talebza...@gmail.com> > wrote: > > Are you using JDBC to push data to MSSQL? > > > Dr Mich Talebzadeh > > > > LinkedIn > *https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > > > > On 19 April 2016 at 23:41, Andrés Ivaldi <iaiva...@gmail.com> wrote: > > I mean local transaction, We've ran a Job that writes into SQLServer then > we killed spark JVM just for testing purpose and we realized that SQLServer > did a rollback. > > > > Regards > > > > On Tue, Apr 19, 2016 at 5:27 PM, Mich Talebzadeh < > mich.talebza...@gmail.com> wrote: > > Hi, > > > > What do you mean by *without transaction*? do you mean forcing SQL Server > to accept a non logged operation? > > > Dr Mich Talebzadeh > > > > LinkedIn > *https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > > > > On 19 April 2016 at 21:18, Andrés Ivaldi <iaiva...@gmail.com> wrote: > > Hello, is possible to execute a SQL write without Transaction? we dont > need transactions to save our data and this adds an overhead to the > SQLServer. > > > > Regards. > > -- > > Ing. Ivaldi Andres > > > > > > > > -- > > Ing. Ivaldi Andres > > > > > > > > > > -- > > Ing. Ivaldi Andres > > > > > > > > > > -- > > Ing. Ivaldi Andres > > > > > > > > -- > > Ing. Ivaldi Andres > > >
