Copy FILLER examples

[adrian.oprea]

Can somebody place some copy FILLER and how to ignore some columns examples .

I am having some difficulties understanding it !!

thx

[JimKnicely]

Hi,

There are a few example topics in the forums:

viewtopic.php?f=49&t=173&p=350&hilit=filler#p350
viewtopic.php?f=18&t=236&p=616&hilit=filler#p616

Here is another quick example.

Say I have a data file with two fields, one is a date and the other is a text string. I want to load the date but ignore the text.

Here is my file:

Code: Select all

vtest$ cat test.txt
2012-19-09|Ignore Me

Notice that the date is in the format YYYY-DD-MM. I need to transform that into a format that Vertica needs for the DATE data type, i.e. YYYY-MM-DD. I can do that with the FILLER option of the COPY command.

Code: Select all

vtest$ vsql
SET
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h for help with SQL commands
       \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> create table test1 (cdate date);
CREATE TABLE
dbadmin=> copy test1 (cdate_f FILLER date FORMAT 'YYYY-DD-MM', ignore FILLER varchar(10), cdate AS cdate_f) from '/usr/home/dbadmin/test.txt';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from test1;
   cdate
------------
 2012-09-19
(1 row)

Explanation of parts of the COPY command:

cdate_f ==> This is a temporary place holder for my formatted data
ignore ==> "ignore" is just a made up variable name
cdate AS cdate_f ==> Here I move my formatted date from cdate_f into my table column cdate

I hope this helps you a little