topic Re: Is there a way to upsert to castable? in SAS Programming

Is there a way to upsert to castable?

Mayt — Fri, 16 Feb 2024 08:49:10 GMT

Hi guys!

I've tried to update and insert new data to castable. But My code takes a lot of time to complete. Do you guys know how to use upsert by any chance? Or how to optimise my code?

Thanks a lot!!

My code right now:

Data public.A;
Input ID Name$ Height;
cards;
1 A 1
3 B 2
5 C 2
7 D 2
9 E 2
;
run;

Data public.B;
Input ID Name$ Height;
cards;
2 A 2
4 B 3
5 C 4
7 D 5
;
run;
/* select all rows from public.a and flag rows that exist in both public.a, public.b */
proc fedsql sessref=MySession;
create table public.C as 
select x.*,case when y.ID is not null then 1 else 0 end as flag
from public.a x 
left join public.b y
on x.ID = y.ID;
quit;
/* delete rows that exist in both public.a, public.b from public.c*/
proc cas;
table.deleteRows                    
table={caslib='public',
name='c',
where="flag = 1"};
quit;
/* drop column flag*/
proc cas;
table.alterTable / 
	name = 'c', caslib ='public', 
	drop={'flag'};
quit;

/* appending new data to the base */
data public.c (append=yes);
set public.b;
run;

public.a

public.b

public.c

Re: Is there a way to upsert to castable?

LinusH — Fri, 16 Feb 2024 12:25:14 GMT

I don't see update in your logic, just insert of new records...?
Not a CAS specific advice: try to reduce number of steps.
Instead of a left join an flag creation, try a where clause with sub-query.

where c.id not in(select distinct id from public.b)

And then and insert/append.

Re: Is there a way to upsert to castable?

sbxkoenk — Fri, 16 Feb 2024 13:34:35 GMT

Append + de-duplicate??

APPEND :

The SAS® Viya® platform provides multiple methods to append to CAS tables
By Kevin Russell on SAS Users November 6, 2023
https://blogs.sas.com/content/sgf/2023/11/06/sas-viya-append-cas-tables/
How to Emulate PROC APPEND in CAS?
By Steven Sober on SAS Users November 20, 2017
https://blogs.sas.com/content/sgf/2017/11/20/how-to-emulate-proc-append-in-cas/
Concurrent data append and update to a global CAS table
By Uttam Kumar on SAS Users November 15, 2017
https://blogs.sas.com/content/sgf/2017/11/15/concurrent-data-append-and-update-to-a-global-cas-table/

DE-DUPLICATE :
You can use the native deduplication.deduplicate CAS action to remove duplicate rows.

Koen

Re: Is there a way to upsert to castable?

Mayt — Fri, 16 Feb 2024 13:47:01 GMT

I think I need to update. You see ID 5,7 the height value of public.a(old
data) and public.b(new data) are different. I want to store the value from
public.b

Re: Is there a way to upsert to castable?

Mayt — Fri, 16 Feb 2024 13:58:30 GMT

Oh I see your point. But when I tried it, look like fedsql doesn't support in/any/all subquery.

proc fedsql sessref=MySession;
create table public.C as 
select *
from public.a x 
where x.id not in(select distinct id from public.b);
quit;

It thew an error

ERROR: Unsupported operation in FedSQL query: IN/ANY/ALL subquery.

Re: Is there a way to upsert to castable?

Mayt — Fri, 16 Feb 2024 14:05:00 GMT

More information : I code in SAS studio of SAS Viya

Re: Is there a way to upsert to castable?

sbxkoenk — Fri, 16 Feb 2024 14:11:33 GMT

Indeed,

FedSQL for CAS does not support use of non-correlated subqueries with the IN, ANY, and ALL predicates. https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casfedsql/p1ucns3lrhzhamn1k6fw6vhzhzam.htm.

You can try to do an alternate query.

Koen

Re: Is there a way to upsert to castable?

Mayt — Fri, 16 Feb 2024 14:21:11 GMT

But some rows are not duplicate. Like ID 5,7 in public.a and public.b. Its height column has different values. I want ID 5,7 in public.c have the value from public.b.

Re: Is there a way to upsert to castable?

Patrick — Fri, 16 Feb 2024 16:55:16 GMT

Appreciate that you're sharing code with sample data that we can run to replicate what you describe.

I believe in your case we need to better understand what you've got in your real environment.

