# mulesoft Amazon Redshift Connector - Full Documentation --- product: Amazon Redshift Connector version: 1.1 --- # Amazon Redshift Connector 1.1 > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) is a bulk-load connector that enables you to connect to Amazon Redshift database to perform inserts, deletes, updates, and to execute SQL queries. For compatibility information and fixed issues, see the Amazon Redshift Connector Release Notes. ## Before You Begin To use this connector, you must be familiar with: - The Amazon Redshift API - Anypoint Connectors - Mule runtime (Mule) - Elements and global elements in a Mule flow - Creating a Mule app using Anypoint Studio or Anypoint Code Builder Before creating an app, you must have: - Credentials to access the Amazon Redshift target resource - Anypoint Platform - Anypoint Studio or Anypoint Code Builder ## Common Use Cases for the Connector Some possible uses for this connector include: - Analytics Amazon Redshift operates as an optimized data warehouse that can store large volumes of data, which can then be analyzed using a business intelligence (BI) tool such as Tableau. - Real-time processing of social media feeds Stream data from your social media feeds, such as a Twitter feed, into Amazon Kinesis Data Streams, and then store that data in a data warehouse, such as Amazon Redshift to perform real-time analytics. ## Authentication Types Amazon Redshift Connector uses the following connection methods: - Basic authentication, which uses **user** and **password** field values to connect to the database - IAM authentication, which uses **access key** and **secret key** field values to retrieve the temporary password for the specified user in order to connect to the database ## See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Use Exchange to Discover Connectors, Templates, and Examples](https://docs.mulesoft.com/connectors/introduction/intro-use-exchange) - [Amazon Redshift Reference](https://docs.mulesoft.com/amazon-redshift-connector/1.1/amazon-redshift-connector-reference) - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.1 --- # Amazon Redshift Connector 1.1 Example > For the full documentation index, see: https://docs.mulesoft.com/llms.txt This example shows you how to use Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) to load data from Amazon S3 to Amazon Redshift. ## Create the Mule Project 1. In Studio, select **File > New > Mule Project**. 2. Enter `redshift-demo` as the name for your Mule project and click **Finish**. 3. Open the `redshift-demo.xml` file in `src/main/mule` folder. 4. In the **Mule Palette** view, click **(X) Search in Exchange**. 5. In **Add Modules to Project**, type `redshift` in the search field. 6. Click **Amazon Redshift Connector** in **Available modules**. 7. Click **Add**. 8. Click **Finish**. 9. Repeat the previous steps to also add the Amazon S3 Connector (search for `amazon s3`) and File Connector (search for `file`) to the Mule project. ## XML Code for This Example Overwrite the contents of `redshift-demo.xml` file with the following XML snippet: ```xml ]]> ``` ## Configure Global Elements 1. Create a file named `mule-artifact.properties` in the `src/main/resources/` folder. 2. Add the following properties in the `mule-artifact.properties` file and assign the correct values: s3.access\_key= s3.secret\_key= s3.region= redshift.cluster\_id= redshift.region= redshift.port= redshift.database= redshift.user= redshift.password= copy.access\_key= copy.secret\_key= *** - You must have Amazon S3 credentials to create and delete buckets and to create an object. - Amazon Redshift credentials are required to establish a connection to the database. - This example is using a `COPY` command to load a table in parallel from a data file on Amazon S3. To use the `COPY` command, you must authenticate using your IAM user credentials with the correct policies attached to it, therefore, it is best to use the Amazon S3 Read only policy for the `copy.access_key` and `copy.secret_key` properties. - Alternatively, it is possible to use an IAM Role instead of the Access and Secret key pair. To do this, you must ensure the role has the correct policies attached to it. It is best to use the Amazon S3 Read only policy for this purpose. - To create an IAM role to allow your Redshift cluster to communicate with the Amazon S3 service on your behalf, follow the steps in this [Amazon Redshift tutorial](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html). 3. Open the Amazon Redshift configuration, scroll down, and configure the JDBC driver in the **Required libraries** section. In this example, choose the **Add recommended library** option. If this doesn’t work, add the Amazon Maven repository to your `pom.xml` file and try again: ```xml ... redshift https://s3.amazonaws.com/redshift-maven-repository/release ``` The following images show the Amazon S3 and Amazon Redshift configurations: ![Amazon S3 configuration](/mulesoft/current/en-us/amazon-redshift-connector/1.1/_images/s3-configuration.png) Figure 1. Amazon S3 Configuration ![Amazon Redshift Basic configuration](/mulesoft/current/en-us/amazon-redshift-connector/1.1/_images/redshift-configuration.png) Figure 2. Amazon Redshift Basic Configuration ![Amazon Redshift IAM configuration](/mulesoft/current/en-us/amazon-redshift-connector/1.1/_images/redshift-configuration-iam.png) Figure 3. Amazon Redshift IAM Configuration ![Amazon Redshift IAM configuration Advanced tab](/mulesoft/current/en-us/amazon-redshift-connector/1.1/_images/redshift-advanced-configuration.png) Figure 4. Amazon Redshift IAM Configuration Advanced Tab ## Prepare the Data File Prepare a data file to upload to Amazon S3. This data file will then be used as the data set in the `COPY` command for the Amazon Redshift table. 1. Create a file named `username.csv` in the `src/main/resources/` folder in your Mule project. 2. Populate the `username.csv` file with the following sample data: ```csv Username; Identifier;First name;Last name booker12;9012;Rachel;Booker grey07;2070;Laura;Grey johnson81;4081;Craig;Johnson jenkins46;9346;Mary;Jenkins smith79;5079;Jamie;Smith ``` ## Flows in This Example The following screenshots show the Anypoint Studio app flows for this example: - This flow creates the Amazon Redshift table, Amazon S3 bucket and object: ![Flow for creating the Redshift table Amazon S3 bucket and Amazon S3 object](/mulesoft/current/en-us/amazon-redshift-connector/1.1/_images/create-flow.png) - This flow executes the `COPY` command, which leverages the Amazon Redshift massively parallel processing (MPP) architecture to load data in parallel from a file in an Amazon S3 bucket: ![Execute the Copy command flow](/mulesoft/current/en-us/amazon-redshift-connector/1.1/_images/execute-flow.png) - This flow deletes the Amazon Redshift table and Amazon S3 bucket: ![Flow for deleting the Amazon Redshift table and Amazon S3 bucket](/mulesoft/current/en-us/amazon-redshift-connector/1.1/_images/delete-flow.png) ## Run the Example 1. Right-click in the project’s canvas and select **Run project redshift-demo**. 2. Open `localhost:8081/init` in a web browser and wait until it returns a response containing `success:true` with the created table, bucket, and an object. 3. Verify that the new Amazon S3 bucket `username-bucket` was created in your Amazon S3 instance. 4. Verify that the new Redshift table `username` was created in your Amazon Redshift instance. 5. Open `localhost:8081/execute` in a web browser and wait until it returns a response containing `success:true`. 6. Verify that the `username` table contains data from the `username.csv` file you added to the Mule project in [Prepare the Data File](#prepare-data-file). 7. Open `localhost:8081/delete` in a web browser and wait until it returns a response containing `success:true`. 8. Verify that both the `username` Amazon Redshift table and `username-bucket` Amazon S3 bucket were deleted. ### See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.1 --- # Amazon Redshift Connector 1.1 Reference > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) is a bulk-load connector that enables you to connect to the Amazon Redshift database to perform inserts, deletes, updates, and to execute SQL queries. ## Configurations
NameTypeDescriptionDefault ValueRequired

