Use COPY FROM LOCAL to Load Data
The COPY FROM LOCAL command allows you to load CSV files from the client to a Vertica table. The table must exist when COPY FROM LOCAL is being executed.
For more information about the COPY FROM LOCAL command in Vertica, see the online Vertica documentation.
To copy data from Amazon S3 buckets, see Use COPY FROM S3 to Load Data.
Load Simple CSV Files
To load data from simple CSV files, use the following syntax:
COPY <table_name> FROM LOCAL '<csv_file>' [,'<another_csv_file>'...];
Load Compressed CSV Files
To load data from compressed CSV files, add the BZIP or GZIP keyword to the command:
COPY <table_name> FROM LOCAL '<csv_file>' BZIP|GZIP;
When loading multiple files, specify the compression type for each loaded file. The compression type must be the same for all loaded files within one command.
COPY <table_name> FROM LOCAL '<csv_file_1>' BZIP, '<csv_file_2>' BZIP;
Using a Data Warehouse Parser with COPY FROM LOCAL
Data Warehouse supports the following parsers:
- Native parser
- GdcCsvParser
For more information about the parsers, see Choose a Data Warehouse Parser.
The default is the native parser (no parser specified in COPY).
To use GdcCsvParser, add the PARSER clause to the command:
COPY <table_name> FROM LOCAL '<csv_file>' [WITH] PARSER GDCCSVPARSER();
Note the empty round brackets following the GDCCSVPARSER identifier. The brackets must be present even when no parameters are passed to GdcCsvParser.
Parser Parameters
You can pass the following parameters to the parser:
| Parameter | Description |
|---|---|
| ABORT ON ERROR | Aborts the load process when an error occurs |
| DELIMITER [AS] '<character>' | Specifies the field or column separator character |
| DIRECT | Requests direct load to Vertica ROS containers |
| ENCLOSED BY '<character>' | Specifies the enclosing character (for example, double quotes) |
| ESCAPE [AS] '<character>' | Specifies the escape character |
| EXCEPTIONS '<exceptions_file>' | Defines the EXCEPTIONS file to log exceptions occurred during a load process |
| NULL [AS] '<string>' | Translates the defined constant string to NULL in Vertica |
| RECORD TERMINATOR '<string>' | Defines a custom record terminator character or sequence |
| REJECTED DATA '<rejected_data_file>' | Stores rejected records as a file |
| REJECTED DATA AS TABLE <table_name> | Stores rejected records as a database table |
| REJECTMAX N | Raises an error if N records were rejected |
We recommend that you apply the best practices when using COPY FROM LOCAL.
Examples:
Passing the DELIMITER [AS] ';' parameter to the native parser:
COPY users FROM LOCAL 'users_all.csv' DELIMITER [AS] ';';
Passing the DELIMITER [AS] ';' parameter to GdcCsvParser (notice that the parser specification must precede any specified parameter):
COPY users FROM LOCAL 'users_all.csv' [WITH] PARSER GDCCSVPARSER() DELIMITER [AS] ';';
Best Practices
Run COPY FROM LOCAL with the parameters that allow you to detect what went wrong in case of a failure.
- If you allow a certain margin of error (for example, you tolerate up to N rows not being loaded to the Vertica table), run COPY FROM LOCAL with the REJECTMAX N, REJECTED DATA, and EXCEPTIONS parameters. Set N to the number of the rejected rows that you agree to tolerate. COPY FROM LOCAL will fail when N records are rejected. If the number of rejected rows is fewer than N, COPY FROM LOCAL will succeed, but the rejected data file will contain the rows that have been rejected, and the exceptions file will contain exceptions occurred during the load process for you to review.
- If you do not allow any margin of error, run COPY FROM LOCAL with the ABORT ON ERROR, REJECTED DATA, and EXCEPTIONS parameters. When an error occurs, COPY FROM LOCAL will fail. The rejected data file will contain the rejected row, and the exceptions file will contain the exception occurred during the load process for you to review.
Troubleshooting
Problem:
COPY FROM LOCAL fails with the following error message:
INTERNAL: VIAssert(input.offset <= input.size)
Reason:
Vertica discovered a syntax issue (for example, could not find the end of a line).
Solution:
- Check what the following COPY FROM LOCAL parameters are set to:
- DELIMITER [AS]
- ESCAPE [AS]
- ENCLOSED BY
- RECORD TERMINATOR
- Compare it to the contents of the CSV file that failed to load.
- Fix the file syntax and run COPY FROM LOCAL again. Alternatively, run COPY FROM LOCAL with the parameters set to the values corresponding to the file structure.