EXTERNAL_TABLE_DETAILS

Returns the amount of disk storage used by the source files backing external tables in the database.

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.

This table reports only data that the current user can read. To include all the data backing external tables, either query this table as a user that has access to all HDFS data or use a session delegation token that grants this access. For more information about using delegation tokens, see Accessing kerberized HDFS data.

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 INTEGER A unique numeric ID assigned by the Vertica catalog that identifies the table.
TABLE_NAME VARCHAR 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.