In Vertica we can make use of external tables by using CREATE EXTERNAL TABLE AS COPY syntax.
So, what is an External Table ?
basically is a flat file on your operational system that can be queried from inside the database as if it was a database table.
there is no ROS or WOS concepts when working with External Tables.
External tables are useful and is a very flexible mechanism to feed data to our systems.
Point of strength while working with External Tables
no internal work is done to maintain them(refreshes,statistics,..
easy to build
take advantage of the server managed parallelism
no delete vectors are created
no extra work for the TM(tuple mover)
can read from compressed files(bzip2)
can combine multiple files into one table
data transformation during the load time
Syntax used to create External Tables in Vertica
To create an external table you need to have superuser privileges(required in order to access the tables via select statement) and also have READ access on the storage location.
Basic Syntax :
For the complete syntax follow this link to Vertica official documentation.
Let's see what is not supported while creating External Tables:
Examples of create External Tables in Vertica
Create external table using a csv file.
Create external table using multiple csv file.
you can use multiple files located in the same location '/tmp/data/*'
Create external table using bzip2 compressed files.
What type of actions are not permitted when using External Tables
Possible actions on External Tables
Add PRIMARY KEY
joins are possible between external tables and also between and external table and regular table.
you will always have the external table as the source table.
views can be create on a external table
Alter External Table name
Thing i don't like about External Table in Vertica
The Create table statement shows no error and the table is create in the catalog even if there is no file present on the file system to feed the data to it. You only get the error when you try to query the table.