----------------------------------------------------------------------- --创建数据源 CREATE TABLE policy_information( contno varchar2(200) DEFAULT NULL COMMENT '保单号', -- dialect: ORACLE riskcode varchar2(200) DEFAULT NULL COMMENT '险种代码', -- dialect: ORACLE riskname varchar2(200) DEFAULT NULL COMMENT '险种名称', -- dialect: ORACLE riskperiod varchar2(200) DEFAULT NULL COMMENT '险种分类', -- dialect: ORACLE risktype varchar2(200) DEFAULT NULL COMMENT '险种设计类型', payintv varchar2(200) DEFAULT NULL COMMENT '缴费方式', security varchar2(200) not NULL COMMENT '保障年期', payendyear number not NULL COMMENT '缴费期间', payendyearflag varchar2(200) not NULL COMMENT '缴费年期单位', insuyer number not NULL COMMENT '保险年期', insuyerflag varchar2(200) not NULL COMMENT '保险年期单位', paycount number not NULL COMMENT '续保次数', signdate timestamp not NULL COMMENT '承保日期', cvalidate timestamp not NULL COMMENT '生效日期', enddate timestamp not NULL COMMENT '保单终止日期', modifydate timestamp not null comment '更新时间', prem number not null COMMENT '保费', appflag varchar2(200) not NULL COMMENT '保单状态', customerno varchar2(200) not null comment '客户号', name varchar2(200) not null comment '客户姓名', sex varchar2(200) not null comment '客户性别', birthday timestamp not null comment '客户出生日期', idtype varchar2(200) not null comment '客户证件类型', idno varchar2(200) not null comment '客户证件号', insuredno varchar2(200) not null comment '被保人号', insuredname varchar2(200) not null comment '被保人姓名', insuredsex varchar2(200) not null comment '被保人性别', insuredbirthday timestamp not null comment '被保人出生日期', insuredidtype varchar2(200) not null comment '被保人证件类型', insuredidno varchar2(200) not null comment '被保人证件号码', relationtoappnt varchar2(200) not null comment '与投保人关系', salechnl varchar2(200) not null comment '销售渠道', salecom varchar2(200) not null comment '业绩归属', salechannels varchar2(200) not NULL COMMENT '二级业绩归属', customgetpoldate timestamp not null comment '客户投保日期', prem_js number not null comment 'prem_js' ) COMMENT '投保清单' STORED AS CSVFILE ; --推送数据命令 --hdfs dfs -put /home/frontData/ /tmp/myData/ LOAD DATA INPATH '/tmp/myData/policy_information.txt' OVERWRITE INTO TABLE qxp.policy_information; -------------------------------------------------------------------------------------------------------------------- TRUNCATE TABLE individual; --创建search目的表 DROP TABLE IF EXISTS individual; CREATE TABLE qxp.individual( indid string DEFAULT NULL COMMENT '个体ID', custid string DEFAULT NULL COMMENT '全局客户号', SCustID string DEFAULT NULL COMMENT '客户号', created_by string DEFAULT NULL COMMENT '创建人', created_time timestamp DEFAULT NULL COMMENT '创建时间', updated_by string DEFAULT NULL COMMENT '更新人', updated_time timestamp DEFAULT NULL COMMENT '更新时间', name string DEFAULT NULL COMMENT '名称', birthday date DEFAULT NULL COMMENT '出生日期', nobirth string DEFAULT NULL COMMENT '出生国家', -- dialect: ORACLE sobirth string DEFAULT NULL COMMENT '出生省份', -- dialect: ORACLE cobirth string DEFAULT NULL COMMENT '出生城市', -- dialect: ORACLE homeadress string DEFAULT NULL COMMENT '籍贯', dday date DEFAULT NULL COMMENT '死亡日期', gender string DEFAULT NULL COMMENT '性别', -- dialect: ORACLE ethnic string DEFAULT NULL COMMENT '民族', -- dialect: ORACLE nation string DEFAULT NULL COMMENT '国籍', -- dialect: ORACLE regresidence string DEFAULT NULL COMMENT '户口所在地', maritalstat string DEFAULT NULL COMMENT '婚姻状况', -- dialect: ORACLE maritalstatdate date DEFAULT NULL COMMENT '婚姻状况日期', offspring string DEFAULT NULL COMMENT '生育状况', -- dialect: ORACLE support string DEFAULT NULL COMMENT '赡养状况', -- dialect: ORACLE empstat string DEFAULT NULL COMMENT '就业状况', -- dialect: ORACLE empstatdate string DEFAULT NULL COMMENT '就业状况日期', -- dialect: ORACLE raid string DEFAULT NULL COMMENT '居住地址', ral string DEFAULT NULL COMMENT '居住地址不分段', al string DEFAULT NULL COMMENT '曾用地址不分段', coadress int DEFAULT NULL COMMENT '地址登录数量', pmphone string DEFAULT NULL COMMENT '常用手机', mhone string DEFAULT NULL COMMENT '曾用手机', sophone int DEFAULT NULL COMMENT '手机登录数量', height decimal(8,2) DEFAULT NULL COMMENT '身高', weight decimal(8,2) DEFAULT NULL COMMENT '体重', cillness string DEFAULT NULL COMMENT '当前疾病', -- dialect: ORACLE pillness string DEFAULT NULL COMMENT '曾患疾病', -- dialect: ORACLE bmi decimal(8,2) DEFAULT NULL COMMENT 'BMI体制指数', evadate date DEFAULT NULL COMMENT '健康评估日期', cohevaluation int DEFAULT NULL COMMENT '健康评估登录数量', hphone string DEFAULT NULL COMMENT '家庭电话', occupationid string DEFAULT NULL COMMENT '职业代码', occupation string DEFAULT NULL COMMENT '职业', -- dialect: ORACLE odate date DEFAULT NULL COMMENT '职业时间', employer string DEFAULT NULL COMMENT '工作单位', wphone string DEFAULT NULL COMMENT '工作电话', empdate date DEFAULT NULL COMMENT '工作单位时间', idcard string DEFAULT NULL COMMENT '身份证', passport string DEFAULT NULL COMMENT '护照', dlicense string DEFAULT NULL COMMENT '驾驶证', education string DEFAULT NULL COMMENT '最高学历', -- dialect: ORACLE university string DEFAULT NULL COMMENT '毕业学校', edate date DEFAULT NULL COMMENT '学历时间', wechat string DEFAULT NULL COMMENT '微信', weibo string DEFAULT NULL COMMENT '微博', email string DEFAULT NULL COMMENT '邮箱', qq string DEFAULT NULL COMMENT 'QQ', tiktok string DEFAULT NULL COMMENT '抖音', saccount1 string DEFAULT NULL COMMENT '社交账号1', sa1cat string DEFAULT NULL COMMENT '社交账号1类别', -- dialect: ORACLE saccount2 string DEFAULT NULL COMMENT '社交账号2', sa2cat string DEFAULT NULL COMMENT '社交账号2类别', -- dialect: ORACLE ctype string DEFAULT NULL COMMENT '首选联系方式', -- dialect: ORACLE father string DEFAULT NULL COMMENT '父亲', mother string DEFAULT NULL COMMENT '母亲', mate string DEFAULT NULL COMMENT '配偶', child1 string DEFAULT NULL COMMENT '子女1', child2 string DEFAULT NULL COMMENT '子女2', cochild int DEFAULT NULL COMMENT '子女登录数量', fmhistory string DEFAULT NULL COMMENT '家族病史', -- dialect: ORACLE rpid string DEFAULT NULL COMMENT '房产', rpdescribe string DEFAULT NULL COMMENT '房产描述', corp int DEFAULT NULL COMMENT '房产登录数量', vid string DEFAULT NULL COMMENT '车辆', vdescribe string DEFAULT NULL COMMENT '车辆描述', covehicle int DEFAULT NULL COMMENT '车辆登录数量', anniversary1 date DEFAULT NULL COMMENT '纪念日1', a1describe string DEFAULT NULL COMMENT '纪念日1类型', -- dialect: ORACLE anniversary2 date DEFAULT NULL COMMENT '纪念日2', a2describe string DEFAULT NULL COMMENT '纪念日2类型', -- dialect: ORACLE soanniversary int DEFAULT NULL COMMENT '纪念日登录数量', custtype string DEFAULT NULL COMMENT '客户类型', -- dialect: ORACLE bankname string DEFAULT NULL COMMENT '开户银行', -- dialect: ORACLE tobankcard string DEFAULT NULL COMMENT '银行卡类型', -- dialect: ORACLE accname string DEFAULT NULL COMMENT '账户名', bankaccno string DEFAULT NULL COMMENT '银行帐号', cobaccount int DEFAULT NULL COMMENT '账户登录数量', sotlirisk decimal(32,8) DEFAULT NULL COMMENT '传统寿险种保额合计', soairisk decimal(32,8) DEFAULT NULL COMMENT '意外类险种保额合计', sosiirisk decimal(32,8) DEFAULT NULL COMMENT '重疾类险种保额合计', somcirisk decimal(32,8) DEFAULT NULL COMMENT '医疗费用类险种保额合计', someirisk decimal(32,8) DEFAULT NULL COMMENT '医疗补贴类险种保额合计', soefirisk decimal(32,8) DEFAULT NULL COMMENT '教育金类险种保额合计', sopirisk decimal(32,8) DEFAULT NULL COMMENT '养老金类险种保额合计', sowmirisk decimal(32,8) DEFAULT NULL COMMENT '财富管理险种保额合计', custclass int DEFAULT NULL COMMENT '客户等级', convalue int DEFAULT NULL COMMENT '贡献度分', awarded3 int DEFAULT NULL COMMENT '家庭加分', awarded2 int DEFAULT NULL COMMENT '续期加分', awarded1 int DEFAULT NULL COMMENT '保单加分', sovalue int DEFAULT NULL COMMENT '总分值', enddate date DEFAULT NULL COMMENT '客户等级失效日期', systemtag1 string DEFAULT NULL COMMENT '系统标签域1', systemtag2 string DEFAULT NULL COMMENT '系统标签域2', systemtag3 string DEFAULT NULL COMMENT '系统标签域3', cocommunication int DEFAULT NULL COMMENT '接触次数', lcdate date DEFAULT NULL COMMENT '最近接触时间', lctype string DEFAULT NULL COMMENT '最近接触类型', lcmethod string DEFAULT NULL COMMENT '最近接触方式 外呼、呼入', cocomplaint int DEFAULT NULL COMMENT '投诉次数', lcptdate date DEFAULT NULL COMMENT '最近投诉时间', lcptreason string DEFAULT NULL COMMENT '最近投诉原因', -- dialect: ORACLE lcptlink string DEFAULT NULL COMMENT '最近投诉环节', -- dialect: ORACLE lcptresult string DEFAULT NULL COMMENT '最近投诉处理结果', -- dialect: ORACLE lcptduration decimal(8,2) DEFAULT NULL COMMENT '最近投诉处理时长', lcptperson string DEFAULT NULL COMMENT '最近投诉人', -- dialect: ORACLE soppremium decimal(32,8) DEFAULT NULL COMMENT '累计已缴保费', lappdate date DEFAULT NULL COMMENT '最近投保日期', cndate date DEFAULT NULL COMMENT '最近报案日期', cnstatus string DEFAULT NULL COMMENT '最近报案状态', -- dialect: ORACLE cocnotification int DEFAULT NULL COMMENT '报案登录数量', fadate date DEFAULT NULL COMMENT '首次投保日期', ladate date DEFAULT NULL COMMENT '最近投保日期', lpno string DEFAULT NULL COMMENT '最近保险单号', lpstate string DEFAULT NULL COMMENT '最近保单状态', -- dialect: ORACLE lpname string DEFAULT NULL COMMENT '最近投保险种名称', copolicy int DEFAULT NULL COMMENT '保单登录数量', iaccno1 string DEFAULT NULL COMMENT '保险账户1', iaccno2 string DEFAULT NULL COMMENT '保险账户2', iaccno3 string DEFAULT NULL COMMENT '保险账户3', soiaccount1 decimal(32,8) DEFAULT NULL COMMENT '保险账户1余额', soiaccount2 decimal(32,8) DEFAULT NULL COMMENT '保险账户2余额', soiaccount3 decimal(32,8) DEFAULT NULL COMMENT '保险账户3余额', coiaccount int DEFAULT NULL COMMENT '保险账户登录数量', HOMEID string COMMENT '家庭号 大数据平台HOME_NETWORK' , BranchCode string COMMENT '机构代码 数据归属机构，用于权限控制' , CustState string COMMENT '客户状态 party可能会合并导致当前记录不再可用' , DataSource string COMMENT '数据来源' , Smoking string COMMENT '吸烟情况 从不、戒烟、吸烟' , Drinking string COMMENT '饮酒情况 从不、戒酒、饮酒' , Pregnancy string COMMENT '怀孕情况' , Hobby1 string COMMENT '兴趣爱好1' , Hobby2 string COMMENT '兴趣爱好2' , Hobby3 string COMMENT '兴趣爱好3' , PIncome decimal(32,8) COMMENT '个人年收入' , FIncome decimal(32,8) COMMENT '家庭年收入' , IncomeSource string COMMENT '收入来源' , SIStatus string COMMENT '社保情况' , NOVPolicy INT COMMENT '有效保单件数' , NOIVPolicy INT COMMENT '失效保单件数' , NOSTInsurance string COMMENT '短意险保单持有情况 仅持有短意险保单，持有短意险保单，无短意险保单' , NOSPInsurance string COMMENT '趸交保单持有情况 仅持有趸交保单，持有趸交保单，无趸交保单' , ISelf string COMMENT '本人投保情况 仅本人投保，本人已投保，本人未投保' , IParent string COMMENT '父母投保情况 仅父母投保，父母已投保，父母未投保' , IChildren string COMMENT '子女投保情况 仅子女投保，子女已投保，子女未·投保' , Imate string COMMENT '配偶投保情况 仅配偶投保，配偶已投保，配偶未投保' , Label1 string COMMENT 'Label1' , Label2 string COMMENT 'Label2' , Label3 string COMMENT 'Label3' , Label4 string COMMENT 'Label4' , Label5 string COMMENT 'Label5' , Label6 string COMMENT 'Label6' , Label7 string COMMENT 'Label7' , Label8 string COMMENT 'Label8' , Label9 string COMMENT 'Label9' , Label10 string COMMENT 'Label10' , Label11 string COMMENT 'Label11' , Label12 string COMMENT 'Label12' , Label13 string COMMENT 'Label13' , Label14 string COMMENT 'Label14' , Label15 string COMMENT 'Label15' , Label16 string COMMENT 'Label16' , Label17 string COMMENT 'Label17' , Label18 string COMMENT 'Label18' , Label19 string COMMENT 'Label19' , Label20 string COMMENT 'Label20' , Label21 string COMMENT 'Label21' , Label22 string COMMENT 'Label22' , Label23 string COMMENT 'Label23' , Label24 string COMMENT 'Label24' , Label25 string COMMENT 'Label25' , Label26 string COMMENT 'Label26' , Label27 string COMMENT 'Label27' , Label28 string COMMENT 'Label28' , Label29 string COMMENT 'Label29' , Label30 string COMMENT 'Label30' , Label31 string COMMENT 'Label31' , Label32 string COMMENT 'Label32' , Label33 string COMMENT 'Label33' , Label34 string COMMENT 'Label34' , Label35 string COMMENT 'Label35' , Label36 string COMMENT 'Label36' , Label37 string COMMENT 'Label37' , Label38 string COMMENT 'Label38' , Label39 string COMMENT 'Label39' , Label40 string COMMENT 'Label40' , Label41 string COMMENT 'Label41' , Label42 string COMMENT 'Label42' , Label43 string COMMENT 'Label43' , Label44 string COMMENT 'Label44' , Label45 string COMMENT 'Label45' , Label46 string COMMENT 'Label46' , Label47 string COMMENT 'Label47' , Label48 string COMMENT 'Label48' , Label49 string COMMENT 'Label49' , Label50 string COMMENT 'Label50' , Label51 string COMMENT 'Label51' , Label52 string COMMENT 'Label52' , Label53 string COMMENT 'Label53' , Label54 string COMMENT 'Label54' , Label55 string COMMENT 'Label55' , Label56 string COMMENT 'Label56' , Label57 string COMMENT 'Label57' , Label58 string COMMENT 'Label58' , Label59 string COMMENT 'Label59' , Label60 string COMMENT 'Label60' , Label61 string COMMENT 'Label61' , Label62 string COMMENT 'Label62' , Label63 string COMMENT 'Label63' , Label64 string COMMENT 'Label64' , Label65 string COMMENT 'Label65' , Label66 string COMMENT 'Label66' , Label67 string COMMENT 'Label67' , Label68 string COMMENT 'Label68' , Label69 string COMMENT 'Label69' , Label70 string COMMENT 'Label70' , Label71 string COMMENT 'Label71' , Label72 string COMMENT 'Label72' , Label73 string COMMENT 'Label73' , Label74 string COMMENT 'Label74' , Label75 string COMMENT 'Label75' , Label76 string COMMENT 'Label76' , Label77 string COMMENT 'Label77' , Label78 string COMMENT 'Label78' , Label79 string COMMENT 'Label79' , Label80 string COMMENT 'Label80' , Label81 string COMMENT 'Label81' , Label82 string COMMENT 'Label82' , Label83 string COMMENT 'Label83' , Label84 string COMMENT 'Label84' , Label85 string COMMENT 'Label85' , Label86 string COMMENT 'Label86' , Label87 string COMMENT 'Label87' , Label88 string COMMENT 'Label88' , Label89 string COMMENT 'Label89' , Label90 string COMMENT 'Label90' , Label91 string COMMENT 'Label91' , Label92 string COMMENT 'Label92' , Label93 string COMMENT 'Label93' , Label94 string COMMENT 'Label94' , Label95 string COMMENT 'Label95' , Label96 string COMMENT 'Label96' , Label97 string COMMENT 'Label97' , Label98 string COMMENT 'Label98' , Label99 string COMMENT 'Label99' , Label100 string COMMENT 'Label100' , Label101 string COMMENT 'Label101' , Label102 string COMMENT 'Label102' , Label103 string COMMENT 'Label103' , Label104 string COMMENT 'Label104' , Label105 string COMMENT 'Label105' , Label106 string COMMENT 'Label106' , Label107 string COMMENT 'Label107' , Label108 string COMMENT 'Label108' , Label109 string COMMENT 'Label109' , Label110 string COMMENT 'Label110' , Label111 string COMMENT 'Label111' , Label112 string COMMENT 'Label112' , Label113 string COMMENT 'Label113' , Label114 string COMMENT 'Label114' , Label115 string COMMENT 'Label115' , Label116 string COMMENT 'Label116' , Label117 string COMMENT 'Label117' , Label118 string COMMENT 'Label118' , Label119 string COMMENT 'Label119' , Label120 string COMMENT 'Label120' ) COMMENT '个人信息' STORED AS ES with shard number 10 replication 1; ---------------------------------------------------------------------------------------、 --创建 自增id --创建序列 CREATE SEQUENCE globaleCusId_sequence INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE NOCYCLE CACHE 1; ----------------------------------------------------------------------------------- -- 增量数据 -------------------------------------------------------------------------------------------- --创建存储过程 插入individual_search !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常 --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE insertOrUpdate_individual() -- 创建存储过程 IS DECLARE BEGIN MERGE INTO individual is1 using ( SELECT scustid ,name,gender ,birthday, idcard FROM ( SELECT customerno AS scustid,--投保人 name AS name, sex AS gender, birthday AS birthday, idtype AS idtype , idno AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype=0 UNION SELECT insuredno AS scustid,--被保人 insuredname AS name, insuredsex AS gender, insuredbirthday AS birthday, insuredidtype AS idtype , insuredidno AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype=0 ) tmpTable LIMIT IN 0,100000 ) is2 on (is2.scustid = is1.scustid and is2.name = is1.name and is2.gender = is1.gender and is2.birthday = is1.birthday and is2.idcard = is1.idcard) WHEN NOT MATCHED THEN INSERT ( scustid, name, gender, birthday, idcard, custid, indid, created_time, created_by ) VALUES ( is2.scustid, is2.name, is2.gender, is2.birthday, is2.idcard, 'CP'||lpad(globaleCusId_sequence.nextval,10,'0'), globaleCusId_sequence.CURRVAL, sysdate, 'qxp' ) EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('insertOrUpdate_individual',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('insertOrUpdate_individual',sqlerrm(),sqlcode()) END; -------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------- ============================================================================================================================================================= ==============================================================利用行号全部插入用户数据========================================================================== ============================================================================================================================================================= --初始化存储过程 -- 身份证 0 -- 护照 1 -- 军人证（军官证） 2 -- 驾照 3 -- 户口本 4 -- 学生证 5 -- 工作证 6 -- 出生证 7 -- 其它 8 -- 无证件 9 -- 士兵证 A -- 回乡证 B -- 临时身份证 C -- 警官证 D -- 台胞证 E -- 港、澳通行证 F -- 外国人永久居留身份证 I -- 港澳台居民居住证 J -- 港澳居民居住证 K -- 台湾居民居住证 M DROP SEQUENCE IF EXISTS globaleCusId_sequence; !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常 --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_main() -- 初始化 数据全部插入 IS DECLARE individual_count int strsql string BEGIN --先验是否一个客户id有多条记录对应不通的证件类别 --初始化 身份证 用户 shanglifeecif.init_individual_0(individual_count); --初始化 护照用户 individual_count:=individual_count+1; shanglifeecif.init_individual_1(individual_count); --初始化 驾照用户 individual_count:=individual_count+1; shanglifeecif.init_individual_3(individual_count); --其他 证件类型用户 individual_count:=individual_count+1; shanglifeecif.init_individual_other(individual_count); --初始化 全局序列 individual_count:=individual_count+1; strsql:=' DROP SEQUENCE IF EXISTS globaleCusId_sequence; CREATE SEQUENCE globaleCusId_sequence INCREMENT BY 1 START WITH '|| individual_count||' NOMAXVALUE NOMINVALUE NOCYCLE CACHE 1; '; EXECUTE IMMEDIATE strsql --更新用户等级信息 shanglifeecif.up_t_customers_class_1(); --更新职业信息 shanglifeecif.up_health_insurance_listing_1(); EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_individual_main',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_individual_main',sqlerrm(),sqlcode()) END; / !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常 --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_0( individual_count OUT int) -- 初始化 省份证 IS BEGIN --查询出此次处理的数据并出表中 insert into shanglifeecif.individual ( indid, custid, scustid, name, gender, birthday, idcard, created_time, created_by ) SELECT row_number()over(), 'CP'||lpad(row_number()over(),10,'0'), scustid , name, gender , birthday, idcard , sysdate, 'admin' FROM ( SELECT customerno AS scustid,--投保人 name AS name, sex AS gender, birthday AS birthday, idtype AS idtype , idno AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype=0 UNION SELECT insuredno AS scustid,--被保人 insuredname AS name, insuredsex AS gender, insuredbirthday AS birthday, insuredidtype AS idtype , insuredidno AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype=0 ) tmpTable --已经存在的数据行数 select count(0) into individual_count from shanglifeecif.individual EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_individual_0',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_individual_0',sqlerrm(),sqlcode()) END; / !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常 --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_1(individual_count INOUT int) -- 初始化 护照 IS BEGIN --查询出此次处理的数据并出表中 insert into shanglifeecif.individual ( indid, custid, scustid, name, gender, birthday, passport, created_time, created_by ) SELECT row_number()over()+individual_count, 'CP'||lpad(row_number()over()+individual_count,10,'0'), scustid , name, gender , birthday, idcard , sysdate, 'admin' FROM ( SELECT customerno AS scustid,--投保人 name AS name, sex AS gender, birthday AS birthday, idtype AS idtype , idno AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype=1 UNION SELECT insuredno AS scustid,--被保人 insuredname AS name, insuredsex AS gender, insuredbirthday AS birthday, insuredidtype AS idtype , insuredidno AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype=1 ) tmpTable --已经存在的数据行数 select count(0) into individual_count from shanglifeecif.individual EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_individual_1',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_individual_1',sqlerrm(),sqlcode()) END; !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常 --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_3(individual_count INOUT int) -- 初始化 护照 IS BEGIN --查询出此次处理的数据并出表中 insert into shanglifeecif.individual( indid, custid, scustid, name, gender, birthday, dlicense, created_time, created_by ) SELECT row_number()over()+individual_count, 'CP'||lpad(row_number()over()+individual_count,10,'0'), scustid , name, gender , birthday, idcard , sysdate, 'admin' FROM ( SELECT customerno AS scustid,--投保人 name AS name, sex AS gender, birthday AS birthday, idtype AS idtype , idno AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype=3 UNION SELECT insuredno AS scustid,--被保人 insuredname AS name, insuredsex AS gender, insuredbirthday AS birthday, insuredidtype AS idtype , insuredidno AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype=3 ) tmpTable --已经存在的数据行数 select count(0) into individual_count from shanglifeecif.individual EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('init_individual_3',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('init_individual_3',sqlerrm(),sqlcode()) END; !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常 --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_other(individual_count INOUT int) -- 初始化出了以上三种证件信息 IS BEGIN --查询出此次处理的数据并出表中 insert into shanglifeecif.individual( indid, custid, scustid, name, gender, birthday, created_time, created_by ) SELECT row_number()over()+individual_count, 'CP'||lpad(row_number()over()+individual_count,10,'0'), scustid , name, gender , birthday, sysdate, 'admin' FROM ( SELECT customerno AS scustid,--投保人 name AS name, sex AS gender, birthday AS birthday, idtype AS idtype , idno AS idcard FROM policy_information WHERE customerno IS NOT NULL AND idtype not in (0,1,3) UNION SELECT insuredno AS scustid,--被保人 insuredname AS name, insuredsex AS gender, insuredbirthday AS birthday, insuredidtype AS idtype , insuredidno AS idcard FROM policy_information WHERE insuredno IS NOT NULL AND insuredidtype in (0,1,3) ) tmpTable --已经存在的数据行数 select count(0) into individual_count from shanglifeecif.individual END; --创建存储过程 插入individual_search !set plsqlUseSlash true SET plsql.catch.hive.exception=true; --使用HIVE_EXCEPTION捕获Hive异常 --set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。 CREATE OR REPLACE PROCEDURE shanglifeecif.up_t_customers_class_1() -- 创建存储过程 IS BEGIN UPDATE shanglifeecif.individual a SET ( CustClass ,-- 客户等级 ConValue ,-- 贡献度分 Awarded3 ,-- 家庭加分2 Awarded2 ,-- 续期加分 Awarded1 ,-- 保单加分 SOValue ,-- 总分值 EndDate, -- 客户等级失效日期 Height, --身高 Weight, --体重 BMI, PIncome, --个人年收入 FIncome, --家庭年收入 IncomeSource, --收入来源 SIStatus, --社保情况 Ethnic, --民族情况 Nation, --国籍 MaritalStat, --婚姻 Employer,--工作单位 Education,--学历 Dday--死亡日期 ) = ( select CLASS_VALUE ,--客户等级 CONTRIBUTION_VALUE ,--贡献度分 AWARDED3, --家庭加分 AWARDED2, --续期加分 AWARDED1, --保单加分 TOTAL_VALUE, --总分值 END_DATE, --失效日期 STATURE, --身高 AVOIRDUPOIS,--体重 BMI, YEARINCOME, --个人年收入 FAMILYYEARSALARY, --家庭年收入 INCOMESOURCE, --收入来源 SOCIALINSUFLAG, --社保情况 NATIONALITY, --民族情况 NATIVEPLACE, --国籍 MARRIAGE,--婚姻 GRPNAME,--工作单位名称 DEGREE,--学历 DEATHDATE--死亡日期 from t_customer_class b where b.CUSTOMER_ID = a.scustid; ) WHERE 1=1 ; EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('up_t_customers_class_1',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('up_t_customers_class_1',sqlerrm(),sqlcode()) END; / DROP TABLE IF EXISTS shanglifeecif.occupation_tmp; CREATE TABLE shanglifeecif.occupation_tmp( scustid string not NULL COMMENT '客户号', OccupationId string DEFAULT NULL COMMENT '名称', Occupation string DEFAULT NULL COMMENT '出生日期' , HomeAdress string DEFAULT NULL COMMENT '籍贯' ) COMMENT 'occupation_tmp 客户职业临时表' STORED AS ES with shard number 10 replication 1; --根据HEALTH_INSURANCE_LISTING表更新 CREATE OR REPLACE PROCEDURE shanglifeecif.up_health_insurance_listing_1() -- 创建存储过程 IS BEGIN --清楚临时表数据 delete shanglifeecif.occupation_tmp; --插入去重数据到临时表 insert into shanglifeecif.occupation_tmp( scustid, OccupationId, Occupation, HomeAdress ) select appntno, appntoccupationcode ,--职业代码 appntoccupationname , APPNTHOMEADDRESS--职业名称 from HEALTH_INSURANCE_LISTING WHERE appntoccupationcode is not null and appntoccupationname is not null group by appntno,appntoccupationcode,appntoccupationname,APPNTHOMEADDRESS --更新个人信息表 UPDATE shanglifeecif.individual a SET ( OccupationId ,-- 职业代码 Occupation, -- 职业名称 HomeAdress ) = ( select OccupationId ,--职业代码 Occupation,--职业名称 HomeAdress from shanglifeecif.occupation_tmp b where b.scustid = a.scustid ) WHERE 1=1 ; EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('up_health_insurance_listing_1',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('up_health_insurance_listing_1',sqlerrm(),sqlcode()) END; ======================================================================================================================================= ===================================================用户关系信息解析插入==================================================================== ======================================================================================================================================= --更新individual 的用户关系 CREATE OR REPLACE PROCEDURE up_individual_relationship() -- 创建存储过程 IS BEGIN --清楚临时表数据 EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('up_individual_relationship',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('up_individual_relationship',sqlerrm(),sqlcode()) END; ======================================================================================================================================= ===================================================用户上游客户ID去重临时表==================================================================== ======================================================================================================================================= DROP TABLE IF EXISTS shanglifeecif.scustid_unique_tmp; CREATE TABLE shanglifeecif.scustid_unique_tmp( scustid string COMMENT '上游客户号' , indid string COMMENT '客户号' ) COMMENT '用户上游客户ID去重临时表' STORED AS ES with shard number 10 replication 1; --更新individual 的用户关系 CREATE OR REPLACE PROCEDURE shanglifeecif.up_scustid_unique_tmp() -- 创建存储过程 IS BEGIN --清楚临时表数据 delete from shanglifeecif.scustid_unique_tmp; --插入临时表数据 insert into shanglifeecif.scustid_unique_tmp(scustid,indid) select scustid,min(indid) from shanglifeecif.individual group by scustid; EXCEPTION WHEN HIVE_EXCEPTION THEN log_exception('up_scustid_unique_tmp',sqlerrm(),sqlcode()) WHEN Others THEN log_exception('up_scustid_unique_tmp',sqlerrm(),sqlcode()) END; ===================================================================================================================== 1,105,462 ---581292 插入投保 1,105,500 ---578591 插入被保人 1,119,154 ---156901 插入UNION 1,119,154 ---row_number()over(), 使用row_number()over() 329312 row_number()over() 行号 1313s 2232186条数据