Changing the default data type mapping

Overview

There are two data types in the database component schema: the Talend type and the DB type. When retrieving the table schema, there is always a default Talend type to map to the real DB type. Also, there is a default DB type to map to the Talend type on the DB schema, when linking a non-DB component to a DB output component. You can modify the default data type mapping for your project rather than changing it manually every time.

 

Environment

This procedure was written with:

  • Talend Open Studio for Data Integration 6.4.0
  • Sun JDK build 1.8.0_45-b15
  • Windows 7
  • Mysql 5.0.67-community-nt

Talend verified this procedure to be compatible with Talend Studio version 6.3.1 or higher versions.

 

Procedure

A Talend type is an intermediate Java type, mapped for each data type of different databases. These default data type mappings are configured in an XML file. Each kind of database has a separate mapping configuration file. For example, the file mapping_Mysql.xml maps MySQL data types to Talend types.

 

A portion of the mapping for a MySQL database is as follows. The DB type DECIMAL will map to the Talend type Float by default when retrieving the table schema.

 

...
<dbToTalendTypes>
...
<dbType type="DECIMAL">
<talendType type="id_Float" default="true" />
<talendType type="id_Double"/>
<talendType type="id_BigDecimal"/>
</dbType>
...

 

To change the default data type mapping, follow these steps (using a MySQL database as an example):

  • Click Project setting icon in the tools bar to open the Project setting window, then expand General Metadata of TalendType. Open the mapping_Mysql.xml file.

          project_setting_windows.png

  • Click Edit to edit the file. Change the default mapping. In this example, map the DB type DECIMAL to the Talend type BigDecimal by default, as follows:

     

    ...
    ...
    <dbType type="DECIMAL">
    <talendType type="id_Float"/>
    <talendType type="id_Double"/>
    <talendType type="id_BigDecimal" default="true"/>
    </dbType>
    ...
  • Click OK to save your changes.
Version history
Revision #:
4 of 4
Last update:
‎10-12-2017 08:02 PM
Updated by:
 
Labels (1)
Comments
RobTScot

 In my 6.3.1, the mappings have moved:

 

File-->Project Settings-->General-->Metadata of TalendType

Timo

@sparks @RobTScot Thank you both. There are still heroes out there Smiley Happy

PaeTron

Hi,
i found my mappingXXX.xml and was able to understand and change default-mappings. I was wondering about the other types. In the example above DECIMAL is mapped in 3 Types. Default is taken in talend automatically, but how/where can i chose the other mappings?


A portion of the mapping for a MySQL database is as follows. The DB type DECIMAL will map to the Talend type Float by default when retrieving the table schema.

...
<dbToTalendTypes>
...
<dbType type="DECIMAL">
<talendType type="id_Float" default="true" />
<talendType type="id_Double"/>
<talendType type="id_BigDecimal"/>
</dbType>
..

What are id_Double and id_BigDecimal in the defaultmapping for?

Is there any possibility to set 2 default type with a condition?

My Problem is:

On my Oracle-DB i got all Numbers defined as NUMBER(XX,Y). When i get the schema i'd like to map all Y=0 to LONG and all floating numbers to BIGDECIMAL

e.g.:

15.0 = LONG

15.1 = BIGDECIMAL

 

shong

Hello Paetron

Both id_Float, id_Double, id_BigDecimal are supported talendtype to map dbType DECIMAL, you can set one of them to the default type when retrieving schema.

<dbType type="DECIMAL">
<talendType type="id_Float" />
<talendType type="id_Double" default="true" />
<talendType type="id_BigDecimal"/>
</dbType>

It is impossible to set multiple default mapping type, you can set only one.

 

Regards

Shong