NULL AS 'string' option of the copy command does not work with fcsvparser()

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

NULL AS 'string' option of the copy command does not work with fcsvparser()

Post by JimKnicely » Thu Jan 19, 2017 2:43 pm

Howdy,

I am trying to use the NULL AS 'string' option of the copy command but am running into an issue...

dbadmin=> \! cat /home/dbadmin/test.txt
1
2
NA
3
4
NA

dbadmin=> \d abc.test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------+------+---------+----------+-------------+-------------
abc | test | a | float | 8 | | f | f |
(1 row)


So the NULL AS 'NA' works okay if I use the default parser:

dbadmin=> copy abc.test from '/home/dbadmin/test.txt' null 'NA' direct;
Rows Loaded
-------------
6
(1 row)

dbadmin=> select * from abc.test;
a
---
1
2
3
4


(6 rows)


But if I use the fcsvparser, it does not:

dbadmin=> truncate table abc.test;
TRUNCATE TABLE

dbadmin=> copy abc.test from '/home/dbadmin/test.txt' parser fcsvparser() null 'NA' rejected data table abc.bad_data;
Rows Loaded
-------------
3
(1 row)

dbadmin=> select * from abc.test;
a
---
2
3
4
(3 rows)

dbadmin=> select * from abc.bad_data;
node_name | file_name | session_id | transaction_id | statement_id | batch_number | row_number | rejected_data | rejected_data_orig_length | rejected_reason
----------------+------------------------+-------------------------------+-------------------+--------------+--------------+------------+---------------+---------------------------+-------------------------
v_p11_node0001 | /home/dbadmin/test.txt | v_p11_node0001-132659:0x8b4a4 | 45035996273869442 | 1 | 0 | 2 | NA | 2 | Parse error in column 1
v_p11_node0001 | /home/dbadmin/test.txt | v_p11_node0001-132659:0x8b4a4 | 45035996273869442 | 1 | 0 | 5 | NA | 2 | Parse error in column 1
(2 rows)



How can I fix this?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: NULL AS 'string' option of the copy command does not work with fcsvparser()

Post by JimKnicely » Sun Jun 04, 2017 3:48 pm

Follow up:

If you click on the link below, you will find an example of “Using the NULL Parameter” with the FCSVPARSER…

https://my.vertica.com/docs/8.1.x/HTML/ ... SVData.htm

Unfortunately, it only appears to work with FLEX tables ...

dbadmin=> create flex table tt();
CREATE TABLE

dbadmin=> COPY tt FROM '/home/dbadmin/tt.txt' PARSER fcsvparser() delimiter '|' NULL 'NULL' REJECTED DATA TABLE tt_rejected;
Rows Loaded
-------------
3
(1 row)

dbadmin=> SELECT compute_flextable_keys_and_build_view('tt');
compute_flextable_keys_and_build_view
------------------------------------------------------------------------------------------
Please see public.tt_keys for updated keys
The view public.tt_view is ready for querying
(1 row)

dbadmin=> select * from public.tt_view;
a | b | c1
----+----+----
10 | 20 |
20 | 30 | 50
20 | 30 | 40
(3 rows)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

fane89
Newbie
Newbie
Posts: 9
Joined: Tue Jul 25, 2017 9:12 am

Re: NULL AS 'string' option of the copy command does not work with fcsvparser()

Post by fane89 » Mon Aug 14, 2017 1:40 pm

any updates?

sKwa
Newbie
Newbie
Posts: 15
Joined: Wed Aug 02, 2017 3:12 pm

Re: NULL AS 'string' option of the copy command does not work with fcsvparser()

Post by sKwa » Mon Aug 14, 2017 7:16 pm

Hi!

No for Vertica 8.0.6.

Post Reply

Return to “New to Vertica SQL”