Accessing mapping files and defining type mappings

Procedure

On the toolbar of the Studio main window, click or click File > Edit Project Properties from the menu bar to open the Project Settings dialog box.

In the tree view of the dialog box, expand General and select Metadata of Talend Type to open the Metadata of TalendType view, which lists the mapping files for all the database types used in Talend Studio.

You can import, export, or delete any of the conversion files by clicking Import, Export or Remove respectively.

You can modify a conversion file according to your needs by double-clicking the file or selecting the file and clicking the Edit button to open the Edit Mapping File dialog box and then modify the XML code directly in the open dialog box.

When you define a type mapping, you need to map both from Talend type to database type and from database type to Talend type.

The <dbTypes> element with its child <dbType> elements defines the supported database types. To add a new database type in the mapping file, you need to add a <dbType> element under the <dbTypes> element. The example below adds two database types BOOLEAN and YESNO.
```
<dbType type="BOOLEAN"/>
<dbType type="YESNO"/>
```
If you have installed the R2020-08 Studio monthly update or a later one provided by Talend, you can set the default pattern for each date type. This allows the date pattern for the date type columns to be set automatically when retrieving or guessing schema from a table. The following example adds two database types DATE and DATETIME.
```
<dbType type="DATE" defaultPattern="yyyy-mm-dd"/>
<dbType type="DATETIME" defaultPattern="yyyy-mm-dd hh:mm:ss.SSSSSS"/>
```
The <talendToDbTypes> element with its child <talendType> elements defines a suggested database type list and the default database type when setting a Talend type for a metadata column. To map a Talend type to one or more database types, you need to add a talendType element under the <talendToDbTypes> element. The example below maps the Talend type id_Boolean to two database types BOOLEAN and YESNO.
```
<talendType type="id_Boolean">	
	<dbType type="BOOLEAN"/>
	<dbType type="YESNO"/>
</talendType>
```
The <dbToTalendTypes> element with its child <dbType> elements defines a suggested Talend type list and the default Talend type when retrieving schema from the database. To map a database type to one or more Talend types, you need to add a dbType element under the <dbToTalendTypes> element. The example below maps the database type YESNO to the Talend type id_Boolean.
```
<dbType type="YESNO">
	<talendType type="id_Boolean"/>
</dbType>
```

Note: The default, defaultLength, defaultPrecision, ignoreLen, ignorePre, and preBeforelen attributes in Talend type mapping files are not taken into account. You do not need to add these attributes when defining new type mappings.

Below is the XML metadata mapping file for the Access database:

