One Star kcd
One Star

Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Hello
Anyone got ETL playing nicely with GUIDs? Out of the box it detects the schema as 36 length varbinaries rather than 16 bit byte arrays, or worse booleans!
I tried changing the mappings (Preferences > Talend > Specific Settings > Metadata of TalendType) but it doesn't seem to help. I adjusted the Java section of MSSQL mapping to:
				<dbType type="UNIQUEIDENTIFIER">
<talendType type="byte[]" default="true" />

But when I retrived the schema I still get a db type VARBINARY and type Object length 36

Handling GUIDs in MySQL is even worse. For Mysql I resorted to doing a find and replace in the *.item file because I couldn't find a better solution.
FIND AND REPLACE
length="16" sourceType="BIT" talendType="id_Boolean"
WITH
length="16" sourceType="BINARY" talendType="id_byte[]"

Surely there are other people who have overcome this problem
Cheers
Kevin
8 REPLIES
Employee

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Hi Kevin,
If you changed the data type matching file, it should have worked. Have you restarted the Studio after doing that?
One Star kcd
One Star

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Thanks Vincent. Yes I have restarted but I think the problem that it is mapping VARBINARY not UNIQUEIDENTIFIER. Same with MySQL using BIT.
One Star kcd
One Star

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

So why does ETL get the schema wrong for connection metadata (using Retrieve Schema) and why can we not fix the 'DB Type' without hacking the .item file?
Should I start a new post?
One Star kcd
One Star

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Turns out we can fix it, as of version 5.3 you must do so yourself:
Window > Preferences >Specific Settings > Metadata of Talend and edit mapping_Mysql.xml
<language name="java">
<talendToDbTypes><!-- Adviced mappings -->
...
<talendType type="id_byte[]">
<dbType type="BINARY" default="true"/>
<dbToTalendTypes>
...
<dbType type="BINARY">
<talendType type="id_byte[]" default="true" />
Moderator

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Hi kcd,
Thanks for your feedback and sharing your solution with us.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Hello Expert,
Any one can elaborate on this.
i am reading data from delimited file and i want to insert data into MSSql table.
Table structure already their in SQL server db i want to load data into existing table for data type uniqueidentifier 
how to convert int to uniqueidentifier  in tmap or MSSqloutput.
I tried with int,object but it won't work, any help would be much appreciated.
 
Four Stars

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Hi,
Any one can elaborate on this.
i am reading data from mssql database table it's has UniqiueIdentifier field,and i want to insert data into MSSql table.
how to convert int to uniqueidentifier  in tmap or MSSqloutput.
I tried with int,object but it won't work, any help would be much appreciated.
Four Stars

Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Hi,
read it as Object from source keep object data type in tmap.
You have to change in mssqloutput --> edits schema DB type="UNIQUEIDENTIFIER"
let me know for further help.