11 Replies Latest reply on Jan 21, 2014 7:28 PM by Ramesh Reddy

    Querying Web Service

    Tom Arnold Novice

      I'm fairly new to Teiid, so apologies if my terminology is a bit off. I'd like to map a proprietary web service into my VDB. I looked at the webservice quickstart and it seems pretty straightforward. My concern is that this webservice is going to return a lot of data which will slow down my queries. Does Teiid support any kind of result set caching for webservices? Based on the quickstart, it looks like the webservice is called once per query even though the data hasn't changed.

       

      Seems like my options are:

       

      - Make use of some kind of caching. Maybe this means writing a materialized view making use of the webservice translator. It's okay if the data is a little out of date.

       

      - Write a custom translator that makes use of the web service's filtering options.

       

      I'll try to get a little more hands-on tomorrow to see if I can answer some of these questions myself. I'm just looking for some advice so I can avoid spending too much time going down the wrong path.

       

      Thanks!

      Tom

        • 1. Re: Querying Web Service
          Ramesh Reddy Master

          Tom,

          - Make use of some kind of caching. Maybe this means writing a materialized view making use of the webservice translator. It's okay if the data is a little out of date.

          Yes, there is resultset caching available that you can use. If web service is issuing same query each time resultset caching is applicable, if  not then look into materialization feature.  See Caching Guide

           

          - Write a custom translator that makes use of the web service's filtering options.

          Typically a web service is backed by a procedure in the Teiid model. So, the transformation SQL of that procedure handles with any criteria that is been passed. So there is no need to write custom translator, Teiid supplies necessary constructs to do so.

          I'm just looking for some advice so I can avoid spending too much time going down the wrong path.

          Feel free to ask any questions related to Teiid, that may expose any weak points in documentation or the examples. We appreciate your active participation in community.

           

          Thanks

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: Querying Web Service
            Tom Arnold Novice

            Thanks Ramesh,

             

            It seems there is no way (short of writing a translator) to push parts of a SQL query to a WS. This is the flow as I understand it.

             

            SQL ====> VIEW ====> PROCEDURE  ====> WS

             

            I can hardcode filters in the VIEW to be passed down to the WS, but filters in the SQL will have to be run in Teiid once the data has been pulled back. On the otherhand if I wrote a translator for the WS, it would look like this.

             

            SQL ====> TRANSLATOR ====> WS

             

            Filters from the SQL would be pushed down to the WS when possible.

             

            Is this accurate?

             

            Thanks,

            Tom

            • 3. Re: Querying Web Service
              Steven Hawkins Master

              Something that may be of interest is that procedure results are cachable as well when the procedure definition contains a cache hint.  We'll allow for up to 256 results based upon hashes of the inputs (assuming non-lob inputs) per cached procedure.

               

              > It seems there is no way (short of writing a translator) to push parts of a SQL query to a WS. This is the flow as I understand it.

               

              Yes, going from a view to a procedure does not offer a way to pass filters.

               

              > Filters from the SQL would be pushed down to the WS when possible.

               

              Yes assuming that the translator exposes tables and appropriate capabilities, then it will get pushdown queries with filters.

               

              Steve

              • 4. Re: Querying Web Service
                Mark Addleman Master

                I think the problem is how to map a WHERE clause (even a very simple one) to the web service.  Can this be done by examining  WSDL?  If not, then  you have to specify the mapping somehow.  Perhaps it can be done in something simpler than a translator...

                • 5. Re: Querying Web Service
                  Ramesh Reddy Master

                  I understood as you are exposing web service rather than consuming it in my first response.

                   

                  I do not think I understand the case


                  SQL ====> VIEW ====> PROCEDURE  ====> WS


                  What is purpose PROCEDURE is here? I am confused when you say "SQL would be pushed down to the WS". You mean literal "WHERE" string from the query needs to be sent to the web service?


                  The flow is


                  SQL ==> VIEW/PROCEDURE ==> TRANSLATOR(web-service) ==> WS

                   

                  I read passing filter to the web-service as passing the input parameters to web-service. If this a SOAP web service, Teiid supports MESSAGE and PAYLOAD modes, where you can build input XML document in the view/procedure and pass-in. If this a rest service the "invoke" procedure allows you to create request parameter.

                   

                  Ramesh..

                  • 6. Re: Querying Web Service
                    Tom Arnold Novice

                    Ramesh,

                     

                    Sorry for the confusion. I think Steve answered my question. To summarize:

                     

                    I want to query the WS with SQL. The WS quickstart shows how to do this using a view that calls the WS (the invoke procedure) and then transforms the response into a table. My concern is that the WS is going to return a very large amount of data, so I need to either have a way to avoid calling the WS (caching) or to translate parts of my SQL WHERE clause to WS parameters so that I pull less data across the network.

                     

                    Thanks,

                    Tom

                    • 7. Re: Re: Querying Web Service
                      Ramesh Reddy Master

                      Tom,

                       

                      Thanks for explaining your usecase. You may have already moved past this thread, but to get closer to topic..

                       

                      Since the Web Services always provide narrow scope in-terms of defining the criteria they accept, I suggest use using a virtual procedure that has input parameter that become input parameters on the web-service, but define the procedure to return a resultset, on which you can retrieve as a view. I believe you can do

                       

                      SELECT * FROM (exec proc(param1, param2) as X
                      

                       

                      If your web-service is complex and can support variety of criteria, then custom translator is only answer. You can see Teiid's OData translator operates exactly same way. However there the interactions and url format defined by a OASIS standard, in your case it is defined by the WS.

                       

                      Thanks.

                       

                      Ramesh..

                      • 8. Re: Re: Querying Web Service
                        Tom Arnold Novice

                        Ramesh,

                         

                        Unfortunately my SQL-generation code (tied with a front-end app) is pretty basic and won't support something like that.

                         

                        I will checkout OData and investigate writing a custom translator.

                         

                        Thanks,

                        Tom

                        • 9. Re: Querying Web Service
                          Tom Arnold Novice

                          Ramesh,

                           

                          I looked into my web service a bit more and now I understand what you are saying. The filtering options are very limited (keyword and date filters implicitly ANDed together). It would be overkill writing a translator when the criteria it could specify to the WS is so narrow. Maybe the translator could issue multiple queries against a source and then intersect them?

                           

                          Anyways, I went back to my SQL-generation code and I think I figured out a way to change it to do what you suggested. I will keep this thread updated with my results.

                           

                          Thanks again!

                          Tom

                          • 10. Re: Querying Web Service
                            Tom Arnold Novice

                            Some related questions...

                             

                            What advantage does streaming offer with the WS translator? Does the WS translator/connector use a scrollable result set? For example: if I use offset/limit in SQL and get enough results, will Teiid close the connection or will it read everything anyways?

                             

                            I am consuming XML from a REST endpoint. Is INVOKE equivalent to doing INVOKEHTTP followed by XMLPARSE?

                             

                            Thanks,

                            Tom

                            • 11. Re: Querying Web Service
                              Ramesh Reddy Master

                              What advantage does streaming offer with the WS translator?

                              In streaming mode Teiid does not create a intermediate copy of the result document, works directly upon source driver returned copy.

                              Does the WS translator/connector use a scrollable result set?

                              The results are not tablular from WS translator, it is document based. If needs to be parsed multiple times do not use streaming.

                              For example: if I use offset/limit in SQL and get enough results, will Teiid close the connection or will it read everything anyways?

                              It really depends upon the source, and query and streaming setting. Typically it will close the stream after the necessary results are read, that is where u are exposing the results directly to source.

                              I am consuming XML from a REST endpoint. Is INVOKE equivalent to doing INVOKEHTTP followed by XMLPARSE?

                              Yes. Use INVOKEHTTP for REST endpoints.

                               

                              Ramesh..