-
1. Re: Federating Datasources without creating a combined Datamodel in Teiid
rareddy Feb 4, 2016 1:07 PM (in response to reijin90)Yes, you can. Defining a view/logical/abstract layer is optional. You need to define your source layers and then you start writing queries on it. If you want you can use Dynamic VDB, where you would only define the source specifics and not even import the metadata, and deploy the VDB. During the deployment, it will fetch metadata of the sources and build source models, that you can query on. An example VDB look like
ontology-vdb.xml
<vdb name="ontology" version="1"> <model visible="true" name="mysql"> <source name="ontology1" translator-name="mysql5" connection-jndi-name="java:/mysql-ds"/> </model> <model visible="true" name="postgres"> <property name="importer.schemaPattern" value="public"/> <source name="ontology2" translator-name="postgresql" connection-jndi-name="java:/postgres-ds"/> </model> </vdb>
Then all you need to do is define the datasource configuration for both mysql and postgres in JBoss EAP. This is done by editing the standalone-teiid.xml file. You can find sample configuration in the "docs/teiid/datasources" directory of the server.
Above is not using the Designer at all. You can also do the same using the designer, the only difference is, you need to import both source models, and then build a VDB out of it. Then when you deploy, since you already imported the metadata, it will not attempt to import again.
HTH.
Ramesh..
-
2. Re: Federating Datasources without creating a combined Datamodel in Teiid
reijin90 Feb 4, 2016 7:35 PM (in response to rareddy)I may be mistaken, but I think I still need the designer to create the dynamic VDB right?
Is it also possible to add several postgres databases by adding several datasources I assume?
//edit:
found the answer: https://docs.jboss.org/author/display/teiid89final/Dynamic+VDBs
No, I dont need the designer at all it seems.
One last question though:
Will this still work with heterogenous sources and how does the SQL querying from an external application happen? Say I want to access a table in mysql with the same name as in psql? how do I form my sql query to make teiid understand which data source it has to access?
thanks again Ramesh, you are a life saver!
-
3. Re: Federating Datasources without creating a combined Datamodel in Teiid
rareddy Feb 5, 2016 7:37 AM (in response to reijin90)>>Will this still work with heterogenous sources and how does the SQL querying from an external application happen? Say I want to access a table in mysql with the same name as in psql? how do I form my sql query to make teiid understand which data source it has to access?
That the value proposition of Teiid
Once you deploy this Virtual Database (VDB), for all intensive purposes that will look like RDMS for any consuming applications, just like Oracle or MySQL. Now to access it you can use JDBC, ODBC as traditional methods from your applications. If you want REST based access you need to look into OData bases access. Teiid does provide all of them. Take look at our examples, GitHub - teiid/teiid-quickstarts: Quick starts for Teiid
Look at "simpleclient" in the examples for a sample Java client application.
Ramesh..
-
4. Re: Federating Datasources without creating a combined Datamodel in Teiid
reijin90 Feb 5, 2016 12:33 PM (in response to rareddy)Okay, I've deployed a jdbc driver (deploy command) for psql and added these lines to the standalone-teiid.xml:
<datasource jndi-name="java:/postgres1" pool-name="postgres1" enabled="true">
<connection-url>jdbc:postgresql://localhost:5432/ontop_tut</connection-url>
<driver>postgresql-9.4.1207.jre7.jar</driver>
<security>
<user-name>postgres</user-name>
<password>OMITTED</password>
</security>
</datasource>
Now I created a new VDB file and put it into the deployments folder:
<vdb name="ontology" version="1">
<model visible="true" name="postgres1">
<property name="importer.schemaPattern" value="public"/>
<source name="ontology1" translator-name="postgresql" connection-jndi-name="java:/postgres1"/>
</model>
</vdb>
The deployment starts, but I receive an error:
16:51:50,861 INFO [org.teiid.RUNTIME] (teiid-async-threads - 2) TEIID50030 VDB ontology.1 model "postgres1" metadata loaded. End Time: 05.02.16 16:51
16:51:50,864 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.aff_wr_pk has no columns defined
16:51:50,864 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.bk_pk has no columns defined
16:51:50,864 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.course_pkey has no columns defined
16:51:50,864 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.ed_pk has no columns defined
16:51:50,864 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.edition_pk has no columns defined
16:51:50,865 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.grade_pkey has no columns defined
16:51:50,865 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.pk_au has no columns defined
16:51:50,865 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.pk_emerge_authors has no columns defined
16:51:50,865 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.pk_gen has no columns defined
16:51:50,865 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.pk_st has no columns defined
16:51:50,866 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.pr_pk has no columns defined
16:51:50,866 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.semester_pkey has no columns defined
16:51:50,866 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.student_pkey has no columns defined
16:51:50,866 WARN [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31071 Invalid table; Table postgres1.public.tb_stores_pkey has no columns defined
16:51:50,867 INFO [org.teiid.RUNTIME] (teiid-async-threads - 2) TEIID40073 The metadata for the VDB ontology.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.
16:51:50,867 INFO [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 2) TEIID40003 VDB ontology.1 is set to FAILED
The important bit is the error, which tells me that the meta-data is not valid, so I guess I have to change the importer property?
I found this page JDBC Translator - Teiid 8.9 - Project Documentation Editor but I dont really undertand what fails here. It seems it scans the table, but does not "like" empty columns.
For the next step:
If i want to query this table I'd have to query sth. like:
select * from postgres1.public.tb_books?
//Edit:
Imgur: The most awesome images on the Internet
all those "tables" which are found, are not actually tables, but constraints in psql does that mean I have to create a model then? :/
-
5. Re: Federating Datasources without creating a combined Datamodel in Teiid
rareddy Feb 5, 2016 12:34 PM (in response to reijin90)>>The important bit is the error, which tells me that the meta-data is not valid, so I guess I have to change the importer property?
Yes. It is importing the all table types. You may want restrict that using "tableTypes" property. Add following.
<property name="importer.tableTypes" value="TABLE">
Ramesh..
-
6. Re: Federating Datasources without creating a combined Datamodel in Teiid
reijin90 Feb 5, 2016 12:45 PM (in response to rareddy)DUDE! You are awesome man! Great way to start into my leasure time! it works and the query i tried went through!
There is a small typo in your text though, you forgot the / before the >:
<property name="importer.tableTypes" value="TABLE" /> Thanks a lot!!!
//edit:
I will try to add an other datasource tomorrow.
-
7. Re: Federating Datasources without creating a combined Datamodel in Teiid
rareddy Feb 5, 2016 12:49 PM (in response to reijin90)Great! Your thesis done?
Kidding, hoping to learn and contributions from your work in the future.
Ramesh..
-
8. Re: Federating Datasources without creating a combined Datamodel in Teiid
reijin90 Feb 6, 2016 7:27 PM (in response to rareddy)Great! Your thesis done?
haha, I wish! Hopefully I can get this up and running soon. Excel worked as well, I will have to find out, how to access files on the network later. Will it work by just putting a network path to the resource or would I have to select an other resource adapter?
Thanks again for your help! (but probably I'll write with you again)
-
9. Re: Federating Datasources without creating a combined Datamodel in Teiid
rareddy Feb 8, 2016 9:58 AM (in response to reijin90)Yes, the "file" resource-adapter can also read from network drive or shared drive as along as t is reachable from system it is being accessed. As per completely remote, if it is reachable though "http" then you can web services resource adapter. We do not have ftp/sftp support yet. BTW, there is another thread on this subject in community now. With Teiid data vartulization; read file which is located on remote machine