Union of dynamic VDBs unexpected behaviour (?)

[reijin90]

Hello everyone!

I'm a bit confused, I want to combine two resultsets from different sources via UNION, but it yields an unusual result:

select bk_title from tb_books union select e.product_type from excel1.sheet1 as e

This query only returns the content of bk_title, but not my product_type contents. When querying eacht source separately I receive the correct info, but after unification I always receive the bk_title content (does not matter in which order I unify the queries).

Is this expected? What am I missing?

Greetings!

[m.ardito]

Hi, I suggest to explain in more detail what you mean with "union of dynamic VDBs", as it could be ambiguous, maybe.

If by "VDBs" you mean two different, separated, VDB definitions, each one accessing a separate datasource, the SQL UNION would be executed by your client, for sure, afaik.

If you have a single but multimodel VDB, the SQL UNION could be

- still executed by your client, acting on two different "tables" in your multimodel VDB

- executed by teiid itself, if you defined a "virtual view" in the single VDB, which should merge all results from the two sources, so that your client would see only a single (united) big table.,

and, I dont' know what values your UNION should merge but have you considered "UNION ALL", to avoid remove duplicates?

Set Operations - Teiid 8.12 - Project Documentation Editor

It won't hurt posting your VDBs, at least, I feel

Marco

[reijin90]

I have one combined VDB with two separate sources:

<vdb name="ontology" version="1"> 

    <model visible="true" name="postgres1"> 

        <property name="importer.schemaPattern" value="public"/>

  <property name="importer.tableTypes" value="TABLE" />

        <source name="ontology1" translator-name="postgresql" connection-jndi-name="java:/postgres1"/>              

    </model>

  <model name="excel1">

        <property name="importer.headerRowNumber" value="1"/>

        <property name="importer.ExcelFileName" value="otherholdings.xls"/>

        <source name="excelconnector" translator-name="excel"  connection-jndi-name="java:/excel-file"/>

    </model>

</vdb> 

Queries are being sent over to Teiid via the JDBC driver (with access to jdbc:teiid:ontology@mm://localhost:31000). You are right, it could be that the application does not show the combined tables results, but the application is a black box and I can not edit it. It is still odd though, that only the results of the psql source are returned (no matter of the select statement was 1. or 2. ) and not the Excel contents.

Would creating a virtual view be a viable option for a large amount of sources (more than 20 different files)? I could then query the large (combined) view instead of each source alone right?

Union all did not work sadly, I tried that . Maybe it is an issue with the Types? Both should be strings though....

[rareddy]

Make sure individually you are getting the results from each side of the union, then issue your union based query. You need to write like

select bk_title as title from tb_books union select e.product_type as title from excel1.sheet1 as e

If you do not want to submit the union call all the time, or you want abstract that logic away from the user then you want to create a view, with above as transformation SQL.

Ramesh..

[reijin90]

Ok, makes sense.

Thanks again Ramesh! Really appreciate your help!

[m.ardito]

Also remember that as the above chapter says "the output columns must be comparable types".

Marco

[rareddy]

Yes, column names and types must match.

[shawkins]

Just column types are expected to match - if they don't a resolver error will be thrown.  The column names will be taken from the first branch and don't have to match the other branches.