本章节为您介绍使用数据智能平台前需要完成的数据准备工作。这些数据主要用于数据汇聚、规范设计、指标与标签体系构建、数据质量管控、数据资产管理、数据资产服务与共享、智能分析等全领域。

前提条件

  • 您已经注册阿里云账号并且已购买数据资源平台,详细请参见购买数据资源平台
  • 已备建表语句、表中需要插入的语句和质量校验函数jar包源代码。

背景信息

下面以在RDS数据中创建表yhkh和xyk_bkxx;在MaxCompute数据库中创建表stg_base_yhkh_di、stg_base_xykbkjl_di、dws_ren_kh_di和dws_wp_xyk_di为例介绍。

数据库表插入方式说明

  • MaxCompute数据库的表和数据支持通过工具插入,例如MaxCompute Studio工具,详细请见MaxCompute Studio对应手册;也支持在数据建模中创建表,详细请参见设计数据模型,在数据开发中插入表数据,详细请参见添加计算节点并发布版本
  • RDS数据库的表支持通过工具插入,例如RDS工具,详细请见RDS对应手册。

数据库表含义说明

RDS数据库表含义如下:

  • yhkh:表示银行客户信息。
  • xyk_bkxx:表示银行卡办卡记录。

MaxCompute数据库表含义如下:

  • stg_base_yhkh_di:表示原始数据层银行客户信息。
  • stg_base_xykbkjl_di:表示原始数据层信用卡信息。
  • dws_ren_kh_di:表示融合数据层银行客户信息。
  • dws_wp_xyk_di:表示融合数据层信用卡信息。

RDS数据库表语句

yhkh表语句

