How to pass sql query conditions to REST API

[ninglif]

Hello,

Use Teiid Embedded, REST service as data sourec. Using Teiid "web service" resource-adapter and "ws" translator. We would like Teiid to pass the client sql query conditions to the REST API. For example,

From Squirrel client, If I run the sql query:

  select * from users where region='US'

We would like Teiid to pass the condition "region='US'" to the REST call:

  http://host:port/TestProd/users?_filter=(region eq 'US')

Is it possible to configure the dynamic VDB to achieve this? How?

Thank you very much for your help!

[rareddy]

N F,

Currently there is no out of the box way to generate REST service (OData or Custom REST) based on a VDB in Embedded. To provide the REST service, one needs a JEE/Servlet environment, since those can not be guaranteed it is not currently provided. I am sure with some amount of work, we can generate utilities to generate .WAR files for OData or Custom REST given the VDB. 

Then question becomes how you are designing your REST service? If you are developing on your own, then passing a parameter to the query is up to YOUR design.

If it is GET call,

• you can pass as query parameter like http://host:port/users?_filter=value
• or a PATH parameter http://host:port/users/value
• or HEADER

If this POST, then you pass it in the BODY content.

HTH

Ramesh..

[ninglif]

Hi Ramesh,

Thank you very much for the prompt reply.

We already have a REST service running on Tomcat.

Our REST API looks like this:

  GET http://localhost:8080/TestProd/Users

  GET http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

We would like to use sql query to access the data source ( REST service ), and use Teiid embedded as the middleware.

We would like to push the sql query parameters to the REST API.

My current webservice-vdb.xml looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<vdb name="webservice" version="4">

    <property name="UseConnectorMetadata" value="true" />

    <model name="UserSource">

        <source name="webservice" translator-name="ws" connection-jndi-name="java:/UserRESTWebSvcSource"/>

    </model>

      <model name="Users" type="VIRTUAL">

        <metadata type="DDL"><![CDATA[       

            CREATE  VIEW UsersView (

            @id varchar(100) , region varchar(100) )

           AS

          SELECT

            A.@id, A.region

          FROM

        (EXEC UserSource.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users', 'TRUE')) AS f, XMLTABLE('/response/results/documents'     PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;           

        ]]>

        </metadata>

    </model>

</vdb>

When I run the sql query from Squirrel client:

  select * from usersview where region='US'

Teiid is calling the REST API:

  http://localhost:8080/TestProd/Users

Rather than

  http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

What can I do to push the filter parameter to the REST API:?

  http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

Note: 'US' is a variable value.

Thank you very much for your help

[rareddy]

N F,

OK, I thought you are trying expose the VDB as REST web services before, thus my comment as such above. From this comment it is evident that, you are trying to consume from the REST service, which is fine.

Teiid can do what you are saying in your example at the end, but you would have to write a custom translator for it, where it has smarts to convert in coming SQL query into a YOUR own REST API. Obviously the REST service you are consuming is not based on any standard specification like OData or Swagger for which we can automatically do such conversions of criteria into query parameter. In Teiid's OData translator that is exactly what we do, for example see teiid/connectors/translator-odata4 at master · teiid/teiid · GitHub

Actually depending upon who wrote the your "TestProd" service, it looks to be something very similar to OData (at least the  _filter clause, OData is pretty extensive), if that service is just fronting a Database, you can remove that put Teiid in that place and expose that database as OData service without any coding, then come back and consume it using Teiid Embedded.

HTH

Ramesh..

[ninglif]

Hi Ramesh,

Your answer helped a lot. I understand the Odata translator's capability. Our existing REST API is not strictly following the Odata syntax.

I have another question:

If I change the REST endpoint in the vdb from:

  http://localhost:8080/TestProd/Users

to

  http://localhost:8080/TestProd/Users?_filter=(region eq [variable])

Eg:

  (EXEC UserSource.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users?_filter=(region eq [variable])', 'TRUE')) AS f, XMLTABLE('/response/results/documents'     PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;  

Without writing a custom translator, Is there a way to pass the client sql query parameter ( for example 'US' ) to the REST API? Eg. substitute the [variable] with value 'US' automatically when I run the sql query from Squirrel client:?

  select * from usersview where region='US'

So the called REST API will be:

http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

Thank you very much for your help

[rareddy]

N F,

It is not possible to define a VIEW and do the automatic propagation of the condition, but you can define the stored procedure and then you pass the parameter. You can see a example using Designer here [1]. You can even do the same using XML based vdb as

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="webservice" version="1">
    <model name="OnWeb">
        <source name="webservice" translator-name="rest" connection-jndi-name="java:/web-ds"/>
    </model>
    <model name="test" type="VIRTUAL">
        <metadata type="DDL"><![CDATA[
                CREATE VIRTUAL PROCEDURE getUsers(region varchar(50)) RETURNS (name varchar(25), region varchar(50)) AS
                BEGIN
                   SELECT A.@id as name, A.region as region
                   FROM
                     (EXEC UserSource.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users?_filter=(region eq '||test.getUsers.region||')', 'TRUE')) AS f,
                    XMLTABLE('/response/results/documents' PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;     
                END
          ]]>       
        </metadata>
    </model>
    <translator name="rest" type="ws">
        <property name="DefaultBinding" value="HTTP"/>
    </translator>
</vdb>

(BTW, I did not test the above, but it will be very close it )

Then you can execute like

select * from (exec test.getUsers('US'))

HTH

Ramesh..

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

[ninglif]

Hi Ramesh,

When I try to follow your example to use the stored procedure, I get the following exception when execute the query:

     select * from (exec test.getUsers('USA'))

How can I fix this?

log4j:WARN No appenders could be found for logger (org.jboss.logging).

log4j:WARN Please initialize the log4j system properly.

org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "'USA'))[*]" at line 1, column 41.

Was expecting: "as" | id

  at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

  at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

  at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:721)

  at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

  at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:560)

  at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:148)

  at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:556)

  at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1063)

  at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:338)

  at org.teiid.example.JDBCUtils.execute(JDBCUtils.java:85)

  at org.teiid.example.TeiidEmbeddedRestWebServiceDataSource.main(TeiidEmbeddedRestWebServiceDataSource.java:75)

Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "'USA'))[*]" at line 1, column 41.

