proc sql error

Reply
Super Contributor
Super Contributor
Posts: 404

proc sql error

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

Valued Guide
Posts: 561

Re: proc sql error

The first two things I'd check would be:

 

  1. Does the user have permissions to access that table; and
  2. Do any rows match the where clause
Super Contributor
Super Contributor
Posts: 404

Re: proc sql error

Posted in reply to ChrisBrooks

thanks,

yes, same query running fine from other sas server and output records.

Valued Guide
Posts: 561

Re: proc sql error

Are you able to execute any other query to that DB and table?

Super Contributor
Super Contributor
Posts: 404

Re: proc sql error

Posted in reply to ChrisBrooks

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

 

 

        

Valued Guide
Posts: 561

Re: proc sql error

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?

Super Contributor
Super Contributor
Posts: 404

Re: proc sql error

Posted in reply to ChrisBrooks

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, 

 

Super User
Posts: 5,829

Re: proc sql error

Have you looked at the SQLXRC and SQLXMSG macro variables?
Also, I can't anything on your query that requires an explicit pass through.
Use a libname instead, and set
Options msglevel=I sastrace=',,,d' saatraceloc=saslog nostsuffix;
Then Hive should feed back what's going on.
Data never sleeps
Super Contributor
Super Contributor
Posts: 404

Re: proc sql error

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;

 

 

 

Super Contributor
Super Contributor
Posts: 404

Re: proc sql error

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

Ask a Question
Discussion stats
  • 9 replies
  • 621 views
  • 0 likes
  • 3 in conversation