DDL语句:
CREATE TABLE IF NOT EXISTS yhkh (
    id int PRIMARY KEY COMMENT '主键',
    zzhm varchar(12) COMMENT '证件号码',
    khmc varchar(4) COMMENT '客户名称',
    nl int(5) COMMENT '年龄',
    xb varchar(2) COMMENT '性别 0:男 1:女',
    yddh1 varchar(16) COMMENT '移动电话1',
    csrq date COMMENT '出生日期',
    jysp varchar(2) COMMENT '教育水平 0:研究生 1:本科 2:高中 3:职业高中 4:职业学院',
    career varchar(2) COMMENT '职业 0:保险 1:教师 2:外企白领 3:自由职业 4:IT工程师',
    hyzk varchar(2) COMMENT '婚姻状况 0:已婚 1:未婚',
    gz int COMMENT '工资',
    decdbyjyje int COMMENT '大额存单本月交易金额',
    decdbyjybs int COMMENT '大额存单本月交易笔数',
    zjzdjyrq date COMMENT '最近主动交易日期',
    jtnsr int COMMENT '家庭年收入',
    dqckcy int COMMENT '定期存款持有'
) ENGINE = InnoDB CHARSET = utf8 COMMENT '银行客户信息';
插入语句:
INSERT INTO yhkh
VALUES (1, 'CKR-1000', '谢敏', 33, 0
        , '(86)31934620595', '2002-03-06', 0, 3, 1
        , 80000, 95429, 31, '2019-05-13', 1425574
        , 83489),
    (2, 'CKR-2000', '罗静', 68, 1
        , '+8623647553845', '2009-11-15', 0, 3, 0
        , 40000, 54194, 34, '2001-10-15', 4732362
        , 99694),
    (3, 'CKR-3000', '汤洋', 46, 1
        , '33904547704', '1993-09-04', 0, 3, 1
        , 40000, 61863, 58, '1987-10-06', 7593193
        , 11215),
    (4, 'CKR-4000', '何平', 44, 0
        , '28290449032', '1999-07-22', 1, 3, 0
        , 35000, 61082, 60, '1989-10-07', 9177195
        , 60752),
    (5, 'CKR-5000', '文娜', 36, 0
        , '25235166267', '1992-07-15', 1, 3, 0
        , 25000, 71953, 35, '2001-04-04', 2925278
        , 4165),
    (6, 'CKR-1005', '江静', 15, 0
        , '26631327402', '1981-03-04', 1, 3, 0
        , 15000, 43857, 42, '2014-01-08', 5253141
        , 65308),
    (7, 'CKR-1006', '汤丽', 6, 0
        , '38099177920', '1986-10-20', 1, 3, 1
        , 10000, 8503, 86, '1970-09-12', 5296756
        , 85285),
    (8, 'CKR-1007', '江军', 62, 1
        , '21731353562', '1982-04-27', 0, 3, 0
        , 17000, 65718, 86, '1989-05-23', 1762593
        , 96453),
    (9, 'CKR-1008', '雷秀英', 34, 0
        , '21647630365', '1989-06-23', 4, 3, 0
        , 50000, 97157, 12, '1977-08-16', 763445
        , 99478),
    (10, 'CKR-1009', '孟超', 95, 0
        , '35153310990', '2008-07-12', 0, 2, 1
        , 15000, 68605, 54, '2011-10-13', 6635322
        , 87174),
    (11, 'CKR-1010', '常超', 71, 0
        , '35476048078', '2008-06-10', 0, 2, 1
        , 8500, 49055, 98, '1978-01-21', 3847031
        , 51571),
    (12, 'CKR-1011', '乔强', 34, 1
        , '37050349190', '1977-11-03', 1, 2, 1
        , 15000, 72188, 65, '2019-08-09', 9715625
        , 11546),
    (13, 'CKR-1012', '金军', 4, 1
        , '+8628364711221', '1977-11-11', 1, 2, 0
        , 17000, 65132, 39, '1992-11-26', 9875371
        , 93019),
    (14, 'CKR-1013', '龚明', 78, 0
        , '33321797005', '2005-12-10', 1, 2, 0
        , 12000, 15774, 83, '1973-12-30', 5007852
        , 78029),
    (15, 'CKR-1014', '何刚', 57, 0
        , '20377333193', '1977-01-27', 1, 2, 1
        , 17000, 12815, 55, '2007-10-22', 409723
        , 62655),
    (16, 'CKR-1015', '贺明', 18, 1
        , '36369234358', '2001-12-25', 4, 4, 1
        , 10000, 90515, 3, '1989-01-07', 751082
        , 18248),
    (17, 'CKR-1016', '潘超', 76, 0
        , '28539508428', '2003-12-24', 4, 4, 1
        , 17000, 68525, 44, '1995-07-22', 7495966
        , 81766),
    (18, 'CKR-1017', '潘明', 74, 1
        , '22110074676', '1973-06-06', 1, 4, 1
        , 10000, 71531, 78, '2011-07-26', 366257
        , 7696),
    (19, 'CKR-1018', '顾秀英', 92, 0
        , '35738683766', '2017-11-11', 1, 4, 0
        , 30000, 62387, 1, '1988-05-21', 2213117
        , 85397),
    (20, 'CKR-1019', '马娜', 13, 0
        , '+8621708223252', '2014-07-02', 1, 4, 0
        , 35000, 66701, 68, '1990-11-03', 8238187
        , 65044),
    (21, 'CKR-1020', '曾磊', 78, 0
        , '21200567551', '1983-05-18', 1, 1, 0
        , 35000, 53658, 26, '2001-04-06', 7262309
        , 36107),
    (22, 'CKR-1021', '阎涛', 77, 0
        , '23252779412', '2014-05-21', 1, 1, 1
        , 15000, 38751, 18, '2006-01-18', 4477225
        , 12690),
    (23, 'CKR-1022', '魏勇', 96, 1
        , '33683988055', '1978-04-18', 4, 1, 1
        , 17000, 31883, 2, '1983-03-11', 8062079
        , 48940),
    (24, 'CKR-1023', '赵静', 55, 1
        , '35197158809', '1998-05-10', 0, 0, 1
        , 12000, 52528, 97, '1999-10-03', 4701315
        , 97298),
    (25, 'CKR-1024', '魏敏', 12, 0
        , '31535636658', '1999-02-13', 2, 0, 1
        , 15000, 33481, 27, '1985-10-14', 7808248
        , 8086),
    (26, 'CKR-1025', '乔娟', 38, 1
        , '+8616799174454', '1978-03-29', 1, 0, 0
        , 16000, 71075, 83, '1993-09-10', 5946732
        , 7717),
    (27, 'CKR-1026', '邓娟', 79, 0
        , '34327912543', '2007-08-20', 4, 0, 0
        , 14000, 44632, 84, '1976-08-17', 9530773
        , 22782),
    (28, 'CKR-1027', '贺伟', 38, 0
        , '33089112291', '2006-05-24', 0, 0, 0
        , 8500, 94004, 49, '1983-02-01', 6219669
        , 29030),
    (29, 'CKR-1028', '谭强', 80, 0
        , '+8620283740235', '1973-12-13', 4, 0, 0
        , 15000, 7536, 24, '2003-11-18', 8550386
        , 9772),
    (30, 'CKR-1029', '余涛', 93, 1
        , '24091917546', '2017-10-26', 4, 0, 1
        , 14000, 77595, 40, '1977-11-19', 5950564
        , 73535),
    (31, 'CKR-1030', '谢明', 21, 0
        , '21662351966', '1992-12-31', 2, 0, 0
        , 17000, 65481, 86, '1978-11-14', 5902767
        , 80462),
    (32, 'CKR-1031', '陆丽', 50, 0
        , '(86)21065489862', '2018-03-04', 1, 0, 1
        , 17000, 16100, 45, '1999-07-13', 7209351
        , 18872),
    (33, 'CKR-1032', '谭秀英', 14, 1
        , '33658105481', '1986-01-09', 0, 0, 0
        , 7000, 87617, 40, '2013-07-14', 7266776
        , 26436),
    (34, 'CKR-1033', '胡明', 17, 0
        , '25078360286', '2002-02-13', 1, 2, 1
        , 11000, 11490, 67, '1993-01-25', 1498183
        , 51372),
    (35, 'CKR-1034', '史秀英', 60, 0
        , '34319771121', '1973-05-16', 1, 2, 1
        , 45000, 87909, 24, '2018-06-05', 2030521
        , 26098),
    (36, 'CKR-1035', '苏军', 72, 0
        , '34047439055', '1988-10-30', 1, 2, 1
        , 6700, 29314, 5, '1995-10-09', 7693879
        , 68804),
    (37, 'CKR-1036', '叶明', 71, 0
        , '20433074633', '2018-04-02', 2, 2, 0
        , 8000, 81260, 43, '2000-11-27', 7005542
        , 29571),
    (38, 'CKR-1037', '潘明', 14, 0
        , '34489635278', '1987-12-23', 1, 2, 1
        , 10000, 8007, 8, '2018-02-23', 9003028
        , 20745),
    (39, 'CKR-1038', '许磊', 4, 0
        , '21405616984', '1990-08-22', 2, 2, 0
        , 10000, 78111, 74, '1994-01-01', 9313883
        , 19159),
    (40, 'CKR-1039', '余强', 30, 0
        , '(86)30699305187', '2002-12-20', 2, 1, 1
        , 8500, 43071, 34, '1992-01-19', 3081415
        , 94513),
    (41, 'CKR-1040', '崔娟', 11, 0
        , '34029933814', '1986-06-19', 2, 1, 0
        , 5000, 87999, 5, '1987-02-13', 178974
        , 61325),
    (42, 'CKR-1041', '贺娟', 97, 0
        , '25353299318', '2001-02-18', 4, 1, 1
        , 6700, 78972, 78, '1972-02-13', 5901890
        , 70525),
    (43, 'CKR-1042', '许霞', 85, 0
        , '36913969766', '2002-05-06', 1, 0, 1
        , 5000, 91055, 24, '2018-01-05', 6859793
        , 78753),
    (44, 'CKR-1043', '沈平', 10, 0
        , '(86)25870287190', '1981-09-02', 0, 0, 0
        , 6800, 63460, 13, '1980-10-23', 2177974
        , 76217),
    (45, 'CKR-1044', '万强', 20, 1
        , '26961761882', '1979-04-07', 2, 0, 1
        , 6600, 39348, 94, '1985-12-10', 1728750
        , 36261),
    (46, 'CKR-1045', '袁伟', 99, 0
        , '29666782625', '1989-10-22', 2, 0, 0
        , 6500, 92593, 2, '1990-03-07', 1821075
        , 11599),
    (47, 'CKR-1046', '傅勇', 23, 1
        , '26923733892', '2004-02-17', 4, 0, 0
        , 7200, 68864, 99, '2016-08-26', 1387723
        , 28969),
    (48, 'CKR-1047', '龚敏', 6, 1
        , '26155576181', '1989-02-04', 0, 0, 1
        , 5000, 79006, 60, '1988-01-04', 7876225
        , 82518),
    (49, 'CKR-1048', '彭超', 17, 0
        , '28200329738', '1999-05-11', 1, 0, 1
        , 5000, 14056, 61, '1983-08-25', 5280068
        , 75582),
    (50, 'CKR-1049', '乔军', 44, 0
        , '20047581812', '1983-01-15', 2, 1, 0
        , 5000, 6073, 86, '1970-03-27', 6822252
        , 55308),
    (51, 'CKR-1050', '贺娟', 4, 0
        , '24457838800', '1990-10-21', 4, 1, 0
        , 5000, 89753, 89, '2004-07-30', 8107579
        , 27253),
    (52, 'CKR-1051', '蒋娟', 83, 0
        , '33187981781', '1981-07-26', 2, 1, 0
        , 12000, 89547, 39, '1996-03-27', 9546849
        , 98847),
    (53, 'CKR-1052', '阎娟', 43, 0
        , '24966035583', '1994-10-30', 4, 0, 1
        , 9000, 56956, 44, '1975-01-18', 115463
        , 75243),
    (54, 'CKR-1053', '范伟', 92, 1
        , '20735252149', '2016-11-16', 3, 0, 1
        , 8000, 99009, 10, '2014-07-05', 6628234
        , 97674),
    (55, 'CKR-1054', '苏丽', 34, 0
        , '34261900542', '2014-07-15', 2, 0, 0
        , 7000, 81145, 33, '2004-07-14', 4140885
        , 14247),
    (56, 'CKR-1055', '赵娟', 91, 0
        , '25229751191', '2001-04-04', 1, 0, 0
        , 8500, 60527, 5, '1988-04-21', 3255066
        , 88485),
    (57, 'CKR-1056', '赵丽', 84, 0
        , '33892657660', '1980-11-27', 1, 0, 0
        , 8500, 42275, 51, '1988-10-16', 8660761
        , 75908),
    (58, 'CKR-1057', '邓磊', 11, 0
        , '24903084592', '1973-11-06', 4, 0, 0
        , 9000, 57619, 62, '1998-09-11', 8035936
        , 6989),
    (59, 'CKR-1058', '张芳', 36, 0
        , '26599014399', '1973-02-09', 1, 0, 1
        , 7200, 33170, 35, '1980-08-20', 3885281
        , 42114),
    (60, 'CKR-1059', '孙平', 35, 0
        , '30124201907', '1989-05-29', 1, 3, 0
        , 6800, 29221, 17, '2001-06-08', 7175461
        , 39317),
    (61, 'CKR-1060', '金霞', 4, 1
        , '32989752689', '2018-04-06', 0, 3, 0
        , 6900, 56864, 48, '1984-09-19', 5103692
        , 34747),
    (62, 'CKR-1061', '谢芳', 37, 1
        , '22325686051', '1981-05-12', 4, 3, 1
        , 7100, 32834, 34, '2006-05-05', 8119980
        , 44347),
    (63, 'CKR-1062', '石勇', 28, 0
        , '31151192235', '2013-02-06', 4, 3, 0
        , 7000, 45832, 81, '2016-06-03', 7215690
        , 60679),
    (64, 'CKR-1063', '蒋伟', 83, 0
        , '32727409172', '1975-08-14', 1, 3, 1
        , 7000, 28624, 67, '2005-05-19', 401890
        , 4493),
    (65, 'CKR-1064', '尹娜', 14, 0
        , '26966147594', '2014-12-16', 2, 2, 1
        , 5000, 50594, 88, '2010-09-03', 4781451
        , 52552),
    (66, 'CKR-1065', '阎伟', 4, 1
        , '29771084127', '1983-01-12', 1, 2, 1
        , 9000, 34747, 46, '1972-12-15', 3021194
        , 99764),
    (67, 'CKR-1066', '杨磊', 3, 1
        , '25880125057', '2005-11-01', 4, 2, 0
        , 6500, 93199, 33, '1989-07-26', 772627
        , 25525),
    (68, 'CKR-1067', '雷平', 10, 1
        , '21501765761', '2016-04-02', 1, 2, 1
        , 7500, 33137, 78, '1989-09-21', 3897294
        , 95987),
    (69, 'CKR-1068', '许杰', 13, 0
        , '34808948302', '2004-06-12', 2, 3, 1
        , 4400, 14611, 90, '1992-03-07', 8212692
        , 30070),
    (70, 'CKR-1069', '熊娜', 80, 1
        , '34238155863', '2003-03-07', 3, 3, 1
        , 4800, 76704, 63, '1977-02-16', 5031631
        , 8627),
    (71, 'CKR-1070', '邓勇', 96, 0
        , '36763644599', '1983-12-15', 0, 3, 0
        , 3700, 74704, 82, '2002-11-05', 3221507
        , 43394),
    (72, 'CKR-1071', '赖芳', 4, 0
        , '24563419827', '1996-10-11', 4, 3, 0
        , 6500, 96055, 18, '2008-03-21', 7341282
        , 90341),
    (73, 'CKR-1072', '王超', 27, 1
        , '38674282514', '1980-08-30', 2, 3, 1
        , 5200, 87710, 34, '1985-10-11', 6442607
        , 52075),
    (74, 'CKR-1073', '秦强', 95, 1
        , '32181869518', '2018-04-25', 1, 2, 0
        , 4650, 88563, 96, '2009-01-05', 2643763
        , 84598),
    (75, 'CKR-1074', '贺涛', 10, 0
        , '33063447619', '2006-10-14', 1, 2, 1
        , 4550, 13903, 18, '1984-01-26', 345408
        , 81526),
    (76, 'CKR-1075', '雷平', 61, 0
        , '24713147347', '1996-08-30', 2, 2, 1
        , 4400, 32375, 0, '1992-06-04', 7901871
        , 29745),
    (77, 'CKR-1076', '秦娟', 61, 0
        , '22435879686', '2013-07-03', 2, 2, 1
        , 4700, 16121, 49, '1979-04-15', 8668010
        , 15787),
    (78, 'CKR-1077', '郭磊', 12, 1
        , '27447270138', '2014-02-23', 1, 3, 1
        , 4500, 32254, 2, '1988-10-12', 2226230
        , 18156),
    (79, 'CKR-1078', '苏娟', 76, 0
        , '20890972094', '1973-04-28', 1, 3, 0
        , 8000, 53199, 2, '1982-07-07', 635335
        , 73580),
    (80, 'CKR-1079', '朱娟', 22, 1
        , '34028589330', '2018-10-06', 4, 3, 1
        , 10000, 85964, 38, '1999-06-05', 8596230
        , 14763),
    (81, 'CKR-1080', '孙静', 93, 1
        , '26195165677', '1994-09-18', 0, 3, 0
        , 10000, 15577, 82, '1975-03-25', 5012535
        , 14102);

