Uncategorized

Bit 1 : Vertica External Tables

Vertica External Tables

Starting with the first bit of information that is related to Vertica and to be very specific , related to External tables in Vertica.

I am sure, you were not aware of this !!!

If you are trying to create a Vertica LOCAL / GLOBAL / PERMANENT table using the base structure of Vertica External tables, it does not allow you to create such tables and gives Error.

This feature is as designed feature of Vertica and it does not allow to replicate an External table using LIKE operator.

The probable reason behind this can be, the external tables are logical schema built across external files and there are no projections created for it, so it may not be feasible to replicate the structure of it.

Sample Code

dbadmin=> CREATE EXTERNAL TABLE IF NOT EXISTS TEST_EXT (id1 int, id2 int, id3 int, id4 int, id5 int)
dbadmin-> AS COPY from ‘/home/dbadmin/test/sample1.txt’ DELIMITER ‘,’;
CREATE TABLE
dbadmin=> select * from TEST_EXT;
id1 | id2 | id3 | id4 | id5
—–+—–+—–+—–+—–
1 | 2 | 3 | 4 | 5
2 | 3 | 4 | 5 | 6
3 | 4 | 5 | 6 | 7
4 | 5 | 6 | 7 | 8
5 | 6 | 7 | 8 | 9
(5 rows)

dbadmin=> create table TEST_PERM_TABLE like TEST_EXT including projections;
ROLLBACK 5660: Source table can not be temp, virtual, system, or external
dbadmin=> create table TEST_PERM_TABLE like TEST_EXT ;
ROLLBACK 5660: Source table can not be temp, virtual, system, or external

We were not able to create the table using LIKE operator, but there is another way to do it.

dbadmin=> create table TEST_PERM_TABLE as select * from TEST_EXT limit 0 ;
CREATE TABLE

Go ahead and try this in your environment.

Happy Exploring !!!

 

.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s