topic Updating metadata for SAS library in Administration and Deployment

Updating metadata for SAS library

ird0nxl — Tue, 04 May 2021 23:47:37 GMT

Hello,

Is there a way to change the SASLIBREF of multiple metadata registered sasdata sets at once?

or Change the data server and DBMS of an existing Library in sas metadata.

Existing library is impala and intended target library to which all existing tables be allocated is snowflake

Thanks

ir0nxl

Solution I used:

Delete the exiting impala Library (say with the libref 'implib') in the target environment (say Test) under which all the tables are registered.

create a new SNOWFLAKE library with the same libref 'implib' in the target environment (TEST)
Export all the table from source environment (Dev) to Test. SAS metadata engine is smart enough to the pick up the target library(newly created Snowflake library) with the same name 'implib'.

Then programmatically uppercase ( which is required for SNOWFLAKE) the PhysicalName/Name of all tables using metadata_setattr() function.

Finally Update metadata of tables selecting them all and updating manually (or programmatically if preferred).

Re: Updating metadata for SAS library

SASKiwi — Fri, 30 Apr 2021 01:51:31 GMT

The easiest way to do this would be to create new libraries in SAS Management Console Library Manager mirroring the existing SAS LIBREFs, then use the Register Tables option on the data library's properties. Then you can delete the old libraries which will remove all of the registered datasets for those libraries in one go.

Re: Updating metadata for SAS library

ird0nxl — Fri, 30 Apr 2021 05:19:35 GMT

That would create new objects? We want to retain the same object_id so that we do not have to change 800+ DIS jobs.

Re: Updating metadata for SAS library

gwootton — Fri, 30 Apr 2021 12:40:00 GMT

You could do this in SAS code with Metadata DATA Step Functions to modify the libref or associated database information and PROC METALIB to register the associated tables.

SAS Language Interfaces to Metadata - DATA Step Functions
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrmeta/part-5.htm

Re: Updating metadata for SAS library

SASKiwi — Fri, 30 Apr 2021 23:39:13 GMT

Are the LIBREFs pointing to an external database server? If so you can change the server name on the data library without changing the library as long as the data library engine isn't changing. For example staying as ODBC, but pointing to a new database server containing the same databases and schemas.

Re: Updating metadata for SAS library

ird0nxl — Sun, 02 May 2021 20:07:22 GMT

Changing from Impala to Snowflake. Changing the server or connection of the registered Library is not possible

Re: Updating metadata for SAS library

SASKiwi — Sun, 02 May 2021 20:26:25 GMT

@ird0nxl - That's unfortunate. We are lucky to be using ODBC only so switching servers is quick and easy.

I don't use DIS, but having tables identified by object_id seems like a siginificant limitation.

Re: Updating metadata for SAS library

Sajid01 — Tue, 04 May 2021 23:12:32 GMT

Hello @ird0nxl

"Changing from Impala to Snowflake. Changing the server or connection of the registered Library is not possible"

Accessing Impala typically requires SAS access interface to Impala, Similarly for Snowflake one would typically use SAS access interface Snowflake.
Just curious how do you plan to manage without making this change ?

Re: Updating metadata for SAS library

ird0nxl — Tue, 04 May 2021 23:28:23 GMT

You can change the server by using meta function

rc = metadata_setattr(liburi, "Engine", 'SASIOSNF');

Then you can can change the other relevant options in the library via SMC or DIS.

But I have found a simpler way than manipulating metadata (see the original question)

Re: Updating metadata for SAS library

Sajid01 — Tue, 04 May 2021 23:33:13 GMT

Thanks for the response. My understanding is that using SAS Management Console is simpler and is the recommended best practice (https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrmeta/p1e5qsh142wjgwn1kz6gpsq1xeyi.htm ).

Writing a program , testing on dev/qa , passing through the change review before implementing on production is a laborious and time consuming process.

I realize that there is a lot to learn in SAS, it would help many of us, if someone can enlighten us with the advantage of doing it using a code and not using SAS Management Console.

Re: Updating metadata for SAS library

ird0nxl — Tue, 04 May 2021 23:54:08 GMT

Using SMC or DIS is best practise.

But you need to find a way to migrate 800+ tables in a single library from one DBMS to new DBMS.

updating them individually is not an option. So the best option was to update the library.

I managed to change the server and other relevant properties of the library using SAS metadata functions.

But the solution I opted involves no programmatic metadata manipulation of the library but uppercasing the physicalName/Name of all table which is an unfortunate requirement of SNOWFLAKE

Solution we used:

Delete the exiting impala Library (say with the libref 'implib') in the target environment (say Test) under which all the tables are registered.

create a new SNOWFLAKE library with the same libref 'implib' in the target environment (TEST)
Export all the table from source environment (Dev) to Test. SAS metadata engine is smart enough to pick up the target library(newly created Snowflake library) with the same name 'implib'.

Then programmatically uppercase (which is required for SNOWFLAKE) the PhysicalName/Name of all tables using metadata_setattr() function.

Finally update metadata of tables selecting them all and updating manually (or programmatically if preferred).