by Dibyendu Datta | December 27, 2023

How to Install and Use an Amazon Redshift Connector

As organizations navigate the complex landscape of business intelligence, the CData Amazon Redshift Connector becomes a cornerstone, elevating analytical processes. By integrating with Amazon Redshift, it empowers users to harness the full potential of their data, allowing organizations to gain more insights and become more efficient in their operations.

This article helps you through the installation steps of the CData Amazon Redshift Python Connector, empowering you to fully leverage its capabilities.

What is Amazon Redshift?

Amazon Redshift is a fully managed, cloud-based data warehousing service provided by Amazon Web Services (AWS). Designed to handle large datasets and complex queries, it offers high-performance analytics for organizations seeking scalable and cost-effective solutions for their data storage and analysis needs.

Fundamentals

• Architecture: Amazon Redshift uses a clustered architecture, allowing for parallel processing of queries across multiple nodes.
• Columnar storage: Data is stored in a columnar format, optimizing query performance and compression.
• Scalability: Redshift offers the flexibility to scale up or down based on changing data requirements.

Key Features

• Performance: Fast query performance supports complex analytical queries.
• Ease-of-use: User-friendly interfaces and SQL compatibility make it accessible for both technical and non-technical users.
• Automation: Automated backups, maintenance, and scaling ensure operational efficiency.
• Security: Robust security measures, including encryption at rest and in transit, protects your data.
• Integration: Integration with various AWS services and third-party tools enhances its versatility.
• Cost-effective: A pay-as-you-go pricing model allows organizations to pay only for the resources they consume.

Applications

• Business intelligence (BI): Supports BI tools for interactive data analysis and reporting.
• Data warehousing: Serves as a centralized repository for large datasets, facilitating efficient data storage and retrieval.
• Data analysis: Enables organizations to perform complex data analysis and gain valuable insights.
• ETL (Extract, Transform, Load): Facilitates ETL processes by efficiently loading and transforming data.

What is the CData Amazon Redshift Python Connector?

The CData Amazon Redshift Python Connector is a powerful tool designed by CData to facilitate seamless and efficient connectivity between Python applications and Amazon Redshift. With built-in optimized data processing, the CData Python Connector offers unmatched performance for interacting with live Redshift data.

When issuing complex SQL queries to your application, the connector pushes supported SQL operations like filters and aggregations directly to Amazon Redshift and utilizes the embedded SQL engine to process any unsupported operations client-side (often in SQL functions and JOIN operations). In addition, the connector's built-in dynamic metadata querying allows you to work with and analyze your Redshift data just like you would any other data source in Python.

This connector empowers developers and data professionals to interact with Amazon Redshift data directly from their Python applications, enabling smooth data integration, retrieval, and manipulation.

Features

• Direct connectivity: Specifically designed for direct connection to Amazon Redshift, built on PostgreSQL 8.0.2.
• Live data: Offers real-time access to live Amazon Redshift data for dynamic and up-to-the-minute information.
• Comprehensive querying: Provides full support for data aggregation and intricate JOINs in SQL queries, providing comprehensive query capabilities.
• Security: Ensures secure connectivity through modern cryptography measures, including TLS 1.2, SHA-256, ECC, and more.
• Enhanced reporting: Seamlessly integrates with leading BI, reporting, and ETL tools, as well as custom applications.

Specifications

• Built using Python Database API (DB-API) Modules for Redshift, offering bi-directional access.
• Allows pure SQL queries to Amazon Redshift data, enabling efficient data retrieval.
• Enables integration with popular Python tools such as Pandas, SQLAlchemy, Dash, and petl.
• Simplifies data exploration of Redshift through a straightforward command-line interface.
• Provides full Unicode support for data, parameter, and metadata, ensuring compatibility with diverse character sets.

How to install the Amazon Redshift Connector

So, how do you install the CData Amazon Redshift Python Connector? We'll explain how to create a simple Python script to connect to and query the Amazon Redshift data.

IAM authentication and activate Amazon Redshift

Enabling Amazon Redshift in IAM (Identity and Access Management) allows for proper access control and permissions, ensuring secure and authorized interaction with the Redshift cluster. This step is essential for managing and maintaining the security posture of your Amazon Redshift cluster within the AWS environment and is crucial to align access control with security requirements and best practices in AWS.

1. Enter 12-digit Account ID and sign in with IAM credentials.
2. Click on “Amazon Redshift”
   1. 
3. “Resume” or Turn the Cluster on
4. Install Amazon Redshift Connector to access the Redshift database

Download and install the connector

Fill in the appropriate contact information and download the Amazon Redshift Python Connector from the CData website.

Windows installation

The following command installs the appropriate package for a 64-bit Python 3.10 distribution on a Windows environment (where xxxx is the version number):