xyk_bkxx表语句

DDL语句:
CREATE TABLE IF NOT EXISTS xyk_bkxx (
    id int PRIMARY KEY COMMENT '主键',
    cid varchar(12) COMMENT '卡ID',
    climit int COMMENT '卡额度',
    actdate varchar(20) COMMENT '统计日期',
    channel varchar(4) COMMENT '办卡渠道',
    rid varchar(12) COMMENT '持卡人ID'
) ENGINE = InnoDB CHARSET = utf8 COMMENT '银行卡办卡记录';
插入数据语句:
INSERT INTO xyk_bkxx
VALUES (1, 'XYK-1001', 5, '1514736000', '渠道1'
        , 'CKR-1000'),
    (2, 'XYK-1002', 5, '1517414400', '渠道1'
        , 'CKR-1000'),
    (3, 'XYK-1003', 10, '1517587200', '渠道1'
        , 'CKR-1000'),
    (4, 'XYK-1004', 5, '1520611200', '渠道1'
        , 'CKR-1000'),
    (5, 'XYK-1005', 10, '1528128000', '渠道4'
        , 'CKR-1000'),
    (6, 'XYK-1006', 15, '1528041600', '渠道4'
        , 'CKR-1000'),
    (7, 'XYK-1007', 10, '1527955200', '渠道4'
        , 'CKR-1000'),
    (8, 'XYK-1008', 15, '1527868800', '渠道4'
        , 'CKR-1000'),
    (9, 'XYK-1009', 10, '1527782400', '渠道4'
        , 'CKR-1000'),
    (10, 'XYK-2001', 20, '1483200000', '渠道1'
        , 'CKR-2000'),
    (11, 'XYK-2002', 10, '1496246400', '渠道1'
        , 'CKR-2000'),
    (12, 'XYK-2003', 15, '1515945600', '渠道2'
        , 'CKR-2000'),
    (13, 'XYK-2004', 5, '1516809600', '渠道2'
        , 'CKR-2000'),
    (14, 'XYK-2005', 5, '1527177600', '渠道3'
        , 'CKR-2000'),
    (15, 'XYK-2006', 15, '1531584000', '渠道3'
        , 'CKR-2000'),
    (16, 'XYK-3001', 10, '1520092800', '渠道3'
        , 'CKR-3000'),
    (17, 'XYK-3002', 10, '1520092800', '渠道3'
        , 'CKR-3000'),
    (18, 'XYK-3003', 10, '1520092800', '渠道3'
        , 'CKR-3000'),
    (19, 'XYK-3004', 10, '1520179200', '渠道3'
        , 'CKR-3000'),
    (20, 'XYK-3005', 10, '1520179200', '渠道3'
        , 'CKR-3000'),
    (21, 'XYK-4001', 10, '1507910400', '渠道2'
        , 'CKR-4000'),
    (22, 'XYK-4002', 15, '1522339200', '渠道2'
        , 'CKR-4000'),
    (23, 'XYK-4003', 5, '1527782400', '渠道2'
        , 'CKR-4000'),
    (24, 'XYK-5001', 5, '1527955200', '渠道4'
        , 'CKR-5000'),
    (25, 'XYK-5002', 10, '1527955200', '渠道5'
        , 'CKR-5000'),
    (26, 'XYK-5003', 10, '1527955200', '渠道5'
        , 'CKR-5000'),
    (27, 'XYK-5004', 20, '1527955200', '渠道4'
        , 'CKR-5000'),
    (28, 'XYK-5005', 10, '1528041600', '渠道4'
        , 'CKR-5000'),
    (29, 'XYK-5006', 10, '1528041600', '渠道5'
        , 'CKR-5000'),
    (30, 'XYK-5007', 15, '1528041600', '渠道4'
        , 'CKR-5000'),
    (31, 'XYK-5008', 5, '1528214400', '渠道4'
        , 'CKR-5000'),
    (32, 'XYK-5009', 15, '1528214400', '渠道4'
        , 'CKR-5000'),
    (33, 'XYK-5010', 5, '1528300800', '渠道4'
        , 'CKR-5000');

MaxCompute数据库表语句

stg_base_yhkh_di表语句

