Pivotal Knowledge Base


Error “Matches 0 Files” when Querying an External Table that Uses PXF


 Product  Version
 Pivotal HDB  1.x, 2.x


When querying an external table that uses PXF, the following error message is seen:

ERROR: remote component error (500) from '': Problem accessing /gpdb/v10/Fragmenter/getFragments. Reason: Server Error Caused by: org.apache.hadoop.mapred.InvalidInputException: Input Pattern hdfs://hawq20:51200/pxf_data/file* matches 0 files (libchurl.c:753)


There are no files matching the wildcard location given in the LOCATION clause of the external table. For example, in the above error, no files matching this wildcard location were found: hdfs://hawq20:51200/pxf_data/file*


1. Obtain the "external location" used in the external table. In

In psql, run \d <tablename>:

gpadmin=# \d otp201001_3
External table "public.otp201001_3"
Column | Type | Modifiers
year | text |
quarter | text |
month | text |
dayofmonth | text |
arrivaldelaygroups | text |
arrtimeblk | text |
Type: readable
Encoding: UTF8
Format type: csv
Format options: delimiter ',' null '' escape '"' quote '"'
External location: pxf://hawq20:51200/pxf_data/file*?Fragmenter=org.apache.hawq.pxf.plugins.hdfs.HdfsDataFragmenter&Accessor=org.apache.hawq.pxf.plugins.hdfs.LineBreakAccessor&Resolver=org.apache.hawq.pxf.plugins.hdfs.StringPassResolver gpadmin=#

2. Login to the HDFS NameNode or the HDFS client.

3. Run the command hdfs dfs -ls <LOCATION> where LOCATION is the path found in step 1.

[root@hawq20 ~]# hdfs dfs -ls /pxf_data/file*
ls: `/pxf_data/file*': No such file or directory
[root@hawq20 ~]#

4. As per the error, the wildcard file location should return nothing, the same way it didn't in the above example. 

5. Correct and retry the command in step three until the files are listed correctly. 

For example: 

[root@hawq20 ~]# hdfs dfs -ls /pxf_data/otp*
-rw-r--r-- 3 gpadmin gpadmin 101891817 2016-09-22 15:13 /pxf_data/otp201001_3.csv
-rw-r--r-- 3 gpadmin gpadmin 1324587874 2016-09-23 02:21 /pxf_data/otp201001_large.csv
[root@hawq20 ~]#

6. Drop the external table, using "DROP EXTERNAL TABLE"

7. Recreate the external table using the "CREATE EXTERNAL TABLE" command with the correct location found in step 6.




Powered by Zendesk