— 创建数据仓库用户
CREATE USER dw_user WITH PASSWORD ‘your_password’ CREATEDB;
GRANT ALL ON TABLESPACE dw_tbs TO dw_user;

— 创建维度表：产品维度
CREATE TABLE dim_product (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100),
product_category VARCHAR(50),
product_brand VARCHAR(50)
);

— 创建维度表：客户维度
CREATE TABLE dim_customer (
customer_id INTEGER PRIMARY KEY,
customer_name VARCHAR(100),
customer_city VARCHAR(50),
customer_region VARCHAR(50)
);

— 创建事实表：销售事实
CREATE TABLE fact_sales (
sales_id INTEGER PRIMARY KEY,
time_id INTEGER REFERENCES dim_time(time_id),
product_id INTEGER REFERENCES dim_product(product_id),from DB视频:www.itpux.com
customer_id INTEGER REFERENCES dim_customer(customer_id),
quantity INTEGER,
amount DECIMAL(10, 2)
);

# 抽取数据
psql -d source_db -U source_user -c “COPY (SELECT * FROM source_sales) TO ‘/tmp/source_sales.csv’ CSV HEADER”;

# 转换数据
python3 transform.py /tmp/source_sales.csv /tmp/transformed_sales.csv;

# 加载数据
psql -d dw_db -U dw_user -c “COPY fact_sales FROM ‘/tmp/transformed_sales.csv’ CSV HEADER”;

# 读取源数据
df = pd.read_csv(‘/tmp/source_sales.csv’)

# 数据清洗
df = df.dropna()

# 数据转换
df[‘amount’] = df[‘quantity’] * df[‘price’]

# 保存转换后的数据
df.to_csv(‘/tmp/transformed_sales.csv’, index=False)