1. Does the target CAS table public.C preexist in CAS? And what's the volume (number of rows)

2. Do you really have two source tables public.A and public.B? Or is this "just" about how to update/insert into a CAS target table and though you would in reality only have a single source table?

3. Where do your source table(s) reside? In CAS or in Compute? And what are the volumes (number of rows).

4. Once you've updated the in-memory CAS table do you also need to save back the changes to the physical table? And if so: Where is this physical table stored?

5. Which Viya version are you using?

Re: Is there a way to upsert to castable?

Mayt — Mon, 19 Feb 2024 03:54:44 GMT

1. Does the target CAS table public.C preexist in CAS? And what's the volume (number of rows)
- Nope, Actually it's used for store merged data from table A(old data) and table B (new data).

2. Do you really have two source tables public.A and public.B? Or is this "just" about how to update/insert into a CAS target table and though you would in reality only have a single source table?
- I do have two source table. In my real code, I have table A and table D. Then I copy data from table D to Table B to rename columns.

3. Where do your source table(s) reside? In CAS or in Compute? And what are the volumes (number of rows).
- In CAS. table A has 45 million row, table B has 1000-10000 rows.
4. Once you've updated the in-memory CAS table do you also need to save back the changes to the physical table? And if so: Where is this physical table stored?
- In CAS. Actually it's table A.
5. Which Viya version are you using?
- 2022.09 and 2022.1

Re: Is there a way to upsert to castable?

Patrick — Mon, 19 Feb 2024 05:26:31 GMT

@Mayt Given your answers and that you're creating table C couldn't you just use a data merge step?

%let sessref=MySess;
%if %sysfunc(sessfound(&sessref)) %then
  %do;
    cas mySess terminate;
  %end;
cas &sessref cassessopts=(caslib="casuser");
libname casuser cas;

data casuser.A;
  input ID Name $ Height;
cards;
1 A 1
3 B 2
5 C 2
7 D 2
9 E 2
;
run;

data casuser.B;
  input ID Name $ Height;
cards;
2 A 2
4 B 3
5 C 4
7 D 5
;
data casuser.c;
  merge casuser.a casuser.b;
  by id;
run;

proc print data=casuser.c;
  where id in (1,2,5);
run;

/*cas &sessref terminate;*/

With all tables in CAS the data step will execute in CAS

08  data casuser.c;
109    merge casuser.a casuser.b;
110    by id;
111  run;
NOTE: Running DATA step in Cloud Analytic Services.
NOTE: The DATA step will run in multiple threads.
NOTE: There were 5 observations read from the table A in caslib CASUSER(<user>).
NOTE: There were 4 observations read from the table B in caslib CASUSER(<user>).
NOTE: The table c in caslib CASUSER(<user>) has 7 observations and 3 variables.

Given the volume of your table A:

Not sure how this code will perform. Let us know.
Either drop table A after this process from memory or also define it as output of the merge step so the process replaces table A (to save some memory).
```
data casuser.a;
  merge casuser.a casuser.b;
  by id;
run;
```

N.B: The ID column used for BY processing may-not be defined as VARCHAR(*). Let us know should this be the case.

Re: Is there a way to upsert to castable?

Mayt — Mon, 19 Feb 2024 07:34:49 GMT

Due to enormous data, I'm afraid that data step might take longer time than fedsql.

Re: Is there a way to upsert to castable?

LinusH — Mon, 19 Feb 2024 12:49:31 GMT

The "problem" here is that CAS is mainly built for analytics, not for DB operations.

Which we can see in the syntax limitations of PROC FEDSQL, data step and PROC CASL.

3.) So B would have 45'' records after the operation?

Is this repeated, like daily?

What is your pain point, from a user and/or maintenance perspective?

Again (partly) untested, but if you have avialable RAM, you could possible overcome limitations of UPDATE, recreate the table using a FEDSQL join query

proc fedsql sessref=MySession;
create table public.C as 
	select 
		coalesce(x.id,y.id) as id, 
		coalesce(x.name, y.name) as name, 
		coalesce(x.height, y.height) as height
	from public.a x 
	full join public.b y
	on x.ID = y.ID;

drop table public.b;
quit;
/* renaming back to 'B' */
proc casutil...
altertable...

Re: Is there a way to upsert to castable?

Patrick — Mon, 19 Feb 2024 13:20:25 GMT

@Mayt wrote:

Due to enormous data, I'm afraid that data step might take longer time than fedsql.

