CloverDesigner Metadata Wizard Versus AnalyzeDB Java Class

CloverDX Platform
1

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 :


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

2

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.

3

OK.

Here’s the explanation :frowning:

Then do you have an approximatively release date for fixed CloverETL framework ?

Thank you for your quick answer.

Bye.

4

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"/>
5

If you need it now, I can send you the fixed DBAnalyze class. Unfortunately, this fix just take JdbcScpecific into account, but the resulted metadata shouldn’t be same as a result of designer wizzard. We need to consolidate both algortihms to a single one.

6

Hello

I think I can wait till the release.

Thank you.

7

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.