topic Re: Improving the data load speed to Amazon redshift database in Talend Studio

Improving the data load speed to Amazon redshift database

sushantk19 — Mon, 05 Oct 2020 11:58:12 GMT

hello Team,

I have a daily job which loads ~6 million data daily(truncate reload). My target database if Amazon redshift. I have used tRedshift_output as the output component. presently the jobs takes somewhere between 4 to 5 hrs to complete. Is there a way to improve the performance of this job? Can i use any other component to improve the performance. My source is a little complex SQL ( 4 full outer join statements)

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Mon, 05 Oct 2020 13:53:28 GMT

Hi sushantk19,

you will get best performance by using COPY command in tDBRow component. You can write Truncate table and copy command in one begin end transaction block. One condition - your source data have to be on S3.

Regards

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Mon, 05 Oct 2020 14:35:29 GMT

@Kamil Kowalczyk : i used the tRedshiftoutputBulk_Exec component. But i get the below error:

Exception in component tDBOutputBulkExec_1_tROB (job_DM_002_DIM_Subscription_tmp1_history)

com.amazonaws.services.s3.model.AmazonS3Exception: The authorization header is malformed; a non-empty Access Key (AKID) must be provided in the credential. (Service: Amazon S3; Status Code: 400; Error Code:

I am new to talend. Can you tell me how to configure the S3 folder in AWS? i guess if i use the above component then it will first copy the content to S3 folder and then write to the target table..isnt it?

Also, can you suggest the steps to setup S3? i have attached the screenshot of the tDBOutputBulkExec component.

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Mon, 05 Oct 2020 16:36:41 GMT

If you want to use s3 you have to know credentials, acces key and Secret key.

Amazon has a really great documentation. Please check this link:

https://docs.aws.amazon.com/redshift/latest/dg/tutorial-loading-data.html

I think you will find there everything you need.

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Tue, 06 Oct 2020 12:58:43 GMT

@Kamil Kowalczyk : have you implemented this before? can you share screenshot of your S3 settings.

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Tue, 06 Oct 2020 13:39:42 GMT

What exactly you want to know? Sample screenshot:

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Wed, 07 Oct 2020 12:38:31 GMT

@Kamil Kowalczyk : i provided access key, bucket & secret key.

Key i left it as blank. Region I choose DEFAULT.

i get the below error:

Exception in component tDBOutputBulkExec_1_tROB (job_DM_003_DIM_Subscription_tmp1_history)

com.amazonaws.SdkClientException: Unable to execute HTTP request: Connection reset by peer: socket write error

what could be cause of this?

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Thu, 08 Oct 2020 12:22:22 GMT

Hello All,

Any inputs to my above issue? is it mandatory to provide key value??

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Fri, 09 Oct 2020 07:44:32 GMT

Looks like some firewall blocking your traffic or maybe you don't have permissions to your s3 bucket.

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Fri, 09 Oct 2020 15:12:32 GMT

S3 Access issue has been resolved now . but i get a new error now:

Exception in component tDBOutputBulkExec_1_tRBE (job_DM_003_DIM_Subscription_tmp1_history)

java.sql.SQLException: [Amazon](500310) Invalid operation: Load into table 'payments_orders_subscriptions' failed. Check 'stl_load_errors' system table for details

i checked for the ETL and it is the same as the regular flow without bulkexec component. i checked the stl_load_errors also: Error is attached.

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Sun, 11 Oct 2020 09:38:53 GMT

Have you read this error? It's quite obvious reason. There is no date like 2018-53-06. Please fix your source data, or change data type to string to load this data

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Mon, 12 Oct 2020 12:12:02 GMT

@Kamil Kowalczyk : you are right. I have fixed the source data. the issue was with date fields .I changed to string and then the job worked fine. ALso, the Secret key was changed and now the job is working fine. The only issue/query that i have now is it takes ~ 65 mins to commit 5 million records( it reads the data in 12 to 13 mins), but takes a very long time to commit this transaction and insert into my Target table. IS this Normal acceptable behavior or i can tune this further? if yes, then how? can you pls suggest

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Tue, 13 Oct 2020 10:25:38 GMT

The only issue/query that i have now is it takes ~ 65 mins to commit 5 million records( it reads the data in 12 to 13 mins), but takes a very long time to commit this transaction and insert into my Target table. IS this Normal acceptable behavior or i can tune this further? if yes, then how? can you pls suggest

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Tue, 13 Oct 2020 19:04:54 GMT

I think this article can help: https://docs.aws.amazon.com/redshift/latest/dg/c_loading-data-best-practices.html

tRedshiftOutputBulkExec has some stepst inside:

* building local file from input flow

* putting this file in S3

* loading data from s3 to Redshift.

Maybe if you will use diffrent components to do above steps, you will locate which step has poor performance, and focus on it.

What is your source? Redshift, or another DB? If redshift, you can use UNLOAD with Parallel and GZIP to imrpvoe loading data.

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Wed, 14 Oct 2020 07:31:52 GMT

@Kamil Kowalczyk : thanks for your valuable feedback. Yes, source is Redshift only. I will try other components also and see if that helps to improve the timings. I know where the bottlenect is- it is while writing the data from S3 to Redshift table.

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Wed, 14 Oct 2020 07:53:55 GMT

Hi sushantk19 (Customer),

that's great:) So try to do unload with parallel and gzip, then do copy with gzip parameter and let me know if it helep 🙂

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Mon, 26 Oct 2020 09:36:44 GMT

@Kamil Kowalczyk : I used to below 2 components instead of bulkexec:

1. Bulkoutput load

2.tRedshiftunload.

The time reduces a bit. but still it is more than 60 mins for 6 million records. Can i improve this further by any other means? i see an option for parallel execution disabled. Will it help to enable that?

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Tue, 27 Oct 2020 12:48:54 GMT

@Kamil Kowalczyk : My unload process is slow( and not copy from S3 to Redshift DB). It takes a very long time to write the data to S3 which is output of complex query( 4 full outer joins). Can you suggest how can i make it faster? presently it takes around 70 mins for this process.

Re: Improving the data load speed to Amazon redshift database

sushantk19 — Tue, 27 Oct 2020 12:49:16 GMT

Hello Friends,

My unload process is slow( and not copy from S3 to Redshift DB). It takes a very long time to write the data to S3 which is output of complex query( 4 full outer joins). Can you suggest how can i make it faster? presently it takes around 70 mins for this process.

Re: Improving the data load speed to Amazon redshift database

pakapi_ — Tue, 27 Oct 2020 14:11:41 GMT

Hi sushantk19,

do you have ENABLED PARALLEL unload with GZIP option ? It improves unloads a lot.