SAS Viya 3.5: CAS CRUD (Table Update) Capabilities

3 Likes

With the Viya 3.5 release, CAS get a new deleteRows CAS action. This gives CAS the full compliment of table CRUD capabilities: Create (Insert/Append); Read; Update; and Delete. So let's take a look at updating CAS tables and see what we should and shouldn't be doing to maximize up-time, minimize down-time, and give our end-users the latest and greatest view of their data as quickly as possible.

Awww CRUD! I stepped in the ACID!

The first thing to note is that, as an analytical server, CAS is not equipped to support ACID compliant transaction processing (no transaction, commit, or rollback support). While CAS tables can be updated, data consistency must be enforced by the update process. CAS provides no functionality for maintaining data consistency itself.

For a better understanding of this topic, see this video. It talks about Hadoop, not CAS, but the concepts are the same.

With that out of the way, let's get into the CRUD...

Inserts / Appends

CAS has handled inserts from its earliest days but new insert functionality continues to appear. In 3.4, we got server-side appends (Appending CAS tables to other CAS tables). In 3.5, we get partitioned table appends.

Below is the CASUTIL syntax to append (insert) client-side rows into a CAS table:

proc casutil ;
   load data=stg.prdsaleNewRecords
        casout="prdsale" append ;
run ;

For examples of server-side appending and much more, see this post: CAS answers to 4 common data manipulation tasks – Part 1 – APPEND

Update

Like a SQL Update statement, CAS' table.update action can update CAS table field values subject to a WHERE clause. Thus, it can update a single row's values by filtering on a single primary key value; Update several rows by filtering on a non-unique field value; And, update all of the table's fields by not filtering at all.

Below is the CASL syntax to update values in a CAS table:

proc cas ;
   table.update /
      set={
         {var="actual",value="22222"},
         {var="country",value="'FRANCE'"} },
      table={
         caslib="Public",
         name="prdsale",
         where="id=10" };
quit ;

CAS offers no "deltas table" update functionality. That is, we can't update our target CAS table with the values of another table like we can with base tables using DATA Step MODIFY or we can with LASR tables using IMSTAT UPDATE.

Delete

Similar to table.update, CAS' table.deleteRows deletes rows subject to a WHERE clause. Thus it can delete a single row by filtering on a single primary key value; Delete several rows by filtering on a non-unique field value; Or, delete all of a table's rows by not filtering at all.

Below is the CASL syntax to delete rows from a CAS table:

proc cas ;
   table.deleteRows /
      table={
         caslib="DM",
         name="sales",
         where="region='EAST'"
      };
quit;

Reading Tables During Update

CAS tables are available for READ during update. Updates (inserts, updates, and deletes) are applied at the session level and are only promoted to the Global table once complete. So any READ requests during update will see the table as it was prior to the update. READ requests after the update will see the updated table.

In VA there is no cue, like the yellow triangle in VA 7.X, that the underlying table has changed. The end-user sees one version of the table for CAS actions prior to update and another version for CAS actions after the update. The results displayed in VA will not change to reflect the modified table data until a post-update CAS action is run. Below we see the behavior. In the example, a simple report is run and then a table.deleteRows action is run to remove all of the "EAST" region records.

The first image shows a VA report run prior to but viewed after the deleteRows action was run. Note that there is no visual cue that the table has changed. The report still shows the EAST region records even though they have been deleted at this point.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

We then modify the report which triggers a new CAS action causing the report to utilize the modified table. Note that the EAST region data is now gone

WRITE Locking

Concurrent table update requests are simply queued and applied in the order they arrived. So be sure to develop a robust update strategy to ensure no updates are lost.

For a full understanding of CAS table locking during update, check out Uttam Kumar's post, Concurrent data append and update to a global CAS table.