PXF external table query fails with missing or extra columns in GPDB

Products

• VMware Tanzu Greenplum

Issue/Introduction

When querying an external table using PXF to read HDFS CSV files, Greenplum Database (GPDB) returns a "missing columns" or "extra columns" error. This occurs when the source files are compressed CSVs containing varying numbers of columns (e.g., some rows contain 2, 3, or 4 columns). Because PXF reads files line-by-line and passes data directly to the database, encountering data lines that do not strictly match the declared table schema triggers an immediate failure.

Cause

GPDB does not support an "ignore extra columns" feature. If the external table is defined with fewer columns than what exists in the widest data file, GPDB errors out upon encountering the extra fields.

Resolution

To resolve this issue, use the FILL MISSING FIELDS format parameter and declare the external table to match the widest file.

 

• Identify the maximum number of columns present across all source data files.

• Create the external table using the maximum number of columns (for example, 4 columns).

• Include the FILL MISSING FIELDS clause in the FORMAT definition. This prevents the query from failing when reading lines that are missing the trailing columns.

 

Example:

Use the following DDL format:

CREATE EXTERNAL TABLE ext_sample_data_202601 (col1 text, col2 text, col3 text, col4 text)
LOCATION ('pxf:///hdfs/data/sample_data_202601.gzip?PROFILE=hdfs:csv')
FORMAT 'CSV' (DELIMITER ',' FILL MISSING FIELDS);

- Query the table by explicitly selecting only the necessary columns.

Example Query Format:

SELECT col1, col2, col3 FROM ext_sample_data_202601;