Redshift

# Install the PostgreSQL adapter (Redshift speaks PostgreSQL)
import psycopg2
import pandas as pd
import numpy as np

# Connection details - typically from environment variables
HOST = 'flipkart-cluster.abc123.us-east-1.redshift.amazonaws.com'
PORT = 5439  # Standard Redshift port
DATABASE = 'analytics'

# Create the connection object
conn = psycopg2.connect(
    host=HOST,
    port=PORT,
    database=DATABASE,
    user='analytics_user',  # Your Redshift username
    password='secure_password'  # Better: use environment variable
)

# Test connection with a simple query
test_query = "SELECT CURRENT_TIMESTAMP, VERSION()"
result = pd.read_sql(test_query, conn)

# Query massive ecommerce data - 50M rows, but Redshift handles it
revenue_query = """
SELECT 
    product_category,
    COUNT(*) as order_count,
    SUM(revenue) as total_revenue,
    AVG(rating) as avg_rating
FROM ecommerce_orders 
WHERE date >= '2023-01-01'
GROUP BY product_category
ORDER BY total_revenue DESC
"""

# Execute the query and load into DataFrame
import time
start_time = time.time()

df_revenue = pd.read_sql(revenue_query, conn)

end_time = time.time()
print(f"Query executed in {end_time - start_time:.2f} seconds")
print(f"Retrieved {len(df_revenue)} product categories")
df_revenue.head()

# Daily order trends with window functions - Redshift speciality
daily_trends_query = """
SELECT 
    date,
    COUNT(*) as daily_orders,
    SUM(revenue) as daily_revenue,
    AVG(COUNT(*)) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg_orders
FROM ecommerce_orders 
WHERE date >= CURRENT_DATE - 90
GROUP BY date
ORDER BY date
"""

# Execute and process the time series data
df_daily = pd.read_sql(daily_trends_query, conn)

# Convert date column to datetime for proper plotting
df_daily['date'] = pd.to_datetime(df_daily['date'])

# Show recent trends
print(f"Latest 7 days of data:")
print(df_daily.tail(7))

# Calculate key metrics
peak_day = df_daily.loc[df_daily['daily_orders'].idxmax()]
print(f"\nPeak orders: {peak_day['daily_orders']} on {peak_day['date'].date()}")

# Check table distribution - this reveals performance bottlenecks
distribution_query = """
SELECT 
    schema as table_schema,
    "table" as table_name,
    diststyle,
    distkey,
    sortkey1
FROM pg_table_def 
WHERE schemaname = 'public' 
  AND tablename = 'ecommerce_orders'
"""

df_distribution = pd.read_sql(distribution_query, conn)
print("Current table structure:")
print(df_distribution)

# Analyze query performance with EXPLAIN
explain_query = """
EXPLAIN
SELECT customer_age, COUNT(*) as order_count
FROM ecommerce_orders 
WHERE date >= '2023-01-01'
  AND product_category = 'Electronics'
GROUP BY customer_age
ORDER BY order_count DESC
"""

# Get query execution plan  
df_explain = pd.read_sql(explain_query, conn)
print("Query execution plan:")
for idx, row in df_explain.iterrows():
    print(f"{idx+1}. {row['QUERY PLAN']}")

# Bulk load from S3 - the fastest way to get data into Redshift
copy_command = """
COPY ecommerce_orders 
FROM 's3://hdfc-data-lake/transactions/2023/10/'
IAM_ROLE 'arn:aws:iam::123456:role/RedshiftCopyRole'
CSV
GZIP
DELIMITER ','
IGNOREHEADER 1
DATEFORMAT 'YYYY-MM-DD'
"""

# Execute the COPY command
cursor = conn.cursor()
cursor.execute(copy_command)
conn.commit()