Odd Error When Running Hive Queries in SAS

[jimbarbour]

Environment:
I'm running SAS (r) Proprietary Software Release 9.4  TS1M4.  I'm executing, as I understand it, on a Linux box hosted by another company.  I don't have command line access, so I can't do my usual tricks to determine what exactly I'm running on, but it's in the UNIX/Linux family.  When I check the server properties in SAS EG, I see OS=Linux OS Family=Lin x64 OS Version 3.10.0-693.17.1el7x86_64.

I'm pulling data from a Hadoop Cluster using Hive QL executed as a pass through query from a SAS program running under SAS EG 7.1.5 HF3..  We're using the Cloudera distribution of Hadoop.  I want to format a number into a string.  For display purposes, I want to put commas in the number.  For example:  999999999 becomes 999,999,999 when formatted.  Looking into the Apache Hadoop documentation (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF), I see a function called format_number.  Seems like a winner.  It wants a number and an integer as arguments.  The integer specifies the number of decimal places.  It returns a string, supposedly formatted with commas separating the digits in the standard fashion.

However, when I use the function in a query, I can't get it to run.  I get the following "highly descriptive" error message:
ERROR: Execute error: Error while processing statement: FAILED: Execution Error, return code 3 from org.apache.hadoop.hive.ql.exec.spark.SparkTask

The query is included, below.  The query runs fine if I remove the line that has the format_number function.

My guess, and it's not much more than that, is that this is some kind of a memory issue, but I'm not really sure.  I've done a Google search on the error message, but it seems to be fairly generic and not indicative of any one specific issue if I'm understanding what I'm reading correctly.

Has anyone encountered this before?  Can anyone shed any light on this?

Jim

	CREATE	TABLE	&Target_DB..&Prefix.&Source_Table._&Secondary_Table.&Version		AS
			SELECT	
				Primary.consumer_key
				,Primary.transaction_key
				,record_ident
				,record_len
				,hr_profiledate
				,hr_mkey_len
				,hr_mkey_text
				,tr_spccmt
				,tr_evaluation
				,tr_opendate
				,tr_statdate
				,tr_type
				,tr_terms
				,f_tr_trms
				,tr_amount1
				,format_number(f_tr_amt1_n,	0)	AS	f_tr_amt1
				,f_tr_amt1_n
				,tr_amount1_qual
			FROM	&Target_DB..&Prefix.&Source_Table.&Version				Primary
			FULL	JOIN	&Target_DB..&Prefix.&Secondary_Table.&Version	Secondary
				ON	Primary.consumer_key		=	Secondary.consumer_key
				AND	Primary.transaction_key		=	Secondary.transaction_key

[LinusH · Accepted solution]

Just go be clear, you mean explicit pass through, right? Since I dont see any ...from connection to/execute (...) by in your code.
If you do a create table in an execute block nothing really pulled to SAS. And nothing here has actually nothing to do with SAS.
If your end game is to pull data to SAS I don't see the benefit of tampering with formats in Hive. That is simply done in SAS at report time, and requires almost no resources.

[jimbarbour]

Actually, I think you're right.  The best idea is to maintain the data as INT in Hive and then format it as needed only when it is to be displayed.  It makes little sense (to me) to store the data in both display format and as INT.  It's poor practice. 

However, I've been asked to essentially replicate in Hive an existing SAS dataset such that the new Hive data is "backward compatible" for old programs.  The idea that we can "unplug" an existing SAS program and then "plug" said program into Hive seems simplistic to me.  I think it's going to take some serious re-design.  Running 4.5 billion rows through a SAS/Access LIBNAME from Hive into a SAS program just isn't going to work, at least from what I can see.

I'm still curious, though, why the format_number function shuts my query down.  I've all but verbatim copied the syntax from the manual.

Oh, and, yes.  By "pass through" I meant "explicit pass through".  I redacted the pass through "wrapper" since I know the CONNECT TO/EXECUTE/DISCONNECT wrapper is working fine.  It is the query itself that is causing the error and that only when I try to use the format_number function.

Jim