10 Replies Latest reply on May 22, 2017 10:58 AM by Van Halbert

    User defined function on jboss teiid

    Aakash Sen Newbie

      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 Master

          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
            Aakash Sen Newbie

            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
              Ramesh Reddy Master

              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
                Aakash Sen Newbie

                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 Master

                  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
                    Ramesh Reddy Master

                    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
                      Aakash Sen Newbie

                      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
                        Aakash Sen Newbie

                        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
                          Ramesh Reddy Master

                          Attach your VDB here.

                          • 10. Re: User defined function on jboss teiid
                            Van Halbert Master

                            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;

                              }

                            }