Name

String

The name for this configuration. Connectors reference the configuration with this name.

x

Connection

The connection types to provide to this configuration.

x

Name

String

The identifier of this element used to reference it in other components

x

Expiration Policy

Minimum amount of time that a dynamic configuration instance can remain idle before Mule considers it eligible for expiration.

## Connection Types ### Basic Connection This connection uses database credentials for authentication.
NameTypeDescriptionDefault ValueRequired

Pooling Profile

Provides a way to configure database connection pooling.

Column Types

Array of Column Type

Specifies non-standard column types.

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

Transaction isolation level to set on the driver when connecting to the database.

NOT_CONFIGURED

Use XA Transactions

Boolean

Indicates whether or not the created datasource must support XA transactions. The default is false.

false

Cluster Id

String

Cluster identifier.

x

Region Endpoint

String

Region to use for authentication against the database.

us-east-1

Port

Number

Configures the port of the database.

5439

Database

String

Database name.

x

User

String

Database user name.

x

Connection Properties

Object

Specifies a list of custom key-value connection properties to configure on the driver when connecting to the database.

Password

String

Password to use for authentication against the database.

Reconnection

When the application is deployed, a connectivity test is performed on all connectors. If set to true, deployment fails if the test doesn’t pass after exhausting the associated reconnection strategy.

### IAM Connection This connection uses IAM credentials to retrieve temporary database credentials for authentication.
NameTypeDescriptionDefault ValueRequired

Pooling Profile

Provides a way to configure database connection pooling.

Column Types

Array of Column Type

Specifies non-standard column types.

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

Transaction isolation level to set on the driver when connecting to the database.

NOT_CONFIGURED

Use XA Transactions

Boolean

Indicates whether or not the created datasource must support XA transactions. The default is false.

false

Cluster Id

String

Cluster identifier.

x

Region Endpoint

String

Region to use for authentication against the database.

us-east-1

Port

Number

Configures the port of the database.

5439

Database

String

Database name.

x

User

String

Database user name.

x

Connection Properties

Object

Specifies a list of custom key-value connection properties to configure on the driver when connecting to the database.

Access Key

String

Access key provided by Amazon for retrieving temporary database credentials.

x

Secret Key

String

Secret key provided by Amazon for retrieving temporary database credentials.

x

Role

Role configuration.

Custom STS Endpoint

String

Sets a custom STS endpoint. Useful when a non-standard service endpoint is required, such as a VPC or FIPS endpoint. The custom STS endpoint might not work if DefaultAWSCredentialsProviderChain is used. Instead, use AWS environment variables such as AWS_STS_REGIONAL_ENDPOINTS, AWS_REGION, and AWS_ENDPOINT_URL.

Reconnection

When the application is deployed, a connectivity test is performed on all connectors. If set to true, deployment fails if the test doesn’t pass after exhausting the associated reconnection strategy.

## Sources ## On Table Row `` Selects from a table at a regular interval and generates one message per row that is obtained. Optionally, watermark and ID columns can be provided. If a watermark column is provided, the values taken from that column will be used to filter the contents of the next poll, so that only rows with a greater watermark value are returned. If an ID column is provided, this component automatically ensures that the same row is not picked twice by concurrent polls. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Table

String

Name of the table to select from.

x

Watermark Column

String

Name of the column to use for the watermark. Values taken from the watermark column will be used to filter the contents of the next poll, so that only rows with a greater watermark value are processed.

Id Column

String

Name of the column to use as the row ID. If provided, this component ensures that the same row is not processed twice by concurrent polls.

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_BEGIN

  • NONE

The type of beginning action that sources can take for transactions.

NONE

Transaction Type

Enumeration, one of:

  • LOCAL

  • XA

The type of transaction to create. Availability depends on Mule version.

LOCAL

Primary Node Only

Boolean

Whether this source should only be executed on the primary node when running in a cluster

Scheduling Strategy

scheduling-strategy

Configures the scheduler that triggers the polling

x

Redelivery Policy

Defines a policy for processing the redelivery of the same message

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Object

### For Configurations - [Configurations](#configurations) ## Operations - [Bulk Delete](#BulkDelete) - [Bulk Insert](#BulkInsert) - [Bulk Update](#BulkUpdate) - [Delete](#Delete) - [Execute DDL](#ExecuteDdl) - [Execute Script](#ExecuteScript) - [Insert](#Insert) - [Query Single](#QuerySingle) - [Select](#Select) - [Stored Procedure](#StoredProcedure) - [Update](#Update) ## Bulk Delete `` Enables the execution of one delete statement at various times using different parameter bindings. This uses a single database statement, which has performance advantages compared to executing a single delete operation at various times. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Input Parameters

Array of Object

A java.util.List of java.util.Maps in which every list item represents a row to be inserted. The map contains the parameter names as keys and the value the parameter is bound to.

#[payload]

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Bulk Insert `` Enables the execution of one insert statement at various times using different parameter bindings. This uses a single database statement, which has performance advantages compared to executing a single update operation at various times. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Input Parameters

Array of Object

A java.util.List of java.util.Maps in which every list item represents a row to be inserted. The map contains the parameter names as keys and the value the parameter is bound to.

#[payload]

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Bulk Update `` Enables the execution of one update statement at various times using different parameter bindings. This uses a single database statement, which has performance advantages compared to executing a single update operation at various times. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Input Parameters

Array of Object

A java.util.List of java.util.Maps in which every list item represents a row to be inserted. The map contains the parameter names as keys and the value the parameter is bound to.

#[payload]

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Delete `` Deletes data in a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Execute DDL `` Enables execution of DDL queries against a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

Name of the configuration to use.

x

SQL Query Text

String

Text of the SQL query to execute.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Execute Script `` Executes a SQL script in a single database statement. The script is executed as provided by the user, without any parameter binding. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

SQL Query Text

String

SQL query to execute.

Script Path

String

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Insert `` Inserts data into a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

Name of the configuration to use.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

Auto Generated Keys Column Indexes

Array of Number

List of column indexes that indicates which auto-generated keys to make available for retrieval.

Auto Generated Keys Column Names

Array of String

List of column names that indicates which auto-generated keys to make available for retrieval.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Statement Result

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Query Single `` Selects a single result from a database. If the specified SQL query returns more than one record, only the first record is returned. Streaming is not used for this operation, so you must be careful because all selected fields will be loaded to memory. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Object

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Select `` Selects data from a database. Streaming is automatically applied to avoid performance and memory issues that can be caused by preemptive consumption of results. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Streaming Strategy

Configure if repeatable streams should be used and their behavior

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors

### Output

Type

Array of Object

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION ## Stored Procedure `` Invokes a Stored Procedure on the database. When the stored procedure returns one or more java.sql.ResultSet instances, streaming is automatically applied to avoid performance and memory issues that can be caused by preemptive consumption of results. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Streaming Strategy

Configures how Mule processes streams. The default is to use repeatable streams.

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Input - Output Parameters

Object

Output Parameters

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

Auto Generated Keys Column Indexes

Array of Number

List of column indexes that indicates which auto-generated keys to make available for retrieval.

Auto Generated Keys Column Names

Array of String

List of column names that indicates which auto-generated keys to make available for retrieval.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Object

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Update `` Updates data in a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

Name of the configuration to use.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

Auto Generated Keys Column Indexes

Array of Number

List of column indexes that indicates which auto-generated keys to make available for retrieval.

Auto Generated Keys Column Names

Array of String

