SPD Server SQL Views

Overview of SPD Server SQL Views

SPD Server supports the creation of SQL views. A view is a virtual table that is based on the result set of an SQL statement. An SPD Server view can reference only SPD Server tables. You should use SPD Server explicit pass-through SQL syntax to create SPD Server views:

     EXECUTE(
         Create view <viewname> 
         as <SELECT-statement>)
     BY [sasspds|alias];

When you create an SQL view, a view file is created in the specified domain with the name <viewname>.view.0.0.0.spds9. After you create an SQL view, you can use the SPD Server view as a table in SPD Server SQL queries.

View Access Inheritance

SPD Server uses View access inheritance to control access to tables that are referenced by SPD Server views. View access inheritance gives a user who has access to a View access to the individual component tables that comprise the view.

For example, user Stan creates tables WinterSales and SpringSales, and then Stan creates a view that joins the two tables. Stan gives user Kyle Read access to the view. Because Kyle has Read access to the view of the joined tables, Kyle also has Read access to the individual component tables WinterSales and SpringSales.

/* User Stan creates tables WinterSales and SpringSales.   */
/* Only user Stan can read these tables directly.          */

LIBNAME Stan sasspds 'temp' user='Stan';
DATA Stan.WinterSales;
INPUT idWinterSales colWinterSales1 $ colWinterSales2 $ ... ;
...
;

DATA Stan.SpringSales;
INPUT idSpringSales colSpringSales1 $ colSpringSales2 $ ... ;
...
quit;

/* Stan creates view WinterSpring to join tables WinterSales */
/* and SpringSales. Stan gives user Kyle read access to the  */
/* view.  Because Kyle has rights to read view WinterSpring, */
/* he also has read access rights to the individual tables   */
/* that Stan used to create the view WinterSpring. Kyle can  */
/* only read the tables WinterSales and SpringSales through  */
/* the view WinterSpring. If Kyle tries to directly access   */
/* the table WinterSales or the table SpringSales, SPD       */
/* Server does not comply and issues an access failure       */
/* warning.                                                  */

