The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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's%20Guide|Appendix%3A%20Binary%20File%20Formats|_____1

    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