Migrate data from MSSQL to Redshift

To migrate data from MSSQL to Redshift using the COPY command, you can follow these steps:

1. Extract data from MSSQL: You can extract data from MSSQL using any ETL tool or using Python libraries like pyodbc or pandas. Here is an example code snippet using pandas library:

import pandas as pd
import pyodbc

# Create a connection to MSSQL
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                      'SERVER=your_server_name;'
                      'DATABASE=your_database_name;'
                      'UID=your_user_name;'
                      'PWD=your_password;')

# Write the SQL query to extract data
sql_query = "SELECT * FROM your_table_name;"

# Use pandas to read data into a dataframe
df = pd.read_sql(sql_query, conn)

# Close the connection
conn.close()

2. Load data into S3: Once you have extracted data into a dataframe, you can write it to S3 using the to_csv() method of pandas dataframe. Here is an example code snippet:

import boto3

# Set up the credentials to access S3
session = boto3.Session(
    aws_access_key_id='YOUR_ACCESS_KEY',
    aws_secret_access_key='YOUR_SECRET_KEY',
)

# Write the dataframe to S3
s3 = session.resource('s3')
bucket_name = 'your_bucket_name'
key = 'your_key_name.csv'
s3.Object(bucket_name, key).put(Body=df.to_csv(index=False, header=True))

3. Load data into Redshift: Once you have written data to S3, you can use the COPY command to load data into Redshift. Here is an example COPY command:

COPY your_redshift_table_name
FROM 's3://your_bucket_name/your_key_name.csv'
IAM_ROLE 'arn:aws:iam::123456789012:role/your_redshift_iam_role_name'
CSV
IGNOREHEADER 1
NULL AS 'NULL'

In the above COPY command, you need to replace your_redshift_table_name with the name of your Redshift table, your_bucket_name with the name of your S3 bucket, your_key_name.csv with the name of the CSV file you wrote to S3, your_redshift_iam_role_name with the name of the IAM role you created for Redshift, and any other options as per your requirements.