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
... View more