Redshift

-- 테이블 생성 (분산 키, 정렬 키 설정)
CREATE TABLE sales (
    sale_id BIGINT IDENTITY(1,1),
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    region VARCHAR(50)
)
DISTSTYLE KEY DISTKEY(customer_id)  -- 분산 키
SORTKEY(sale_date);                  -- 정렬 키

-- COPY: S3에서 데이터 로드 (가장 빠른 방법)
COPY sales
FROM 's3://my-bucket/sales-data/'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftLoadRole'
FORMAT AS PARQUET;

-- CSV 로드 (압축 파일 지원)
COPY sales
FROM 's3://my-bucket/sales.csv.gz'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftLoadRole'
CSV GZIP
IGNOREHEADER 1
DATEFORMAT 'YYYY-MM-DD';

-- 분석 쿼리
SELECT
    DATE_TRUNC('month', sale_date) AS month,
    region,
    COUNT(*) AS num_sales,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY 1, total_revenue DESC;

-- 윈도우 함수
SELECT
    product_id,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales,
    RANK() OVER (PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY amount DESC) AS monthly_rank
FROM sales;

-- Redshift Spectrum: S3 외부 테이블
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'my_glue_database'
IAM_ROLE 'arn:aws:iam::123456789:role/SpectrumRole'
REGION 'us-east-1';

-- S3 데이터 직접 쿼리 (Redshift 테이블과 조인 가능)
SELECT
    s.product_id,
    p.product_name,
    SUM(s.amount) AS total
FROM sales s
JOIN spectrum_schema.products p ON s.product_id = p.id
GROUP BY 1, 2;

-- 쿼리 성능 분석
EXPLAIN
SELECT * FROM sales WHERE region = 'Seoul';

-- 테이블 통계 갱신
ANALYZE sales;

-- 디스크 공간 회수 (DELETE 후)
VACUUM sales;

-- Python에서 연결 (psycopg2 또는 redshift_connector)
-- import redshift_connector
-- conn = redshift_connector.connect(
--     host='cluster.xxx.region.redshift.amazonaws.com',
--     database='dev',
--     user='admin',
--     password='password'
-- )
-- cursor = conn.cursor()
-- cursor.execute("SELECT * FROM sales LIMIT 10")