说明
通过数据集成任务,把ods和dim数据同步到LAS ods和dim内表,使用ods+dim数据经过逻辑加工任务dwd生成考生明细表,通过dwd指标汇总到dwm生成最终指标结果数据
说明
说明
此章节将介绍如何通过数据开发新建目录以及HSQL任务,完成对LAS表加工,配置和发布
可查看关于数据开发的更多细节
-- -- ******************************************************************** -- Author: demo02 -- CreateTime: 2022-08-10 00:57:00.000 -- Description: -- Update: Task Update Description -- ******************************************************************** INSERT OVERWRITE TABLE dwd.dwd_action_exam_detail_df partition(date = '${date}') SELECT a.exam_id, a.student_id, b.student_name, b.class_id, b.class_name, b.jk_code, b.school_id, b.school_name, b.grade_id, b.grade_name, a.subject_id, a.subject_name, a.exam_time, a.exam_score, is_test_school FROM ( SELECT exam_id, student_id, exam_subject_id as subject_id, case when exam_subject_id = 1 then '数学' when exam_subject_id = 2 then '语文' when exam_subject_id = 3 then '英语' when exam_subject_id = 4 then '物理' when exam_subject_id = 5 then '化学' when exam_subject_id = 6 then '生物' else '其他' end as subject_name, exam_score, exam_time FROM ods.exam_event_df WHERE date >= '${date}' ) AS a LEFT JOIN ( SELECT school_id, school_name, grade_id, grade_name, class_id, class_name, school_year, jk_code, student_id, student_name, is_test_school FROM dim.student_info_df WHERE date >= '${date}' and school_year = 2021 ) AS b ON a.student_id = b.student_id
预览目标表数据,是否准确写入数据
小技巧:使用智能IDE功能,快速跳转至数据地图预览目标表数据
方法介绍:
-- ******************************************************************** -- Author: demo02 -- CreateTime: 2022-08-10 10:30:18.000 -- Description: -- Update: Task Update Description -- ******************************************************************** INSERT OVERWRITE TABLE dwm.dwm_action_exam_summary_df partition(date = '${date}') SELECT class_id, max(class_name) class_name, school_id, max(school_name) school_name, grade_id, max(grade_name) grade_name, subject_id, max(subject_name) subject_name, count(DISTINCT student_id) AS total_student_cnt, sum(exam_score) total_score FROM dwd.dwd_action_exam_detail_df WHERE date >= '${date}' GROUP BY class_id, school_id, grade_id, subject_id