-
1. Re: Phoenix dynamic columns
rareddy Oct 4, 2017 2:05 PM (in response to davidi123)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
davidi123 Oct 5, 2017 4:05 AM (in response to rareddy)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
rareddy Oct 5, 2017 11:23 AM (in response to davidi123)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..