Getting more detail from Hadoop (Hive) error message in SAS?

[Rachel1]

I'm querying Hadoop (Hive) using explicit pass-through SQL in Enterprise Guide via an ODBC connection. I'm experiencing intermittent issues that according to our admins are caused by various data issues. These data issues are not my problem, but I need to let our support team know about them and supply any error messages for trouble-shooting. My problem is that SAS is truncating the Hadoop (Hive) error message.

Let's say my code looks something like this:

proc sql;
connect to odbc(user= &ID password=&PWD dsn=HiveODBC_PROD); 
create table work.edl as select * from connection to odbc (
select * from dbname.tblname);
disconnect from odbc; quit;

If I get a Hadoop (Hive) error from running this, I've noticed that I get only an abbreviated error message in SAS, for example:

ERROR: CLI open cursor error: [MapR][Hardy] (35) Error from server: error code: '2' error message:  'org.apache.hive.service.cli.HiveSQLException: Error while processing statement: FAILED: Execution Error, return code 2 from  org.apache.hadoop.hive.ql.exec.tez.TezTask. Vertex failed, vertexName=Map 1, vertexId=vertex_1621152461129_10267_1_00,  diagnostics='.

However, if I run the same query in an environment like Hue and get the same error, I get a full error message with a great deal more detail that is not truncated like the SAS error. This detail has been useful to our Hadoop Hive support team in fixing the data  problems.

Are there any options I'm missing in SAS that would allow me to get the full error message returned from Hadoop?

[jimbarbour]

The first thing I would try is

OPTIONS 	MsgLevel		=	I;

which will turn on additional messages that SAS might normally suppress.  I don't think it's going to affect what you're doing here, but let's be thorough and turn it on.

Next, I would add the following before you run your query:

OPTIONS 	SASTRACE		=	",,,ds";
OPTIONS 	SASTRACELOC		=	SASLOG;
OPTIONS 	NOSTSUFFIX;

The above OPTIONS will cause additional information about how the database is interacting with SAS to be written to the SAS log.

Why don't you try adding the above to your code?  I realize that the error is only intermittent, but if it's really a problem, it won't be long where you'll have a run where you have these OPTIONS in effect and you will hopefully get additional information.

In addition to the preceding there is a macro variable SQLXRC that you can query to get the return code from the database.  Macro variables SYSCC, SYSERR, and SQLRC are also available, but SYSCC, SYSERR, and SQLRC relate to SAS whereas SQLXRC is the  return code as issued by database.  The following would write those return codes to the SAS log.  You would place the following immediately after your query.  The OPTIONS statements should be before your query.

%PUT NOTE:  &=SYSCC;
%PUT NOTE-  &=SYSERR;
%PUT NOTE-  &=SQLRC;
%PUT NOTE-  &=SQLXRC;

After you obtain the SQLXRC, you can usually Google the code and get more information.  The code returned by the database isn't just a number buy might be something like HY or some other alphanumeric code that could give you more info if looked up in the company's documentation or on Google.  Sometimes these are a bit generic, but it's worth a try.

Jim

[jimbarbour]

Oh, and @Rachel1, one more thing:  There are a series of SQL related macro variables that get created when you run explicit pass through SQL or HQL.  See graphic, below.  The one that might be interesting to the write to the log is SQLXMSG.  This is the message returned by the database.  SQLXMSG is complementary to SQLRC.  I would add SQLXMSG to the list of macro variables to write to the log that I gave you in my earlier response.  It may tell us nothing that we don't already know, but it's worth a shot.

Jim

P.S.  If you have time, why not add all of these to the list that gets written to the log?  I suspect SQLXRC and SQLXMSG are the ones that will be most useful, but the others won't hurt.