New Data Sources for Teiid Part 1: Using DDL

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

Teiid natively 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. Based on this toolkit, you can easily build access to a simple data source (for example, make requests on twitter, there is a ready-made example in Teiid's delivery), and you can do this only with a DDL description. But for something more complicated, you already 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, organizing access to a source requires two components: a connector and a 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 a response, and converts it into a Teeid-friendly form.

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

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

Basic settings

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

[...]
    teiid-connector-ws.rarNoTransactionhttp://habrahabr.ru/api/profile/
[...]

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

Writing DDL

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

So habr-vdb.xml :

 '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;
        ]]> 

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

Parse the code in more detail

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

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

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

Everything is simple here: we create a virtual procedure getHabrfor which we describe the input parameter and results, and which is implemented through a SELECT request, which, in turn, calls habr.invokeHTTP()to execute a GET request to the web service (with an alias assigned to the result w). Here the parameter is nametransferred from the virtual procedure to the real one. habr.invokeHTTP()returns the received data in the result parameter, which is then passed to the built-in function XMLPARSE(document w.result), wheredocumentmeans this is well-formed XML, not a fragment. This function parses the received data and already passes it in the form of an XML tree, into a function XMLTABLEfor which we, like the virtual procedure, specify a list of columns with types, and in addition to types, specify the paths along which the values ​​will be delivered from an XML document. 'habrauser'means the base path.

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

All. The rest is only to make a request:

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