External Tables

[JimKnicely]

One of my favorite features of the Oracle database is the External Table.

Just like in Oracle, an External Table in Vertica lets you query a text file that sits outside of the database as if I were a table internal to the database!

Example:

Say I have a text file named test.txt that contains a list of text messages exchanged between two friends. I can list the files contents with the Linux cat command:

Code: Select all

Vertica01$ cat /usr/home/dbadmin/test.txt
1|It is hot outside!
2|Well, it is summer...
3|Yeah, I know. I miss winter on days like these.
4|Let's see if you say that during the next blizzard!

In the Vertica database I can create an external table which references the text file:

Code: Select all

dbadmin=> create external table text (pk int, msg varchar(100)) as copy from '/usr/home/dbadmin/test.txt';
CREATE TABLE

Finally, I can query the new table:

Code: Select all

dbadmin=> select * from text;
pk |                         msg
----+-----------------------------------------------------
  1 | It is hot outside!
  2 | Well, it is summer...
  3 | Yeah, I know. I miss winter on days like these.
  4 | Let's see if you say that during the next blizzard!
(4 rows)

dbadmin=> select * from text where pk = 4;
pk |                         msg
----+-----------------------------------------------------
  4 | Let's see if you say that during the next blizzard!
(1 row)