Error with where statement from libname hadoop dataset

[ashlyn]

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.

[AnandVyas]

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?

[ashlyn]

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?

[AnandVyas · Accepted solution]

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.

[ashlyn]

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