Using filler with COPY..FAVROPARSER?

yurmix

Hello, I have COPY command, looks similar to this:
COPY tab (col1, col2,...) FROM LOCAL 'file.txt' PARSER FAVROPARSER(....) ENFORCELENGTH ABORT ON ERROR;
I need to transform one of the columns using STRING_TO_ARRAY(). I tried using filler but got a few errors:

1. When using col10 FILLER VARCHAR I got:
   ERROR 2671: Column reference "col10" is ambiguous DETAIL: Reference "col10" could refer to either "myschema.tab.col10" or "*FILLER*.col10".
2. Using *FILLER*.col10 gave:
   ERROR 4856: Syntax error at or near "*" at character 465 so you're not really supposed to use *.
3. Using FILLER.col10 FILLER VARCHAR gave:
   ERROR 3549: Indirection is not allowed in the name of a FILLER column.
4. Using just, FILLER.col10, gave:
   ERROR 4267: Only table column names and filler column names can appear in the list of columns to copy.

SergeB

I think it's possible but in your example ( col10 FILLER VARCHAR ) col10 has to be a field in your AVRO record and can not be a column in your target table.

Here's an example with FSONPARSER (should work similarly with FAVROPARSER):

My avro record has a field named data_device_ip that I want to copy as INET_ATON to a column named ipv4

COPY ip_test(data_device_ip filler varchar(30),ipv4 as INET_ATON(data_device_ip))
FROM '/tmp/ip_test.json'
PARSER FJSONParser();

SergeB

I think it's possible but in your example ( col10 FILLER VARCHAR ) col10 has to be a field in your AVRO record and can not be a column in your target table.

Here's an example with FSONPARSER (should work similarly with FAVROPARSER):

My JSON record has a field named data_device_ip that I want to copy as INET_ATON to a column named ipv4

COPY ip_test(data_device_ip filler varchar(30),ipv4 as INET_ATON(data_device_ip))
FROM '/tmp/ip_test.json'
PARSER FJSONParser();

yurmix

Thank you! I was able to implement that with Avro parser just like you did with JSON:

copy t (c FILLTR VARCHAR(3000), c_tag AS STRING_TO_ARRAY('['||c||']')::ARRAY[INT])

Notice that if you don't explicitly cast to ARRAY[INT] then you'll get an error:

ERROR 2764: COPY: Expression for column c_tag cannot be coerced

Jim_Knicely

The FILLER should have a different name than the column name!

Example:

dbadmin=> CREATE TABLE t (c ARRAY[VARCHAR, 3]);
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/text.txt
A,B,C

dbadmin=> COPY t (c_f FILLER VARCHAR, c AS STRING_TO_ARRAY('[' || c_f || ']')) FROM '/home/dbadmin/text.txt';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> SELECT * FROM t;
       c
---------------
 ["A","B","C"]
(1 row)

yurmix

Thank you! But I think FILLER doesn't work with the FAVROPARSER, as the columns are assigned based on the Avro schema, not based on the ordinal position (unlike the default DELIMITER parser).
I see you have answered this before
https://forum.vertica.com/discussion/241146/can-i-use-filler-with-fcsvparser

I'll contact support since it's not documented and also there has to be a way to perform transformations with flex parsers.
But if anyone has an answer I'd like to hear it. Thanks!!