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
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)
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
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)
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)
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)