Error Loading Data to an External Table by Querying a Table in a Different Database

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

The following DDL was used for an external table in the database:

CREATE EXTERNAL WEB TABLE test1 ( col1 varchar(5),col2 varchar(2)) EXECUTE E' psql -d db1 -U postgres -q -Af /data/test/test.sql'on MASTER FORMAT 'text' (delimiter '~');
CREATE EXTERNAL TABLE

The test.sql is just a select from another table in another database:

select * from test; 

The following errors popped up when running the query against the external table test 1:

select * from test1 limit 5;
ERROR: missing data for column "col2"
CONTEXT: External table test1, line 1 of execute:psql -d DB1 -U postgres -q -af /data/test/test.sql: "set quiet on"
ERROR: external table sdmtd_fund command ended with SHELL TERMINATED by signal SIGPIPE (13)
DETAIL: Command: execute:psql -d DB1 -U postgres -q -af /data/test/test.sql

Environment

Cause

When running the SQL script directly, it returns the following output:

$psql -d DB1 -U postgres -q -Af /data/test/test.sql
My_Db1_test_col1|My_Db1__test_col1
06824|CE
05839|CT
30207|B
30314|C
00313|R6
79141|A
00325|A
00923|B
00811|R
00739|1
00481|A
00624|AV

You can see that the output contains the column name which doesn't match the column data type defined in the external table.

 

Resolution

Modify the DDL by adding -t like below to allow the query of external tables to output only the tuples:

CREATE EXTERNAL WEB TABLE test1 ( col1 varchar(5),col2 varchar(2)) EXECUTE E' psql -d db1 -U postgres -t -q -Af /data/test/test.sql'on MASTER FORMAT 'text' (delimiter '|');
CREATE EXTERNAL TABLE

Note: In the above example, the delimiter was also changed to "|" to reflect the correct format.