For vertica s3 export query escape multiple characters-CodePudding

In Vertica DB we have an attribute column that is either comma-separated or enclosed within inverted commas (double and single applicable). When we do an s3 export query on Vertica DB we get the CSV file but when we validate it through an online CSV validator or s3 select query formatted we get an error.

SELECT S3EXPORT(* USING PARAMETERS url='xxxxxxxxxxxxxxxxxxxx.csv', delimiter=',', enclosed_by='\"', prepend_hash=false, header=true, chunksize='10485760'....

and suggestions on how to resolve this issue? PS: Reading manually every row and checking columns is not the choice

example attributes:-

select uid, cid, att1 from table_name where uid in (16, 17, 15);
 uid |  cid  |        att1         
----- ------- ---------------------
  16 | 78940 | yel,k
  17 | 78940 | master#$;@
  15 | 78940 | "hello , how are you"

CodePudding user response：

S3EXPORT() is deprecated as from Version 11. We are at Version 12 currently.

Now, you would export like so:

EXPORT TO DELIMITED(
  directory='s3://mybucket/mydir'                                                                                                                    
, filename='indata'
, addHeader='true'
, delimiter=','
, enclosedBy='"'
) OVER(PARTITION BEST) AS
SELECT * FROM indata;

With your three lines, this would generate the below:

dbadmin@gessnerm-HP-ZBook-15-G3:~$ cat /tmp/export/indata.csv 
uid,cid,att1
15,78940,"\"hello \, how are you\""
16,78940,"yel\,k"
17,78940,"master#$;@"

Do you need a different format?