EXTERNAL_TABLE_DETAILS

Returns the amount of disk storage used by the source files backing external tables in the database. The information in this system table is useful in determining Hadoop license compliance.

When computing the size of an external table, Vertica counts all data found in the location specified by the COPY FROM clause. If you have a directory that contains ORC and delimited files, for example, and you define your external table with "COPY FROM *" instead of "COPY FROM *.orc", this table includes the size of the delimited files. (You would probably also encounter errors when querying that external table.) When you query this system table Vertica does not validate your table definition; it just uses the path to find files to report.

Restrict your queries to filter by schema, table, or format to avoid expensive queries. Vertica calculates the values in this table at query time, so "SELECT *" accesses every input file contributing to every external table.

Predicates in queries may use only the TABLE_SCHEMA, TABLE_NAME, and SOURCE_FORMAT columns. Values are case-sensitive.

This table includes TEMP external tables.

Column Name Data Type Description
SCHEMA_OID

INTEGER

The unique identification number of the schema in which the external table resides.
TABLE_SCHEMA

VARCHAR

The name of the schema in which the external table resides.
TABLE_OID

VARCHAR

A unique numeric ID assigned by the Vertica catalog that identifies the table.
TABLE_NAME

INTEGER

The table name.
SOURCE_FORMAT

VARCHAR

The data format the source file used, one of ORC, PARQUET, DELIMITED, USER DEFINED, or NULL if another format.
TOTAL_FILE_COUNT

INTEGER

The number of files used to store this table's data, expanding globs and partitions.
TOTAL_FILE_SIZE_BYTES

INTEGER

Total number of bytes used by all of this table's data files.
SOURCE_STATEMENT

VARCHAR

The load statement used to copy data from the source files.
FILE_ACCESS_ERROR

VARCHAR

The access error returned during the source statement. NULL, if there was no access error during the source statement.