KNOWLEDGE BASE

Error "[Cloudera][ImpalaODBC](110) Error ... all DISTINCT aggregate functions need to have the same set of parameters"


Published: 25 Jul 2014
Last Modified Date: 13 Nov 2017

Issue

When using count distinct (COUNT D) with a live Impala connection, the following error might occur:
 
[Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: all DISTINCT aggregate functions need to have the same set of parameters as COUNT(DISTINCT <filed-name>); deviating function: COUNT(DISTINCT <field-name>)

Environment

  • Tableau Desktop
  • Cloudera Hadoop Hive
  • Cloudera Impala

Resolution

Option 1

Remove multiple COUNT D statements.

Option 2:

  • For Impala 1.4 and earlier: Modify your Impala server configuration and use initial SQL in your workbook. For more information, see Query Options for the SET Command page (for CDH 5.0.x) on the Cloudera website. 
Note: Currently, there is no way to set query options directly through the JDBC and ODBC interfaces. For JDBC and ODBC applications, you can execute queries that need specific query options by invoking impala-shell to run a script that starts with SET commands, or by defining query options globally through the impalad startup flag --default_query_options
  • For Impala Impala 2.0 and later:  Use initial SQL in your workbook. For more information, see Query Options for the SET Command page (for Cloudera 5.3.x) on the Cloudera website. 
Note: In Impala 2.0 and later, you can set query options directly through the JDBC and ODBC interfaces by using the SET statement. Formerly, SET was only available as a command within the impala-shell interpreter.

Cause

This issue is caused by a limitation with the Cloudera database. Cloudera is unable to support multiple COUNT D statements in a query. For more information, see the Cloudera Issues web page.

Additional Information

Please note that this issue is not confined to COUNT D, a similar issue can be occur when using multiple TO_DATE(TRUNC( x)) statements in the query. If you do encounter this issue, please remove all TO_DATE(TRUNC( x)) statements. 
Did this article resolve the issue?