1 2 Previous Next 17 Replies Latest reply on Jun 2, 2014 10:58 AM by shawkins

    how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?

    madhu.garimilla

      I have a sample.txt file deployed with in a jboss module com.abc.testmodule. I would like to access this file inside a view with in my vdb.xml. So, i have included my module as a property inside my vdb.xml as

       

      <property name ="lib" value ="com.abc.testmodule"></property>

       

      How can i access sample.txt contents inside one of the view definitions in vdb.xml? I am using Teiid 8.7 + EAP 6.1 alpha.

       

      I am expecting it to work similar to a file translator like below.

       

      CREATE VIEW my_url (

                          URL string)

                  AS

                  SELECT URL

                      FROM

                          (call fileModel.getTextFiles('sample.txt'))AS f,

                          TEXTTABLE(F.file

                              COLUMNS

                                  URL string

                              HEADER 1) AS t;

       

      Any idea how to achieve this?

        • 1. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
          shawkins

          Sorry for the delayed reply.  We appear to be having issues with email notifications.

           

          > Any idea how to achieve this?

           

          The file translator only understands retrieving files from the filesystem, and I don't recall anything that we have built-in that will read off of the vdb classpath like that.  You can introduce a user defined function or include the file directly in the vdb.

          • 2. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
            madhu.garimilla

            Thanks Steve for the reply.  How do we include the file directly in the vdb.xml ?

            • 3. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
              shawkins

              Just put it into the vdb zip file at some path of your choosing.  Then use the the VDBResources table System Tables - Teiid 8.8 (draft) - Project Documentation Editor:

               

              SELECT contents FROM SYSADMIN.VDBResources WHERE resourcePath = '/somePath/sample.txt'

               

              That will return a blob of your file.

              • 4. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                madhu.garimilla

                Hi Steve,

                 

                Here my use case is, I have a jboss module that has some resources in it. say for example, sample.properties. I tried to include this module inside my -vdb.xml file as

                 

                <property name ="lib" value ="com.abc.testmodule"></property>


                Now i wanted to use the sample.properties contents inside one of my view definitions. Can we achieve this use case with your query?


                I tried to issue the SELECT * FROM SYSADMIN.VDBResources against my vdb but it returned an empty result.

                • 5. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                  shawkins

                  > Now i wanted to use the sample.properties contents inside one of my view definitions. Can we achieve this use case with your query?

                   

                  Not when using a module.  There is currently no built-in SQL accessible logic that will get file contents from the classpath.  To use VDBResources the file must be in the vdb zip file itself.

                  • 6. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                    madhu.garimilla

                    Steve, I have resolved this by creating a translator containing a procedure in it that takes a jboss module name and the resource name as input and returns a clob as a output. By making use of module name we could get any resources under its classpath.

                    • 7. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                      rareddy

                      You could have simply written a UDF and looked for the file in the current thread context. Translator approach seems grandiose and need to depend upon the JBoss specific libraries. 

                      • 8. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                        markaddleman

                        Hi Ramesh -  Right now, we've implemented a stored procedure using a custom translator that takes two parameters:  the JBoss module name and resource name.  Yes, we depend on JBoss libraries to obtain the appropriate classloader; we didn't think of using the context loader.  I'm not sure a function is the right semantic but it occurs to me that we could have used OBJECTARRAY.

                         

                        We're navigating our internal processes to open source this translator and offer it to the Teiid community.

                        • 9. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                          rareddy

                          I am not sure what you mean by function not being right semantic, and not sure about your thoughts on OBJECTARRY solution, may be you can elaborate more?

                           

                          Teiid does not depend upon the JBoss AS libraries for any translators, so that would be a hard sell. What I *thought* already available and upon checking the code the VDB classloader is not available in the ExecutionContext. If that is available then translator developer can look up their resources there. That could be requested as enhancement.

                           

                          If you want treat this data more as source data, then we could add another "getFile" method to the File translator that works off class loader from previous comment.

                           

                          Ramesh..

                          • 10. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                            shawkins

                            > That could be requested as enhancement.

                             

                            There is already getVDBClassloader on the CommandContext, so this would be a straight-forward UDF

                             

                            > If you want treat this data more as source data, then we could add another "getFile" method to the File translator that works off class loader from previous comment.

                             

                            I don't think that would be needed given that a system function or UDF would be straight-forward.  The only reason for additional translator/ra support would be if the specification of the possible module or other classpath resources seems beyond just what the vdb classpath offers.

                            • 11. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                              markaddleman

                              > I am not sure what you mean by function not being right semantic, and not sure about your thoughts on OBJECTARRY solution, may be you can elaborate more?

                               

                              I meant OBJECTTABLE.  My thought is that we could have written some Java code that exposes a resource as a string/CLOB and then mapped that Java code into a VDB using OBJECTTABLE.  That approach is arguably simpler than building out a translator.

                               

                              > Teiid does not depend upon the JBoss AS libraries for any translators, so that would be a hard sell

                               

                              Understood.  It turns out that we have future needs to load arbitrary resources through a translator so depending on JBoss APIs necessary and not a big deal for us.  The general use case would be something like exposing WAR file resources through Teiid REST and/or OData.  In this light, I'm not sure how broadly useful this translator is but we'd like to open source it anyway (as it helps set a precedent and makes future contributions a little easier).  We'll probably just publish it to github.  Does Teiid community have a web page for third-party ecosystem libraries?

                              • 12. Re: Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                                markaddleman

                                > There is already getVDBClassloader on the CommandContext, so this would be a straight-forward UDF

                                 

                                This brings up a point I've been a little curious about.  If we implemented this as a UDF, our view would be defined as something like

                                CREATE VIEW my_url (URL string)

                                            AS

                                            SELECT URL

                                                FROM

                                                    (SELECT getResource('resource_name.csv'))AS f,

                                                    TEXTTABLE(F.file

                                                        COLUMNS

                                                            URL string

                                                        HEADER 1) AS t;

                                While the plan for accessing the resource through a UDF is slightly different than the plan for accessing the resource through a procedure, is there any real-world performance difference?


                                > The only reason for additional translator/ra support would be if the specification of the possible module or other classpath resources seems beyond just what the vdb classpath offers.

                                Correct.  As it turns out, we like the additional flexibility of specifying the JBoss module name and we don't care about the dependency.

                                • 13. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                                  rareddy

                                  Thanks Steve, I remember implementing, guess looking for wrong method name

                                  • 14. Re: how to access a resource(.txt) file , present in a jboss module, from a vdb.xml?
                                    rareddy

                                    > I am not sure what you mean by function not being right semantic, and not sure about your thoughts on OBJECTARRY solution, may be you can elaborate more?

                                     

                                    I meant OBJECTTABLE.  My thought is that we could have written some Java code that exposes a resource as a string/CLOB and then mapped that Java code into a VDB using OBJECTTABLE.  That approach is arguably simpler than building out a translator.

                                    Even in this case you would have to do some UDF to do the String/Clob object.

                                     

                                      We'll probably just publish it to github.  Does Teiid community have a web page for third-party ecosystem libraries?

                                    We do not right now. However, that is FINE idea. We have thought about it before, but never acted on it. We could add another organization to git for this

                                    1 2 Previous Next