5 Replies Latest reply on May 15, 2017 11:09 AM by Ramesh Reddy

    How can I use RESTful POST as a data source for Teiid Designer?

    David Martin Newbie

      I saw a message about this from two years ago, but not enough information for me to move forward.

       

      I am using the latest and greatest Teiid Designer, v11.  I have a RESTful POST that gives me back a big parcel of JSON with id attributes.  Each id can correspond (with a second RESTful POST) to a data source I'd like to expose relationally with TEIID.

       

      Any hints on how to get this done?

        • 1. Re: How can I use RESTful POST as a data source for Teiid Designer?
          Ramesh Reddy Master

          You mean you need to design a REST API that takes JSON with Id attributes, that in turn, you need to POST on to another service? One is consuming from existing REST API and next is producing the REST API. Why relational? Are you using Designer?

           

          You can see this article [1] for consumption side, and also a DDL based example here [2]

          For producing REST based service see [3]

           

           

          [1] How to Consume a JSON REST Web Service in Teiid Designer

          [2] teiid-embedded-examples/restwebservice-vdb.xml at master · teiid/teiid-embedded-examples · GitHub

          [3] REST Service Through VDB · Teiid Documentation

          • 2. Re: How can I use RESTful POST as a data source for Teiid Designer?
            David Martin Newbie

            Ramesh: 

             

            Let me be as specific as possible.  I have JSON data structures stored in a backend that are only exposed by REST API.  Each data structure is associated with one or more vertices in a graph.  To get traces, I must first call a REST API to obtain the IDs of the vertices with traces.  Then, I can call the data structure retrieval API with one or more of the vertex IDs I retrieved with my first REST call, which gives me "summary traces" that include the trace id of actual full trace data structures.

             

            For example, if I issue a POST like this:

             

            http://<hostname>:<port>/apm/appmap/private/graph

             

            With a suitable JSON POST payload specifying the vertices I want, I get back JSON that looks like this:

             

            {

            "vertices":

             
            {

             

            "vertexId": "1:4464"

            "attributes":

            {

             

            "servletMethod":

              "service"

             

            "endUser":

              "Client"

             

            and so forth for hundred or thousands of vertices.

             

            Now I have a list of vertices.  I can make a second REST call to their associated summary data structures:

             

            http://<server>t:8081/apm/appmap/private/ttviewer/summary

             

            {

                "startTime"       : "2016-03-25T00:00:00-07:00",

                "endTime"     : "2016-03-26T00:00:00-07:00",

                "vertexIds"   : [<v1>,...,<vn>],

                "minDuation:   : <value>,

                "maxDuration"     : <value>

            }

             

            This gets me JSON that looks like this:

             

            {

              "summaries": [

                {

                  "duration": 4029,

                  "startTime": 1481915022165,

                  "description": "/MathSimpleBackend/services/MathSimpleBackend.MathSimpleBackendHttpSoap12Endpoint/",

                  "traceID": "1481915028974:2",

                  "traceType": "ErrorSnapshot",

                  "userID" : "John Smith",

                 "corellationIds": []

                },

                {

                  "duration": 4029,

                  "startTime": 1481915022165,

                  "description": "/MathSimpleBackend/services/MathSimpleBackend.MathSimpleBackendHttpSoap12Endpoint/",

                  "traceID": "1481915028974:3",

                  "traceType": "ErrorSnapshot",

                  "userID" : "Bill Murray",

                  "corellationIds": []

                },

            ......

             

            At this point, I can make final call for details on specific trace ID...

             

            http://<server>:8081/apm/appmap/private/ttviewer/detail

             

            {

              "traceId         : <Trace id string from summary call>

              "crossCorId"    : [<corellation is from summary call>]

            }

             

            Gets me JSON like this...

             

            {

              "transactionData": [

                {

                  "traceId": "1481834038730:2",

                  "correlationKeys": [],

                  "agentName": {

                    "host": "MANER03W2k8DEV",

                    "domain": "SuperDomain",

                    "process": "Tomcat",

                    "agentName": "Tomcat",

                    "processURL": "SuperDomain|MANER03W2k8DEV|Tomcat|Tomcat",

                    "processURLWithoutDomain": "MANER03W2k8DEV|Tomcat|Tomcat",

                    "processURLHashCode": -337901440,

                    "processParser": {

                      "empty": false,

                      "segmentCount": 4,

                      "entireString": "SuperDomain|MANER03W2k8DEV|Tomcat|Tomcat"

                    },

                    "socketType": "default",

                    "lastSeenStartTimeSec": 2147483647,

                    "lastSeenEndTimeSec": -2147483648,

                    "persistent": true,

                    "persistentID": 2

                  },

            etc.

             

             

            What I'd like to do is write queries like this:

             

            SELECT *

            FROM traces_tbl, attributes.tbl

            WHERE

              traces_tbl.host = 'hostname'

              AND traces_tbl.agentName = 'tomcat'

              AND traces.tbl.ts BETWEEN '2017-04-12' AND '2017-5-12'

             

            Eventually, I'd like to have an attributes table too.  Key-value pairings in the attribute table could be joined against the traces_tbl so as to pull only traces that have a matching attribute.

             

            What do you think?  Can this kind of thing be done?

             

            Dave

            • 3. Re: How can I use RESTful POST as a data source for Teiid Designer?
              Ramesh Reddy Master

              Yes, you can. I understand it better. Both consuming REST models. Expose relational to the client.

               

              How does POST payload look like in the first REST query?

              • 4. Re: How can I use RESTful POST as a data source for Teiid Designer?
                David Martin Newbie

                Certainly.   Here's an example.  This might retrieve thousands or even tens of thousands (theoretically), but never hundreds of thousands or millions of vertex ids.

                 

                http://<hostname>:<port>/apm/appmap/private/graph

                {

                    "items": [

                        {

                            "attributeName": "type",

                            "values": [

                                "APPLICATION_ENTRYPOINT",

                                "BUSINESSTRANSACTION"

                            ]

                        }

                    ]

                }

                 

                Which yields the sample JSON already shown above... The "type" is from a list of a dozen or so different possibilities.  These two types are significant because they are known to have traces associated with them.

                • 5. Re: How can I use RESTful POST as a data source for Teiid Designer?
                  Ramesh Reddy Master

                  Basically, you need to create three different stored procedures with their respective inputs and outputs. You can use JSON and XML functions and JSONTOXML function to create requests and parse the results. Maybe these stored procedures need to take in scalar values and return tabular results, hiding all the JSON specifics inside these routines. Then the final part is gluing all these together as wanted in tabular structure, which I am not totally clear which depends on these structures.