ShangHaiLifeECIF/sql-achievement/存储过程/洛阳测试环境使用sql整理/csv建表语句.sql

DROP TABLE IF EXISTS shanghailifeecif.policy_information;
CREATE TABLE shanghailifeecif.policy_information(
  contno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  riskcode varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  riskname varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  riskperiod varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  risktype varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  payintv varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  security varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  payendyear decimal(10,2) DEFAULT NULL, 
  payendyearflag varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuyear decimal(10,2) DEFAULT NULL, 
  insuyearflag varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  paycount decimal(10,2) DEFAULT NULL, 
  signdate string DEFAULT NULL, 
  cvalidate string DEFAULT NULL, 
  enddate string DEFAULT NULL, 
  modifydate string DEFAULT NULL, 
  prem decimal(10,2) DEFAULT NULL, 
  appflag varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  customerno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  name varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  sex varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  birthday string DEFAULT NULL, 
  idtype varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  idno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuredno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuredname varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuredsex varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuredbirthday string DEFAULT NULL, 
  insuredidtype varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuredidno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  relationtoappnt varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  salechnl varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  salecom varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  salechannels varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  customgetpoldate string DEFAULT NULL, 
  prem_js decimal(10,2) DEFAULT NULL, 
  agentcom varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  paytodate string DEFAULT NULL, 
  amnt decimal(10,2) DEFAULT NULL, 
  insuredmobile varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  preservationflag varchar2(2) DEFAULT NULL,  -- dialect: ORACLE
  newbankcode varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  bankcode varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  insuredbankcode varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  newacctype varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  acctype varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  insuredacctype varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  insert_time string DEFAULT NULL, 
  modify_time string DEFAULT NULL
)
COMMENT 'policy_information'
STORED AS CSVFILE ;

DROP TABLE IF EXISTS shanghailifeecif.t_customer_class;
CREATE  TABLE shanghailifeecif.t_customer_class(
  id int NOT NULL, 
  customer_id varchar2(200) NOT NULL,  -- dialect: ORACLE
  customer_name varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  gender varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  birthdate string DEFAULT NULL, 
  id_type varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  id_no varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  class_value int DEFAULT NULL, 
  contribution_value decimal(10,2) DEFAULT NULL, 
  awarded3 decimal(10,2) DEFAULT NULL, 
  awarded2 decimal(10,2) DEFAULT NULL, 
  awarded1 decimal(10,2) DEFAULT NULL, 
  total_value decimal(10,2) DEFAULT NULL, 
  end_date string DEFAULT NULL, 
  created_user varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  created_date string DEFAULT NULL, 
  modified_user varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  modified_date string DEFAULT NULL, 
  is_delete varchar2(2) DEFAULT NULL,  -- dialect: ORACLE
  effective_date string DEFAULT NULL, 
  updateby string DEFAULT NULL, 
  stature decimal(8,2) DEFAULT NULL, 
  avoirdupois decimal(8,2) DEFAULT NULL, 
  dentype varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  yearincome decimal(8,2) DEFAULT NULL, 
  familyyearsalary decimal(8,2) DEFAULT NULL, 
  incomesource char(4) DEFAULT NULL,  -- dialect: ORACLE
  socialinsuflag varchar2(1) DEFAULT NULL,  -- dialect: ORACLE
  pre_total_value decimal(10,2) DEFAULT NULL, 
  pre_class_value int DEFAULT NULL, 
  nationality char(4) DEFAULT NULL,  -- dialect: ORACLE
  nativeplace char(4) DEFAULT NULL,  -- dialect: ORACLE
  marriage char(4) DEFAULT NULL,  -- dialect: ORACLE
  grpname varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  degree char(4) DEFAULT NULL,  -- dialect: ORACLE
  deathdate string DEFAULT NULL, 
  zipcode varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  phone varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  postaladdress varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  email varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  mobile varchar2(128) DEFAULT NULL,  -- dialect: ORACLE
  bmi decimal(8,2) DEFAULT NULL, 
  insert_time string DEFAULT NULL, 
  modify_time string DEFAULT NULL
)
COMMENT 't_customer_class'
STORED AS CSVFILE ;

