Greenplum创建外部表

1.数据地址

  SZB-L0038788的/data/tpc-data目录

2.数据格式 文本数据

  1|AAAAAAAABAAAAAAA|18|Jackson |Parkway|Suite 280|Fairfield|Maricopa County|AZ|86192|United States|-7|condo|

3.GP的master是SZB-L0038784

4.定义外部表ddl语句

CREATE EXTERNAL TABLE ext_customer_address (

    ca_address_sk             integer                       ,    

    ca_address_id             char(16)                      ,

    ca_street_number          char(10)                      ,

    ca_street_name            varchar(60)                   ,

    ca_street_type            char(15)                      ,

    ca_suite_number           char(10)                      ,

    ca_city                   varchar(60)                   ,

    ca_county                 varchar(30)                   ,

    ca_state                  char(2)                       ,

    ca_zip                    char(10)                      ,

    ca_country                varchar(20)                   ,

    ca_gmt_offset             decimal(5,2)                  ,

    ca_location_type          char(20)                      

)

LOCATION('gpfdist://SZB-L0038788:8080/customer_address.dat')

FORMAT 'TEXT' (DELIMITER AS '|')

;

5.启动gpfdist服务，在有数据的服务器，即SZB-L0038788执行：

$ gpfdist -d /data/tpc-data -p 8080 -l /home/gpadmin/gpfdist.log  &

     -d指定etl服务器访问根目录，默认值是$GPHOME

6.执行ddl语句

$ psql -h SZB-L0038784  -d tpc -f /home/gpadmin/tpc-ds-yml/ext_customer_address.ddl

Password for user gpadmin: 

CREATE EXTERNAL TABLE



//目前操作用户是gpadmin，输入对应密码即可

//LOCATION('gpfdist://SZB-L0038788:8080/customer_address.dat')

  这里不能使用localhost或者127.0.0.1等，不然master访问会报error code = 111 (Connection refused)

7.执行sql查询

$ psql -h SZB-L0038784 -d tpc

tpc=# select * from ext_customer_address limit 3;

ERROR:  extra data after last expected column  (seg7 slice1 SZB-L0038786:40001 pid=2821)

DETAIL:  External table ext_customer_address, line 1 of gpfdist://SZB-L0038788:8080/customer_address.dat: "1|AAAAAAAABAAAAAAA|18|Jackson |Parkway|Suite 280|Fairfield|Maricopa County|AZ|86192|United States|-7..."

    ERROR:  extra data after last expected column是因为文本数据中‘|’分割导致多了一列导致，有14列，而在创建外部表的时候定义约束只有13列，所以又两种解决方法：1.删除源文本数据中的最后一个分隔符‘|’;2.ddl语句增加列约束符号

tpc=# alter external table ext_customer_address  add column null_name text;

ALTER EXTERNAL TABLE

tpc=# select * from ext_customer_address limit 3;

 ca_address_sk |  ca_address_id   | ca_street_number |   ca_street_name   | ca_street_type | ca_suite_number |     ca_city     |    ca_county    | ca_state | ca_zip |  ca_country   | ca_gmt_offset | ca_location_type | null_name 

---------------+------------------+------------------+--------------------+----------------+-----------------+-----------------+-----------------+----------+--------+---------------+---------------+------------------+-----------

 1             | AAAAAAAABAAAAAAA | 18               | Jackson            | Parkway        | Suite 280       | Fairfield       | Maricopa County | AZ       | 86192  | United States | -7            | condo            | 

 2             | AAAAAAAACAAAAAAA | 362              | Washington 6th     | RD             | Suite 80        | Fairview        | Taos County     | NM       | 85709  | United States | -7            | condo            | 

 3             | AAAAAAAADAAAAAAA | 585              | Dogwood Washington | Circle         | Suite Q         | Pleasant Valley | York County     | PA       | 12477  | United States | -5            | single family    | 

(3 rows)