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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.