Phoenix dynamic columns

[davidi123]

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

[rareddy]

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

[davidi123]

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.

[rareddy]

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