Problem Note 64528: Aggregate functions are not passed down through an implicit pass-through query to Apache Hive
 |  |  |  |  |
When you use aggregate functions in an implicit pass-through request to query a Hive table, an error message that is similar to the following appears in the SAS® log. In your environment, the relevant function name appears in place of TEST:
ERROR: Function TEST could not be located.
The following syntax is an example of the code that can produce this error message:
options sastrace=',,,ds' sastraceloc=saslog nostsuffix sql_ip_trace=(note, source) msglevel=i debug=dbms_select;
options dbidirectexec;
option set=SAS_HADOOP_JAR_PATH='/opt/sasinside/hadoop/lib';
option set=SAS_HADOOP_CONFIG_PATH='/opt/sasinside/hadoop/conf/';
options dbidirectexec;
option set=SAS_HADOOP_JAR_PATH='/opt/sasinside/hadoop/lib';
option set=SAS_HADOOP_CONFIG_PATH='/opt/sasinside/hadoop/conf/';
data work.newfunc;
SASFUNCNAME = "test";
SASFUNCNAMELEN = 4;
DBMSFUNCNAME = "test";
DBMSFUNCNAMELEN = 4;
FUNCTION_CATEGORY = "AGGREGATE";
FUNC_USAGE_CONTEXT = "SELECT_LIST";
FUNCTION_RETURNTYP = "NUMERIC";
FUNCTION_NUM_ARGS = 1;
CONVERT_ARGS = 0;
ENGINEINDEX = 0;
output;
run;
SASFUNCNAME = "test";
SASFUNCNAMELEN = 4;
DBMSFUNCNAME = "test";
DBMSFUNCNAMELEN = 4;
FUNCTION_CATEGORY = "AGGREGATE";
FUNC_USAGE_CONTEXT = "SELECT_LIST";
FUNCTION_RETURNTYP = "NUMERIC";
FUNCTION_NUM_ARGS = 1;
CONVERT_ARGS = 0;
ENGINEINDEX = 0;
output;
run;
LIBNAME cdh HADOOP PORT=10000 SERVER="hive_server" sql_functions="EXTERNAL_APPEND=work.newfunc"
sql_functions_copy=saslog;
sql_functions_copy=saslog;
proc delete data=cdh.class;
proc delete data=cdh.class_agg;
proc delete data=cdh.class_agg;
data cdh.class;
set sashelp.class;
run;
set sashelp.class;
run;
proc sql;
create table cdh.class_agg as
select test(age)
from cdh.class
;
quit;
create table cdh.class_agg as
select test(age)
from cdh.class
;
quit;
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | Microsoft® Windows® for x64 | 9.4_M5 | 9.4_M5 | 9.4 TS1M5 | 9.4 TS1M5 |
| 64-bit Enabled AIX | 9.4_M5 | 9.4_M5 | 9.4 TS1M5 | 9.4 TS1M5 | ||
| 64-bit Enabled Solaris | 9.4_M5 | 9.4_M5 | 9.4 TS1M5 | 9.4 TS1M5 | ||
| HP-UX IPF | 9.4_M5 | 9.4_M5 | 9.4 TS1M5 | 9.4 TS1M5 | ||
| Linux for x64 | 9.4_M5 | 9.4_M5 | 9.4 TS1M5 | 9.4 TS1M5 | ||
| Solaris for x64 | 9.4_M5 | 9.4_M5 | 9.4 TS1M5 | 9.4 TS1M5 | ||
A fix for this issue for Base SAS 9.4_M6 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/D9T.html#64528A fix for this issue for Base SAS 9.4_M5 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/B6Q.html#64528| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2019-09-03 10:36:23 |
| Date Created: | 2019-07-29 10:19:57 |