Was expecting: "as" | id

  at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214)

  at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:164)

  at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:140)

  at org.teiid.dqp.internal.process.Request.parseCommand(Request.java:305)

  at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:399)

  at org.teiid.dqp.internal.process.Request.processRequest(Request.java:473)

  at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:642)

  at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:337)

  at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

  at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

  at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:313)

  at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:245)

  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

  at java.lang.reflect.Method.invoke(Method.java:497)

  at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:177)

  at java.util.concurrent.FutureTask.run(FutureTask.java:266)

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:260)

  at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:175)

  at com.sun.proxy.$Proxy13.executeRequest(Unknown Source)

  at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:688)

  at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:554)

  ... 4 more

SQL: select * from (exec test.getUsers('USA'))

Thank you very much again for your help

[rareddy]

The VDB is corrected as below

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>  
<vdb name="webservice" version="1">  
    <model name="OnWeb">  
        <property name="importer.importWSDL" value="false"/>  
        <source name="webservice" translator-name="rest" connection-jndi-name="java:/web-ds"/>  
    </model>  
    <model name="test" type="VIRTUAL">  
        <metadata type="DDL"><![CDATA[ 
                CREATE VIRTUAL PROCEDURE getUsers(region varchar(50)) RETURNS (name varchar(25), region varchar(50)) AS 
                BEGIN 
                   SELECT A.@id as name, A.region as region 
                   FROM 
                     (EXEC OnWeb.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users?_filter=(region eq '||test.getUsers.region||')', 'TRUE')) AS f, 
                    XMLTABLE('/response/results/documents' PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;      
                END 
          ]]>         
        </metadata>  
    </model>  
    <translator name="rest" type="ws">  
        <property name="DefaultBinding" value="HTTP"/>  
    </translator>  
</vdb> 

The execute "select * from (exec test.getUsers('USA')) as A"

[klmurty]

Hi Ramesh,

I am trying to understand this response from you. What is the JSON(/response/results/documents) format for this and how it looks like? Please provide sample JSON data here. I really appreciate that.

Thanks

Murty

[rareddy]

Parsing of the JSON document is currently done after conversion from XML as shown in above example, there is no direct parsing of JSON document currently in Teiid. XML gives XPATH/XQUERY help to extract individual element content from the document, there is no good facility for JSON. So, there is no direct parsing JSON example. The above is a JSON example.

[klmurty]

I need to understand and visualize for what format of JSON data the above works fine. I just need JSON sample data.

I appreciate your help.

[rareddy]

Something like close to

{ results: {

documents:

[

{id: "1",

region : "foo"

},

{id:"2",

region :"bar"

}

]

}

}