Error on tDBOutput using dynamic schema when there is a VARBINERY data type
Hi,
My source and target are both jdbc mssqlserver. I'm using dynamic schema to load a table contains two columns in VARBINARY. I'm getting errors on the tDbOutput component saying: java.lang.UnsupportedOperationException: Unrecognized type id_Object
In the tDBInput component is's uing the default MSSQL mapping file for Dynamic with below setting:
I have seen a similar issue before but not the same case as the source is MSSql server and target is snowflake.
Please see the following details
Exception in component tSnowflakeOutput_1 (testJob)
java.lang.UnsupportedOperationException: Unrecognized type id_Object
at org.talend.daikon.di.DiIncomingSchemaEnforcer.diToAvro(DiIncomingSchemaEnforcer.java:199)
at org.talend.daikon.di.DiIncomingSchemaEnforcer.addDynamicField(DiIncomingSchemaEnforcer.java:149)
at local_project651.testjob_0_1.testJob.tMSSqlInput_1Process(testJob.java:1220)
at local_project651.testjob_0_1.testJob.runJobInTOS(testJob.java:2728)
at local_project651.testjob_0_1.testJob.main(testJob.java:2541)
Root Cause
The above error was due to the dbType in the mapping files are not compatible. For example of the test job for the error above; the job retrieve data from the table name account1 in Microsoft Server database then insert data into SnowFlake database. Both tables in both database has the same columns and the same datatype (note: datatype is not the same as dbType) in the schema.
When customer used the dynamic schema in their job to retrieve data from the dabase (ex: SQL Server in this case) then insert data into SnowFlake with dynamic schema. The dbType of input columns in the job (ex: tMSSqlInput) will be rely on the setting in the Talend Type Mapping Files. For this case, it is the mapping_MSSQL.xml. For the dbType of Money in the mapping_MSSQL.xml file for acct_balance column; the setting is only has
<dbType type="MONEY">
<talendType type="id_Object" default="true" />
</dbType>
But in the mapping_Snowflake.xml, there is no dbType of MONEY. It only has the type (which is datatype) of Double:
<dbType type="DOUBLE">
<talendType type="id_Double" default="true" />
</dbType>
Therefore, when it passes data from the tMSSqlInput component to tSnowflakeOutput component, it is error out in the tSnowflakeOutput component for not recognize the id_Object.
Solution
The easy way to fix this error is, we need to change the dbType from id_Object to id_Double for the MONEY in the mapping_MSSQL.xml file. So, it is compatible to the mapping_Snowflake.xml. To do this by follow below steps:
Open the studio
Navigate to File
Click on Edit Project properties
Expand the General
Click on the Metadata of TalendType
Scroll down then click on the mapping_MSSQL.xml and then click Edit
Scroll down the mapping_MSSQL.xml file to the tag <dbType type="MONEY">
<dbType type="MONEY">
<talendType type="id_Object" default="true" />
</dbType>
Then change from <talendType type="id_Object" default="true" /> in the tag <dbType type="MONEY"> to <talendType type="id_Double" default="true" />
Example:
From:
<dbType type="MONEY">
<talendType type="id_Object" default="true" />
</dbType>
To:
<dbType type="MONEY">
<talendType type="id_Double" default="true" />
</dbType>
After, made the above change then ran the job; the job should works.
Or maybe you could choose just the columns which are not causing the problem if you have a mass volume of tables and data, the tables which probably causes the problem.
Thanks and I understand this issue but my case is SQL Server to SQL Server. Correct me if I'm wrong, it should use the same mapping_MSSQL.xml file for both input and output. In my current file, it is :
<dbType type="VARBINARY">
<talendType type="id_Object" default="true" />
</dbType>
and I don't understand why I'm getting Unrecognized type id_Object.
Could you please also raise a case with Talend Support, this issue should be reproducable by support with remote assistance and if that's the case then R&D can be involved easily.
