flink数据仓库之考试模型

操作数据存储层（ODS）

CREATE TABLE ods_exam_record (  
    id BIGINT PRIMARY KEY,  
    student_id BIGINT,  
    teacher_id BIGINT,  
    exam_time TIMESTAMP,  
    score INT,  
    ... -- 其他字段  
) WITH (  
    'connector' = '...', -- 根据你的数据源选择合适的连接器，如 Kafka, JDBC 等  
    ... -- 其他配置参数  
);

维度模型层（DIM）

CREATE TABLE dim_student (  
    student_id BIGINT PRIMARY KEY,  
    name STRING,  
    gender STRING,  
    age INT,  
    class_id BIGINT, -- 班级ID，可能还需要与 dim_class 表关联  
    ... -- 其他字段  
) WITH (  
    ... -- 存储配置，如 HDFS, HBase 等  
);  
  
CREATE TABLE dim_teacher (  
    teacher_id BIGINT PRIMARY KEY,  
    name STRING,  
    subject STRING,  
    ... -- 其他字段  
) WITH (  
    ... -- 存储配置  
);

数据仓库明细层（DWD）

CREATE TABLE dwd_exam_detail AS  
SELECT  
    e.id,  
    e.student_id,  
    s.name AS student_name, -- 与 dim_student 表关联获取学生姓名  
    e.teacher_id,  
    t.name AS teacher_name, -- 与 dim_teacher 表关联获取教师姓名  
    e.exam_time,  
    e.score,  
    ... -- 其他需要的字段  
FROM  
    ods_exam_record e  
LEFT JOIN  
    dim_student s ON e.student_id = s.student_id  
LEFT JOIN  
    dim_teacher t ON e.teacher_id = t.teacher_id;

应用数据服务层（ADS）

CREATE TABLE ads_exam_summary AS  
SELECT  
    s.name AS student_name,  
    AVG(d.score) AS avg_score,  
    MAX(d.score) AS max_score,  
    MIN(d.score) AS min_score  
FROM  
    dwd_exam_detail d  
JOIN  
    dim_student s ON d.student_id = s.student_id  
GROUP BY  
    s.name;