topic Re: Issue with tAzureSynapseOutput and tDBOutput in Talend Studio

Issue with tAzureSynapseOutput and tDBOutput

vbadri — Sat, 16 Nov 2024 00:09:26 GMT

Hello there,

I'm trying to load some data, using Dynamic column type to synapse tables.

Tried both 'tAzureSynapseOutput' and 'tDBOutput' components and both are performing very slow INSERT only operations, doing around 4.2 rows/sec.

Also seeing an error saying that '

tAzureSynapseOutput' doesn't support 'Dynamic Type' but the documentation (

tAzureSynapseOutput Standard properties • Azure SQL Data Warehouse • Reader • Welcome to Talend Help Center) says that it does support

"The dynamic schema feature can be used in the following modes:

Insert , Update, Insert or update , Update or insert , and Delete.".

Does it mean 'Dynamic' data type for the schema?

Thanks & Kind Regards

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Mon, 17 May 2021 09:57:47 GMT

Hello,

tAzureSynapseOutput component doesn't support dynamic type

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

vbadri — Tue, 18 May 2021 15:19:49 GMT

Thanks for your reply.

I understand that it doesn't support Dynamic type, but from the documentation as quoted in my post, what does it mean when it says :

"The dynamic schema feature can be used in the following modes:

Insert , Update, Insert or update , Update or insert , and Delete.".

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Wed, 19 May 2021 07:24:50 GMT

Hello,

Dynamic Schema is an advanced feature in Talend subscription products. It allows you to design Jobs without having to know the structure (names, types, and number of columns) of the data to be handled. You can find more information about this feature at How to process changing data structure?

However, not every component supports the dynamic schema feature.

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

vbadri — Wed, 19 May 2021 15:02:28 GMT

Thanks for your response, I understand the concept of Dynamic Schema with in Talend (been using since 3-4 years).

Currently trying to use the same in one of my projects (using the Subscription Products), how ever, unable to use the same feature with Synapse components as I mentioned above in my original post.

I understand you are saying that it doesn't support dynamic, but the documentation for 'tAzureSynapseOutput' says it does support it, can you confirm if the documentation is wrong?

https://help.talend.com/r/fE4RCKUei9Dgtwy6fHDVhg/zn_gfUge39FLoHuWwxzBRQ

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Thu, 20 May 2021 03:45:30 GMT

Hello,

In the component reference, 'tAzureSynapseOutput' mainly says "Action on data" setting and a note that "The dynamic schema feature can be used in the following modes: Insert, Update, Insert or update, Update or insert, and Delete." with this setting.

It doesn't indicate this component tAzureSynapseOutput does support dynamic schema.

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

vbadri — Thu, 20 May 2021 08:17:42 GMT

Thanks for the response.

Sure thing.

"It doesn't indicate this component tAzureSynapseOutput does support dynamic schema."

I understand you are saying it doesn't indicate the support for Dynamic Schema.

"The dynamic schema feature can be used in the following modes: Insert, Update, Insert or update, Update or insert, and Delete."

What does the above statement actually mean, can you please explain, thanks.

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Thu, 20 May 2021 08:47:10 GMT

Hello,

It should be a general note that "The dynamic schema feature can be used in the following modes: Insert, Update, Insert or update, Update or insert, and Delete." on the condition that you are using components that does support dynamic schema feature.

You could skip this section if the current component doesn't support for dynamic schema.

Feel free let us know if it is OK with you.

Best regards

Sabrina

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

vbadri — Thu, 20 May 2021 09:51:15 GMT

Thanks for clarifying 🙂

Kind Regards.

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Fri, 21 May 2021 07:42:39 GMT

Hello,

Feel free to let us know if there is any further help we can give.

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

vbadri — Mon, 24 May 2021 16:00:31 GMT

I forgot to highlight another issue here.

I've tried the very same components by adding a schema (instead of Dynamic) and the speeds are relatively same.

The operation is just INSERT only, I assume just INSERT (like TRUNCATE and LOAD) should be relatively very fast compared to the 'Update, Insert or update, Update or insert, and Delete'

Can you please take a look at this issue?

Thanks.

Re: Issue with tAzureSynapseOutput and tDBOutput

COW_WW — Tue, 26 Apr 2022 21:03:37 GMT

Hello,

I've got similar issue.

I'm using the tAzureSynapseOutput for Insert with Built-In schema and it works extremely slow. Loading 8,760 records takes about 10 minutes which doesn't make sense.

I tried to replace the tAzureSynapseOutput with tFileOutputDelimited just to test the job itself and the load completed in about 2 sec.

Is there any solution for this?

Thanks

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Wed, 27 Apr 2022 03:03:44 GMT

Hello,

Could please clarify in which Talend version/edition you are?

When you want to load large, we suggest you use tAzureSynapseBulkExec component.

It supports 2 ways to load large data into Azure synapse:

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

COW_WW — Wed, 27 Apr 2022 14:35:38 GMT

I'm using 8.0.1 version. Build: R2022-03

The load data size is very small - 8,760 records (about 1.5MB).

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Thu, 28 Apr 2022 08:57:22 GMT

Hello,

Could you please check box "Die on error" option in tAzureSynapseOutput component to see if there is any more error logged in console?

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

COW_WW — Thu, 28 Apr 2022 14:15:24 GMT

The "Die on error" option was on from the beginning.

Re: Issue with tAzureSynapseOutput and tDBOutput

Anonymous — Fri, 29 Apr 2022 02:43:31 GMT

Hello,

Would you mind posting your job design screenshots here which will be helpful for us to address your issue. Please mask your sensitive data.

Best regards

Sabrina

Re: Issue with tAzureSynapseOutput and tDBOutput

COW_WW — Fri, 29 Apr 2022 17:05:39 GMT

I also tried to load the data directly from the parquet file which is located on ADLS Gen2 and the load speed was still extremely slow: