gpload的load yaml文件里面的SQL字段的问题

[AlexiaChen]

原因是我用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}"

References:

• https://www.postgresqltutorial.com/postgresql-create-table/
• https://gpdb.docs.pivotal.io/6-3/utility_guide/ref/gpload.html
• https://docs.greenplum.org/6-6/utility_guide/ref/psql.html