topic Re: SAS DI and hive unique key generator in SAS Data Management

SAS DI and hive unique key generator

Sheeba — Tue, 07 Jun 2016 20:39:37 GMT

Hi,

i am trying to load data from file into sas and load the same file into a hive table.

is there anyway i can generate a unique id in hive table at record level? is there any transformaton which helps in unique key generation in hive? Also is it possible to keep the unique keys in synch in both sas and hive?

Regards,

Sheeba

Re: SAS DI and hive unique key generator

jklaverstijn — Tue, 07 Jun 2016 21:18:07 GMT

DI Studio has the Surrogate Key generator. Have a look at the doc. And if you add it to the SAS table which is subsequently loaded into hive, wouldn't they be automatically in sync?

Hope this helps,

-Jan

Re: SAS DI and hive unique key generator

Sheeba — Tue, 07 Jun 2016 21:42:25 GMT

Hi Jan,

Thanks for the reply.

can we use the surrogate key generator for Hive tables as well?

Design is to load the data from file to sas and again from file to hive . I am not loading data from sas to hive.

Regards,

Sheeba

Re: SAS DI and hive unique key generator

LinusH — Tue, 07 Jun 2016 21:58:50 GMT

@Sheeba: I don't think you see the flow clear. To be able to use DI Studio you need to have data in some point into SAS tables. In rare occasions you could do 100% ELT, but that requires careful design and implementation. So the question is, do you really need to do 100 % ELT?
Start with the default DI Studio functionality and analyze the performance.

Re: SAS DI and hive unique key generator

Sheeba — Tue, 07 Jun 2016 22:14:23 GMT

Hi Linush,

Thanks for the reply.

Due to certain constraints we going for an ELT

File x from System A --> SAS --> data modification

File x from System A --> hive--> data modification

I am trying to create a unique id in both sas and hive at record level and trying to keep both in synch.

Regards,

Sheeba

Re: SAS DI and hive unique key generator

ballardw — Tue, 07 Jun 2016 22:28:13 GMT

You may want to think of:

File x from System A --> hive--> data modification--> SAS --> other data modification

Re: SAS DI and hive unique key generator

Sheeba — Tue, 07 Jun 2016 22:38:00 GMT

Hi,

Thanks for the reply.

But due to some constraints I am not planning to transfer the data between SAS and hive.

I am looking for something like a surrogate key generator which can be used in hive.

Regards,

Sheeba

Re: SAS DI and hive unique key generator

LinusH — Wed, 08 Jun 2016 10:19:01 GMT

Then you need to look at Hive functionality, not SAS/DI Studio.

Re: SAS DI and hive unique key generator

LinusH — Wed, 08 Jun 2016 10:29:42 GMT

File x from System A --> SAS --> data modification

File x from System A --> hive--> data modification

I am trying to create a unique id in both sas and hive at record level and trying to keep both in synch."

I don't understand. Are you building two parallel flows, and want to create a key both in SAS and in Hive and want them to be in sync...? Please exemplify with sample data, and elaborate about the end results.

Also, define "some constraints".

Re: SAS DI and hive unique key generator

jklaverstijn — Wed, 08 Jun 2016 11:19:01 GMT

So if I understand correctly you want to have two separate processes (SAS and Hive) for loading the data and still keep the keys in sync. I can think of one way but it depends on the existence of a primary key ijn your data (which is always a good idea). In the past I have used the MD5 function and fed it the variables that make up the key. MD5 should be portable enough that it would generate the same outcome for both Hive and SAS. If you are afraid of clashes (two different keys with the same MD5 summ, not unlikely) use the more modern SHA256 alternative. Thius yields a larger hash but the chances of a clash are very small indeed.

Of course any approach you like can be applied, even plain concatenation of the values, provided there is such a key available in your data.

Good luck,

- Jan.

Re: SAS DI and hive unique key generator

Sheeba — Wed, 08 Jun 2016 16:16:25 GMT

Hi Jan,

Thanks for the details.

I will do more research on MD5 and SHA256 . But what is making it more complicated there is no unique combination of values which identify a record. So with this condition, do you think i will be able to implement the same?

Thanks again,

Regards,

Sheeba

Re: SAS DI and hive unique key generator

Sheeba — Wed, 08 Jun 2016 16:22:36 GMT

Hi Linush,

We decided to go with this flow thinking about performance issues while loading from Sas to hive.

It is exactly as you said am building two flows and want to create a key both in SAS and in Hive and want them to be in sync.

also there is no primary key value for data .so I am trying to create a key and keep it same for a record in both sas and hive.

Hope it is more clear now.

Thanks,

Regards,

Sheeba

Re: SAS DI and hive unique key generator

LinusH — Wed, 08 Jun 2016 19:46:15 GMT

If you don't have a clear business key, how would you ever expect to manage synchronised surrogate keys?

Instead of reveal small parts each time, and get non relevant sugestions: describe as complete as possible your business case, source data, data manipulation rules and the constraints you have.

Re: SAS DI and hive unique key generator

Sheeba — Mon, 13 Jun 2016 15:09:37 GMT

Hi Linush,

Sure..I will keep all this points in mind while posting.. Sorry about that.

Regards,

Sheeba