chengben/Scripts/虚机外部表建表语句.sql

--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 '');