topic Re: Convert the code from SAS to HADOOP in SAS Enterprise Guide

Convert the code from SAS to HADOOP

saikiran_nemani — Thu, 31 May 2018 10:26:16 GMT

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.

Re: Convert the code from SAS to HADOOP

RW9 — Thu, 31 May 2018 10:33:54 GMT

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.

Re: Convert the code from SAS to HADOOP

saikiran_nemani — Thu, 31 May 2018 10:40:34 GMT

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 .

Re: Convert the code from SAS to HADOOP

RW9 — Thu, 31 May 2018 10:42:43 GMT

Its no different to:

where t1.process_type = 'ZSC1' and t1.zhdl = . and t1.zpre = .;

Re: Convert the code from SAS to HADOOP

Patrick — Thu, 31 May 2018 10:49:12 GMT

@saikiran_nemani

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.

Re: Convert the code from SAS to HADOOP

saikiran_nemani — Thu, 31 May 2018 11:25:50 GMT

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.

Re: Convert the code from SAS to HADOOP

Patrick — Thu, 31 May 2018 22:16:59 GMT

@saikiran_nemani

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.