<?xml version="1.0"?>
<mapping>
    <dbms product="ACCESS" id="access_id" label="Mapping Access" default="true">
        <dbTypes>
            <dbType type="BIT" ignoreLen="true" ignorePre="true"/>
            <dbType type="BOOLEAN" ignoreLen="true" ignorePre="true"/>
            <dbType type="COUNTER"/>
            <dbType type="DATE" ignoreLen="true" ignorePre="true"/>
            <dbType type="DOUBLE" ignoreLen="true" ignorePre="true"/>
            <dbType type="DECIMAL" ignoreLen="true" ignorePre="true"/>
            <dbType type="FLOAT" ignoreLen="true" ignorePre="true"/>
            <dbType type="INTEGER" ignoreLen="true" ignorePre="true"/>
            <dbType type="NUMERIC" ignoreLen="true" ignorePre="true"/>
            <dbType type="REAL" ignoreLen="true" ignorePre="true"/>
            <dbType type="SMALLINT" ignoreLen="true" ignorePre="true"/>
            <dbType type="TINYINT" ignoreLen="true" ignorePre="true"/>
            <dbType type="TIME" ignoreLen="true" ignorePre="true"/>
            <dbType type="TIMESTAMP" ignoreLen="true" ignorePre="true"/>
            <dbType type="VARCHAR" default="true" defaultLength="200" ignorePre="true"/>
            <dbType type="DATETIME" ignoreLen="true" ignorePre="true"/>
            <dbType type="MEMO" ignoreLen="true" ignorePre="true"/>
            <dbType type="YESNO" ignoreLen="true" ignorePre="true"/>
        </dbTypes>

        <language name="java">
            <talendToDbTypes>
                <!-- Adviced mappings  -->
                <talendType type="id_List"/>
                <talendType type="id_Boolean">
                    <dbType type="YESNO" default="true"/>
                    <dbType type="BOOLEAN"/>
                </talendType>
                <talendType type="id_Byte">
                    <dbType type="TINYINT" default="true"/>
                    <dbType type="SMALLINT"/>
                    <dbType type="INTEGER"/>
                </talendType>
                <talendType type="id_byte[]"> </talendType>
                <talendType type="id_Character">
                    <dbType type="VARCHAR" default="true"/>
                </talendType>
                <talendType type="id_Date">
                    <dbType type="DATE" default="true"/>
                    <dbType type="TIMESTAMP"/>
                    <dbType type="TIME"/>
                    <dbType type="DATETIME"/>
                </talendType>
                <talendType type="id_BigDecimal">
                    <dbType type="NUMERIC" default="true"/>
                    <dbType type="DOUBLE"/>
                    <dbType type="FLOAT"/>
                    <dbType type="DECIMAL"/>
                    <dbType type="REAL"/>
                </talendType>
                <talendType type="id_Double">
                    <dbType type="DOUBLE" default="true"/>
                    <dbType type="NUMERIC"/>
                    <dbType type="FLOAT"/>
                    <dbType type="DECIMAL"/>
                    <dbType type="REAL"/>
                </talendType>
                <talendType type="id_Float">
                    <dbType type="FLOAT" default="true"/>
                    <dbType type="DOUBLE"/>
                    <dbType type="NUMERIC"/>
                    <dbType type="DECIMAL"/>
                    <dbType type="REAL"/>
                </talendType>
                <talendType type="id_Integer">
                    <dbType type="INTEGER" default="true"/>
                    <dbType type="SMALLINT"/>
                    <dbType type="TINYINT"/>
                    <dbType type="COUNTER"/>
                </talendType>
                <talendType type="id_Long">
                    <dbType type="INTEGER" default="true"/>
                    <dbType type="SMALLINT"/>
                    <dbType type="TINYINT"/>
                    <dbType type="COUNTER"/>
                </talendType>
                <talendType type="id_Object"> </talendType>
                <talendType type="id_Short">
                    <dbType type="SMALLINT" default="true"/>
                    <dbType type="INTEGER"/>
                    <dbType type="TINYINT"/>
                    <dbType type="COUNTER"/>
                </talendType>
                <talendType type="id_String">
                    <dbType type="VARCHAR" default="true"/>
                    <dbType type="MEMO"/>
                </talendType>
            </talendToDbTypes>
            <dbToTalendTypes>
                <dbType type="BIT">
                    <talendType type="id_Boolean" default="true"/>
                </dbType>
                <dbType type="BOOLEAN">
                    <talendType type="id_Boolean" default="true"/>
                </dbType>
                <dbType type="COUNTER">
                    <talendType type="id_Integer" default="true"/>
                </dbType>
                <dbType type="DATE">
                    <talendType type="id_Date" default="true"/>
                </dbType>
                <dbType type="DECIMAL">
                    <talendType type="id_Double"/>
                    <talendType type="id_BigDecimal" default="true"/>
                    <talendType type="id_Float"/>
                </dbType>
                <dbType type="DOUBLE">
                    <talendType type="id_Double" default="true"/>
                    <talendType type="id_BigDecimal"/>
                    <talendType type="id_Float"/>
                </dbType>
                <dbType type="FLOAT">
                    <talendType type="id_Float" default="true"/>
                    <talendType type="id_BigDecimal"/>
                    <talendType type="id_Double"/>
                </dbType>
                <dbType type="INTEGER">
                    <talendType type="id_Integer" default="true"/>
                    <talendType type="id_Short"/>
                    <talendType type="id_Long"/>
                    <talendType type="id_Byte"/>
                </dbType>
                <dbType type="NUMERIC">
                    <talendType type="id_Float"/>
                    <talendType type="id_BigDecimal" default="true"/>
                    <talendType type="id_Double"/>
                </dbType>
                <dbType type="REAL">
                    <talendType type="id_Float" default="true"/>
                    <talendType type="id_BigDecimal"/>
                    <talendType type="id_Double"/>
                </dbType>
                <dbType type="SMALLINT">
                    <talendType type="id_Short" default="true"/>
                    <talendType type="id_Integer"/>
                    <talendType type="id_Long"/>
                    <talendType type="id_Byte"/>
                </dbType>
                <dbType type="TINYINT">
                    <talendType type="id_Byte" default="true"/>
                    <talendType type="id_Integer"/>
                    <talendType type="id_Short"/>
                    <talendType type="id_Long"/>
                </dbType>
                <dbType type="TIME">
                    <talendType type="id_Date" default="true"/>
                </dbType>
                <dbType type="TIMESTAMP">
                    <talendType type="id_Date" default="true"/>
                </dbType>
                <dbType type="VARCHAR">
                    <talendType type="id_String" default="true"/>
                </dbType>
                <dbType type="DATETIME">
                    <talendType type="id_Date" default="true"/>
                </dbType>
                <dbType type="MEMO">
                    <talendType type="id_String" default="true"/>
                </dbType>
                <dbType type="YESNO">
                    <talendType type="id_Boolean" default="true"/>
                </dbType>
            </dbToTalendTypes>
        </language>
    </dbms>
</mapping>

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here