Simulating DML Operations on External Tables

[Deleted User]

Jim Knicely authored this tip.

An external table lets you 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 you can run shell commands from within vsql, you 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 to 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!