Query using PXF fails with ERROR: Missing FRAGMENTER option in the pxf uri: .......

Products

• Services Suite

Issue/Introduction

Symptoms:
"Missing FRAGMENTER option" error would occur sometimes for querying PXF-based external table in HAWQ, as shown below.

gpadmin=# select * from retail_demo.categories_dim_pxf;
 ERROR: Missing FRAGMENTER option in the pxf uri: pxf://hawq-mdw:50070/retail_demo/categories_dim/categories_dim.tsv.gz?
DETAIL: Fragmenter=HdfsDataFragmenter&Accessor=TextFileAccessor&Resolver=TextResolver

Environment

Cause

There is no check for syntax validation while creating an external table using PXF. In the below example, there is a new line after ?, due to which the error is observed.

CREATE EXTERNAL TABLE test_data2 ( i text )
LOCATION ('pxf://hdm2:50070/user/rjain04/test_data2.txt?
Fragmenter=HdfsDataFragmenter&Accessor=TextFileAccessor&Resolver=TextResolver')
FORMAT 'TEXT' (DELIMITER = E'\t');

Resolution

Fix

Drop the external table and recreate the table with correct syntax having LOCATION keyword with it's argument in single line.

CREATE EXTERNAL TABLE test_data2 ( i text )
LOCATION ('pxf://hdm2:50070/user/rjain04/test_data2.txt?Fragmenter=HdfsDataFragmenter&Accessor=TextFileAccessor&Resolver=TextResolver') FORMAT 'TEXT' (DELIMITER = E'\t');

The issue is fixed since HAWQ 1.1.4, in which "ERROR:  Invalid URI pxf://" will be observed with the invalid "CREATE EXTERNAL TABLE" statement.

gpadmin=# CREATE EXTERNAL TABLE test_data2 ( i text )
gpadmin-# LOCATION ('pxf://hdm2:50070/user/rjain04/test_data2.txt?
gpadmin'# Fragmenter=HdfsDataFragmenter&Accessor=TextFileAccessor&Resolver=TextResolver')
gpadmin-# FORMAT 'TEXT' (DELIMITER = E'\t');
ERROR: Invalid URI pxf://hdm2:50070/user/rjain04/test_data2.txt?
DETAIL: Fragmenter=HdfsDataFragmenter&Accessor=TextFileAccessor&Resolver=TextResolver: PROFILE or FRAGMENTER option(s) missing