Viewing External Table Definitions: Quick Tip

Posted February 1, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

An alternative to importing data into Vertica is to query it in place. For that we use external tables. Once an external table is created you can view its definition via the TABLES system table.

Example: dbadmin=> \! cat /home/dbadmin/ext.txt 1|TEST1 2|TEST2 dbadmin=> CREATE EXTERNAL TABLE ext (c1 INT, c2 VARCHAR(10)) AS COPY FROM '/home/dbadmin/ext.txt'; CREATE TABLE dbadmin=> SELECT * FROM ext; c1 | c2 ----+------- 1 | TEST1 2 | TEST2 (2 rows) dbadmin=> SELECT table_name, table_definition dbadmin-> FROM tables dbadmin-> WHERE table_name = 'ext'; table_name | table_definition ------------+----------------------------------- ext | COPY FROM '/home/dbadmin/ext.txt' (1 row) Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/CreatingExternalTables.htm

https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/Intro.htm

Have fun!