The dynamicvdb-datafederation quickstart demonstrate the ability of Data Virtualization to integrate multiple datasources so that a single query can return results from one or more of those sources. This article give the detailed steps to understand and runt the dynamic data federation example. Specifically, we split the example to 3 stages:
- Run Marketdata VDB
- Run Account VDB
- Run Federation VDB
- JBoss Data Virtualization 6.x
JBoss Data Virtualization 6.x installed and configured correctly, refer to document for details
marketdata.csv should be copied to a location, eg /home/kylin/project/teiid-designer-samples/metadata
customer-schema.sql should be executed against a exist mysql database
- For install, configure, setup Mysql refer to mysql quick document
As below figure:
Market Data (Stock prices), stored in a CSV text file. The Marketdata VDB define a model for the data source. The VDB will be deployed to the JBoss Data Virtualization Runtime, thereby, making it accessible to the user application using JDBC.
Either use the following CLI commands:
/subsystem=resource-adapters/resource-adapter=filemarketdata:add(module=org.jboss.teiid.resource-adapter.file)
subsystem=resource-adapters/resource-adapter=filemarketdata/connection-definitions=fileDS:add(jndi-name=java:/marketdata-file, class-name=org.teiid.resource.adapter.file.FileManagedConnectionFactory, enabled=true, use-java-context=true)
/subsystem=resource-adapters/resource-adapter=filemarketdata/connection-definitions=fileDS/config-properties=ParentDirectory:add(value=/home/kylin/project/teiid-designer-samples/metadata)
/subsystem=resource-adapters/resource-adapter=filemarketdata/connection-definitions=fileDS/config-properties=AllowParentPaths:add(value=true)
/subsystem=resource-adapters/resource-adapter=filemarketdata:activate
/:reload
Or, just edit the standalone.xml in subsystem urn:jboss:domain:resource-adapters:1.1, add the following:
<resource-adapter id="filemarketdata">
<module slot="main" id="org.jboss.teiid.resource-adapter.file"/>
<connection-definitions>
<connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/marketdata-file" enabled="true" use-java-context="true" pool-name="fileDS">
<config-property name="ParentDirectory">
/home/kylin/project/teiid-designer-samples/metadata
</config-property>
<config-property name="AllowParentPaths">
true
</config-property>
</connection-definition>
</connection-definitions>
</resource-adapter>
Copy both marketdata-vdb.xml and marketdata-vdb.xml.dodeploy to JDV_HOME/standalone/deployments, note the following line log hint it deployed success.
13:44:58,634 INFO [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID40003 VDB Marketdata.1 is set to ACTIVE
Run MarketdataClient as java Application, it will query VDB via SQL SELECT * FROM Marketdata, the console output like:
Query SQL: SELECT * FROM Marketdata
1: RHT, 30.00
2: BA, 42.75
3: MON, 78.75
4: PNRA, 84.97
5: SY, 24.30
6: BTU, 41.25
7: IBM, 80.89
8: DELL, 10.75
9: HPQ, 31.52
10: GE, 16.45
As below figure:
Account Information, stored in Mysql database which is referenced as the accounts-ds data source in the Data Virtualization server, The VDB, which call Account, will define a model for the data source, it will be deployed to the JBoss Data Virtualization Runtime, thereby, making it accessible to the user application using JDBC.
We have executed customer-schema.sql against a exist database, it looks
| Database | Username | Password |
|---|---|---|
| customer | jdv_user | jdv_pass |
We also can setup datasource via CLI commands ot edit the configuration file, refer to this document for details
Copy both account-vdb.xml and account-vdb.xml.dodeploy to JDV_HOME/standalone/deployments, note the following line log hint it deployed success.
14:01:30,101 INFO [org.teiid.RUNTIME] (teiid-async-threads - 2) TEIID40003 VDB Account.1 is set to ACTIVE
Run AccountClient as java Application, it will query VDB via SQL SELECT * FROM Stock, the console output like:
Query SQL: SELECT * FROM Stock
1: 1010, HPQ, Hewlett-Packard Company
2: 1011, GTW, Gateway, Incorporated
3: 1012, GE, General Electric Company
4: 1013, MRK, Merck and Company Incorporated
5: 1014, DIS, Walt Disney Company
6: 1015, MCD, McDonalds Corporation
7: 1016, DOW, Dow Chemical Company
8: 1018, GM, General Motors Corporation
9: 1024, SBGI, Sinclair Broadcast Group Incorporated
10: 1025, COLM, Columbia Sportsware Company
11: 1026, COLB, Columbia Banking System Incorporated
As below figure:
We will base above Run Marketdata VDB and Run Account VDB, Market Data (Stock prices), stored in a CSV text file, Account Information, stored in Mysql database which is referenced as the accounts-ds data source in the Data Virtualization server, the VDB which called Portfolio, it have 2 models for each data source, Account and Marketdata, respectively. The VDB will be deployed to the Teiid Runtime, thereby, making it accessible to the user application using JDBC.
Due to the Run Federation VDB base on above Run Marketdata VDB and Run Account VDB, so we don't need either install resource adapter, or setup datasource, we start from deply VDB.
Copy both portfolio-vdb.xml and portfolio-vdb.xml.dodeploy to JDV_HOME/standalone/deployments, note the following line log hint it deployed success.
14:20:05,709 INFO [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID40003 VDB Portfolio.1 is set to ACTIVE
Run PortfolioClient as java Application, the console output like:
Query SQL: SELECT * FROM Marketdata
1: RHT, 30.00
2: BA, 42.75
3: MON, 78.75
4: PNRA, 84.97
5: SY, 24.30
6: BTU, 41.25
7: IBM, 80.89
8: DELL, 10.75
9: HPQ, 31.52
10: GE, 16.45
Query SQL: SELECT * FROM PRODUCT
1: 1010, HPQ, Hewlett-Packard Company
2: 1011, GTW, Gateway, Incorporated
3: 1012, GE, General Electric Company
4: 1013, MRK, Merck and Company Incorporated
5: 1014, DIS, Walt Disney Company
6: 1015, MCD, McDonalds Corporation
7: 1016, DOW, Dow Chemical Company
8: 1018, GM, General Motors Corporation
9: 1024, SBGI, Sinclair Broadcast Group Incorporated
10: 1025, COLM, Columbia Sportsware Company
11: 1026, COLB, Columbia Banking System Incorporated
Query SQL: SELECT A.* FROM (EXEC MarketData.getTextFiles('marketdata.csv')) AS f, TEXTTABLE(f.file COLUMNS SYMBOL string, PRICE bigdecimal HEADER) AS A
1: RHT, 30.00
2: BA, 42.75
3: MON, 78.75
4: PNRA, 84.97
5: SY, 24.30
6: BTU, 41.25
7: IBM, 80.89
8: DELL, 10.75
9: HPQ, 31.52
10: GE, 16.45
Query SQL: SELECT * FROM Stock
1: 1012, GE, 16.45, General Electric Company
2: 1010, HPQ, 31.52, Hewlett-Packard Company
Query SQL: select product.symbol, stock.price, company_name from product, (EXEC MarketData.getTextFiles('marketdata.csv')) f, TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) stock where product.symbol=stock.symbol
1: HPQ, 31.52, Hewlett-Packard Company
2: GE, 16.45, General Electric Company
Query SQL: SELECT A.ID, S.symbol, S.price, A.COMPANY_NAME FROM Marketdata AS S, PRODUCT AS A WHERE S.symbol = A.SYMBOL
1: 1012, GE, 16.45, General Electric Company
2: 1010, HPQ, 31.52, Hewlett-Packard Company