Amazon Redshift¶

-- Typical data warehouse schema design
-- Fact table with foreign keys to dimension tables
CREATE TABLE sales_fact (
    sale_id BIGINT IDENTITY(1,1),
    date_key INTEGER REFERENCES dim_date(date_key),
    customer_key INTEGER REFERENCES dim_customer(customer_key),
    product_key INTEGER REFERENCES dim_product(product_key),
    store_key INTEGER REFERENCES dim_store(store_key),
    quantity INTEGER,
    amount DECIMAL(10,2),
    cost DECIMAL(10,2)
)
DISTSTYLE KEY
DISTKEY(customer_key)
SORTKEY(date_key, customer_key);

-- Dimension table
CREATE TABLE dim_customer (
    customer_key INTEGER IDENTITY(1,1),
    customer_id VARCHAR(50),
    customer_name VARCHAR(255),
    customer_segment VARCHAR(50),
    customer_region VARCHAR(50)
)
DISTSTYLE ALL
SORTKEY(customer_key);

-- Optimized table design with distribution and sort keys
CREATE TABLE sales_optimized (
    sale_date DATE,
    customer_id INTEGER,
    product_id INTEGER,
    store_id INTEGER,
    quantity INTEGER,
    amount DECIMAL(10,2),
    cost DECIMAL(10,2)
)
-- Choose distribution strategy based on join patterns
DISTSTYLE KEY
DISTKEY(customer_id)  -- Distribute by most frequently joined column
-- Sort by date for time-series queries
SORTKEY(sale_date, customer_id)
-- Enable compression
ENCODE AUTO;

-- Best practices for data loading
COPY sales_optimized
FROM 's3://my-data-bucket/sales-data/'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftS3Role'
FORMAT AS PARQUET
COMPUPDATE ON
STATUPDATE ON;

-- Maintenance commands
VACUUM sales_optimized;  -- Reclaim space and resort
ANALYZE sales_optimized; -- Update table statistics

-- Query optimization with materialized views
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', sale_date) as month,
    customer_id,
    SUM(amount) as total_sales,
    COUNT(*) as transaction_count
FROM sales_optimized
GROUP BY 1, 2;

-- Distribution styles comparison
-- DISTSTYLE EVEN - distribute rows evenly across nodes
CREATE TABLE table_even (id INTEGER, data VARCHAR(255))
DISTSTYLE EVEN;

-- DISTSTYLE KEY - distribute based on column values
CREATE TABLE table_key (id INTEGER, data VARCHAR(255))
DISTSTYLE KEY
DISTKEY(id);

-- DISTSTYLE ALL - replicate entire table on each node
CREATE TABLE table_all (id INTEGER, data VARCHAR(255))
DISTSTYLE ALL;

-- Sort key strategies
-- Single column sort key
CREATE TABLE table_single_sort (date DATE, id INTEGER, data VARCHAR(255))
SORTKEY(date);

-- Compound sort key (columns sorted in order)
CREATE TABLE table_compound_sort (date DATE, region VARCHAR(50), id INTEGER)
SORTKEY(date, region, id);

-- Interleaved sort key (equal weight to all columns)
CREATE TABLE table_interleaved_sort (date DATE, region VARCHAR(50), id INTEGER)
INTERLEAVED SORTKEY(date, region, id);

-- Load data from S3
COPY sales_fact
FROM 's3://data-lake/sales/year=2023/'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftS3Role'
FORMAT AS PARQUET
PARTITION AUTO;

-- Unload data to S3
UNLOAD ('SELECT * FROM sales_fact WHERE sale_date >= \'2023-01-01\'')
TO 's3://data-export/sales-2023/'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftS3Role'
FORMAT AS PARQUET
PARTITION BY (sale_date)
ALLOWOVERWRITE;

-- Query data in S3 using Redshift Spectrum
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'spectrum_db'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftSpectrumRole';

-- Query external table
SELECT COUNT(*)
FROM spectrum_schema.external_sales
WHERE sale_date >= '2023-01-01';

-- Query performance monitoring
-- Check running queries
SELECT
    query,
    pid,
    user_name,
    starttime,
    duration/1000000 as duration_seconds,
    substring(querytxt, 1, 100) as query_text
FROM stv_recents
WHERE status = 'Running'
ORDER BY starttime DESC;

-- Query performance history
SELECT
    query,
    userid,
    starttime,
    endtime,
    datediff(seconds, starttime, endtime) as duration,
    aborted,
    substring(querytxt, 1, 100) as query_text
FROM stl_query
WHERE starttime >= DATEADD(hour, -24, GETDATE())
ORDER BY duration DESC
LIMIT 20;

-- Table scan analysis
SELECT
    schema_name,
    table_name,
    size_mb,
    pct_used,
    unsorted,
    stats_off
FROM svv_table_info
ORDER BY size_mb DESC;

-- Disk usage by table
SELECT
    "schema",
    "table",
    size,
    pct_of_total
FROM svv_diskusage
ORDER BY size DESC
LIMIT 20;

-- Workload Management (WLM) queue configuration
-- Check current WLM configuration
SELECT * FROM stv_wlm_service_class_config;

-- Monitor query queue wait times
SELECT
    service_class,
    slots,
    query,
    slot_count,
    total_queue_time,
    total_exec_time
FROM stl_wlm_query
WHERE start_time >= DATEADD(hour, -1, GETDATE())
ORDER BY total_queue_time DESC;

-- Analyze query execution steps
SELECT
    query,
    step,
    rows,
    bytes,
    workmem,
    is_diskbased,
    label
FROM stl_query_summary
WHERE query = <query_id>
ORDER BY step;

-- Check for data distribution skew
SELECT
    slice,
    COUNT(*) as row_count
FROM stv_tbl_perm
WHERE name = 'sales_fact'
GROUP BY slice
ORDER BY slice;

-- User and group management
-- Create groups
CREATE GROUP analysts;
CREATE GROUP developers;
CREATE GROUP admins;

-- Create users
CREATE USER analyst1 PASSWORD 'SecurePassword123!' IN GROUP analysts;
CREATE USER dev1 PASSWORD 'SecurePassword123!' IN GROUP developers;

-- Grant permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO GROUP analysts;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA staging TO GROUP developers;

-- Row-level security
CREATE TABLE sales_rls (
    sale_id INTEGER,
    region VARCHAR(50),
    amount DECIMAL(10,2),
    sale_date DATE
);

-- Enable RLS
ALTER TABLE sales_rls ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE RLS POLICY region_policy ON sales_rls
FOR ALL
TO analyst_role
USING (region = current_setting('app.current_region'));

-- Column-level security (data masking)
CREATE TABLE customer_sensitive (
    customer_id INTEGER,
    name VARCHAR(255),
    ssn VARCHAR(11) ENCODE raw,
    email VARCHAR(255)
);

-- Apply masking policy
ALTER TABLE customer_sensitive ALTER COLUMN ssn
SET MASKING USING (CASE WHEN current_user IN ('admin', 'dba')
                   THEN ssn
                   ELSE 'XXX-XX-' || RIGHT(ssn, 4) END);