topic Re: how to create dynamic schema in Talend Studio 8 in Talend Studio

how to create dynamic schema in Talend Studio 8

Debasish — Wed, 27 Mar 2024 08:34:16 GMT

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.

Re: how to create dynamic schema in Talend Studio 8

Shicong_Hong — Wed, 27 Mar 2024 15:16:51 GMT

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

Re: how to create dynamic schema in Talend Studio 8

Debasish — Fri, 29 Mar 2024 07:39:43 GMT

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

Re: how to create dynamic schema in Talend Studio 8

Shicong_Hong — Fri, 29 Mar 2024 16:06:36 GMT

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"); ?

Re: how to create dynamic schema in Talend Studio 8

Debasish — Mon, 01 Apr 2024 08:40:39 GMT

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

Re: how to create dynamic schema in Talend Studio 8

Shicong_Hong — Mon, 01 Apr 2024 10:28:52 GMT

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.

Re: how to create dynamic schema in Talend Studio 8

David_Underdown — Wed, 19 Mar 2025 15:40:45 GMT

I was interested in looking at the knowledge base article linked in the solution above, https://community.qlik.com/t5/tkb/workflowpage/tkb-id/qlik-support-knowledge-base/article-id/12118?prePageCrumb=TkbUserContributedArticlesPage but the link seems to be broken. I've been trying to find it in the support knowledge base but it doesn't seem to be possible to search on the article-id. Any ideas what this was supposed to refer to?

Thanks