@Mayt What makes you think that? The data step will execute multithreaded within CAS - as a CAS action. The main challenge is that you create a new in-memory CAS table that's then also no more in-synch with the physical backing table.

It's most of the time better to do data prep before loading the data into CAS and only load the final table server side into CAS. To propose any such approach we would need to understand more about your scenario, where the data comes from, how often you have to update it in CAS and what it gets used for.

Re: Is there a way to upsert to castable?

Mayt — Tue, 20 Feb 2024 08:39:08 GMT

@Patrick @LinusH

where the data comes from?
- new data comes from API.
It's like new data comes from API --> update to old data --> feed to the model and save data(from 2) as physical file to sas.
how often you have to update it in CAS?
- every 20 minutes
what it gets used for?
- use as input to train model.
What is your pain point, from a user and/or maintenance perspective?
- due to the circle need to repeat every 20 minutes, sometimes the data can't be updated completely before feed to model. So I need to wait next round to complete it. But I want to finish it within each round. That's why I think if there's a way to update data faster, that might be great.

Re: Is there a way to upsert to castable?

LinusH — Tue, 20 Feb 2024 10:15:05 GMT

For each answer I get more questions, like what does the model to with the data it gets fed by every 20 minutes?

What kind of API calls are there? Sounds more like a stream, but those typically doesn't have updates.

Updates typically belongs to a relational database platform.

So, it sounds like a complex use case, and I think you need someone at your site to guide you to a functioning design for this.

Re: Is there a way to upsert to castable?

Patrick — Tue, 20 Feb 2024 11:05:42 GMT

@Mayt In below sample code table A is your big table with 45 Million rows and table B is your transaction table with 1T to 10T rows used to upsert table A.

There is unfortunately not table action merge.

The available table actions append, update and deleteRows have a parameter whereTable that allows to base processing on values of another table.

But even with using the whereTable parameter I couldn't find a way to define the append only for IDs in table B that don't already exist in table A and I couldn't think of a performant approach to work out the updates and inserts upfront.

For this reason the approach I've chosen where I believe it should reasonably perform given the volumes of table B:

1. Delete all rows in table A with an id that also exists in table B

2. Append (insert) all rows from table B to table A

With databases a delete operation is much slower than an update or an insert. Not sure how that is with CAS - but then table B is not that big. I'm really curious how below code performs.

If you're going to use this code with your real data then please let us know on how many nodes (threads) it executes and how long it takes.

%let sessref=MySess;
%if %sysfunc(sessfound(&sessref)) %then
  %do;
    cas mySess terminate;
  %end;
cas &sessref cassessopts=(caslib="casuser");
libname casuser cas;

data casuser.A;
  input ID Name $ Height;
cards;
1 A 1
3 B 2
5 C 2
7 D 2
9 E 2
;
run;

data casuser.B (duplicate=yes);
  input ID Name $ Height;
cards;
2 A 2
4 B 3
5 C 4
7 D 5
;

proc cas;
  /* delete all rows in table A with an ID that also exists in table B */
  table.deleteRows /
    table={
      caslib='casuser'
      name='A',
      whereTable={
        caslib='casuser',
        name='B',
        vars={{name="ID"}}
        }
      };

  /* append (insert) all rows from table B to table A */
  table.append /
    source={caslib='casuser', name='B'},
    target={caslib='casuser', name='A'}
    ;

  /* drop table B */
  table.dropTable /
    caslib='casuser'
    ,name='B'
    ;
quit;

proc print data=casuser.A;
run;

/*cas &sessref terminate;*/

I'm creating table B with all rows on all nodes to avoid the need for data movement via cas_disk_cash during the delete and append operations. This is for performance reasons and given the volume of table B shouldn't impact memory consumption to much.

data casuser.B (duplicate=yes);

Not yet covered but something you need to address

I don't save the cas in-memory table back to the physical table in the backing store and though this table gets out of sync.

If you "lose" your CAS in-memory table for example as part of a CAS restart then you need somewhere the physical data for it. If this data doesn't already exist then you need also a process to upsert your physical table in the backing store.

Addendum

Like @LinusH I was asking myself why you need to re-train your model every 20 minutes against all these 45M rows. But then that's not my home turf and when Googling I found that there appear to be cases where that's the right thing to do. ...but it's certainly worth for you to consider if there could be other ways to keep your model sufficiently up-to-date without having to re-process all the data every 20 minutes.