topic Dynamic schema : no primary key ? in Talend Studio

Dynamic schema : no primary key ?

François_T — Mon, 26 Feb 2018 14:22:11 GMT

Hello !

I would like to transfer the content of a table into another database, while keeping the exact same structure.

I used the MSSQL Input & output components, with a dynamic schema, to allow easy modifications of the source table.

However, if I do that, I can't have a primary key, the new table is created without one, which seems quite weird. Is there any way to change that ? Or should I use a more rigid schema with a primary key ? Thanks if you have any comment !

Re: Dynamic schema : no primary key ?

Anonymous — Mon, 26 Feb 2018 14:44:34 GMT

Instead of outputting your data to a single Dynamic column, output your primary key column AND a Dynamic schema column. I am assuming that your PK will be called something consistently across your tables ("id" for example), but this might help. The Dynamic schema is able to work like this.

Re: Dynamic schema : no primary key ?

François_T — Mon, 26 Feb 2018 16:25:13 GMT

Oh ok, I didn't know that. So I can mix some fixed columns (the one(s) used for the primary key), and the dynamic column type for everything else, if I understand this right. Very interesting, thanks for this feedback ! I will try this solution.

Re: Dynamic schema : no primary key ?

Anonymous — Mon, 26 Feb 2018 16:37:51 GMT

I've not tested this exhaustively, but I do know you can separate known columns out.

Re: Dynamic schema : no primary key ?

François_T — Tue, 27 Feb 2018 08:31:25 GMT

I tried, but I'm getting errors :

I have change my schema to

Field1

Field 2

Dynamic field

(Field 1 & 2 being the primary key)

Then my SQL query is "SELECT Field1, Field2, * FROM table"

But then it gives me field format errors, saying that it can convert a value to an integer.

I guess I should spent a bit more time to list the useful columns and add the new ones later manually. Too bad I can't easily import the full table with the primary key using Talend !

Re: Dynamic schema : no primary key ?

Anonymous — Tue, 27 Feb 2018 09:20:16 GMT

Lets say your table looks like this.....

CREATE TABLE [dbo].[Batch](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[catalogue_id] [bigint] NULL,
	[datasource] [varchar](max) NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NULL,
	[status_id] [bigint] NULL,

 CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
)

In this case your schema would need to be ...

id = Long

row = Dynamic

Your query could be...

Select *
From Batch

This has worked for me.

Re: Dynamic schema : no primary key ?

François_T — Tue, 27 Feb 2018 09:39:49 GMT

I think it works because your primary key is the first one in the table.

I tried what you proposed, to simply use a select *, with the schema you gave. But it's not working, as it's expecting the primary key fields to be the first in the query. Unfortunately, in my case, they are not, and it's often like that. So I think it's unfortunately more complicated for me. Unless I'm missing an option ?

Re: Dynamic schema : no primary key ?

Anonymous — Tue, 27 Feb 2018 09:41:37 GMT

Have you used your primary field names or just used random names? But you are correct, my pks are always at the start of the table.

Re: Dynamic schema : no primary key ?

François_T — Tue, 27 Feb 2018 09:50:57 GMT

In my tests, I used the primary key field names yes, and I immediately got error. I thought that maybe Talend would not mind receiving all the columns in a different order. But apparently it does, so unless I have my primary key field(s) at the beginning of my tables, the dynamic solution seems to be impossible unfortunately. Not a huge deal, and at least I know better how it works now ! Thanks for the time spent thinking about it.