topic Re: SAS Impala CLI error in SAS Programming

SAS Impala CLI error

Anuz — Thu, 30 Dec 2021 12:23:12 GMT

Hi,

I get an error quite intermittently.

My code is pretty straight forward with simple libname and proc sql statements something like below:

libname IMP1 impala dsn="XXXXXXX" schema=XXXX;
libname IMP2 impala dsn="XXXXXXX" schema=XXYY;

proc sql;
create table TBZDY as
select a.*,b.* from IMP1.xyz a left join IMP2.zxy b

on a.id=b.id;

This works most days but some odd days, I get the below error

ERROR: CLI open cursor error: [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : Runtime Error: Query
ef46070c48782ed0:c7afbf8700000000: 81% Complete (74 out of 91)

quit;

Has anyone encountered a similar error and know why this happens and what can be done to fix it.

Re: SAS Impala CLI error

AMSAS — Thu, 30 Dec 2021 15:29:41 GMT

I recommend you open a TS track for this question.

Please include the following:
- SAS Version you are running (the following code will report SAS version information)
%put &sysvlong ;
%put &sysscpl ;
%put &syssite ;
- Registry Report (*.txt files) for each server, please clearly name the report files to reflect the server it was executed on.

From other tracks, the issue appears to be on the Impala side and there are a couple of recommendations:

run the query from Hue to see if it runs successfully.
Simplify the query (e.g can you access both those tables independently? can you access any other tables)
Have you run this query before successfully? If so then what has changed recently?

Re: SAS Impala CLI error

Anuz — Thu, 30 Dec 2021 16:25:53 GMT

@AMSAS

9.04.01M3P062415
Linux
70209795

I can access these tables separately.

I can run the query via HUE no issues

I run this query almost every day. It runs fine. Nothing has changed recently. A rerun of the query twice or thrice and it works.

Its only the odd day it just fails.

Re: SAS Impala CLI error

AMSAS — Thu, 30 Dec 2021 16:58:32 GMT

@Anuz Please open a TS track

Provide the information I requested already, and also indicate that it's an intermittent issue.

Is there any kind of pattern to the failures, e.g. It always fails on a Monday or 5th of the month, or between 1-2pm

When you say "Its only the odd day it just fails." does it fail every time you run it on that day? Or can you rerun it and it then runs successfully on the same day?

Re: SAS Impala CLI error

Anuz — Thu, 30 Dec 2021 17:07:58 GMT

ok. shall raise a ticket.

There is no pattern to the failures as you have asked.

Yes I can rerun the same day and it will run ok. 99% of the times by the third rerun its fine.

Re: SAS Impala CLI error

SASKiwi — Sun, 02 Jan 2022 19:57:44 GMT

I suggest you try an SQL passthru version of this to see if it is more reliable.

Re: SAS Impala CLI error

Anuz — Wed, 12 Jan 2022 08:00:18 GMT

@SASKiwi The error occurs even if pass thru query is used. so that doesnt make any difference. Its just temperamental when it happens

Re: SAS Impala CLI error

SASKiwi — Wed, 12 Jan 2022 20:05:56 GMT

@Anuz - Well it was worth a try. Hope you make progress with Tech Support.