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
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
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
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
You may want to think of:
File x from System A --> hive--> data modification--> SAS --> other data modification
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
Then you need to look at Hive functionality, not SAS/DI Studio.
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.
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
"
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".
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
Hi Linush,
Sure..I will keep all this points in mind while posting.. Sorry about that.
Regards,
Sheeba
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.