topic Re: Upload Data from SAS to Hadoop Impala in SAS Programming

Upload Data from SAS to Hadoop Impala

Bankshot — Thu, 25 Jun 2020 18:00:41 GMT

I've struggled to load an existing SAS dataset saved in a SAS server and load it to a designated db under the hadoop impala server. I've tried two ways of doing so but both were only able to create a table yet with no data loaded, seemingly for different reasons.

libname PrD	impala dsn=risk 	database=grn     user="&NBK@company.com" 	pw="&dbpass";
libname SAS "/retail/Adhoc";

/* Option 1 */
data PrD.smp_203;
	set SAS.smp_203;
run;

/* Option 2 */
proc sql;
connect to impala (dsn=&dsn database=&grn user="&NBK@company.com" pw="&dbpass");	
create table PrD.smp_203
    (BULKLOAD=YES
    BL_DATAFILE='/retail/Adhoc/'
    BL_HOST='MyIP_address'
    BL_PORT=50070)
as select * from SAS.smp_203;
quit;

The error I receive from 'Option 1' was, "ERROR: CLI execute error: [Cloudera][Support] (40470) Conversion error at column 1 and row 1: numeric value out of range." The first column of the SAS dataset was a numeric value with format 20. Was the error due to this particular column that has incompatible format to the hadoop server?

The error received as a result of 'Option 2' was a bit different: "ERROR: java.lang.NullPointerException

ERROR: CLI execute error: bulk load directory '/tmp' does not exist". I'm at a complete loss about this one and don't know where I'd start to debug this.

Any inputs on this are greatly appreciated, whether you were to comment just on one of the options I had above, or providing an alternative way of doing so. If you choose to comment on one of the above, please kindly provide a top-down comment on why the error occurred and a possible way of remediation.

Thanks community! I owe you, again.

Re: Upload Data from SAS to Hadoop Impala

yabwon — Thu, 25 Jun 2020 18:20:42 GMT

Did you saw this: https://support.sas.com/kb/63/350.html ?
Bart

Re: Upload Data from SAS to Hadoop Impala

Bankshot — Thu, 25 Jun 2020 19:14:09 GMT

I did see this. But first, I did use bulk load

BULKLOAD=YES

I'm also lost at its suggestion on to "make a copy of the core-site.xml file and move it into the SAS_HADOOP_JAR_PATH directory". I have no idea how this works. What is SAS_HADOOP_JAR_PATH

Re: Upload Data from SAS to Hadoop Impala

Bankshot — Thu, 25 Jun 2020 23:16:14 GMT

Update: I found out the reason on this. Apparently proc sql will not assign formats to derived variables, that's why the select * here wouldn't work. The second obstacle was when I referred to cccp_key’s format in SAS, I assumed it was format 20.0 based on the output from running proc contents. However, proc sql it requires the exact output so I had to change my format accordingly. The max number of my first column is 16-digit number, not 20. Hence:

proc sql;

      create table PrD.smp_203 as

      select account_key format=16.0 as account_key, date, etc, etc...

      from SAS.smp_203;

run;