FEDSQL Syntax Problem -- How to Specify the Schema?

[jimbarbour]

I'm trying to read a Hive table using FEDSQL.  Eventually, I want to join the Hive table to an SQL Server table, but first I need to get the basic Hive query working.  I have the Hive query working in regular Proc SQL, but FedSQL complains that I'm not providing the Schema. 

Here's the error message from the log:

NOTE: Executing Proc FedSQL
NOTE: FEDSQL: Using libname WORK (engine V9).
NOTE: FEDSQL: Using libname FIN3823 (engine ODBC).
NOTE: FEDSQL: Using libname OPSI_RSC (engine ODBC).
NOTE: FEDSQL: Using libname SASUSER (engine V9).
NOTE: Connection string:
NOTE: DRIVER=FEDSQL;CONOPTS= ( (DRIVER=BASE;CATALOG=WORK;SCHEMA= 
      (NAME=WORK;PRIMARYPATH={X:\saswork\jbarbou3\_TD69644_APSWP2335_\Prc2})); 
      (DRIVER=ODBC;DB=DBSEP3823_FINANCE;UID=_prompt_;PWD=*;CATALOG= (FIN3823=Finance)); (DRIVER=ODBC;CONOPTS= 
      (DSN=OPSI_HIVE_RSC1;AUTHMECH=3;HOST=apvrp69817;PORT=10846;PROPERTIES={mapred.job.queue.name=root.opsictm_q1.dev_sq1};PW
      D=*;SCHEMA=Informatics_Prd;UID=jbarbou3);CATALOG=OPSI_RSC); (DRIVER=BASE;CATALOG=SASUSER;SCHEMA= 
      (NAME=SASUSER;PRIMARYPATH={X:\sasuser\jbarbou3})))
ERROR: Invalid connection string attribute : SCHEMA is required for this datasource
ERROR: PROC FEDSQL initialization failed.

However, my Libname very much specifies the schema; you can see the schema in the FEDSQL messages in the log excerpt, above.  I'm even specifying the schema twice which is a workaround I've used in the past but isn't working here.  Here's the Libname:

**------------------------------------------------------------------------------**;
**	Allocate Hive Database.														**;
**------------------------------------------------------------------------------**;
LIBNAME 	&Lib	ODBC	
	Schema		=	"&Schema"
	NoPrompt	=	"uid=jbarbou3; pwd=&lPwd_PASSWORD;
					host=&host;	dsn=&DSN; 
					schema=&Schema;
					port=&port; authmech=3;
					PROPERTIES=""&Queue"";
					"
					;

The Libname works fine with Proc SQL but works not at all with Proc FedSQL.  It's failing during initialization, so it's not the query itself that is causing the problem.

Any suggestions as to how to re-code the Libname?  Any ideas regarding some kind of workaround?  Maybe there's an option for FedSQL that I just don't know about?  

Thanks in advance,

Jim

[jimbarbour · Accepted solution]

Ah, well, I figured it out.  I was on the right track with thinking it was a Libname parameter that would do the job, but in fact one cannot use a Libname but rather must replace the Libname with a CONN parameter on the Proc FEDSQL statement itself.  In fact, one must clear the Libname if one exists or FEDSQL will issue a duplicate definition error.  As I suspected, I did have to supply the schema twice, and, while that may sound redundant, it made FEDSQL happy, and my query is now running.

Code:

		LIBNAME	OPSI_RSC	CLEAR;

		PROC	FEDSQL	CONN="DRIVER=FEDSQL;CONOPTS=((DRIVER=ODBC;
							CONOPTS=(DSN=&DSN;AUTHMECH=3;HOST=&Host;PORT=&Port;
							PROPERTIES={&Queue};PWD=&lPwd_PASSWORD;SCHEMA=&Schema;
							UID=&SysuserID);CATALOG=OPSI_RSC;SCHEMA=&Schema))"
							;

Notice that first I clear the corresponding Libname and then replace the Libname with the same information in a CONN statement.  The Catalog= parameter in the CONN statement is the equivalent of a Libname.   

The schema is listed twice, once in the "inner" CONOPTS and once in the "outer" CONOPTS.  The schema must be listed twice when using Authmech 3 type authentication.  This is a bit of an oddity in how SAS works, but this work around isn't too bad to code.

Note:  If you need to refer to the Libname again after the Proc FEDSQL step, you must code a second Libname statement.

Jim