The following entries are captured in the DSSErrors.log
(SQL Generation CompleteQueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. (35) Error from Hive: error code: '40000' error message: 'Error while processing statement: FAILED: ParseException line 1:263 cannot recognize input near 'SELECT' 'AVG' '(' in expression specification
'. Connection String: DSN=Apache_Hive;UID=xxx;. SQL Statement: SELECT
EMP_ID,
EMP_LAST_NAME,
EMP_FIRST_NAME,
SALARY
FROM
LU_EMPLOYEE
WHERE
SALARY >
(
SELECT AVG(SALARY)
FROM LU_EMPLOYEE
) .. Error in Process method of Component: QueryEngineServer, Project Hive - Strategy Tutorial, Job 648, Error Code= -2147212544.) CAUSE:HiveQL does not support the full SQL-92 specification. In this Free Form SQL report, a correlated subquery is used and this is not supported by HiveQL; subqueries are supported only in the FROM clause.
Note: Subqueries in the WHERE clause are supported in Apache Hive 0.13 onwards (refer https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.4.3/bk_dataintegration/content/hive-013-feature-subqueries-in-where-clauses.html for documentation), so this issue can occur when running the report against lower version of HiveQL.
ACTION:Since some well-known SQL statements are not executable against Apache Hive, make sure that fully supported HiveQL statements are specified in the Free Form report.
As a reference, users may want to check the following link
MAPR.Doc (
http://doc.mapr.com/pages/viewpage.action?pageId=23266473)
Third Party Software Installation:WARNING:The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.
