dump varbinary data using VSQL

jumanji

1. Create table test(hash1 varbinary(64000), hash2 varbinary(64000))
2. Select * from test

hash1 hash2
xxx. yyy
yyy bbb

3. I exported this table using VSQL like this
   ~/opt/vertica/bin/vsql -c 'Select * from test' -U ‘user_name’ -w ‘password’ -h hostname -p 5433 -F $',' -At -o '/tmp/output.csv'

4. The problem is output.csv gets stored in text format, when I try to load the data back from this file using copy command like

copy test2
FROM LOCAL '/tmp/output.csv'
WITH DELIMITER AS ‘,’

I get invalid binary value error. I need to store the data in binary format, so that, copy command can pass. Is there a VSQL option for this?

Jim_Knicely

I'm not getting an error...

dbadmin=> CREATE TABLE test(hash1 VARBINARY(64000), hash2 VARBINARY(64000));
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 'xxx'::VARBINARY, 'yyy'::VARBINARY;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO test SELECT 'yyy'::VARBINARY, 'bbb'::VARBINARY;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> \q

[dbadmin@vertica01 ~]$ vsql -c 'Select * from test' -F $',' -At -o '/home/dbadmin/output.csv'

[dbadmin@vertica01 ~]$ cat /home/dbadmin/output.csv
xxx,yyy
yyy,bbb

[dbadmin@vertica01 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> create table test2 like test;
CREATE TABLE

dbadmin=> COPY test2 FROM LOCAL '/home/dbadmin/output.csv' WITH DELIMITER AS ',';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> SELECT * FROM test2;
 hash1 | hash2
-------+-------
 xxx   | yyy
 yyy   | bbb
(2 rows)

jumanji

Hi Jim_knicely,
Thanks much!. In my case, the values of VARBINARY columns are generated by UDFs they are hash values stored in DB as VARBINARY. I get "invalid binary value error" when I try to copy the value back. I think, by generating a file in Binary format and loading it back may solve the issue I guess. Could you please let me know how we can generate the Binary file using VSQL is there a command line option.

There is some info here on Binary files
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/AdministratorsGuide/BinaryFilesAppendix/CreatingNativeBinaryFormatFiles.htm?TocPath=Administrator's%20Guide|Appendix%3A%20Binary%20File%20Formats|_____1

but there is no details on how to generate one.

Jim_Knicely

Can convert the varbinary values to HEX?

vsql -c 'SELECT to_hex(hash1::varbinary(32500)), to_hex(hash2::varbinary(32500)) FROM test' -F $',' -At -o '/home/dbadmin/output.csv'

Then convert the HEX back to binary?

COPY test2 (hash1_f FILLER VARCHAR, hash2_f FILLER VARCHAR, hash1 AS hex_to_binary(hash1_f), hash2 AS hex_to_binary(hash2_f)) FROM LOCAL '/home/dbadmin/output.csv' WITH DELIMITER AS ',';

jumanji

Hi Jim_knicely,
Thanks again,
The conversions happened and data got loaded. However, the value is getting corrupted. That is, without the conversion the hash value will result in a value of 3300, but after conversion when I try to decode the hash value it will result in 20.

So basically the hash value/encoded value is getting corrupted by the conversion.

jumanji

Hi Jim_Knicely,

Thanks much your solution worked. The problem was in the VARCHAR in copy statement it was copying only first few bits. When I changed it back to VARCHAR(64000). I got right results.

Jim_Knicely

Awesome! Sorry about the confusion with the data type size...

bmurrell

Hi Jim_Knicely,
I have this issue, but getting the following error : -
ERROR 4800: String of 65001 octets is too long for type Varchar(65000)
Source column type is LONG VARBINARY(256000).
How do I work around this ?