Working with External Tables

After creating external tables, you access them as any other table. However, you cannot perform UPDATE, INSERT, or DELETE operations on external tables.

Managing Resources for External Tables

External tables require minimal additional resources. When you use a select query for an external table, Vertica uses a small amount of memory when reading external table data, since the table contents are not part of your database and are parsed each time the external table is used.

Vertica Does Not Back Up External Tables

Since the data in external tables is managed outside of Vertica, only the external table definitions, not the data files, are included in database backups. Arrange for a separate backup process for your external table data.

Using Sequences and Identity Columns in External Tables

The COPY statement definition for external tables can include identity columns and sequences. Whenever a select statement queries the external table, sequences and identity columns are re-evaluated. This results in changing the external table column values, even if the underlying external table data remains the same.

Viewing External Table Definitions

When you create an external table, Vertica stores the COPY definition statement in the table_definition column of the v_catalog.tables system table.

  1. To list all tables, use a select * query, as shown:

    select * from v_catalog.tables where table_definition <> '';
  2. Use a query such as the following to list the external table definitions (table_definition):

    select table_name, table_definition from v_catalog.tables;
     table_name |                                table_definition                                                   
    ------------+----------------------------------------------------------------------
     t1         | COPY            FROM 'TMPDIR/external_table.dat'  DELIMITER ','
     t1_copy    | COPY            FROM 'TMPDIR/external_table.dat'  DELIMITER ','
     t2         | COPY FROM 'TMPDIR/external_table2.dat' DELIMITER ','
    (3 rows)
    

External Table DML Support

Following are examples of supported queries, and others that are not:

Supported Unsupported
SELECT * FROM external_table;
DELETE FROM external_table WHERE x = 5;
SELECT * FROM external_table where col1=4; 
INSERT INTO external_table SELECT * FROM ext;
DELETE FROM internal_table WHERE id IN 
  (SELECT x FROM external_table); 

 

INSERT INTO internal_table
  SELECT * FROM external_table; 
SELECT * FROM external_table FOR UPDATE;

Using External Table Values

Following is a basic example of how you could use the values of an external table.

  1. Create and display the contents of a file with some integer values:

    [dbadmin@localhost ~]$ more ext.dat1
    2
    3
    4
    5
    6
    7
    8
    10
    11
    12
    
  2. Create an external table pointing at ext.dat:

    VMart=> create external table ext (x integer) as copy from '/home/dbadmin/ext.dat';
    CREATE TABLE
    
  3. Select the table contents:

    VMart=> select * from ext;
      x  
    ----
      1
      2
      3
      4
      5
      6
      7
      8
     10
     11
     12
    (11 rows)
    
  4. Perform evaluation on some external table contents:

    VMart=> select ext.x, ext.x + ext.x as double_x from ext where x > 5;
     x  | double_x 
    ----+----------
      6 |       12
      7 |       14
      8 |       16
     10 |       20
     11 |       22
     12 |       24
    (6 rows)
    
  5. Create a second table (second), also with integer values:

    VMart=> create table second (y integer);
    CREATE TABLE
    
  6. Populate the table with some values:

    VMart=> copy second from stdin;Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> 1
    >> 1
    >> 3
    >> 4
    >> 5
    >> \.
    
  7. Join the external table (ext) with the table created in Vertica, called second:

    VMart=> select * from ext join second on x=y;
     x | y 
    ---+---
     1 | 1
     1 | 1
     3 | 3
     4 | 4
     5 | 5
    (5 rows)