Capture SQL View Definition for Programmatic Change

[Patrick]

Hi Experts,

I need to replicate a SAS 9.4 data environment in a Linux environment. Using rsync -av works like a charm for this.

The challenge I'm facing is that the source environment also contains SQL views in the form:

select *
from lref.table
using libname lref "<source_root_path>/<relative path>";

I need to re-create these views in the target environment by replacing the source_root_path with the target_root_path.

Do you know of any other/better way than executing a SQL describe view, capture the log output in a file, parse out the view definition, change the path and then use this new code to create the views in the target environment?

I couldn't figure out any other way to retrieve the view definition.

Thanks, Patrick

[Kurt_Bremser]

UNIX systems have very nice tools for exactly such mass text change operations, e.g. awk. Just run that on the original source codes of the SQL views. If those source codes are not available, someone fucked up big time (see Maxim 27).

[Patrick]

Hi @Kurt_Bremser 

Thanks for answering. 

There are no explicit scripts. The views get created metadata driven. Below an example of the main "table" for this.

What I'm trying to do, is to create the data environment for an integration environment using the data from source that has been defined as "source of truth" and against which data needs to get reconciled (after running for another month).

In theory I wouldn't have to bother about the views, because they should get re-created by such a run. ...but one can't be sure.

I could of course use the config Excels and the existing scripts from source to build some adhoc job for re-creating the views in the target environment, BUT... I really would like to avoid this. Things are done messy. 
What I would like to do, is to create the data (views included) for this integration environment without any dependency on any version of Excel config sheets or scripts.

I hope this explains why I'm trying to extract and use the view definitions to re-create the views. 

Regards, Patrick

[Tom]

Does the code that creates the views from the metadata still work?

Why not just modify it to point to the new location and try running it.

[Patrick]

Sorry for answering so late. I had a few rather busy weeks. 

@Tom Unfortunately things are still changing and also inconsistent. This metadata driven approach is only used for a portion of the view creation. Some views are also created directly fully coded spread out over a lot of scripts. And there is talk about getting rid of the metadata driven approach at all. That's why I'm aiming for an approach that doesn't rely on the code that created the views.

@Quentin Symlinks are certainly not the solution. A view can be something like select <subset of source columns> from blah.table using libname blah "path to folder". I believe the only way is to re-create the view with a different "path to folder".

[Tom]

So if you have a set of SQL defined views (as opposed to DATA step defined views) then you could use something like this to gather the definitions into a dataset.

First let's make some views.

* make some SQL views ;
proc sql;
create view class as 
 select *
 from sashelp.class
;
create view class2 as 
 select * 
 from work.class
;
quit;

Now let's make a dataset with the set of views that we need to find the definitions of

* Get list of views ;
proc sql ;
create table views as 
  select libname,memname
  from dictionary.members
  where memtype='VIEW'
    and libname='WORK'
;
quit;

Now we can start making the code to find the code.

First we can use a data step to generate one DESCRIBE VIEW statement for each view.

* Generate temp file with source code of views ;
filename code temp;
data _null_;
  file code;
  set views;
  put 'describe view ' libname +(-1) '.' memname ';' ;
run;

Now we can use %INCLUDE to run that generated code inside of a PROC SQL step. We can redirect the log to a file and use options to suppress other text from the log.

filename viewcode temp;
options nosource nonotes ;
proc printto log=viewcode new;
run;
proc sql;
%include code / nosource2;
quit;
proc printto log=log;
run;
options source notes;

Now we can use a data step to read in the generated code and match it with the view that it defines.

* Read in the code ;
data viewcode ;
  infile viewcode ;
  input ;
  line=_infile_;
  if upcase(left(line))=:'SELECT ' then set views;
  if line ne ' ';
run;

Results:

Now it should be trivial to generate lines of code from that dataset.

data _null_;
  set viewcode;
  by libname memname;
  if first.memname then put 'create view ' libname +(-1) '.' memname 'as';
  len=length(line);
  put line $varying200. len;
run;

