I am an absolute nubie to CloverETL. I have been playing with it for a while and I really like what I see. So, of course, I immediately try to use it to do a particular task and can't figure out how to pass a parameter to a SQL query I'm using in a dbInputTable component. I have created a parameter called TODAY and set its value to `today()`. But when I reference it in the SQL script, it fails validation. I know there must be a way to do this, I'm just spinning my wheels trying to get it to work. Am I not understanding how parameters are used or is my syntax just wrong?
This is my SQL script:
SELECT dossier.dosid AS Dosid,
dossier.actid AS CompanyID,
F_PlDosPhaCode(dossier.dosid, ${TODAY}) AS Phase,
trunc(crevt.credtcreat) AS Closing_Date,
BOA_f_GetConversionData(dossier.dosid, To_Number(To_Char(${TODAY},'YYYY')),'INTEREST') AS Converted_Interest,
PA_ETL.F_GetFirstPaymentDueDate(DOSSIER.DOSID) AS First_Payment_Due_Date,
PA_ETL.F_GetInitialFundingDate(DOSSIER.DOSID) AS Initial_Funding_Date,
dosnum AS Contract_Number,
dosnom AS Contract_Name,
decode(dostypefinancement,'PRETCHA','Construction','Regular') AS Contract_Type,
dossier.dosmtcomite AS Original_Commitment,
dosdtdeb AS Start_Date
from dossier join crevt on dossier.dosid = crevt.dosid
where crevt.tmffonction = 'EVD_REAL' AND
crevt.credtvalid IS NOT NULL;
Thanks.
This is my SQL script:
SELECT dossier.dosid AS Dosid,
dossier.actid AS CompanyID,
F_PlDosPhaCode(dossier.dosid, ${TODAY}) AS Phase,
trunc(crevt.credtcreat) AS Closing_Date,
BOA_f_GetConversionData(dossier.dosid, To_Number(To_Char(${TODAY},'YYYY')),'INTEREST') AS Converted_Interest,
PA_ETL.F_GetFirstPaymentDueDate(DOSSIER.DOSID) AS First_Payment_Due_Date,
PA_ETL.F_GetInitialFundingDate(DOSSIER.DOSID) AS Initial_Funding_Date,
dosnum AS Contract_Number,
dosnom AS Contract_Name,
decode(dostypefinancement,'PRETCHA','Construction','Regular') AS Contract_Type,
dossier.dosmtcomite AS Original_Commitment,
dosdtdeb AS Start_Date
from dossier join crevt on dossier.dosid = crevt.dosid
where crevt.tmffonction = 'EVD_REAL' AND
crevt.credtvalid IS NOT NULL;
Thanks.
-
Hi jgaffleck,
Unfortunately parameter substitution does not work in 'SQL query' property of DbInputTable. Workaround is to prepare single record with statement in DataGenerator and pass it over edge to DbInputTable using "port:$0.FieldName:discrete" syntax (http://doc.cloveretl.com/documentation/ ... aders.html) written into "Query URL" property.
I hope this helps. -
Hi,
that is correct. DataGenerator is component available in commercial version only. However, there might be a solution for you. When you use parameter reference (e.g. ${YOUR_PARAMETER}) in your SQL query, the parameter value is placed into a query as a string. Depending on a data type and used database the value might need to be treated accordingly.
Let's take a look at the following example. You would like to read records from a MySQL table, but only those where a column value of date type is equal to a data stored in a graph parameter. Then the query might look like this:SELECT $firstOutputField:=some_column, $secondOutputField:=another_column FROM my_table WHERE some_date_column = '${MYDATE}'
The issue might be caused by something else as well, so I would like to ask you to provide more information:- Run the graph with a parameter reference (e.g. ${SOME_PARAM}) and without a reference (with some particular date value) - if any of the runs fails, could you please provide us with the log?
- What is the value (or at least format) of the parameter you were trying to pass to the query?
- Have you tried to run the graph with a parameter reference, or did you just try to validate the query?
- What database you use?
Thank you. -
Thanks. Not sure I followed all of that but I will give it a shot. I am using Oracle for the database. I came up with a bit of a work around - created a table in the database to store the parameter values then just join to it in the select. All I have to do is come up with an easy way to populate the table in the ETL process.
I am having some data issues though - might be a character set issue. I assume Clover can handle uniciode? -
I think I resolved the character set issue, but I get another error now that I cannot figure out. It's telling me I have a datatype mismatch between Clover and the SQL. I don't understand why it thinks the Closing_Date is numeric coming from the SQL. I even tried casting NULL as a date, and it still thinks it's numeric. Is there something about Oracle dates that I'm missing?
Thanks for the help....
Here is the output:
INFO [main] - *** CloverETL framework/transformation graph, (c) 2002-2014 Javlin a.s. ***
INFO [main] - Running with CloverETL library version 4.0.0.M1 build#026 compiled 10/04/2014 01:40:45
INFO [main] - Running on 8 CPU(s), OS Windows 8, architecture amd64, Java version 1.7.0_11, max available memory for JVM 2778048 KB
INFO [main] - Loading default properties from: defaultProperties
INFO [main] - Graph definition file: Load scd_contracts.grf
INFO [main] - Graph revision: 1.44 Modified by: Jim Modified: Sun Jul 13 13:44:39 EDT 2014
INFO [main] - Checking graph configuration...
WARN [main] - Issue in component [DBOutputTable:DBOUTPUT_TABLE]
Compatibility of field types could not have been validated (not supported by the driver).
INFO [main] - Graph configuration is valid.
INFO [main] - Graph initialization (Load scd_contracts)
INFO [main] - Initializing connection:
INFO [main] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriverImpl@1373ef79]:jndi[null]:url[jdbc:oracle:thin:@192.168.23.12:1521:TSH3]:user[GWLDW] ... OK
INFO [main] - Initializing connection:
INFO [main] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriverImpl@1373ef79]:jndi[null]:url[jdbc:oracle:thin:@192.168.23.12:1521:TSH3]:user[gwprodh2] ... OK
INFO [main] - Initializing phase 0
INFO [main] - Phase 0 initialized successfully.
INFO [WatchDog_0] - Job parameters: PROJECT=., CONN_DIR=${PROJECT}/conn, DATAIN_DIR=${PROJECT}/data-in, DATAOUT_DIR=${PROJECT}/data-out, DATATMP_DIR=${PROJECT}/data-tmp, GRAPH_DIR=${PROJECT}/graph, SUBGRAPH_DIR=${GRAPH_DIR}/subgraph, LOOKUP_DIR=${PROJECT}/lookup, META_DIR=${PROJECT}/meta, PROFILE_DIR=${PROJECT}/profile, SEQ_DIR=${PROJECT}/seq, TRANS_DIR=${PROJECT}/trans, From_Date=to_date('01012013','mmddyyyy'), To_Date=to_date('01012013','mmddyyyy'), Lancode='EN', Contracts='ALL', Cemetary=to_number('0'), TODAY=`today()`
INFO [WatchDog_0] - Pre-execute initialization of connection:
INFO [WatchDog_0] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriverImpl@1373ef79]:jndi[null]:url[jdbc:oracle:thin:@192.168.23.12:1521:TSH3]:user[GWLDW] ... OK
INFO [WatchDog_0] - Pre-execute initialization of connection:
INFO [WatchDog_0] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriverImpl@1373ef79]:jndi[null]:url[jdbc:oracle:thin:@192.168.23.12:1521:TSH3]:user[gwprodh2] ... OK
INFO [WatchDog_0] - Starting up all nodes in phase [0]
INFO [WatchDog_0] - Successfully started all nodes in phase!
ERROR [WatchDog_0] - Component [DBInputTable:DBINPUT_TABLE1] finished with status ERROR. (Out0: 0 recs)
Parsing error in field 2 - CLOSING_DATE (date) in metadata S_DIM_CONTRACTS
Incompatible Clover & JDBC field types - field 'CLOSING_DATE'; Clover type: date; SQL type: NUMERIC
ERROR [WatchDog_0] - Error details:
org.jetel.exception.JetelRuntimeException: Component [DBInputTable:DBINPUT_TABLE1] finished with status ERROR. (Out0: 0 recs)
at org.jetel.graph.Node.createNodeException(Node.java:571)
at org.jetel.graph.Node.run(Node.java:547)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.lang.RuntimeException: Parsing error in field 2 - CLOSING_DATE (date) in metadata S_DIM_CONTRACTS
at org.jetel.connection.jdbc.SQLDataParser.populateField(SQLDataParser.java:258)
at org.jetel.connection.jdbc.SQLDataParser.parseNext(SQLDataParser.java:199)
at org.jetel.connection.jdbc.SQLDataParser.getNext(SQLDataParser.java:127)
at org.jetel.component.DBInputTable.processSqlQuery(DBInputTable.java:355)
at org.jetel.component.DBInputTable.execute(DBInputTable.java:304)
at org.jetel.graph.Node.run(Node.java:509)
... 1 more
Caused by: java.sql.SQLException: Incompatible Clover & JDBC field types - field 'CLOSING_DATE'; Clover type: date; SQL type: NUMERIC
at org.jetel.connection.jdbc.AbstractCopySQLData.sql2jetel(AbstractCopySQLData.java:187)
at org.jetel.connection.jdbc.SQLDataParser.populateField(SQLDataParser.java:247)
... 6 more
INFO [WatchDog_0] - Execution of phase [0] finished with error - elapsed time(sec): 2
ERROR [WatchDog_0] - !!! Phase finished with error - stopping graph run !!!
INFO [WatchDog_0] - Post-execute finalization of connection:
INFO [WatchDog_0] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriverImpl@1373ef79]:jndi[null]:url[jdbc:oracle:thin:@192.168.23.12:1521:TSH3]:user[GWLDW] ... OK
INFO [WatchDog_0] - Post-execute finalization of connection:
INFO [WatchDog_0] - DBConnection driver[org.jetel.connection.jdbc.driver.JdbcDriverImpl@1373ef79]:jndi[null]:url[jdbc:oracle:thin:@192.168.23.12:1521:TSH3]:user[gwprodh2] ... OK
INFO [WatchDog_0] - -----------------------** Summary of Phases execution **---------------------
INFO [WatchDog_0] - Phase# Finished Status RunTime(sec) MemoryAllocation(KB)
INFO [WatchDog_0] - 0 ERROR 2 23339
INFO [WatchDog_0] - ------------------------------** End of Summary **---------------------------
INFO [WatchDog_0] - WatchDog thread finished - total execution time: 2 (sec)
INFO [main] - Freeing graph resources.
ERROR [main] -
------------------------------------------- Error details ---------------------------------------------
Component [DBInputTable:DBINPUT_TABLE1] finished with status ERROR. (Out0: 0 recs)
Parsing error in field 2 - CLOSING_DATE (date) in metadata S_DIM_CONTRACTS
Incompatible Clover & JDBC field types - field 'CLOSING_DATE'; Clover type: date; SQL type: NUMERIC
-------------------------------------------------------------------------------------------------------
ERROR [main] - Execution of graph failed ! -
Could you please post create statement of your table in Oracle database? I will try to reproduce the issue on my side. Version of Oracle DB as well as version of your jdbc driver (in case you used your own driver instead of the default one) would be useful too.
I use combination Oracle timestamp and CloverETL date all the time and it has never been an issue so I'm sure we'll figure it out eventually.
Regards, -
Hi jgaffleck,
Unfortunately parameter substitution does not work in 'SQL query' property of DbInputTable. Workaround is to prepare single record with statement in DataGenerator and pass it over edge to DbInputTable using "port:$0.FieldName:discrete" syntax (http://doc.cloveretl.com/documentation/ ... aders.html) written into "Query URL" property.
I hope this helps.
Are there any plans to have parameter substitution in the upcoming release ?? Most of the times, there are SQL's which have few predicates which are parameterised and will be computed at the graph parameter level. e.g. gmt offset for the date range based on the day light savings.
Computing them at graph level and having parameter substitution in SQL will make life more easy. -
I have attached the DDL for the table in question.
Oracle version is 11.2.0.1.0
I have also attached the screen shot of my connection parameters.
Thanks for the help. -
Lubos,
Just realized I sent you the wrong DDL - that was the definition of the destination table. The source is from multiple tables, but the column in question comes from the CREVT table (DDL attached). This column is stored as a timestamp (as are all Oracle dates). I am truncating it to convert it to just a date (MMDDYYYY 00:00:00).
Thanks again. -
Are there any plans to have parameter substitution in the upcoming release ?? Most of the times, there are SQL's which have few predicates which are parameterised and will be computed at the graph parameter level. e.g. gmt offset for the date range based on the day light savings.
Computing them at graph level and having parameter substitution in SQL will make life more easy.
Sorry for this confusion. Parameters are in fact supported in SQL query property. You can use "SELECT * FROM ${MY_TABLE}" without any issues. But you can not use dictionary values directly, such queries would have to be prepared in advance in CTL in the way my colleague described above. -
Lubos,
Just realized I sent you the wrong DDL - that was the definition of the destination table. The source is from multiple tables, but the column in question comes from the CREVT table (DDL attached). This column is stored as a timestamp (as are all Oracle dates). I am truncating it to convert it to just a date (MMDDYYYY 00:00:00).
Thanks again.
I am not sure I understand. I have looked into your sql script and I do not see any TIMESTAMP column there. Are you sure you posted the right script? Or did you mean DATE type instead of TIMESTAMP? Nevertheless, I have just tried both DATE and TIMESTAMP types in my Oracle table and both can be read by CloverETL metadata of "date" type without any further settings.
Could you please try to create a new table in your DB, with only one TIMESTAMP column? Then insert one value into this table and try to read it with CloverETL. If this does not work, please send me the create statement again as well as your CloverETL graph.
Thank you for your cooperation. -
OK - Here's where I am now:
1) I created a table with one timestamp column and populated it with sysdate. I was able to read this in Clover.
2) Then I tried the same thing with one date column and populated it with trunc(sysdate). This time I get the data type mismatch error. Clover thinks this is an INTEGER.
3) Then I tried casting the date column as a timestamp in the SQL in the DBInputTable and that worked!
So I figured I had it licked. So I went back and redefined all my date columns to timestamp in my OUTPUT table and cast all my date columns to timestamps in the SQL script retrieving the data in the DBInputTable component and tried to run the original graph. An POOF - back to the same error on CLOSING DATE.
This has got me stumped...
I have attached both graphs.
I really appreciate the attention here. I'm trying to evaluate this software as a possible solution to building a datawarehouse from a highly-normalized transactional schema. -
I think I know where the issue lies. You are selecting closing date as the fourth column from your table but your metadata says that closing date is the second one. You should be careful about the order of fields in your queries. CloverETL expects closing date in the second column and the second column in your query is Company number which is an integer.
Please reorder either columns in your query or fields in your metadata to match each other and the issue should disappear.
Regards, -
The workaround is to dynamically create the entire SQL select as string in Reformat component and feed it to the DBInputTable component on an Input port with Processing Type stream.
For example, to get a date value from one database A and use it as part of the select query in another database B then write it elsewhere C, setup:
DBInputTableA --meta_date--> Reformat --meta_sql--> DBinputTableB --meta_sql_result--> Trash (or DBOutputTableC)
DBInputTableA : SQL query: SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- any valid query returing a date/time
meta_date: last_load date Field Property Advanced Format: yyyy-MM-dd HH:mm:ss
Reformat: $out.0.select_qry = "select * from tableB where entry_date > '" +
date2str($in.0.last_load, 'yyyy-MM-dd HH:mm:ss') + "'" ;
meta_sql: select_qry string
DBInputTableB: Properties Basic Query URL: port:$0.select_qry:stream
meta_sql_result: metadata matching the results of DBInputTableB
Please sign in to leave a comment.
Comments 19