SAS DI and hive unique key generator

[Sheeba]

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

[jklaverstijn]

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

[Sheeba]

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

[LinusH]

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

[Sheeba]

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

[ballardw]

You may want to think of:

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

[Sheeba]

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

[LinusH]

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

[jklaverstijn]

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.

[Sheeba]

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

[LinusH]

"

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

[Sheeba]

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

[LinusH]

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.

[Sheeba]

Hi Linush,

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

Regards,

Sheeba