[Viya] CAS tables for transactional applications

[Edoedoedo]

Hi,

I'm developing a DDC inserted into a VA Report, which basically contains a html form to let the user to "add/update/delete an order". An "order" is represented as a row on a global cas table "ORDERS". In the other parts of the Report, there are standard VA graphs which show some info about the ORDERS table (total orders per month, ...).

Now, when a user add/update/delete an order on my DCC, the information about this order is sent to a job which must update the global ORDERS table.

The problem is: how should the job update the ORDERS table to maintain consistency and handle concurrency?

What I have been implementing is very standard:

• COPY the global ORDERS table to a local cas table
• EXECUTE various data step or actions to edit the local cas table, every time OVERWRITING it (it's local, I can)
• (*) when it's done, DROP the global ORDERS table
• PROMOTE the local cas table to the global ORDERS table
• SAVE the global ORDERS table

You can see at least two serious issues:

• there exists a short moment (*) where the global table does not even exist!
• the entire procedure relies on a local temporary copy of the table, so if two user execute this procedure almost at the same time with different parameters, the latter overwrites the changes made by the former!

So the very problem here is that by design the global ORDERS table should never be dropped and replaced, it must always remains the same! But how to handle the classics insert/update/delete on a global table on CAS without dropping it?

• INSERT: I found out that if I use

  data CASLIB.GLOBALTABLE(append=YES);
    set CASUSER.LOCALTEMPTABLE;
  run;

  then the LOCALTEMPTABLE is inserted in the GLOBALTABLE without dropping it. Very well.
• UPDATE: I found out the "update" action, which can overwrites rows; unfortunately, the new values MUST be literals, so I cannot specify the update values as the result of a subquery (as you do with a "UPDATE TABLE ... FROM ..." in sql). Not so good.
• DELETE: I found out there is no way to delete a row of a cas table. Very bad!

So actually I'm stuck, it seems I cannot use the CAS engine for my transactional application (i.e. with concurrent insert/update/delete on a table from multiple users with consistency and standard RDBMS concurrency rules).

What do you think? Am I missing something?

Thank you

Regards

[BrunoMueller]

As for the table.update you can use values returned back from a FEDSQL query, see example below.

One has to convert the type for numeric values and add quotes for char values to make it work

proc casutil;
  load data=sashelp.class outcaslib="casuser" casout="class" replace;
  load data=sashelp.class outcaslib="casuser" casout="class_update" replace;
run;

proc cas;
  action fedsql.execdirect result=class_update status=sqlrc / 
    query="select name, age, height * 100 as newValue, trim( both from reverse(name) ) as newChar from casuser.class where age = 14"
  ;

  describe class_update;
  print sqlrc;
  do row over class_update["Result Set"];
    print row ;

    /* convert value to string type */
    newValue = catq("1", row["newValue"]);
    /* add quotes for char value */
    newchar = catq("a", row["newChar"]);

    print "Updating: " row["name"];

    action table.update status=updaterc /
      table={
        caslib="casuser" name="class_update"
        where = catx(" ", " name = ", quote(trim(row["name"])) )
      }
      set={
        {var="height", value=newValue }
        {var="name", value=newChar }
      }
    ;
    print updaterc;
  end;
/*  action builtins.history;*/
run;
  action table.fetch / table={caslib="casuser" name="class_update"};
run;
quit;

[Edoedoedo]

Nice trick Even though I expect huge performance issues when the rows to be updated are a lot, since the update is done one row at a time and it doesn't use parallelism at all!

What about delete? Is there maybe a similar trick?

[Rahul_B]

I face the same issue for smaller chunks of data this works fine, but when you try to do updates on 100,000 records its super slow.

[LinusH]

Perhaps I missed something, but the A in CAS is for analytical. I don't think building transactional system on top of Viya is perfect use case.

Maxim 14 - Use the right tool.

[XavierBizoux]

Hi, 

I understand the needs for CRUD operations. I've written a blog series on that topic. They are not yet published externally but it will come ;-). 

Basically, the technique I use is the same as yours: copy a temporary table and interact with that table. As you mention, the problem is concurrency. There is no real technique to avoid this as this would mean define a locking mechanism at row level which is kind of difficult to manage and handle on large scale environments. 

In my blogs, I mention that even though it is possible to perform CRUD operations directly on the CAS table, it is better to use a database for that purpose. One reason being the rollback functionality. So, for a large scale environment, you should perform the updates using SAS Data Connectors to access an external database. You should then create a process to update the CAS table whenever needed (full reload or append). 

To handle the multiple updates on the same record, you can use timestamp or compare the original values in your form with the one stored in the database.

• Using timestamp, you can set a rule that if the update timestamp is smaller than some amount of time, the record is not updated.
• If you don't want to use timestamp, you might want to check the value that is currently stored in the database with the original values in your input form. This will allow to identify if the record was updated between the read and the update. 

With both techniques, you should decide what you want to do if the update is not performed. 

I think there are many considerations when performing CRUD actions on data. Those considerations are not specific to SAS but CAS adds an extra layer of complexity as it not designed for CRUD activities. 

Let me know if you have questions. I will do my best to answer based on the information you provide.