Comments

• Jim_Knicely - Select Field - Administrator

  June 2018 edited June 2018

  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)
  

  0
• jumanji ✭

  June 2018

  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.

  0
• Jim_Knicely - Select Field - Administrator

  June 2018

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

  1
• jumanji ✭

  June 2018 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.

  0
• jumanji ✭

  June 2018

  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.

  0
• Jim_Knicely - Select Field - Administrator

  June 2018

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

  1
• bmurrell Community Edition User ✭

  July 2020

  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 ?

  0