topic Re: Running fluid query from SAS in SAS Programming

Running fluid query from SAS

kajal_30 — Mon, 04 Jul 2022 16:20:08 GMT

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

Re: Running fluid query from SAS

SASKiwi — Mon, 04 Jul 2022 20:19:10 GMT

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

Re: Running fluid query from SAS

kajal_30 — Tue, 05 Jul 2022 02:05:57 GMT

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

Re: Running fluid query from SAS

SASKiwi — Tue, 05 Jul 2022 03:43:41 GMT

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

proc product_status;
run;

proc setinit;
run;

Re: Running fluid query from SAS

kajal_30 — Tue, 05 Jul 2022 17:41:37 GMT

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

Re: Running fluid query from SAS

Tom — Tue, 05 Jul 2022 19:19:40 GMT

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?

Re: Running fluid query from SAS

SASKiwi — Tue, 05 Jul 2022 20:10:29 GMT

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

Re: Running fluid query from SAS

kajal_30 — Tue, 05 Jul 2022 23:12:56 GMT

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

Re: Running fluid query from SAS

SASKiwi — Tue, 05 Jul 2022 23:37:37 GMT

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;

Re: Running fluid query from SAS

kajal_30 — Wed, 06 Jul 2022 14:22:20 GMT

@SASKiwi Thank you and getting error

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

Regards

Kajal

Re: Running fluid query from SAS

SASKiwi — Wed, 06 Jul 2022 19:48:54 GMT

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

Re: Running fluid query from SAS

Tom — Wed, 06 Jul 2022 20:43:46 GMT

The only TOAD I know is a tool for connecting to databases and submitting code.

Is that what you are doing?

What database are you connecting to?

Did you try connecting to the same database from SAS and then using the EXECUTE statement of PROC SQL to push your current SQL to that database instead of using TOAD to push the SQL to the database?

Re: Running fluid query from SAS

kajal_30 — Thu, 07 Jul 2022 02:34:12 GMT

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.

Re: Running fluid query from SAS

SASKiwi — Thu, 07 Jul 2022 04:12:07 GMT

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.

Re: Running fluid query from SAS

kajal_30 — Tue, 12 Jul 2022 03:11:57 GMT

Though it is running fine but still not adding any records to the final table. May I know the possible reasons ?

Regards

Kajal

Re: Running fluid query from SAS

SASKiwi — Tue, 12 Jul 2022 04:24:57 GMT

Can you post the complete SAS log of your latest run including SAS notes?

Re: Running fluid query from SAS

Patrick — Tue, 12 Jul 2022 04:59:35 GMT

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.

Re: Running fluid query from SAS

kajal_30 — Tue, 12 Jul 2022 13:05:02 GMT

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

Re: Running fluid query from SAS

kajal_30 — Tue, 12 Jul 2022 13:15:34 GMT

Here is one error I am getting after doing some troubleshooting

ERROR: CLI execute error: ERROR: Error from JDBC driver: Error while compiling statement: FAILED: SemanticException [Error 10004]:
Line 1:5175 Invalid table alias or column reference 'process_date': (possible column names are: )

After adding table alias 't' getting error:

ERROR: CLI execute error: ERROR: Error from JDBC driver: Error while compiling statement: FAILED: ParseException line 1:5168
missing EOF at 't' near 'source_table'

Re: Running fluid query from SAS

Tom — Tue, 12 Jul 2022 13:44:08 GMT

Are you sure the code you posted here is what you are running via TOAD?

It seems to use an extremely strange syntax.

The start looks normal enough, basically requesting to insert some values for 7 selected variable into an existing dataset ("table" in SQL speak).

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

What should go after the FROM keyword is a table (or something that looks like a table, such as a view or a subquery).

But instead you have this code that I cannot understand.

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

What does TABLE mean here?

What does WITH mean here?

What does FINAL() mean here? Is it a function call? Is it different than the final above which was the name of a table in the NZ_TBL schema?

What does HD2() mean here? That looks a lot like a function call. What type of function is that? It looks like it takes three arguments that are all character strings. What do they mean? What does it return? Does it return something that looks like a "table" ?