List of column names that indicates which auto-generated keys to make available for retrieval.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Statement Result

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Types ### Pooling Profile
FieldTypeDescriptionDefault ValueRequired

Max Pool Size

Number

5

Min Pool Size

Number

0

Acquire Increment

Number

1

Prepared Statement Cache Size

Number

5

Max Wait

Number

0

Max Wait Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

SECONDS

Max Idle Time

Number

0

Additional Properties

Object

### Column Type
FieldTypeDescriptionDefault ValueRequired

Id

Number

x

Type Name

String

x

Class Name

String

### Reconnection
FieldTypeDescriptionDefault ValueRequired

Fails Deployment

Boolean

When the application is deployed, a connectivity test is performed on all connectors. If set to true, deployment fails if the test doesn’t pass after exhausting the associated reconnection strategy.

Reconnection Strategy

The reconnection strategy to use.

### Reconnect
FieldTypeDescriptionDefault ValueRequired

Frequency

Number

How often in milliseconds to reconnect

Blocking

Boolean

If false, the reconnection strategy will run in a separate, non-blocking thread

Count

Number

How many reconnection attempts to make.

### Reconnect Forever
FieldTypeDescriptionDefault ValueRequired

Frequency

Number

How often in milliseconds to reconnect

Blocking

Boolean

If false, the reconnection strategy will run in a separate, non-blocking thread

### Role
FieldTypeDescriptionDefault ValueRequired

Arn

String

The Amazon Resource Name (ARN) of the role to assume.

x

External Id

String

A unique identifier that might be required when you assume a role in another account. If the administrator of the account to which the role belongs provides an external ID, then provide that value in this field.

Duration

Number

The duration of the role session.

3600

Duration Time Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Duration value.

SECONDS

Referred Policy Arns

Array of String

The Amazon Resource Names (ARNs) of the IAM-managed policies to use as managed session policies. The policies must exist in the same account as the role.

Tags

Object

A list of session tags that you want to pass. Each session tag consists of a key name and an associated value.

### Expiration Policy
FieldTypeDescriptionDefault ValueRequired

Max Idle Time

Number

A scalar time value for the maximum amount of time a dynamic configuration instance is allowed to be idle before it’s considered eligible for expiration.

Time Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Max Idle Time field.

### Redelivery Policy
FieldTypeDescriptionDefault ValueRequired

Max Redelivery Count

Number

The maximum number of times a message can be redelivered and processed unsuccessfully before triggering process-failed-message

Message Digest Algorithm

String

The secure hashing algorithm to use. If not set, the default is SHA-256.

SHA-256

Message Identifier

Defines which strategy is used to identify the messages.

Object Store

ObjectStore

The object store where the redelivery counter for each message is stored.

### Redelivery Policy Message Identifier
FieldTypeDescriptionDefault ValueRequired

Use Secure Hash

Boolean

Whether to use a secure hash algorithm to identify a redelivered message.

Id Expression

String

Defines one or more expressions to use to determine when a message has been redelivered. This property may only be set if Use Secure Hash is false.

### Parameter Type
FieldTypeDescriptionDefault ValueRequired

Key

String

x

Type Classifier

x

### Type Classifier
FieldTypeDescriptionDefault ValueRequired

Type

Enumeration, one of:

  • BIT

  • TINYINT

  • SMALLINT

  • INTEGER

  • BIGINT

  • FLOAT

  • REAL

  • DOUBLE

  • NUMERIC

  • DECIMAL

  • CHAR

  • VARCHAR

  • LONGVARCHAR

  • DATE

  • TIME

  • TIMESTAMP

  • BINARY

  • VARBINARY

  • LONGVARBINARY

  • NULL

  • OTHER

  • JAVA_OBJECT

  • DISTINCT

  • STRUCT

  • ARRAY

  • BLOB

  • CLOB

  • REF

  • DATALINK

  • BOOLEAN

  • ROWID

  • NCHAR

  • NVARCHAR

  • LONGNVARCHAR

  • NCLOB

  • SQLXML

  • UNKNOWN

Custom Type

String

### Statement Result
FieldTypeDescriptionDefault ValueRequired

Affected Rows

Number

Generated Keys

Object

### Repeatable In Memory Iterable
FieldTypeDescriptionDefault ValueRequired

Initial Buffer Size

Number

The number of instances to initially keep in memory to consume the stream and provide random access to it. If the stream contains more data than can fit into this buffer, then the buffer expands according to the bufferSizeIncrement attribute, with an upper limit of maxInMemorySize. Default value is 100 instances.

Buffer Size Increment

Number

This is by how much the buffer size expands if it exceeds its initial size. Setting a value of zero or lower means that the buffer should not expand, meaning that a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised when the buffer gets full. Default value is 100 instances.

Max Buffer Size

Number

The maximum amount of memory to use. If more than that is used then a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised. A value lower than or equal to zero means no limit.

### Repeatable File Store Iterable
FieldTypeDescriptionDefault ValueRequired

In Memory Objects

Number

The maximum amount of instances to keep in memory. If more than that is required, content on the disk is buffered.

Buffer Unit

Enumeration, one of:

  • BYTE

  • KB

  • MB

  • GB

The unit in which maxInMemorySize is expressed

### Repeatable In Memory Stream
FieldTypeDescriptionDefault ValueRequired

Initial Buffer Size

Number

The amount of memory that will be allocated to consume the stream and provide random access to it. If the stream contains more data than can be fit into this buffer, then the buffer expands according to the bufferSizeIncrement attribute, with an upper limit of maxInMemorySize.

Buffer Size Increment

Number

This is by how much the buffer size expands if it exceeds its initial size. Setting a value of zero or lower means that the buffer should not expand, meaning that a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised when the buffer gets full.

Max Buffer Size

Number

The maximum amount of memory to use. If more than that is used then a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised. A value lower than or equal to zero means no limit.

Buffer Unit

Enumeration, one of:

  • BYTE

  • KB

  • MB

  • GB

The unit in which all these attributes are expressed

### Repeatable File Store Stream
FieldTypeDescriptionDefault ValueRequired

In Memory Size

Number

Defines the maximum memory that the stream should use to keep data in memory. If more than that is consumed content on the disk is buffered.

Buffer Unit

Enumeration, one of:

  • BYTE

  • KB

  • MB

  • GB

The unit in which maxInMemorySize is expressed

### Output Parameter
FieldTypeDescriptionDefault ValueRequired

Key

String

x

Type Classifier

x

