1.19 COPY : Using ENCLOSED BY clause

COPY : Using the ENCLOSED BY

If the input data contains the Delimiter characters, then one way to escape them is to use the escape character.

Another way is to enclose text in characters specified by the ENCLOSED BY clause.

Delimiter characters inside the enclosed characters are not treated as delimiters.

You can use any ASCII value in the range E'\001' to E'\177' inclusive (any ASCII character except NULL:E'\000') for the ENCLOSED BY value. Using double quotation marks (") is the most commonly used quotation character. 

For instance, the following parameter specifies that input data to the COPY statement is enclosed within double quotes:

ENCLOSED BY '"'

With the following input (using the default DELIMITER (|) character), specifying:

"vertica | value"

Results in:

• Column 1 containing "vertica
• Column 2 containing value"

However if we had specified ENCLOSED BY '"' then the | would not have been interpreted as delimiter.

And Column1 would have got the entire value .i.e vertica | value

Enclosed by basically means that delimiters within the enclosed by characters are not interpreted as delimiters.

Following example shows that the file has comma as the delimiter. But the 1st field as the character comma in the tezt itself.

So we have enclosed it in double quotes.

<> /home/sukul1 $ cat employeedet1.dat

"SHANE,WARNE",25,2015-01-01,133010

"SACHIN,TENDULKAR",35,2014-02-02,1234

"BRFET,LEE",43,2014-02-02,1234

<> /home/sukul1 $

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

sukul1-> (

sukul1(> NAME,

sukul1(> AGE,

sukul1(> JOINING_DATE FILLER VARCHAR(10),

sukul1(> JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

sukul1(> SALARY

sukul1(> )

sukul1-> FROM LOCAL '/home/sukul1/employeedet1.dat'

sukul1-> ENCLOSED BY '"'

sukul1-> DELIMITER ','

sukul1-> DIRECT;

 Rows Loaded

-------------

           3

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

       NAME       | AGE | JOINING_YEAR | SALARY

------------------+-----+--------------+--------

 BRFET,LEE        |  43 |         2014 |   1234

 SHANE,WARNE      |  25 |         2015 | 133010

 SACHIN,TENDULKAR |  35 |         2014 |   1234

(3 rows)

However note that not all rows should have values enclosed in double quotes.

In below example the 1st column in 2nd row does not have the delimiter character in it.

So its okay to not have it enclosed in double quotes.

<> /home/sukul1 $ vi employeedet1.dat

"employeedet1.dat" 3 lines, 104 characters

"SHANE,WARNE",25,2015-01-01,133010

SACHIN,35,2014-02-02,1234

"BRFET,LEE",43,2014-02-02,1234

Note that its possible to specify the ENCLOSED BY character only for a specific column.

This will allow us to enclose a specific column in double quotes and still allow literal double quotes in other columns.

The following example uses double quotes to enclose a single column

=> COPY Retail.Dim (Dno, Dname ENCLOSED BY '"', Dstore) FROM '/home/dbadmin/dim3.txt'
   DELIMITER ','
   EXCEPTIONS '/home/dbadmin/exp.txt';

1.14 COPY : Specifying Load Metadata

COPY : Specifying Load Metadata

In addition to choosing a parser option, COPY supports other options to determine how to handle raw data.

These options are considered load metadata, and we can specify metadata options in different parts of the COPY statement.

Following are the places where we can specify the load metadata:

1. As a Column or Expression.

2. As a Column Option.

3. As a FROM level Option.

Following table shows Which option can be specified at what level.

Metadata Option	As a Column or Expression Option	As a  COLUMN OPTION	As a FROM  Level Option
DELIMITER	Y	Y	Y
ENCLOSED BY	Y	Y	Y
ESCAPE AS	Y	Y	Y
NULL	Y	Y	Y
TRIM	Y		Y
RECORD TERMINATOR			Y
SKIP			Y
SKIP BYTES			Y (Fixed-width only)
TRAILING NULLCOLS			Y

Notice that the NULL, ESCAPE,ENCLOSED and DELIMITER (NEED) metadata options can be specified at all the 3 levels.

The following precedence rules apply to all data loads:

• All column-level parameters override statement-level parameters.

• COPY uses the statement-level parameter if you do not specify a column-level parameter.

• COPY uses the default metadata values for the DELIMITER, ENCLOSED BY, ESCAPE AS, and NULL options if you do not specify them at either the statement- or column-level.