dump varbinary data using VSQL

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

hash1 hash2
xxx. yyy
yyy bbb

  1. 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'

  2. 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'

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_KnicelyJim_Knicely - Select Field - Administrator
    edited June 2018

    I'm not getting an error...

    dbadmin=> CREATE TABLE test(hash1 VARBINARY(64000), hash2 VARBINARY(64000));
    dbadmin=> INSERT INTO test SELECT 'xxx'::VARBINARY, 'yyy'::VARBINARY;
    (1 row)
    dbadmin=> INSERT INTO test SELECT 'yyy'::VARBINARY, 'bbb'::VARBINARY;
    (1 row)
    dbadmin=> COMMIT;
    dbadmin=> \q
    [dbadmin@vertica01 ~]$ vsql -c 'Select * from test' -F $',' -At -o '/home/dbadmin/output.csv'
    [dbadmin@vertica01 ~]$ cat /home/dbadmin/output.csv
    [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;
    dbadmin=> COPY test2 FROM LOCAL '/home/dbadmin/output.csv' WITH DELIMITER AS ',';
     Rows Loaded
    (1 row)
    dbadmin=> SELECT * FROM test2;
     hash1 | hash2
     xxx   | yyy
     yyy   | bbb
    (2 rows)
  • 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

    but there is no details on how to generate one.

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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 ',';

  • jumanjijumanji
    edited June 2018

    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.

  • 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_KnicelyJim_Knicely - Select Field - Administrator

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

  • bmurrellbmurrell Community Edition User

    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 ?

Leave a Comment

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