DDL语句:
CREATE TABLE IF NOT EXISTS stg_base_yhkh_di (
    id bigint COMMENT '主键',
    zzhm string COMMENT '证件号码',
    khmc string COMMENT '客户名称',
    nl bigint COMMENT '年龄',
    xb bigint COMMENT '性别 0:男 1:女',
    yddh1 string COMMENT '移动电话1',
    csrq string COMMENT '出生日期',
    jysp bigint COMMENT '教育水平 0:研究生 1:本科 2:高中 3:职业高中 4:职业学院',
    career bigint COMMENT '职业 0:保险 1:教师 2:外企白领 3:自由职业 4:it工程师',
    hyzk bigint COMMENT '婚姻状况 0:已婚 1:未婚',
    gz bigint COMMENT '工资',
    decdbyjyje bigint COMMENT '大额存单本月交易金额',
    decdbyjybs bigint COMMENT '大额存单本月交易笔数',
    zjzdjyrq string COMMENT '最近主动交易日期',
    jtnsr bigint COMMENT '家庭年收入',
    dqckcy bigint COMMENT '定期存款持有'
) COMMENT '银行客户信息';
插入数据语句:
INSERT INTO stg_base_yhkh_di
VALUES (1, 'CKR-1000', '谢敏', 33, 0
        , '(86)31934620595', '2002-03-06', 0, 3, 1
        , 80000, 95429, 31, '2019-05-13', 1425574
        , 83489),
    (2, 'CKR-2000', '罗静', 68, 1
        , '+8623647553845', '2009-11-15', 0, 3, 0
        , 40000, 54194, 34, '2001-10-15', 4732362
        , 99694),
    (3, 'CKR-3000', '汤洋', 46, 1
        , '33904547704', '1993-09-04', 0, 3, 1
        , 40000, 61863, 58, '1987-10-06', 7593193
        , 11215),
    (4, 'CKR-4000', '何平', 44, 0
        , '28290449032', '1999-07-22', 1, 3, 0
        , 35000, 61082, 60, '1989-10-07', 9177195
        , 60752),
    (5, 'CKR-5000', '文娜', 36, 0
        , '25235166267', '1992-07-15', 1, 3, 0
        , 25000, 71953, 35, '2001-04-04', 2925278
        , 4165),
    (6, 'CKR-1005', '江静', 15, 0
        , '26631327402', '1981-03-04', 1, 3, 0
        , 15000, 43857, 42, '2014-01-08', 5253141
        , 65308),
    (7, 'CKR-1006', '汤丽', 6, 0
        , '38099177920', '1986-10-20', 1, 3, 1
        , 10000, 8503, 86, '1970-09-12', 5296756
        , 85285),
    (8, 'CKR-1007', '江军', 62, 1
        , '21731353562', '1982-04-27', 0, 3, 0
        , 17000, 65718, 86, '1989-05-23', 1762593
        , 96453),
    (9, 'CKR-1008', '雷秀英', 34, 0
        , '21647630365', '1989-06-23', 4, 3, 0
        , 50000, 97157, 12, '1977-08-16', 763445
        , 99478),
    (10, 'CKR-1009', '孟超', 95, 0
        , '35153310990', '2008-07-12', 0, 2, 1
        , 15000, 68605, 54, '2011-10-13', 6635322
        , 87174),
    (11, 'CKR-1010', '常超', 71, 0
        , '35476048078', '2008-06-10', 0, 2, 1
        , 8500, 49055, 98, '1978-01-21', 3847031
        , 51571),
    (12, 'CKR-1011', '乔强', 34, 1
        , '37050349190', '1977-11-03', 1, 2, 1
        , 15000, 72188, 65, '2019-08-09', 9715625
        , 11546),
    (13, 'CKR-1012', '金军', 4, 1
        , '+8628364711221', '1977-11-11', 1, 2, 0
        , 17000, 65132, 39, '1992-11-26', 9875371
        , 93019),
    (14, 'CKR-1013', '龚明', 78, 0
        , '33321797005', '2005-12-10', 1, 2, 0
        , 12000, 15774, 83, '1973-12-30', 5007852
        , 78029),
    (15, 'CKR-1014', '何刚', 57, 0
        , '20377333193', '1977-01-27', 1, 2, 1
        , 17000, 12815, 55, '2007-10-22', 409723
        , 62655),
    (16, 'CKR-1015', '贺明', 18, 1
        , '36369234358', '2001-12-25', 4, 4, 1
        , 10000, 90515, 3, '1989-01-07', 751082
        , 18248),
    (17, 'CKR-1016', '潘超', 76, 0
        , '28539508428', '2003-12-24', 4, 4, 1
        , 17000, 68525, 44, '1995-07-22', 7495966
        , 81766),
    (18, 'CKR-1017', '潘明', 74, 1
        , '22110074676', '1973-06-06', 1, 4, 1
        , 10000, 71531, 78, '2011-07-26', 366257
        , 7696),
    (19, 'CKR-1018', '顾秀英', 92, 0
        , '35738683766', '2017-11-11', 1, 4, 0
        , 30000, 62387, 1, '1988-05-21', 2213117
        , 85397),
    (20, 'CKR-1019', '马娜', 13, 0
        , '+8621708223252', '2014-07-02', 1, 4, 0
        , 35000, 66701, 68, '1990-11-03', 8238187
        , 65044),
    (21, 'CKR-1020', '曾磊', 78, 0
        , '21200567551', '1983-05-18', 1, 1, 0
        , 35000, 53658, 26, '2001-04-06', 7262309
        , 36107),
    (22, 'CKR-1021', '阎涛', 77, 0
        , '23252779412', '2014-05-21', 1, 1, 1
        , 15000, 38751, 18, '2006-01-18', 4477225
        , 12690),
    (23, 'CKR-1022', '魏勇', 96, 1
        , '33683988055', '1978-04-18', 4, 1, 1
        , 17000, 31883, 2, '1983-03-11', 8062079
        , 48940),
    (24, 'CKR-1023', '赵静', 55, 1
        , '35197158809', '1998-05-10', 0, 0, 1
        , 12000, 52528, 97, '1999-10-03', 4701315
        , 97298),
    (25, 'CKR-1024', '魏敏', 12, 0
        , '31535636658', '1999-02-13', 2, 0, 1
        , 15000, 33481, 27, '1985-10-14', 7808248
        , 8086),
    (26, 'CKR-1025', '乔娟', 38, 1
        , '+8616799174454', '1978-03-29', 1, 0, 0
        , 16000, 71075, 83, '1993-09-10', 5946732
        , 7717),
    (27, 'CKR-1026', '邓娟', 79, 0
        , '34327912543', '2007-08-20', 4, 0, 0
        , 14000, 44632, 84, '1976-08-17', 9530773
        , 22782),
    (28, 'CKR-1027', '贺伟', 38, 0
        , '33089112291', '2006-05-24', 0, 0, 0
        , 8500, 94004, 49, '1983-02-01', 6219669
        , 29030),
    (29, 'CKR-1028', '谭强', 80, 0
        , '+8620283740235', '1973-12-13', 4, 0, 0
        , 15000, 7536, 24, '2003-11-18', 8550386
        , 9772),
    (30, 'CKR-1029', '余涛', 93, 1
        , '24091917546', '2017-10-26', 4, 0, 1
        , 14000, 77595, 40, '1977-11-19', 5950564
        , 73535),
    (31, 'CKR-1030', '谢明', 21, 0
        , '21662351966', '1992-12-31', 2, 0, 0
        , 17000, 65481, 86, '1978-11-14', 5902767
        , 80462),
    (32, 'CKR-1031', '陆丽', 50, 0
        , '(86)21065489862', '2018-03-04', 1, 0, 1
        , 17000, 16100, 45, '1999-07-13', 7209351
        , 18872),
    (33, 'CKR-1032', '谭秀英', 14, 1
        , '33658105481', '1986-01-09', 0, 0, 0
        , 7000, 87617, 40, '2013-07-14', 7266776
        , 26436),
    (34, 'CKR-1033', '胡明', 17, 0
        , '25078360286', '2002-02-13', 1, 2, 1
        , 11000, 11490, 67, '1993-01-25', 1498183
        , 51372),
    (35, 'CKR-1034', '史秀英', 60, 0
        , '34319771121', '1973-05-16', 1, 2, 1
        , 45000, 87909, 24, '2018-06-05', 2030521
        , 26098),
    (36, 'CKR-1035', '苏军', 72, 0
        , '34047439055', '1988-10-30', 1, 2, 1
        , 6700, 29314, 5, '1995-10-09', 7693879
        , 68804),
    (37, 'CKR-1036', '叶明', 71, 0
        , '20433074633', '2018-04-02', 2, 2, 0
        , 8000, 81260, 43, '2000-11-27', 7005542
        , 29571),
    (38, 'CKR-1037', '潘明', 14, 0
        , '34489635278', '1987-12-23', 1, 2, 1
        , 10000, 8007, 8, '2018-02-23', 9003028
        , 20745),
    (39, 'CKR-1038', '许磊', 4, 0
        , '21405616984', '1990-08-22', 2, 2, 0
        , 10000, 78111, 74, '1994-01-01', 9313883
        , 19159),
    (40, 'CKR-1039', '余强', 30, 0
        , '(86)30699305187', '2002-12-20', 2, 1, 1
        , 8500, 43071, 34, '1992-01-19', 3081415
        , 94513),
    (41, 'CKR-1040', '崔娟', 11, 0
        , '34029933814', '1986-06-19', 2, 1, 0
        , 5000, 87999, 5, '1987-02-13', 178974
        , 61325),
    (42, 'CKR-1041', '贺娟', 97, 0
        , '25353299318', '2001-02-18', 4, 1, 1
        , 6700, 78972, 78, '1972-02-13', 5901890
        , 70525),
    (43, 'CKR-1042', '许霞', 85, 0
        , '36913969766', '2002-05-06', 1, 0, 1
        , 5000, 91055, 24, '2018-01-05', 6859793
        , 78753),
    (44, 'CKR-1043', '沈平', 10, 0
        , '(86)25870287190', '1981-09-02', 0, 0, 0
        , 6800, 63460, 13, '1980-10-23', 2177974
        , 76217),
    (45, 'CKR-1044', '万强', 20, 1
        , '26961761882', '1979-04-07', 2, 0, 1
        , 6600, 39348, 94, '1985-12-10', 1728750
        , 36261),
    (46, 'CKR-1045', '袁伟', 99, 0
        , '29666782625', '1989-10-22', 2, 0, 0
        , 6500, 92593, 2, '1990-03-07', 1821075
        , 11599),
    (47, 'CKR-1046', '傅勇', 23, 1
        , '26923733892', '2004-02-17', 4, 0, 0
        , 7200, 68864, 99, '2016-08-26', 1387723
        , 28969),
    (48, 'CKR-1047', '龚敏', 6, 1
        , '26155576181', '1989-02-04', 0, 0, 1
        , 5000, 79006, 60, '1988-01-04', 7876225
        , 82518),
    (49, 'CKR-1048', '彭超', 17, 0
        , '28200329738', '1999-05-11', 1, 0, 1
        , 5000, 14056, 61, '1983-08-25', 5280068
        , 75582),
    (50, 'CKR-1049', '乔军', 44, 0
        , '20047581812', '1983-01-15', 2, 1, 0
        , 5000, 6073, 86, '1970-03-27', 6822252
        , 55308),
    (51, 'CKR-1050', '贺娟', 4, 0
        , '24457838800', '1990-10-21', 4, 1, 0
        , 5000, 89753, 89, '2004-07-30', 8107579
        , 27253),
    (52, 'CKR-1051', '蒋娟', 83, 0
        , '33187981781', '1981-07-26', 2, 1, 0
        , 12000, 89547, 39, '1996-03-27', 9546849
        , 98847),
    (53, 'CKR-1052', '阎娟', 43, 0
        , '24966035583', '1994-10-30', 4, 0, 1
        , 9000, 56956, 44, '1975-01-18', 115463
        , 75243),
    (54, 'CKR-1053', '范伟', 92, 1
        , '20735252149', '2016-11-16', 3, 0, 1
        , 8000, 99009, 10, '2014-07-05', 6628234
        , 97674),
    (55, 'CKR-1054', '苏丽', 34, 0
        , '34261900542', '2014-07-15', 2, 0, 0
        , 7000, 81145, 33, '2004-07-14', 4140885
        , 14247),
    (56, 'CKR-1055', '赵娟', 91, 0
        , '25229751191', '2001-04-04', 1, 0, 0
        , 8500, 60527, 5, '1988-04-21', 3255066
        , 88485),
    (57, 'CKR-1056', '赵丽', 84, 0
        , '33892657660', '1980-11-27', 1, 0, 0
        , 8500, 42275, 51, '1988-10-16', 8660761
        , 75908),
    (58, 'CKR-1057', '邓磊', 11, 0
        , '24903084592', '1973-11-06', 4, 0, 0
        , 9000, 57619, 62, '1998-09-11', 8035936
        , 6989),
    (59, 'CKR-1058', '张芳', 36, 0
        , '26599014399', '1973-02-09', 1, 0, 1
        , 7200, 33170, 35, '1980-08-20', 3885281
        , 42114),
    (60, 'CKR-1059', '孙平', 35, 0
        , '30124201907', '1989-05-29', 1, 3, 0
        , 6800, 29221, 17, '2001-06-08', 7175461
        , 39317),
    (61, 'CKR-1060', '金霞', 4, 1
        , '32989752689', '2018-04-06', 0, 3, 0
        , 6900, 56864, 48, '1984-09-19', 5103692
        , 34747),
    (62, 'CKR-1061', '谢芳', 37, 1
        , '22325686051', '1981-05-12', 4, 3, 1
        , 7100, 32834, 34, '2006-05-05', 8119980
        , 44347),
    (63, 'CKR-1062', '石勇', 28, 0
        , '31151192235', '2013-02-06', 4, 3, 0
        , 7000, 45832, 81, '2016-06-03', 7215690
        , 60679),
    (64, 'CKR-1063', '蒋伟', 83, 0
        , '32727409172', '1975-08-14', 1, 3, 1
        , 7000, 28624, 67, '2005-05-19', 401890
        , 4493),
    (65, 'CKR-1064', '尹娜', 14, 0
        , '26966147594', '2014-12-16', 2, 2, 1
        , 5000, 50594, 88, '2010-09-03', 4781451
        , 52552),
    (66, 'CKR-1065', '阎伟', 4, 1
        , '29771084127', '1983-01-12', 1, 2, 1
        , 9000, 34747, 46, '1972-12-15', 3021194
        , 99764),
    (67, 'CKR-1066', '杨磊', 3, 1
        , '25880125057', '2005-11-01', 4, 2, 0
        , 6500, 93199, 33, '1989-07-26', 772627
        , 25525),
    (68, 'CKR-1067', '雷平', 100, 1
        , '21501765761', '2016-04-02', 1, 2, 1
        , 7500, 33137, 78, '1989-09-21', 3897294
        , 95987),
    (69, 'CKR-1068', '许杰', 13, 0
        , '34808948302', '2004-06-12', 2, 3, 1
        , 4400, 14611, 90, '1992-03-07', 8212692
        , 30070),
    (70, 'CKR-1069', '熊娜', 80, 1
        , '34238155863', '2003-03-07', 3, 3, 1
        , 4800, 76704, 63, '1977-02-16', 5031631
        , 8627),
    (71, 'CKR-1070', '邓勇', 96, 0
        , '36763644599', '1983-12-15', 0, 3, 0
        , 3700, 74704, 82, '2002-11-05', 3221507
        , 43394),
    (72, 'CKR-1071', '赖芳', 4, 0
        , '24563419827', '1996-10-11', 4, 3, 0
        , 6500, 96055, 18, '2008-03-21', 7341282
        , 90341),
    (73, 'CKR-1072', '王超', 27, 1
        , '38674282514', '1980-08-30', 2, 3, 1
        , 5200, 87710, 34, '1985-10-11', 6442607
        , 52075),
    (74, 'CKR-1073', '秦强', 95, 1
        , '32181869518', '2018-04-25', 1, 2, 0
        , 4650, 88563, 96, '2009-01-05', 2643763
        , 84598),
    (75, 'CKR-1074', '贺涛', 10, 0
        , '33063447619', '2006-10-14', 1, 2, 1
        , 4550, 13903, 18, '1984-01-26', 345408
        , 81526),
    (76, 'CKR-1075', '雷平', 61, 0
        , '24713147347', '1996-08-30', 2, 2, 1
        , 4400, 32375, 0, '1992-06-04', 7901871
        , 29745),
    (77, 'CKR-1076', '秦娟', 61, 0
        , '22435879686', '2013-07-03', 2, 2, 1
        , 4700, 16121, 49, '1979-04-15', 8668010
        , 15787),
    (78, 'CKR-1077', '郭磊', 12, 1
        , '27447270138', '2014-02-23', 1, 3, 1
        , 4500, 32254, 2, '1988-10-12', 2226230
        , 18156),
    (79, 'CKR-1078', '苏娟', 76, 0
        , '20890972094', '1973-04-28', 1, 3, 0
        , 8000, 53199, 2, '1982-07-07', 635335
        , 73580),
    (80, 'CKR-1079', '朱娟', 22, 1
        , '34028589330', '2018-10-06', 4, 3, 1
        , 10000, 85964, 38, '1999-06-05', 8596230
        , 14763),
    (81, 'CKR-1080', '孙静', 93, 1
        , '26195165677', '1994-09-18', 0, 3, 0
        , 10000, 15577, 82, '1975-03-25', 5012535
        , 14102);

