Hello.
I've got a serious problem to create metadata file.
Indeed metadata files created by CloverDesigner Wizard are good.
I try to create the same metadata file with AnalyzeDB Class and I don't really have the same file.
Here is the correct file I get with the Wizard :
and here is the second one with AnalyzeDB:
Actually what is most wrong is type of fields. I mean AnalyzeDB write "decimal" everywhere for Oracle number fields. The correct type is "number" as the CloverDesigner wizard write!
Of course there are other differences.
I use an Oracle Database. I think the problem may come from jdbcSpecific option. I can't use this option with AnalyzeDB class.
Here's the result :
Do you think it could be the explanation? What are the differences between the wizard and use of AnalyzeDB Class?
Someone can help me?
Thank you.
I've got a serious problem to create metadata file.
Indeed metadata files created by CloverDesigner Wizard are good.
I try to create the same metadata file with AnalyzeDB Class and I don't really have the same file.
Here is the correct file I get with the Wizard :
<?xml version="1.0" encoding="UTF-8"?>
<Record fieldDelimiter=";" name="PATRIMOINE" recordDelimiter="\n" recordSize="-1" type="delimited">
<Field name="INDEX_INDIVIDU" nullable="true" shift="0" type="number"/>
<Field name="CODE_FAMILLE_PATRIMOINE" nullable="true" shift="0" type="number"/>
<Field name="CODE_NATURE_PATRIMOINE" nullable="true" shift="0" type="number"/>
<Field length="11" name="VALEUR_PATRIMOINE" nullable="true" scale="2" shift="0" type="decimal"/>
<Field name="PRODUCTIVITE_PATRIMOINE" nullable="true" shift="0" type="number"/>
<Field name="ANNEE_EVALUATION_PATRIMOI" nullable="true" shift="0" type="number"/>
<Field length="12" name="MONTANT_RESSOURCE_INDUITE" nullable="true" scale="2" shift="0" type="decimal"/>
<Field name="COMMENTAIRE_PATRIMOINE" nullable="true" shift="0" type="string"/>
<Field name="CODE_PERIODICITE" nullable="true" shift="0" type="number"/>
<Field name="CODE_FAMILLE_RESSOURCE" nullable="true" shift="0" type="number"/>
<Field name="CODE_NATURE_RESSOURCE" nullable="true" shift="0" type="number"/>
<Field format="yyyy-MM-dd" name="DATE_DEBUT_PATRIMOINE" nullable="true" shift="0" type="date"/>
<Field format="yyyy-MM-dd" name="DATE_FIN_PATRIMOINE" nullable="true" shift="0" type="date"/>
</Record>
and here is the second one with AnalyzeDB:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Record name="PATRIMOINE" type="delimited">
<Field delimiter="," name="INDEX_INDIVIDU" nullable="yes" type="decimal"/>
<Field delimiter="," name="CODE_FAMILLE_PATRIMOINE" nullable="yes" type="decimal"/>
<Field delimiter="," name="CODE_NATURE_PATRIMOINE" nullable="yes" type="decimal"/>
<Field delimiter="," name="VALEUR_PATRIMOINE" nullable="yes" type="decimal"/>
<Field delimiter="," name="PRODUCTIVITE_PATRIMOINE" nullable="yes" type="decimal"/>
<Field delimiter="," name="ANNEE_EVALUATION_PATRIMOI" nullable="yes" type="decimal"/>
<Field delimiter="," name="MONTANT_RESSOURCE_INDUITE" nullable="yes" type="decimal"/>
<Field delimiter="," name="COMMENTAIRE_PATRIMOINE" nullable="yes" type="string"/>
<Field delimiter="," name="CODE_PERIODICITE" nullable="yes" type="decimal"/>
<Field delimiter="," name="CODE_FAMILLE_RESSOURCE" nullable="yes" type="decimal"/>
<Field delimiter="," name="CODE_NATURE_RESSOURCE" nullable="yes" type="decimal"/>
<Field delimiter="," name="DATE_DEBUT_PATRIMOINE" nullable="yes" type="date"/>
<Field delimiter="\n" name="DATE_FIN_PATRIMOINE" nullable="yes" type="date"/>
</Record>
Actually what is most wrong is type of fields. I mean AnalyzeDB write "decimal" everywhere for Oracle number fields. The correct type is "number" as the CloverDesigner wizard write!
Of course there are other differences.
I use an Oracle Database. I think the problem may come from jdbcSpecific option. I can't use this option with AnalyzeDB class.
public static void main(String[] args) {
String[] parameters = new String[]{
"-dbDriver","oracle.jdbc.OracleDriver",
"-driverLibrary","C:\\Developpement\\workspace\\Infocentre\\lib\\oracle.jar",
"-dbURL","jdbc:oracle:thin:mordred:1521:prcv861",
"-jdbcSpecific","ORACLE",
"-user","system",
"-password","system",
"-o","patrimoine.fmt",
"-q","SELECT * FROM CIVI.PATRIMOINE",
"-plugins","C:\\Developpement\\workspace\\Infocentre\\lib\\plugins\\"};
AnalyzeDB.main(parameters);
}
Here's the result :
[Error] JDBC specific 'ORACLE' does not exist.Do you think it could be the explanation? What are the differences between the wizard and use of AnalyzeDB Class?
Someone can help me?
Thank you.
-
Hello,
CloverDesigner use it's own classes to create clover metadata from database, not the AnalyzeDB utility, so the results from thees two sources can differer. Unfortunatly there is a bug in AnalyzeDB (http://bug.cloveretl.com/view.php?id=2884) so the jdbcSpecific can't be used with AnalyzeDB currently. -
Hello,
CloverETL 2.8.1 will be available in the second half of the October. In the time being you can use dynamic metadata in your graph. Creating mechanisms of the dynamic metadata is the same as in CloverDesigner.
Dynamic metadata definition looks like follows:<Metadata connection="OracleConn" id="dbMetadata" sqlQuery="select * from mytable where 1=0"/>
<Connection dbConfig="${CONN_DIR}/oracle.cfg" id="OracleConn" type="JDBC"/> -
Hi!
I'm afraid it still doesn't work as it should be with release 2.8.1.
I explain :
String[] parameters = new String[]{"-dbDriver","oracle.jdbc.OracleDriver",
"-dbURL","DBURL",
"-jdbcSpecific","ORACLE",
"-user","USER",
"-password","PWD",
"-o","MYTABLE.fmt",
"-q","SELECT * FROM MYTABLE",
"-plugins","lib\\plugins\\"
};
AnalyzeDB.main(parameters);
Here's the result console log :
INFO [Thread-0] - Loading default properties from: defaultProperties
DEBUG [Thread-0] - Plugin com.infodb.component loaded.
id - com.infodb.component
version - 2.8.1
provider-name - null
component { type = BUFFER_COMPARE; className = com.infodb.component.BufferCompare; }
ERROR [Thread-0] - IO error occure in plugin manifest reading - file:/C:/Developpement/workspace/Infocentre/lib/plugins/CVS/plugin.xml. (C:\Developpement\workspace\Infocentre\lib\plugins\CVS\plugin.xml (Le fichier spécifié est introuvable))
DEBUG [Thread-0] - Plugin org.jetel.bulkloader loaded.
id - org.jetel.bulkloader
version - 2.8.1
provider-name -
component { type = ORACLE_DATA_WRITER; className = org.jetel.component.OracleDataWriter; }
component { type = DB2_DATA_WRITER; className = org.jetel.component.DB2DataWriter; }
component { type = INFORMIX_DATA_WRITER; className = org.jetel.component.InformixDataWriter; }
component { type = MS_SQL_DATA_WRITER; className = org.jetel.component.MsSqlDataWriter; }
component { type = MYSQL_DATA_WRITER; className = org.jetel.component.MysqlDataWriter; }
component { type = POSTGRESQL_DATA_WRITER; className = org.jetel.component.PostgreSqlDataWriter; }
DEBUG [Thread-0] - Plugin org.jetel.component loaded.
id - org.jetel.component
version - 2.8.1
provider-name - null
component { type = DATA_READER; className = org.jetel.component.DataReader; }
component { type = DATA_WRITER; className = org.jetel.component.DataWriter; }
component { type = DELIMITED_DATA_READER; className = org.jetel.component.DelimitedDataReader; }
component { type = DELIMITED_DATA_WRITER; className = org.jetel.component.DelimitedDataWriter; }
component { type = SIMPLE_COPY; className = org.jetel.component.SimpleCopy; }
component { type = CONCATENATE; className = org.jetel.component.Concatenate; }
component { type = SIMPLE_GATHER; className = org.jetel.component.SimpleGather; }
component { type = REFORMAT; className = org.jetel.component.Reformat; }
component { type = DB_INPUT_TABLE; className = org.jetel.component.DBInputTable; }
component { type = SORT; className = org.jetel.component.Sort; }
component { type = DB_OUTPUT_TABLE; className = org.jetel.component.DBOutputTable; }
component { type = FIXLEN_DATA_WRITER; className = org.jetel.component.FixLenDataWriter; }
component { type = DEDUP; className = org.jetel.component.Dedup; }
component { type = FIXLEN_DATA_READER; className = org.jetel.component.FixLenDataReader; }
component { type = MERGE; className = org.jetel.component.Merge; }
component { type = MERGE_JOIN; className = org.jetel.component.MergeJoin; }
component { type = EXT_MERGE_JOIN; className = org.jetel.component.MergeJoin; }
component { type = SORTED_JOIN; className = org.jetel.component.MergeJoin; }
component { type = TRASH; className = org.jetel.component.Trash; }
component { type = DB_EXECUTE; className = org.jetel.component.DBExecute; }
component { type = HASH_JOIN; className = org.jetel.component.HashJoin; }
component { type = EXT_HASH_JOIN; className = org.jetel.component.HashJoin; }
component { type = CHECK_FOREIGN_KEY; className = org.jetel.component.CheckForeignKey; }
component { type = DBF_DATA_READER; className = org.jetel.component.DBFDataReader; }
component { type = EXT_FILTER; className = org.jetel.component.ExtFilter; }
component { type = EXT_SORT; className = org.jetel.component.ExtSort; }
component { type = SORT_WITHIN_GROUPS; className = org.jetel.component.SortWithinGroups; }
component { type = PARTITION; className = org.jetel.component.Partition; }
component { type = DATA_INTERSECTION; className = org.jetel.component.DataIntersection; }
component { type = AGGREGATE; className = org.jetel.component.Aggregate; }
component { type = SYS_EXECUTE; className = org.jetel.component.SystemExecute; }
component { type = RUN_GRAPH; className = org.jetel.component.RunGraph; }
component { type = KEY_GEN; className = org.jetel.component.KeyGenerator; }
component { type = APROX_MERGE_JOIN; className = org.jetel.component.AproxMergeJoin; }
component { type = DBJOIN; className = org.jetel.component.DBJoin; }
component { type = XLS_READER; className = org.jetel.component.XLSReader; }
component { type = XLS_WRITER; className = org.jetel.component.XLSWriter; }
component { type = CLOVER_WRITER; className = org.jetel.component.CloverDataWriter; }
component { type = CLOVER_READER; className = org.jetel.component.CloverDataReader; }
component { type = STRUCTURE_WRITER; className = org.jetel.component.StructureWriter; }
component { type = NORMALIZER; className = org.jetel.component.Normalizer; }
component { type = DENORMALIZER; className = org.jetel.component.Denormalizer; }
component { type = ROLLUP; className = org.jetel.component.Rollup; }
component { type = JMS_READER; className = org.jetel.component.JmsReader; }
component { type = JMS_WRITER; className = org.jetel.component.JmsWriter; }
component { type = LOOKUP_JOIN; className = org.jetel.component.LookupJoin; }
component { type = LOOKUP_TABLE_READER_WRITER; className = org.jetel.component.LookupTableReaderWriter; }
component { type = DATA_GENERATOR; className = org.jetel.component.DataGenerator; }
component { type = SEQUENCE_CHECKER; className = org.jetel.component.SequenceChecker; }
component { type = TEXT_TABLE_WRITER; className = org.jetel.component.TextTableWriter; }
component { type = XML_XPATH_READER; className = org.jetel.component.XmlXPathReader; }
component { type = XML_EXTRACT; className = org.jetel.component.XMLExtract; }
component { type = PACEMAKER; className = org.jetel.component.Pacemaker; }
component { type = SPEED_LIMITER; className = org.jetel.component.SpeedLimiter; }
component { type = JAVA_EXECUTE; className = org.jetel.component.JavaExecute; }
component { type = XML_WRITER; className = org.jetel.component.XmlWriter; }
component { type = XSL_TRANSFORMER; className = org.jetel.component.XSLDataTransformer; }
component { type = HTTP_CONNECTOR; className = org.jetel.component.HttpConnector; }
DEBUG [Thread-0] - Plugin org.jetel.connection loaded.
id - org.jetel.connection
version - 2.8.1
provider-name - null
connection { type = JDBC; className = org.jetel.connection.jdbc.DBConnection; }
connection { type = JMS; className = org.jetel.connection.jms.JmsConnection; }
DEBUG [Thread-0] - Plugin org.jetel.ctlfunction loaded.
id - org.jetel.ctlfunction
version - 2.5.2
provider-name - null
ctlfunction { className = org.jetel.ctl.extensions.MathLib; libraryName = math; }
ctlfunction { className = org.jetel.ctl.extensions.DateLib; libraryName = date; }
ctlfunction { className = org.jetel.ctl.extensions.StringLib; libraryName = string; }
ctlfunction { className = org.jetel.ctl.extensions.ConvertLib; libraryName = convert; }
ctlfunction { className = org.jetel.ctl.extensions.ContainerLib; libraryName = container; }
DEBUG [Thread-0] - Plugin org.jetel.engine loaded.
id - org.jetel.engine
version - 2.8.1
provider-name - null
dictionaryType { type = string; className = org.jetel.graph.dictionary.StringDictionaryType; }
dictionaryType { type = object; className = org.jetel.graph.dictionary.ObjectDictionaryType; }
dictionaryType { type = readable.channel; className = org.jetel.graph.dictionary.ReadableChannelDictionaryType; }
dictionaryType { type = writable.channel; className = org.jetel.graph.dictionary.WritableChannelDictionaryType; }
tlCompiler { type = simple.compiler; className = org.jetel.ctl.TLCompiler; }
DEBUG [Thread-0] - Plugin org.jetel.jdbc loaded.
id - org.jetel.jdbc
version - 2.8.1
provider-name - null
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.DB2Specific; name = DB2; database = DB2; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.InformixSpecific; name = Informix; database = INFORMIX; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.MSSQLSpecific; name = MS SQL Server 2008; database = MSSQL; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.MSSQLSpecific2005; name = MS SQL Server 2000-2005; database = MSSQL2005; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.MySQLSpecific; name = MySQL; database = MYSQL; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.OracleSpecific; name = Oracle; database = ORACLE; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.PostgreSpecific; name = PostgreSQL; database = POSTGRE; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.SybaseSpecific; name = Sybase; database = SYBASE; }
jdbcSpecific { class = org.jetel.connection.jdbc.specific.impl.SQLiteSpecific; name = SQLite; database = SQLITE; }
jdbcDriver { jdbcSpecific = MYSQL; dbDriver = org.gjt.mm.mysql.Driver; jdbc.zeroDateTimeBehavior = convertToNull; urlHint = jdbc:mysql://hostname:3306/database; name = MySQL; database = MYSQL; driverLibrary = lib/mysql/mysql-connector-java-5.1.7-bin.jar; }
jdbcDriver { jdbcSpecific = POSTGRE; dbDriver = org.postgresql.Driver; urlHint = jdbc:postgresql://hostname/database; name = PostgreSQL; database = POSTGRE; driverLibrary = lib/postgre/postgresql-8.3-603.jdbc3.jar; }
jdbcDriver { jdbcSpecific = MSSQL; dbDriver = net.sourceforge.jtds.jdbc.Driver; urlHint = jdbc:jtds:sqlserver://hostname:1433/database; name = Microsoft SQL Server; database = MSSQL; driverLibrary = lib/jtds/jtds-1.2.4.jar; }
jdbcDriver { jdbcSpecific = SYBASE; dbDriver = net.sourceforge.jtds.jdbc.Driver; urlHint = jdbc:jtds:sybase://hostname:7100/database; name = Sybase; database = SYBASE; driverLibrary = lib/jtds/jtds-1.2.4.jar; }
DEBUG [Thread-0] - Plugin org.jetel.lookup loaded.
id - org.jetel.lookup
version - 2.8.1
provider-name - null
lookup { type = simpleLookup; className = org.jetel.lookup.SimpleLookupTable; }
lookup { type = dbLookup; className = org.jetel.lookup.DBLookupTable; }
lookup { type = rangeLookup; className = org.jetel.lookup.RangeLookupTable; }
DEBUG [Thread-0] - Plugin org.jetel.sequence loaded.
id - org.jetel.sequence
version - 2.8.1
provider-name - null
sequence { type = SIMPLE_SEQUENCE; className = org.jetel.sequence.SimpleSequence; }
sequence { type = PRIMITIVE_SEQUENCE; className = org.jetel.sequence.PrimitiveSequence; }
DEBUG [Thread-0] - Plugin org.jetel.thirdparty loaded.
id - org.jetel.thirdparty
version - 2.8.1
provider-name - null
component { type = FILTER; className = org.jetel.component.Filter; }
component { type = LDAP_READER; className = com.linagora.component.LdapReader; }
component { type = LDAP_WRITER; className = com.linagora.component.LdapWriter; }
DEBUG [Thread-0] - Plugin org.jetel.tlfunction loaded.
id - org.jetel.tlfunction
version - 2.8.1
provider-name - null
tlfunction { className = org.jetel.interpreter.extensions.MathLib; function = sqrt,log,log10,exp,round,pow,pi,e,random,random_gaussian,random_boolean,random_int,random_long,abs,bit_and,bit_or,bit_xor,bit_set,bit_invert,bit_is_set,bit_lshift,bit_rshift; libraryName = math; }
tlfunction { className = org.jetel.interpreter.extensions.DateLib; function = today,dateadd,datediff,trunc,trunc_date,random_date; libraryName = date; }
tlfunction { className = org.jetel.interpreter.extensions.StringLib; function = concat,uppercase,lowercase,substring,left,right,trim,length,soundex,replace,split,char_at,is_blank,is_ascii,is_number,is_integer,is_long,is_date,remove_diacritic,remove_blank_space,get_alphanumeric_chars,translate,join,index_of,count_char,chop,remove_nonprintable,remove_nonascii,find,cut,edit_distance,metaphone,NYSIIS,random_string; libraryName = string; }
tlfunction { className = org.jetel.interpreter.extensions.ConvertLib; function = num2str,date2str,str2date,date2num,str2num,try_convert,base64byte,byte2base64,bits2str,str2bits,hex2byte,byte2hex,num2num,num2bool,bool2num,str2bool,long2date,date2long,to_string,md5,sha,long2pacdecimal,pacdecimal2long; libraryName = convert; }
tlfunction { className = org.jetel.interpreter.extensions.ContainerLib; function = remove_all,push,pop,poll,remove,insert,sort,copy,reverse,dict_put_str,dict_get_str; libraryName = container; }
DEBUG [Thread-0] - create PluginClassLoader:[file:/C:/Developpement/workspace/Infocentre/lib/plugins/org.jetel.ctlfunction/cloveretl.ctlfunction.jar]
DEBUG [Thread-0] - create PluginClassLoader:[file:/C:/Developpement/workspace/Infocentre/lib/plugins/org.jetel.connection/cloveretl.connection.jar]
WARN [Thread-0] - Graph element DBConnection driver[null]:jndi[null]:url[jdbc:oracle:thin:@mordred:1521:prcv861]:user[ASMA] is not checked by checkConfig() method. Please call TransformationGraph.checkConfig() first.
DEBUG [Thread-0] - create PluginClassLoader:[file:/C:/Developpement/workspace/Infocentre/lib/plugins/org.jetel.jdbc/cloveretl.jdbc.jar]
WARN [Thread-0] - Optimizing connection failed: READ_COMMITTED et SERIALIZABLE sont les seuls niveaux de transaction valides
WARN [Thread-0] - Try to use another jdbc specific
And the metadata file is like before. I mean all of the numeric fileds are set to "decimal" instead of "numeric" !
So how do I get metadata file like I can have with CloverDesigner ?
Thank you.
Please sign in to leave a comment.
Comments 6