-
1. Re: virtual procedure SQL transformation
shawkins Oct 19, 2015 1:08 PM (in response to jietao)> Can I create sum from a column?
The engine is just looking for an explicit name for the element - XMLFOREST(SUM(table.colum1) AS something)
> Is there any guide about the syntax in the transformation editor?
The direct link to the latest is XML Functions - Teiid 9.0 (draft) - Project Documentation Editor
Or you can see the whole doc set at Documentation · Teiid
-
2. Re: virtual procedure SQL transformation
jietao Oct 20, 2015 4:04 AM (in response to shawkins)Thanks. It works now.
Another question: I have an input parameter in my procedure of type dateTime. As I "preview data" I give "2002-05-30T09:00:00" in the "input parameter" wizard of Teiid designer. I got this:
org.teiid.runtime.client.teiidClientException: unnable to convert procedural parameter .... of type string to expected type timestamp
It seems that Teiid takes my input as a string. How to correctly give the input or can I not define a parameter as dateTime?
-
3. Re: virtual procedure SQL transformation
shawkins Oct 20, 2015 8:50 AM (in response to jietao)> It seems that Teiid takes my input as a string. How to correctly give the input or can I not define a parameter as dateTime?
Even if the design time type is dateTime, unless this procedure is invoked from a web context the parameter will be understood as the runtime type timestamp. So while 2002-05-30T09:00:00 is the iso datetime format, the expected sql timestamp format does not include the T.
-
4. Re: virtual procedure SQL transformation
jietao Oct 22, 2015 5:19 AM (in response to shawkins)I changed type to string. This is easy for preview. Other question: I have many variable computation in the transformation editor of the virtual procedure. How can I test whether the computation is correct (the variables get correct values)?
-
5. Re: virtual procedure SQL transformation
shawkins Oct 22, 2015 9:11 AM (in response to jietao)There isn't yet a debugger mode for designer. A workaround is to add logging statements to the logging system procedures in your sql - System Procedures - Teiid 9.0 (draft) - Project Documentation Editor
Then you can see log messages of exactly what you are looking for.
Also at a trace level you will see all variable assignments in the log, but it will be quite verbose.
-
6. Re: virtual procedure SQL transformation
jietao Oct 22, 2015 10:06 AM (in response to shawkins)Thx. I added EXEC SYSADMIN.logMsg(level => 'ERROR', context => 'org.something', msg => VARIABLES.h1fr)
but I do not see anything in my error log. Where is the log? Is it correct to put the variable (which I want to observe" in msg?
-
7. Re: virtual procedure SQL transformation
shawkins Oct 22, 2015 2:16 PM (in response to jietao)> Where is the log?
It will be in the server log - for example standalone/log/server.log
> Is it correct to put the variable (which I want to observe" in msg?
Yes, the message can be any string/value of what you want printed in the log.
See also Logging - Teiid 9.0 (draft) - Project Documentation Editor
-
8. Re: virtual procedure SQL transformation
jietao Oct 23, 2015 9:51 AM (in response to shawkins)I noted that teiid designer modified my "select" statement written in the transformation editor (e.g., add date_format, ...). This is a problem for our database where we have to "force primary key" and others. Is it possible to let teiid take the same SQL statement as I written in the transformation editor?
-
9. Re: virtual procedure SQL transformation
shawkins Oct 23, 2015 10:47 AM (in response to jietao)The sql statement you write in Designer is in Teiid SQL, which will go through several stages of optimizations before getting to the source sql. There are some mechanisms for associating hints with the source query - such as the source hint mechanism (see Federated Optimizations - Teiid 9.0 (draft) - Project Documentation Editor source hint), which works for non-positional hints, or you can use a direct query procedure to by pass Teiid altogether. Another option is to use extension metadata and translator customizations to handle additional manipulations to source sql.
-
10. Re: virtual procedure SQL transformation
jietao Oct 24, 2015 9:19 AM (in response to shawkins)Thanks for the answer. My use case is: I need a web service API to access our databases (mysql and Hive). The easiest way is to create rest war using teiid designer via creating virtual procedures that only works with a view model. But I want my queries (without teiid modification) going directly to my database source. One of your solution above "use a direct query procedure to by pass Teiid altogether": how can I do this in my case?
-
11. Re: virtual procedure SQL transformation
jietao Oct 26, 2015 5:45 AM (in response to jietao)I studied a little bit and see one solution:
create native query procedures to my source and then "exc" the procedure in my virtual procedure, right? Question:
1. my query is created on-the-fly: I have to use JAVA to create an SQL string and give back the string to my virtual procedure. If I understand correctly, I must give the exact query in the native query procedure. How shall I work around?
2. my native query is not simple: I select multiple columns, join several tables and union results. Possible to use native procedure for this?
-
12. Re: virtual procedure SQL transformation
shawkins Oct 26, 2015 8:44 AM (in response to jietao)> my query is created on-the-fly: I have to use JAVA to create an SQL string and give back the string to my virtual procedure. If I understand correctly, I must give the exact query in the native query procedure. How shall I work around?
There are multiple flavors to native/direct queries. One is to define a procedure/table with extension metadata of what is actually going to be executed at the source. The other is to enable the direct query procedure on your translator and then you'll have access to a Teiid procedure that can be used to directly query the source. If the manipulation that you need to do is parameterizable, then you could use a procedure with the native-query extension metadata. If the manipulation that you need to do is filtering, projection, etc. against a common base query, then you can use a view with the native-query extension metadata. If not, then you'd have to use the direct query procedure. See JDBC Translators - Teiid 9.0 (draft) - Project Documentation Editor under native queries and direct query procedure.
> my native query is not simple: I select multiple columns, join several tables and union results. Possible to use native procedure for this?
It doesn't really matter what it does as long as it's all against a single source.
-
13. Re: virtual procedure SQL transformation
jietao Oct 26, 2015 10:20 AM (in response to shawkins)thx. I enabled the direct query procedure with an override translator for my VDB. I do in the transformation editor of a virtual procedure:
BEGIN
SELECT x.kid, x.visits FROM (EXEC RealMySQL.native('SELECT ......')) AS w, ARRAYTABLE(w."output" COLUMNS kid string, visits long) AS x;
END
I got error from the transformation editor: ERROR: java.lang.Exception: RealMySQL.native does not exist. (where RealMySQL is my source model that is included in my VDB).
Based on the document, "JDBC translator provides a procedure to execute any ad-hoc SQL query". So, I need not writing the procedure "native".
What is the problem?
My virtual procedure is combined with a virtual view transformed from my source model RealMySQL
-
14. Re: virtual procedure SQL transformation
shawkins Oct 27, 2015 8:03 AM (in response to jietao)> I got error from the transformation editor: ERROR: java.lang.Exception: RealMySQL.native does not exist. (where RealMySQL is my source model that is included in my VDB).
If Teiid Designer does not have an option to add a direct query procedure, then you would need to add one manually or use the Teiid Connection Importer against a translator that is configured with the option enabled - although from your earlier experience I gather that Teiid Designer does not allow setting translator properties for the import process so that may not work.