pip install PATH\TO\cdata_redshift_connector-23.0.xxxx-cp311-cp311-win_amd64.whl

Linux/Mac installation

The command below installs the appropriate package for a Python 3.10 distribution on a Linux environment (where xxxx is the version number):

pip install PATH/TO/cdata_redshift_connector-23.0.xxxx-python311.tar.gz

Licensing

After the installation is complete, a separate step is needed to activate a license for the connector. Among the CData assets in the distribution's site packages, there is an install-license tool that activates this license. From within the distribution's site-packages folder, after navigating to the "cdata/installlic_redshift" folder, simply use a command like the below to activate the license. Omitting the <key> argument activates a trial license:

• Windows: ./install-license.exe <key>
• Linux/Mac:./install-license.sh <key>

Connect to query data

Once the module is installed, you can use it to work with Amazon Redshift data directly from Python. We explain each step in the process below, and include a full script. Start by opening a Python shell by calling python in a terminal.

Import the module

In the Python shell, import the module: import cdata.redshift as redshift_mod

Connect to data

With the module imported, you can use the connect() method to establish a connection to your data source, using a semi-colon (;) separated string of name-value pairs for the required connection properties.

To connect to Redshift, set the following properties:

• Server: Set this to the host name or IP address of the cluster hosting the Database you want to connect to.
• Port: Set this to the port of the cluster.
• Database: Set this to the name of the database, or leave this blank to use the default database of the authenticated user.
• User: Set this to the username you want to use to authenticate to the Server.
• Password: Set this to the password you want to use to authenticate to the Server.

You can obtain the Server and Port values in the AWS Management Console:

1. Open the Amazon Redshift console (http://console.aws.amazon.com/redshift).
2. On the Clusters page, click the name of the cluster.
3. On the Configuration tab for the cluster, copy the cluster URL from the connection strings displayed.

conn = redshift_mod.connect("User=admin;Password=admin;Database=dev;Server=examplecluster.my.us-west-2.redshift.amazonaws.com;Port=5439;")

Query data in Python

After establishing the connection, use the execute function to create a cursor (a Python object that helps execute queries and fetch records from a database).

cursor = conn.execute("SELECT ShipName, ShipCity FROM \"sales_db\".\"public\".Orders")

Use the fetchall function to retrieve the data and store it in a result set.

result_set = cursor.fetchall()

Display the results

Lastly, iterate over the result set and perform actions and operations on the data. In this case, we simply print each row.

for row in result_set:
      print(row)

Full Python script

The Python script below can be executed in a Python shell, saved in a Python script (a .py file), or run in an IDE.

import cdata.redshift as redshift_mod
conn = redshift_mod.connect("User=admin;Password=admin;Database=dev;Server=examplecluster.my.us-west-2.redshift.amazonaws.com;Port=5439;")
cursor = conn.execute("SELECT ShipName, ShipCity FROM \"sales_db\".\"public\".Orders")
result_set = cursor.fetchall()
for row in result_set:
      print(row)

You can also refer to our YouTube video to see the connectivity in action.

CData Amazon Redshift Drivers & Connectors

By leveraging CData Amazon Redshift Drivers (JDBC, ODBC) & Connectors (Python, Power BI, Tableau), businesses can effortlessly connect and interact with Amazon Redshift from various applications, including business intelligence, analytics, reporting, ETL, and custom solutions,

Use Cases

• Business intelligence: With bi-directional data drivers and the robust functionality of CData Amazon Redshift drivers, businesses can integrate Redshift data effortlessly into their BI systems. This integration empowers BI tools like Power BI, Tableau, etc. to extract valuable insights, facilitating data-driven decision-making.
• Analytics: The CData Redshift Connector offers a transformative solution for analytics. Utilizing the advanced capabilities of CData Connect Cloud, businesses can seamlessly access and analyze Amazon Redshift data using simple SQL statements. This integration ensures a dynamic analytics environment, enabling organizations to derive meaningful patterns and trends from their data.
• ETL (Extract, Transform, Load): The CData Redshift Connector is a game-changer for ETL processes. With the efficiency of CData Sync, organizations can seamlessly integrate Amazon Redshift data into their ETL workflows, both as a data source and a replication destination. This ensures a streamlined data pipeline, allowing for efficient data extraction, transformation, and loading processes.

The CData difference

CData Amazon Redshift connectivity solutions redefine data connectivity, enabling seamless integration with Amazon Redshift. They serve as a robust solution, offering bi-directional access to your Amazon Redshift database. Explore the comprehensive capabilities of CData Amazon Redshift Drivers & Connectors for a transformative approach to connect and leverage Amazon Redshift data in diverse applications. 

As always, our support team is ready to answer any questions you have. Have you joined the CData Community? Ask questions, get answers, and share your knowledge in CData connectivity tools. Join us!