By enabling SQL optimization Modeler will create SQL statement correspondence with the process performs in each node on the stream. When HIVE database use as data source, in a specific stream, the SQL statement created by Modeler could not be processed correctly by HIVE database. For example, the simple stream which contains combination of Filler and Distinct node, produce following SQL statement which causing “ParseException” error on HIVE database.
INSERT INTO spssdb.table02 (a,b,c) SELECT T0.a AS a,T0.b AS b,T0.c AS c FROM (SELECT T0.a AS a,T0.b AS b,T0.c AS c,T0.`ROW_NUMBER` AS `ROW_NUMBER` FROM (SELECT (CASE WHEN 0=1 THEN 0 ELSE T0.a END) AS a,T0.b AS b,T0.c AS c,ROW_NUMBER() OVER ( PARTITION BY (CASE WHEN 0=1 THEN 0 ELSE T0.a END),T0.b ORDER BY T0.c ASC) AS `ROW_NUMBER` FROM spssdb.table01 T0) T0 WHERE (T0.`ROW_NUMBER` = 1)) T0
Above SQL statement created by Modeler is a valid statement. So, HIVE parser should able to handle format used by this SQL statement since it adheres SQL92 format.
This issue might have related with following HIVE problem:
But, since currently there is no workaround for this kind of issue, we are strongly request an enhancement to allow Modeler produce SQL statement with some modification to meet HIVE Database parser limitation.
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "firstname.lastname@example.org" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions