Automating-data-transfer-to-Redshift-Using-Python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "import psycopg2\n",
    "\n",
    "# Set up credentials\n",
    "aws_access_key_id = '<AWS_ACCESS_KEY_ID>'\n",
    "aws_secret_access_key = '<AWS_SECRET_ACCESS_KEY>'\n",
    "region_name = '<REGION_NAME>'\n",
    "cluster_identifier = '<CLUSTER_IDENTIFIER>'\n",
    "database_name = '<DATABASE_NAME>'\n",
    "table_name = '<TABLE_NAME>'\n",
    "csv_file_path = '<CSV_FILE_PATH>'\n",
    "iam_role = '<IAM_ROLE_ARN>'\n",
    "\n",
    "# Create Redshift client\n",
    "redshift = boto3.client('redshift', aws_access_key_id=aws_access_key_id, aws_secret_access_key=aws_secret_access_key, region_name=region_name)\n",
    "\n",
    "# Create Redshift cluster\n",
    "redshift.create_cluster(\n",
    "    ClusterIdentifier=cluster_identifier,\n",
    "    NodeType='dc2.large',\n",
    "    MasterUsername='admin',\n",
    "    MasterUserPassword='Password123',\n",
    "    ClusterSubnetGroupName='default',\n",
    "    VpcSecurityGroupIds=['<SECURITY_GROUP_ID>'],\n",
    "    PubliclyAccessible=True,\n",
    ")\n",
    "\n",
    "# Wait for cluster to become available\n",
    "redshift.get_waiter('cluster_available').wait(ClusterIdentifier=cluster_identifier)\n",
    "\n",
    "# Create database table\n",
    "with psycopg2.connect(\n",
    "    host=redshift.describe_clusters(ClusterIdentifier=cluster_identifier)['Clusters'][0]['Endpoint']['Address'],\n",
    "    port=5439,\n",
    "    dbname=database_name,\n",
    "    user='admin',\n",
    "    password='Password123',\n",
    ") as conn:\n",
    "    with conn.cursor() as cur:\n",
    "        cur.execute(f\"CREATE TABLE {table_name} (col1 VARCHAR(50), col2 VARCHAR(50), col3 INT)\")\n",
    "        conn.commit()\n",
    "\n",
    "# Load data from CSV file into table\n",
    "with psycopg2.connect(\n",
    "    host=redshift.describe_clusters(ClusterIdentifier=cluster_identifier)['Clusters'][0]['Endpoint']['Address'],\n",
    "    port=5439,\n",
    "    dbname=database_name,\n",
    "    user='admin',\n",
    "    password='Password123',\n",
    ") as conn:\n",
    "    with conn.cursor() as cur:\n",
    "        cur.execute(f\"COPY {table_name} FROM '{csv_file_path}' IAM_ROLE '{iam_role}' DELIMITER ',' CSV HEADER\")\n",
    "        conn.commit()"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}