gpload 数据装载问题

Question

数据装载过程中一直报错:
-f my.yml
2017-04-25 16:22:18|INFO| session started 2017-04-25 16:22:18
2017-04-25 16:22:18|INFO|setting schema 'public' for table 'test'
2017-04-25 16:22:18|INFO|started gpfdist -p 8000 -P 8001 -f "/home/gpadmin/extend/test2." -t 30
2017-04-25 16:22:18||A gpload control file processing error occurred. The configuration must contain gpload:input:quotefor csv formatted data
2017-04-25 16:22:18|INFO|rows Inserted = 0
2017-04-25 16:22:18|INFO|rows Updated = 0
2017-04-25 16:22:18|INFO|data formatting errors = 0
2017-04-25 16:22:18|INFO| failed

my.yml内容:

---

VERSION: 1.0.0.1

DATABASE: ljlj-test

USER: gpadmin

HOST: mdw

PORT: 5432

:

   INPUT:

    - SOURCE:

         LOCAL_HOSTNAME:

           - mdw

         PORT: 8000

         : 

           - /home/gpadmin/extend/test2.

    - COLUMNS:

           - id: int

           - pact_number: varchar(50)

           - grant_type: varchar(50)

           - ticket_usecode: varchar(100)

           - pact_id: int           

    - FORMAT: 

    - DELIMITER: '|'

    - ERROR_LIMIT: 25

    - ERROR_TABLE: test_err

   OUTPUT:

    - TABLE: test

    - MODE: INSERT

test2.内容:
6|102900928|product_conume|4290000100005|0
7|102900929|product_conume|3260000100006|0

