
An external table lets us query a text file that sits outside of the database as if it were a table internal to the database.
Example:
dbadmin=> CREATE EXTERNAL TABLE public.mood (pk INT, mood VARCHAR(100)) AS COPY FROM '/home/dbadmin/mood.txt';
CREATE TABLE
dbadmin=> SELECT * FROM public.mood;
pk | mood
----+-------------
1 | Happy
2 | Sad
3 | Indifferent
4 | Surprised
(4 rows)
Unfortunately, there is a limitation on external tables in that normal DML statements (INSERT, UPDATE and DELETE) cannot be used on them.
dbadmin=> INSERT INTO public.mood VALUES (5, 'Frisky');
ERROR 5263: Unsupported access to external table
dbadmin=> UPDATE public.mood SET mood = 'Ecstatic' WHERE mood = 'Sad';
ERROR 5263: Unsupported access to external table
dbadmin=> DELETE FROM public.mood WHERE mood = 'Indifferent';
ERROR 5263: Unsupported access to external table
Although a bit quirky, there is a workaround!
Since we can run shell commands from within vsql, we can modify the underlying mood.txt file manually to simulate the DML commands listed above. Changes made to the text file are immediately evident in the external table!
Here are several examples showing how we can perform simple DML-like statements on external tables in Vertica:
INSERT:
dbadmin=> \! echo "5|Frisky" >> /home/dbadmin/mood.txt
dbadmin=> SELECT * FROM public.mood;
pk | mood
----+-------------
1 | Happy
2 | Sad
3 | Indifferent
4 | Surprised
5 | Frisky
(5 rows)
UPDATE:
dbadmin=> \! sed -i 's/2|Sad/2|Ecstatic/g' /home/dbadmin/mood.txt
dbadmin=> SELECT * FROM public.mood;
pk | mood
----+-------------
1 | Happy
2 | Ecstatic
3 | Indifferent
4 | Surprised
5 | Frisky
(5 rows)
DELETE:
dbadmin=> \! sed -i '/3|Indifferent/d' /home/dbadmin/mood.txt
dbadmin=> SELECT * FROM public.mood;
pk | mood
----+-----------
1 | Happy
2 | Ecstatic
4 | Surprised
5 | Frisky
(4 rows)
Have Fun!