Connecting Amazon Redshift Database using MuleSoft DB Connector

This blog will showcase how we can set connections with Amazon Redshift Database using MuleSoft Database Connector.

Configuration:

1. We need to add Redshift jdbc Shared Library configuration, Dependency and Repository in POM.

Shared Library Configuration (pom.xml)

xml

<"text-amber-300">class="text-sky-400 font-semibold">configuration>
  <"text-amber-300">class="text-sky-400 font-semibold">classifier>mule-application</"text-amber-300">class="text-sky-400 font-semibold">classifier>
  <"text-amber-300">class="text-sky-400 font-semibold">sharedLibraries>
    <"text-amber-300">class="text-sky-400 font-semibold">sharedLibrary>
      <"text-amber-300">class="text-sky-400 font-semibold">groupId>com.amazon.redshift</"text-amber-300">class="text-sky-400 font-semibold">groupId>
      <"text-amber-300">class="text-sky-400 font-semibold">artifactId>redshift-jdbc42</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
    </"text-amber-300">class="text-sky-400 font-semibold">sharedLibrary>
  </"text-amber-300">class="text-sky-400 font-semibold">sharedLibraries>
</"text-amber-300">class="text-sky-400 font-semibold">configuration>

Redshift JDBC Driver Dependency (pom.xml)

xml

<"text-amber-300">class="text-sky-400 font-semibold">dependency>
  <"text-amber-300">class="text-sky-400 font-semibold">groupId>com.amazon.redshift</"text-amber-300">class="text-sky-400 font-semibold">groupId>
  <"text-amber-300">class="text-sky-400 font-semibold">artifactId>redshift-jdbc42</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
  <"text-amber-300">class="text-sky-400 font-semibold">version>2.0.0.2</"text-amber-300">class="text-sky-400 font-semibold">version>
</"text-amber-300">class="text-sky-400 font-semibold">dependency>

Amazon Redshift Maven Repository (pom.xml)

xml

<"text-amber-300">class="text-sky-400 font-semibold">repository>
  <"text-amber-300">class="text-sky-400 font-semibold">id>redshift</"text-amber-300">class="text-sky-400 font-semibold">id>
  <"text-amber-300">class="text-sky-400 font-semibold">url>http://redshift-maven-repository.s3-website-us-east-1.amazonaws.com/release</"text-amber-300">class="text-sky-400 font-semibold">url>
</"text-amber-300">class="text-sky-400 font-semibold">repository>

Generic Connection Setup

2. Now move to the flow and drag a database connector as per the operation needed. In global-element, let's do the configuration for the database.

3. Below are the required Configs:

Connection: Generic Connection

Url: jdbc:redshift://<host>:<port>/<dbname>

DriverClass: com.amazon.redshift.jdbc42.Driver

User: <dbuser>

Password: <dbpassword>

As we have already added the Shared Library configuration, Dependency, and Repository in POM we can find the driver class as mentioned and the JDBC Driver jar is not required.

MuleSoft Database Config with Generic Connection for Amazon Redshift — Figure 1: Database Connector generic connection properties for Amazon Redshift

Connection Testing & Verification

4. Once the configurations are done we can test the connection.

Test Connection Successful to Amazon Redshift — Figure 2: Successful Redshift JDBC database test connection dialog

5. As the connection is successful we can perform our required operations.

POM.xml Listing

Complete pom.xml

xml