stg_base_xykbkjl_di表语句

DDL语句:
CREATE TABLE IF NOT EXISTS stg_base_xykbkjl_di (
    id bigint COMMENT '主键',
    cid string COMMENT '卡id',
    climit bigint COMMENT '卡额度',
    actdate bigint COMMENT '统计日期',
    channel string COMMENT '办卡渠道',
    rid string COMMENT '持卡人id'
) COMMENT '信用卡信息';
插入数据语句:
INSERT INTO stg_base_xykbkjl_di
VALUES (1, 'XYK-1001', 5, 1514736000, '渠道1'
        , 'CKR-1000'),
    (2, 'XYK-1002', 5, 1517414400, '渠道1'
        , 'CKR-1000'),
    (3, 'XYK-1003', 10, 1517587200, '渠道1'
        , 'CKR-1000'),
    (4, 'XYK-1004', 5, 1520611200, '渠道1'
        , 'CKR-1000'),
    (5, 'XYK-1005', 10, 1528128000, '渠道4'
        , 'CKR-1000'),
    (6, 'XYK-1006', 15, 1528041600, '渠道4'
        , 'CKR-1000'),
    (7, 'XYK-1007', 10, 1527955200, '渠道4'
        , 'CKR-1000'),
    (8, 'XYK-1008', 15, 1527868800, '渠道4'
        , 'CKR-1000'),
    (9, 'XYK-1009', 10, 1527782400, '渠道4'
        , 'CKR-1000'),
    (10, 'XYK-2001', 20, 1483200000, '渠道1'
        , 'CKR-2000'),
    (11, 'XYK-2002', 10, 1496246400, '渠道1'
        , 'CKR-2000'),
    (12, 'XYK-2003', 15, 1515945600, '渠道2'
        , 'CKR-2000'),
    (13, 'XYK-2004', 5, 1516809600, '渠道2'
        , 'CKR-2000'),
    (14, 'XYK-2005', 5, 1527177600, '渠道3'
        , 'CKR-2000'),
    (15, 'XYK-2006', 15, 1531584000, '渠道3'
        , 'CKR-2000'),
    (16, 'XYK-3001', 10, 1520092800, '渠道3'
        , 'CKR-3000'),
    (17, 'XYK-3002', 10, 1520092800, '渠道3'
        , 'CKR-3000'),
    (18, 'XYK-3003', 10, 1520092800, '渠道3'
        , 'CKR-3000'),
    (19, 'XYK-3004', 10, 1520179200, '渠道3'
        , 'CKR-3000'),
    (20, 'XYK-3005', 10, 1520179200, '渠道3'
        , 'CKR-3000'),
    (21, 'XYK-4001', 10, 1507910400, '渠道2'
        , 'CKR-4000'),
    (22, 'XYK-4002', 15, 1522339200, '渠道2'
        , 'CKR-4000'),
    (23, 'XYK-4003', 5, 1527782400, '渠道2'
        , 'CKR-4000'),
    (24, 'XYK-5001', 5, 1527955200, '渠道4'
        , 'CKR-5000'),
    (25, 'XYK-5002', 10, 1527955200, '渠道5'
        , 'CKR-5000'),
    (26, 'XYK-5003', 10, 1527955200, '渠道5'
        , 'CKR-5000'),
    (27, 'XYK-5004', 20, 1527955200, '渠道4'
        , 'CKR-5000'),
    (28, 'XYK-5005', 10, 1528041600, '渠道4'
        , 'CKR-5000'),
    (29, 'XYK-5006', 10, 1528041600, '渠道5'
        , 'CKR-5000'),
    (30, 'XYK-5007', 15, 1528041600, '渠道4'
        , 'CKR-5000'),
    (31, 'XYK-5008', 5, 1528214400, '渠道4'
        , 'CKR-5000'),
    (32, 'XYK-5009', 15, 1528214400, '渠道4'
        , 'CKR-5000'),
    (33, 'XYK-5010', 5, 1528300800, '渠道4'
        , 'CKR-5000');

