7 Replies Latest reply on Feb 10, 2016 9:03 AM by Steven Hawkins

    Union of dynamic VDBs unexpected behaviour (?)

    Benedikt Tröster Newbie

      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!

        • 1. Re: Union of dynamic VDBs unexpected behaviour (?)
          Marco Ardito Master

          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

          • 2. Re: Union of dynamic VDBs unexpected behaviour (?)
            Benedikt Tröster Newbie

            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....

            • 3. Re: Union of dynamic VDBs unexpected behaviour (?)
              Ramesh Reddy Master

              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..

              • 4. Re: Union of dynamic VDBs unexpected behaviour (?)
                Benedikt Tröster Newbie

                Ok, makes sense.

                Thanks again Ramesh! Really appreciate your help!

                • 5. Re: Union of dynamic VDBs unexpected behaviour (?)
                  Marco Ardito Master

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

                   

                  Marco

                  • 6. Re: Union of dynamic VDBs unexpected behaviour (?)
                    Ramesh Reddy Master

                    Yes, column names and types must match.

                    • 7. Re: Union of dynamic VDBs unexpected behaviour (?)
                      Steven Hawkins Master

                      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.