dump varbinary data using VSQL

jumanjijumanji Registered User
  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'
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?

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited June 19

    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
    
    [[email protected] ~]$ vsql -c 'Select * from test' -F $',' -At -o '/home/dbadmin/output.csv'
    
    [[email protected] ~]$ cat /home/dbadmin/output.csv
    xxx,yyy
    yyy,bbb
    
    [[email protected] ~]$ 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)
    
  • jumanjijumanji Registered User

    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_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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 Registered User
    edited June 19

    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.

  • jumanjijumanji Registered User

    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 Employee, Registered User, VerticaExpert

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

Leave a Comment

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