topic Re: Dynamic Data Type in Talend Studio

Dynamic Data Type

Anonymous — Sat, 16 Nov 2024 02:24:31 GMT

Hello,

Trying to bring in data from MS SQL Server into MySQL using Dynamic Datatype. Lot of fields are ntext field on source tables.

1) What could be the equivalent data type in MySQL? Will LONGTEXT help?

2) How to use this with Dynamic? (I prefer Dropping and Creating new tables every time I run the job. This way, the job handles the metadata of source and target tables instead of us having to maintain the target tables.)

MS SQL Server -> select * from sourcetable -> MySQL -> Target table

Thanks,

Sachin

Re: Dynamic Data Type

manodwhb — Tue, 19 May 2020 04:37:45 GMT

@sachinattalend, you need to use it as a string data type in Talend.

Re: Dynamic Data Type

Anonymous — Tue, 09 Jun 2020 02:51:09 GMT

Talend could not make ntext of MS SQL Server to automatically convert to its corresponding datatype in MySQL.

This is what we did:

- Identified all ntext columns in MSSQL (backup database) and convert to text data types

- Ran jobs using Dynamic.

Re: Dynamic Data Type

JohnRMK — Tue, 09 Jun 2020 12:11:50 GMT

Hello,
Unfortunately the dynamic schema has some limitations including this one and the nvarchar (max) for example in sql server.

To help you move forward, here is an approach that I have used in several projects and that works perfectly.

-You must create the target tables before retrieving the data with the dynamic schema ==> You can use this script to do the same work https: // dataedo .com / kb / query / sql-server / list-columns-names-in-specific-table (execute it in a tDBInput connected to the source DB)

-You must write a simple java routine, which takes as a parameter a column name, size, type ... and returns a character string formatted in mysql

==> sqlToMysqlFormat (String name, String Type, Int precision, Int length, boolean isNull)
Ex: sqlToMysqlFormat (FirstName, nvarchar, 0, 50, false)
Must return "FirstName CHAR (50) NOT NULL"

The example routine below allows me to convert Salesforce fields to SQL Server. You have to do the same thing nothing complicated. (http://www.sqlines.com/sql-server-to-mysql)

This routines must be applied to each attribute (in tMap after retriveing all columns list ) and then you group in a list separated by ',' (tAggregatRow group by table name and ) to get a table creation query that you will inject into a tDBRow (mysql) just before recovering data with the dynamic schema.

After this you will have something like query="CREATE TABLE tableName (Id int .. , ....)"

You can delete the table if it already exists.

Tell me if you want more detail, or if you didn't understand a point

Re: Dynamic Data Type

Anonymous — Tue, 23 Jun 2020 19:13:22 GMT

Hello @JohnRMK

Thanks , It's really very helpful for me . I am new to talend.

could you please let us know what are the column you used in tmap and what you applied.

Re: Dynamic Data Type

JohnRMK — Wed, 24 Jun 2020 17:08:13 GMT

Hello,

input:

-TableName

-ColumnName

-ColumnSize

-ColumnType

...

Output

-TableName

-ListFieldsForScript<==> TalendRoutinesFormatSqlToMySQL(ColumnName,ColumnSize,...)

Basically, to summarize, it takes as a parameter the name of the table and all the columns and it returns the table name and a request formatted using the routine.

After tAggregatRow, You add CREATE TABLE TableName ( ListFieldsForScript );

Then you inject that into your db with tDBRows