BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
TylerP8813
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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. 

View solution in original post

11 REPLIES 11
Reeza
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

@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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User

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

Patrick
Opal | Level 21

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
Obsidian | Level 7

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1349 views
  • 3 likes
  • 4 in conversation