Using filler with COPY..FAVROPARSER?
yurmix
Vertica Customer
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:
- When using
col10 FILLER VARCHARI got:ERROR 2671: Column reference "col10" is ambiguous DETAIL: Reference "col10" could refer to either "myschema.tab.col10" or "*FILLER*.col10". - Using
*FILLER*.col10gave:ERROR 4856: Syntax error at or near "*" at character 465so you're not really supposed to use*. - Using
FILLER.col10 FILLER VARCHARgave:ERROR 3549: Indirection is not allowed in the name of a FILLER column. - 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
-
SergeB
- Select Field - Employee
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();0 -
SergeB
- Select Field - Employee
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();0 -
yurmix
Vertica Customer
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
0
Answers
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)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!!