05-11-2018 08:34 PM - edited 05-11-2018 08:41 PM
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?
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
05-12-2018 03:21 AM
05-12-2018 10:55 AM
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.