topic SAS Viya - ODBC Connection in Developers

SAS Viya - ODBC Connection

DionisMotor — Tue, 24 Mar 2020 15:05:02 GMT

Hi.

I'm trying to force working SAS Viya 3.5 <-> ODBC(Exasol) connection. ( Driver EXASOL_ODBC-6.1.3 )

What I have:

Table in Exasol DB defined as

CREATE TABLE CITIES ( CITY_ID DECIMAL(9,0), COUNTRY_CODE VARCHAR(2) UTF8, ZIP_CODE VARCHAR(20) UTF8, CITY_NAME VARCHAR(200) UTF8, DISTRICT VARCHAR(50) UTF8, AREA VARCHAR(50) UTF8, AREA_SHORT VARCHAR(50) UTF8, LAT DECIMAL(9,6), LON DECIMAL(9,6) );

plus one row of data:

Definition of ODBC connection:

caslib Exasol desc="Exasol_Caslib"
datasource=( srctype="odbc"
username="c64"
password="..."
odbc_dsn="exasol"
SCHEMA="C64"
)
libref=Exasol ;

loading data

proc casutil incaslib="Exasol" outcaslib="Exasol" ;
  droptable casdata="CITIES" quiet;
	load   casdata="CITIES" 
	datasourceoptions=(	SCHEMA="C64" )
	VARS=(	(NAME="CITY_ID" NFL=9 NFD=0)
			(NAME="COUNTRY_CODE")
           (NAME="ZIP_CODE"    )
           (NAME="CITY_NAME"   )
           (NAME="DISTRICT"    )
           (NAME="AREA"        )
           (NAME="AREA_SHORT"  )
			(NAME="LAT" NFL=9 NFD=6)
 			(NAME="LON" NFL=9 NFD=6)
		)
    casout="CITIES";
quit;

as a result I got:

proc sql;
select * from Exasol.CITIES;

ERROR: Invalid characters were present in the data.
ERROR: An error occurred while processing text data.
ERROR: Invalid characters were present in the data.
ERROR: An error occurred while processing text data.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

After I tried to use CLIENT_ENCODING "UTF-16" parameter

caslib Exasol desc="Exasol_Caslib"
     datasource=( srctype="odbc"
                  username="c64"
                  password="..."
                  odbc_dsn="exasol"
	SCHEMA="C64"
  CLIENT_ENCODING="UTF-16" 
				)
libref=Exasol ;

Data extracted correctly, but I have new ERROR when I'm trying save table:

proc casutil incaslib="Exasol" outcaslib="Exasol" ;
	SAVE  CASDATA="CITIES"
		  CASout="CITIES"
	datasourceoptions=( SCHEMA="C64" )
replace
 ;quit;

83 ! SAVE CASDATA="CITIES"
84 CASout="CITIES"
85 datasourceoptions=( SCHEMA="C64" )
86 replace
87 ;quit;
NOTE: Executing action 'table.save'.
NOTE: Performing serial SaveTable action using SAS Data Connector to ODBC.
ERROR: Invalid attribute/option identifier
NOTE: The target table has been dropped due to a failure during the Save action.
ERROR: Function failed.
ERROR: The action stopped due to errors.
NOTE: Action 'table.save' used (Total process time):
NOTE: The Cloud Analytic Services server processed the request in 0.579997 seconds.
ERROR: Failed to save the table "CITIES" from the "Exasol" caslib to the "Exasol" caslib.
NOTE: The SAS System stopped processing this step because of errors.

So, now, I'm run out of ideas what else I could try.

Do you know why I need use UTF-16 for fetching data if both Exasol and SAS are using UTF-8 ?
What wrong with saving data ? (this is more crucial question) Should I use something else for saving table from CAS to ODBC ?

Thanks for any hint.

Re: SAS Viya - ODBC Connection

joeFurbee — Fri, 27 Mar 2020 20:42:22 GMT

Hi @DionisMotor.

One of my colleagues suggested contacting Technical Support for this issue. He also referenced and SASGF paper that may shed some light from the ODBC side log. Here is his entire post:

Sounds like a tech support issue to me, this should not happen. At the same time, you might want to reference this SASGF 2017 paper. It has a section explaining how to set debug options for SAS Data Connectors. Just search the paper for the word "debug", the info starts on page 11. It shows how proc cas; action log /…; and proc casutil; contents …; can be used to display what the ODBC driver reports in terms of data types returned to CAS. Wouldn't be surprised if that is one of the first things TS would like to know.

Thanks,

Joe

Re: SAS Viya - ODBC Connection

Vince_SAS — Fri, 27 Mar 2020 20:40:34 GMT

Here is the link to the SAS Global Forum 2017 paper:

Introduction to SAS Data Connectors and SAS Data Connect Accelerators on SAS Viya
https://support.sas.com/resources/papers/proceedings17/SAS0331-2017.pdf

Vince DelGobbo

SAS R&D

Re: SAS Viya - ODBC Connection

DionisMotor — Mon, 30 Mar 2020 10:01:42 GMT

Thanks a lot for a hint. Debug information was really helpful.

I'm in contact with SAS support now. Let's hope we could solve this issue.

And side question: according to what I see in logs/documentation - when I'm trying "save table" to ODBC library I can't append data to already existing table.

Either SAS expecting no target table or with replace option SAS just remove it and recreate with own DDL. Did I miss such possibility or it is undocumented function?

Denys

Re: SAS Viya - ODBC Connection

DionisMotor — Fri, 03 Apr 2020 13:36:52 GMT

After discussion with SAS support :

Next line in odbc.ini on server could help with loading data

ENCODING =UTF8

Re: SAS Viya - ODBC Connection

DionisMotor — Thu, 09 Apr 2020 16:03:35 GMT

So, FYI, final solution was : do not use ODBC 🙂
We switched to JDBC driver and it works better. I'm still have open question about performance, but I guess this also will be solved.