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
- Create table test(hash1 varbinary(64000), hash2 varbinary(64000))
- Select * from test
hash1 hash2
xxx. yyy
yyy bbb
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'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?
0
Comments
I'm not getting an error...
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.
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 ',';
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.
Awesome! Sorry about the confusion with the data type size...
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 ?