在线教育数据仓库建设

1. 在线教育业务技术介绍

2. 数据同步层内容与案例介绍

2.1 ODS层介绍

ods层，全称operational data store 操作数据存储层。主要作用是存储业务数据库中的原始操作型数据，通过同步工具定时或者实时方式抽取到ods层，进行全量保存。ods层数据一般来说不进行数据清洗，保留和原始数据一样，方便最大化还原原始数据。

2.2 MySQL上游表schema

  • 线索表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    CREATE TABLE `pe_clue_sale` (
      `dm_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `stud_id` int(11) NOT NULL COMMENT '学员ID',
      `user_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '学员用户名',
      `real_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '注册电话',
      `product_group_id` int(11) DEFAULT NULL COMMENT '产品类别 这里指产品分类（大类）',
      `product_group_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '产品名称',
      `pay_status` tinyint(4) DEFAULT NULL COMMENT '订单的支付状态 0 - 未下单  1- 未支付 2 - 已支付',
      `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
      `order_time` datetime DEFAULT NULL COMMENT '下单时间',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `old_dm_id` int(11) DEFAULT NULL COMMENT '老线索id',
      `second_clue_source` int(11) DEFAULT NULL COMMENT '线索二级来源',
      `clue_source` int(11) DEFAULT NULL COMMENT '1-表单 2-在线咨询 3-手动录入4-官网注册5-微信6-销售活动',
      `form_id` int(11) DEFAULT NULL COMMENT '表单ID',
      `pool_index` int(11) NOT NULL COMMENT '线索池标识 ',
      `status` tinyint(1) DEFAULT NULL COMMENT '1 静默  0-非静默 ',
      `ts` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
      `last_follow_sys_id` int(11) DEFAULT NULL COMMENT '上次跟进人',
      `last_follow_sys_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '上次跟进人姓名',
      `last_follow_time` datetime DEFAULT NULL COMMENT '上次跟进时间',
      `country` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '国家编码',
      `nation_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '国际区号',
      `first_call` datetime DEFAULT NULL COMMENT '首次外呼时间',
      `clue_status` tinyint(4) DEFAULT NULL COMMENT '线索状态:新线索，待跟进， 复活待跟进，跟进中，待支付，已支付，放弃',
      `data_status` tinyint(4) DEFAULT NULL COMMENT '线索数据状态:open，close-won(已支付), close-lost(放弃)',
      `allocate_status` tinyint(4) DEFAULT NULL COMMENT '线索分配状态：1待分配（非系统）、2已分配（系统）、3待分配（系统）、4已分配（人工）',
      `allocate_time` datetime DEFAULT NULL COMMENT '仅记录最后一次分配的时间',
      `reservation_time` datetime DEFAULT NULL COMMENT '预约时间',
      `product_category_id` int(11) DEFAULT NULL COMMENT '产品线id(产品小类id)',
      `product_category_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '产品线名称(产品小类名称)',
      `curr_follow_sys_id` int(11) DEFAULT NULL COMMENT '当前跟进人id',
      `curr_follow_sys_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当前跟进人姓名',
      `curr_follow_sys_uid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当前跟进人域用户',
      `curr_follow_dept_id` int(11) DEFAULT NULL COMMENT '当前跟进人部门id',
      `follow_level` tinyint(4) DEFAULT '1' COMMENT '优先级，默认为1，每增加一个意向，则加1',
      `close_time` datetime DEFAULT NULL COMMENT '关闭时间',
      `recover_time` datetime DEFAULT NULL COMMENT '复活时间',
      `is_del` tinyint(4) DEFAULT '0' COMMENT '逻辑删除标记',
      `product_type` tinyint(4) DEFAULT NULL COMMENT '线索关联的第一个订单的课程类型，引流课，正价课',
      `is_resurrect` tinyint(4) DEFAULT '0' COMMENT '线索复活标记,1是复活过',
      `last_clue_status` tinyint(4) DEFAULT '0' COMMENT '线索放弃之前的状态',
      `reason` int(10) DEFAULT NULL,
      `is_hand` tinyint(4) DEFAULT '0' COMMENT '领取方式 0--其他领取 1--手动领取',
      `star_flag` tinyint(4) DEFAULT NULL COMMENT '售后转售前星标',
      `rainbow_mark` tinyint(4) DEFAULT '0' COMMENT '线索彩虹标记，默认0，无标记',
      `deposit_payment` tinyint(4) DEFAULT '0' COMMENT '定金支付，默认0，无标记',
      `rcrai_score` double DEFAULT '0' COMMENT '睿科伦线索评级',
      `course_price` tinyint(10) DEFAULT '0' COMMENT '课程价格: 0:默认, 1: 0元课, 2: 9.9元课',
      `server_time_flag` tinyint(4) DEFAULT NULL COMMENT '线索服务期标识：默认null，0电销服务期，2学管服务期',
      `task_pre_create_source` int(11) DEFAULT NULL,
      `receive_sys_id` int(11) DEFAULT NULL COMMENT '手动领取用户id',
      `receive_date` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手动领取日期',
      `job_num` int(11) DEFAULT '0' COMMENT '工单数',
      `job_valid_num` int(11) DEFAULT '0' COMMENT '有效工单数',
      `public_status` tinyint(4) DEFAULT '0' COMMENT '公海状态：1首次待分配、2激活待分配、3转派待分配、4放弃待分配、5回收待分配、6已回收、7已放弃、8首次已分配、9激活再分配、10转派再分配、11放弃再分配、12回收再分配、13购课完结、14放弃完结、15回收完结',
      `private_status` tinyint(4) DEFAULT '0' COMMENT '私海状态：1待跟进、2跟进中、3已放弃、4已回收、5已完结',
      `clue_type` tinyint(4) DEFAULT '0' COMMENT '线索种类：1新线索、2转派线索、3回收线索、4放弃线索、5完结线索',
      `clue_score` double DEFAULT '0' COMMENT '线索评分 0~100分',
      `backup_a` tinyint(4) DEFAULT '-1' COMMENT '洗数据备份字段一',
      `backup_b` tinyint(4) DEFAULT '-1' COMMENT '洗数据备份字段二',
      `wx_status` tinyint(4) DEFAULT '-1' COMMENT '加微状态：0已加好友、1未绑定微信、2未加好友',
      `reserve_type` tinyint(4) DEFAULT NULL COMMENT '预约时间类型 0-自动 1-手动',
      `hand_wx_status` tinyint(4) DEFAULT '0' COMMENT '是否加微：0:未添加，1:已添加',
      `group_status` tinyint(4) DEFAULT '0' COMMENT '是否加群：0:未添加，1:已添加',
      PRIMARY KEY (`dm_id`),
      KEY `idx_ts` (`ts`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='线索表'

  • 订单表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    CREATE TABLE `tb_order` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `order_no` varchar(50) DEFAULT NULL COMMENT '订单号',
      `agent_id` varchar(50) DEFAULT NULL COMMENT '代理商id',
      `agent_new_id` int(11) DEFAULT NULL COMMENT '新代理商系统代理商ID',
      `agent_account_id` int(11) DEFAULT NULL COMMENT '新代理商系统代理商子账户ID',
      `agent_account_version_id` int(11) DEFAULT NULL COMMENT '新代理商账户版本Id',
      `campus_id` int(11) DEFAULT NULL COMMENT '校园代理id',
      `order_type` tinyint(4) DEFAULT '1' COMMENT '订单类型：1-购买,2-换课购买,3-退课,4-换课退课',
      `subject_type` tinyint(4) DEFAULT NULL COMMENT '科目类型0-不区分文理,1-文科,2-理科,只针对高考有效',
      `city` int(11) DEFAULT NULL COMMENT '开通城市代码',
      `user_id` int(11) DEFAULT NULL COMMENT '用户ID',
      `status` tinyint(4) DEFAULT '0' COMMENT '订单状态，0：未支付，1：已支付，2:取消',
      `freeze_status` tinyint(4) DEFAULT '0' COMMENT '冻结状态,0-未冻结,1-已冻结',
      `order_time` datetime DEFAULT NULL COMMENT '下订单时间',
      `pay_time` datetime DEFAULT NULL COMMENT '订单支付时间',
      `amount_before` decimal(12,2) DEFAULT NULL COMMENT '订单原金额',
      `promote_after_amount` decimal(12,2) DEFAULT NULL COMMENT '通过优惠计算后的价格',
      `amount` decimal(12,2) DEFAULT NULL COMMENT '订单实际付款总金额',
      `net_value` decimal(12,2) DEFAULT NULL COMMENT '净值',
      `mix_gold_content` decimal(12,2) DEFAULT '100.00' COMMENT '综合含金量',
      `discount_id` int(11) DEFAULT NULL COMMENT '促销活动id',
      `valid_income_percent` tinyint(4) DEFAULT NULL COMMENT '订单可确认含金量【暂时保留】',
      `product_line` int(11) DEFAULT NULL COMMENT '产品线',
      `payway_id` int(11) DEFAULT NULL COMMENT '支付方式【支付方式字典表id】',
      `is_group_pay` tinyint(4) DEFAULT '0' COMMENT '组合支付方式,0:非组合支付,1:第三方+现金账户,2:第三方+学习卡账户,3:学习卡+现金账户,4:第三方+学习卡账户+现金账户,5:代理商+现金账户',
      `partner_id` int(11) DEFAULT NULL COMMENT '商户ID',
      `cancel_type` tinyint(4) DEFAULT '0' COMMENT '订单取消类型，0：用户操作，1：系跑批取消，2：客服取消',
      `cancel_time` datetime DEFAULT NULL COMMENT '订单取消时间',
      `order_source` tinyint(4) DEFAULT '0' COMMENT '订单来源，0：PC端，1：手机端',
      `is_basic_order` tinyint(4) DEFAULT '1' COMMENT '是否原单，1：是，0：否',
      `basic_order_id` int(11) DEFAULT NULL COMMENT '原单id',
      `is_agent_order` tinyint(4) DEFAULT '0' COMMENT '是否代理商订单， 0：否 ， 1：是',
      `is_refunded` int(4) DEFAULT '1' COMMENT '是否已退款，1：否，0：是',
      `is_changed` int(4) DEFAULT '1' COMMENT '是否发生换课，1：否，0：是',
      `order_c_place` tinyint(4) DEFAULT '0' COMMENT '下单场景',
      `order_channel` tinyint(4) DEFAULT '0' COMMENT '下单渠道, 0:官网;1:网盟;2:校代;3:天猫; 4:虚拟代理商;5:返点代理商',
      `order_limit_sign` tinyint(4) DEFAULT '0' COMMENT '限时标识：0-普通订单，1-限时订单',
      `order_alliance_sign` tinyint(4) DEFAULT '0' COMMENT '网盟标识：0-非网盟，1-网盟',
      `order_shortcut_sign` tinyint(4) DEFAULT '0' COMMENT '快捷支付标识：0-普通订单，1-快捷支付',
      `order_overdue_time` datetime DEFAULT NULL COMMENT '过期时间',
      `ip` varchar(40) DEFAULT NULL COMMENT 'ip',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
      PRIMARY KEY (`id`),
      UNIQUE KEY `index_unique_order_no` (`order_no`),
      KEY `index_payway_id` (`payway_id`),
      KEY `ts` (`ts`) USING BTREE,
      KEY `agent_new_id_paytiem` (`agent_new_id`,`pay_time`) USING BTREE,
      KEY `index_order_time` (`order_time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单表'

2.3 案例介绍

  1. ODS表设计
    ods表数据同步一般分为按天全量同步和按天增量同步，同步的数据存储在hive ods库中,ods表按天进行分区存储。ods表命名规范如下：

    1
    2
    3
    ods.ods_库表_表名_更新周期(day/hour)_(da或者di) 
    da: 表示全量同步
    di: 表示整量同步

  2. 按照全量如何同步数据
    比如我们要同步mysql表crm_sea.pe_clue_sale到hive ods库ods_crm_sea_pe_clue_sale_day_da

    源表结构如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
        CREATE TABLE `pe_clue_sale` (
      `dm_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
      `stud_id` int(11) NOT NULL COMMENT '学员ID',
      `user_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '学员用户名',
      `real_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `phone` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '注册电话',
      `product_group_id` int(11) DEFAULT NULL COMMENT '产品类别 这里指产品分类（大类）',
      `product_group_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '产品名称',
      `pay_status` tinyint(4) DEFAULT NULL COMMENT '订单的支付状态 0 - 未下单  1- 未支付 2 - 已支付',
      `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
      `order_time` datetime DEFAULT NULL COMMENT '下单时间',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `old_dm_id` int(11) DEFAULT NULL COMMENT '老线索id',
      `second_clue_source` int(11) DEFAULT NULL COMMENT '线索二级来源',
      `clue_source` int(11) DEFAULT NULL COMMENT '1-表单 2-在线咨询 3-手动录入4-官网注册5-微信6-销售活动',
      `form_id` int(11) DEFAULT NULL COMMENT '表单ID',
      `pool_index` int(11) NOT NULL COMMENT '线索池标识 ',
      `status` tinyint(1) DEFAULT NULL COMMENT '1 静默  0-非静默 ',
      `ts` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
      `last_follow_sys_id` int(11) DEFAULT NULL COMMENT '上次跟进人',
      `last_follow_sys_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '上次跟进人姓名',
      `last_follow_time` datetime DEFAULT NULL COMMENT '上次跟进时间',
      `country` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '国家编码',
      `nation_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '国际区号',
      `first_call` datetime DEFAULT NULL COMMENT '首次外呼时间',
      `clue_status` tinyint(4) DEFAULT NULL COMMENT '线索状态:新线索，待跟进， 复活待跟进，跟进中，待支付，已支付，放弃',
      `data_status` tinyint(4) DEFAULT NULL COMMENT '线索数据状态:open，close-won(已支付), close-lost(放弃)',
      `allocate_status` tinyint(4) DEFAULT NULL COMMENT '线索分配状态：1待分配（非系统）、2已分配（系统）、3待分配（系统）、4已分配（人工）',
      `allocate_time` datetime DEFAULT NULL COMMENT '仅记录最后一次分配的时间',
      `reservation_time` datetime DEFAULT NULL COMMENT '预约时间',
      `product_category_id` int(11) DEFAULT NULL COMMENT '产品线id(产品小类id)',
      `product_category_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '产品线名称(产品小类名称)',
      `curr_follow_sys_id` int(11) DEFAULT NULL COMMENT '当前跟进人id',
      `curr_follow_sys_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当前跟进人姓名',
      `curr_follow_sys_uid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当前跟进人域用户',
      `curr_follow_dept_id` int(11) DEFAULT NULL COMMENT '当前跟进人部门id',
      `follow_level` tinyint(4) DEFAULT '1' COMMENT '优先级，默认为1，每增加一个意向，则加1',
      `close_time` datetime DEFAULT NULL COMMENT '关闭时间',
      `recover_time` datetime DEFAULT NULL COMMENT '复活时间',
      `is_del` tinyint(4) DEFAULT '0' COMMENT '逻辑删除标记',
      `product_type` tinyint(4) DEFAULT NULL COMMENT '线索关联的第一个订单的课程类型，引流课，正价课',
      `is_resurrect` tinyint(4) DEFAULT '0' COMMENT '线索复活标记,1是复活过',
      `last_clue_status` tinyint(4) DEFAULT '0' COMMENT '线索放弃之前的状态',
      `reason` int(10) DEFAULT NULL,
      `is_hand` tinyint(4) DEFAULT '0' COMMENT '领取方式 0--其他领取 1--手动领取',
      `star_flag` tinyint(4) DEFAULT NULL COMMENT '售后转售前星标',
      `rainbow_mark` tinyint(4) DEFAULT '0' COMMENT '线索彩虹标记，默认0，无标记',
      `deposit_payment` tinyint(4) DEFAULT '0' COMMENT '定金支付，默认0，无标记',
      `rcrai_score` double DEFAULT '0' COMMENT '睿科伦线索评级',
      `course_price` tinyint(10) DEFAULT '0' COMMENT '课程价格: 0:默认, 1: 0元课, 2: 9.9元课',
      `server_time_flag` tinyint(4) DEFAULT NULL COMMENT '线索服务期标识：默认null，0电销服务期，2学管服务期',
      `task_pre_create_source` int(11) DEFAULT NULL,
      `receive_sys_id` int(11) DEFAULT NULL COMMENT '手动领取用户id',
      `receive_date` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手动领取日期',
      `job_num` int(11) DEFAULT '0' COMMENT '工单数',
      `job_valid_num` int(11) DEFAULT '0' COMMENT '有效工单数',
      `public_status` tinyint(4) DEFAULT '0' COMMENT '公海状态：1首次待分配、2激活待分配、3转派待分配、4放弃待分配、5回收待分配、6已回收、7已放弃、8首次已分配、9激活再分配、10转派再分配、11放弃再分配、12回收再分配、13购课完结、14放弃完结、15回收完结',
      `private_status` tinyint(4) DEFAULT '0' COMMENT '私海状态：1待跟进、2跟进中、3已放弃、4已回收、5已完结',
      `clue_type` tinyint(4) DEFAULT '0' COMMENT '线索种类：1新线索、2转派线索、3回收线索、4放弃线索、5完结线索',
      `clue_score` double DEFAULT '0' COMMENT '线索评分 0~100分',
      `backup_a` tinyint(4) DEFAULT '-1' COMMENT '洗数据备份字段一',
      `backup_b` tinyint(4) DEFAULT '-1' COMMENT '洗数据备份字段二',
      `wx_status` tinyint(4) DEFAULT '-1' COMMENT '加微状态：0已加好友、1未绑定微信、2未加好友',
      `reserve_type` tinyint(4) DEFAULT NULL COMMENT '预约时间类型 0-自动 1-手动',
      `hand_wx_status` tinyint(4) DEFAULT '0' COMMENT '是否加微：0:未添加，1:已添加',
      `group_status` tinyint(4) DEFAULT '0' COMMENT '是否加群：0:未添加，1:已添加',
      PRIMARY KEY (`dm_id`),
      KEY `idx_ts` (`ts`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='线索表'

目标hive表结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
CREATE EXTERNAL TABLE `ods.ods_crm_sea_pe_clue_sale_day_da`(
`dm_id` int COMMENT '主键ID',	
`stud_id` int COMMENT '学员ID',	
`user_name` string COMMENT '学员用户名',	
`real_name` string COMMENT '',	
`phone` string COMMENT '注册电话',	
`product_group_id` int COMMENT '产品类别 这里指产品分类（大类）',	
`product_group_name` string COMMENT '产品名称',	
`pay_status` int COMMENT '订单的支付状态 0 - 未下单  1- 未支付 2 - 已支付',	
`pay_time` timestamp COMMENT '支付时间',	
`order_time` timestamp COMMENT '下单时间',	
`create_time` timestamp COMMENT '创建时间',	
`old_dm_id` int COMMENT '老线索id',	
`second_clue_source` int COMMENT '线索二级来源',	
`clue_source` int COMMENT '1-表单 2-在线咨询 3-手动录入4-官网注册5-微信6-销售活动',	
`form_id` int COMMENT '表单ID',	
`pool_index` int COMMENT '线索池标识 ',	
`status` int COMMENT '1 静默  0-非静默 ',	
`ts` timestamp COMMENT '时间戳',	
`last_follow_sys_id` int COMMENT '上次跟进人',	
`last_follow_sys_name` string COMMENT '上次跟进人姓名',	
`last_follow_time` timestamp COMMENT '上次跟进时间',	
`country` string COMMENT '国家编码',	
`nation_code` string COMMENT '国际区号',	
`first_call` timestamp COMMENT '首次外呼时间',	
`clue_status` int COMMENT '线索状态:新线索，待跟进， 复活待跟进，跟进中，待支付，已支付，放弃',	
`data_status` int COMMENT '线索数据状态:open，close-won(已支付), close-lost(放弃)',	
`allocate_status` int COMMENT '线索分配状态：1待分配（非系统）、2已分配（系统）、3待分配（系统）、4已分配（人工）',	
`allocate_time` timestamp COMMENT '仅记录最后一次分配的时间',	
`reservation_time` timestamp COMMENT '预约时间',	
`product_category_id` int COMMENT '产品线id(产品小类id)',	
`product_category_name` string COMMENT '产品线名称(产品小类名称)',	
`curr_follow_sys_id` int COMMENT '当前跟进人id',	
`curr_follow_sys_name` string COMMENT '当前跟进人姓名',	
`curr_follow_sys_uid` string COMMENT '当前跟进人域用户',	
`curr_follow_dept_id` int COMMENT '当前跟进人部门id',	
`follow_level` int COMMENT '优先级，默认为1，每增加一个意向，则加1',	
`close_time` timestamp COMMENT '关闭时间',	
`recover_time` timestamp COMMENT '复活时间',	
`is_del` int COMMENT '逻辑删除标记',	
`product_type` int COMMENT '线索关联的第一个订单的课程类型，引流课，正价课',	
`is_resurrect` int COMMENT '线索复活标记,1是复活过',	
`last_clue_status` int COMMENT '线索放弃之前的状态',	
`reason` int COMMENT '',	
`is_hand` int COMMENT '领取方式 0--其他领取 1--手动领取',	
`star_flag` int COMMENT '售后转售前星标',	
`rainbow_mark` int COMMENT '线索彩虹标记，默认0，无标记',	
`deposit_payment` int COMMENT '定金支付，默认0，无标记',	
`rcrai_score` double COMMENT '睿科伦线索评级',	
`course_price` int COMMENT '课程价格: 0:默认, 1: 0元课, 2: 9.9元课',	
`server_time_flag` int COMMENT '线索服务期标识：默认null，0电销服务期，2学管服务期',	
`task_pre_create_source` int COMMENT '',	
`receive_sys_id` int COMMENT '手动领取用户id',	
`receive_date` string COMMENT '手动领取日期',	
`job_num` int COMMENT '工单数',	
`job_valid_num` int COMMENT '有效工单数',	
`public_status` int COMMENT '公海状态：1首次待分配、2激活待分配、3转派待分配、4放弃待分配、5回收待分配、6已回收、7已放弃、8首次已分配、9激活再分配、10转派再分配、11放弃再分配、12回收再分配、13购课完结、14放弃完结、15回收完结',	
`private_status` int COMMENT '私海状态：1待跟进、2跟进中、3已放弃、4已回收、5已完结',	
`clue_type` int COMMENT '线索种类：1新线索、2转派线索、3回收线索、4放弃线索、5完结线索',	
`clue_score` double COMMENT '线索评分 0~100分',	
`backup_a` int COMMENT '洗数据备份字段一',	
`backup_b` int COMMENT '洗数据备份字段二',	
`wx_status` int COMMENT '加微状态：0已加好友、1未绑定微信、2未加好友',	
`reserve_type` int COMMENT '预约时间类型 0-自动 1-手动',	
`hand_wx_status` int COMMENT '是否加微：0:未添加，1:已添加',	
`group_status` int COMMENT '是否加群：0:未添加，1:已添加')	
COMMENT '销售&全量'	
PARTITIONED BY (	
`dt` string)	
ROW FORMAT SERDE	
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'	
WITH SERDEPROPERTIES (	
'field.delim'='\u0001',	
'serialization.format'='\u0001')	
STORED AS INPUTFORMAT	
'org.apache.hadoop.mapred.TextInputFormat'	
OUTPUTFORMAT	
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION	
'hdfs://nstest/input/hive/warehouse/ods.db/ods_crm_sea_pe_clue_sale_day_da'

注意目标表按照dt分区
最后我们使用sqoop工具进行数据同步，如下：

1
sqoop import   --connect 'jdbc:mysql://host:3018/crm_sea?tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull'   --username xxx   --password 'xxx'   --query "select \`dm_id\`,\`stud_id\`,\`user_name\`,\`real_name\`,\`phone\`,\`product_group_id\`,\`product_group_name\`,\`pay_status\`,\`pay_time\`,\`order_time\`,\`create_time\`,\`old_dm_id\`,\`second_clue_source\`,\`clue_source\`,\`form_id\`,\`pool_index\`,\`status\`,\`ts\`,\`last_follow_sys_id\`,\`last_follow_sys_name\`,\`last_follow_time\`,\`country\`,\`nation_code\`,\`first_call\`,\`clue_status\`,\`data_status\`,\`allocate_status\`,\`allocate_time\`,\`reservation_time\`,\`product_category_id\`,\`product_category_name\`,\`curr_follow_sys_id\`,\`curr_follow_sys_name\`,\`curr_follow_sys_uid\`,\`curr_follow_dept_id\`,\`follow_level\`,\`close_time\`,\`recover_time\`,\`is_del\`,\`product_type\`,\`is_resurrect\`,\`last_clue_status\`,\`reason\`,\`is_hand\`,\`star_flag\`,\`rainbow_mark\`,\`deposit_payment\`,\`rcrai_score\`,\`course_price\`,\`server_time_flag\`,\`task_pre_create_source\`,\`receive_sys_id\`,\`receive_date\`,\`job_num\`,\`job_valid_num\`,\`public_status\`,\`private_status\`,\`clue_type\`,\`clue_score\`,\`backup_a\`,\`backup_b\`,\`wx_status\`,\`reserve_type\`,\`hand_wx_status\`,\`group_status\` from crm_sea.pe_clue_sale where \$CONDITIONS"   --fields-terminated-by '\001'   --lines-terminated-by '\n'   --null-string '\\N'   --null-non-string '\\N'   --hive-drop-import-delims   --target-dir /input/hive/warehouse/ods.db/ods_crm_sea_pe_clue_sale_day_da/2022/05/03/   --mapreduce-job-name ods_crm_sea_pe_clue_sale   -class-name ods_crm_sea_pe_clue_sale --append --split-by dm_id -m 10

最后，我们添加表分区，如下：

1
alter table ods.ods_crm_sea_pe_clue_sale_day_da add if not exists partition (dt = '20220503') location '/input/hive/warehouse/ods.db/ods_crm_sea_pe_clue_sale_day_da/2022/05/03/'

4.按照增量如何同步数据
增量同步适用于源表数据量比较大的情况（一般千万以上），这时如果每天都全量同步的话，一个是同步时间会很慢，另外同步的时候对于mysql服务io压力是比较大的。所以，这时需要增量同步。
比如：要同步mysql表db_order.tb_order到ods.ods_db_order_tb_order_day_di，我们才用增量的方式
sqoop同步语句如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
sqoop import --connect $mysql_db_url  --query "select 
id
,order_no
,agent_id
,agent_new_id
,agent_account_id
,agent_account_version_id
,campus_id
,order_type
,subject_type
,city
,user_id
,status
,freeze_status
,order_time
,pay_time
,amount_before
,promote_after_amount
,amount
,net_value
,mix_gold_content
,discount_id
,valid_income_percent
,product_line
,payway_id
,is_group_pay
,partner_id
,cancel_type
,cancel_time
,order_source
,is_basic_order
,basic_order_id
,is_agent_order
,is_refunded
,is_changed
,order_c_place
,order_limit_sign
,order_alliance_sign
,order_shortcut_sign
,order_overdue_time
,ip
,create_time
,ts
,order_channel
from db_order.tb_order
where ts >= '2022-05-03 00:00:00' and 1=1" --fields-terminated-by '\001' --lines-terminated-by '\n' --null-string '\\N' --null-non-string '\\N' -m 1 --hive-drop-import-delims  --target-dir /input/hive/warehouse/ods.db/ods_db_order_tb_order_day_di/2022/05/03/

注意，这里需要使用源表里的增量时间字段ts来限定数据范围，每次我们都同步大于昨天的数据

最后添加分区数据：

1
2
ALTER TABLE ods.ods_db_order_tb_order_day_di ADD if not exists PARTITION (dt='20220503') LOCATION '/input/hive/warehouse/ods.db/ods_db_order_tb_order_day_di/2022/05/03/'

由于，同步到的hive ods表是按天增量数据，不方便查询全量数据，所以，我们需要在ods表上加一个整合的全量表,我们称之为mid表，如：mid_db_order_tb_order
实现逻辑如下： 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
-- 抽取前天mid全量数据和ods今天新增数据(只有新增数据)
use ods;
-- 插入物理表
INSERT OVERWRITE TABLE mid.mid_db_order_tb_order PARTITION (dt='${hivevar:dt}')
SELECT  id 
       ,order_no 
       ,agent_id 
       ,agent_new_id 
       ,agent_account_id 
       ,agent_account_version_id 
       ,campus_id 
       ,order_type 
       ,subject_type 
       ,city 
       ,user_id 
       ,status 
       ,freeze_status 
       ,order_time 
       ,pay_time 
       ,amount_before 
       ,promote_after_amount 
       ,amount 
       ,net_value 
       ,mix_gold_content 
       ,discount_id 
       ,valid_income_percent 
       ,product_line 
       ,payway_id 
       ,is_group_pay 
       ,partner_id 
       ,cancel_type 
       ,cancel_time 
       ,order_source 
       ,is_basic_order 
       ,basic_order_id 
       ,is_agent_order 
       ,is_refunded 
       ,is_changed 
       ,order_c_place 
       ,order_limit_sign 
       ,order_alliance_sign 
       ,order_shortcut_sign 
       ,order_overdue_time 
       ,ip 
       ,create_time 
       ,ts 
       ,order_channel
FROM 
(
    SELECT  id 
           ,order_no 
           ,agent_id 
           ,agent_new_id 
           ,agent_account_id 
           ,agent_account_version_id 
           ,campus_id 
           ,order_type 
           ,subject_type 
           ,city 
           ,user_id 
           ,status 
           ,freeze_status 
           ,order_time 
           ,pay_time 
           ,amount_before 
           ,promote_after_amount 
           ,amount 
           ,net_value 
           ,mix_gold_content 
           ,discount_id 
           ,valid_income_percent 
           ,product_line 
           ,payway_id 
           ,is_group_pay 
           ,partner_id 
           ,cancel_type 
           ,cancel_time 
           ,order_source 
           ,is_basic_order 
           ,basic_order_id 
           ,is_agent_order 
           ,is_refunded 
           ,is_changed 
           ,order_c_place 
           ,order_limit_sign 
           ,order_alliance_sign 
           ,order_shortcut_sign 
           ,order_overdue_time 
           ,ip 
           ,create_time 
           ,ts 
           ,order_channel 
           ,row_number() over(partition by order_no ORDER BY ts desc) rank
    FROM 
    (
        SELECT  id 
               ,order_no 
               ,agent_id 
               ,agent_new_id 
               ,agent_account_id 
               ,agent_account_version_id 
               ,campus_id 
               ,order_type 
               ,subject_type 
               ,city 
               ,user_id 
               ,status 
               ,freeze_status 
               ,order_time 
               ,pay_time 
               ,amount_before 
               ,promote_after_amount 
               ,amount 
               ,net_value 
               ,mix_gold_content 
               ,discount_id 
               ,valid_income_percent 
               ,product_line 
               ,payway_id 
               ,is_group_pay 
               ,partner_id 
               ,cancel_type 
               ,cancel_time 
               ,order_source 
               ,is_basic_order 
               ,basic_order_id 
               ,is_agent_order 
               ,is_refunded 
               ,is_changed 
               ,order_c_place 
               ,order_limit_sign 
               ,order_alliance_sign 
               ,order_shortcut_sign 
               ,order_overdue_time 
               ,ip 
               ,create_time 
               ,ts 
               ,order_channel
        FROM mid.mid_db_order_tb_order
        WHERE dt ='${hivevar:dt_date1}'  
        UNION ALL
        SELECT  id 
               ,order_no 
               ,agent_id 
               ,agent_new_id 
               ,agent_account_id 
               ,agent_account_version_id 
               ,campus_id 
               ,order_type 
               ,subject_type 
               ,city 
               ,user_id 
               ,status 
               ,freeze_status 
               ,order_time 
               ,pay_time 
               ,amount_before 
               ,promote_after_amount 
               ,amount 
               ,net_value 
               ,mix_gold_content 
               ,discount_id 
               ,valid_income_percent 
               ,product_line 
               ,payway_id 
               ,is_group_pay 
               ,partner_id 
               ,cancel_type 
               ,cancel_time 
               ,order_source 
               ,is_basic_order 
               ,basic_order_id 
               ,is_agent_order 
               ,is_refunded 
               ,is_changed 
               ,order_c_place 
               ,order_limit_sign 
               ,order_alliance_sign 
               ,order_shortcut_sign 
               ,order_overdue_time 
               ,ip 
               ,create_time 
               ,ts 
               ,order_channel
        FROM ods.ods_db_order_tb_order
        WHERE dt ='${hivevar:dt}'  
        UNION ALL
        SELECT  id 
               ,order_no 
               ,agent_id 
               ,agent_new_id 
               ,agent_account_id 
               ,agent_account_version_id 
               ,campus_id 
               ,order_type 
               ,subject_type 
               ,city 
               ,user_id 
               ,status 
               ,freeze_status 
               ,order_time 
               ,pay_time 
               ,amount_before 
               ,promote_after_amount 
               ,amount 
               ,net_value 
               ,mix_gold_content 
               ,discount_id 
               ,valid_income_percent 
               ,product_line 
               ,payway_id 
               ,is_group_pay 
               ,partner_id 
               ,cancel_type 
               ,cancel_time 
               ,order_source 
               ,is_basic_order 
               ,basic_order_id 
               ,is_agent_order 
               ,is_refunded 
               ,is_changed 
               ,order_c_place 
               ,order_limit_sign 
               ,order_alliance_sign 
               ,order_shortcut_sign 
               ,order_overdue_time 
               ,ip 
               ,create_time 
               ,ts 
               ,order_channel
        FROM ods.ods_db_trade_order_tb_order_di
        WHERE dt ='${hivevar:dt}'  
    )a 
)b
WHERE rank=1; 
--最后因为mid层数据量很大，我们只保留3天的分区，删除三天前的分区
alter table mid.mid_db_order_tb_order drop IF EXISTS partition (dt='${hivevar:dt_date3}');

3. 数据明细层（ＤＷＤ层）按照主题划分

3.1 DWD层主题划分

dwd明细层，主要是按照业务过程进行主题域划分，对于在线教育，我们一般会划分为如下几个主题：

  • 流量（bhv）、销售(sales)、用户(user)、产品(product)、授课(lesson)、咨询(consult)、投放(put) 七个主题域
  • 按照主题域进行维度建模及多维分析形成汇总数据主题表

3.2 案例介绍

如何构建出销售主题的明细表

dwd_sales_order_product_detail: 订单产品明细表 ,主要包含订单相关信息：订单产品的销售信息，包含用户、支付时间、支付金额、净值等
这个表的构建的SQL代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
set hive.auto.convert.join=true;
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=13;
set hive.exec.reducers.bytes.per.reducer=512000000;


use bdp_tmp;
DROP TABLE IF EXISTS dwd_sales_order_product_detail_temp;
CREATE TABLE dwd_sales_order_product_detail_temp AS
select
    t1.order_no as order_no
    ,t2.product_id as product_id
    ,t1.id as order_id
    ,t2.exam_season_id
    ,t2.price
    ,t2.product_type
    ,t2.product_name
    ,t2.promote_after_amount as op_promote_after_amt
    ,t2.discount_price as op_discount_price
    ,t2.net_value as op_net_value
    ,t2.cost_price as op_cost_price
    ,t2.discount_item_id
    ,t2.buy_num
    ,t2.product_status
    ,t2.end_time
    ,t2.is_give_product
    ,t2.create_time as op_create_time

    ,t1.agent_id as agent_id
    ,t1.agent_new_id as agent_new_id
    ,t1.agent_account_id as agent_account_id
    ,t1.agent_account_version_id as agent_account_version_id
    ,t1.campus_id
    ,t1.order_type
    ,t1.subject_type
    ,t1.city as city_id
    ,t1.user_id
    ,t1.status as order_status
    ,t1.freeze_status as freeze_status
    ,t1.order_time
    ,t1.pay_time
    ,t1.amount_before as order_amt -- 订单原金额
    ,t1.promote_after_amount as promote_after_amt --通过优惠计算后的价格
    ,t1.amount as order_pay_amt -- 订单实付金额
    ,t1.net_value as order_net_value
    ,t1.mix_gold_content
    ,t1.discount_id
    ,t1.product_line
    ,t1.payway_id
    ,t1.is_group_pay
    ,t1.partner_id
    ,t1.cancel_type
    ,t1.cancel_time
    ,t1.order_source as order_fputype_code
    ,t1.is_basic_order
    ,t1.basic_order_id
    ,t1.is_agent_order
    ,t1.is_refunded
    ,t1.is_changed
    ,t1.order_c_place
    ,t1.order_limit_sign
    ,t1.order_alliance_sign
    ,t1.order_shortcut_sign
    ,t1.order_overdue_time
    ,t1.ip
    ,t1.order_channel

    ,t6.resubmit_path
    ,t2.finance_version
    ,t6.referrer

    ,t5.kid
    ,t5.alliance_id as aid
    ,t5.last_alliance_id as last_aid
    ,t5.last_kid
    ,case when t2.discount_recall = 1 and t1.order_type = 4 then 2 -- 退费重算-H单
          when t2.discount_recall = 1 then 3 -- 退费重算-新单
     else 0 end discount_recall -- 退费重算标识
    ,case when t6.order_source  in ('K12_xg_nblz_xslz111963','K12_xg_nblz_xslz111961','K12_xg_nblz_xslz111962') then '1'
          when t6.order_source  in ('K12_wzqd_zr_dswap136402') then '2'
          when t1.order_no like '%SG%' then '1'
          when t1.order_no like '%V_GIFT%' then '2'
     else '0' end as order_class_type



    ,t6.order_source as put_source_channel  -- 投放来源渠道
from
(
        select  *
        from mid.mid_db_order_tb_order
        where dt='${hivevar:dt}'
)t1
join
(
        select
            order_no
            ,product_id
            ,product_line
            ,exam_season_id
            ,price
            ,product_type
            ,product_name
            ,promote_after_amount
            ,discount_price
            ,net_value
            ,cost_price
            ,discount_item_id
            ,partner_id
            ,buy_num
            ,product_status
            ,end_time
            ,is_give_product
            ,create_time
            ,finance_version
            ,get_json_object(extend_info,'$.discountRecall') discount_recall
        from mid.mid_db_order_tb_order_product
        where dt='${hivevar:dt}'
)t2 on t1.order_no=t2.order_no

LEFT JOIN
(
        SELECT  order_no
               ,kid
               ,alliance_id
               ,last_alliance_id
               ,last_kid
        FROM mid.mid_db_trade_order_tb_order_alliance
        WHERE dt='${hivevar:dt}'
)t5 on t1.order_no=t5.order_no
left join
(
    SELECT  user_id -- 用户ID
           ,order_no -- 订单ID
           ,CASE WHEN get_json_object(extend_param,'$.resubmit_path') is not null THEN get_json_object(extend_param,'$.resubmit_path')  ELSE order_source END AS resubmit_path -- 渠道
           ,referrer -- URL
           ,order_source -- 投放渠道来源
    FROM mid.mid_db_order_pe_order_extend_info
    WHERE dt = '${hivevar:dt}'
)t6 on t1.user_id = t6.user_id and t1.order_no = t6.order_no
;
INSERT OVERWRITE TABLE dwd.dwd_sales_order_product_detail_da PARTITION (dt='${hivevar:dt}')
select
    order_no
    ,a.product_id
    ,order_id
    ,exam_season_id
    ,price
    ,product_type
    ,product_name
    ,op_promote_after_amt
    ,op_discount_price
    ,op_net_value
    ,op_cost_price
    ,discount_item_id
    ,buy_num
    ,product_status
    ,end_time
    ,is_give_product
    ,op_create_time

    ,agent_id
    ,agent_new_id
    ,agent_account_id
    ,agent_account_version_id
    ,campus_id
    ,order_type
    ,subject_type
    ,city_id
    ,user_id
    ,order_status
    ,freeze_status
    ,order_time
    ,pay_time
    ,order_amt -- 订单原金额
    ,promote_after_amt --通过优惠计算后的价格
    ,order_pay_amt -- 订单实付金额
    ,order_net_value
    ,mix_gold_content
    ,discount_id
    ,product_line
    ,payway_id
    ,is_group_pay
    ,partner_id
    ,cancel_type
    ,cancel_time
    ,order_fputype_code
    ,is_basic_order
    ,a.basic_order_id
    ,is_agent_order
    ,is_refunded
    ,is_changed
    ,order_c_place
    ,order_limit_sign
    ,order_alliance_sign
    ,order_shortcut_sign
    ,order_overdue_time
    ,ip
    ,order_channel


    ,resubmit_path
    ,finance_version
    ,referrer

    ,kid
    ,aid
    ,last_aid
    ,last_kid
    ,case when b.basic_order_id is not null then 101 --退费重算原单
          when discount_recall=2 then 401 -- 退费重算-H单
          when discount_recall=3 then 201 --退费重算-新单
          when order_type=3 then 300 -- 退课 R单
          when order_type=1 then 100 -- 购买
          when order_type=2 then 200 --换课新单
          when order_type=4 then 400 --H单
          else 999 end as sub_order_type
    ,order_class_type
    ,put_source_channel
from mds_sales_order_product_detail_temp a
left join
(
        SELECT  basic_order_id
               ,product_id
        FROM dwd_sales_order_product_detail_temp
        WHERE discount_recall = 2
        GROUP BY  basic_order_id
                 ,product_id
) b
ON a.order_id = b.basic_order_id AND a.product_id = b.product_id ;

4. 在线教育数仓模型体系（星形如何构造出来）

5. 在线教育指标体系/报表层如何构建

6. 数仓开发hive实践指南

6.1 hive基础语法

展开全文 >>

现代数据仓库的建设之路

什么是数据仓库

数据仓库 ，由数据仓库之父比尔·恩门（Bill Inmon）于1990年提出，他在1991年出版的“Building the Data Warehouse”（《建立数据仓库》）一书中所提出的数据仓库的定义——数据仓库（Data Warehouse）是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合，用于支持管理决策(Decision Making Support)。
现在我们构建的数据仓库，仍然是在Bill Inmon提出的数仓理论基础之上，只不过现在根据时代不同，需求变了，衍生出各种工具和技术来支持数据仓库的发展。比如：大数据技术，数据湖技术，流计算技术等。

more >>

展开全文 >>

be crash引起的IO高的问题

最近，doris集群不定时出现be节点宕掉的情况，查看监控发现在宕掉的节点load很高，io write写很高。所以首先怀疑有大的sql执行。经过一番排查，始终找不出是什么sql会导致这么高的io。于是又怀疑是不是broker上的同步任务导致的，于是我在机器上写了个监控程序，看挂的时候到底是那个进程导致了这么高的io.

经过一番操作，过几天后又出现be挂掉的情况，于是登录上发现有个abrt-hook-ccpp的进程io很高，这时才找到了真正的问题所在。

abrt-hook-ccpp 进程

linux 上的c/c++程序，当出现一些内存越界，非法访问的问题时会导致进程直接crash，现象就是直接挂掉了，这种问题很难排查。好在Linux内核会监控这个crash的情况，会捕获到进程崩溃信息，然后将进程的coredump信息写入到文件中。

怎么生成coredump信息

  • 开启生成coredump信息

    • 输入命令ulimit -a 
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      core file size          (blocks, -c) 0
      data seg size           (kbytes, -d) unlimited
      scheduling priority             (-e) 0
      file size               (blocks, -f) unlimited
      pending signals                 (-i) 513562
      max locked memory       (kbytes, -l) 64
      max memory size         (kbytes, -m) unlimited
      open files                      (-n) 10240
      pipe size            (512 bytes, -p) 8
      POSIX message queues     (bytes, -q) 819200
      real-time priority              (-r) 0
      stack size              (kbytes, -s) 10240
      cpu time               (seconds, -t) unlimited
      max user processes              (-u) 513562
      virtual memory          (kbytes, -v) unlimited
      file locks                      (-x) unlimited
      其中的 -c: core file size 如果设置为0的话，当程序崩溃的时候就不会产生core文件。
      1
      2
      ulimit -c 1024 #设置CoreDump文件大小为1024k
      ulimit -c unlimited #不限制CoreDump文件大小
      注意：通常be的coredump很大，不要设置成 unlimited

  • 指定生成CoreDump文件的路径

默认情况下，coreDump生成的文件名为core，默认在进程当前目前。

/proc/sys/kernel/core_pattern 可以设置格式化的 core文件保存位置或文件名，默认的是 |/usr/libexec/abrt-hook-ccpp %s %c %p %u %g %t e
可以使用命令修改：

1
sysctl kernel.core_pattern="/tmp/core_%t_%e_%p"

关于abrt-hook-ccpp

上面的core_pattern默认值是

1
|/usr/libexec/abrt-hook-ccpp %s %c %p %u %g %t e

这种格式的意思是：如果core_pattern 中第一个字符是 Linux管道符 |, 那么Linux 内核在捕获进程崩溃信息的时候，就会以root权限执行管道符后门的程序或者脚本，将进程崩溃信息传递给这个程序或者脚本。

abrt-hook-ccpp 是 abrt进程的一个钩子程序。abrt-hook-ccpp会收集coredump信息，把这些信息写入 /var/spool/abrt 这个目录，供abrt服务使用。

Abrt是linux一个自动化 Bug 检测和报告工具。这是红帽领导开发的一个项目

ABRT is a set of tools to help users detect and report application crashes. Its main purpose is to ease the process of reporting an issue and finding a solution.

参考：https://abrt.readthedocs.io/en/latest/

展开全文 >>

【debezium源码分析】-插件架构

背景

本文主要是从全局角度来分析debezium的插件架构特性，帮助大家对debezium有一个宏观的了解。

架构

debezium的使用方式目前有3种形式：

  1. 部署作为一个kafka connector in Kafka Connect cluster
  2. 部署在Debezium Server里
  3. 作为一个embedded engine 在自己的应用里

本文主要讨论作为embedded engine的时候的架构。

嵌入式引擎需要实现DebeziumEngine接口，这个接口也是一个Runnable，所以它的入口方法是在run方法里。

1
public interface DebeziumEngine<R> extends Runnable, Closeable
more >>

展开全文 >>

binlog事件数据结构

binlog事件数据结构

  • TABLE_MAP

    row格式的binlog文件中，每个ROW_EVENT之前都有一个TABLE_MAP_EVENT，用于描述该更新的元数据信息，记录包括，tableId, database,table,columnTypes等

结构如下： 

1
2
3
监听的事件类型:TABLE_MAP
header:EventHeaderV4{timestamp=1629959676000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=35, nextPosition=9806, flags=0}
data:TableMapEventData{tableId=1447, database='flink_sql', table='xx', columnTypes=3, 15, 3, columnMetadata=0, 180, 0, columnNullability={1, 2}, eventMetadata=null}
more >>

展开全文 >>

实时数仓在新东方在线教育的落地实践

背景介绍

在传统数据仓库方面，通常以T+1离线批量计算为主，按照数仓建模方式，把要处理的业务按照主题域划分，构建各种数据模型，来满足公司经营分析，财务分析等各种公司管理层的数据需求。
然而，随着在线教育快速发展市场竞争非常激烈，T+1的方式在某些需求上很难对业务产生实际的价值，很可能因为数据延迟导致业务动作滞后，管理要求跟进不及时，最终导致客户流失，影响公司业务发展。
目前我们遇到的主要痛点如下

more >>

展开全文 >>

Flink cdc 2.0 尝鲜

前几天flink cdc 发布了2.0版本，于是跟进了一下。2.0版本主要解决1.x里的几个重要问题，如下：

  1. 全量 + 增量读取的过程需要保证所有数据的一致性，因此需要通过加锁保证，但是加锁在数据库层面上是一个十分高危的操作。底层 Debezium 在保证数据一致性时，需要对读取的库或表加锁，全局锁可能导致数据库锁住，表级锁会锁住表的读，DBA 一般不给锁权限。

    more >>

展开全文 >>

本站总访问量次 |

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

加我微信拉你进入实时数仓交流群，微信：iweibo_x 谢谢大家