## See Also - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.1 --- # Amazon Redshift Connector 1.1 XML and Maven Support > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Although you can manually code a Mule app in XML, it is more efficient to use Anypoint Studio: - If you manually code a Mule runtime engine (Mule) app in XML, either from the Anypoint Studio XML editor or from a text editor, you can access the connector from your app by adding reference statements to both your XML Mule flow and the Apache Maven `pom.xml` file. - If you add the connector in Studio, Studio automatically populates the XML code with the connector’s namespace and schema location and it also adds a dependency to the `pom.xml` file. ## Add a Namespace for the Connector Paste the following code inside the `mule` tag of the header of your configuration XML: ```xml http://www.mulesoft.org/schema/mule/redshift http://www.mulesoft.org/schema/mule/redshift/current/mule-redshift.xsd ``` This example shows how the namespace and schema statements are placed in the XML: ```xml ``` ## Add a POM File Dependency The Apache Maven `pom.xml` file generated by Anypoint Studio contains dependencies for a Mule app. When you code a Mule app manually, include this XML snippet in your `pom.xml` file to enable access to this connector: ```xml com.mulesoft.connectors mule4-amazon-redshift-connector x.x.x mule-plugin ``` Replace `x.x.x` with the version that corresponds to the connector you are using. To obtain the most up-to-date `pom.xml` file information: 1. Go to [Anypoint Exchange](https://www.mulesoft.com/exchange/). 2. In Exchange, click **Login** and supply your Anypoint Platform username and password. 3. In Exchange, search for `amazon redshift`. 4. Select the connector. 5. Click **Dependency Snippets** near the upper right of the screen. ## Next Step After completing your namespace and `pom.xml` file, you can try the [example](https://docs.mulesoft.com/amazon-redshift-connector/1.1/amazon-redshift-connector-example). ## See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.2 is-latest-version: true --- # Amazon Redshift Connector 1.2 > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) is a bulk-load connector that enables you to connect to Amazon Redshift database to perform inserts, deletes, updates, and to execute SQL queries. For compatibility information and fixed issues, see the Amazon Redshift Connector Release Notes. ## Before You Begin To use this connector, you must be familiar with: - The Amazon Redshift API - Anypoint Connectors - Mule runtime (Mule) - Elements and global elements in a Mule flow - Creating a Mule app using Anypoint Studio or Anypoint Code Builder Before creating an app, you must have: - Credentials to access the Amazon Redshift target resource - Anypoint Platform - Anypoint Studio or Anypoint Code Builder ## Common Use Cases for the Connector Some possible uses for this connector include: - Analytics Amazon Redshift operates as an optimized data warehouse that can store large volumes of data, which can then be analyzed using a business intelligence (BI) tool such as Tableau. - Real-time processing of social media feeds Stream data from your social media feeds, such as a Twitter feed, into Amazon Kinesis Data Streams, and then store that data in a data warehouse, such as Amazon Redshift to perform real-time analytics. ## Authentication Types Amazon Redshift Connector uses the following connection methods: - Basic authentication, which uses **user** and **password** field values to connect to the database - IAM authentication, which uses **access key** and **secret key** field values to retrieve the temporary password for the specified user in order to connect to the database ## See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Use Exchange to Discover Connectors, Templates, and Examples](https://docs.mulesoft.com/connectors/introduction/intro-use-exchange) - [Amazon Redshift Reference](https://docs.mulesoft.com/amazon-redshift-connector/latest/amazon-redshift-connector-reference) - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.2 is-latest-version: true --- # Amazon Redshift Connector 1.2 Example > For the full documentation index, see: https://docs.mulesoft.com/llms.txt This example shows you how to use Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) to load data from Amazon S3 to Amazon Redshift. ## Create the Mule Project 1. In Studio, select **File > New > Mule Project**. 2. Enter `redshift-demo` as the name for your Mule project and click **Finish**. 3. Open the `redshift-demo.xml` file in `src/main/mule` folder. 4. In the **Mule Palette** view, click **(X) Search in Exchange**. 5. In **Add Modules to Project**, type `redshift` in the search field. 6. Click **Amazon Redshift Connector** in **Available modules**. 7. Click **Add**. 8. Click **Finish**. 9. Repeat the previous steps to also add the Amazon S3 Connector (search for `amazon s3`) and File Connector (search for `file`) to the Mule project. ## XML Code for This Example Overwrite the contents of `redshift-demo.xml` file with the following XML snippet: ```xml ]]> ``` ## Configure Global Elements 1. Create a file named `mule-artifact.properties` in the `src/main/resources/` folder. 2. Add the following properties in the `mule-artifact.properties` file and assign the correct values: s3.access\_key= s3.secret\_key= s3.region= redshift.cluster\_id= redshift.region= redshift.port= redshift.database= redshift.user= redshift.password= copy.access\_key= copy.secret\_key= *** - You must have Amazon S3 credentials to create and delete buckets and to create an object. - Amazon Redshift credentials are required to establish a connection to the database. - This example is using a `COPY` command to load a table in parallel from a data file on Amazon S3. To use the `COPY` command, you must authenticate using your IAM user credentials with the correct policies attached to it, therefore, it is best to use the Amazon S3 Read only policy for the `copy.access_key` and `copy.secret_key` properties. - Alternatively, it is possible to use an IAM Role instead of the Access and Secret key pair. To do this, you must ensure the role has the correct policies attached to it. It is best to use the Amazon S3 Read only policy for this purpose. - To create an IAM role to allow your Redshift cluster to communicate with the Amazon S3 service on your behalf, follow the steps in this [Amazon Redshift tutorial](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html). 3. Open the Amazon Redshift configuration, scroll down, and configure the JDBC driver in the **Required libraries** section. In this example, choose the **Add recommended library** option. If this doesn’t work, add the Amazon Maven repository to your `pom.xml` file and try again: ```xml ... redshift https://s3.amazonaws.com/redshift-maven-repository/release ``` The following images show the Amazon S3 and Amazon Redshift configurations: ![Amazon S3 configuration](/mulesoft/current/en-us/amazon-redshift-connector/latest/_images/s3-configuration.png) Figure 1. Amazon S3 Configuration ![Amazon Redshift Basic configuration](/mulesoft/current/en-us/amazon-redshift-connector/latest/_images/redshift-configuration.png) Figure 2. Amazon Redshift Basic Configuration ![Amazon Redshift IAM configuration](/mulesoft/current/en-us/amazon-redshift-connector/latest/_images/redshift-configuration-iam.png) Figure 3. Amazon Redshift IAM Configuration ![Amazon Redshift IAM configuration Advanced tab](/mulesoft/current/en-us/amazon-redshift-connector/latest/_images/redshift-advanced-configuration.png) Figure 4. Amazon Redshift IAM Configuration Advanced Tab ## Prepare the Data File Prepare a data file to upload to Amazon S3. This data file will then be used as the data set in the `COPY` command for the Amazon Redshift table. 1. Create a file named `username.csv` in the `src/main/resources/` folder in your Mule project. 2. Populate the `username.csv` file with the following sample data: ```csv Username; Identifier;First name;Last name booker12;9012;Rachel;Booker grey07;2070;Laura;Grey johnson81;4081;Craig;Johnson jenkins46;9346;Mary;Jenkins smith79;5079;Jamie;Smith ``` ## Flows in This Example The following screenshots show the Anypoint Studio app flows for this example: - This flow creates the Amazon Redshift table, Amazon S3 bucket and object: ![Flow for creating the Redshift table Amazon S3 bucket and Amazon S3 object](/mulesoft/current/en-us/amazon-redshift-connector/latest/_images/create-flow.png) - This flow executes the `COPY` command, which leverages the Amazon Redshift massively parallel processing (MPP) architecture to load data in parallel from a file in an Amazon S3 bucket: ![Execute the Copy command flow](/mulesoft/current/en-us/amazon-redshift-connector/latest/_images/execute-flow.png) - This flow deletes the Amazon Redshift table and Amazon S3 bucket: ![Flow for deleting the Amazon Redshift table and Amazon S3 bucket](/mulesoft/current/en-us/amazon-redshift-connector/latest/_images/delete-flow.png) ## Run the Example 1. Right-click in the project’s canvas and select **Run project redshift-demo**. 2. Open `localhost:8081/init` in a web browser and wait until it returns a response containing `success:true` with the created table, bucket, and an object. 3. Verify that the new Amazon S3 bucket `username-bucket` was created in your Amazon S3 instance. 4. Verify that the new Redshift table `username` was created in your Amazon Redshift instance. 5. Open `localhost:8081/execute` in a web browser and wait until it returns a response containing `success:true`. 6. Verify that the `username` table contains data from the `username.csv` file you added to the Mule project in [Prepare the Data File](#prepare-data-file). 7. Open `localhost:8081/delete` in a web browser and wait until it returns a response containing `success:true`. 8. Verify that both the `username` Amazon Redshift table and `username-bucket` Amazon S3 bucket were deleted. ### See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.2 is-latest-version: true --- # Amazon Redshift Connector 1.2 Reference > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) is a bulk-load connector that enables you to connect to the Amazon Redshift database to perform inserts, deletes, updates, and to execute SQL queries. ## Configurations
NameTypeDescriptionDefault ValueRequired

