topic Re: Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL in Talend Studio

Uniqueidentifier (GUID/UUID) handling from MSSQL and MySQL

Anonymous — Sat, 16 Nov 2024 12:41:34 GMT

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

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

Anonymous — Mon, 12 Sep 2011 03:28:04 GMT

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

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

Anonymous — Mon, 12 Sep 2011 03:52:57 GMT

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

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

Anonymous — Wed, 14 Sep 2011 23:28:13 GMT

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?

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

Anonymous — Fri, 05 Jul 2013 03:48:47 GMT

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" />

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

Anonymous — Fri, 05 Jul 2013 04:46:54 GMT

Hi kcd,
Thanks for your feedback and sharing your solution with us.
Best regards
Sabrina

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

Anonymous — Mon, 09 Nov 2015 15:15:49 GMT

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.

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

sasi8008 — Wed, 16 Dec 2015 04:12:32 GMT

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.

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

Anonymous — Wed, 16 Dec 2015 07:13:29 GMT

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.