1.23 COPY : Skipping Rows,bytes while loading data.

COPY : Skipping Rows,bytes while loading data.

The COPY statement has two options to skip input data.

The SKIP BYTES option is only for fixed-width data loads:

SKIP BYTES total	Skips the total number (integer) of bytes from the input data.
SKIP records	Skips the number (integer) of records you specify. SKIP works with both delimited and fixed width files.

Following examples shows how to skip 1st record in the file.

<> /home/sukul1 $ cat employeedet3.dat

rahul,dravID       ***2010-01-01100000

SAURAV,GANGULY     ***2010-10-01200000

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

sukul1-> (

sukul1(> NAME,

sukul1(> AGE NULL '***',

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/employeedet3.dat'

sukul1-> FIXEDWIDTH COLSIZES(19,3,10,6)

sukul1-> SKIP 1

sukul1-> DIRECT;

 Rows Loaded

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

           1

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

        NAME         | AGE | JOINING_YEAR | SALARY

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

 SAURAV,GANGULY      |     |         2010 | 200000

(1 row)

1.21 COPY : Loading FIXED width files.

COPY : Loading FIXED width files.

To load fixed width files we should use the FIXEDWIDTH parser option.

We must specify the COLSIZES option values to specify the number of bytes for each column.

The last record in a fixed-width data file must include a record terminator to determine the end of the load data.

Following options cannot be used with fixed width files.

• DELIMITER
• ENCLOSED BY

• ESCAPE AS

• TRAILING NULLCOLS

(Note that we can use NULL metadata option with FIXED WIDTH).

Following example shows a fixed width file and how this gets loaded to the table.

Note that 1st column is of width 19 bytes, 2nd of 3 bytes,3rd is 10 bytes and last one is 6 bytes.

<> /home/sukul1 $ cat employeedet3.dat

RAHUL,DRAVID       45 2010-01-01100000

SAURAV,GANGULY     1012010-10-01200000

<> /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/employeedet3.dat'

sukul1-> FIXEDWIDTH COLSIZES(19,3,10,6)

sukul1-> DIRECT;

 Rows Loaded

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

           2

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

        NAME         | AGE | JOINING_YEAR | SALARY

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

 RAHUL,DRAVID        |  45 |         2010 | 100000

 SAURAV,GANGULY      | 101 |         2010 | 200000

(2 rows)

Note that if we dont specify correct lengths, the records will be rejected.

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.18 COPY : Loading records with lesser number of data columns. Using TRAILING NULLCOLS option

COPY : Loading records with lesser number of data columns. Using TRAILING NULLCOLS option

Following file shows that the 2nd record in the file does not enough number of columns.

<> /home/sukul1 $ cat employeedet1.dat

SUKUL||2010-12-20|10000

RUTUJA|25

Following shows the error we get when we run the copy

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-> DELIMITER '|'

sukul1-> ABORT ON ERROR

sukul1-> DIRECT;

ERROR 2035:  COPY: Input record 2 has been rejected (Too few columns found)

sukul1=>

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

 NAME | AGE | JOINING_YEAR | SALARY

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

(0 rows)

In above scenario we had chosen the "AbORT ON ERROR" option. That caused the entire statement to be rolled back and even the correct row was rolled back.

In Such cases we could use the TRAILING NULLCOLS option to load NULLS for the missing columns.

Following COPy statement uses the TRAILING NULLCOLS option.

Note that it does not reject the row and loads NULL's for missing columns.

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-> DELIMITER '|'

sukul1-> TRAILING NULLCOLS

sukul1-> ABORT ON ERROR

sukul1-> DIRECT;

 Rows Loaded

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

           2

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

  NAME  | AGE | JOINING_YEAR | SALARY

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

 RUTUJA |  25 |              |

 SUKUL  |     |         2010 |  10000

(2 rows)

Caution: This option cannot be used if the columns are defined as NOT NULL.

COPY would try to insert NULL's in NOT null columns and reject the row.