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

Forum for the novice Vertica database SQL topics

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1737
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 HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1737
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 HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”

Who is online

Users browsing this forum: No registered users and 1 guest