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.
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?
Thanks! I will check with Admin on the Beeline access. Thanks for your help!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.