DROP TABLE IF EXISTS shanghailifeecif.insuranceinfo;
CREATE  TABLE shanghailifeecif.insuranceinfo(
  contno varchar2(20) DEFAULT NULL,  -- dialect: ORACLE
  prtno varchar2(20) DEFAULT NULL,  -- dialect: ORACLE
  prem decimal(10,2) DEFAULT NULL, 
  riskname varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  riskstyle varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  appflag varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  uwstate varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  agentcom varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  isybt varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  selltype varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  polapplydate string DEFAULT NULL, 
  scandate string DEFAULT NULL, 
  scantime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  inputdate string DEFAULT NULL, 
  inputtime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  signdate string DEFAULT NULL, 
  signtime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  cvalidate string DEFAULT NULL, 
  customgetpoldate string DEFAULT NULL, 
  uwdate string DEFAULT NULL, 
  uwtime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  appntbirthday string DEFAULT NULL, 
  isuwpass varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  occupationcode varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  occupationname varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  appntage decimal(10,2) DEFAULT NULL, 
  insuredage decimal(10,2) DEFAULT NULL, 
  approvedate string DEFAULT NULL, 
  approvetime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  deliverydate string DEFAULT NULL, 
  deliverytime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  agentcomname varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  newbankcode varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  username_input varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  username_scan varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  username_approve varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  enteraccdate string DEFAULT NULL, 
  confmakedate string DEFAULT NULL, 
  confmaketime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  chargedate string DEFAULT NULL, 
  chargetime varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  isrealtimeflag varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  iscleanflag varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  isuwstandardflag varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  issuetype varchar2(8) DEFAULT NULL,  -- dialect: ORACLE
  managecom varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  managecomname varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  salechnl varchar2(255) DEFAULT NULL,  -- dialect: ORACLE
  uwconclusion varchar2(4000) DEFAULT NULL,  -- dialect: ORACLE
  ins_time string DEFAULT NULL
)
COMMENT 'insuranceinfo'
STORED AS CSVFILE ;

DROP TABLE IF EXISTS shanghailifeecif.personal_insurance;
CREATE  TABLE shanghailifeecif.personal_insurance(
  comcode4 varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  comname4 varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  comcode6 varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  comname6 varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  managecom varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  managecomname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  salesareacode varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  salesareaname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  branchattr varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  branchattrname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  salesdepcode varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  salesdepname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  salesgroupcode varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  salesgroupname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  agentcode varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  agentname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  employdate string DEFAULT NULL, 
  gradename varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  prtno varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  contno varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  riskcode varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  riskname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  mainattachedriskflag varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  amnt decimal(10,2) DEFAULT NULL, 
  polapplydate string DEFAULT NULL, 
  scandate string DEFAULT NULL, 
  confdate string DEFAULT NULL, 
  signdate string DEFAULT NULL, 
  cvalidate string DEFAULT NULL, 
  customgetpoldate string DEFAULT NULL, 
  getpoldate string DEFAULT NULL, 
  overhesitationperiod varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  withdrawaldate string DEFAULT NULL, 
  wtdate string DEFAULT NULL, 
  ctdate string DEFAULT NULL, 
  contstate varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  longpols int DEFAULT NULL, 
  prem decimal(10,2) DEFAULT NULL, 
  standprem decimal(10,2) DEFAULT NULL, 
  annualizedprem decimal(10,2) DEFAULT NULL, 
  fycryc decimal(10,2) DEFAULT NULL, 
  payintv varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  payyears int DEFAULT NULL, 
  polyear int DEFAULT NULL, 
  conttype varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  contflag varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  calldate string DEFAULT NULL, 
  agentstate varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  insert_time string DEFAULT NULL, 
  modify_time string DEFAULT NULL
)
COMMENT 'personal_insurance'
STORED AS CSVFILE ;

DROP TABLE IF EXISTS shanghailifeecif.health_group_listing;
CREATE TABLE shanghailifeecif.health_group_listing(
  contno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  prtno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  appntno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  grpname varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  comdocidtype varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  comdocidno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  validity_date string DEFAULT NULL, 
  salechnl varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  agentcom varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  agentname varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  riskname varchar2(400) DEFAULT NULL,  -- dialect: ORACLE
  amnt decimal(10,2) DEFAULT NULL, 
  paymode varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  chargefeerate decimal(10,2) DEFAULT NULL, 
  commrate decimal(10,2) DEFAULT NULL, 
  summanagepremr decimal(10,2) DEFAULT NULL, 
  prem decimal(10,2) DEFAULT NULL, 
  peoples3 decimal(10,2) DEFAULT NULL, 
  cvalidate string DEFAULT NULL, 
  payenddate string DEFAULT NULL, 
  signdate string DEFAULT NULL, 
  sumduepaymoney decimal(10,2) DEFAULT NULL, 
  nipeople decimal(10,2) DEFAULT NULL, 
  niprem decimal(10,2) DEFAULT NULL, 
  ztpeople decimal(10,2) DEFAULT NULL, 
  ztprem decimal(10,2) DEFAULT NULL, 
  insert_time string DEFAULT NULL, 
  modify_time string DEFAULT NULL
)
COMMENT 'health_group_listing'
STORED AS CSVFILE ;


