How Astera Centerprise Streamlines Connectivity to Amazon Redshift Database

By |2020-12-11T11:44:33+00:00December 11th, 2020|

Amazon Redshift is a cloud-based solution that falls under the Amazon Web Services umbrella. The low maintenance requirements, scalability, speed, and efficient compression features make it a popular data storage option for businesses dealing with large amounts of data.

To make data integration with this database (DB) easier, Astera Centerprise has launched a pre-built connector, allowing code-free connectivity to Redshift. This connector can be used as both a source and destination object to access and store data. Additionally, users can also connect to the Redshift database use it for DB lookup and SQL statement lookup.

How to Connect to Redshift Database with Astera Centerprise

Astera Centerprise features a drag-and-drop environment, allowing business users to connect to the Redshift DB without typing long chunks of code or specifying connection strings. Easily configure Redshift connectivity to process data or perform database lookups by selecting Amazon Redshift from the drop-down list of supported databases.

Redshift connector in the list of data providers

Figure 1: List of data providers supported by Astera Centerprise.

Redshift Database as a Source

Drag the database table source object from the toolbox and drop it on to the designer window to connect to the Redshift database and use it as a source object. Next, you can configure it by selecting Redshift as the data provider from the drop-down list.

In the next step, we need to select the table from which the data will be sourced. In this case, we are selecting a table with employee details named public.orders. We can click on the Partition Table for Reading option to break the table into smaller segments that will be read individually. This option can be selected to reduce load on the database and improve performance. Here, we also have the option to select the key field to divide the table in partitions.

Another option in the database properties table is for specifying the Read Strategy. Here, we can decide whether we want to read the complete data (Full Load) or just updated records (Incremental Load Based on Audit Fields).

Pick Table and Read Strategy for Redshift table

Figure 2: Selecting the table and Read Strategy for our Database Source

The next screen shows the Layout Builder for the Database Source Table. Here, we can see the datatypes and lengths of each field along with a few other details.

Redshift Database Table Layout Builder

Figure 3: The Layout Builder of the Redshift Database Table with details about the datatype and length of each field.

The data from this Redshift table can be processed in multiple ways using various built-in transformations available in Centerprise and loaded to a file, database, or any other available destination.

Filter transformation being applied to Redshift table

Figure 4: A dataflow showing a filter being applied to data sourced from a Redshift table and mapped on to an Excel destination object

The screenshot above shows a dataflow that filters data from the Orders table using a Filter transformation and mapped on an Excel destination file named FilteredRedshiftData.

Redshift Database as a Destination

Users can also connect to the Amazon Redshift database and configure it as a destination object. For this, the database table destination object needs to be dragged from the toolbox and dropped on to the designer. Next, we need to point the destination object to the Redshift database as follows:

Configuring a Redshift destination table

Figure 5: Configuring a database table destination object with Redshift as the data provider.

The image also shows an option where users can add their Amazon Simple Storage Service (S3) credentials to load data in bulk to the Redshift DB.

Once Redshift has been selected as the data provider, the user needs to decide whether they want to pick an existing table, create a new one, or overwrite the data present on an existing one. In this case, we have created a new table in the database and named it WebAggregate.

 

Create a new Redshift table

Figure 6: A new database table named WebAggregate is created to load data in bulk.

In this example, data from a Database source object named WebConnectionRegistration is aggregated and passed to the WebAggregate database table. The complete dataflow is as follows:

Mapping aggregated data to a Redshift destination table

Figure 7: Data from a database table is aggregated and mapped to a Redshift destination table.

Make the Most of Bi-directional Connectivity to Redshift with Astera Centerprise

Data on the Redshift database can be compressed far more efficiently than on row-based databases, reducing processing time and time-to-insight. Owing to the benefits of Redshift over on-premise databases, businesses that need to store and process large amounts of data are looking for ways to create a well-connected enterprise ecosystem by connecting to Redshift. Unlike connectors that require a lot of manual configuration, Astera Centerprise’s Redshift connector makes accessing and storing data on the database very simple and convenient.

Here are a few benefits of using Astera Centerprise’s native Redshift connector:

Uninterrupted Connectivity

Users don’t have to go through a long and complicated configuration process to connect to Redshift. Establishing a connection is as simple as selecting the correct data provider from a drop-down list and pointing the database object to the correct table.

Ease of Access

Connecting to Redshift using Astera Centerprise’s built-in connector allows users to retrieve, transform, and load data to the required destination system with minimal IT intervention.

Workflow Automation

With the help of Astera Centerprise, users can automate their Redshift integration workflows to reduce the time required for analysis and increase process efficiency.

Amazon Redshift’s efficient data storage, parallel processing, and simple querying capabilities make it a popular choice for businesses interested in data analysis. With Astera Centerprise’s native connector, businesses can easily use Redshift data to derive insights and make data-driven decisions.

Download the trial version of Astera Centerprise and make the most of Redshift connectivity.

Centerprise Data Extractor Banner