The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Viewing External Table Definitions

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/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ExternalTables/CreatingExternalTables.htm

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

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.