can merge transformation be applied to hive table?

[Sheeba]

Hi,

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

Appreciate any help.

Regards,
Sheeba

[LinusH]

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

[Sheeba]

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

[LinusH]

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...?

[Sheeba]

Hi Linush,

tnx a lot for the details.

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

Regards,

Sheeba

[LinusH]

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.

[Sheeba]

Hi Linush,

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

Regards,

Sheeba

[Patrick]

@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/  

[Sheeba]

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