Lakebase 백업·모니터링·네트워크 설정

from databricks.sdk import WorkspaceClient
from datetime import datetime, timedelta

w = WorkspaceClient()

# 2시간 전 시점으로 복원
restore_time = datetime.utcnow() - timedelta(hours=2)

restored_db = w.lakebase.restore_database(
    catalog_name="my_catalog",
    schema_name="my_schema",
    name="app_db_restored",
    source_database="app_db",
    restore_point=restore_time.isoformat()
)

print(f"복원 완료: {restored_db.name}")
print(f"복원 시점: {restore_time}")

-- 현재 활성 연결 확인 (Lakebase에 직접 접속하여 실행)
SELECT count(*) AS active_connections
FROM pg_stat_activity
WHERE state = 'active';

-- 테이블별 크기 확인
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
    n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

-- 느린 쿼리 확인 (5초 이상)
SELECT
    query,
    calls,
    mean_exec_time / 1000 AS avg_seconds,
    total_exec_time / 1000 AS total_seconds
FROM pg_stat_statements
WHERE mean_exec_time > 5000
ORDER BY total_exec_time DESC
LIMIT 10;

from databricks.sdk import WorkspaceClient
import psycopg2

# 1. 워크스페이스 클라이언트 초기화
w = WorkspaceClient()

# 2. Lakebase 데이터베이스 생성
db = w.lakebase.create_database(
    catalog_name="production",
    schema_name="ecommerce",
    name="shop_db"
)
print(f"✅ 데이터베이스 생성: {db.host}:{db.port}")

# 3. 연결
conn = psycopg2.connect(
    host=db.host,
    port=db.port,
    dbname="shop_db",
    user="token",
    password="dapi_your_token",
    sslmode="require"
)
cursor = conn.cursor()

# 4. 테이블 생성
cursor.execute("""
    CREATE TABLE customers (
        id          SERIAL PRIMARY KEY,
        name        VARCHAR(100) NOT NULL,
        email       VARCHAR(200) UNIQUE NOT NULL,
        tier        VARCHAR(20) DEFAULT 'standard',
        created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE orders (
        id          SERIAL PRIMARY KEY,
        customer_id INTEGER REFERENCES customers(id),
        product     VARCHAR(200) NOT NULL,
        amount      DECIMAL(10,2) NOT NULL,
        status      VARCHAR(20) DEFAULT 'pending',
        created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX idx_orders_customer ON orders(customer_id);
    CREATE INDEX idx_orders_status ON orders(status);
""")
conn.commit()
print("✅ 테이블 생성 완료")

# 5. 샘플 데이터 입력
cursor.execute("""
    INSERT INTO customers (name, email, tier) VALUES
    ('김철수', 'cs.kim@example.com', 'premium'),
    ('이영희', 'yh.lee@example.com', 'standard'),
    ('박민수', 'ms.park@example.com', 'premium');
""")

cursor.execute("""
    INSERT INTO orders (customer_id, product, amount, status) VALUES
    (1, '노트북', 1500000, 'completed'),
    (1, '마우스', 35000, 'completed'),
    (2, '키보드', 89000, 'pending'),
    (3, '모니터', 450000, 'shipped');
""")
conn.commit()
print("✅ 샘플 데이터 입력 완료")

# 6. 데이터 확인
cursor.execute("""
    SELECT c.name, o.product, o.amount, o.status
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    ORDER BY o.amount DESC
""")
for row in cursor.fetchall():
    print(f"  {row[0]}: {row[1]} - ₩{row[2]:,.0f} ({row[3]})")

# 출력 예시:
# 김철수: 노트북 - ₩1,500,000 (completed)
# 박민수: 모니터 - ₩450,000 (shipped)
# 이영희: 키보드 - ₩89,000 (pending)
# 김철수: 마우스 - ₩35,000 (completed)

conn.close()
print("✅ 설정 완료!")