hello, I have below code where I am connecting from sas to hadoop and it is failing with below error and I am quite not sure what it is,
can someone please help,
10 proc sql outobs=100;
11 connect to impala (datasrc=xxx user=abc@.company.com pw=xxx database=whatever);
12 create table test as select * from connection to impala
13 (select var1,
var2,
var3
14 from whatver.table_name
15 where var3='2017-10-17'
16 );
ERROR: PROC SQL requires any created table to have at least 1 column.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
18 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE SQL used (Total process time):
real time 1.18 seconds
cpu time 0.05 seconds
The first two things I'd check would be:
thanks,
yes, same query running fine from other sas server and output records.
Are you able to execute any other query to that DB and table?
if I run just connection, it seems it works fine,
4 proc sql outobs=100;
5 connect to impala as test2 (datasrc=xxx user=abc@.company.com pw=xxx database=whatever);
6 DISCONNECT FROM test2;
7
8 quit;
^L2 The SAS System Thursday, October 19, 2017 09:56:00 AM
NOTE: PROCEDURE SQL used (Total process time):
real time 1.16 seconds
cpu time 0.04 seconds
Is var3 a date variable or is it character because it looks like you're treating it as character in the query which would be somewhat unusual?
i am pulling data from hadoop and have same query in different server with same configuration and its running fine.
however, i still believe i am missing some configuration on this server, not sure,
tried different table with libname statement,
options msglevel=I sastrace=',,,d' sastraceloc=saslog nostsuffix;
libname hdp impala dsn=xxx UID=abc PWD=whatever;
proc sql outobs=1000;
create table testlib.test as
(select var1,
var2,
var3
from hdp.table_name
where
var1 >= '2017-10-16'
and var2=<= '2017-10-17'
);
quit;
run;
Log,
NOTE: Libref HDP was successfully assigned as follows:
Engine: IMPALA
IMPALA_1: Prepared: on connection 0
SELECT * FROM `table_name`
ERROR: Table HDP.table_name doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
ERROR: Error trying to read from a DBMS table. At least one column must be selected.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
40
41 quit;
one more thing I found from /var/log/messages is,
sas: looking for plugins in '/opt/cloudera/impalaodbc/lib/64/sasl2', failed to open directory, error: No such file or directory
have these parameters from .odbc.ini file
[instance1]
Description=whatever
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so
Description=Cloudera ODBC Driver for Impala(64-bit) DSN
Host=abc.com
Port=xxxxx
Database=default
AuthMech=3
UseSASL=1
SSL=1
CAIssuedCertNamesMismatch=1
AllowSelfSignedServerCert=1
TrustedCerts=/opt/cloudera/impalaodbc/lib/64/cacerts.pem
TSaslTransportBufSize=2000
RowsFetchedPerBlock=10000
SocketTimeout=0
StringColumnLength=32767
UseNativeQuery=0
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.