的deug模式:
2017-04-25 16:28:30|DEBUG|getting config for version
2017-04-25 16:28:30|DEBUG|trying version
2017-04-25 16:28:30|DEBUG|found version
2017-04-25 16:28:30|INFO| session started 2017-04-25 16:28:30
2017-04-25 16:28:30|DEBUG|config {u'database': u'ljlj-test', u'host': u'mdw', u'version': u'1.0.0.1', u'user': u'gpadmin', u'': {u'input': [{u'source': {u'local_hostname': [u'mdw'], u'port': 8000, u'': [u'/home/gpadmin/extend/test2.']}}, {u'columns': [{u'id': u'int'}, {u'pact_number': u'varchar(50)'}, {u'grant_type': u'varchar(50)'}, {u'ticket_usecode': u'varchar(100)'}, {u'pact_id': u'int'}]}, {u'format': u''}, {u'delimiter': u'|'}, {u'error_limit': 25}, {u'error_table': u'test_err'}], u'output': [{u'table': u'test'}, {u'mode': u'INSERT'}]}, u'port': 5432}
2017-04-25 16:28:30|DEBUG|getting config for :output
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying output
2017-04-25 16:28:30|DEBUG|found output
2017-04-25 16:28:30|DEBUG|getting config for :output:table
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying output
2017-04-25 16:28:30|DEBUG|found output
2017-04-25 16:28:30|DEBUG|trying table
2017-04-25 16:28:30|DEBUG|found table
2017-04-25 16:28:30|DEBUG|getting config for host
2017-04-25 16:28:30|DEBUG|trying host
2017-04-25 16:28:30|DEBUG|found host
2017-04-25 16:28:30|DEBUG|getting config for port
2017-04-25 16:28:30|DEBUG|trying port
2017-04-25 16:28:30|DEBUG|found port
2017-04-25 16:28:30|DEBUG|getting config for user
2017-04-25 16:28:30|DEBUG|trying user
2017-04-25 16:28:30|DEBUG|found user
2017-04-25 16:28:30|DEBUG|getting config for database
2017-04-25 16:28:30|DEBUG|trying database
2017-04-25 16:28:30|DEBUG|found database
2017-04-25 16:28:30|DEBUG|getting config for password
2017-04-25 16:28:30|DEBUG|trying password
2017-04-25 16:28:30|DEBUG|connection string: user=gpadmin host=mdw port=5432 database=ljlj-test
2017-04-25 16:28:30|DEBUG|Successfully connected to database
2017-04-25 16:28:30|INFO|setting schema 'public' for table 'test'
2017-04-25 16:28:30|DEBUG|found input column: [u'"id"', u'integer', None, False]
2017-04-25 16:28:30|DEBUG|found input column: [u'"pact_number"', u'character varying(50)', None, False]
2017-04-25 16:28:30|DEBUG|found input column: [u'"grant_type"', u'character varying(50)', None, False]
2017-04-25 16:28:30|DEBUG|found input column: [u'"ticket_usecode"', u'character varying(100)', None, False]
2017-04-25 16:28:30|DEBUG|found input column: [u'"pact_id"', u'integer', None, False]
2017-04-25 16:28:30|DEBUG|getting config for :input:columns
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying columns
2017-04-25 16:28:30|DEBUG|found columns
2017-04-25 16:28:30|DEBUG|from columns are:
2017-04-25 16:28:30|DEBUG|id: int
2017-04-25 16:28:30|DEBUG|pact_number: varchar(50)
2017-04-25 16:28:30|DEBUG|grant_type: varchar(50)
2017-04-25 16:28:30|DEBUG|ticket_usecode: varchar(100)
2017-04-25 16:28:30|DEBUG|pact_id: int
2017-04-25 16:28:30|DEBUG|getting config for :output:mapping
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying output
2017-04-25 16:28:30|DEBUG|found output
2017-04-25 16:28:30|DEBUG|trying mapping
2017-04-25 16:28:30|DEBUG|"id": integer = "id"
2017-04-25 16:28:30|DEBUG|"pact_number": character varying(50) = "pact_number"
2017-04-25 16:28:30|DEBUG|"grant_type": character varying(50) = "grant_type"
2017-04-25 16:28:30|DEBUG|"ticket_usecode": character varying(100) = "ticket_usecode"
2017-04-25 16:28:30|DEBUG|"pact_id": integer = "pact_id"
2017-04-25 16:28:30|DEBUG|getting config for :input
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|getting config for :input:source(1)
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source(1)
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|getting config for :input:source(1):local_hostname
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source(1)
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|trying local_hostname
2017-04-25 16:28:30|DEBUG|found local_hostname
2017-04-25 16:28:30|DEBUG|getting config for :input:source:ssl
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|trying ssl
2017-04-25 16:28:30|DEBUG|getting config for :input:source:certificates_path
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|trying certificates_path
2017-04-25 16:28:30|DEBUG|getting config for :input:source(1):port
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source(1)
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|trying port
2017-04-25 16:28:30|DEBUG|found port
2017-04-25 16:28:30|DEBUG|getting config for :input:source(1):port_range
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source(1)
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|trying port_range
2017-04-25 16:28:30|DEBUG|getting config for :input:source(1):
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source(1)
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|getting config for :input:max_line_length
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying max_line_length
2017-04-25 16:28:30|DEBUG|getting config for :input:transform
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying transform
2017-04-25 16:28:30|DEBUG|getting config for :input:transform_config
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying transform_config
2017-04-25 16:28:30|LOG|trying to run gpfdist -p 8000 -P 8001 -f "/home/gpadmin/extend/test2." -t 30 -V
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO Before opening listening sockets - following listening sockets are available:
2017-04-25 16:28:30|DEBUG|gpfdist: [msg] libevent using: epoll
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV6 socket: [::]:8000
2017-04-25 16:28:30|DEBUG|gpfdist:
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV4 socket: 0.0.0.0:8000
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO Trying to open listening socket:
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV6 socket: [::]:8000
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 WARN Address already in use (errno = 98), port: 8000
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO Trying to open listening socket:
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV4 socket: 0.0.0.0:8000
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 WARN Address already in use (errno = 98), port: 8000
2017-04-25 16:28:30|LOG|gpfdist says:
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO Before opening listening sockets - following listening sockets are available:
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV6 socket: [::]:8001
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV4 socket: 0.0.0.0:8001
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO Trying to open listening socket:
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV6 socket: [::]:8001
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO Opening listening socket succeeded
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO Trying to open listening socket:
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 INFO IPV4 socket: 0.0.0.0:8001
2017-04-25 16:28:30|LOG|gpfdist says: 2017-04-25 16:28:30 11767 WARN Address already in use (errno = 98), port: 8001
2017-04-25 16:28:30|LOG|gpfdist says: Serving HTTP on port 8001, directory /home/gpadmin/extend
2017-04-25 16:28:30|INFO|started gpfdist -p 8000 -P 8001 -f "/home/gpadmin/extend/test2." -t 30 -V
2017-04-25 16:28:30|LOG|gpfdist is running on port 8001
2017-04-25 16:28:30|DEBUG|getting config for :input:source:ssl
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source
2017-04-25 16:28:30|DEBUG|found source
2017-04-25 16:28:30|DEBUG|trying ssl
2017-04-25 16:28:30|DEBUG|getting config for :input:source(2)
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying source(2)
2017-04-25 16:28:30|DEBUG|getting config for :preload
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying preload
2017-04-25 16:28:30|DEBUG|getting config for :output:mode
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying output
2017-04-25 16:28:30|DEBUG|found output
2017-04-25 16:28:30|DEBUG|trying mode
2017-04-25 16:28:30|DEBUG|found mode
2017-04-25 16:28:30|DEBUG|getting config for :input:error_table
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying error_table
2017-04-25 16:28:30|DEBUG|found error_table
2017-04-25 16:28:30|DEBUG|getting config for :input:log_errors
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying log_errors
2017-04-25 16:28:30|DEBUG|getting config for :sql
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying sql
2017-04-25 16:28:30|DEBUG|getting config for :input:format
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying format
2017-04-25 16:28:30|DEBUG|found format
2017-04-25 16:28:30|DEBUG|getting config for :input:delimiter
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying delimiter
2017-04-25 16:28:30|DEBUG|found delimiter
2017-04-25 16:28:30|DEBUG|getting config for :input:delimiter
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying delimiter
2017-04-25 16:28:30|DEBUG|found delimiter
2017-04-25 16:28:30|DEBUG|getting config for :input:null_as
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying null_as
2017-04-25 16:28:30|DEBUG|null False
2017-04-25 16:28:30|DEBUG|getting config for :input:escape
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying escape
2017-04-25 16:28:30|DEBUG|getting config for :input:quote
2017-04-25 16:28:30|DEBUG|trying
2017-04-25 16:28:30|DEBUG|found
2017-04-25 16:28:30|DEBUG|trying input
2017-04-25 16:28:30|DEBUG|found input
2017-04-25 16:28:30|DEBUG|trying quote
2017-04-25 16:28:30||A
2017-04-25 16:28:30|LOG|killing gpfdist
2017-04-25 16:28:30|DEBUG|gpfdist: 2017-04-25 16:28:30 11767 WARN signal 15 received. gpfdist exits
2017-04-25 16:28:30|DEBUG|gpfdist: 2017-04-25 16:28:30 11767 INFO ---------------------------------------
2017-04-25 16:28:30|DEBUG|gpfdist: 2017-04-25 16:28:30 11767 INFO STATUS: total session(s) 0
2017-04-25 16:28:30|DEBUG|gpfdist: session: [
2017-04-25 16:28:30|DEBUG|gpfdist: ]
2017-04-25 16:28:30|DEBUG|gpfdist: 2017-04-25 16:28:30 11767 INFO ---------------------------------------
2017-04-25 16:28:30|INFO|rows Inserted = 0
2017-04-25 16:28:30|INFO|rows Updated = 0
2017-04-25 16:28:30|INFO| formatting errors = 0
2017-04-25 16:28:30|INFO| failed

其中端口8000是gpfdist的端口.
求大神解释下,

 A gpload control file processing error occurred. The configuration must contain gpload:input:quotefor csv formatted data        
      
 
 为什么不匹配,test2.中的分隔符就是'|' 呀!!!!
 

Answer 1

需要注意两点:
1.gpfdist没有必要手动启动,在执行gpload的时候会自动启动gpfdist,还有在yml中之配置了一个8000的端口,但是在执行的时候会出现8001端口(命令执行完毕后,gpfdist会自动杀死)
2.yml中缺少对quote的设置: - QUOTE: ''''

以上两点就可以解决上面的问题.