AWS Redshift Overview

AWS Redshift offers a highly optimized method for storing time-based data, which is often generated at high volume and velocity in IoT solutions.

Qubitro Storage Function for AWS Redshift forwards either raw or processed data directly into predefined AWS Redshift collections.

Prerequisites

Active Qubitro account on the Enterprise plan
AWS Redshift cluster (opens in a new tab)
AWS Redshift Credential

Allow 145.40.114.97/32 by configuring security groups, and the VPC settings on AWS Console.

Reserved keys

The time key is reserved and will be ignored by the function.

Configuring the Storage Function

Select a device that you want to create a function for and navigate to the detail page. Click on the Functions tab, then, click on the Create function button.

Choose `AWS Redshift` from the list.

Storage Function List

Configure function basics

Choose the AWS Redshift Credential and provide basic information for the function including name and description and click on Next.

Storage Function Configuration AWS Redshift Basics

Configure AWS Redshift options

Provide the table name that you want to create, or enable the toggle to automatically create a table with the same Device ID, and then click on Done.

Storage Function Configuration AWS Redshift Config

Verify the incoming data

In this example, the following data is published to the Qubitro with an HTTP data source.

{
  "Pressure_PSI": 55.29,
  "Temperature_Celsius": 15.62,
  "battery_level": 3.29406,
  "firmware_version": 6,
  "node_id": 0,
  "reserve_byte": 0,
  "rssi": 100,
  "time": "2023-10-25T07:49:00.673679Z",
  "transmission_count": 72,
  "type": 26,
  "is_active": true,
  "status_message": "Operational"
}

Run the following query on the Amazon Redshift Query editor:

SELECT "time", "pressure_psi", "status_message" FROM "PASTE_DEVICE_ID" LIMIT 5;

You can verify that the raw data is inserted successfully and the time column is automatically set.

AWS Redshift Data

Insert Transformed Data

You can also transform data on the fly before inserting it into the AWS Redshift collection by enabling and selecting a Transformation Function.

The following example demonstrates how to insert modified data into AWS Redshift while simultaneously inserting raw data into Qubitro.

An example transformation function:

Transformation Function Configuration

Raw data on device data table:

Raw Data View Qubitro Table

Transformed data:

{
  "Battery": 3.29406,
  "Pressure": 55.29,
  "Status": "Operational",
  "Temperature": 15.62,
  "time": "2023-11-08T19:05:47.91485Z"
}

Run the following query to list column names on the Amazon Redshift Query editor:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'PASTE_DEVICE_ID or TABLE NAME';

You can verify that the transformed data is inserted successfully and the time column is automatically set.

AWS Redshift Transformed Data

If you need further help, you can browse support plan options.

Start building today

Collect, process, and activate device data. Scale from one device to thousands.

Talk to an expert

MongoDB Time Series MQTT Publish