네임스페이스 구조

-- 3-Level 이름: catalog.schema.object
SELECT * FROM production.ecommerce.orders;
--               ^^^^^^^^^^  ^^^^^^^^^  ^^^^^^
--               카탈로그      스키마     테이블

-- Metastore 정보 확인
SELECT * FROM system.information_schema.catalogs;

-- Managed Table 생성
CREATE TABLE production.ecommerce.orders (
    order_id BIGINT,
    customer_id BIGINT,
    order_date DATE,
    total_amount DECIMAL(10,2)
) USING DELTA
COMMENT '주문 데이터';

-- External Table 생성
CREATE TABLE production.ecommerce.legacy_orders (
    order_id BIGINT,
    order_date DATE
) USING DELTA
LOCATION 's3://my-bucket/legacy/orders';

-- 일반 View
CREATE VIEW production.ecommerce.v_active_customers AS
SELECT customer_id, name, email
FROM production.ecommerce.customers
WHERE status = 'active';

-- Dynamic View (행 수준 보안)
CREATE VIEW production.ecommerce.v_orders_secure AS
SELECT *
FROM production.ecommerce.orders
WHERE region = current_user_region();  -- 사용자별로 다른 데이터 표시

CREATE MATERIALIZED VIEW production.ecommerce.mv_daily_sales AS
SELECT
    order_date,
    region,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM production.ecommerce.orders
GROUP BY order_date, region;

CREATE STREAMING TABLE production.ecommerce.bronze_orders AS
SELECT * FROM STREAM read_files('/Volumes/raw/orders/*.json');

-- Managed Volume 생성
CREATE VOLUME production.ecommerce.raw_files
COMMENT '원본 파일 저장소';

-- Volume에 파일 경로 접근
-- /Volumes/production/ecommerce/raw_files/invoice.pdf

-- SQL UDF
CREATE FUNCTION production.ecommerce.calculate_tax(price DECIMAL(10,2), rate DECIMAL(4,2))
RETURNS DECIMAL(10,2)
RETURN price * rate;

-- Python UDF
CREATE FUNCTION production.ecommerce.clean_text(text STRING)
RETURNS STRING
LANGUAGE PYTHON AS $$
    import re
    return re.sub(r'[^가-힣a-zA-Z0-9\s]', '', text).strip()
$$;

import mlflow

# 모델을 Unity Catalog에 등록
mlflow.register_model(
    "runs:/<run_id>/model",
    "production.ml_models.churn_prediction"
)

# 등록된 모델 로드
model = mlflow.pyfunc.load_model("models:/production.ml_models.churn_prediction@champion")

CREATE CONNECTION mysql_prod TYPE MYSQL
OPTIONS (
    host 'mysql.example.com',
    port '3306',
    user '{{secrets/db/mysql-user}}',
    password '{{secrets/db/mysql-pass}}'
);

-- 카탈로그 생성
CREATE CATALOG IF NOT EXISTS production
COMMENT '프로덕션 데이터';

-- 스키마 생성
CREATE SCHEMA IF NOT EXISTS production.ecommerce
COMMENT '이커머스 도메인'
MANAGED LOCATION 's3://my-bucket/production/ecommerce';

-- 테이블 생성
CREATE TABLE production.ecommerce.orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id BIGINT NOT NULL,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status STRING
) USING DELTA
COMMENT '주문 데이터'
TBLPROPERTIES ('quality' = 'gold');

-- 볼륨 생성
CREATE VOLUME production.ecommerce.raw_files
COMMENT '원본 파일 저장소';

-- 기본 카탈로그/스키마 설정 (매번 3-Level 이름을 쓰지 않아도 됨)
USE CATALOG production;
USE SCHEMA ecommerce;
SELECT * FROM orders;  -- = production.ecommerce.orders

-- 카탈로그 목록 조회
SELECT catalog_name, comment, created
FROM system.information_schema.catalogs;

-- 특정 카탈로그의 스키마 목록
SELECT schema_name, comment, created
FROM production.information_schema.schemata;

-- 특정 스키마의 테이블 목록
SELECT table_name, table_type, comment, created
FROM production.information_schema.tables
WHERE table_schema = 'ecommerce';

-- 특정 테이블의 컬럼 목록
SELECT column_name, data_type, is_nullable, comment
FROM production.information_schema.columns
WHERE table_schema = 'ecommerce' AND table_name = 'orders';

-- 볼륨 목록
SELECT volume_name, volume_type, comment
FROM production.information_schema.volumes
WHERE volume_schema = 'ecommerce';