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

    User Defined Function in Dynamic VDBs




      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.



        • 1. Re: User Defined Function in Dynamic VDBs

          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.


          • 2. Re: User Defined Function in Dynamic VDBs



            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();




            try {

            MySQL5ExecutionFactory mysql = new MySQL5ExecutionFactory();



            DataSource ds = ...



            ModelMetaData md = new ModelMetaData();



            //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));



            //deploy code




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

            • 3. Re: User Defined Function in Dynamic VDBs
              • 4. Re: User Defined Function in Dynamic VDBs

                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

                  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

                    Hey Ramesh

                    Please find the below code :


                    ModelMetaData mmd1 = new ModelMetaData();




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


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




                    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

                      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

                        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;
                            public void setup() {
                                es = new EmbeddedServer();
                            public void teardown() {
                                if (es != null) {
                            @Test public void testDDLWay() throws Exception {
                                EmbeddedConfiguration ec = new EmbeddedConfiguration();
                                es.addTranslator("loopy", new LoopbackExecutionFactory());
                                ModelMetaData sourceModel = new ModelMetaData();
                                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.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()) {


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



                        • 9. Re: User Defined Function in Dynamic VDBs

                          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



                          • 10. Re: User Defined Function in Dynamic VDBs

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