// 애플리케이션 코드에서
1. MySQL 연결 → 고객 데이터 조회
2. PostgreSQL 연결 → 주문 데이터 조회
3. 메모리에서 두 데이터 조인
4. 결과 처리

-- PostgreSQL에서 바로
SELECT c.name, COUNT(o.order_id) as order_count
FROM mysql_customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

# Ubuntu/Debian
sudo apt-get install postgresql-14-mysql-fdw

# CentOS/RHEL
sudo yum install mysql_fdw_14

# macOS (Homebrew)
brew install mysql_fdw

-- PostgreSQL에 접속해서
CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host '192.168.1.100',
        port '3306'
    );

CREATE USER MAPPING FOR postgres
    SERVER mysql_server
    OPTIONS (
        username 'mysql_user',
        password 'mysql_password'
    );

CREATE FOREIGN TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP
)
SERVER mysql_server
OPTIONS (
    dbname 'mydb',
    table_name 'customers'
);

-- 간단한 조회
SELECT * FROM customers LIMIT 10;

-- 조인도 해보고
SELECT c.name, o.order_date, o.total_amount
FROM customers c
JOIN local_orders o ON c.customer_id = o.customer_id
WHERE o.order_date > '2024-01-01';

-- 집계도 가능!
SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as new_customers
FROM customers
GROUP BY month
ORDER BY month DESC;

-- 나쁜 예: 모든 데이터를 가져온 후 필터링
SELECT * FROM (
    SELECT * FROM foreign_table
) sub
WHERE created_at > '2024-01-01';

-- 좋은 예: 조건을 직접 명시
SELECT * FROM foreign_table
WHERE created_at > '2024-01-01';

ALTER SERVER mysql_server 
OPTIONS (ADD use_remote_estimate 'true');

ALTER SERVER mysql_server 
OPTIONS (ADD fetch_size '10000');

-- 외부 테이블에 인덱스 정보 추가
CREATE INDEX ON foreign_table (customer_id);

-- 실제로는 생성되지 않고, 힌트만 제공됨

-- 외부 데이터를 로컬에 캐싱
CREATE MATERIALIZED VIEW cached_customers AS
SELECT * FROM foreign_customers
WHERE active = true;

-- 주기적으로 갱신
REFRESH MATERIALIZED VIEW cached_customers;

-- Oracle의 계좌 정보와 PostgreSQL의 앱 사용 로그 조인
SELECT 
    o.customer_name,
    o.account_balance,
    COUNT(a.login_time) as monthly_logins,
    MAX(a.login_time) as last_login
FROM oracle_accounts o
LEFT JOIN app_logs a ON o.customer_id = a.customer_id
WHERE a.login_time > CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.customer_name, o.account_balance
ORDER BY monthly_logins DESC;

-- 여러 소스의 데이터를 한 번에 조회
CREATE VIEW realtime_dashboard AS
SELECT 
    p.product_name,
    m.order_count,
    r.avg_rating,
    red.current_stock
FROM products p
LEFT JOIN (
    SELECT product_id, COUNT(*) as order_count
    FROM mysql_orders
    WHERE order_date = CURRENT_DATE
    GROUP BY product_id
) m ON p.product_id = m.product_id
LEFT JOIN (
    SELECT product_id, AVG(rating) as avg_rating
    FROM mongo_reviews
    GROUP BY product_id
) r ON p.product_id = r.product_id
LEFT JOIN redis_inventory red ON p.product_id = red.product_id;

-- 1단계: 외부 테이블로 MySQL 데이터 접근
CREATE FOREIGN TABLE mysql_users ...;

-- 2단계: 로컬 테이블로 복사
CREATE TABLE local_users AS
SELECT * FROM mysql_users;

-- 3단계: 점진적으로 애플리케이션 전환
-- 일부는 local_users, 일부는 mysql_users 사용

-- 4단계: 모든 전환 완료 후 외부 테이블 제거
DROP FOREIGN TABLE mysql_users;

-- 재능 카테고리별 거래 분석
SELECT 
    u.user_name,
    u.talent_category,
    COUNT(t.transaction_id) as total_transactions,
    SUM(t.amount) as total_revenue,
    AVG(c.satisfaction_score) as avg_satisfaction
FROM users u
JOIN mysql_transactions t ON u.user_id = t.seller_id
LEFT JOIN mongo_chat_logs c ON t.transaction_id = c.transaction_id
WHERE t.transaction_date >= '2024-01-01'
GROUP BY u.user_name, u.talent_category
HAVING COUNT(t.transaction_id) > 10
ORDER BY total_revenue DESC;

-- 파티션 테이블 생성
CREATE TABLE orders (
    order_id BIGINT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- 최근 데이터는 로컬에
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 과거 데이터는 외부 서버에
CREATE FOREIGN TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
SERVER archive_server
OPTIONS (table_name 'orders_2023');

-- 쿼리는 동일하게!
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-06-01' AND '2024-06-01';

-- Multicorn 설치
CREATE EXTENSION multicorn;

-- Python FDW 정의
CREATE SERVER api_server
FOREIGN DATA WRAPPER multicorn
OPTIONS (
    wrapper 'multicorn.restfdw.RestFDW',
    url 'https://api.example.com/users'
);

-- 외부 테이블 생성
CREATE FOREIGN TABLE api_users (
    id INT,
    name TEXT,
    email TEXT
) SERVER api_server;

-- 비동기 실행 활성화
ALTER SERVER mysql_server 
OPTIONS (ADD async_capable 'true');

ALTER SERVER oracle_server 
OPTIONS (ADD async_capable 'true');

-- 여러 외부 테이블 조인
SELECT 
    m.customer_name,
    o.order_total,
    p.product_name
FROM mysql_customers m
JOIN oracle_orders o ON m.customer_id = o.customer_id
JOIN postgres_products p ON o.product_id = p.product_id;

-- MySQL의 모든 테이블을 자동으로 가져오기
IMPORT FOREIGN SCHEMA mydb
FROM SERVER mysql_server
INTO public;

-- 특정 테이블만 가져오기
IMPORT FOREIGN SCHEMA mydb
LIMIT TO (customers, orders)
FROM SERVER mysql_server
INTO public;

-- 특정 테이블 제외
IMPORT FOREIGN SCHEMA mydb
EXCEPT (temp_table, backup_table)
FROM SERVER mysql_server
INTO public;