how to create dynamic schema in Talend Studio 8

[Debasish]

I need to load 100+ files (pipe delimited) with different schemas to DB tables.

I have created a mapping using Dynamic Schema and it is able to iterate the files , create table and load the data.

But issue is , All the column data types are created as VARCHAR2 .

What I want is that the column datatype in the output tables should be created as per data present in the input files.(say NUMBER for values like 1,2,3 etc and DATE for values like 1/1/2020 etc).

Please suggest how to make this happen.

Talend Studio 

[Shicong_Hong · Accepted solution]

Hello 

Refer to this KB article, have a try to access the column properties, parse and change them at runtime. Here is an example code:

Dynamic columns = input_row.data;

for (int i = 0; i < columns.getColumnCount(); i++)

{

DynamicMetadata columnMetadata = columns.getColumnMetadata(i);

String col_name=columnMetadata.getName();

String col_value=(String)columns.getColumnValue(i);

//check if the value is a numeric data.

if(Mathematical.NUM(col_value)==1){ 

columnMetadata.setType("id_Integer");

}

//check if the value is a Date

if(TalendDate.isDate(col_value, "yyyy-MM-dd")==true){

columnMetadata.setType("id_Date");

columns.setColumnValue(i, TalendDate.parseDate("yyyy-MM-dd", col_value));

}

}

output_row.data=columns;

Hope it helps you!

Regards

Shicong

[Debasish]

Hi Shicong,

Thank you for the solution.

It works for all scenarios except for when any column has decimal value (say 10.50) where Mathematical.NUM returns False.

Also , one observation that I have is the column type for numeric values are created as 'Integer'.

So is there a way to create the column type as Oracle DB type 'NUMBER' ?

I tried columnMetadata.setDbType("Number") but it did not work.

Thanks

Deb

[Shicong_Hong]

Decimal value (say 10.50) contains a comma, so Mathematical.NUM returns False. You need to find another way to parse the DECIMAL value. I only tested with Mysql, the code does not cover for DB types. For Oracle 'Number' date, have you tried columnMetadata.setType("id_Number"); ?

[Debasish]

Hi @Shicong_Hong , 

columnMetadata.setType("id_Number") did not throw any error , But the column got created as VARCHAR and not as Number.
So can you suggest some way for NUMBER type.

Thanks

[Shicong_Hong]

I don't have an environment for testing at the moment. You can try to call the function columnMetadata.getType() and columnMetadata.getDbType() to see what value you get for VARCHAR, then, call columnMetadata.setType("xxx") or columnMetadata.setDbType("xxx") to reset the column type.