I’m in a middle of a PoC and the following issue raised.
Customer requests to load data simply and quickly.
Loading Parquet files is not fast enough and loading CSV (faster) is also possible.
Customer wants to enclose its data fields with quotes and use the ‘~’ as separator.
As it seems from here https://forum.vertica.com/discussion/205765/copy-issue-loading-enclosed-data-with-nulls
Vertica requires for each column explicit handling with CASE, or use a (slow) Parser.
Customer don’t want to take care for each column separately nor to start compile the whole world (from their perspective) for something so intuitive to work.
In addition, although FCSVPARSER should support the 'delimiter' parameter as mentioned in the doc below, as you can see below the '~' delimiter does not work.
See:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/FlexTables/FCSVPARSERreference.htm
The following works:
cat test3.csv
CREATE TABLE mytest
(
f1 VARCHAR(100),
f2 INT,
f3 BOOLEAN,
f4 INT);
COPY mytest
FROM STDIN
PARSER fcsvparser(delimiter='~',HEADER='false',enclosed_by='"')
ABORT ON ERROR;
"a. all values in data","1","TRUE","1"
"b. missing first INT","","TRUE","2"
"c. missing 3rd value","1","","3"
"d. missing 2,3,4 values","","",""
\.
With '~' as delimiter the following does not work
cat test4.sql
CREATE TABLE mytest
(
f1 VARCHAR(100),
f2 INT,
f3 BOOLEAN,
f4 INT);
COPY mytest
FROM STDIN
PARSER fcsvparser(delimiter='~',HEADER='false',enclosed_by='"')
ABORT ON ERROR;
"a. all values in data"~"1"~"TRUE"~"1"
"b. missing first INT"~""~"TRUE"~"2"
"c. missing 3rd value"~"1"~""~"3"
"d. missing 2,3,4 values"~""~""~""
\.
vsql:test4.sql:16: ERROR 2035: COPY: Input record 1 has been rejected (Error: Unexpected character found at [24])
SELECT * FROM mytest;
f1 | f2 | f3 | f4
----+----+----+----
(0 rows)
