Simulating DML Operations on External Tables

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Simulating DML Operations on External Tables

Post by JimKnicely » Wed Apr 10, 2013 1:09 pm

Hi,

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:

Say I have the text file /usr/home/dbadmin/mood.txt containing the following data:

Code: Select all

bash-3.2$ cat /usr/home/dbadmin/mood.txt
1|Happy
2|Sad
3|Indifferent
4|Surprised
I want to view this data in Vertica as if it were a table. To do so, I could create an external table like this:

Code: Select all

dbadmin=> CREATE EXTERNAL TABLE public.mood
dbadmin->   (pk INT, mood VARCHAR(100))
dbadmin->   AS COPY FROM '/usr/home/dbadmin/mood.txt' ON v_intersect_node0001;
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.

Code: Select all

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 test 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:

Code: Select all

dbadmin=> \! echo "5|Frisky" >> /usr/home/dbadmin/mood.txt

dbadmin=> select * from public.mood;
pk |    mood
----+-------------
  1 | Happy
  2 | Sad
  3 | Indifferent
  4 | Surprised
  5 | Frisky
(5 rows)
UPDATE:

Code: Select all

dbadmin=> \! sed -i 's/2|Sad/2|Ecstatic/g' /usr/home/dbadmin/mood.txt

dbadmin=> SELECT * FROM public.mood;
pk |    mood
----+-------------
  1 | Happy
  2 | Ecstatic
  3 | Indifferent
  4 | Surprised
  5 | Frisky
(5 rows)
DELETE:

Code: Select all

dbadmin=> \! sed -i '/3|Indifferent/d' /usr/home/dbadmin/mood.txt

dbadmin=> SELECT * FROM public.mood;
pk |   mood
----+-----------
  1 | Happy
  2 | Ecstatic
  4 | Surprised
  5 | Frisky
(4 rows)
Have fun!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”