Co-author – Srihari Ananda Kumar 

Amazon Redshift is a fully managed data warehouse solution by AWS, which enables you to store and query large volumes of analytical data. It uses SQL to analyze structured and semi-structured data across various data stores where you can access and analyze the data without the need for setting up a dedicated data warehouse.

Building a flow in IBM App Connect with Amazon Redshift

Use App Connect to build flows that integrate with Amazon Redshift and other applications. The connector is displayed as Amazon Redshift on the App Connect User Interface (UI). 

To allow App Connect to connect to your Amazon Redshift account, you need to complete the connection fields that you see in the App Connect Designer Catalog page or flow editor.

For more information on connection fields, see How to use App Connect with Amazon Redshift.

Amazon Redshift objects

The following are the Amazon Redshift objects that can be run in App Connect.

Scenario 1: Create a new row in a Google Sheets spreadsheet for each new employee retrieved from the Amazon Redshift record

Consider the following scenario, where you use App Connect to create a new row in a Google Sheets spreadsheet for each new employee retrieved from the Amazon Redshift record. 

In this example, employee details are obtained by executing a custom SQL query that retrieves information about an employee who recently joined an organization from specific Amazon Redshift employee database tables. The details for each employee retrieved from Amazon Redshift are then added to a spreadsheet in Google Sheets.

In this flow:

1. You run a scheduler-based flow at 15-minute intervals.
2. A Set variable node is added to define the previous runtime value, ‘previousruntime’ in ISO 8601 UTC format, YYYY-MM-DDTHH:MM:SS.SSSZ.
   
   For example, the set variable property is set to 2023-11-07.
   
   
   
3. Execute a custom SQL query to retrieve details of employees who joined after the specified set variable property 'previousruntime' value up to the current date. 
   
   For example, the following is a snippet of the SQL query:
   
   select appconnect.tracks."track_name",appconnect.tracks."experienced",appconnect.newjoinees."full_name",
   appconnect.newjoinees."address",appconnect.newjoinees."doj",appconnect.newjoinees."dob",appconnect.skills."technology",
   appconnect.skills."experience" FROM appconnect.tracks JOIN appconnect.newjoinees ON appconnect.tracks."emp_id"=appconnect.newjoinees."emp_id" JOIN appconnect.skills ON appconnect.newjoinees."emp_id"=appconnect.skills."emp_id"  where appconnect.newjoinees."doj" > {{$Setvariable.variable.previousruntime}} and appconnect.newjoinees."doj" < {{$Trigger.currentEventTime}}
   
   
4. For each new employee record retrieved from Amazon Redshift, the information is parsed into JSON strings.
   1. A JSON Parser node from the App Connect toolbox is used to parse the Current Item properties and output into JSON string.
      
      For example:
      
      
      
      
   2. Create a row in Google Sheets for each new employee. The information is added into a new row in the Google Sheets worksheet based on the parsed information. 
      
      For example:
      
      
      
      The following is an example of the parsed JSON properties that can be used to insert into the worksheet.
      
       

In summary, when a new employee onboard at Company ABC, their details are gathered and recorded in a Google Sheets spreadsheet.
This streamlined process facilitates efficient project allocation by the company management for the designated employee.

Resources

Try out our templates

You can view the following use cases in the Templates gallery in your App Connect Designer instance.

The following use cases are available in the Templates gallery on App Connect Designer.

• Create new row in a Google Sheets spreadsheet for each new employee retrieved from the Amazon Redshift record

  Template URL: https://<your-instance-id>/templates/Create%20a%20new%20row%20in%20a%20Google%20Sheets%20spreadsheet%20for%20each%20new%20employee%20retrieved%20from%20the
%20Amazon%20Redshift%20record



• Data integration between IBM Could Object Storage S3 and Amazon Redshift

  Template URL: https://<your-instance-id>/templates/Data%20integration%20between%20IBM%20Could%20Object%20Storage%20S3%20and%20Amazon%20Redshift



You must enable the Designer AI features in your containerized environment to access the App Connect templates. For more information, see The preloaded IBM App Connect templates.

You can also import these use cases directly into your App Connect Designer. These templates are added to a public GitHub repository at https://github.com/ot4i/app-connect-templates/tree/cp4i-templates/resources. For more information about the supported connectors, see App Connect Connectors.

For any inquiries on this blog or connector, contact Srihari at srihakum@in.ibm.com.

#AppConnect #amazon-redshift