Name

String

The name for this configuration. Connectors reference the configuration with this name.

x

Connection

The connection types to provide to this configuration.

x

Name

String

The identifier of this element used to reference it in other components

x

Expiration Policy

Minimum amount of time that a dynamic configuration instance can remain idle before Mule considers it eligible for expiration.

## Connection Types ### Basic Connection This connection uses database credentials for authentication.
NameTypeDescriptionDefault ValueRequired

Pooling Profile

Provides a way to configure database connection pooling.

Column Types

Array of Column Type

Specifies non-standard column types.

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

Transaction isolation level to set on the driver when connecting to the database.

NOT_CONFIGURED

Use XA Transactions

Boolean

Indicates whether or not the created datasource must support XA transactions. The default is false.

false

Cluster Id

String

Cluster identifier.

x

Region Endpoint

String

Region to use for authentication against the database.

us-east-1

Port

Number

Configures the port of the database.

5439

Database

String

Database name.

x

User

String

Database user name.

x

Connection Properties

Object

Specifies a list of custom key-value connection properties to configure on the driver when connecting to the database.

Password

String

Password to use for authentication against the database.

Reconnection

When the application is deployed, a connectivity test is performed on all connectors. If set to true, deployment fails if the test doesn’t pass after exhausting the associated reconnection strategy.

### IAM Connection This connection uses IAM credentials to retrieve temporary database credentials for authentication.
NameTypeDescriptionDefault ValueRequired

Pooling Profile

Provides a way to configure database connection pooling.

Column Types

Array of Column Type

Specifies non-standard column types.

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

Transaction isolation level to set on the driver when connecting to the database.

NOT_CONFIGURED

Use XA Transactions

Boolean

Indicates whether or not the created datasource must support XA transactions. The default is false.

false

Cluster Id

String

Cluster identifier.

x

Region Endpoint

String

Region to use for authentication against the database.

us-east-1

Port

Number

Configures the port of the database.

5439

Database

String

Database name.

x

User

String

Database user name.

x

Connection Properties

Object

Specifies a list of custom key-value connection properties to configure on the driver when connecting to the database.

Access Key

String

Access key provided by Amazon for retrieving temporary database credentials.

x

Secret Key

String

Secret key provided by Amazon for retrieving temporary database credentials.

x

Role

Role configuration.

Custom STS Endpoint

String

Sets a custom STS endpoint. Useful when a non-standard service endpoint is required, such as a VPC or FIPS endpoint. The custom STS endpoint might not work if DefaultAWSCredentialsProviderChain is used. Instead, use AWS environment variables such as AWS_STS_REGIONAL_ENDPOINTS, AWS_REGION, and AWS_ENDPOINT_URL.

Reconnection

When the application is deployed, a connectivity test is performed on all connectors. If set to true, deployment fails if the test doesn’t pass after exhausting the associated reconnection strategy.

## Sources ## On Table Row `` Selects from a table at a regular interval and generates one message per row that is obtained. Optionally, watermark and ID columns can be provided. If a watermark column is provided, the values taken from that column will be used to filter the contents of the next poll, so that only rows with a greater watermark value are returned. If an ID column is provided, this component automatically ensures that the same row is not picked twice by concurrent polls. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Table

String

Name of the table to select from.

x

Watermark Column

String

Name of the column to use for the watermark. Values taken from the watermark column will be used to filter the contents of the next poll, so that only rows with a greater watermark value are processed.

Id Column

String

Name of the column to use as the row ID. If provided, this component ensures that the same row is not processed twice by concurrent polls.

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_BEGIN

  • NONE

The type of beginning action that sources can take for transactions.

NONE

Transaction Type

Enumeration, one of:

  • LOCAL

  • XA

The type of transaction to create. Availability depends on Mule version.

LOCAL

Primary Node Only

Boolean

Whether this source should only be executed on the primary node when running in a cluster

Scheduling Strategy

scheduling-strategy

Configures the scheduler that triggers the polling

x

Redelivery Policy

Defines a policy for processing the redelivery of the same message

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Object

### For Configurations - [Configurations](#configurations) ## Operations - [Bulk Delete](#BulkDelete) - [Bulk Insert](#BulkInsert) - [Bulk Update](#BulkUpdate) - [Delete](#Delete) - [Execute DDL](#ExecuteDdl) - [Execute Script](#ExecuteScript) - [Insert](#Insert) - [Query Single](#QuerySingle) - [Select](#Select) - [Stored Procedure](#StoredProcedure) - [Update](#Update) ## Bulk Delete `` Enables the execution of one delete statement at various times using different parameter bindings. This uses a single database statement, which has performance advantages compared to executing a single delete operation at various times. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Input Parameters

Array of Object

A java.util.List of java.util.Maps in which every list item represents a row to be inserted. The map contains the parameter names as keys and the value the parameter is bound to.

#[payload]

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Bulk Insert `` Enables the execution of one insert statement at various times using different parameter bindings. This uses a single database statement, which has performance advantages compared to executing a single update operation at various times. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Input Parameters

Array of Object

A java.util.List of java.util.Maps in which every list item represents a row to be inserted. The map contains the parameter names as keys and the value the parameter is bound to.

#[payload]

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Bulk Update `` Enables the execution of one update statement at various times using different parameter bindings. This uses a single database statement, which has performance advantages compared to executing a single update operation at various times. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Input Parameters

Array of Object

A java.util.List of java.util.Maps in which every list item represents a row to be inserted. The map contains the parameter names as keys and the value the parameter is bound to.

#[payload]

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Delete `` Deletes data in a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Execute DDL `` Enables execution of DDL queries against a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

Name of the configuration to use.

x

SQL Query Text

String

Text of the SQL query to execute.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Execute Script `` Executes a SQL script in a single database statement. The script is executed as provided by the user, without any parameter binding. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

SQL Query Text

String

SQL query to execute.

Script Path

String

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Array of Number

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Insert `` Inserts data into a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

Name of the configuration to use.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

Auto Generated Keys Column Indexes

Array of Number

List of column indexes that indicates which auto-generated keys to make available for retrieval.

Auto Generated Keys Column Names

Array of String

List of column names that indicates which auto-generated keys to make available for retrieval.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Statement Result

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Query Single `` Selects a single result from a database. If the specified SQL query returns more than one record, only the first record is returned. Streaming is not used for this operation, so you must be careful because all selected fields will be loaded to memory. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component.

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Object

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Select `` Selects data from a database. Streaming is automatically applied to avoid performance and memory issues that can be caused by preemptive consumption of results. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Streaming Strategy

Configure if repeatable streams should be used and their behavior

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors

### Output

Type

Array of Object

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION ## Stored Procedure `` Invokes a Stored Procedure on the database. When the stored procedure returns one or more java.sql.ResultSet instances, streaming is automatically applied to avoid performance and memory issues that can be caused by preemptive consumption of results. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

