Open AlexiaChen opened 2 years ago
原因是我用gpload同步数据搭配GP中,需要根据日期分表来推送,所以需要在SQL字段的BEFORE字段下面运行CREATE TABLE语句,但是坑的地方是,这个CREATE TABLE 不起作用,其他的INSERT UPDATE DROP都可以起作用,所以gpload报错了,如下:
2022-02-23 15:31:25|ERROR|table XXXXXXXX does not exist in database postgres 2022-02-23 15:31:25|INFO|rows Inserted = 0 2022-02-23 15:31:25|INFO|rows Updated = 0 2022-02-23 15:31:25|INFO|data formatting errors = 0 2022-02-23 15:31:25|INFO|gpload failed
但是用Navicat单独运行CREATE TABLE 确实是可以看到这个表被成功建立的。试了各种方法都不行。
以下就是问题load yaml文件:
VERSION: 1.0.0.1 DATABASE: postgres USER: [GP-USERname] HOST: [GP-host] PORT: [GP-port] GPLOAD: INPUT: - SOURCE: LOCAL_HOSTNAME: - ods-gp-intf-01 PORT: 9005 FILE: - /home/gpadmin/gpfdist/txt/SMF_20220223100715_log_node5.167673.txt - COLUMNS: - vnfid: VARCHAR(100) - sequence_no: VARCHAR(100) - starttime: VARCHAR(100) - endtime: VARCHAR(100) - operation_result: VARCHAR(100) - procedure_identification: VARCHAR(100) - protocol_cause: VARCHAR(100) - external_cause: VARCHAR(100) - fail_cause_type: VARCHAR(100) - fail_peernf_type: VARCHAR(100) - imsi: VARCHAR(100) - imei: VARCHAR(100) - msisdn: VARCHAR(100) - guami: VARCHAR(100) - pdu_session_id: VARCHAR(100) - qos_flow_id: VARCHAR(100) - linked_eps_bearer_id: VARCHAR(100) - eps_interworking_indication: VARCHAR(100) - dnn: VARCHAR(100) - snssai: VARCHAR(100) - ssc_mode: VARCHAR(100) - dnn_selection_mode: VARCHAR(100) - ladn_state: VARCHAR(100) - antype: VARCHAR(100) - rattype: VARCHAR(100) - tai: VARCHAR(100) - ncgi: VARCHAR(100) - gnodeb_id: VARCHAR(100) - ue_ipv4_address: VARCHAR(100) - ue_ipv6_address_central_psa: VARCHAR(100) - ue_ipv6_address_local_psa: VARCHAR(100) - local_access_type: VARCHAR(100) - central_psa_upf_node_id: VARCHAR(100) - local_psa_upf_node_id: VARCHAR(100) - an_ipv4_address_for_data: VARCHAR(100) - an_ipv6_address_for_data: VARCHAR(100) - charging_characteristics: VARCHAR(100) - charg_id: VARCHAR(100) - using_ul_ambr: VARCHAR(100) - using_dl_ambr: VARCHAR(100) - ul_apn_dnn_ambr_in_sub_data: VARCHAR(100) - dl_apn_dnn_ambr_in_sub_data: VARCHAR(100) - qi5_in_sub_data: VARCHAR(100) - arp_in_sub_data: VARCHAR(100) - using_5qi: VARCHAR(100) - using_arp: VARCHAR(100) - qo_sflow_qos_list: VARCHAR(4000) - p_provincecode: VARCHAR(100) - FORMAT: text - DELIMITER: ',' - QUOTE: '"' - HEADER: false - ESCAPE: OFF - NULL_AS: '' - ERROR_LIMIT: 25 - ERROR_TABLE: public.member_err #- LOG_ERRORS: true OUTPUT: - TABLE: ipd_ztoy.ods_5g_smf_telecom_q_dayid - MODE: INSERT #PRELOAD: #- REUSE_TABLES: true SQL: - BEFORE: "CREATE TABLE IF NOT EXISTS ipd_ztoy.ods_5g_smf_telecom_q_dayss(vnfid VARCHAR(100),sequence_no VARCHAR(100),starttime VARCHAR(100),endtime VARCHAR(100),operation_result VARCHAR(100),procedure_identification VARCHAR(100),protocol_cause VARCHAR(100),external_cause VARCHAR(100),fail_cause_type VARCHAR(100),fail_peernf_type VARCHAR(100),imsi VARCHAR(100),imei VARCHAR(100),msisdn VARCHAR(100),guami VARCHAR(100),pdu_session_id VARCHAR(100),qos_flow_id VARCHAR(100),linked_eps_bearer_id VARCHAR(100),eps_interworking_indication VARCHAR(100),dnn VARCHAR(100),snssai VARCHAR(100),ssc_mode VARCHAR(100),dnn_selection_mode VARCHAR(100),ladn_state VARCHAR(100),antype VARCHAR(100),rattype VARCHAR(100),tai VARCHAR(100),ncgi VARCHAR(100),gnodeb_id VARCHAR(100),ue_ipv4_address VARCHAR(100),ue_ipv6_address_central_psa VARCHAR(100),ue_ipv6_address_local_psa VARCHAR(100),local_access_type VARCHAR(100),central_psa_upf_node_id VARCHAR(100),local_psa_upf_node_id VARCHAR(100),an_ipv4_address_for_data VARCHAR(100),an_ipv6_address_for_data VARCHAR(100),charging_characteristics VARCHAR(100),charg_id VARCHAR(100),using_ul_ambr VARCHAR(100),using_dl_ambr VARCHAR(100),ul_apn_dnn_ambr_in_sub_data VARCHAR(100),dl_apn_dnn_ambr_in_sub_data VARCHAR(100),qi5_in_sub_data VARCHAR(100),arp_in_sub_data VARCHAR(100),using_5qi VARCHAR(100),using_arp VARCHAR(100),qo_sflow_qos_list VARCHAR(4000),p_provincecode VARCHAR(100),p_date VARCHAR(100),p_hour VARCHAR(100),p_quarter VARCHAR(100)) distributed BY (imsi);COMMIT;" #- BEFORE: "CREATE TABLE ipd_ztoy.ods_5g_smf_telecom_q_dayss(vnfid VARCHAR(100),sequence_no VARCHAR(100),starttime VARCHAR(100),endtime VARCHAR(100),operation_result VARCHAR(100),procedure_identification VARCHAR(100),protocol_cause VARCHAR(100),external_cause VARCHAR(100),fail_cause_type VARCHAR(100),fail_peernf_type VARCHAR(100),imsi VARCHAR(100),imei VARCHAR(100),msisdn VARCHAR(100),guami VARCHAR(100),pdu_session_id VARCHAR(100),qos_flow_id VARCHAR(100),linked_eps_bearer_id VARCHAR(100),eps_interworking_indication VARCHAR(100),dnn VARCHAR(100),snssai VARCHAR(100),ssc_mode VARCHAR(100),dnn_selection_mode VARCHAR(100),ladn_state VARCHAR(100),antype VARCHAR(100),rattype VARCHAR(100),tai VARCHAR(100),ncgi VARCHAR(100),gnodeb_id VARCHAR(100),ue_ipv4_address VARCHAR(100),ue_ipv6_address_central_psa VARCHAR(100),ue_ipv6_address_local_psa VARCHAR(100),local_access_type VARCHAR(100),central_psa_upf_node_id VARCHAR(100),local_psa_upf_node_id VARCHAR(100),an_ipv4_address_for_data VARCHAR(100),an_ipv6_address_for_data VARCHAR(100),charging_characteristics VARCHAR(100),charg_id VARCHAR(100),using_ul_ambr VARCHAR(100),using_dl_ambr VARCHAR(100),ul_apn_dnn_ambr_in_sub_data VARCHAR(100),dl_apn_dnn_ambr_in_sub_data VARCHAR(100),qi5_in_sub_data VARCHAR(100),arp_in_sub_data VARCHAR(100),using_5qi VARCHAR(100),using_arp VARCHAR(100),qo_sflow_qos_list VARCHAR(4000),p_provincecode VARCHAR(100),p_date VARCHAR(100),p_hour VARCHAR(100),p_quarter VARCHAR(100)) distributed BY (imsi)" #- BEFORE: "INSERT INTO ipd_ztoy.ods_5g_smf_telecom_q_dayid VALUES('vnfid', 'fuck you')" #- BEFORE: "DROP TABLE ipd_ztoy.ods_5g_smf_telecom_q_dayid"
最后怎么解决的?不用gpload的SQL字段创建table了,直接用psql命令行创建:
psql -h ${gpload_host} -p ${gpload_port} -U ${gpload_user} -d ${gpload_database} -c "${create_table_sql}"
原因是我用gpload同步数据搭配GP中,需要根据日期分表来推送,所以需要在SQL字段的BEFORE字段下面运行CREATE TABLE语句,但是坑的地方是,这个CREATE TABLE 不起作用,其他的INSERT UPDATE DROP都可以起作用,所以gpload报错了,如下:
但是用Navicat单独运行CREATE TABLE 确实是可以看到这个表被成功建立的。试了各种方法都不行。
以下就是问题load yaml文件:
最后怎么解决的?不用gpload的SQL字段创建table了,直接用psql命令行创建:
References: