topic Re: can merge transformation be applied to hive table? in SAS Data Management

can merge transformation be applied to hive table?

Sheeba — Wed, 27 Sep 2017 19:27:15 GMT

Hi,

Can a merge transformation be applied to a hive table using a sas dataset?

Appreciate any help.

Regards,
Sheeba

Re: can merge transformation be applied to hive table?

LinusH — Thu, 28 Sep 2017 15:09:47 GMT

Yes, but the merge operation will take place in SAS, not in Hive.

Re: can merge transformation be applied to hive table?

Sheeba — Thu, 28 Sep 2017 21:09:30 GMT

Hi Linush,

Thanks for the reply.

I believe it wouldnt impact the data right.

I am looking for inserting new rows and updating existing rows which is same as what merge transformation does. i Have used it to update sas datasets but i have not tried this to update hive tables.. Is there any other way to do an insert and update to a hive table using sas DI studio?

Regards,

Sheeba

Re: can merge transformation be applied to hive table?

LinusH — Fri, 29 Sep 2017 14:56:40 GMT

data step merge does not update/insert data at rest, it's a type of query that results in a new data set.

So from a data management/ETL perspective I would say this is a no-no.

There are ready made transformations for dealing with target table updates, select the one appropriate to your logic.

Sounds like that you need help from an ETL designer...?

Re: can merge transformation be applied to hive table?

Sheeba — Mon, 02 Oct 2017 18:22:40 GMT

Hi Linush,

tnx a lot for the details.

I will look into the transformations which does the target table updates.

Regards,

Sheeba

Re: can merge transformation be applied to hive table?

LinusH — Tue, 03 Oct 2017 12:34:48 GMT

Bare in mind that Hive have limited set of methods for update (none until recently). So be sure to align your ETL with Hive capabilities.

Re: can merge transformation be applied to hive table?

Patrick — Tue, 03 Oct 2017 20:26:19 GMT

@Sheeba

Given that Hadoop is only good at Insert (Append) and Read you might also consider a strategy as outlined here:

https://hortonworks.com/blog/four-step-strategy-incremental-updates-hive/

Re: can merge transformation be applied to hive table?

Sheeba — Tue, 03 Oct 2017 22:29:50 GMT

Hi Linush,

Thanks. Sure I will check if it is supported by SAS DI.

Regards,

Sheeba

Re: can merge transformation be applied to hive table?

Sheeba — Tue, 03 Oct 2017 22:31:02 GMT

Hi Patrick,

Thanks a lot for the link. It is really helpful.

I will try to deep dive into it and follow it.

Regards,

Sheeba