Using filler with COPY..FAVROPARSER?

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.

Best Answers

  • SergeBSergeB - Select Field - Employee
    Answer ✓

    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();

  • SergeBSergeB - Select Field - Employee
    edited February 2022 Answer ✓

    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();

  • yurmixyurmix Vertica Customer
    Answer ✓

    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

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
    
  • yurmixyurmix Vertica Customer

    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!!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file