topic Re: Hot to perfrom explicit pass through in CAS with Using Oracle in Developers

Hot to perfrom explicit pass through in CAS with Using Oracle

Rahul_B — Tue, 20 Nov 2018 11:59:52 GMT

I was trying to run explicit pass through code, that actually executes in Oracle and gives an output in CAS

I have tried running a simple code, but this isn’t working and am getting the below error. I tried to run this as per recommendation of SAS.

https://communities.sas.com/t5/SAS-Communities-Library/SAS-Viya-3-4-What-s-New-in-CAS-Data-Access/ta-p/490016

Do you guys have ever seen this before ? for your info i am using SAS viya 3.3

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 proc fedsql sessref=mySession _method ; 74 create table hdfs_kim.KIM_FACT_TEST{options replace=true} as 75 select CT."CONTACT_KEY",CT."CONTACT_DATE_KEY", CT."KURT_ID_OWNER", 76 CT."ACTION_CATEGORY",CT."ACTIVITY_DESC",CT."ACTIVITY_ID",CT."ACTIVITY_MAIN_OBJECTIVE",CT."ACTIVITY_OBJEC 76 ! TIVE" from 77 connection to ORA_KIM(select df. "CONTACT_KEY",df."CONTACT_DATE_KEY", df."KURT_ID_OWNER", 78 ex."ACTION_CATEGORY",ex."ACTIVITY_DESC",ex."ACTIVITY_ID",ex."ACTIVITY_MAIN_OBJECTIVE",ex."ACTIVITY_OBJEC 78 ! TIVE" 79 from "KIM_CAMPAIGN_DETAIL_FACT" df left outer join "KIM_CAMPAIGN_DETAIL_FACT_EXT" ex on 80 df."CONTACT_KEY" = ex."CONTACT_KEY" 81 WHERE df."CONTACT_DATE_KEY" = 20181119) CT ; NOTE: Executing action 'fedSql.execDirect'. ERROR: Catalog name not found ERROR: The action stopped due to errors. NOTE: Action 'fedSql.execDirect' used (Total process time): NOTE: real time 0.024378 seconds NOTE: cpu time 0.097488 seconds (399.90%) NOTE: total nodes 17 (936 cores) NOTE: total memory 8.36T NOTE: memory 29.21M (0.00%) ERROR: The FedSQL action was not successful. NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements. 82 83 quit ; NOTE: PROCEDURE FEDSQL used (Total process time): real time 0.05 seconds cpu time 0.00 seconds

/* connecting*/

options cashost="xxx-yyy-rr-001.corp.lat.no" casport=5570;

cas;

caslib _all_ assign;

*cas mySession sessopts=(caslib=hdfs_kim timeout=3600 locale="en_US" metrics=true);

cas mySession sessopts=(caslib=hdfs_kim timeout=3600 locale="en_US" metrics=true);

caslib casora datasource=(srctype="oracle",username="ABC",password="xxxxxxxxxx ",

path="//a-ftd.llllll.xxxxxx.cc:9999/1293",schema="TEST") LIBREF=ORA_KIM;

proc fedsql sessref=mySession _method ;

create table hdfs_abc.xyz {options replace=true} as

select CT."CONTACT_KEY",CT."CONTACT_DATE_KEY", CT."CUST_NO",

CT."ACTION_CATEGORY",CT."ACTIVITY_DESC",CT."ACTIVITY_ID",CT."ACTIVITY_MAIN_OBJECTIVE",CT."ACTIVITY_OBJECTIVE" from

connection to ORA_KIM(select df. "CONTACT_KEY",df."CONTACT_DATE_KEY", df."CUST_NO",

ex."ACTION_CATEGORY",ex."ACTIVITY_DESC",ex."ACTIVITY_ID",ex."ACTIVITY_MAIN_OBJECTIVE",ex."ACTIVITY_OBJECTIVE"

from "FACT" df left outer join "FACT_EXT" ex on

df."CONTACT_KEY" = ex."CONTACT_KEY"

WHERE df."CONTACT_DATE_KEY" = 20181119) CT ;

quit ;

Re: Hot to perfrom explicit pass through in CAS with Using Oracle

BrunoMueller — Fri, 18 Jan 2019 08:59:00 GMT

Have a look at the example below. It creates a session cas library pointing to an Oracle database. As part of the library definition also the schema name is defined. You then use the CAS library name in the explicit FEDSQL pass through, so it knows how to talk to the DBMS.

/* test sas libname, the schema is set to the user */
libname v9ora oracle path="&oraPath" user="&oraUser" password="&oraPw";
proc contents data=v9ora._all_ nods;
run;

cas sugus sessopts=(metrics=true);

libname casuser cas caslib="casuser";

proc cas;
  action table.dropCaslib / caslib="xora" quiet=TRUE ; 
  action  table.addCaslib result=r /
      caslib="xora"
      datasource={srctype="oracle",
                  username="&oraUser",
                  password="&oraPw",
                  path="&oraPath"
                  schema="&oraSchema"
    };
   print r;
run;


proc cas;
  action table.fileinfo / caslib="xora" ;
run;

proc fedsql sessref=sugus;
  drop table casuser.dbms_result force;
  
  create table casuser.dbms_result as
  select * from connection to xora (
    /* DBMS specific SQL */
    select
      customer_country
      , customer_name
      , customer_age
      , max(customer_age) over(partition by customer_country ) as max_age
      , min(customer_age) over(partition by customer_country ) as min_age
      , avg(customer_age) over(partition by customer_country ) as avg_age
      , count(*) over(partition by customer_country ) as nkunden
    from
      kunden
    where
      customer_id between 4100 and 4200
    order by
      customer_country
      , customer_age desc

  );
quit;

Re: Hot to perfrom explicit pass through in CAS with Using Oracle

Rahul_B — Mon, 21 Jan 2019 11:57:33 GMT

Thanks - This works like charm !

But do you know if it possible to run oracle stored process in SAS Viya ?

for example like :

proc sql;

connect to oracle (user=userid password=password path=database_name);

execute (execute st_pr_name('parm')) by oracle;

disconnect from oracle;

quit;

Re: Hot to perfrom explicit pass through in CAS with Using Oracle

BrunoMueller — Mon, 21 Jan 2019 16:46:00 GMT

Yes you can use


proc fedsql;
  execute ( create table create_test (name varchar(20) ) ) by v9ora;
quit;

Please note, this time there is no SESSREF option in Proc FEDSQL, so this means the SQL is executed from the SAS process, but not run within the CAS server. v9ora refers to a SAS libref pointing to Oracle.