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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.