Accessing Table in Impala where Table Name > 32 Char

[TylerP8813]

Background: We recently migrated from SQL Server to Impala Hadoop Environment. We use Kerberos for authentication. We also use an Autoexec at SAS startup to create our data libraries.

Does anyone know the proper syntax for writing a proc sql statement for creating a database object for such an environment in the event the filename exceeds 32 characters?

For example, the current proc sql step I use (for our former SQL Server environment) looks like this:

proc sql;
connect to odbc(DSN=MyDSNName user=ReadUser pw=ReadPass);
create table issue.newtable as
select * from connection to odbc
(select * from dbo.MyGoodnessWhyIsTheNameOfThisTableSoEmbarassinglyLong);
disconnect from odbc;
quit;

And no, changing the table name, at this time, is currently not an option.

[Reeza]

For those tables, use a direct pass through query or creating a view of the table with a truncated name are the two workarounds I'm familiar with for this issue. 

[TylerP8813]

That is what the example I provided does when we were connecting to the SQL environment. However, I'm trying to find the correct syntax for an Impala connection.

[Tom]

---

@TylerP8813 wrote:

That is what the example I provided does when we were connecting to the SQL environment. However, I'm trying to find the correct syntax for an Impala connection.

---

So your actual question has nothing to do with the length of the names of the tables?  Just how to connect to Impala?

[TylerP8813]

I'm connected to Impala using an ODBC connection. We also use an autoexecutable file that creates all the libraries we need at SAS startup. Within that autoexecutable, we've been using code to create pass through tables for those with names longer than 32 characters. I'm looking for the proper syntax for creating those pass-through's for impala.

[Reeza]

https://documentation.sas.com/doc/en/bicdc/9.4/bidsag/p08mx9tev28i7qn1o3hct8jqsljx.htm

You need to set up an ODBC to Impala and then connect the same way really. 

If this a server installation it may need to be set up on the server.

[TylerP8813]

I have an ODBC already set up. It works fine for all but the 25 or so tables with names longer than 32 characters. We've been using a pass-through for SQL up to this point. Since we migrated to Impala, I need to know the syntax and any other relevant info for the new pass-through statements.

[Reeza]

It's the same but you use the impala ODBC (line in red would change to Impala) and the impala version of SQL if you had any functions in the query, which you don't in your example.

proc sql;
connect to odbc(DSN=MyDSNName user=ReadUser pw=ReadPass);
create table issue.newtable as
select * from connection to odbc
(select * from dbo.MyGoodnessWhyIsTheNameOfThisTableSoEmbarassinglyLong);
disconnect from odbc;
quit;

[TylerP8813]

We use this proc sql statement to create newly named versions of the tables in a new library called "issue" so that they can be referenced from our code. I'm not necessarily trying to run a function, just create a copy of the table in a new library that SAS can reference.

[Reeza]

Ok. I think you have the answer to your question then, or is something still unclear/not working?

[Patrick · Accepted solution]

In case you've got already a working libname to Impala defined you can also use "connect USING" as an alternative to an explicit connection string.
Note: USING doesn't work with the libname meta engine.

proc sql;
connect using <SAS libref to impala> as imp;
create table issue.newtable as
select * from connection to imp
(select * from dbo.MyGoodnessWhyIsTheNameOfThisTableSoEmbarassinglyLong);
disconnect from imp;
quit;

If you want (or have) to stick with an explicit connection string and got already a libname defined then all the information you need is already available in the libname definition.
I certainly would avoid using explicit credential in a connect statement but would always use an authentication domain instead. 

[TylerP8813]

This appears to be the right solution but I keep getting this error:
"ERROR: CLI prepare error: [Cloudera][ImpalaODBC] (350) Max query retry limit exceeded. Retry Limit: 5"

Which may be due to some settings or issue with driver, etc. rather than SAS itself.