The name of the configuration to use.

x

Config Ref

ConfigurationProvider

The name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Streaming Strategy

Configures how Mule processes streams. The default is to use repeatable streams.

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Input - Output Parameters

Object

Output Parameters

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

Auto Generated Keys Column Indexes

Array of Number

List of column indexes that indicates which auto-generated keys to make available for retrieval.

Auto Generated Keys Column Names

Array of String

List of column names that indicates which auto-generated keys to make available for retrieval.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Object

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Update `` Updates data in a database. ### Parameters
NameTypeDescriptionDefault ValueRequired

Configuration

String

Name of the configuration to use.

x

Config Ref

ConfigurationProvider

Name of the configuration to use to execute this component

x

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take for transactions.

JOIN_IF_POSSIBLE

Query Timeout

Number

Indicates the minimum amount of time before a running statement is canceled. By default, no timeout is used.

0

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Query Timeout field.

SECONDS

Fetch Size

Number

Limit for the number of records to retrieve in a single page.

Max Rows

Number

Indicates the maximum number of rows to fetch from the result set.

SQL Query Text

String

SQL query to execute.

x

Parameter Types

Input Parameters

Object

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

Auto Generated Keys Column Indexes

Array of Number

List of column indexes that indicates which auto-generated keys to make available for retrieval.

Auto Generated Keys Column Names

Array of String

List of column names that indicates which auto-generated keys to make available for retrieval.

Target Variable

String

Name of the variable that stores the operation’s output.

Target Value

String

Expression that evaluates the operation’s output. The outcome of the expression is stored in the Target Variable.

#[payload]

Reconnection Strategy

A retry strategy in case of connectivity errors.

### Output

Type

Statement Result

### For Configurations - [Configurations](#configurations) ### Throws - REDSHIFT:BAD\_SQL\_SYNTAX - REDSHIFT:CONNECTIVITY - REDSHIFT:QUERY\_EXECUTION - REDSHIFT:RETRY\_EXHAUSTED ## Types ### Pooling Profile
FieldTypeDescriptionDefault ValueRequired

Max Pool Size

Number

5

Min Pool Size

Number

0

Acquire Increment

Number

1

Prepared Statement Cache Size

Number

5

Max Wait

Number

0

Max Wait Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

SECONDS

Max Idle Time

Number

0

Additional Properties

Object

### Column Type
FieldTypeDescriptionDefault ValueRequired

Id

Number

x

Type Name

String

x

Class Name

String

### Reconnection
FieldTypeDescriptionDefault ValueRequired

Fails Deployment

Boolean

When the application is deployed, a connectivity test is performed on all connectors. If set to true, deployment fails if the test doesn’t pass after exhausting the associated reconnection strategy.

Reconnection Strategy

The reconnection strategy to use.

### Reconnect
FieldTypeDescriptionDefault ValueRequired

Frequency

Number

How often in milliseconds to reconnect

Blocking

Boolean

If false, the reconnection strategy will run in a separate, non-blocking thread

Count

Number

How many reconnection attempts to make.

### Reconnect Forever
FieldTypeDescriptionDefault ValueRequired

Frequency

Number

How often in milliseconds to reconnect

Blocking

Boolean

If false, the reconnection strategy will run in a separate, non-blocking thread

### Role
FieldTypeDescriptionDefault ValueRequired

Arn

String

The Amazon Resource Name (ARN) of the role to assume.

x

External Id

String

A unique identifier that might be required when you assume a role in another account. If the administrator of the account to which the role belongs provides an external ID, then provide that value in this field.

Duration

Number

The duration of the role session.

3600

Duration Time Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Duration value.

SECONDS

Referred Policy Arns

Array of String

The Amazon Resource Names (ARNs) of the IAM-managed policies to use as managed session policies. The policies must exist in the same account as the role.

Tags

Object

A list of session tags that you want to pass. Each session tag consists of a key name and an associated value.

### Expiration Policy
FieldTypeDescriptionDefault ValueRequired

Max Idle Time

Number

A scalar time value for the maximum amount of time a dynamic configuration instance is allowed to be idle before it’s considered eligible for expiration.

Time Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Time unit for the Max Idle Time field.

### Redelivery Policy
FieldTypeDescriptionDefault ValueRequired

Max Redelivery Count

Number

The maximum number of times a message can be redelivered and processed unsuccessfully before triggering process-failed-message

Message Digest Algorithm

String

The secure hashing algorithm to use. If not set, the default is SHA-256.

SHA-256

Message Identifier

Defines which strategy is used to identify the messages.

Object Store

ObjectStore

The object store where the redelivery counter for each message is stored.

### Redelivery Policy Message Identifier
FieldTypeDescriptionDefault ValueRequired

Use Secure Hash

Boolean

Whether to use a secure hash algorithm to identify a redelivered message.

Id Expression

String

Defines one or more expressions to use to determine when a message has been redelivered. This property may only be set if Use Secure Hash is false.

### Parameter Type
FieldTypeDescriptionDefault ValueRequired

Key

String

x

Type Classifier

x

### Type Classifier
FieldTypeDescriptionDefault ValueRequired

Type

Enumeration, one of:

  • BIT

  • TINYINT

  • SMALLINT

  • INTEGER

  • BIGINT

  • FLOAT

  • REAL

  • DOUBLE

  • NUMERIC

  • DECIMAL

  • CHAR

  • VARCHAR

  • LONGVARCHAR

  • DATE

  • TIME

  • TIMESTAMP

  • BINARY

  • VARBINARY

  • LONGVARBINARY

  • NULL

  • OTHER

  • JAVA_OBJECT

  • DISTINCT

  • STRUCT

  • ARRAY

  • BLOB

  • CLOB

  • REF

  • DATALINK

  • BOOLEAN

  • ROWID

  • NCHAR

  • NVARCHAR

  • LONGNVARCHAR

  • NCLOB

  • SQLXML

  • UNKNOWN

Custom Type

String

### Statement Result
FieldTypeDescriptionDefault ValueRequired

Affected Rows

Number

Generated Keys

Object

### Repeatable In Memory Iterable
FieldTypeDescriptionDefault ValueRequired

Initial Buffer Size

Number

The number of instances to initially keep in memory to consume the stream and provide random access to it. If the stream contains more data than can fit into this buffer, then the buffer expands according to the bufferSizeIncrement attribute, with an upper limit of maxInMemorySize. Default value is 100 instances.

Buffer Size Increment

Number

This is by how much the buffer size expands if it exceeds its initial size. Setting a value of zero or lower means that the buffer should not expand, meaning that a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised when the buffer gets full. Default value is 100 instances.

Max Buffer Size

Number

The maximum amount of memory to use. If more than that is used then a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised. A value lower than or equal to zero means no limit.

### Repeatable File Store Iterable
FieldTypeDescriptionDefault ValueRequired

In Memory Objects

Number

The maximum amount of instances to keep in memory. If more than that is required, content on the disk is buffered.

Buffer Unit

Enumeration, one of:

  • BYTE

  • KB

  • MB

  • GB

The unit in which maxInMemorySize is expressed

### Repeatable In Memory Stream
FieldTypeDescriptionDefault ValueRequired

Initial Buffer Size

Number

The amount of memory that will be allocated to consume the stream and provide random access to it. If the stream contains more data than can be fit into this buffer, then the buffer expands according to the bufferSizeIncrement attribute, with an upper limit of maxInMemorySize.

Buffer Size Increment

Number

