--create schema sqmdb_cost create foreign table sqmdb_cost.jituanchengben_e( report_id varchar, period varchar, period_type varchar, segment1 varchar, segment1_name varchar, prov_code varchar, city_code varchar, city_name varchar, prov_name varchar, grid_code varchar, grid_name varchar, entity_id varchar, entity_name_disp varchar, measure_sts numeric, ds_cn varchar, lgl_type_cd varchar, lgl_type_name varchar, sch_cd varchar, sch_name varchar, sort_id numeric, column_tag varchar, amount numeric, field1 varchar, field2 varchar, field3 varchar, field4 varchar, field5 varchar, sdate timestamp, smonth numeric, scode numeric, field6 varchar ) server pg_file_server options(filename '/data/esbftp/dopdata/nlptdata/jt_chengben_202204_202205060050',format 'text',header 'false',delimiter '|',null ''); drop table sqmdb_cost.equip CREATE FOREIGN TABLE sqmdb_cost.equip_e( --create table sqmdb_cost.equip( eqp_sid numeric NULL, eqp_name varchar NULL, profession varchar NULL, nms_orig_res_id varchar NULL, city_code numeric NULL, area_code numeric NULL, factory varchar NULL, reslvl varchar NULL, eqp_type varchar NULL, room_sid numeric NULL, room_name varchar NULL, baidu_longitude numeric NULL, baidu_latitude numeric NULL, equip_model varchar NULL, manage_ipaddress varchar NULL, loopback varchar NULL, grid_id numeric null, wx_type varchar null, property_type numeric null, station_sid numeric null, maintenace_person varchar NULL, device_maintenace_person varchar NULL ) SERVER pg_file_server OPTIONS (filename '/data/chengben_e/equip_e_suc', format 'text', header 'false', delimiter '|', null ''); --IP端口 baobiao_ipduankou_yyyymm --drop foreign table sqmdb_cost.baobiao_ipduankou_e; create foreign table sqmdb_cost.baobiao_ipduankou_e( kpi_code varchar, kpi_name varchar, month_id varchar, pro_name varchar, prov_id numeric, grade varchar, city_desc varchar, area_no varchar, kpi_value numeric, m_ly_value numeric, tongbi_daoda numeric, by_szl numeric, qn_szl numeric, tongbi_szl numeric, byjz numeric, syjz numeric, ljjz numeric, zd1 varchar) SERVER pg_file_server OPTIONS (filename '/data/chengben_e/baobiao_ipduankou_e', format 'text', header 'false', delimiter '|', null ''); --经分_用户数 baobiao_jf_yonghush_yyyymm --drop foreign table sqmdb_cost.baobiao_jf_yonghush_e; create foreign table sqmdb_cost.baobiao_jf_yonghush_e( kpi_code varchar, kpi_name varchar, month_id varchar, prov_id numeric, pro_name varchar, grade varchar, city_id varchar, city_name varchar, kpi_value numeric, m_ly_value numeric, preinfo1 varchar, preinfo2 varchar, preinfo3 varchar, preinfo4 varchar, preinfo5 varchar, preinfo6 varchar, zd1 varchar) SERVER pg_file_server OPTIONS (filename '/data/chengben_e/baobiao_jf_yonghush_e', format 'text', header 'false', delimiter '|', null ''); --kuandai_ftth（包含端口数 yyyymmdd_kuandai_ftth.TXT） create foreign TABLE sqmdb_cost.kuandai_ftth_e ( local_net_name varchar NULL, area_name varchar NULL, areal_type_name varchar NULL, dslam_port_count numeric NULL, dslam_port_idle_count numeric NULL, dslam_port_p varchar NULL, fttb_port_count numeric NULL, fttb_port_sts_count numeric NULL, fttb_port_p varchar NULL, pon_ad_port_count numeric NULL, pon_ad_port_sts_count numeric NULL, pon_ad_port_p varchar NULL, pon_lan_port_count numeric NULL, pon_lan_port_sts_count numeric NULL, pon_lan_port_p varchar NULL, ftth_port_count numeric NULL, ftth_port_sts_count numeric NULL, ftth_port_p varchar NULL, ftth_zj_port_count numeric NULL, ftth_zj_port_sts_count numeric NULL, ftth_zj_port_p varchar NULL, ftth_hz_port_count numeric NULL, ftth_hz_port_sts_count numeric NULL, ftth_hz_port_p varchar NULL, sdate timestamp NULL) SERVER pg_file_server OPTIONS (filename '/data/chengben_e/kuandai_ftth_e', format 'text', header 'false', delimiter '|', null ''); --合同预提表（带成本中心） --drop FOREIGN table sqmdb_cost.hetong_yuti_e; create foreign table sqmdb_cost.hetong_yuti_e( --create table sqmdb_cost.hetong_yuti( sob_id numeric, sob_code varchar, sob_name varchar, pro_code varchar, pro_name varchar, le_cate_code varchar, le_cate_name varchar, le_code varchar, le_name varchar, com_code varchar, com_name varchar, contract_com_code varchar, contract_com_name varchar, contract_dept_code numeric, contract_dept_name varchar, period_name varchar, period_year numeric, period_num varchar, period_id varchar, prebill_arrair_no varchar, prebill_contract_no varchar, contract_no_local varchar, contract_no_global varchar, contract_name varchar, contract_desc varchar, repository_name_unicom varchar, repository_name_vendor varchar, contract_type_code numeric, contract_type_name varchar, contract_status_code numeric, contract_status_name varchar, contract_changetype_code numeric, contract_changetype_name varchar, contract_cate_l1_code numeric, contract_cate_l1_name varchar, contract_cate_l2_code numeric, contract_cate_l2_name varchar, contract_cate_l3_code numeric, contract_cate_l3_name varchar, prebillno varchar, vendor_name varchar, start_date varchar, end_date varchar, relatedparty_name varchar, projectaccount_desc varchar, billrowid varchar, longvalue numeric, prebill_type_name varchar, prebill_cate_l1_code numeric, prebill_cate_l1_name varchar, prebill_cate_l2_code numeric, prebill_cate_l2_name varchar, cost_center_id varchar, cost_center_name varchar, profess_id varchar, profess_name varchar, cost_index_id varchar, cost_index_name varchar, debit_account_code varchar, debit_account_desc varchar, credit_account_code varchar, credit_account_desc varchar, contract_taker_code numeric, contract_taker_name varchar, contract_taker_mobile varchar, contract_currency_code numeric, contract_currency_name varchar, contract_exchange_rate numeric, contract_amount numeric, contract_rmbmoney numeric, amount_current_amortize numeric, amount_provision numeric, amount_offset numeric, amount_current_provision numeric, amount_accruedm numeric, amount_beach numeric, amount_handle numeric, prebill_record_id numeric, prebill_record_rowid numeric, prebill_certificate_id numeric, prebill_period_id numeric, prebill_contract_id numeric, orgouname varchar, customer_account varchar, customer_account_desc varchar, money_benefit numeric, remark varchar, dwa_creation_date varchar, month_id varchar, day_id varchar, prov_id varchar, zd1 varchar) SERVER pg_file_server OPTIONS (filename '/data/chengben_e/hetong_yuti_e_suc', format 'text', header 'false', delimiter '|', null ''); --合同基本信息，带当前版本/历史版本 create foreign table sqmdb_cost.hetong_jibenxinxi_e( sob_id numeric, sob_code varchar, sob_name varchar, pro_code varchar, pro_name varchar, le_cate_code varchar, le_cate_name varchar, le_code varchar, le_name varchar, com_code varchar, com_name varchar, dept_code numeric, dept_name varchar, vision_flag varchar, arrair_no varchar, contract_no_local varchar, contract_no_global varchar, contract_name varchar, contract_desc varchar, repository_name_unicom varchar, repository_name_vendor varchar, contract_type_code numeric, contract_type_name varchar, arrair_status_code numeric, arrair_status_name varchar, contract_status_code numeric, contract_status_name varchar, contract_cate_l1_code numeric, contract_cate_l1_name varchar, contract_cate_l2_code numeric, contract_cate_l2_name varchar, contract_cate_l3_code numeric, contract_cate_l3_name varchar, contract_select_code numeric, contract_select_name varchar, contract_degree_code numeric, contract_degree_name varchar, contract_priority_code numeric, contract_priority_name varchar, contract_payment_type_code numeric, contract_payment_type_name varchar, contract_approve_level_code numeric, contract_approve_level_name varchar, contract_changetype_code numeric, contract_changetype_name varchar, contract_timelimit_code numeric, contract_timelimit_name varchar, contract_budget_type_code numeric, contract_budget_type_name varchar, perform_type_code numeric, perform_type_name varchar, is_renew numeric, account_rate varchar, taker_code numeric, taker_name varchar, taker_mobile varchar, sign_name varchar, currency_code numeric, currency_name varchar, exchange_rate numeric, finance_amount numeric, finance_rmbmoney numeric, finance_gbkmoney varchar, finance_total numeric, finance_vat_rate varchar, finance_un_vat_amount numeric, finance_rmb_un_vat_amount numeric, finance_vatax numeric, finance_rmb_vatax numeric, arrair_amount numeric, arrair_amount_gbk4 varchar, arrair_pay_plan numeric, arrair_pay_actual numeric, arrair_balance numeric, audit_money4 numeric, audit_ratio4 numeric, audit_sum4 numeric, audit_vatax numeric, audit_unvat_amount numeric, audit_vat_rate varchar, fixed_amount numeric, arrair_id numeric, arrair_id_contract numeric, contract_id numeric, contract_id_finance numeric, contract_id_arrair_now numeric, contract_id_arrair_origin numeric, provinceid numeric, companyid numeric, companyname varchar, draftouttab varchar, performtab varchar, last_approver_code numeric, last_approver_name varchar, sign_status_code numeric, sign_date varchar, start_date varchar, end_date varchar, sign_dept_name varchar, approval_date_send varchar, approval_date varchar, relationid numeric, relationname varchar, arrair_id_old numeric, purchase_result_id varchar, arrair_deleted_flag numeric, content_deleted_flag numeric, creation_date varchar, created_by varchar, last_update_date varchar, dwa_creation_date varchar, month_id varchar, day_id varchar, prov_id varchar, zd1 varchar) SERVER pg_file_server OPTIONS (filename '/data/chengben_e/hetong_jibenxinxi_e_suc', format 'text', header 'false', delimiter '|', null '');