Hitachi Vantara Pentaho Community Forums
Results 1 to 15 of 15

Thread: Metadata SQL generation in Platform 3.5

  1. #1
    Join Date
    Apr 2007
    Posts
    2,010

    Default Metadata SQL generation in Platform 3.5

    Hi,

    Looking at the SQL generated by metadata it now looks like the ID of the business table is used rather than the name as displayed in metadata editor.

    In my case I have a physical table called:

    BVH_PHARMA_COMPANY_STATUS

    Unfortunately the ID generated for that by the 1.7 metadata editor is:

    BT_BVH_PHARMA_COMPANY_STATUS_BVH_PHARMA_COMPANY_STATUS

    Which is far too long for oracle.

    In platform 2.0, and 1.7 i believe there were fixes made to work around this issue - in that it would never generate sql with an ID which is too long.

    I think this change has been introduced because of the change made so that it now generates proper JOIN syntax.

    So; Is this a bug, or should I just fix my metadata? It's certainly a regression failure from 1.7 to 3.5, but if thats how it is then so be it.

    Perhaps if anything, the metadata should generate shorter business table id's?

    Thanks,
    Dan

  2. #2
    wgorman Guest

    Default

    Hi,

    There is logic in the code that shrinks the name down below the maximum alias length of the database. Some databases don't have a max length, while others do. Are you seeing this behavior in the Metadata Editor or the BI Platform?

    Will

  3. #3
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    this is in the bi platform, it is a CE ci build taken about a week after RC1 was released.

    Same metadata file worked in 1.7. I can send the xmi file to a support call if you like? or I can email it direct or something?

    Actually you should be able to reproduce in any old oracle example by setting the id of your business table to something very long.
    Last edited by codek; 08-28-2009 at 10:57 AM.

  4. #4
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    Actually this is definately a bug. have a look at this generated sql:

    BVH_PRODUCT_DATA_SOURCE BT_BVH_PRODUCT_DATA_SOURCE_BVH_PRODUCT_DATA_SOURCE JOIN BV_PRODUCT_DICTIONARY BT_BV_PRODUCT_DICTIONARY_BV_PRODUCT_DICTIONARY
    ON ( BT_BVH_PRODUCT_DATA_SOURCE_B01.PRODUCT_DATA_SOURCE_ID = BT_BV_PRODUCT_DICTIONARY_BV_01.PRODUCT_DATA_SOURCE_ID )

    Note that it joins to BV_PRODUCT_DICTIONARY giving it an alias of
    BT_BV_PRODUCT_DICTIONARY_BV_PRODUCT_DICTIONARY

    But in the join it uses an alias name of:

    BT_BV_PRODUCT_DICTIONARY_BV_01

    So the shortening code is firing on the actual join condition, but it isnt working correctly on the actual aliasing of the table name.

    I'm actually away for the next 3 working days, so dont have time to get this raised up as a bug, but if you can't reproduce (should be pretty easy) i'll work on an example for you late next week.

  5. #5
    wgorman Guest

    Default

    I've reproduced this with the unit tests, I hope to have a fix in soon. You can track the bug at PMD-576

  6. #6
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    many thanks. Once it's fixed i'll download the CI build and re-test.

  7. #7
    wgorman Guest

    Default

    Hi Dan,

    I've checked in the fix. Let me know if you still have problems in the latest CI builds.

    Thanks,

    Will

  8. #8
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    I'm afraid the latest BIServer CI CE build doesnt work at all as it doesnt even start: (will post this elsewhere too.. )

    15:20:58,485 ERROR [Digester] End event threw exception
    java.lang.reflect.InvocationTargetException
    at sun.reflect.GeneratedMethodAccessor20.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.tomcat.util.IntrospectionUtils.callMethodN(IntrospectionUtils.java:930)
    at org.apache.tomcat.util.digester.CallMethodRule.end(CallMethodRule.java:579)
    at org.apache.tomcat.util.digester.Rule.end(Rule.java:229)
    at org.apache.tomcat.util.digester.Digester.endElement(Digester.java:1058)
    at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.endNamespaceScope(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.handleEndElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.dtd.XMLDTDValidator.endElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unk
    nown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
    at org.apache.tomcat.util.digester.Digester.parse(Digester.java:1562)
    at org.apache.catalina.startup.ContextConfig.applicationWebConfig(ContextConfig.java:352)
    at org.apache.catalina.startup.ContextConfig.start(ContextConfig.java:1044)
    at org.apache.catalina.startup.ContextConfig.lifecycleEvent(ContextConfig.java:261)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
    at org.apache.catalina.core.StandardContext.start(StandardContext.java:4148)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
    at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:926)
    at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:889)
    at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:492)
    at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1149)
    at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1022)
    at org.apache.catalina.core.StandardHost.start(StandardHost.java:736)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1014)
    at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
    at org.apache.catalina.core.StandardService.start(StandardService.java:448)
    at org.apache.catalina.core.StandardServer.start(StandardServer.java:700)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:552)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:295)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:433)
    Caused by: java.lang.IllegalArgumentException: Servlet mapping specifies an unknown servlet name TestSuite
    at org.apache.catalina.core.StandardContext.addServletMapping(StandardContext.java:2323)
    at org.apache.catalina.core.StandardContext.addServletMapping(StandardContext.java:2303)
    ... 46 more

  9. #9
    wgorman Guest

    Default

    Sorry about that, CI isn't the most stable environment. That problem should be fixed shortly.

    Will

  10. #10
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    no probs, thats expected from ci

  11. #11
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    Just to confirm the fix works, many thanks, if i find time today i'll test our other metadata models. Brilliant work!

  12. #12
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    Hi Will,

    I think that fix (maybe) has broken something quite severrely now.

    In another model it is now generating this sql:

    SELECT DISTINCT
    BT_BV_SAFETY_ALERTS_BV_SAFET01.IS_OFFLINE AS COL0
    ,BT_BV_SAFETY_ALERTS_BV_SAFET01.PRODUCT_CODE AS COL1
    ,BT_BV_SAFETY_ALERTS_BV_SAFET01.APP_SERVER_EVENT_DATE_UTC AS COL2
    ,BT_BV_PRODUCT_DICTIONARY_BV_01.PRODUCT_NAME AS COL3
    ,BT_BVH_PRODUCT_CODE_TYPE_BVH01.NAME AS COL4
    FROM
    BVH_PRODUCT_CODE_TYPE BT_BVH_PRODUCT_CODE_TYPE_BVH01
    ,BV_PRODUCT_DICTIONARY BT_BV_PRODUCT_DICTIONARY_BV_01
    ,BV_SAFETY_ALERTS BT_BV_SAFETY_ALERTS_BV_SAFET01
    WHERE
    ( BT_BV_SAFETY_ALERTS_BV_SAFET01.PRODUCT_CODE_TYPE_ID = BT_BVH_PRODUCT_CODE_TYPE_BVH01.PRODUCT_CODE_TYPE_ID )
    AND ( null )

    Note that the second join between prod dict and safety alerts isnt there, instead it just says "null"

    I think this may be because of my complex join, as that causes this error:
    11:17:19,232 ERROR [SqlGenerator] SqlGenerator.ERROR_0017 - Failed to parse complex join null
    org.pentaho.pms.core.exception.PentahoMetadataException: SqlOpenFormula.ERROR_0003 - No Formula String Provided
    at org.pentaho.metadata.query.impl.sql.SqlOpenFormula.<init>(SqlOpenFormula.java:145)
    at org.pentaho.metadata.query.impl.sql.SqlGenerator.getJoin(SqlGenerator.java:921)

    I had to use a complex join because it's a multi part key...

    Should these still work? I guess so..

    I'll send my xmi file to a support case for you.

    Thanks!
    Dan

  13. #13
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    clearly the problem is that the references to the business table in the complex join, are not the "shortened" references.

    Presumably in 1.7 it would also parse the complex join and shorten those too?

  14. #14
    wgorman Guest

    Default

    Hello,

    I just checked in a modified unit test to Pentaho Metadata verifying the correct column aliasing and shortening behavior when using complex joins, so something else must be going wrong with your query. Make sure your complex join is being parsed correctly by libformula. If that fails, you should see error logs in the console.

    Will

  15. #15
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    Finally, i can confirm this works now, when i get the complex join syntax right. I will now update my other models for 3.5. Roll on GA...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.