gpload的load yaml文件里面的SQL字段的问题
Opened this issue · 0 comments
AlexiaChen commented
原因是我用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}"