10 Replies Latest reply on May 22, 2018 10:59 AM by Ramesh Reddy

    User Defined Function in Dynamic VDBs

    Lalit Verma Newbie

      Hi,

       

      We have been trying to work with teiid embedded and want add custom udf functions in the vdbs. As per documentation it is mentioned to use ExecutionFactory.getMetaData()  and add an function to the MetaDataFactory using addFunction(MetaDataFactory,Connection). We have tried multiple ways and times and no luck.

       

      Is there any specific documentation or unit test or integration test which we can follow up to solve this. We basically need to add our own functions to schema instances.

       

      -Lalit 

        • 1. Re: User Defined Function in Dynamic VDBs
          Ramesh Reddy Master

          you mean a source (like Oracle, SQLServer) specific function or write your own to be used in user SQL statements? You can do both, but UDF typically means later one. See documentation here

           

          Support for User-Defined Functions(Non-Pushdown) · Teiid Documentation

           

          essentially you need to add details about functions in view model DDL, then supply the respective jar files in your classpath.

          Ramesh..

          • 2. Re: User Defined Function in Dynamic VDBs
            Jay V Newbie

            Ramesh,

             

            Thank you for your quick response. We are trying to add a non-push down custom function (i.e., a non-source specific function).

            We are trying to create it programmatically (i.e without using XML descriptor). A previous post on this topic indicates that it can be done using MetadataFactory.addFunction.

            We haven't had much success using it.

             

            Here's an excerpt from the code we are trying to use -

             

             

            EmbeddedServer es = new EmbeddedServer();

            ..

            es.start(config);

             

            try {

            MySQL5ExecutionFactory mysql = new MySQL5ExecutionFactory();

            mysql.start();

            es.addTranslator("mysql5",mysql);

            DataSource ds = ...

            ..

             

            ModelMetaData md = new ModelMetaData();

            md.setName("test");

            md.addSourceMapping("test","mysql5","java://localDS");

            //query with custom non-pushdown func

            md.setSchemaText("create view \"actor\" (name varchar(300)) as select my_custom_func(first_name) as first_name from \"test.actor\");

             

            //define custom function - this part needs clarity

            MetadataFactory mf = new MetadataFactory("test",1,null,md);

            mf.addFunction("my_custom_func", StringOperation.getMethod("toUpperCase", String.class));

            mysql.getMetadata(mf,null);

             

            //deploy code

            ..

            }

             

            I would appreciate any leads here on how to make this code work. Thanks!    

            • 4. Re: User Defined Function in Dynamic VDBs
              Lalit Verma Newbie

              Hey Ramesh,

               

              Thanks for the lead.

               

              But currently we are facing an error as

               

              org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31080

              virt.actor_view validation error: TEIID30068 The function

              'toLowerCase(source.sakila.actor.first_name)' is an unknown form.  Check

              that the function name and number of arguments is correct.

              at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:833)

              at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:704)

              at com.infosys.dv.main.Runner.main(Runner.java:68)

               

               

              here toLowerCase is our test UDF

               

              on searching a bit have found that the support for the function modal is no

              longer avaliable.

               

              Can you please confirm on this.

               

              - Lalit

              • 5. Re: User Defined Function in Dynamic VDBs
                Ramesh Reddy Master

                There is no need for Function Model, that is so Teiid 7 or something. There is no restriction on where the function is defined in later Teiid versions. If you define a function on a "view" model it is a UDF, the same function if you define on a "source" model then it is a "pushdown" function. A UDF needs to be backed by the Java methods, the documentation shows how to write one here [1].

                 

                Post the code what you have written and SQL you are trying to use with maybe we can see why engine thinks it is in the wrong form. Also, sometimes you need to call as "mymodel.toLowerCase9(...)" to qualify it.

                 

                [1] Support for User-Defined Functions(Non-Pushdown) · Teiid Documentation

                • 6. Re: User Defined Function in Dynamic VDBs
                  Lalit Verma Newbie

                  Hey Ramesh

                  Please find the below code :

                   

                  ModelMetaData mmd1 = new ModelMetaData();

                  mmd1.setName("virt");

                  mmd1.setModelType(Model.Type.VIRTUAL);

                  mmd1.addSourceMetadata("ddl",

                  "create view actor_view(first_name varchar(300)) as select toLowerCase(first_name) from actor");

                   

                  MetadataFactory mf = new MetadataFactory("byCode", 1, SystemMetadata.getInstance().getRuntimeTypeMap(),

                  mmd1);

                   

                  mf.addFunction(MetadataFactory.createFunctionFromMethod("toLowerCase",

                  StringOperation.class.getMethod("toLowerCase", String.class)));

                   

                  mf.setParser(new QueryParser());

                  mysqlt5.getMetadata(mf, ds.getConnection());

                   

                   

                  ---- UDF class ---

                   

                  public class StringOperation {

                   

                  public static String toUpperCase(String value) {

                  return value.toUpperCase();

                  }

                   

                  public static String toLowerCase(String value) {

                  return value.toLowerCase();

                  }

                  }

                   

                  I am trying to use the toLowerCase function using the addFunction, the vdb gets deployed properly but  I the get the above mentioned error. Is this the right way or do we need to use the DDL approach and write an virtual function.

                   

                  Let me know incase you need more clarity on the issue

                   

                  - Lalit

                   



                  • 7. Re: User Defined Function in Dynamic VDBs
                    Lalit Verma Newbie

                    Also Please note all of this is using teiid embedded. We are trying to avoid xml based approach.

                    • 8. Re: User Defined Function in Dynamic VDBs
                      Ramesh Reddy Master

                      You use of MetadataFactory is not consistent. I am not sure how you are building the end VDB. You can take a simpler route like by defining the Function in the DDL itself. For example:

                       

                      public class TestFunction {
                          EmbeddedServer es;
                          
                          @Before 
                          public void setup() {
                              es = new EmbeddedServer();
                          }
                          
                          @After 
                          public void teardown() {
                              if (es != null) {
                                  es.stop();
                              }
                          }
                          
                          @Test public void testDDLWay() throws Exception {
                              EmbeddedConfiguration ec = new EmbeddedConfiguration();
                              ec.setUseDisk(false);
                              es.start(ec);
                              
                              es.addTranslator("loopy", new LoopbackExecutionFactory());
                              
                              ModelMetaData sourceModel = new ModelMetaData();
                              sourceModel.setName("source");
                              sourceModel.addSourceMapping("x", "loopy", null);
                              sourceModel.addSourceMetadata("DDL", "CREATE FOREIGN TABLE actor (e1 integer PRIMARY KEY, first_name varchar(25), last_name double);");
                      
                           
                              String schema = "CREATE VIRTUAL FUNCTION toLowerCase(IN \"value\" varchar) RETURNS varchar(300) " +
                                      "OPTIONS(CATEGORY 'misc', JAVA_CLASS 'org.teiid.runtime.StringOperation', JAVA_METHOD 'toLowerCase');"
                                      + "create view actor_view(first_name varchar(300)) as select toLowerCase(first_name) from actor;";
                              
                              ModelMetaData viewModel = new ModelMetaData();
                              viewModel.setName("virt");
                              viewModel.setModelType(Type.VIRTUAL);
                              viewModel.addSourceMetadata("DDL", schema);
                      
                      
                              es.deployVDB("test", sourceModel, viewModel);
                              
                              TeiidDriver td = es.getDriver();
                              Connection c = td.connect("jdbc:teiid:test", null);
                              Statement s = c.createStatement();
                              ResultSet rs = s.executeQuery("select * from \"actor_view\"");
                              while(rs.next()) {
                                  System.out.println(rs.getString(1));
                              }
                          }    
                      }

                       

                      If you want use MetadataFactory, you need show an example of how you are building the VDB.

                       

                      Ramesh..

                      • 9. Re: User Defined Function in Dynamic VDBs
                        Lalit Verma Newbie

                        Hey Ramesh,

                         

                        Thanks for the details. Your snippet helped to solve the issue I had used the metadatafactory in an wrong way but now I have my udf working.

                         

                        Thanks for all the help

                         

                        Lalit

                        • 10. Re: User Defined Function in Dynamic VDBs
                          Ramesh Reddy Master

                          You can Thank me by writing me a blog about Teiid, why you used and how it helped your usecase