Jeff,   It appears that HIVE EXTERNAL table is one of the best way to handle 32767 length issue however the problem I am running into is data truncation since the source base (hadoop) table has data stored as a string and new external table that I created has varchar, char and int.   Did you face this issue or have any suggestions/recommendations?   Thanks Matt ... View more

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 ... View more

Here is the correct Alter table syntax   ALTER TABLE sas_test.a CHANGE column_1 column_1 varchar(50); ... View more

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   ... View more

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.        ... View more

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. ... View more

Jeff,   I am back to bother you. It's getting more complicated now it seems.   I am trying to write my output SAS dataset to Impala and it appears all character fields are being saved as string so while pulling data back to SAS all character fields come with 32767 length.   DBMAX_TEXT and defining a length StringColumnLength= in odbc.ini is not going to work for me, due to the nature of the data.   This leaves me with only one option Alter Table property with SASFMT. Let me know if you know other better ways to handle the length issue while writing output to Impala.   Also, I read about SASFMT metadata option for Implicit sql. Can you please share some information how it can be used and where it can be used?   Thank you so much for all your help.   Matt ... View more

Thank you Jeff. I was able to get hold of Peter at SAS TS. It looks like I don't have write access to Impala. I was under wrong impression it seems. I am going to reach out to our hadoop admins and take it from there.   Thank you for your help.   ... View more

Thank you Jeff for your prompt response with details. We are on M2 and planning to upgrade to M4 soon. Your code will help me once we are on M4.   Do you buy any chance know the root cause of the issues I am facing while trying to connect Impala with server= instead of DSN?   Here is the libname and error message. Our impala cluster is kerberized and I do have valid ticket.I am getting same error with or without user name and password.    libname myimp impala server='fqdn' user=test password='xxxxx' schema=default;   data myimp.test; set sashelp.class; run;   /*libname myimp impala server='fqdn' user=test password=XXXXXXXXXXXXXXXXXX schema=default; ERROR: CLI error trying to establish connection: [unixODBC][Cloudera][ImpalaODBC] (100) Error from the Impala Thrift API: No more data to read. ERROR: Error in the LIBNAME statement. ... View more

Thank You JBailey for the suggestions and sample code. I have issues connecting to IMPALA with server name as well it works fine with DSN.   Based on what I found from http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0usrxwbz3hw7qn1nqb2zwp1ho84.htm page, it seems like with varchar the default SAS format would be $65355 so it actually going to take more space and time to process than string data type. Please correct me if I mis interpreted.   Data Conversion from Impala to SAS This table shows the default SAS formats that are assigned to SAS variables that are created when SAS/ACCESS reads Impala table columns. Note: In the third maintenance release for SAS 9.4, support for CHAR and VARCHAR data types was added. Impala to SAS: Default SAS Formats for Impala Data Types Impala Data Type SAS Data Type Default SAS Format CHAR character $255. STRING $32767. VARCHAR $65355 BOOLEAN numeric 1. BIGINT 20. DOUBLE none FLOAT none INT 11. SMALLINT 6. TINYINT 4.   I was informed about CAST function from SAS TS but as you said it's a very time consuming specially when there are multiple tables and 100's of columns in each table that needs to be modified.    I looked at the SAS notes which talks about limit the length of the strings in ODBC driver but the issue is it is going to limit the length to one that user mention in StringColumnLength= within odbc.ini for each variable regardless the actual value of particular variable. Let's say I define length in StringColumnLength=250 in odbc.ini then all string fields will have 250 length and the fields which has data more than 250 character bytes will get truncated.         ... View more

Can you please share the sample code with DBMS_TXT= option in libname and dataset name? I tried but length comes as 32767 even with DBMS_TXT= option.    Thank You! Mathur ... View more