3 Replies Latest reply on Oct 5, 2017 11:23 AM by Ramesh Reddy

    Phoenix dynamic columns

    David Wennemaring Newbie

      Hello,

       

      I want to create a view on a Phoenix Table with use of dynamic columns in the Teiid Designer.

       

      In the transformation editor I have this SQL statement.

       

      SELECT
           MID, TS, TMP AS "Value" 
      FROM
           Phoenix.DB(TMP double) 
      

       

      The Error I get is:

      Error in parsing of sql - Encountered " "(" "( "" at line 4, column 16

       

      Is there a possibility to use dynamic columns in Teeid?

       

      Kind regards,

      David

        • 1. Re: Phoenix dynamic columns
          Ramesh Reddy Master

          David,

           

          Try like

           

          SELECT
               MID, TS, A.TMP as "Value"
          FROM 
                Phoenix.DB as A
          

           

          Note you still need to provide how you are building MID and TS virtual columns. The above needs to be a transformation query for the view you are building.

           

          For Example:

           

          CREATE VIEW FOO (
               MID inetger,
               TS timestamp
               "Value" string
          ) AS 
          SELECT 1 as MID, now() as TS, A.TMP as "Value" FROM Phoenix.DB as A
          

           

          HTH.

           

          Ramesh..

          • 2. Re: Phoenix dynamic columns
            David Wennemaring Newbie

            Hi Ramesh,

            thanks for your reply.

             

            I think I didn't make it clear enough what I am looking for.

             

            I have a Phoenix table and want to create a view for it:

             

            In the transformation Editor I have this SQL which works fine:

             

            SELECT
            Phoenix.BIKEDATA.ACT, Phoenix.BIKEDATA.MID, Phoenix.BIKEDATA.TS, 'GWE' AS Measurement, Phoenix.BIKEDATA.GWE AS "Value"
            FROM
            Phoenix.BIKEDATA
            

             

            This works because the Phoenix jdbc provides all necessary columns. But Now I want to use a column which is not provided by the jdbc driver, because it was not there on table creation. It was later added dynamically.

            When I try to read this column like this:

            SELECT
            Phoenix.BIKEDATA.ACT, Phoenix.BIKEDATA.MID, Phoenix.BIKEDATA.TS, 'GWE' AS Measurement, Phoenix.BIKEDATA.GWE AS "Value"
            FROM
            Phoenix.BIKEDATA
            UNION ALL
            SELECT
            Phoenix.BIKEDATA.ACT, Phoenix.BIKEDATA.MID, Phoenix.BIKEDATA.TS, 'LEN' AS Measurement, Phoenix.BIKEDATA.LEN AS "Value"
            FROM
            Phoenix.BIKEDATA
            

            It says that "LEN" is not defined by any relevant group, which makes sense because Teiid/Phoenix doesn't know that this column exists. Phoenix offers the possibility, to select columns dynamically.

            When I change the SELECT accordingly:

            SELECT
            Phoenix.BIKEDATA.ACT, Phoenix.BIKEDATA.MID, Phoenix.BIKEDATA.TS, 'GWE' AS Measurement, Phoenix.BIKEDATA.GWE AS "Value"
            FROM
            Phoenix.BIKEDATA
            UNION ALL
            SELECT
            Phoenix.BIKEDATA.ACT, Phoenix.BIKEDATA.MID, Phoenix.BIKEDATA.TS, 'LEN' AS Measurement, Phoenix.BIKEDATA.LEN AS "Value"
            FROM
            Phoenix.BIKEDATA(LEN double)
            

            I get the error described in my initial question.

            • 3. Re: Phoenix dynamic columns
              Ramesh Reddy Master

              David,

               

              Thanks for the explanation. Teiid currently does not support the dynamic column definitions with Phoenix. It would possible to re-write the query being sent to Phoenix to include the dynamic columns, but for that, we would need to capture those columns in Teiid's metadata with additional metadata, then change the pushdown query logic in Phoenix translator.

               

              You can submit an enhancement request, then if you want to contribute code for it, I can help with that.

               

              Ramesh..