SAS/Access Hadoop String Columns - Field Length Options

[ChrisNZ]

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]

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]

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]

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]

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]

Here is the correct Alter table syntax

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

[Matt]

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

[KatT_sas]

Here’s where you can find related topics in the SAS/ACCESS documentation.

• Address Issues When Converting Data from Hive to SAS for Pass-Through SQL
• SAS/ACCESS Interface to Impala
• SQL Pass-Through Facility Specifics for Impala
• Highlighting New Hive .12 Data Types
• Issues When Converting Data from Hive to SAS
• Address Issues When Converting Data from Hive to SAS with Table Properties
• Alternatives to Table Properties for Issues with Data Conversion from Hive to SAS
• DBMAX_TEXT= LIBNAME Option
• DBMAX_TEXT= Data Set Option