topic Create a Hive table with SAS in SAS Programming

Create a Hive table with SAS

jimbarbour — Thu, 09 Jun 2022 22:21:39 GMT

We're converting from SAS to PySpark, 😞 which is why I haven't been around much lately. I'm trying to create a Hive table using SAS 9.4 M6 running on a "Windows 2016 Standard" (64 bit) server. My version of Hive is the MapR implementation, version Hive 2.3.6-mapr-1912-r13. I'm running Hive in an RHEL 8.5 environment. I only have an ODBC driver available to me.

My code is pretty straightforward:

LIBNAME	OPSIdflt	ODBC	SCHEMA=default				NOPROMPT="&CnxOPSIrsc";

OPTIONS	DBIDIRECTEXEC	=	YES;
%DelDS(OPSIDFLT.Cars_Test);
PROC	SQL;
	CREATE	TABLE	OPSIDFLT.Cars_Test	AS	
		SELECT	*	FROM	SASHELP.Cars;
QUIT;

I've tried the above code with and without the DBIDIRECTEXEC option, but neither works. See trace and error information, below.

ODBC_33: Prepared: on connection 4
SELECT * FROM default.CARS_Test WHERE 0=1 
 
 
Summary Statistics for ODBC are:
Total SQL prepare seconds were:                     0.007943
Total seconds used by the ODBC ACCESS engine were     0.008045
 
ODBC: AUTOCOMMIT is YES for connection 5
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
 
ODBC_34: Executed: on connection 5
CREATE TABLE default.CARS_Test (Make VARCHAR(13),Model VARCHAR(40),Type VARCHAR(8),Origin VARCHAR(6),DriveTrain 
VARCHAR(5),MSRP DOUBLE,Invoice DOUBLE,EngineSize DOUBLE,Cylinders DOUBLE,Horsepower DOUBLE,MPG_City DOUBLE,MPG_Highway 
DOUBLE,Weight DOUBLE,Wheelbase DOUBLE,Length DOUBLE)
 
 
ODBC_35: Prepared: on connection 5
INSERT INTO default.CARS_Test 
(Make,Model,Type,Origin,DriveTrain,MSRP,Invoice,EngineSize,Cylinders,Horsepower,MPG_City,MPG_Highway,Weight,Wheelbase,Length)
  VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )
 
 
ODBC_36: Executed: on connection 5
Prepared statement ODBC_35
 
ERROR: CLI execute error: [MapR][HiveODBC] (80) Syntax or semantic analysis error thrown in server while execurint query. 
       Error message from server: Error while compiling statement: FAILED: ParseException line 1:164 cannot recognize input 
       near '?' ',' '?' in expression specification
 
Summary Statistics for ODBC are:
Total SQL execution seconds were:                   0.463769
Total SQL prepare seconds were:                     0.008294
Total seconds used by the ODBC ACCESS engine were     0.473198

Regarding my effort to create a table in Hive using SAS: The create seems to go OK, but the insert appears to be failing. Presumably the question marks in the above log excerpt should be filled in by data pulled from SAShelp.Cars, but something isn't working right. I can create tables in our Hive environment with my ID using PySpark, so I believe I have the proper permissions. I can query data using the above Libname, so I don't think there's an issue with the Libname. Do you have any suggestions?

Jim

Re: Create a Hive table with SAS

ChrisNZ — Fri, 10 Jun 2022 03:50:47 GMT

Very long shot, but try proc append see if the generated code is different?

I wonder if the values are corrupted by SAS of by the ODBC driver. I'd lean toward the latter since SAS can do that fine with other access methods?

Re: Create a Hive table with SAS

jimbarbour — Fri, 10 Jun 2022 07:15:18 GMT

Hello, Chris,

Thank you for that idea. Unfortunately, I get the same result. It may well be, as you suggest, an ODBC driver issue (I wouldn't be surprised at all), but I have no idea how to address such a thing assuming that it is so.

I can see that my original Proc SQL did in fact create the table. If I use validvarname=V7, then SAS -- or more likely the driver -- prefixes the column names with the table name. If I use validvarname=ANY, then SAS (or the driver) wants to use a name literal with again the table name as a part of the column name. I'm suspicious that this may be what is screwing up the works. This most likely implicates the driver.

Jim

P.S. I also tried Proc FedSQL, but got a "Feature not supported" error (see below). Have I got a syntax error or something? Or can I literally not run a CTAS type query?

NOTE: Connection string:
NOTE: DRIVER=FEDSQL;CONOPTS= ( ( (DRIVER=ODBC;CONOPTS= 
   (DSN=OPSIRSC;AUTHMECH=3;HOST=rp000056703;PORT=10846;PWD=*;SCHEMA=default;UID=*);CATALOG=OPSIDFLT;SCHEMA=default)
      ); (DRIVER=BASE;CATALOG=WORK;SCHEMA= (NAME=WORK;PRIMARYPATH={X:\saswork\jbarbour\_TD44996_APSWP2335_\Prc2})); 
      (DRIVER=BASE;CATALOG=SASUSER;SCHEMA= (NAME=SASUSER;PRIMARYPATH={X:\sasuser\jbarbour})))
60         	CREATE	TABLE	OPSIDFLT.Cars_Test	AS	
61         		SELECT	*	FROM	WORK.Cars_Test;
ERROR: Feature not supported
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.

Re: Create a Hive table with SAS

SAS_ACE — Tue, 20 Sep 2022 15:41:53 GMT

Hi Jim,

Were you able to get this resolved and working?

Re: Create a Hive table with SAS

jimbarbour — Thu, 03 Nov 2022 22:15:43 GMT

Hi, @SAS_ACE ,

Thanks for asking. Sorry to get back to you so late. I sort of gave up hope and haven't been checking SAS Communities because they're doing away with SAS in my group at work.

I never did find a way to create and populate the tables using SAS. What I did was a work-around. I exported to a tab delimited dataset and then wrote a PySpark program to read the tab delimited and create a Hive table with that data. A bit kludgey, but it does work.

Jim