Greenplum: Find associated error table of any external table

Question

is there any way to find out the list of all error tables associated with each external table.

Actual Requirement: I am using External tables in Greenplum and data coming from source in form of files,data ingestion to Greenplum via external tables. and I want to report all the rejected rows to source system

Regards, Gurupreet

Each external table does not require its own error table. All the external tables in a database can share a single error table. Which may make your problem go away to start with. — Bell, Mar 13 '16 at 14:22

score 1 · Answer 1 · answered Feb 24 '16 at 16:28

1

http://gpdb.docs.pivotal.io/4340/admin_guide/load/topics/g-viewing-bad-rows-in-the-error-table-or-error-log.html

You basically just use the built-in function gp_read_error_log() and pass in the external table name to get the errors associated with the files. There is an example in the above link too.

answered Feb 24 '16 at 16:28

Jon Roberts

2,068
1
9
11

Thanks for your reply, Above solution will work if there is any error logged into error table, but is there any way to find error table name by passing external table – Gurupreet Singh Bhatia Mar 10 '16 at 07:18

score 1 · Answer 2 · answered Mar 13 '16 at 14:22

The field fmterrtbl of pg_exttable contains the oid of the error table for any external table. So the query to find the error table for all external tables in the database is:

SELECT
    external_namespace.nspname AS external_schema, external_class.relname AS external_table,
    error_namespace.nspname AS error_schema, error_class.relname AS error_table
  FROM pg_exttable AS external_tables
    INNER JOIN pg_class AS external_class ON external_class.oid = external_tables.reloid
    INNER JOIN pg_namespace AS external_namespace ON external_namespace.oid = external_class.relnamespace
    LEFT JOIN (
      pg_class AS error_class 
      INNER JOIN pg_namespace AS error_namespace ON error_namespace.oid = error_class.relnamespace
    ) ON error_class.oid = external_tables.fmterrtbl

the error_schema and error_table fields will be NULL for external tables with no error tables.

Greenplum: Find associated error table of any external table

2 Answers2