Re: Hive update operation
Hi Mich, Nice explanation! The Update operation in hive work on row by row or it is performed in batches? We also observed multiple temp files getting generated in hdfs while performing the update operation. It will be really helpful if you can share details what hive does in the background. Thanks, Saurabh > On 26-Aug-2016, at 11:47 AM, Mich Talebzadeh > wrote: > > Ok this is what you have in MSSQL (COLLATE) does not come into it in Hive) > > UPDATE table1 > SET > address=regexp_replace(t2.cout_event_description,'''''''','''') > , latitude=t2.latitude > , longitude=t2.longitude > , speed =t2.speed > , dtimestamp =mv.dtimestamp > , reg_no=t2.registration > , gpsstate = t2.bgps > FROM > default.maxvalues mv > , table2 t2 > INNER JOIN table2 t2 on mv.dtimestamp=t2.dtimestamp AND mv.acqnum=t2.acqnum > INNER JOIN table1 t1 on mv.acqnum=t1.deal_number > where t1.deal_number=mv.acqnum; > > Simplify this in Hive and test > > CREATE TEMPORARY TABLE tmp1 > AS > SELECT FROM > table2 t2, default.maxvalues mv > WHERE mv.dtimestamp=t2.dtimestamp AND mv.acqnum=t2.acqnum > > UPDATE table1 > SET > address=regexp_replace(t2.cout_event_description,'''''''','''') > , latitude=tmp1.latitude > , longitude=tmp1.longitude > , speed =tmp1.speed > , dtimestamp =tmp1.dtimestamp > , reg_no=tmp1.registration > , gpsstate = tmp1.bgps > FROM > tmp1, table1 t1 > WHERE tmp1.acqnum = tmp1.deal_number > > 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 26 August 2016 at 06:38, Priyanka Raghuvanshi >> wrote: >> Current RDBMS: SQL Server 2012 >> >> >> Yes, I tried below one. >> >> >> UPDATE table1 set >> address=regexp_replace(t2.cout_event_description,'''''''',''''),latitude=t2.latitude,longitude=t2.longitude >> ,speed =t2.speed,dtimestamp =mv.dtimestamp,reg_no=t2.registration,gpsstate >> = t2.bgps FROM default.maxvalues mv, table2 t2 INNER JOIN table2 t2 on >> mv.dtimestamp=t2.dtimestamp AND mv.acqnum=t2.acqnum INNER JOIN table1 t1 on >> mv.acqnum=t1.deal_number >> where t1.deal_number=mv.acqnum; >> >> OUTPUT: >> >> " FAILED: ParseException line 1:221 missing EOF at 'FROM' near 'bgps' " >> >> >> From: Mich Talebzadeh >> Sent: 25 August 2016 21:41:51 >> >> To: user >> Subject: Re: Fw: Hive update operation >> >> Him >> >> What is your current RDBMS and are these SQL the ones used in RDBMS? >> >> Have you tried them on Hive? >> >> 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 25 August 2016 at 06:56, Priyanka Raghuvanshi >>> wrote: >>> Hi Dr Mich, >>> >>> >>> Thank you for replying. >>> >>> >>> Yes, while creating the table, transactional property has been set as true, >>> same implies to other tables. >>> >>> >>> Following in are SQL update query example, same I want to achieve through >>> HQL: >>> >>> >>> 1) >>> >>> UPDATE table1 >>> SET FAging=t2.FAging, >>> PaymentR=t2.PaymentR, >>> ArrearsO=t2.ArrearsO , >>> IRemaini
Re: Fw: Hive update operation
Ok this is what you have in MSSQL (COLLATE) does not come into it in Hive) UPDATE table1 SET address=regexp_replace(t2.cout_event_description,'''''''','''') , latitude=t2.latitude , longitude=t2.longitude , speed =t2.speed , dtimestamp =mv.dtimestamp , reg_no=t2.registration , gpsstate = t2.bgps FROM default.maxvalues mv , table2 t2 INNER JOIN table2 t2 on mv.dtimestamp=t2.dtimestamp AND mv.acqnum=t2.acqnum INNER JOIN table1 t1 on mv.acqnum=t1.deal_number where t1.deal_number=mv.acqnum; Simplify this in Hive and test CREATE TEMPORARY TABLE tmp1 AS SELECT https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <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 26 August 2016 at 06:38, Priyanka Raghuvanshi wrote: > Current RDBMS: SQL Server 2012 > > > Yes, I tried below one. > > > UPDATE table1 set address=regexp_replace(t2.cout > _event_description,'''''''',''''),latitude=t2.latitude,longitude=t2. > longitude ,speed =t2.speed,dtimestamp =mv.dtimestamp,reg_no=t2.regis > tration,gpsstate = t2.bgps FROM default.maxvalues mv, table2 t2 INNER > JOIN table2 t2 on mv.dtimestamp=t2.dtimestamp AND mv.acqnum=t2.acqnum > INNER JOIN table1 t1 on mv.acqnum=t1.deal_number > where t1.deal_number=mv.acqnum; > > OUTPUT: > > " FAILED: ParseException line 1:221 missing EOF at 'FROM' near 'bgps' " > > > -- > *From:* Mich Talebzadeh > *Sent:* 25 August 2016 21:41:51 > > *To:* user > *Subject:* Re: Fw: Hive update operation > > Him > > What is your current RDBMS and are these SQL the ones used in RDBMS? > > Have you tried them on Hive? > > HTH > > Dr Mich Talebzadeh > > > > LinkedIn * > https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <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 25 August 2016 at 06:56, Priyanka Raghuvanshi > wrote: > >> Hi Dr Mich, >> >> >> Thank you for replying. >> >> >> Yes, while creating the table, transactional property has been set as >> true, same implies to other tables. >> >> >> Following in are SQL update query example, same I want to achieve through >> HQL: >> >> >> 1) >> >> UPDATE table1 >> SET FAging=t2.FAging, >> PaymentR=t2.PaymentR, >> ArrearsO=t2.ArrearsO , >> IRemaining=t2.IRemaining, >> Taxi_Association=t2.TaxiAssociation >> From table2 t2 >> Left JOIN table1 t1 >> ON t2.AccNum COLLATE DATABASE_DEFAULT= t1.AccNo COLLATE DATABASE_DEFAULT >> >> 2) >> >> UPDATE table1 >> SET Img_String=CASE WHEN convert(nvarchar,T1.dTimeStamp,103)=Convert( >> nvarchar,getdate(),103) AND T1.Speed>0 then >> isnull(T2.clmn1,'Other') >> +';Moving;'+ISNULL(T2.PinsStatus,'Performing')+';'+CASE >> WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' THEN 'Refurbished' ELSE >> 'New' END >> ELSE >> isnull(T2.clmn1,'Other') +';Non Moving;'+ISNULL(VEH.PinsStatus >> ,'Performing')+';'+CASE WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' >> THEN 'Refurbished' ELSE 'New' END >> END, >> Moving_or_NonMoving =CASE WHEN convert(nvarchar,T1.dTimeStamp,103) >> =Convert(nvarchar,getdate(),103) AND T1.Speed>0 then >> 'Moving' >> ELSE >> 'Non Moving' >> END, >> InfoWindow= CAST (T1.dTimeStamp as NVARCHAR(20)) + ' ' + [Address] >> +' @ ' + cast(T1.Speed as nvarchar) + ' KMPH
Re: Fw: Hive update operation
Current RDBMS: SQL Server 2012 Yes, I tried below one. UPDATE table1 set address=regexp_replace(t2.cout_event_description,'''''''',''''),latitude=t2.latitude,longitude=t2.longitude ,speed =t2.speed,dtimestamp =mv.dtimestamp,reg_no=t2.registration,gpsstate = t2.bgps FROM default.maxvalues mv, table2 t2 INNER JOIN table2 t2 on mv.dtimestamp=t2.dtimestamp AND mv.acqnum=t2.acqnum INNER JOIN table1 t1 on mv.acqnum=t1.deal_number where t1.deal_number=mv.acqnum; OUTPUT: " FAILED: ParseException line 1:221 missing EOF at 'FROM' near 'bgps' " ____ From: Mich Talebzadeh Sent: 25 August 2016 21:41:51 To: user Subject: Re: Fw: Hive update operation Him What is your current RDBMS and are these SQL the ones used in RDBMS? Have you tried them on Hive? 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 25 August 2016 at 06:56, Priyanka Raghuvanshi mailto:priyan...@winjit.com>> wrote: Hi Dr Mich, Thank you for replying. Yes, while creating the table, transactional property has been set as true, same implies to other tables. Following in are SQL update query example, same I want to achieve through HQL: 1) UPDATE table1 SET FAging=t2.FAging, PaymentR=t2.PaymentR, ArrearsO=t2.ArrearsO , IRemaining=t2.IRemaining, Taxi_Association=t2.TaxiAssociation >From table2 t2 Left JOIN table1 t1 ON t2.AccNum COLLATE DATABASE_DEFAULT= t1.AccNo COLLATE DATABASE_DEFAULT 2) UPDATE table1 SET Img_String=CASE WHEN convert(nvarchar,T1.dTimeStamp,103)=Convert(nvarchar,getdate(),103) AND T1.Speed>0 then isnull(T2.clmn1,'Other') +';Moving;'+ISNULL(T2.PinsStatus,'Performing')+';'+CASE WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' THEN 'Refurbished' ELSE 'New' END ELSE isnull(T2.clmn1,'Other') +';Non Moving;'+ISNULL(VEH.PinsStatus,'Performing')+';'+CASE WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' THEN 'Refurbished' ELSE 'New' END END, Moving_or_NonMoving =CASE WHEN convert(nvarchar,T1.dTimeStamp,103)=Convert(nvarchar,getdate(),103) AND T1.Speed>0 then 'Moving' ELSE 'Non Moving' END, InfoWindow= CAST (T1.dTimeStamp as NVARCHAR(20)) + ' ' + [Address] +' @ ' + cast(T1.Speed as nvarchar) + ' KMPH ['+ isnull(T1.AccountNo,'') +'];'+ isnull(T2.ChassisNumber,'') +';'+ isnull(replace(T2.OwnerName,'''','') ,'') +';'+ isnull(T2.clmn1,''), Status=T2.PinsStatus FROM table2 T2 INNER JOIN table1 T1 on T2.AcqNum=T1.AccoNo Priyanka Raghuvanshi From: Mich Talebzadeh mailto:mich.talebza...@gmail.com>> Sent: 24 August 2016 22:02:58 To: user Subject: Re: Fw: Hive update operation have the underlying table to be updated been defined as transactional? can you give the update example? 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 24 August 2016 at 14:50, Priyanka Raghuvanshi mailto:priyan...@winjit.com>> wrote: From: Priyanka Raghuvanshi Sent: 24 August 2016 19:12 To: user@hive.apache.org<mailto:user@hive.apache.org> Subject: Hive update operation Hi, Is it possible to update a table(ORC) using join or column of another table(ORC)? Kind Regards, Priyanka Raghuvanshi
Re: Fw: Hive update operation
Him What is your current RDBMS and are these SQL the ones used in RDBMS? Have you tried them on Hive? HTH Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <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 25 August 2016 at 06:56, Priyanka Raghuvanshi wrote: > Hi Dr Mich, > > > Thank you for replying. > > > Yes, while creating the table, transactional property has been set as > true, same implies to other tables. > > > Following in are SQL update query example, same I want to achieve through > HQL: > > > 1) > > UPDATE table1 > SET FAging=t2.FAging, > PaymentR=t2.PaymentR, > ArrearsO=t2.ArrearsO , > IRemaining=t2.IRemaining, > Taxi_Association=t2.TaxiAssociation > From table2 t2 > Left JOIN table1 t1 > ON t2.AccNum COLLATE DATABASE_DEFAULT= t1.AccNo COLLATE DATABASE_DEFAULT > > 2) > > UPDATE table1 > SET Img_String=CASE WHEN convert(nvarchar,T1.dTimeStamp,103)=Convert( > nvarchar,getdate(),103) AND T1.Speed>0 then > isnull(T2.clmn1,'Other') +';Moving;'+ISNULL(T2.PinsStatus,'Performing') > +';'+CASE WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' THEN > 'Refurbished' ELSE 'New' END > ELSE > isnull(T2.clmn1,'Other') +';Non Moving;'+ISNULL(VEH.PinsStatus > ,'Performing')+';'+CASE WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' > THEN 'Refurbished' ELSE 'New' END > END, > Moving_or_NonMoving =CASE WHEN convert(nvarchar,T1.dTimeStamp,103)=Convert > (nvarchar,getdate(),103) AND T1.Speed>0 then > 'Moving' > ELSE > 'Non Moving' > END, > InfoWindow= CAST (T1.dTimeStamp as NVARCHAR(20)) + ' ' + [Address] > +' @ ' + cast(T1.Speed as nvarchar) + ' KMPH ['+ isnull(T1.AccountNo,'') > +'];'+ isnull(T2.ChassisNumber,'') +';'+ isnull(replace(T2.OwnerName,'''','') > ,'') +';'+ isnull(T2.clmn1,''), > Status=T2.PinsStatus > > FROM table2 T2 > INNER JOIN table1 T1 on T2.AcqNum=T1.AccoNo > > Priyanka Raghuvanshi > -- > *From:* Mich Talebzadeh > *Sent:* 24 August 2016 22:02:58 > *To:* user > *Subject:* Re: Fw: Hive update operation > > have the underlying table to be updated been defined as transactional? > > can you give the update example? > > Dr Mich Talebzadeh > > > > LinkedIn * > https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <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 24 August 2016 at 14:50, Priyanka Raghuvanshi > wrote: > >> >> >> >> -- >> *From:* Priyanka Raghuvanshi >> *Sent:* 24 August 2016 19:12 >> *To:* user@hive.apache.org >> *Subject:* Hive update operation >> >> >> Hi, >> >> >> Is it possible to update a table(ORC) using join or column of another >> table(ORC)? >> >> >> Kind Regards, >> >> Priyanka Raghuvanshi >> > >
Re: Fw: Hive update operation
Hi Dr Mich, Thank you for replying. Yes, while creating the table, transactional property has been set as true, same implies to other tables. Following in are SQL update query example, same I want to achieve through HQL: 1) UPDATE table1 SET FAging=t2.FAging, PaymentR=t2.PaymentR, ArrearsO=t2.ArrearsO , IRemaining=t2.IRemaining, Taxi_Association=t2.TaxiAssociation >From table2 t2 Left JOIN table1 t1 ON t2.AccNum COLLATE DATABASE_DEFAULT= t1.AccNo COLLATE DATABASE_DEFAULT 2) UPDATE table1 SET Img_String=CASE WHEN convert(nvarchar,T1.dTimeStamp,103)=Convert(nvarchar,getdate(),103) AND T1.Speed>0 then isnull(T2.clmn1,'Other') +';Moving;'+ISNULL(T2.PinsStatus,'Performing')+';'+CASE WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' THEN 'Refurbished' ELSE 'New' END ELSE isnull(T2.clmn1,'Other') +';Non Moving;'+ISNULL(VEH.PinsStatus,'Performing')+';'+CASE WHEN ISNULL(T2.SupplierName,'New') LIKE '%Repo%' THEN 'Refurbished' ELSE 'New' END END, Moving_or_NonMoving =CASE WHEN convert(nvarchar,T1.dTimeStamp,103)=Convert(nvarchar,getdate(),103) AND T1.Speed>0 then 'Moving' ELSE 'Non Moving' END, InfoWindow= CAST (T1.dTimeStamp as NVARCHAR(20)) + ' ' + [Address] +' @ ' + cast(T1.Speed as nvarchar) + ' KMPH ['+ isnull(T1.AccountNo,'') +'];'+ isnull(T2.ChassisNumber,'') +';'+ isnull(replace(T2.OwnerName,'''','') ,'') +';'+ isnull(T2.clmn1,''), Status=T2.PinsStatus FROM table2 T2 INNER JOIN table1 T1 on T2.AcqNum=T1.AccoNo Priyanka Raghuvanshi From: Mich Talebzadeh Sent: 24 August 2016 22:02:58 To: user Subject: Re: Fw: Hive update operation have the underlying table to be updated been defined as transactional? can you give the update example? 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 24 August 2016 at 14:50, Priyanka Raghuvanshi mailto:priyan...@winjit.com>> wrote: From: Priyanka Raghuvanshi Sent: 24 August 2016 19:12 To: user@hive.apache.org<mailto:user@hive.apache.org> Subject: Hive update operation Hi, Is it possible to update a table(ORC) using join or column of another table(ORC)? Kind Regards, Priyanka Raghuvanshi
Re: Fw: Hive update operation
have the underlying table to be updated been defined as transactional? can you give the update example? Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <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 24 August 2016 at 14:50, Priyanka Raghuvanshi wrote: > > > > -- > *From:* Priyanka Raghuvanshi > *Sent:* 24 August 2016 19:12 > *To:* user@hive.apache.org > *Subject:* Hive update operation > > > Hi, > > > Is it possible to update a table(ORC) using join or column of another > table(ORC)? > > > Kind Regards, > > Priyanka Raghuvanshi >
Fw: Hive update operation
From: Priyanka Raghuvanshi Sent: 24 August 2016 19:12 To: user@hive.apache.org Subject: Hive update operation Hi, Is it possible to update a table(ORC) using join or column of another table(ORC)? Kind Regards, Priyanka Raghuvanshi
