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. |