Using DB loader node with Amazon redshift DB

Hi,
I would like to load data from a KNIME table (or csv file) to a Amazon redshift DB table. As DB insert node performance is very slow, I’m trying to use the DB loader node together with a S3 bucket.

I have an amazon S3 bucket setup, and have an IAM username and password setup for the account. (Earlier we assigned IAM role to the EC2 instance KNIME server runs on but it looks like a username/ password or access key id/ secret key is required to connect using KNIME’s Amazon authentication node)
However I’m facing authentication issues when trying to connect from KNIME to amazon S3 bucket.

I’m using Amazon authentication node following by Amazon S3 Connector and DB loader nodes.
From KNIME documentation, it is not clear to me now to configure the Amazon authentication node as ‘credentials’ option is greyed out. Further the DB loader node also has ‘Authentication Parameters’ field. so not clear how this interacts with Amazon authentication node parameters.

Its my first post here. Appreciate any help on this. Please let me know if any further details are required.

Thanks,
Murali

Hello @mkrishnan ,
this parameter refers to the Authorization parameter of the COPY command that the node uses. It needs needs authorization to access data in Amazon S3 e.g.

  • IAM_ROLE ‘arn:aws:iam::<aws-account-id>:role/<role-name>’
  • ACCESS_KEY_ID ‘<access-key-id>’
  • SECRET_ACCESS_KEY ‘<secret-access-key>’
  • SESSION_TOKEN ‘<temporary-token>’

For more details see the parameter documentation in the Amazon documentation.
Bye
Tobias

1 Like

Thank you for the quick reply, Tobias.

If the credentials are the same as the one I’ve used for Amazon Authentication node, should I repeat them here?

As the authorization parameter in DB loader is a single parameter, when using access key id and secret access key, what is the format expected?
should I enter “ACCESS_KEY_ID ‘’ SECRET_ACCESS_KEY ‘’” in the single line, or is a separator like ‘;’ required between the access key id and secret access key?

Thanks,
Murali