Result

 create view WORK.CLASS as
         select *
           from SASHELP.CLASS;
 create view WORK.CLASS2 as
         select *
           from WORK.CLASS;

[Quentin]

---

@Patrick wrote:

@Quentin Symlinks are certainly not the solution. A view can be something like select <subset of source columns> from blah.table using libname blah "path to folder". I believe the only way is to re-create the view with a different "path to folder".

---

Why couldn't symlinks work?  I'm assuming on your source server, all your data is sitting buried within one directory tree, e.g.:

/sourcerootpath/Data/Project1/...
/sourcerootpath/Data/Project2/...

And when you copied the files to the target, they went to something like:

/targetrootpath/Data/Project1/...
/targetrootpath/Data/Project2/...

So on the target server, if your created a symlink /sourcerootpath which points to the existing directory /targetrootpath, then all of the original views and code that point to /sourcerootpath would magically work, wouldn't they?

[Patrick]

@Quentin The views in the target environment must point to the replicated data in the target environment. For this reason they must get re-created.  

This environment with replicated production data is then what one can use to create other environment for various Dev & Test purposes and there one can then create links to the replicated data for anything file that doesn't need write access during the Dev or Test activities. 

Hope that makes sense.

[Tom]

You probably need to plan ahead better.  For example before you mentioned that the views are created by multiple programs (in apparently different ways).  Instead setup a plan for how the views are created that will make it easier to reuse programs without having to make any changes to the code.

One solution might be to only setup the views once (or via a single update process).

One solution might be to use an operating system environment variable.   Here is a simple example:

libname prod '~/prod';
libname dev '~/dev';
data prod.ds1; env='prod'; run;
data dev.ds1; env='dev ';run;

proc sql;
create view test as 
  select * 
  from env.ds1
  using libname env "~/$env"
;
quit;

options set=env=prod;
data _null_;
  set test;
  put env=;
run;

options set=env=dev;
data _null_;
  set test;
  put env=;
run;

[Quentin]

---

@Patrick wrote:

@Quentin The views in the target environment must point to the replicated data in the target environment. For this reason they must get re-created.  

This environment with replicated production data is then what one can use to create other environment for various Dev & Test purposes and there one can then create links to the replicated data for anything file that doesn't need write access during the Dev or Test activities. 

Hope that makes sense.

---

I think we're talking past each other a bit.  I understand the views must be recreated, because views sometimes don't migrate etc.  But I think symlinks can help you avoid changing the code that creates the view.

I just played with it, and it worked as I expect.  Given a view like you have:

proc sql;
create view test as 
  select * 
  from source.myclass
  using libname source "~/sourcerootpath"
;
quit;

I made a symlink ~/sourcerootpath which points to the target directory.  With that setup, I can point the symlink to anywhere on my target server, the view works.

So on your target server, creating a symlink should allow you to avoid updating the view definition to have a different path.

[Quentin]

AI had a reasonable suggestion:

In your target environment, could you create a symlink that points  source_root_path  to target_root_path?

That would avoid code changes.

AI also generated a macro to implement the log capture approach you mentioned.  This is #AI and #UNTESTED, but from a quick skim read doesn't look crazy.  The AI was running this on windows SAS, not linux, so would likely need to be refined... 

