topic Re: Error with where statement from libname hadoop dataset in New SAS User

Error with where statement from libname hadoop dataset

ashlyn — Wed, 13 May 2020 10:57:44 GMT

Hi,

I have created a libname as shown below and execution is successful.

libname cs hadoop user='myusername' pw='mypw' server="myserver" port=443 dbmax_text=200 subprotocol=hive2
properties="myproperties" database=mydb;

I proceeded to pull data from the created libname and execution is successful as well.

data abc;

set cs.fruits (obs=100);

run;

However, when I added a WHERE statement into the same codes above, I get an error.

data def;

set cs.fruits (obs=100);

where fruit_type = 'Banana';

run;

ERROR: Prepare error: org.apache.hive.service.cli.HiveSQLException: Error while processing statement: FAILED: Execution Error,

return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask at

org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:380) at

org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:257) at

org.apache.hive.service.cli.operation.SQLOperation.access$800(SQLOperation.java:91) at

org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork$1.run(SQLOperation.java:348) at

java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at

org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1669) at

org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork.run(SQLOperation.java:362) at

java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at

java.util.concurrent.FutureTask.run(FutureTask.java:266) at

java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at

java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)

SQL statement: SELECT `fruits`.`id`, `fruits`.`fruit_type`,

`fruits`.`cost_per_unit`, `fruits`.`purchase_date`, `fruits`.`item_desc` FROM `mydb`.`fruits` WHERE ( `fruit_type` = 'Banana' )

Please help.

Re: Error with where statement from libname hadoop dataset

AnandVyas — Wed, 13 May 2020 14:57:54 GMT

Exception looks to be something with permission 'javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1669) '
Are you facing this for all the tables or only this one? Does it work outside of SAS like from beeline?

Re: Error with where statement from libname hadoop dataset

ashlyn — Wed, 13 May 2020 16:00:25 GMT

Hi Anand, I've tried a few tables and they work fine until I include the WHERE statement in my data step. Does this mean that the administrator has limited access to the database?

Re: Error with where statement from libname hadoop dataset

AnandVyas — Wed, 13 May 2020 16:10:56 GMT

Yes, it could be. That's why if you have access to beeline you can query and confirm it for sure if it's happening only from SAS or outside of SAS as well. If you don't have access to beeline then I would suggest to check with Admin if any such permissions are applied to be sure.

Re: Error with where statement from libname hadoop dataset

ashlyn — Wed, 13 May 2020 16:28:07 GMT

Thanks! I will check with Admin on the Beeline access. Thanks for your help!