Hi @Matt
The option is DBMAX_TEXT=, but that won't help you. It appears that there is a defect lurking here. I tried this code:
/* This does not work!!! */
libname myimp impala server=domo7 user=myuser pw=mypasswd dbmax_text=255;
proc sql;
connect using myimp;
execute (create table jefftest(x string)) by myimp;
execute (insert into jefftest values('a')) by myimp;
quit;
data work.jefftest;
set myimp.jefftest;
run;
Unfortunately, the above code does not work. Frustrating, but not catastrophic. I am going to enter a defect on this.
You can tell the ODBC driver to limit the length of the strings. Here is a SAS note that discusses it:
http://support.sas.com/kb/53/835.html
This code does work:
/* notice I am using a DSN */
/* I limited the string length using it */
libname myimp impala dsn=domo7dsn user=myuser pw=mypasswd ;
proc sql;
connect using myimp;
execute (create table jefftest(x string)) by myimp;
execute (insert into jefftest values('a')) by myimp;
quit;
data work.jefftest;
set myimp.jefftest;
run;
Here is what I did in the driver setup (Advanced Options tab):
If you are using a recent version of Impala you can use the VARCHAR data type instead of string. It doesn't have the 32K string problem.
This may help others who stumble upon this thread - take a look at the course materials from the SGF SAS and Hadoop workshop. They are not specific to Impala, it is explained there:
https://github.com/Jeff-Bailey/SGF2016_SAS3880_Insiders_Guide_Hadoop_HOW
Althought the DBMAX_TEXT= discussion turns-out to be a huge lie, this SGF 2016 paper may help with Impala:
https://sasglobalforum2016.lanyonevents.com/connect/sessionDetail.ww?SESSION_ID=3960
The workshop materials explain the 32k String Thing (trademark pending 😉 in great detail. It is for SAS/ACCESS Interface to Hadoop but the concepts are the same. It does not include the DBMAX_TEXT= (which appears to not work) option. Update: I am being told that this option is documented to only work with blob-type data, and Impala doesn't technically support any blob/clob, so it doesn't apply for Impala.
I am not buying into this, but it may be legit. In the meantime, changing the configuration of the ODBC DSN does work. So that is a possiblity.
Here are the options that will work (In order from best to worst - in my opinion):
Use VARCHAR data type instead of STRING
Cast STRING columns to something else (this is a lot of work and could be seen as the worst solution)
Limit the length of strings via the ODBC driver (see above)
Adding extended table attributes to the table may help. I will have to test this.
... View more