This is by how much the buffer size expands if it exceeds its initial size. Setting a value of zero or lower means that the buffer should not expand, meaning that a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised when the buffer gets full.

Max Buffer Size

Number

The maximum amount of memory to use. If more than that is used then a STREAM_MAXIMUM_SIZE_EXCEEDED error is raised. A value lower than or equal to zero means no limit.

Buffer Unit

Enumeration, one of:

  • BYTE

  • KB

  • MB

  • GB

The unit in which all these attributes are expressed

### Repeatable File Store Stream
FieldTypeDescriptionDefault ValueRequired

In Memory Size

Number

Defines the maximum memory that the stream should use to keep data in memory. If more than that is consumed content on the disk is buffered.

Buffer Unit

Enumeration, one of:

  • BYTE

  • KB

  • MB

  • GB

The unit in which maxInMemorySize is expressed

### Output Parameter
FieldTypeDescriptionDefault ValueRequired

Key

String

x

Type Classifier

x

## See Also - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.2 is-latest-version: true --- # Amazon Redshift Connector 1.2 XML and Maven Support > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Although you can manually code a Mule app in XML, it is more efficient to use Anypoint Studio: - If you manually code a Mule runtime engine (Mule) app in XML, either from the Anypoint Studio XML editor or from a text editor, you can access the connector from your app by adding reference statements to both your XML Mule flow and the Apache Maven `pom.xml` file. - If you add the connector in Studio, Studio automatically populates the XML code with the connector’s namespace and schema location and it also adds a dependency to the `pom.xml` file. ## Add a Namespace for the Connector Paste the following code inside the `mule` tag of the header of your configuration XML: ```xml http://www.mulesoft.org/schema/mule/redshift http://www.mulesoft.org/schema/mule/redshift/current/mule-redshift.xsd ``` This example shows how the namespace and schema statements are placed in the XML: ```xml ``` ## Add a POM File Dependency The Apache Maven `pom.xml` file generated by Anypoint Studio contains dependencies for a Mule app. When you code a Mule app manually, include this XML snippet in your `pom.xml` file to enable access to this connector: ```xml com.mulesoft.connectors mule4-amazon-redshift-connector x.x.x mule-plugin ``` Replace `x.x.x` with the version that corresponds to the connector you are using. To obtain the most up-to-date `pom.xml` file information: 1. Go to [Anypoint Exchange](https://www.mulesoft.com/exchange/). 2. In Exchange, click **Login** and supply your Anypoint Platform username and password. 3. In Exchange, search for `amazon redshift`. 4. Select the connector. 5. Click **Dependency Snippets** near the upper right of the screen. ## Next Step After completing your namespace and `pom.xml` file, you can try the [example](https://docs.mulesoft.com/amazon-redshift-connector/latest/amazon-redshift-connector-example). ## See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.1 --- # Using Anypoint Studio to Configure Amazon Redshift Connector 1.1 > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Anypoint Studio (Studio) editors help you design and update your Mule applications, properties, and configuration files. To add and configure a connector in Studio: 1. [Create a mule project](#create-mule-project). 2. [Add the connector to your Mule project](#add-connector-to-project). 3. [Configure a Source](#configure-source). 4. [Add a connector operation to the flow](#add-connector-operation). 5. [Configure a global element for the connector](#configure-global-element). 6. [View the app log](#view-app-log). When you run the connector, you can view the app log to check for problems, as described in [View the App Log](#view-app-log). If you are new to configuring connectors in Studio, see [Using Anypoint Studio to Configure a Connector](https://docs.mulesoft.com/connectors/introduction/intro-config-use-studio). ## Create a Mule Project In Studio, create a new Mule project in which to add and configure the connector: 1. In Studio, select **File > New > Mule Project**. 2. Enter a name for your Mule project and click **Finish**. ## Add the Connector to Your Mule Project Add Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) to your Mule project to automatically populate the XML code with the connector’s namespace and schema location and add the required dependencies to the project’s `pom.xml` file: 1. In the **Mule Palette** view, click **(X) Search in Exchange**. 2. In **Add Modules to Project**, type `redshift` in the search field. 3. Click **Amazon Redshift Connector** in **Available modules**. 4. Click **Add**. 5. Click **Finish**. Adding a connector to a Mule project in Studio does not make that connector available to other projects in your Studio workspace. ## Configure a Source A source initiates a flow when a specified condition is met. You can configure one of these sources to use with Amazon Redshift Connector: - **On Table Row** Select rows from a database table at a regular interval and generates one message per row that is obtained. - **HTTP > Listener** Initiates a flow each time it receives a request on the configured host and port - **Scheduler** Initiates a flow when a time-based condition is met To configure the **On Table Row** source, follow these steps: 1. In the **Mule Palette** view, select **Redshift**. 2. Drag **On Table Row** to the Studio canvas. 3. On the **On Table Row** properties window, optionally change the value of the **Display Name** field. 4. Select the created connector configuration or click the plus sign (**+**) next to the \*Connector configuration field to configure a new global element. Follow the steps in [Configure a global element for the connector](#configure-global-element). 5. On the **General** tab, complete the following fields:
FieldDescription

Name

Name used to reference this connector instance

Table

Name of the table to select from

## Add a Connector Operation to the Flow When you add a connector operation to your flow, you are specifying an action for that connector to perform. To add an operation for , follow these steps: 1. In the **Mule Palette** view, select **Amazon Redshift** and then select the desired operation. 2. Drag the operation onto the Studio canvas to the right of the source. ## Configure a Global Element for the Connector When you configure a connector, it’s best to configure a global element that all instances of that connector in the app can use. Configuring a global element requires you to provide the authentication credentials that the connector requires to access the target Amazon Redshift cluster. Amazon Redshift Connector supports: - Basic authentication, which uses **user** and **password** field values to connect to the database - IAM authentication, which uses **access key** and **secret key** field values to retrieve the temporary password for the specified user in order to connect to the database - Optionally, you can specify the **ARN** field value in the **Role** field if the connector should first assume an IAM role for a set of temporary security IAM credentials and use those to retrieve the password. When you configure a global element, you can reference a configuration file that contains ANT-style property placeholders (recommended), or you can enter your authorization credentials in the global configuration properties. For information about the benefits of using property placeholders and how to configure them, see [Anypoint Connector Configuration](https://docs.mulesoft.com/connectors/introduction/intro-connector-configuration-overview). To configure a global element, follow these steps: 1. Select the name of the connector in the Studio canvas. 2. In the configuration screen for the operation, click the plus sign (**+**) next to the **Connector configuration** field to access the global element configuration fields. 3. On the **General** tab, configure the fields depending on the connection type. - Basic Connection
FieldDescription

Name

Name used to reference the connector instance

Cluster id

Cluster identifier

Region Endpoint

Region Endpoint (optional)

Port

Database port (optional)

Database

Database name

User

Database username

Password

Password to use for authentication against the database

- IAM Connection
FieldDescription

Name

Name used to reference the connector instance

Cluster id

Cluster identifier

Region Endpoint

Region Endpoint (optional)

Port

Database port (optional)

Database

Database name

User

Database username

Access key

Access key provided by Amazon for retrieving temporary database credentials

Secret key

Secret key provided by Amazon for retrieving temporary database credentials

Role

Role configuration (optional)

Custom STS Endpoint

Sets a custom STS endpoint. Useful when a non-standard service endpoint is required, such as a VPC or FIPS endpoint (optional and on Advanced tab).