dws_ren_kh_di表语句

DDL语句:
CREATE TABLE IF NOT EXISTS dws_ren_kh_di (
    id bigint COMMENT '主键',
    zzhm string COMMENT '证件号码',
    khmc string COMMENT '客户名称',
    nl bigint COMMENT '年龄',
    xb string COMMENT '性别',
    yddh1 string COMMENT '移动电话1',
    csrq string COMMENT '出生日期',
    jysp string COMMENT '教育水平',
    career string COMMENT '职业',
    hyzk string COMMENT '婚姻状况',
    gz bigint COMMENT '工资',
    decdbyjyje bigint COMMENT '大额存单本月交易金额',
    decdbyjybs bigint COMMENT '大额存单本月交易笔数',
    zjzdjyrq string COMMENT '最近主动交易日期',
    jtnsr bigint COMMENT '家庭年收入',
    dqckcy bigint COMMENT '定期存款持有'
) COMMENT '银行客户信息';
插入数据语句:
INSERT INTO dws_ren_kh_di
VALUES (1, "CKR-1000", "谢敏", 33, "男"
        , "331934620595", "2002-03-06", "研究生", "自由职业", "未婚,"
        , 80000, 95429, 31, "2019-05-13", 1425574
        , 83489),
    (2, "CKR-2000", "罗静", 68, "女"
        , "223647553845", "2009-11-15", "研究生", "自由职业", "已婚,"
        , 40000, 54194, 34, "2001-10-15", 4732362
        , 99694),
    (3, "CKR-3000", "汤洋", 46, "女"
        , "33904547704", "1993-09-04", "研究生", "自由职业", "未婚,"
        , 40000, 61863, 58, "1987-10-06", 7593193
        , 11215),
    (4, "CKR-4000", "何平", 44, "男"
        , "28290449032", "1999-07-22", "本科", "自由职业", "已婚,"
        , 35000, 61082, 60, "1989-10-07", 9177195
        , 60752),
    (5, "CKR-5000", "文娜", 36, "男"
        , "25235166267", "1992-07-15", "本科", "自由职业", "已婚,"
        , 25000, 71953, 35, "2001-04-04", 2925278
        , 4165),
    (6, "CKR-1005", "江静", 15, "男"
        , "26631327402", "1981-03-04", "本科", "自由职业", "已婚,"
        , 15000, 43857, 42, "2014-01-08", 5253141
        , 65308),
    (7, "CKR-1006", "汤丽", 6, "男"
        , "38099177920", "1986-10-20", "本科", "自由职业", "未婚,"
        , 10000, 8503, 86, "1970-09-12", 5296756
        , 85285),
    (8, "CKR-1007", "江军", 62, "女"
        , "21731353562", "1982-04-27", "研究生", "自由职业", "已婚,"
        , 17000, 65718, 86, "1989-05-23", 1762593
        , 96453),
    (9, "CKR-1008", "雷秀英", 34, "男"
        , "21647630365", "1989-06-23", "职业学院", "自由职业", "已婚,"
        , 50000, 97157, 12, "1977-08-16", 763445
        , 99478),
    (10, "CKR-1009", "孟超", 95, "男"
        , "35153310990", "2008-07-12", "研究生", "外企白领", "未婚,"
        , 15000, 68605, 54, "2011-10-13", 6635322
        , 87174),
    (11, "CKR-1010", "常超", 71, "男"
        , "35476048078", "2008-06-10", "研究生", "外企白领", "未婚,"
        , 8500, 49055, 98, "1978-01-21", 3847031
        , 51571),
    (12, "CKR-1011", "乔强,", 34, "女"
        , "37050349190", "1977-11-03", "本科", "外企白领", "未婚,"
        , 15000, 72188, 65, "2019-08-09", 9715625
        , 11546),
    (13, "CKR-1012", "金军", 4, "女"
        , "28364711221", "1977-11-11", "本科", "外企白领", "已婚,"
        , 17000, 65132, 39, "1992-11-26", 9875371
        , 93019),
    (14, "CKR-1013", "龚明", 78, "男"
        , "33321797005", "2005-12-10", "本科", "外企白领", "已婚,"
        , 12000, 15774, 83, "1973-12-30", 5007852
        , 78029),
    (15, "CKR-1014", "何刚", 57, "男"
        , "20377333193", "1977-01-27", "本科", "外企白领", "未婚,"
        , 17000, 12815, 55, "2007-10-22", 409723
        , 62655),
    (16, "CKR-1015", "贺明", 18, "女"
        , "36369234358", "2001-12-25", "职业学院", "IT工程师", "未婚,"
        , 10000, 90515, 3, "1989-01-07", 751082
        , 18248),
    (17, "CKR-1016", "潘超", 76, "男"
        , "28539508428", "2003-12-24", "职业学院", "IT工程师", "未婚,"
        , 17000, 68525, 44, "1995-07-22", 7495966
        , 81766),
    (18, "CKR-1017", "潘明", 74, "女"
        , "22110074676", "1973-06-06", "本科", "IT工程师", "未婚,"
        , 10000, 71531, 78, "2011-07-26", 366257
        , 7696),
    (19, "CKR-1018", "顾秀英", 92, "男"
        , "35738683766", "2017-11-11", "本科", "IT工程师", "已婚,"
        , 30000, 62387, 1, "1988-05-21", 2213117
        , 85397),
    (20, "CKR-1019", "马娜", 13, "男"
        , "21708223252", "2014-07-02", "本科", "IT工程师", "已婚,"
        , 35000, 66701, 68, "1990-11-03", 8238187
        , 65044),
    (21, "CKR-1020", "曾磊", 78, "男"
        , "21200567551", "1983-05-18", "本科", "教师", "已婚,"
        , 35000, 53658, 26, "2001-04-06", 7262309
        , 36107),
    (22, "CKR-1021", "阎涛", 77, "男"
        , "23252779412", "2014-05-21", "本科", "教师", "未婚,"
        , 15000, 38751, 18, "2006-01-18", 4477225
        , 12690),
    (23, "CKR-1022", "魏勇", 96, "女"
        , "33683988055", "1978-04-18", "职业学院", "教师", "未婚,"
        , 17000, 31883, 2, "1983-03-11", 8062079
        , 48940),
    (24, "CKR-1023", "赵静", 55, "女"
        , "35197158809", "1998-05-10", "研究生", "保险", "未婚,"
        , 12000, 52528, 97, "1999-10-03", 4701315
        , 97298),
    (25, "CKR-1024", "魏敏", 12, "男"
        , "31535636658", "1999-02-13", "高中", "保险", "未婚,"
        , 15000, 33481, 27, "1985-10-14", 7808248
        , 8086),
    (26, "CKR-1025", "乔娟", 38, "女"
        , "36799174454", "1978-03-29", "本科", "保险", "已婚,"
        , 16000, 71075, 83, "1993-09-10", 5946732
        , 7717),
    (27, "CKR-1026", "邓娟", 79, "男"
        , "34327912543", "2007-08-20", "职业学院", "保险", "已婚,"
        , 14000, 44632, 84, "1976-08-17", 9530773
        , 22782),
    (28, "CKR-1027", "贺伟", 38, "男"
        , "33089112291", "2006-05-24", "研究生", "保险", "已婚,"
        , 8500, 94004, 49, "1983-02-01", 6219669
        , 29030),
    (29, "CKR-1028", "谭强", 80, "男"
        , "20283740235", "1973-12-13", "职业学院", "保险", "已婚,"
        , 15000, 7536, 24, "2003-11-18", 8550386
        , 9772),
    (30, "CKR-1029", "余涛", 93, "女"
        , "24091917546", "2017-10-26", "职业学院", "保险", "未婚,"
        , 14000, 77595, 40, "1977-11-19", 5950564
        , 73535),
    (31, "CKR-1030", "谢明", 21, "男"
        , "21662351966", "1992-12-31", "高中", "保险", "已婚,"
        , 17000, 65481, 86, "1978-11-14", 5902767
        , 80462),
    (32, "CKR-1031", "陆丽", 98, "男"
        , "21065489862", "2018-03-04", "本科", "保险", "未婚,"
        , 17000, 16100, 45, "1999-07-13", 7209351
        , 18872),
    (33, "CKR-1032", "谭秀英", 14, "女"
        , "33658105481", "1986-01-09", "研究生", "保险", "已婚,"
        , 7000, 87617, 40, "2013-07-14", 7266776
        , 26436),
    (34, "CKR-1033", "胡明", 17, "男"
        , "25078360286", "2002-02-13", "本科", "外企白领", "未婚,"
        , 11000, 11490, 67, "1993-01-25", 1498183
        , 51372),
    (35, "CKR-1034", "史秀英", 60, "男"
        , "34319771121", "1973-05-16", "本科", "外企白领", "未婚,"
        , 45000, 87909, 24, "2018-06-05", 2030521
        , 26098),
    (36, "CKR-1035", "苏军", 72, "男"
        , "34047439055", "1988-10-30", "本科", "外企白领", "未婚,"
        , 6700, 29314, 5, "1995-10-09", 7693879
        , 68804),
    (37, "CKR-1036", "叶明", 71, "男"
        , "20433074633", "2018-04-02", "高中", "外企白领", "已婚,"
        , 8000, 81260, 43, "2000-11-27", 7005542
        , 29571),
    (38, "CKR-1037", "潘明", 14, "男"
        , "34489635278", "1987-12-23", "本科", "外企白领", "未婚,"
        , 10000, 8007, 8, "2018-02-23", 9003028
        , 20745),
    (39, "CKR-1038", "许磊", 4, "男"
        , "21405616984", "1990-08-22", "高中", "外企白领", "已婚,"
        , 10000, 78111, 74, "1994-01-01", 9313883
        , 19159),
    (40, "CKR-1039", "余强", 98, "男"
        , "30699305187", "2002-12-20", "高中", "教师", "未婚,"
        , 8500, 43071, 34, "1992-01-19", 3081415
        , 94513),
    (41, "CKR-1040", "崔娟", 11, "男"
        , "34029933814", "1986-06-19", "高中", "教师", "已婚,"
        , 5000, 87999, 5, "1987-02-13", 178974
        , 61325),
    (42, "CKR-1041", "贺娟", 97, "男"
        , "25353299318", "2001-02-18", "职业学院", "教师", "未婚,"
        , 6700, 78972, 78, "1972-02-13", 5901890
        , 70525),
    (43, "CKR-1042", "许霞", 85, "男"
        , "36913969766", "2002-05-06", "本科", "保险", "未婚,"
        , 5000, 91055, 24, "2018-01-05", 6859793
        , 78753),
    (44, "CKR-1043", "沈平", 10, "男"
        , "25870287190", "1981-09-02", "研究生", "保险", "已婚,"
        , 6800, 63460, 13, "1980-10-23", 2177974
        , 76217),
    (45, "CKR-1044", "万强", 20, "女"
        , "26961761882", "1979-04-07", "高中", "保险", "未婚,"
        , 6600, 39348, 94, "1985-12-10", 1728750
        , 36261),
    (46, "CKR-1045", "袁伟", 99, "男"
        , "29666782625", "1989-10-22", "高中", "保险", "已婚,"
        , 6500, 92593, 2, "1990-03-07", 1821075
        , 11599),
    (47, "CKR-1046", "傅勇", 23, "女"
        , "26923733892", "2004-02-17", "职业学院", "保险", "已婚,"
        , 7200, 68864, 99, "2016-08-26", 1387723
        , 28969),
    (48, "CKR-1047", "龚敏", 6, "女"
        , "26155576181", "1989-02-04", "研究生", "保险", "未婚,"
        , 5000, 79006, 60, "1988-01-04", 7876225
        , 82518),
    (49, "CKR-1048", "彭超", 17, "男"
        , "28200329738", "1999-05-11", "本科", "保险", "未婚,"
        , 5000, 14056, 61, "1983-08-25", 5280068
        , 75582),
    (50, "CKR-1049", "乔军", 44, "男"
        , "20047581812", "1983-01-15", "高中", "教师", "已婚,"
        , 5000, 6073, 86, "1970-03-27", 6822252
        , 55308),
    (51, "CKR-1050", "贺娟", 4, "男"
        , "24457838800", "1990-10-21", "职业学院", "教师", "已婚,"
        , 5000, 89753, 89, "2004-07-30", 8107579
        , 27253),
    (52, "CKR-1051", "蒋娟", 83, "男"
        , "33187981781", "1981-07-26", "高中", "教师", "已婚,"
        , 12000, 89547, 39, "1996-03-27", 9546849
        , 98847),
    (53, "CKR-1052", "阎娟", 43, "男"
        , "24966035583", "1994-10-30", "职业学院", "保险", "未婚,"
        , 9000, 56956, 44, "1975-01-18", 115463
        , 75243),
    (54, "CKR-1053", "范伟", 92, "女"
        , "20735252149", "2016-11-16", "职业高中", "保险", "未婚,"
        , 8000, 99009, 10, "2014-07-05", 6628234
        , 97674),
    (55, "CKR-1054", "苏丽", 34, "男"
        , "34261900542", "2014-07-15", "高中", "保险", "已婚,"
        , 7000, 81145, 33, "2004-07-14", 4140885
        , 14247),
    (56, "CKR-1055", "赵娟", 91, "男"
        , "25229751191", "2001-04-04", "本科", "保险", "已婚,"
        , 8500, 60527, 5, "1988-04-21", 3255066
        , 88485),
    (57, "CKR-1056", "赵丽", 84, "男"
        , "33892657660", "1980-11-27", "本科", "保险", "已婚,"
        , 8500, 42275, 51, "1988-10-16", 8660761
        , 75908),
    (58, "CKR-1057", "邓磊", 11, "男"
        , "24903084592", "1973-11-06", "职业学院", "保险", "已婚,"
        , 9000, 57619, 62, "1998-09-11", 8035936
        , 6989),
    (59, "CKR-1058", "张芳", 36, "男"
        , "26599014399", "1973-02-09", "本科", "保险", "未婚,"
        , 7200, 33170, 35, "1980-08-20", 3885281
        , 42114),
    (60, "CKR-1059", "孙平", 35, "男"
        , "30124201907", "1989-05-29", "本科", "自由职业", "已婚,"
        , 6800, 29221, 17, "2001-06-08", 7175461
        , 39317),
    (61, "CKR-1060", "金霞", 4, "女"
        , "32989752689", "2018-04-06", "研究生", "自由职业", "已婚,"
        , 6900, 56864, 48, "1984-09-19", 5103692
        , 34747),
    (62, "CKR-1061", "谢芳", 37, "女"
        , "22325686051", "1981-05-12", "职业学院", "自由职业", "未婚,"
        , 7100, 32834, 34, "2006-05-05", 8119980
        , 44347),
    (63, "CKR-1062", "石勇", 28, "男"
        , "31151192235", "2013-02-06", "职业学院", "自由职业", "已婚,"
        , 7000, 45832, 81, "2016-06-03", 7215690
        , 60679),
    (64, "CKR-1063", "蒋伟", 83, "男"
        , "32727409172", "1975-08-14", "本科", "自由职业", "未婚,"
        , 7000, 28624, 67, "2005-05-19", 401890
        , 4493),
    (65, "CKR-1064", "尹娜", 14, "男"
        , "26966147594", "2014-12-16", "高中", "外企白领", "未婚,"
        , 5000, 50594, 88, "2010-09-03", 4781451
        , 52552),
    (66, "CKR-1065", "阎伟", 4, "女"
        , "29771084127", "1983-01-12", "本科", "外企白领", "未婚,"
        , 9000, 34747, 46, "1972-12-15", 3021194
        , 99764),
    (67, "CKR-1066", "杨磊", 3, "女"
        , "25880125057", "2005-11-01", "职业学院", "外企白领", "已婚,"
        , 6500, 93199, 33, "1989-07-26", 772627
        , 25525),
    (68, "CKR-1067", "雷平", 10, "女"
        , "21501765761", "2016-04-02", "本科", "外企白领", "未婚,"
        , 7500, 33137, 78, "1989-09-21", 3897294
        , 95987),
    (69, "CKR-1068", "许杰", 13, "男"
        , "34808948302", "2004-06-12", "高中", "自由职业", "未婚,"
        , 4400, 14611, 90, "1992-03-07", 8212692
        , 30070),
    (70, "CKR-1069", "熊娜", 80, "女"
        , "34238155863", "2003-03-07", "职业高中", "自由职业", "未婚,"
        , 4800, 76704, 63, "1977-02-16", 5031631
        , 8627),
    (71, "CKR-1070", "邓勇", 96, "男"
        , "36763644599", "1983-12-15", "研究生", "自由职业", "已婚,"
        , 3700, 74704, 82, "2002-11-05", 3221507
        , 43394),
    (72, "CKR-1071", "赖芳", 4, "男"
        , "24563419827", "1996-10-11", "职业学院", "自由职业", "已婚,"
        , 6500, 96055, 18, "2008-03-21", 7341282
        , 90341),
    (73, "CKR-1072", "王超", 27, "女"
        , "38674282514", "1980-08-30", "高中", "自由职业", "未婚,"
        , 5200, 87710, 34, "1985-10-11", 6442607
        , 52075),
    (74, "CKR-1073", "秦强,", 95, "女"
        , "32181869518", "2018-04-25", "本科", "外企白领", "已婚,"
        , 4650, 88563, 96, "2009-01-05", 2643763
        , 84598),
    (75, "CKR-1074", "贺涛", 10, "男"
        , "33063447619", "2006-10-14", "本科", "外企白领", "未婚,"
        , 4550, 13903, 18, "1984-01-26", 345408
        , 81526),
    (76, "CKR-1075", "雷平", 61, "男"
        , "24713147347", "1996-08-30", "高中", "外企白领", "未婚,"
        , 4400, 32375, 0, "1992-06-04", 7901871
        , 29745),
    (77, "CKR-1076", "秦娟", 61, "男"
        , "22435879686", "2013-07-03", "高中", "外企白领", "未婚,"
        , 4700, 16121, 49, "1979-04-15", 8668010
        , 15787),
    (78, "CKR-1077", "郭磊", 12, "女"
        , "27447270138", "2014-02-23", "本科", "自由职业", "未婚,"
        , 4500, 32254, 2, "1988-10-12", 2226230
        , 18156),
    (79, "CKR-1078", "苏娟", 76, "男"
        , "20890972094", "1973-04-28", "本科", "自由职业", "已婚,"
        , 8000, 53199, 2, "1982-07-07", 635335
        , 73580),
    (80, "CKR-1079", "朱娟", 22, "女"
        , "34028589330", "2018-10-06", "职业学院", "自由职业", "未婚,"
        , 10000, 85964, 38, "1999-06-05", 8596230
        , 14763),
    (81, "CKR-1080", "孙静", 93, "女"
        , "26195165677", "1994-09-18", "研究生", "自由职业", "已婚,"
        , 10000, 15577, 82, "1975-03-25", 5012535
        , 14102);

