IBM Data & AI

Welcome to the idea forum for IBM Data & AI (Formerly Analytics) Clients! 


IBM Employees:

The correct URL for entering your ideas is



Our team welcomes any feedback  and suggestions you have for improving our offerings / products!  This forum allows us to connect your offering / product improvement ideas with IBM product and engineering teams.


If you have not registered on this portal please click on the following link and register.  To complete registration you will need to open the email you will receive from Aha to confirm your identity.


Allowing Modeler to produce SQL statement with some modification to meet HIVE Database parser limitation

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.

  • Guest
  • Dec 14 2018
  • Planned
Customer Name
Role Summary Enhancement
  • Attach files

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 "anonymous@euprivacy.out" 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