PROC SQL;
Delete FROM BI_BGRS.TEMP_APR18 t1
WHERE t1.PROCESS_TYPE = 'ZSC1' AND (t1.ZHDL = . and t1.ZPRE = .) ;
QUIT;
As we are having Millions (103 Million) of Records , we want change the present SAS Query to Hadoop Query Because it is taking Huge time and meanwhile its getting stucked all the time.
And what is the question? This:
Delete FROM BI_BGRS.TEMP_APR18 t1
WHERE t1.PROCESS_TYPE = 'ZSC1' AND (t1.ZHDL = . and t1.ZPRE = .) ;
Looks like ANSI SQL, so should work on the database like that. You may need to change the . to the missing value or function of the database, and it may be that you need to change the single quote to double. otherwise I can't see an issue. Have you tried it.
Why the brackets? Also, avoid coding in upper or mixed case.
where t1.process_type = 'ZSC1' and (t1.zhdl = . and t1.zpre = .) ;
The above query mainly deletes the process type whose type is zsc1 and also deletes the Level of Services who are missing .
Its no different to:
where t1.process_type = 'ZSC1' and t1.zhdl = . and t1.zpre = .;
I haven't had real hand-on with Hadoop yet 😞 so only on a generic level when interfacing with any database:
If using implicit SQL (SAS SQL) then make sure to not use any function or expression which the SAS Access engine can't translate into database specific syntax. If such a translation is not possible and SAS can't push execution to the database then what happens is that all the data gets first transferred to the SAS side for execution - and this can have a huge performance impact.
To avoid such unfavorable scenarios:
1. Use options "options sastrace=',,,d' sastraceloc=saslog nostsuffix;" as this will show you in the SAS log what part of the code SAS was able to send to the remote database for execution.
2. Eventually use explicit pass-through as this will a) ensure that the code always executes on the database side and b) will allow you to take advantage of database functions not available in SAS.
23 options sastrace=',,,d' sastraceloc=saslog nostsuffix;
24
25 proc sql;
26 create table work.filter_for_temp as
27 select * from hdp_hsf.temp_apr18 t1
28 where t1.process_type = 'ZSC1' and (t1.zhdl = . and t1.zpre = .) ;
HADOOP_1: Executed: on connection 1
USE `analytics_hsf`
HADOOP_2: Prepared: on connection 1
SHOW TABLES 'TEMP_APR18'
HADOOP_3: Prepared: on connection 1
DESCRIBE FORMATTED TEMP_APR18
HADOOP_4: Prepared: on connection 1
SELECT * FROM `TEMP_APR18`
HADOOP_5: Prepared: on connection 1
SELECT `TEMP_APR18`.`business_partner`, `TEMP_APR18`.`business_agreement`, `TEMP_APR18`.`product_guid`, `TEMP_APR18`.`los`,
`TEMP_APR18`.`report_date`, `TEMP_APR18`.`bundle_seq_id`, `TEMP_APR18`.`process_type`, `TEMP_APR18`.`product_life_indicator`,
`TEMP_APR18`.`contract_status`, `TEMP_APR18`.`product_status`, `TEMP_APR18`.`prod_cnt`, `TEMP_APR18`.`product_start_date`,
`TEMP_APR18`.`product_end_date`, `TEMP_APR18`.`months_to_go`, `TEMP_APR18`.`tax_rate`, `TEMP_APR18`.`zopt`, `TEMP_APR18`.`zrda`,
`TEMP_APR18`.`zrdp`, `TEMP_APR18`.`zcaf`, `TEMP_APR18`.`zcap`, `TEMP_APR18`.`zjc1`, `TEMP_APR18`.`zjc2`, `TEMP_APR18`.`zmpf`,
`TEMP_APR18`.`zmpp`, `TEMP_APR18`.`zpda`, `TEMP_APR18`.`zptd`, `TEMP_APR18`.`zvi1`, `TEMP_APR18`.`zvi2`, `TEMP_APR18`.`zvt1`,
`TEMP_APR18`.`zvt2`, `TEMP_APR18`.`zhdl`, `TEMP_APR18`.`zqu1`, `TEMP_APR18`.`zwip`, `TEMP_APR18`.`zpre`, `TEMP_APR18`.`zncd`,
2 The SAS System 10:57 Thursday, May 31, 2018
`TEMP_APR18`.`znpd`, `TEMP_APR18`.`znrd`, `TEMP_APR18`.`zl0a`, `TEMP_APR18`.`zld0`, `TEMP_APR18`.`zld1`, `TEMP_APR18`.`zd03`,
`TEMP_APR18`.`zd04`, `TEMP_APR18`.`zcd1`, `TEMP_APR18`.`zd06`, `TEMP_APR18`.`zd02`, `TEMP_APR18`.`zcd2`, `TEMP_APR18`.`zd05`,
`TEMP_APR18`.`zcd3` FROM `TEMP_APR18` WHERE ( ( `process_type` = 'ZSC1' ) AND ( `zhdl` IS NULL ) AND ( `zpre` IS NULL ) )
NOTE: Compressing data set WORK.FILTER_FOR_TEMP decreased size by 95.54 percent.
Compressed is 7 pages; un-compressed would require 157 pages.
NOTE: Table WORK.FILTER_FOR_TEMP created, with 2032 rows and 48 columns.
29 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 55.21 seconds
user cpu time 0.15 seconds
system cpu time 0.05 seconds
memory 4010.84k
OS Memory 25248.00k
Timestamp 05/31/2018 12:20:51 PM
Step Count 3 Switch Count 104
Page Faults 0
Page Reclaims 561
Page Swaps 0
Voluntary Context Switches 2287
Involuntary Context Switches 169
Block Input Operations 168
Block Output Operations 1808
30
31 GOPTIONS NOACCESSIBLE;
32 %LET _CLIENTTASKLABEL=;
33 %LET _CLIENTPROJECTPATH=;
34 %LET _CLIENTPROJECTNAME=;
35 %LET _SASPROGRAMFILE=;
36
37 ;*';*";*/;quit;run;
38 ODS _ALL_ CLOSE;
39
40
41 QUIT; RUN;
42
I am running the same query on Hadoop Hive but it throws an error still SAS excutes.
Is this the full log or have you remove lines?
I believe I should see the prepared statements in the log but then also a log message which tells me which one of t he prepared statements has actually been executed.
As for the timings: You've stated that this takes a "huge" amount of time. Is this the 55 seconds real time?
real time 55.21 seconds user cpu time 0.15 seconds system cpu time 0.05 seconds
Real time is much higher than CPU time. What data volumes are you transferring form Hadoop to SAS? Could it be that the WHERE clause gets only executed on the SAS side? The log message which SQL code got executed should tell us.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.