Running fluid query from SAS

[kajal_30]

Good morning Team,

I am trying to run a fluid query from SAS EG. Could you please advise the best way to execute a Netezza sql fluid query using SAS code. Fluid query is transferring data from Hadoop to Netezza. I am only restricted to use fluid query as there is currently no information available to make other connections.

Regards

Kajal 

[SASKiwi]

Do you have a working data connection from SAS to Netezza already? If so what SAS/ACCESS product are you using?

[kajal_30]

Hi @SASKiwi ,

Can you tell me how can I see that SAS/ACCESS ? 

so here is the scenario. my source data is in Hadoop and target location is Netezza. So fluid query is reading data from Hadoop and loading it into Netezza also it is working fine and expected when I am running this query on a DB tool.

Regards

Kajal

[SASKiwi]

Run this program in SAS to confirm both your installed and licensed products:

proc product_status;
run;

proc setinit;
run;

[kajal_30]

For Base SAS Software ...
Custom version information: 9.4_M6
Image version information: 9.04.01M6P110718
For SAS/STAT ...
Custom version information: 15.1
For SAS/GRAPH ...
Custom version information: 9.4_M6
For SAS/ETS ...
Custom version information: 15.1
For SAS/FSP ...
Custom version information: 9.4_M6
For SAS/OR ...
Custom version information: 14.2
Image version information: 9.04.01M4P110916
For SAS/AF ...
Custom version information: 9.4_M6
For SAS/IML ...
Custom version information: 15.1
For SAS/SHARE ...
Custom version information: 9.4_M5
For SAS/ASSIST ...
Custom version information: 9.4
Image version information: 9.04.01M0P061913
For SAS/CONNECT ...
Custom version information: 9.4_M6
For SAS/EIS ...
Custom version information: 9.4_M6
2 The SAS System Tuesday, July 5, 2022 09:07:00 AM

For SAS/ACCESS Interface to Netezza ...
Custom version information: 9.43
For SAS OLAP Server ...
Custom version information: 9.4
For SAS Enterprise Miner ...
Custom version information: 15.1
For SAS/ACCESS to Impala ...
Custom version information: 9.45
For SAS/IntrNet ...
Custom version information: 9.4_M6
For SAS Integration Technologies ...
Custom version information: 9.4_M6
For SAS/Secure 168-bit ...
Custom version information: 9.41_M3
For SAS Credit Scoring ...
Custom version information: 15.1
For SAS Data Quality Server ...
Custom version information: 9.46
For High Performance Suite ...
Custom version information: 2.2_M7
For SAS/ACCESS Interface to DB2 ...
Custom version information: 9.4_M2
For SAS/ACCESS Interface to Oracle ...
Custom version information: 9.43
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M6
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M6

[SASKiwi]

So you have SAS/ACCESS Interface to Netezza. That's the best option for running SQL queries in Netezza from SAS.

[Tom]

How do you "run" a "fluid query" now?  

Do you have to login to some website and push some button?

Does it have an API to allow you to trigger the execution via a program?

[kajal_30]

I am running a fluid query in TOAD data point. Adding sample fluid query I am running in Netezza to get data from Hadoop. Where hd2 is a fluid query. Just thinking a way to run similarly in SAS EG but it is not recognizing fluid query.  

INSERT INTO NZ_tbl.final(
	rec_type
	, rv
	, trends 
	, account            
	, address          
	, client            
	, segment           

	)
	SELECT 
	rec_type
	, rv
	, trends 
	, account            
	, address          
	, client            
	, segment  
	FROM TABLE
	WITH FINAL (hd2('db.dev',
	'',
	'SELECT 
	rec_type
	, rv
	, trends 
	, account            
	, address          
	, client            
	, segment
	FROM view
	where date = 20220526
	'))
;

 Thanks

Kajal

[SASKiwi]

You can run that exact code in SAS using SQL Passthru and the EXECUTE statement. The program would look similar to this, bearing in mind you need to add your correct Netezza LIBNAME settings:

libname netezza odbc noprompt = "server=MyServerName;DRIVER=MyNetezzaDriver;Trusted Connection=yes;";

proc sql;
 connect using netezza;
 
  execute 
   (
INSERT INTO NZ_tbl.final(
	rec_type
	, rv
	, trends 
	, account            
	, address          
	, client            
	, segment           

	)
	SELECT 
	rec_type
	, rv
	, trends 
	, account            
	, address          
	, client            
	, segment  
	FROM TABLE
	WITH FINAL (hd2('db.dev',
	'',
	'SELECT 
	rec_type
	, rv
	, trends 
	, account            
	, address          
	, client            
	, segment
	FROM view
	where date = 20220526
	'))
   ) by netezza
  ;
quit;

[kajal_30]

@SASKiwi Thank you and getting error

ERROR: CLI execute error: ERROR: 'INSERT INTO nz_tbl.final ( rec_type,rv,trends,

Regards

Kajal

[SASKiwi]

@kajal_30 - It  looks like your error message is not complete. Did you post all of it?

[kajal_30]

see below the rest of the portion

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
553 quit;
NOTE: The SAS System stopped processing this step because of errors.

[SASKiwi]

Normally a CLI error includes more information than this:

ERROR: CLI execute error: ERROR: 'INSERT INTO nz_tbl.final ( rec_type,rv,trends,

It would help if you posted the complete SAS log including errors and notes.

[Patrick]

You sure this quote in your code should be there?

Using pass-through SQL as you do SAS does nothing else than sending the code "as-is"  to the database and though it's VERY similar to running code via Toad. 

If your code runs via Toad and via the same user than you use for your SAS session then there is no reason why you should get this CLI error using SAS or why you should get a different result if the code executes without errors.

It's actually a very common development approach for such scenarios to first develop the code "outside" of SAS via some client and only once the code works copy/paste it into some SAS code section for explicit pass-through SQL.

[kajal_30]

yes exactly this code is developed in toad and running absolutely fine and loading data when running in toad but failing in SAS only also doing the same just pasting the working code inside sql pass through in sas.

Regards

Kajal