topic Re: Accessing Table in Impala where Table Name > 32 Char in SAS Programming

Accessing Table in Impala where Table Name > 32 Char

TylerP8813 — Fri, 12 Aug 2022 17:43:05 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

Reeza — Fri, 12 Aug 2022 17:51:07 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

TylerP8813 — Fri, 12 Aug 2022 18:43:26 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

Tom — Fri, 12 Aug 2022 20:01:38 GMT

@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?

Re: Accessing Table in Impala where Table Name > 32 Char

Reeza — Fri, 12 Aug 2022 20:14:09 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

TylerP8813 — Fri, 12 Aug 2022 20:14:48 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

TylerP8813 — Fri, 12 Aug 2022 20:16:49 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

Reeza — Fri, 12 Aug 2022 20:34:06 GMT

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;

Re: Accessing Table in Impala where Table Name > 32 Char

TylerP8813 — Fri, 12 Aug 2022 20:57:58 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

Reeza — Fri, 12 Aug 2022 21:08:03 GMT

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

Re: Accessing Table in Impala where Table Name > 32 Char

Patrick — Sat, 13 Aug 2022 04:29:50 GMT

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.

Re: Accessing Table in Impala where Table Name > 32 Char

TylerP8813 — Tue, 16 Aug 2022 20:43:37 GMT

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.