DROP TABLE IF EXISTS shanghailifeecif.audit_edorlist;
CREATE TABLE shanghailifeecif.audit_edorlist(
  managecom varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  managename varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  salechnl varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  salechnlname varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  selltype varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  selltypename varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  contno varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  riskcodes varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  risknames varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  risktypes varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  signdate string DEFAULT NULL, 
  appntname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  prem decimal(10,2) DEFAULT NULL, 
  edorname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  edoracceptno varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  edorno varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  edorvalidate string DEFAULT NULL, 
  customerhandleflag varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  operator varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  approveoperator varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  getmoney decimal(10,2) DEFAULT NULL, 
  cusappdate string DEFAULT NULL, 
  payyears varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  agentname varchar2(256) DEFAULT NULL,  -- dialect: ORACLE
  edorstate varchar2(64) DEFAULT NULL,  -- dialect: ORACLE
  insert_time string DEFAULT NULL, 
  modify_time string DEFAULT NULL
)
COMMENT 'audit_edorlist'
STORED AS CSVFILE ;

DROP TABLE IF EXISTS shanghailifeecif.insurance_claim;
CREATE  TABLE shanghailifeecif.insurance_claim(
  batno varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  contno varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  clmno varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  cvalidate string DEFAULT NULL, 
  riskdate string DEFAULT NULL, 
  ageofdanger decimal(3,0) DEFAULT NULL, 
  riskcode varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  riskname varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  lloccurreason varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  accresult1 varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  accresult2 varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  accidentdetail varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  llgettype varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  realpay decimal(12,2) DEFAULT NULL, 
  applydate string DEFAULT NULL, 
  accepteddate string DEFAULT NULL, 
  rptdate string DEFAULT NULL, 
  rgtdate string DEFAULT NULL, 
  auditdate string DEFAULT NULL, 
  examdate string DEFAULT NULL, 
  senddate string DEFAULT NULL, 
  returndate string DEFAULT NULL, 
  llclaimstate varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  operator varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  auditper varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  examper varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  isinvestigated varchar2(10) DEFAULT NULL,  -- dialect: ORACLE
  idno varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  insuredname varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  isfromwx varchar2(10) DEFAULT NULL,  -- dialect: ORACLE
  insuredno varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  amnt decimal(12,2) DEFAULT NULL, 
  managecom varchar2(10) DEFAULT NULL,  -- dialect: ORACLE
  estimatedreparations varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  risktype varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  getdutycode varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  getdutyname varchar2(512) DEFAULT NULL,  -- dialect: ORACLE
  salechnl varchar2(40) DEFAULT NULL,  -- dialect: ORACLE
  accname varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  accidenttype varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  enddate string DEFAULT NULL, 
  salechnl2 varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuredsex varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  insuredjob varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  paystatus varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  rgtno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  rptno varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  rptorname varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  relation varchar2(200) DEFAULT NULL,  -- dialect: ORACLE
  accdesc varchar2(2000) DEFAULT NULL,  -- dialect: ORACLE
  accdentdesc varchar2(2000) DEFAULT NULL,  -- dialect: ORACLE
  insert_time string DEFAULT NULL, 
  modify_time string DEFAULT NULL
)
COMMENT 'insurance_claim'
STORED AS CSVFILE ;


服务器操作命令  
	初始化命令      source /home/xinghuan-install/TDH-Client/init.sh   （bash: hdfs: 未找到命令...出现后执行初始化命令）
	上传文件命令    
	hdfs dfs -put /home/frontData/policy_information.txt /tmp/myData/
	hdfs dfs -put /home/frontData/t_customer_class.txt /tmp/myData/
	hdfs dfs -put /home/frontData/insuranceinfo.txt /tmp/myData/
	hdfs dfs -put /home/frontData/personal_insurance.txt /tmp/myData/
	hdfs dfs -put /home/frontData/health_group_listing.txt /tmp/myData/
	hdfs dfs -put /home/frontData/audit_edorlist.txt /tmp/myData/
	hdfs dfs -put /home/frontData/insurance_claim.txt /tmp/myData/

数据库操作命令加载数据：
LOAD DATA INPATH '/tmp/myData/policy_information.txt' OVERWRITE INTO TABLE shanghailifeecif.policy_information;
LOAD DATA INPATH '/tmp/myData/t_customer_class.txt' OVERWRITE INTO TABLE shanghailifeecif.t_customer_class;
LOAD DATA INPATH '/tmp/myData/insuranceinfo.txt' OVERWRITE INTO TABLE shanghailifeecif.insuranceinfo;
LOAD DATA INPATH '/tmp/myData/personal_insurance.txt' OVERWRITE INTO TABLE shanghailifeecif.personal_insurance;
LOAD DATA INPATH '/tmp/myData/health_group_listing.txt' OVERWRITE INTO TABLE shanghailifeecif.health_group_listing;
LOAD DATA INPATH '/tmp/myData/audit_edorlist.txt' OVERWRITE INTO TABLE shanghailifeecif.audit_edorlist;
LOAD DATA INPATH '/tmp/myData/insurance_claim.txt' OVERWRITE INTO TABLE shanghailifeecif.insurance_claim;