CODEWITHSUNDEEP
greenplumexternal-tables
Gurupreet Singh Bhatia
Gurupreet Singh Bhatia

Reputation: 728

Greenplum: Find associated error table of any external table

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

Upvotes: 0

Views: 652

Answers (2)

Bell
Bell

Reputation: 18685

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.

Upvotes: 1

Jon Roberts
Jon Roberts

Reputation: 2106

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.

Upvotes: 1

Related Questions