Lakebase 설정

-- 기본 Lakebase 데이터베이스 생성
CREATE DATABASE my_catalog.my_schema.app_db
TYPE LAKEBASE;

-- 생성 확인
DESCRIBE DATABASE my_catalog.my_schema.app_db;

-- 결과 예시:
-- name: app_db
-- type: LAKEBASE
-- state: RUNNING
-- host: app-db-xxxx.lakebase.databricks.com
-- port: 5432

from databricks.sdk import WorkspaceClient

w = WorkspaceClient()

# Lakebase 데이터베이스 생성
db = w.lakebase.create_database(
    catalog_name="my_catalog",
    schema_name="my_schema",
    name="app_db"
)

print(f"데이터베이스 생성 완료: {db.name}")
print(f"호스트: {db.host}")
print(f"포트: {db.port}")
print(f"상태: {db.state}")

# Personal Access Token으로 연결
psql "host=app-db-xxxx.lakebase.databricks.com \
      port=5432 \
      dbname=app_db \
      user=token \
      password=dapi_your_personal_access_token \
      sslmode=require"

# 연결 테스트
SELECT version();
-- 결과: PostgreSQL 16.x (Lakebase)

import psycopg2

# 기본 연결
conn = psycopg2.connect(
    host="app-db-xxxx.lakebase.databricks.com",
    port=5432,
    dbname="app_db",
    user="token",
    password="dapi_your_personal_access_token",
    sslmode="require"
)

# 연결 테스트
cursor = conn.cursor()
cursor.execute("SELECT version()")
print(cursor.fetchone())
# 출력: ('PostgreSQL 16.x (Lakebase)',)

# 테이블 생성 예제
cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        product_id  SERIAL PRIMARY KEY,
        name        VARCHAR(200) NOT NULL,
        category    VARCHAR(100),
        price       DECIMAL(10,2) NOT NULL,
        stock       INTEGER DEFAULT 0,
        created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")
conn.commit()
print("테이블 생성 완료!")

from sqlalchemy import create_engine

# SQLAlchemy 엔진 생성
engine = create_engine(
    "postgresql+psycopg2://token:dapi_your_token@"
    "app-db-xxxx.lakebase.databricks.com:5432/app_db",
    connect_args={"sslmode": "require"},
    pool_size=5,        # 커넥션 풀 크기
    max_overflow=10,    # 최대 추가 연결 수
    pool_timeout=30     # 풀 대기 타임아웃 (초)
)

# pandas와 함께 사용
import pandas as pd
df = pd.read_sql("SELECT * FROM products LIMIT 10", engine)
print(df)

jdbc:postgresql://app-db-xxxx.lakebase.databricks.com:5432/app_db?
  user=token&
  password=dapi_your_token&
  sslmode=require

-- 특정 사용자에게 데이터베이스 사용 권한 부여
GRANT USAGE ON DATABASE my_catalog.my_schema.app_db
TO `user@company.com`;

-- 특정 그룹에게 테이블 읽기 권한 부여
GRANT SELECT ON TABLE my_catalog.my_schema.app_db.products
TO `data_analysts`;

-- 특정 그룹에게 테이블 쓰기 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE my_catalog.my_schema.app_db.orders
TO `app_developers`;

-- 권한 확인
SHOW GRANTS ON TABLE my_catalog.my_schema.app_db.products;

import psycopg2
from databricks.sdk import WorkspaceClient

# Databricks SDK를 통해 OAuth 토큰 획득
w = WorkspaceClient()
token = w.tokens.create(
    comment="Lakebase app connection",
    lifetime_seconds=3600  # 1시간
)

# OAuth 토큰으로 Lakebase 연결
conn = psycopg2.connect(
    host="app-db-xxxx.lakebase.databricks.com",
    port=5432,
    dbname="app_db",
    user="token",
    password=token.token_value,
    sslmode="require"
)

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("✅ 설정 완료!")