13 Replies Latest reply on May 25, 2017 1:17 AM by aakash03

    User defined function on jboss teiid

    aakash03

      Hi,

       

      I am trying to create user defined function and for this I am referring below link :

      https://teiid.gitbooks.io/documents/content/v/9.2.x/dev/Support_for_User-Defined_Functions_Non-Pushdown.html

       

      I have gone through the below steps:

       

      1. Created Java class and a method under it

       

      package org.something;

      public class TempConv
      {
         public static Double celsiusToFahrenheit(Double doubleCelsiusTemp)
        
      {
         if (doubleCelsiusTemp == null)
        {
         return null;
        }
         return (doubleCelsiusTemp)*9/5 + 32;
        }
      }

       

      2. converted this java class into the jar file and have placed on below location:

               teiid-9.1.3\modules\celsiusToFahrenheit\

       

      3. VDB:

       

      <?xml version="1.0" ?><vdb name="SvcSourceVdb_testConnection" version="1">

      <description>VDB for: SvcSourceVdb_testConnection, Version: 1</description>

      <connection-type>BY_VERSION</connection-type>

       

       

      <property name ="lib" value ="celsiusToFahrenheit"></property>

       

       

      <model name="{model-name}" type="VIRTUAL">

      <metadata type="DDL"><![CDATA[

        CREATE VIRTUAL FUNCTION celsiusToFahrenheit(celsius decimal) RETURNS decimal OPTIONS (JAVA_CLASS   'org.something.TempConv',  JAVA_METHOD 'celsiusToFahrenheit');]]>

      </metadata>

      </model>

       

       

      <model name="testConnection" type="PHYSICAL" visible="true">

      <property name="importer.tableTypes" value="TABLE"></property>

      <source name="testConnection" translator-name="mysql5" connection-jndi-name="java:/DSMYSQL2017051611225212"></source></model>

       

       

      <model name="testConnectionNative" type="PHYSICAL" visible="true">

      <source name="testConnectionNative" translator-name="mysql-native" connection-jndi-name="java:/DSMYSQL2017051611225212"></source>

      </model>

      <translator name="mysql-native" type="mysql5">

      <property name="SupportsDirectQueryProcedure" value="true"></property></translator>

      </vdb>

       

       

      4. deploying the vdb

       

      I am getting below error message

       

       

      ERROR [org.jboss.msc.service.fail] (MSC service thread 1-3)  MSC000001: Failed to start service jboss.deployment.unit."SvcSourceVdb_testConnection-vdb.xml".DEPENDENCIES: org.jboss.msc.service.StartException in service jboss.deployment.unit."SvcSourceVdb_testConnection-vdb.xml".DEPENDENCIES: WFLYSRV0153: Failed to process phase DEPENDENCIES of deployment "SvcSourceVdb_testConnection-vdb.xml"

        at org.jboss.as.server.deployment.DeploymentUnitPhaseService.start(DeploymentUnitPhaseService.java:154)

        at org.jboss.msc.service.ServiceControllerImpl$StartTask.startService(ServiceControllerImpl.java:1948)

        at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1881)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

        at java.lang.Thread.run(Unknown Source)

      Caused by: org.jboss.as.server.deployment.DeploymentUnitProcessingException: TEIID50088 Failed to load module celsiusToFahrenheit for UDF in VDB SvcSourceVdb_testConnection.1:org.jboss.modules.ModuleNotFoundException: celsiusToFahrenheit:main

        at org.teiid.jboss.VDBDependencyDeployer.deploy(VDBDependencyDeployer.java:85)

        at org.jboss.as.server.deployment.DeploymentUnitPhaseService.start(DeploymentUnitPhaseService.java:147)

        ... 5 more

       

       

      Note: I am using wildfly 10.0.0 and Teiid 9.1.3

       

      Thanks,

      Aakash Sen

        • 1. Re: User defined function on jboss teiid
          van.halbert

          Aakash,

           

          When you deployed the UDF jar, did you deploy it in the following way:

          -  a module folder structure will be:    /modules/{path}/main      where main is the default slot or the name of the slot

          -  contains the module.xml that defines the module name, resource and dependencies

          -  contains the jar that should be referenced as a resource.

           

          example module.xml:

           

          {code}

           

          <?xml version="1.0" encoding="UTF-8"?>

           

          <module xmlns="urn:jboss:module:1.1" name="celsiusToFahrenheit">

             <resources>

                <resource-root path="celsiusToFahrenheit.jar" />

             </resources>

             <dependencies>

                <module name="org.slf4j" />

                <module name="javax.api" />

             </dependencies>

          </module>

          {code}

           

          Van

          • 2. Re: User defined function on jboss teiid
            aakash03

            Thanks Van for the reply.

             

            I have tried the same steps as you suggested now I am getting below error message:

             

             

            10:39:47,435 INFO  [org.jboss.as] (Controller Boot Thread)  WFLYSRV0025: WildFly Full 10.0.0.Final (WildFly Core 2.0.10.Final) started in 14533ms - Started 670 of 1034 services (514 services are lazy, passive or on-demand)

            10:39:51,370 INFO  [org.teiid.CONNECTOR] (Worker2_async-teiid-threads2)  MySQL5ExecutionFactory Commit=true;DatabaseProductName=MySQL;DatabaseProductVersion=5.0.67-community-nt;DriverMajorVersion=5;DriverMajorVersion=1;DriverName=MySQL Connector Java;DriverVersion=mysql-connector-java-5.1.37 ( Revision: 09940f05b4c98150f352e787a2549f11a2e9da93 );IsolationLevel=2

            10:39:52,092 INFO  [org.teiid.RUNTIME] (Worker2_async-teiid-threads2)  TEIID50030 VDB SvcSourceVdb_testConnection.1 model "testConnectionNative" metadata loaded. End Time: 5/17/17 10:39 AM

            10:39:55,687 INFO  [org.teiid.CONNECTOR] (Worker1_async-teiid-threads1)  MySQL5ExecutionFactory Commit=true;DatabaseProductName=MySQL;DatabaseProductVersion=5.0.67-community-nt;DriverMajorVersion=5;DriverMajorVersion=1;DriverName=MySQL Connector Java;DriverVersion=mysql-connector-java-5.1.37 ( Revision: 09940f05b4c98150f352e787a2549f11a2e9da93 );IsolationLevel=2

            10:39:56,519 INFO  [org.teiid.RUNTIME] (Worker1_async-teiid-threads1)  TEIID50030 VDB SvcSourceVdb_testConnection.1 model "testConnection" metadata loaded. End Time: 5/17/17 10:39 AM

            10:39:56,535 INFO  [org.teiid.RUNTIME] (Worker1_async-teiid-threads1)  TEIID40073 The metadata for the VDB SvcSourceVdb_testConnection.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

            10:39:56,535 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker1_async-teiid-threads1)  TEIID40003 VDB SvcSourceVdb_testConnection.1 is set to FAILED

             

             

            Thanks,

            Aakash

            • 3. Re: User defined function on jboss teiid
              rareddy

              If you log into WebConsole and go to the Teiid Runtime page, and look at the errors on the VDB, what do you see? It should give more details on what is wrong?

              • 4. Re: User defined function on jboss teiid
                aakash03

                Please see the attached error log screenshot.I am not able to see any details error on this.error_on_console.png

                 

                Thanks,

                Aakash

                • 5. Re: User defined function on jboss teiid
                  van.halbert

                  Aakash,

                   

                  My guess is there's something strange about the UDF model.  I have seen where a strange character gets into an xml file and does strange things.   So my suggestion would be to try the following:

                   

                  -  create a duplicate of the vdb to save what you have

                  -  remove the UDF {model} section from the vdb that only contains the 2 testConnection models

                  -  redeploy so that it can be confirmed that the VDB will deploy

                  -  if the vdb becomes active, this will also confirm that there is something about the UDF model is off.

                   

                  - Now re-add the UDF model by manually retyping the model definition (don't cut-n-paste) and redeploy.

                   

                  Also, I was able to deploy a similar VDB and also tried breaking it to achieve a similar message, but was unable.    Any changes to the details of the UDF produced specific messages that I knew what was wrong.

                   

                  Van

                  • 6. Re: User defined function on jboss teiid
                    rareddy

                    I was looking for errors on vdb screen in the bottom in errors list box, what you did above is reload of VDB.

                    • 7. Re: User defined function on jboss teiid
                      aakash03

                      Van,

                       

                       

                      created a duplicate of the vdb to save what you have

                      -  removed the UDF {model} section from the vdb that only contains the 2 testConnection models

                      -  redeployed so that it can be confirmed that the VDB will deploy

                           VDB is deploying successfully with Active status.

                      - Now re-added the UDF model by manually retyping the model definition (don't cut-n-paste) and redeployed.

                       

                      I did exact the same steps as you suggested but I still facing the same issue:

                       

                      TEIID40073 The metadata for the VDB SvcSourceVdb_testConnection.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

                      11:08:16,957 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker1_async-teiid-threads2)  TEIID40003 VDB SvcSourceVdb_testConnection.1 is set to FAILED

                      • 8. Re: User defined function on jboss teiid
                        aakash03

                        Ramesh,

                         

                        I am unable to any errors on vdb screen please see the attachment for the same.

                         

                        Please let me know am I referring the correct screen or can you suggest me the exact path to see the detail errors. I am using Wildfly 10.0.0 and Teiid 9.1.3.

                         

                        errors_on_vdb.png

                         

                        Thanks,

                        Aakash

                        • 9. Re: User defined function on jboss teiid
                          rareddy

                          Attach your VDB here.

                          • 10. Re: User defined function on jboss teiid
                            van.halbert

                            When I deployed the VDB with only the UDF, I get the following error:

                             

                            10:54:22,742 WARN  [org.teiid.RUNTIME] (Worker0_async-teiid-threads0)  TEIID40073 The metadata for the VDB test_udf.1 is loaded, however it is not valid. Check models for errors to correct the metadata and re-deploy: test_udf: org.teiid.metadata.MetadataException: TEIID30388 UDF "celsiusToFahrenheit(bigdecimal celsius) : bigdecimal result" could not be loaded, since no method on class "org.something.TempConv" with name "celsiusToFahrenheit" has a matching type signature.

                                at org.teiid.query.function.FunctionTree.createFunctionDescriptor(FunctionTree.java:328)

                                at org.teiid.query.function.FunctionTree.addFunction(FunctionTree.java:213)

                                at org.teiid.query.function.FunctionTree.<init>(FunctionTree.java:103)

                                at org.teiid.deployers.CompositeVDB.buildTransformationMetaData(CompositeVDB.java:101)

                                at org.teiid.deployers.CompositeVDB.metadataLoadFinished(CompositeVDB.java:309)

                                at org.teiid.deployers.VDBRepository.finishDeployment(VDBRepository.java:333)

                                at org.teiid.runtime.AbstractVDBDeployer.metadataLoaded(AbstractVDBDeployer.java:264)

                                at org.teiid.jboss.VDBService.access$1000(VDBService.java:82)

                                at org.teiid.jboss.VDBService$6.run(VDBService.java:362)

                                at org.teiid.jboss.VDBService$7.run(VDBService.java:392)

                                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

                                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

                                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

                                at java.lang.Thread.run(Thread.java:748)

                            Caused by: java.lang.NoSuchMethodException: celsiusToFahrenheit Args: [class org.teiid.query.util.CommandContext, class java.math.BigDecimal]

                                at org.teiid.core.util.ReflectionHelper.findBestMethodWithSignature(ReflectionHelper.java:186)

                                at org.teiid.query.function.FunctionTree.createFunctionDescriptor(FunctionTree.java:322)

                                ... 15 more

                             

                             

                            VDB udf:

                             

                            ...

                             

                                <model name="{model-name}" type="VIRTUAL">

                                    <metadata type="DDL"><![CDATA[

                                    CREATE VIRTUAL FUNCTION celsiusToFahrenheit(celsius decimal) RETURNS decimal OPTIONS (JAVA_CLASS   'org.something.TempConv',  JAVA_METHOD 'celsiusToFahrenheit');]]>

                                    </metadata>

                                </model>

                            ...

                             

                            UDF:

                             

                            package org.something;

                             

                            public class TempConv

                            {

                               public static Double celsiusToFahrenheit(Double doubleCelsiusTemp)

                               {

                             

                               return (doubleCelsiusTemp)*9/5 + 32;

                              }

                            }

                            • 11. Re: User defined function on jboss teiid
                              shawkins

                              To follow up, Van is seeing the warning likely due to [TEIID-4756] Improve log messages when using an UDAF without the required classes defined in VDB - JBoss Issue Tracker which is not in 9.1

                               

                              The root exception is due to type mismatches.  It should return and accept a double:

                               

                              CREATE VIRTUAL FUNCTION celsiusToFahrenheit(celsius DOUBLE) RETURNS DOUBLE OPTIONS (JAVA_CLASS   'org.something.TempConv',  JAVA_METHOD 'celsiusToFahrenheit');

                               

                              We should also support defining the function automatically just based upon the signature - [TEIID-4932] Support defining UDFs based upon a class and or function - JBoss Issue Tracker

                              • 12. Re: User defined function on jboss teiid
                                aakash03

                                Thanks a lot Steven and Van !! for letting me knowing the root cause of the problem.This helps me a lot and now my problem has been resolved.Now my UDF is working fine.

                                 

                                Thanks,

                                Aakash

                                • 13. Re: User defined function on jboss teiid
                                  aakash03

                                  Ramesh,

                                   

                                  The problem has been resolved now there was the problem with UDF declaration on vdb.

                                   

                                  previous one :

                                   

                                     CREATE VIRTUAL FUNCTION celsiusToFahrenheit(celsius decimal) RETURNS decimal OPTIONS (JAVA_CLASS   'org.something.TempConv',  JAVA_METHOD 'celsiusToFahrenheit');

                                   

                                  Corrected one:

                                   

                                  CREATE VIRTUAL FUNCTION celsiusToFahrenheit(celsius DOUBLE) RETURNS DOUBLE OPTIONS (JAVA_CLASS   'org.something.TempConv',  JAVA_METHOD 'celsiusToFahrenheit');

                                   

                                   

                                  Thanks,

                                  Aakash