dws_wp_xyk_di表语句

DDL语句:
CREATE TABLE IF NOT EXISTS dws_wp_xyk_di (
    id bigint COMMENT '主键',
    cid string COMMENT '卡',
    climit bigint COMMENT '卡额度',
    actdate string COMMENT '统计日期',
    channel string COMMENT '办卡渠道',
    rid string COMMENT '持卡人id',
    age string COMMENT '年龄',
    gender string COMMENT '性别',
    career string COMMENT '职业'
) COMMENT '信用卡信息';
插入数据语句:
INSERT INTO dws_wp_xyk_di
VALUES (1, "XYK-1001", 5, "20180101", "渠道1"
        , "CKR-1000", 33, "男", "自由职业"),
       (2, "XYK-1002", 5, "20180201", "渠道1"
        , "CKR-1000", 33, "男", "自由职业"),
       (3, "XYK-1003", 10, "20180203", "渠道1"
        , "CKR-1000", 33, "男", "自由职业"),
       (4, "XYK-1004", 5, "20180310", "渠道1"
        , "CKR-1000", 33, "男", "自由职业"),
       (5, "XYK-1005", 10, "20180605", "渠道4"
        , "CKR-1000", 33, "男", "自由职业"),
       (6, "XYK-1006", 15, "20180604", "渠道4"
        , "CKR-1000", 33, "男", "自由职业"),
       (7, "XYK-1007", 10, "20180603", "渠道4"
        , "CKR-1000", 33, "男", "自由职业"),
       (8, "XYK-1008", 15, "20180602", "渠道4"
        , "CKR-1000", 33, "男", "自由职业"),
       (9, "XYK-1009", 10, "20180601", "渠道4"
        , "CKR-1000", 33, "男", "自由职业"),
      (10, "XYK-2001", 20, "20170101", "渠道1"
        , "CKR-2000", 68, "女", "外企白领"),
      (11, "XYK-2002", 10, "20170601", "渠道1"
        , "CKR-2000", 68, "女", "外企白领"),
      (12, "XYK-2003", 15, "20180115", "渠道2"
        , "CKR-2000", 68, "女", "外企白领"),
      (13, "XYK-2004", 5, "20180125", "渠道2"
        , "CKR-2000", 68, "女", "外企白领"),
      (14, "XYK-2005", 5, "20180525", "渠道3"
        , "CKR-2000", 68, "女", "外企白领"),
      (15, "XYK-2006", 15, "20180715", "渠道3"
        , "CKR-2000", 68, "女", "外企白领"),
      (16, "XYK-3001", 10, "20180304", "渠道3"
        , "CKR-3000", 46, "女", "IT工程师"),
      (17, "XYK-3002", 10, "20180304", "渠道3"
        , "CKR-3000", 46, "女", "IT工程师"),
      (18, "XYK-3003", 10, "20180304", "渠道3"
        , "CKR-3000", 46, "女", "IT工程师"),
      (19, "XYK-3004", 10, "20180305", "渠道3"
        , "CKR-3000", 46, "女", "IT工程师"),
      (20, "XYK-3005", 10, "20180305", "渠道3"
        , "CKR-3000", 46, "女", "IT工程师"),
      (21, "XYK-4001", 10, "20171014", "渠道2"
        , "CKR-4000", 44, "男", "教师"),
      (22, "XYK-4002", 15, "20180330", "渠道2"
        , "CKR-4000", 44, "男", "教师"),
      (23, "XYK-4003", 5, "20180601", "渠道2"
        , "CKR-4000", 44, "男", "教师"),
      (24, "XYK-5001", 5, "20180603", "渠道4"
        , "CKR-5000", 36, "男", "保险"),
      (25, "XYK-5002", 10, "20180603", "渠道5"
        , "CKR-5000", 36, "男", "保险"),
      (26, "XYK-5003", 10, "20180603", "渠道5"
        , "CKR-5000", 36, "男", "保险"),
      (27, "XYK-5004", 20, "20180603", "渠道4"
        , "CKR-5000", 36, "男", "保险"),
      (28, "XYK-5005", 10, "20180604", "渠道4"
        , "CKR-5000", 36, "男", "保险"),
      (29, "XYK-5006", 10, "20180604", "渠道5"
        , "CKR-5000", 36, "男", "保险"),
      (30, "XYK-5007", 15, "20180604", "渠道4"
        , "CKR-5000", 36, "男", "保险"),
      (31, "XYK-5008", 5, "20180606", "渠道4"
        , "CKR-5000", 36, "男", "保险"),
      (32, "XYK-5009", 15, "20180606", "渠道4"
        , "CKR-5000", 36, "男", "保险"),
      (33, "XYK-5010", 5, "20180607", "渠道4"
        , "CKR-5000", 36, "男", "保险");
                

质量校验函数jar包源代码

创建质量校验函数时,请先使用java工具将下面示例代码打成jar包,jar包文件名称为“PhoneNumberChecker”。

package com.aliyun.odps.udf.examples;

import com.aliyun.odps.udf.UDF;


public class PhoneNumberChecker extends UDF {
    public Long evaluate(String number, String eleMetaStr) {
        long j = 0;

        if ((number == null) || number.trim().isEmpty()) {
            return 1L;
        }

        if (number.length() < 11) {
            return 1L;
        }

        if (number.length() < 11) {
            return 1L;
        }

        String substring = number.substring(number.length() - 11);

        if (substring.length() != 11) {
            return 1L;
        }

        return Long.valueOf(j);
    }

    public static void main(String[] args) {
        System.out.println(new PhoneNumberChecker().evaluate(args[0], null));
    }
}