Teiid embedded:Custom Translator - Access to SQL query or parsed SQL Query?

[jrod2016]

In a custom translator, is it possible to get access to the original SQL query or even some sort of Object tree which represents the parsed query? The use case is that I'd like to utilize the predicates specified in the where clause of the SQL query, to limit the data coming back from my data source. Are there any examples of this?

[rareddy]

You can mark as the predicates can be pushed to the source, then you have access to the parsed command, with predicates defined. Also, you can looked at Direct Query stuff, to get original query as is (but this is just passthough Teiid).

[jrod2016]

Thanks for the prompt response Ramesh. So, this is my view definition:

<metadata type="DDL">
  <![CDATA[CREATE VIEW Sales (Product_line VARCHAR(62) , Product_type VARCHAR(82) OPTIONS (SEARCHABLE 'UNSEARCHABLE'), Unit_sale_price DOUBLE , Unit_cost DOUBLE , Revenue DOUBLE ) AS SELECT A.Product_line, A.Product_type, A.Unit_sale_price, A.Unit_cost, A.Revenue FROM (EXEC AdfcoreSource.executeProfile(52)) AS f, XMLTABLE('/*:dataset/*:data/*:row' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) COLUMNS Product_line VARCHAR(62) PATH '*[local-name()=''value''][1]/text()', Product_type VARCHAR(82) PATH '*[local-name()=''value''][2]/text()', Unit_sale_price DOUBLE PATH '*[local-name()=''value''][3]/text()', Unit_cost DOUBLE PATH '*[local-name()=''value''][4]/text()', Revenue DOUBLE PATH '*[local-name()=''value''][5]/text()') AS A]]>
  </metadata>

I marked Product_type as unsearchable, but don't see any partial predicates being passed down when I issue the query:

select * from Sales where Product_type = 'Tools';

I'm looking for the partial predicate here:

   @Override
   public Execution createExecution(Command command, ExecutionContext executionContext, RuntimeMetadata metadata, ADFTeiidConnection connection) throws TranslatorException {

Also, Teiid does the filtering and returns results where the Product_type is 'Tools'.

So, my questions are:

1. Am I missing something while specifying partial predicates?

2. In which object passed to createExecution, should I expect to find the partial predicate?

3. Does Teiid perform filtering anyway, even if I choose to ignore the partial predicates sent to me?

[rareddy]

Sales you defined is VIEW, not a FOREIGN TABLE that will be sent to a [custom] translator for evaluation. The VIEW is being put together in Teiid Query engine, based on your XML document.  You are looking for pushdown where, on XML Document? If yes, that is not possible. You need to pass that in EXEC AdfcoreSource.executeProfile(52) invocation, and predicate can not be converted to a parameter.

Alternatives are:

1) Write your custom translator, that can expose metadata of "sales" as FOREIGN table, and handle "select * from Sales where type = ?"  which may do back end execution of the "AdfcoreSource.executeProfile" code and handle type of predicates as you see fit.

2) This thread shows an alternative way to design How to pass sql query conditions to REST API  your user query based on procedure execution.

Ramesh..

[jrod2016]

Both alternatives have potential. I'll pursue both to see what fits best. I tried the foreign table option and now my vdb xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<vdb name="adfcorevdb" version="2">
    <connection-type>ANY</connection-type>
    <model name="AdfcoreSource" type="PHYSICAL" visible="true">
        <source name="adfcore" translator-name="translator-adf" connection-jndi-name="java:/AdfcoreSource"/>
        <metadata type="DDL">
            <![CDATA[CREATE FOREIGN TABLE Sales (Product_line VARCHAR(62), Product_type VARCHAR(82) OPTIONS (SEARCHABLE 'UNSEARCHABLE'), Unit_sale_price DOUBLE, Unit_cost DOUBLE, Revenue DOUBLE)]]>
        </metadata>
    </model>
</vdb>

I get the sql in my createExecution, however I still don't see the where condition for Product_Type.

[rareddy]

For custom translator approach, for an example how to handle SQL based query into a web service call take look at OData or Swagger translator implementation. Note using this approach you would need to handle any type of SQL that is sent your custom translator. What is being sent to translator is defined by various "supports" methods on the ExecutionFactory which are called tranlator capabilities. That is where you configure that you support WHERE clause and type of comparisons supported etc. Read the Translator Development guide for more details.

[jrod2016]

I tried out the embedded-odata sample , where I modified the VDB definition for categories to the following:

CREATE FOREIGN TABLE Categories (
  CategoryID integer NOT NULL,
  CategoryName string(15) NOT NULL OPTIONS (SEARCHABLE 'UNSEARCHABLE'),
  Description string(2147483647) NOT NULL,
  Picture blob NOT NULL,
  PRIMARY KEY(CategoryID)
) OPTIONS (UPDATABLE TRUE, "teiid_odata:EntityType" 'NorthwindModel.Category');

On executing the query:

execute(c, "SELECT * FROM Categories where CategoryName = 'Condiments'", false);

I still don't see the where clause being populated:

However, the result of execution is:

Dec 12, 2016 1:47:04 PM org.teiid.runtime.JBossLogger log
1: 2, Condiments, Sweet and savory sauces, relishes, spreads, and seasonings, javax.sql.rowset.serial.SerialBlob@9e995bd0

So it is doing the filtering, but not pushing down the predicates.

[rareddy]

When mark the column as unsearchable, that will not gets pushed to the source, the evaluation happens in Teiid engine.

[jrod2016]

Ok, that did the trick. My Table definition is:

CREATE FOREIGN TABLE Categories (
  CategoryID integer NOT NULL,
  CategoryName string(15) NOT NULL OPTIONS (SEARCHABLE 'SEARCHABLE'),
  Description string(2147483647) NOT NULL,
  Picture blob NOT NULL,
  PRIMARY KEY(CategoryID)
) OPTIONS (UPDATABLE TRUE, "teiid_odata:EntityType" 'NorthwindModel.Category');

And I now see the where clause in the Select Object: