Data Integration

The classic OGSA-DAI data integration scenario deals with the issue of performing a SQL join operation between tables in two different databases. In this page, you will learn how to join a set of XML documents from an XML database with a relational table.

This page will guide you through the following steps:

Below you will find an example solution. You'll need to use threads in order to start both requests at once (or you could write two programs and pass the parameters manually). Make sure that the request which waits for the data is started first.

  1. Locate a sink data service which connects to a MySQL database resource and a source data service which connects to a Xindice database resource.
    String handle = "http://HOST:PORT/wsrf/services/ogsadai/DataService";
    String sinkID = "MySQLResource";
    String sourceID = "XindiceResource";
    DataService sinkService = WSRFServiceFetcher.getInstance().getWSRFDataService(handle, sinkID);
    DataService sourceService = WSRFServiceFetcher.getInstance().getWSRFDataService(handle, sourceID);
    
  2. Create a new table in the sink database.
    String tableName = "mytable";
    String createTable = "create table if not exists " + tableName  +
                         " (id INTEGER, name VARCHAR(64), " +
                         "address VARCHAR(128), phone VARCHAR(20))";
    SQLUpdate create = new SQLUpdate( createTable );
    sinkService.perform( create );
    
  3. Prepare an XPath query for the source XML database - via xPathStatement (using an XPathQuery object). Since Xindice is quite slow, we will use only the first 50 entries in the littleblackbook collection.
    XPathQuery query = new XPathQuery("/entry[@id<50]");
    
  4. Connect the output of the XPathQuery activity object to an XSLTransform activity object in order to transform the resource set format into WebRowSet format (via execution of the xslTransform activity).
    XSLTransform transform = new XSLTransform();
    transform.setXMLInput( query.getOutput() );
    
  5. You can use the XSLT document here to translate the resource set into the WebRowSet format. Deliver the XSLT document from the URL above and connect the input of the transform activity to the delivery's output.
    DeliverFromURL deliver = new DeliverFromURL( url );
    transform.setXSLTInput( deliver.getOutput() );
    
  6. Connect the output of the XSLTransform activity object to the OutputStreamActivity object's input. The output stream will provide the data to another data service.
    OutputStreamActivity outputStream = new OutputStreamActivity();
    outputStream.setInput(transform.getOutput());
    
  7. Then add all activities to the source request.
    ActivityRequest sourceRequest = new ActivityRequest();
    sourceRequest.addActivity( deliver );
    sourceRequest.addActivity( query );
    sourceRequest.addActivity( transform );
    sourceRequest.addActivity( outputStream );
    
  8. You can now start the source request which will provide the data through the output stream.
    sourceService.perform( sourceRequest );
    
  9. Construct another request for the sink with two activities: deliverFromDT (using an DeliverFromDT object) and sqlBulkLoadRowSet (using an SQLBulkLoad object). The deliverFromDT will pull data from the source data service and then pass on the data to the sqlBulkLoadRowSet activity.
    DeliverFromDT deliverFromDT = new DeliverFromDT();
    deliverFromDT.setDataTransportURL( sourceService.getURL() );
    deliverFromDT.setDataTransportResourceID( sourceService.getResourceID() );
    deliverFromDT.setDataTransportStreamID( outputStream.getName() );
    deliverFromDT.setDataTransportMode( DataTransportMode.BLOCK );
    SQLBulkLoad bulkload = new SQLBulkLoad( deliverFromDT.getOutput(), tableName );
    sinkRequest = new ActivityRequest();
    sinkRequest.addActivity( deliverFromDT );
    sinkRequest.addActivity( bulkload );
    
  10. Now start the sink request.
    Response sinkResponse = sinkService.perform(sinkRequest);
    

See examples/tutorials/clienttoolkit/DataIntegrationExample.java for the complete code.