%macro export_sql_views_with_path_map(
  lib=,
  outcode=,
  source_root=,
  target_root=,
  execute=no,
  include=,
  exclude=
);
  %local execute_uc view_count;

  %let execute_uc=%upcase(%superq(execute));

  %if %superq(lib)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) LIB= is required.;
    %return;
  %end;

  %if %superq(outcode)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) OUTCODE= is required.;
    %return;
  %end;

  %if %superq(source_root)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) SOURCE_ROOT= is required.;
    %return;
  %end;

  %if %superq(target_root)= %then %do;
    %put ERROR: (export_sql_views_with_path_map) TARGET_ROOT= is required.;
    %return;
  %end;

  %if &execute_uc ne YES and &execute_uc ne NO %then %do;
    %put ERROR: (export_sql_views_with_path_map) EXECUTE= must be YES or NO.;
    %return;
  %end;

  proc sql noprint;
    create table work._esv_views as
    select libname
          ,memname
    from dictionary.tables
    where libname = upcase("%superq(lib)")
      and memtype = 'VIEW'
      %if %superq(include) ne %then %do;
      and upcase(memname) like upcase("%superq(include)")
      %end;
      %if %superq(exclude) ne %then %do;
      and upcase(memname) not like upcase("%superq(exclude)")
      %end;
    order by memname
    ;

    select count(*)
      into :view_count trimmed
    from work._esv_views
    ;
  quit;

  %if &view_count = 0 %then %do;
    %put WARNING: (export_sql_views_with_path_map) No views matched in LIB=&lib..;
    %return;
  %end;

  filename esvdesc temp lrecl=32767 encoding='utf-8';
  filename esvdrv  temp lrecl=32767 encoding='utf-8';
  filename esvout  "%superq(outcode)" lrecl=32767 encoding='utf-8';

  data _null_;
    file esvdrv;
    put 'proc sql;';
    set work._esv_views end=last;
    put '  describe view ' libname '.' memname ';';
    if last then put 'quit;';
  run;

  proc printto log=esvdesc new;
  run;

  %include esvdrv;

  proc printto;
  run;

  data work._esv_view_lines;
    length raw $32767 line $32767 view_name $41 libname $8 memname $32;
    infile esvdesc lrecl=32767 truncover;
    input raw $char32767.;

    raw = prxchange('s/\x0c//o', -1, raw);
    raw = tranwrd(raw, '0D'x, ' ');

    retain capture 0 seq 0 view_name libname memname;

     if scan(strip(raw), 1, ' ') = 'NOTE:'
       and scan(strip(raw), 2, ' ') = 'SQL'
       and scan(strip(raw), 3, ' ') = 'view'
       and upcase(scan(strip(raw), 5, ' ')) in ('IS', 'HAS')
    then do;
      view_name = scan(strip(raw), 4, ' ');
      libname = scan(view_name, 1, '.');
      memname = scan(view_name, 2, '.');
      capture = 1;
      seq = 0;
      return;
    end;

    if capture then do;
      if prxmatch('/^\s*\d+\s+The SAS System\b/i', raw) then return;

      line = raw;
      seq + 1;
      output;

      if strip(raw) = ';' then do;
        capture = 0;
      end;
    end;

    keep libname memname view_name seq line;
  run;

  proc sql noprint;
    select count(*)
      into :view_count trimmed
    from work._esv_view_lines
    ;
  quit;

  %if &view_count = 0 %then %do;
    %put ERROR: (export_sql_views_with_path_map) No DESCRIBE VIEW text was captured.;
    %return;
  %end;

  proc sort data=work._esv_view_lines;
    by view_name seq;
  run;

  data _null_;
    if fexist('esvout') then do;
      rc = fdelete('esvout');
      if rc ne 0 then put 'ERROR: (export_sql_views_with_path_map) Unable to replace existing output file.';
    end;
  run;

  data _null_;
    set work._esv_view_lines end=last;
    by view_name seq;

    file esvout;

    if _n_ = 1 then do;
      put '/* Generated by %export_sql_views_with_path_map */';
      put 'proc sql;';
    end;

    length rewritten $32767;
    rewritten = tranwrd(line, symget('source_root'), symget('target_root'));

    if index(symget('target_root'), '/') then do;
      rewritten = tranwrd(rewritten, '\', '/');
    end;

    if first.view_name then do;
      put;
      put '  create view ' view_name ' as';
    end;
    put rewritten;

    if last then put 'quit;';
  run;

  %if &execute_uc = YES %then %do;
    %include esvout;
  %end;

  %put NOTE: (export_sql_views_with_path_map) Processed &view_count lines for LIB=&lib..;
  %put NOTE: (export_sql_views_with_path_map) Output written to %superq(outcode).;
%mend export_sql_views_with_path_map;