SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS DI and hive unique key generator

Reply
Regular Contributor
Posts: 167

SAS DI and hive unique key generator

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

Super Contributor
Posts: 441

Re: SAS DI and hive unique key generator

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

Regular Contributor
Posts: 167

Re: SAS DI and hive unique key generator

Posted in reply to jklaverstijn

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

Super User
Posts: 5,438

Re: SAS DI and hive unique key generator

@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.
Data never sleeps
Regular Contributor
Posts: 167

Re: SAS DI and hive unique key generator

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

Super User
Posts: 11,343

Re: SAS DI and hive unique key generator

You may want to think of:

 

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

 

 

Regular Contributor
Posts: 167

Re: SAS DI and hive unique key generator

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

Super User
Posts: 5,438

Re: SAS DI and hive unique key generator

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

Data never sleeps
Super Contributor
Posts: 441

Re: SAS DI and hive unique key generator

[ Edited ]

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.

Regular Contributor
Posts: 167

Re: SAS DI and hive unique key generator

Posted in reply to jklaverstijn

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

Super User
Posts: 5,438

Re: SAS DI and hive unique key generator

"

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

Data never sleeps
Regular Contributor
Posts: 167

Re: SAS DI and hive unique key generator

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

Super User
Posts: 5,438

Re: SAS DI and hive unique key generator

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.
Data never sleeps
Regular Contributor
Posts: 167

Re: SAS DI and hive unique key generator

Hi Linush,

 

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

 

Regards,

Sheeba

Ask a Question
Discussion stats
  • 13 replies
  • 715 views
  • 0 likes
  • 4 in conversation