<?xml "text-amber-300">version="1.0" "text-amber-300">encoding="UTF-8"?>
<"text-amber-300">class="text-sky-400 font-semibold">project "text-amber-300">xmlns="http://maven.apache.org/POM/4.0.0"
  "text-amber-300">xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  "text-amber-300">xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <"text-amber-300">class="text-sky-400 font-semibold">modelVersion>4.0.0</"text-amber-300">class="text-sky-400 font-semibold">modelVersion>
  <"text-amber-300">class="text-sky-400 font-semibold">groupId>com.mycompany</"text-amber-300">class="text-sky-400 font-semibold">groupId>
  <"text-amber-300">class="text-sky-400 font-semibold">artifactId>redshift-db</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
  <"text-amber-300">class="text-sky-400 font-semibold">version>1.0.0-SNAPSHOT</"text-amber-300">class="text-sky-400 font-semibold">version>
  <"text-amber-300">class="text-sky-400 font-semibold">packaging>mule-application</"text-amber-300">class="text-sky-400 font-semibold">packaging>
  <"text-amber-300">class="text-sky-400 font-semibold">name>redshift-db</"text-amber-300">class="text-sky-400 font-semibold">name>
  <"text-amber-300">class="text-sky-400 font-semibold">properties>
    <"text-amber-300">class="text-sky-400 font-semibold">project.build.sourceEncoding>UTF-8</"text-amber-300">class="text-sky-400 font-semibold">project.build.sourceEncoding>
    <"text-amber-300">class="text-sky-400 font-semibold">project.reporting.outputEncoding>UTF-8</"text-amber-300">class="text-sky-400 font-semibold">project.reporting.outputEncoding>
    <"text-amber-300">class="text-sky-400 font-semibold">app.runtime>4.4.0</"text-amber-300">class="text-sky-400 font-semibold">app.runtime>
    <"text-amber-300">class="text-sky-400 font-semibold">mule.maven.plugin.version>3.8.0</"text-amber-300">class="text-sky-400 font-semibold">mule.maven.plugin.version>
  </"text-amber-300">class="text-sky-400 font-semibold">properties>
  <"text-amber-300">class="text-sky-400 font-semibold">build>
    <"text-amber-300">class="text-sky-400 font-semibold">plugins>
      <"text-amber-300">class="text-sky-400 font-semibold">plugin>
        <"text-amber-300">class="text-sky-400 font-semibold">groupId>org.apache.maven.plugins</"text-amber-300">class="text-sky-400 font-semibold">groupId>
        <"text-amber-300">class="text-sky-400 font-semibold">artifactId>maven-clean-plugin</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
        <"text-amber-300">class="text-sky-400 font-semibold">version>3.2.0</"text-amber-300">class="text-sky-400 font-semibold">version>
      </"text-amber-300">class="text-sky-400 font-semibold">plugin>
      <"text-amber-300">class="text-sky-400 font-semibold">plugin>
        <"text-amber-300">class="text-sky-400 font-semibold">groupId>org.mule.tools.maven</"text-amber-300">class="text-sky-400 font-semibold">groupId>
        <"text-amber-300">class="text-sky-400 font-semibold">artifactId>mule-maven-plugin</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
        <"text-amber-300">class="text-sky-400 font-semibold">version>${mule.maven.plugin.version}</"text-amber-300">class="text-sky-400 font-semibold">version>
        <"text-amber-300">class="text-sky-400 font-semibold">extensions>true</"text-amber-300">class="text-sky-400 font-semibold">extensions>
        <"text-amber-300">class="text-sky-400 font-semibold">configuration>
          <"text-amber-300">class="text-sky-400 font-semibold">classifier>mule-application</"text-amber-300">class="text-sky-400 font-semibold">classifier>
          <"text-amber-300">class="text-sky-400 font-semibold">sharedLibraries>
            <"text-amber-300">class="text-sky-400 font-semibold">sharedLibrary>
              <"text-amber-300">class="text-sky-400 font-semibold">groupId>com.amazon.redshift</"text-amber-300">class="text-sky-400 font-semibold">groupId>
              <"text-amber-300">class="text-sky-400 font-semibold">artifactId>redshift-jdbc42</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
            </"text-amber-300">class="text-sky-400 font-semibold">sharedLibrary>
          </"text-amber-300">class="text-sky-400 font-semibold">sharedLibraries>
        </"text-amber-300">class="text-sky-400 font-semibold">configuration>
      </"text-amber-300">class="text-sky-400 font-semibold">plugin>
    </"text-amber-300">class="text-sky-400 font-semibold">plugins>
  </"text-amber-300">class="text-sky-400 font-semibold">build>
  <"text-amber-300">class="text-sky-400 font-semibold">dependencies>
    <"text-amber-300">class="text-sky-400 font-semibold">dependency>
      <"text-amber-300">class="text-sky-400 font-semibold">groupId>org.mule.connectors</"text-amber-300">class="text-sky-400 font-semibold">groupId>
      <"text-amber-300">class="text-sky-400 font-semibold">artifactId>mule-http-connector</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
      <"text-amber-300">class="text-sky-400 font-semibold">version>1.7.1</"text-amber-300">class="text-sky-400 font-semibold">version>
      <"text-amber-300">class="text-sky-400 font-semibold">classifier>mule-plugin</"text-amber-300">class="text-sky-400 font-semibold">classifier>
    </"text-amber-300">class="text-sky-400 font-semibold">dependency>
    <"text-amber-300">class="text-sky-400 font-semibold">dependency>
      <"text-amber-300">class="text-sky-400 font-semibold">groupId>com.amazon.redshift</"text-amber-300">class="text-sky-400 font-semibold">groupId>
      <"text-amber-300">class="text-sky-400 font-semibold">artifactId>redshift-jdbc42</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
      <"text-amber-300">class="text-sky-400 font-semibold">version>2.0.0.2</"text-amber-300">class="text-sky-400 font-semibold">version>
    </"text-amber-300">class="text-sky-400 font-semibold">dependency>
    <"text-amber-300">class="text-sky-400 font-semibold">dependency>
      <"text-amber-300">class="text-sky-400 font-semibold">groupId>org.mule.connectors</"text-amber-300">class="text-sky-400 font-semibold">groupId>
      <"text-amber-300">class="text-sky-400 font-semibold">artifactId>mule-db-connector</"text-amber-300">class="text-sky-400 font-semibold">artifactId>
      <"text-amber-300">class="text-sky-400 font-semibold">version>1.13.6</"text-amber-300">class="text-sky-400 font-semibold">version>
      <"text-amber-300">class="text-sky-400 font-semibold">classifier>mule-plugin</"text-amber-300">class="text-sky-400 font-semibold">classifier>
    </"text-amber-300">class="text-sky-400 font-semibold">dependency>
  </"text-amber-300">class="text-sky-400 font-semibold">dependencies>
  <"text-amber-300">class="text-sky-400 font-semibold">repositories>
    <"text-amber-300">class="text-sky-400 font-semibold">repository>
      <"text-amber-300">class="text-sky-400 font-semibold">id>anypoint-exchange-v3</"text-amber-300">class="text-sky-400 font-semibold">id>
      <"text-amber-300">class="text-sky-400 font-semibold">name>Anypoint Exchange</"text-amber-300">class="text-sky-400 font-semibold">name>
      <"text-amber-300">class="text-sky-400 font-semibold">url>https://maven.anypoint.mulesoft.com/api/v3/maven</"text-amber-300">class="text-sky-400 font-semibold">url>
      <"text-amber-300">class="text-sky-400 font-semibold">layout>default</"text-amber-300">class="text-sky-400 font-semibold">layout>
    </"text-amber-300">class="text-sky-400 font-semibold">repository>
    <"text-amber-300">class="text-sky-400 font-semibold">repository>
      <"text-amber-300">class="text-sky-400 font-semibold">id>mulesoft-releases</"text-amber-300">class="text-sky-400 font-semibold">id>
      <"text-amber-300">class="text-sky-400 font-semibold">name>MuleSoft Releases Repository</"text-amber-300">class="text-sky-400 font-semibold">name>
      <"text-amber-300">class="text-sky-400 font-semibold">url>https://repository.mulesoft.org/releases/</"text-amber-300">class="text-sky-400 font-semibold">url>
      <"text-amber-300">class="text-sky-400 font-semibold">layout>default</"text-amber-300">class="text-sky-400 font-semibold">layout>
    </"text-amber-300">class="text-sky-400 font-semibold">repository>
    <"text-amber-300">class="text-sky-400 font-semibold">repository>
      <"text-amber-300">class="text-sky-400 font-semibold">id>redshift</"text-amber-300">class="text-sky-400 font-semibold">id>
      <"text-amber-300">class="text-sky-400 font-semibold">url>http://redshift-maven-repository.s3-website-us-east-1.amazonaws.com/release</"text-amber-300">class="text-sky-400 font-semibold">url>
    </"text-amber-300">class="text-sky-400 font-semibold">repository>
  </"text-amber-300">class="text-sky-400 font-semibold">repositories>
  <"text-amber-300">class="text-sky-400 font-semibold">pluginRepositories>
    <"text-amber-300">class="text-sky-400 font-semibold">pluginRepository>
      <"text-amber-300">class="text-sky-400 font-semibold">id>mulesoft-releases</"text-amber-300">class="text-sky-400 font-semibold">id>
      <"text-amber-300">class="text-sky-400 font-semibold">name>MuleSoft Releases Repository</"text-amber-300">class="text-sky-400 font-semibold">name>
      <"text-amber-300">class="text-sky-400 font-semibold">layout>default</"text-amber-300">class="text-sky-400 font-semibold">layout>
      <"text-amber-300">class="text-sky-400 font-semibold">url>https://repository.mulesoft.org/releases/</"text-amber-300">class="text-sky-400 font-semibold">url>
      <"text-amber-300">class="text-sky-400 font-semibold">snapshots>
        <"text-amber-300">class="text-sky-400 font-semibold">enabled>false</"text-amber-300">class="text-sky-400 font-semibold">enabled>
      </"text-amber-300">class="text-sky-400 font-semibold">snapshots>
    </"text-amber-300">class="text-sky-400 font-semibold">pluginRepository>
  </"text-amber-300">class="text-sky-400 font-semibold">pluginRepositories>
</"text-amber-300">class="text-sky-400 font-semibold">project>

References:

“Thank you for taking out time to read the above post. Hope you found it useful. In case of any questions, feel free to comment below. Also, if you are keen on knowing about a specific topic, happy to explore your recommendations as well.”

About the Author

Abhishek Bathwal

Integration and API Specialist at DigitalBuzz Software, focusing on MuleSoft Database Connector, AWS cloud integrations, Redshift data warehouses, and enterprise connectivity.

Share this article

All Articles

MuleSoft