Vertica Quick Tip: Simulating DML Operations on External Tables

This blog post was authored by Jim Knicely.

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!