4. Scroll down and configure the JDBC driver in the **Required libraries** section. You can choose to select **Use local file**, **Add recommended library**, or **Add Maven dependency**. If you try to select **Add recommended library** or **Add Maven dependency** and this doesn’t work, add the Amazon Maven repository to your `pom.xml` file and try again. ```xml ... redshift https://s3.amazonaws.com/redshift-maven-repository/release ``` 5. On the **Advanced** tab, optionally add JDBC connection properties and reconnection information, including a reconnection strategy. 6. Click **Test Connection** to confirm that Mule can connect with the specified server. 7. Click **OK**. ## View the App Log To check for problems, you can view the app log as follows: - If you’re running the app from Anypoint Platform, the app log output is visible in the Anypoint Studio console window. - If you’re running the app using Mule from the command line, the app log output is visible in your OS console. Unless the log file path is customized in the app’s log file (`log4j2.xml`), you can also view the app log in the default location `MULE_HOME/logs/.log`. You can configure the location of the log path in the app log file `log4j2.xml`. ## Next Step After you configure a global element and connection information, configure the other fields for the connector. ## See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Amazon Redshift Connector Reference](https://docs.mulesoft.com/amazon-redshift-connector/1.1/amazon-redshift-connector-reference) - [Salesforce Help](https://help.salesforce.com) --- product: Amazon Redshift Connector version: 1.2 is-latest-version: true --- # Using Anypoint Studio to Configure Amazon Redshift Connector 1.2 > For the full documentation index, see: https://docs.mulesoft.com/llms.txt Anypoint Studio (Studio) editors help you design and update your Mule applications, properties, and configuration files. To add and configure a connector in Studio: 1. [Create a mule project](#create-mule-project). 2. [Add the connector to your Mule project](#add-connector-to-project). 3. [Configure a Source](#configure-source). 4. [Add a connector operation to the flow](#add-connector-operation). 5. [Configure a global element for the connector](#configure-global-element). 6. [View the app log](#view-app-log). When you run the connector, you can view the app log to check for problems, as described in [View the App Log](#view-app-log). If you are new to configuring connectors in Studio, see [Using Anypoint Studio to Configure a Connector](https://docs.mulesoft.com/connectors/introduction/intro-config-use-studio). ## Create a Mule Project In Studio, create a new Mule project in which to add and configure the connector: 1. In Studio, select **File > New > Mule Project**. 2. Enter a name for your Mule project and click **Finish**. ## Add the Connector to Your Mule Project Add Anypoint Connector for Amazon Redshift (Amazon Redshift Connector) to your Mule project to automatically populate the XML code with the connector’s namespace and schema location and add the required dependencies to the project’s `pom.xml` file: 1. In the **Mule Palette** view, click **(X) Search in Exchange**. 2. In **Add Modules to Project**, type `redshift` in the search field. 3. Click **Amazon Redshift Connector** in **Available modules**. 4. Click **Add**. 5. Click **Finish**. Adding a connector to a Mule project in Studio does not make that connector available to other projects in your Studio workspace. ## Configure a Source A source initiates a flow when a specified condition is met. You can configure one of these sources to use with Amazon Redshift Connector: - **On Table Row** Select rows from a database table at a regular interval and generates one message per row that is obtained. - **HTTP > Listener** Initiates a flow each time it receives a request on the configured host and port - **Scheduler** Initiates a flow when a time-based condition is met To configure the **On Table Row** source, follow these steps: 1. In the **Mule Palette** view, select **Redshift**. 2. Drag **On Table Row** to the Studio canvas. 3. On the **On Table Row** properties window, optionally change the value of the **Display Name** field. 4. Select the created connector configuration or click the plus sign (**+**) next to the \*Connector configuration field to configure a new global element. Follow the steps in [Configure a global element for the connector](#configure-global-element). 5. On the **General** tab, complete the following fields:
FieldDescription

Name

Name used to reference this connector instance

Table

Name of the table to select from

## Add a Connector Operation to the Flow When you add a connector operation to your flow, you are specifying an action for that connector to perform. To add an operation for , follow these steps: 1. In the **Mule Palette** view, select **Amazon Redshift** and then select the desired operation. 2. Drag the operation onto the Studio canvas to the right of the source. ## Configure a Global Element for the Connector When you configure a connector, it’s best to configure a global element that all instances of that connector in the app can use. Configuring a global element requires you to provide the authentication credentials that the connector requires to access the target Amazon Redshift cluster. Amazon Redshift Connector supports: - Basic authentication, which uses **user** and **password** field values to connect to the database - IAM authentication, which uses **access key** and **secret key** field values to retrieve the temporary password for the specified user in order to connect to the database - Optionally, you can specify the **ARN** field value in the **Role** field if the connector should first assume an IAM role for a set of temporary security IAM credentials and use those to retrieve the password. When you configure a global element, you can reference a configuration file that contains ANT-style property placeholders (recommended), or you can enter your authorization credentials in the global configuration properties. For information about the benefits of using property placeholders and how to configure them, see [Anypoint Connector Configuration](https://docs.mulesoft.com/connectors/introduction/intro-connector-configuration-overview). To configure a global element, follow these steps: 1. Select the name of the connector in the Studio canvas. 2. In the configuration screen for the operation, click the plus sign (**+**) next to the **Connector configuration** field to access the global element configuration fields. 3. On the **General** tab, configure the fields depending on the connection type. - Basic Connection
FieldDescription

Name

Name used to reference the connector instance

Cluster id

Cluster identifier

Region Endpoint

Region Endpoint (optional)

Port

Database port (optional)

Database

Database name

User

Database username

Password

Password to use for authentication against the database

- IAM Connection
FieldDescription

Name

Name used to reference the connector instance

Cluster id

Cluster identifier

Region Endpoint

Region Endpoint (optional)

Port

Database port (optional)

Database

Database name

User

Database username

Access key

Access key provided by Amazon for retrieving temporary database credentials

Secret key

Secret key provided by Amazon for retrieving temporary database credentials

Role

Role configuration (optional)

Custom STS Endpoint

Sets a custom STS endpoint. Useful when a non-standard service endpoint is required, such as a VPC or FIPS endpoint (optional and on Advanced tab).

4. Scroll down and configure the JDBC driver in the **Required libraries** section. You can choose to select **Use local file**, **Add recommended library**, or **Add Maven dependency**. If you try to select **Add recommended library** or **Add Maven dependency** and this doesn’t work, add the Amazon Maven repository to your `pom.xml` file and try again. ```xml ... redshift https://s3.amazonaws.com/redshift-maven-repository/release ``` 5. On the **Advanced** tab, optionally add JDBC connection properties and reconnection information, including a reconnection strategy. 6. Click **Test Connection** to confirm that Mule can connect with the specified server. 7. Click **OK**. ## View the App Log To check for problems, you can view the app log as follows: - If you’re running the app from Anypoint Platform, the app log output is visible in the Anypoint Studio console window. - If you’re running the app using Mule from the command line, the app log output is visible in your OS console. Unless the log file path is customized in the app’s log file (`log4j2.xml`), you can also view the app log in the default location `MULE_HOME/logs/.log`. You can configure the location of the log path in the app log file `log4j2.xml`. ## Next Step After you configure a global element and connection information, configure the other fields for the connector. ## See Also - [Introduction to Anypoint Connectors](https://docs.mulesoft.com/connectors/introduction/introduction-to-anypoint-connectors) - [Amazon Redshift Connector Reference](https://docs.mulesoft.com/amazon-redshift-connector/latest/amazon-redshift-connector-reference) - [Salesforce Help](https://help.salesforce.com)