1.20 COPY: Specifying a Custom End of Record String (RECORD TERMINATOR)

COPY: Specifying a Custom End of Record String (RECORD TERMINATOR)

To specify the literal character string that indicates the end of a data file record, use the RECORD TERMINATOR parameter

If you do not specify a value, then HP Vertica attempts to determine the correct line ending, accepting either just

• a linefeed (E'\n') common on UNIX systems, or

• a carriage return and linefeed (E'\r\n') common on Windows platforms.

To specify the RECORD TERMINATOR as non-printing characters, use either the extended string syntax or Unicode string literals. The following table lists some common record terminator characters.

Extended String Syntax	Unicode Literal String	Description	ASCII Decimal
E'\b'	U&'\0008'	Backspace	8
E'\t'	U&'\0009'	Horizontal tab	9
E'\n'	U&'\000a'	Linefeed	10
E'\f'	U&'\000c'	Formfeed	12
E'\r'	U&'\000d'	Carriage return	13
E'\\'	U&'\005c'	Backslash	92

The record terminator cannot be the same as DELIMITER, NULL, ESCAPE,or ENCLOSED BY.

Following shows an example of loading a file that has records separated by Tab.

<> /home/sukul1 $ cat employeedet1.dat

"SHANE,WARNE",25,2015-01-01,133010      SACHIN,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-> RECORD TERMINATOR E'\t'

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      |  35 |         2014 |   1234

(3 rows)

(END)

Note that even the last record should also be terminated by the tab (record terminator character).

Without that the last record will not load.