Simplified Snowflake to Redshift Migration 3 Easy Steps

Cloud-based data storage and warehousing solutions have now become the preferred choice for most modern businesses. This is primarily because leveraging a Cloud-based solution makes it easier for businesses to ensure that their Data Warehouses grow along with their unique data requirements and scale up or down on-demand or automatically to accommodate all peak-workload periods.

Cloud-based Data Warehouses enable businesses to efficiently settle all Data Availability and Security concerns as they support seamless Database Replication across multiple geographical locations, in addition to numerous backup and data recovery options. Two of the most popular Cloud-based Data Warehousing solutions are Amazon Redshift and Snowflake.

Both Data Warehouses offer their users a wide variety of features. Amazon Redshift offers its users exceptional features such as robust security and compliance across all its tiers along with discounts on long-term plans whereas Snowflake gives users the ability to scale Compute or Storage Nodes separately based on their requirements, thereby reducing costs significantly but offers security and compliance based on their tiers. Hence, based on the use case, businesses sometimes feel the need to migrate from one Data Warehouse to another.

This article will provide you with an in-depth understanding of how you can set up Snowflake to Redshift Migration seamlessly.

Steps to Set up Snowflake to Redshift Migration

Users can set up Snowflake to Redshift Migration by implementing the following steps:

Snowflake to Redshift Migration Step 1: Installing Required Packages

The following Python packages are required for this process:

• Pandas: Pandas for Python can be installed by implementing the following command:

pip install pandas

• Snowflake Connector: This package is required to export all data from the Snowflake Data Warehouse as a Pandas Dataframe for Snowflake to Redshift Migration. It can be installed by running the following command:

pip install snowflake-connector-python==<version>

<version> in the above command has to be replaced with the version of Python installed on your local system.

• Pandas_redshift: This package is required to store the data extracted from Snowflake as a Pandas Dataframe into Amazon Redshift and fully perform the Snowflake to Redshift Migration. It can be installed by running the following command:

pip install pandas-redshift

More information on these packages can be found on the Pandas, Snowflake Connector, and Pandas_Redshift documentation.

Snowflake to Redshift Migration Step 2: Extracting Data from Snowflake

In order to set up Snowflake to Redshift Migration, the data first has to be extracted from Snowflake. This can be done using Snowflake’s Python connector. The following code can be used to extract data from Snowflake using Python:

import snowflake.connector
import pandas as pd

ctx = snowflake.connector.connect(
          host=host,
          user=user,
          password=password,
          account=account,
          warehouse=warehouse,
          database=database,
          schema=schema,
          protocol='https',
          port=port)

# Create a cursor object.
cur = ctx.cursor()

# Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)

# Fetch the result set from the cursor and deliver it as the Pandas DataFrame.
df = cur.fetch_pandas_all()

You will have to make the following changes to your Snowflake to Redshift Migration code based on your unique requirements:

• The first function call to connect Python to Snowflake i.e. snowflake.connector.connect() should have the required parameters of your Snowflake Data Warehouse.
• The SQL query being executed should extract the data that you wish to load into Redshift. Information on queries supported by Snowflake can be found here.
• You can choose to execute multiple queries and create multiple Pandas Dataframes to suit your data and business needs.

Snowflake to Redshift Migration Step 3: Loading Data into Amazon Redshift

Once the required data has been extracted from Snowflake and stored in Pandas Dataframes, you will now need to load it into Amazon Redshift to complete your Snowflake to Redshift Migration. You can load your Pandas Dataframes into Amazon Redshift by running the following Python script:

import pandas_redshift as pr
pr.connect_to_redshift(dbname = <dbname>,
                        host = <host>,
                        port = <port>,
                        user = <user>,
                        password = <password>)
# Connect to S3
pr.connect_to_s3(aws_access_key_id = <aws_access_key_id>,
                aws_secret_access_key = <aws_secret_access_key>,
                bucket = <bucket>,
                subdirectory = <subdirectory>
                # As of release 1.1.1 you are able to specify an aws_session_token (if necessary):
                # aws_session_token = <aws_session_token>
                )

# Write the DataFrame to S3 and then to redshift
pr.pandas_to_redshift(data_frame = data,
                        redshift_table_name = <redshift_table_name>, append=False)

You will have to make the following changes to your Snowflake to Redshift Migration code that you execute based on your unique requirements:

• The first function call to connect Python to Amazon Redshift i.e. pr.connect_to_redshift() should have the required parameters of your Amazon Redshift Data Warehouse.
• The second function call to connect Python to Amazon S3 i.e. pr.connect_to_s3() should have the required parameters associated with your Amazon S3 account and buckets. This call is required since this Python package first loads data into Amazon S3 and that data is then loaded into Amazon Redshift from Amazon S3.
• The third function call to load your Pandas Dataframe to Amazon Redshift i.e. pr.pandas_to_redshift() should have the required information about the Pandas Dataframe you wish to import into Amazon Redshift along with the name of the table in which it should be stored. If the append parameter is set as False, any existing table with the same name in Amazon Redshift will be dropped and a new one will be created to store the Pandas Dataframe, and if it is set as True, it will append the Pandas Dataframe to the end of an existing table.
• If multiple Pandas Dataframes were created to extract the necessary data from Snowflake, you will have to make multiple calls to store that data in Amazon Redshift.

Conclusion

This article provided you with a step-by-step guide on how you can set up Snowflake to Redshift Migration seamlessly using Python. However, this process can be challenging for someone who does not have enough technical knowledge and expertise in Python, Snowflake, and Amazon Web Services (AWS) environment.

Visit our Website to Explore Hevo

Most database migration tasks including Snowflake to Redshift Migration, if done manually, require immense engineering bandwidth and resources for the development and maintenance of Data Pipelines. Hence, businesses can instead use existing automated No-code platforms like Hevo.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready. 

Frequently Asked Questions