New data sources for Teiid, part 1: use DDL

Those who are faced with the need to combine several data sources, probably already know about JBoss Teiid , an introductory article about it is even on Habré . In short, this system is designed to represent several physical data sources (for example, a DBMS) in the form of a single virtual database (virtual database, VDB) with access via SQL.

Teiid normally supports many data sources, for example, Oracle , DB2 , M $ SQL Server , MySQL , PostgreSQL , SalesForce , but at the same time it also provides convenient tools for working with web services, XML, JSON. On the basis of this toolkit, you can easily build access to a simple data source (for example, to make requests in twitter, there is a ready-made example in the Teiid delivery), and you can only do with a DDL description. But for something more complicated, you need to write code.

The second part of the article.
')
In this part we will consider the description method using DDL, in the next we will write a translator.

In general, for access to the source, two components are required: a connector (connector) and a translator (translator). The connector is responsible for the connection itself, while the translator aggregates the source data (request), sends it through the connection provided by the connector, receives the answer and converts it into a form that is understandable to Teeid.

Traditionally, to illustrate described in posts on Habré, the authors use something related to Habr. Why not? As a data source, we take habr-api: http://habrahabr.ru/api/profile/%name% .

Among the other connectors supplied with Teiid, there is a universal WS connector, suitable for any service that provides access via http / https. We will use them to access the habr web service.

Basic settings

First you need to register the data source. For JBoss 7, you need to add the following to the % JBOSS_HOME% / standalone / configuration / standalone.xml file (or ... domain ... ):

<subsystem xmlns="urn:jboss:domain:resource-adapters:1.0"> [...] <resource-adapters> <resource-adapter> <archive>teiid-connector-ws.rar</archive> <transaction-support>NoTransaction</transaction-support> <connection-definitions> <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/habrDS" enabled="true" use-java-context="true" pool-name="habr-ds"><!-- [1] --> <config-property name="EndPoint">http://habrahabr.ru/api/profile/</config-property><!-- [2] --> </connection-definition> </connection-definitions> </resource-adapter> </resource-adapters> [...] 

There are 2 important points: in the line marked [1] we set the JNDI name of our data source (we will use this name later), and in the line marked [2] - end point - the URL of our service.

Writing DDL

I already mentioned above about the example of the implementation of requests in twitter, which comes with Teiid. In order to get access to Habr-Api in the same way, it is enough to write the correct DDL in the VDB file.

So, habr-vdb.xml :

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="habr" version="1"> <model name="habr"> <source name="habr" translator-name="rest" connection-jndi-name="java:/habrDS"/> </model> <model name="habrview" type="VIRTUAL"> <metadata type="DDL"><![CDATA[ CREATE VIRTUAL PROCEDURE getHabr(name varchar) RETURNS (login varchar(128), karma float, rating float, ratingposition long) AS select ha.* from (call habr.invokeHTTP(action => 'GET', endpoint =>querystring(name))) w, XMLTABLE('habrauser' passing XMLPARSE(document w.result) columns login varchar(128) PATH 'login', karma float PATH 'karma', rating float PATH 'rating', ratingposition long PATH 'ratingPosition') ha; CREATE VIEW Habr AS select * FROM habrview.getHabr; ]]> </metadata> </model> <translator name="rest" type="ws"> <property name="DefaultBinding" value="HTTP"/> <property name="DefaultServiceMode" value="MESSAGE"/> </translator> </vdb> 

In essence, this is a ready-made implementation of our data source, since written enough to get information about any user through Habr-Api.

Parse the code details

/> - a way to describe a new translator through inheritance (type is the name of the parent translator, name is the name of the newly created translator). This method is intended so that you can set the value of properties (properties) that are different from the default values. Because the default property is DefaultBinding = SOAP12 , then we cannot directly use the ws translator.

The standard WS connector implements the invokeHTTP () procedure, through which all its functionality works. /> serves to connect our habrDS data source (we described it above in standalone.xml ) and the newly created ' rest ' translator. Thus, when calling habr.invokeHTTP() we call the translator with specific parameters and the given URL of the web service.

For direct data processing, we need to create an additional model - /> : the fact is that we can only describe database entities through DDL in models with type="VIRTUAL" , on the other hand, we can only specify data sources and translators in non -virtual models, and we need both that, and another.

Everything is simple here: we create a virtual procedure getHabr , for which we describe the input parameter and results, and which is implemented via a SELECT query, which, in turn, calls habr.invokeHTTP() to execute a GET request to the web service (with assignment of the result). alias w ). Here the name parameter is passed from the virtual procedure to the real one. habr.invokeHTTP() returns the received data in the result parameter, which is then passed to the XMLPARSE(document w.result) built-in function XMLPARSE(document w.result) , where document means that it is well-formed XML, and not a fragment. This function parses the received data and already in the form of an XML tree passes on to the XMLTABLE function, for which we, like for the virtual procedure, specify a list of columns indicating types, and in addition to types we also indicate the ways in which the values ​​will be get from the XML document. 'habrauser' means base path.

And the last step: create a view, which is implemented as a call to a virtual procedure and, accordingly, borrows a list of its output parameters as its structure.

Everything. The rest is just to make a request:

 select * from habrview.habr where name='elfuegobiz'