PROC SQL;
CONNECT TO sasspds(dbq='temp' user='Stan';
EXECUTE(create view WinterSpring as 
        SELECT * from SpringSales, WinterSales
        WHERE SpringSales.id = WinterSales.id);
quit;

PROC SPDO lib=Stan;
SET ACLUSER;
SET ACLTYPE=view;
ADD ACL WinterSpring;
MODIFY ACL WinterSpring / Stan=(y,n,n,n);
quit;

SPD Server View access inheritance is available only when it is invoked with SPD Server explicit pass-through SQL syntax. If a user accesses a view directly through SAS SQL or a SAS DATA step, the user must also have direct access to the component tables that are referenced in the view. In this case, the ACL credentials of the user are applied to the component view tables. This restriction limits the usefulness of SPD Server views that are accessed via SAS SQL to cases where a SAS SQL user creates a virtual table to simplify SQL coding.

SPD Server SQL views that reference DICTIONARY tables cannot be used by SAS SQL.

Materialized Views

Overview of Materialized Views

You can create an SQL view as a materialized view. For a materialized view, the results of the view statement are computed and saved in a temporary SPD Server table when the view is created. For a standard SQL view, the results are computed each time the view is referenced in a subsequent SQL statement. As long as the input tables that the view consists of are not changed, the materialized view returns the results from the temporary table when the view is referenced in an SQL statement. If any of the input tables that comprise the view are modified, the materialized view recomputes the results the next time the view is referenced and refreshes the temporary table with the new results. The temporary results table for a materialized view exists for as long as the view exists. When a user deletes or drops a view, the temporary results table is also deleted.

You can create a materialized view only when you create an SQL view. You must use the SPD Server SQL pass-through facility. The keyword Materialized in the Create View syntax identifies the view as a materialized view. When you create a materialized view, the Create View operation does not complete until the temporary results table is populated. This process can add substantial time to the execution of a Create View statement.

Each time you reference a materialized view in an SQL statement, a check determines whether any of the input tables that are used to produce the temporary results table have been modified. If none of the tables have been modified, the temporary table is substituted in place of the view file in the SQL statement. If any of the input tables have been modified, the SQL statement executes and uses the changed tables. The statement functions like a standard SQL view reference. A background thread is also launched. The background thread is independent of the SQL statement execution. This thread refreshes the temporary results table. Until the refresh is completed, any incoming references to the view are treated as standard view references.

When you create a materialized view, an additional SPD Server table is created in the same domain as a standard SQL view file. You cannot view or access the materialized view table by using PROC DATASETS or other SAS procedures. If one or more simple indexes are defined on any of the input tables that are used to create the results table, the indexes are also created on the materialized view table, as long as the column that was indexed in the input table also exists in the materialized view table.

For more information about using PROC SPDO to manage access control to your tables and views, see Controlling SPD Server Resources with PROC SPDO in SAS Scalable Performance Data Server: Administrator's Guide.

Create a Materialized View

To create a materialized view, use the following SQL pass-through syntax.

EXECUTE (
  Create Materialized View <viewname> 
  as <SELECT-statements> ) 
BY [sasspds | alias];

Use the existing SQL syntax for all other references to the view whether the view is a standard SQL view or a materialized view. Use the Materialized keyword only in the Create statement. For example, to drop a materialized view, use the following syntax.

EXECUTE (Drop View <viewname> ) BY [sasspds | alias];

Benefits of Materialized Views

A materialized view can provide enormous performance benefits when the view is referenced in an SQL statement. For views that contain costly operations such as multiple table joins or operations on very large tables, the execution time for queries containing a materialized view can be orders of magnitude less than a standard view. If the results table produced by the view is relatively small in comparison with the input tables, the execution time for queries that use a materialized view might be a few seconds versus several minutes for a standard view.

For example, if it takes on average 20 minutes to produce the result set from a view, and the result is in the order of thousands of rows or fewer, a query that references a materialized view takes seconds to execute. If you create a standard view, every time the view is referenced results in 20 minutes of execution time. You should measure the performance benefits on a case-by-case basis.

You can base your decision of whether to use a standard view or a materialized view on how often the input tables to the view are updated, versus how often the view is referenced in an SQL statement. If a view is being referenced at least twice before any updates occur, then the materialized view should provide superior performance. In cases when you can create the defined view quickly, you probably do not need a materialized view. If the input tables are frequently updated in comparison to how often the view is referenced, a standard view is probably more efficient.

Accessing Materialized Views

You only can query or access an SPD Server materialized view though an explicit pass-through connection. Attempts to access SPD Server materialized views via native SAS will result in an error.

The example statements below illustrate how to access an SPD Server materialized view:

select * 
  from connection 
  to sasspds 
   (select .... from <viewname> ...);

execute(create table <tablename> 
  as select ... 
  from <viewname> ... 
  by [sasspds or <àlias>] );

Materialized View Example

The following code creates and uses a materialized view. The code creates the input tables X and Z. Table X has three columns (a,b,c), and table Z has four columns (a,b,c,d).

data mydomain.X;
  do a = 1 to 1000;
     b = sin(a);
     c = cos(a);
  output;
end;
run;

data mydomain.Z;
  do a = 500 to 1500;
     b = sin(a);
     c = cos(a);
     d = mod(a,99);
  output;
end;
run;

PROC SQL;
connect to sasspds (dbq='mydomain'
  host='myhost'
  serv='myport'
  user='me'
  passwd='mypasswd');

execute (create materialized view XZVIEW as
         select *
           from Z
           where a in
             (select a from X))
           by sasspds;

         select *
           from connection
           to sasspds
            (select *
              from XZVIEW
              where d >90);

execute (drop view XZVIEW);
quit;