BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ChrisNZ
Tourmaline | Level 20

How come you get fields of type string? The fields created for me when exporting are of type varchar() and therefore have the correct length.

 

Running:

 

data IMPALA1.TEST; 
  A='1';
run;

proc sql;
  connect using IMPALA1;
  select * from connection to IMPALA1 ( describe extended `test` ); 
quit;

proc contents data=IMPALA1.TEST; 
run;

 

shows that the variable has a length of 1:

 

name type comment
# col_name data_type comment
     
a varchar(1)  
     

 

 

Alphabetic List of Variables and Attributes
# Variable Type Len Format Informat Label
1 a Char 1 $1. $1. a

 

 

Matt
Quartz | Level 8
Chris, I am not sure why it creates a table with string data type. We are on SAS 9.4M2 and Impala version is 2.3.0+cdh5.5.2+0. and I believe varchar is supported from 9.4M3 and later, that could be reason why it writes all character data as string. Attached proc content output. Also, below syntax is not supported either. proc sql; connect using myimp; select * from connection to myimp ( describe extended 'length' ); quit; Here is the error RROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Data source name not found, and no default driver specified NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 25 select * from connection to myimp ( describe extended 'length2' ); ERROR: The MYIMP engine cannot be found. ERROR: A Connection to the myimp DBMS is not currently supported, or is not installed at your site.
ChrisNZ
Tourmaline | Level 20

I am on 9.4 M3. That's probably the reason.

 

The syntax connect using requires that a libname statement be run beforehand:

 

libname IMP1 impala <connection parameters>  ;

proc sql;

connect using IMP1 ;

Matt
Quartz | Level 8

Right, I already have a libname on top of proc sql connect statement. I think the issue is I am not able to connect to Impala with server= option, it only works with DSN= option. I am in the process of troubleshooting the issue. If you know any steps to consider for resolution let me know. 

 

 

 

Matt
Quartz | Level 8

This is what I did to handle the lenght issue while writing a dataset/file to Impala from SAS, since we are on 9.4M2 so VARCHAR is not supported and seems like alter table properties with SASFMT is not supported either and this leaves me with only 2 option.

 

(1) Create a table in Impala and define a datatype and insert data to that table. The only caveate is that the source has to be in Impala and the source table has to have same length but in my case the source is an oracle table so when I write Oracle table to Impala all character fields gets string datatype so the lenght is 32767 and it won't let me insert data from this table to the table I created with create table statement because the datatype and length are different. So technically this won't work.

 

But I found the work around and this one works fine.

 

(2) Write a sas datasetp, save result dataset to Impala. Then ran ALTER table statement on Impala table, this will assign appropriate length and datatype varchar that you used in alter table statement. 

 

LIBNAME myimp impala DSN=impala schema=sas_test;
data myimp.a;
set oracle_lib.oracle_source_table;
run;

 

ALTER TABLE sas_test.a column_1 column_1 varchar(50);

 

You have to do this for all character columns. 

 

Thanks!

Matt

 

Matt
Quartz | Level 8

Here is the correct Alter table syntax

 

ALTER TABLE sas_test.a CHANGE column_1 column_1 varchar(50);

Matt
Quartz | Level 8

Jeff,

 

I am trying to use server= option in libname statement for Impala and running into issues. Here is the libname statement I am using. 

 

libname myimptes impala server='ustsmasmywsp669.prod' schema=default; Since we have a kerberized hadoop/Impala cluster I am not passig username and password and AuthMech is set to 1 in odbc.ini

 

ERROR: CLI error trying to establish connection: [unixODBC][Cloudera][ImpalaODBC] (100) Error from the Impala Thrift API: No more
data to read.

 

Let me know if you have experience this issue before and have any suggestions.

 

Thank You!

Matt

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 22 replies
  • 13543 views
  • 4 likes
  • 5 in conversation