Since this topic comes up in many places we decided to package up the UNLOAd/extract process into a Docker service. For example, you can use this option to escape the delimiter character, a quote, an embedded newline, or the escape character itself when any of these characters is a legitimate part of a column value. The character that immediately follows the backslash character is loaded into the table as part of the current column value, even if it is a character that normally serves a special purpose. A quote character: " or ' (if both ESCAPE and ADDQUOTES are specifiedĪnd with escape in copy command, the backslash character () in input data is treated as an escape character.The delimiter character specified for the unloaded data.With escape in unload command, for CHAR and VARCHAR columns in delimited unload files, an escape character (\) is placed before every occurrence of the following characters:
I'm wondering if there's a way to unload/copy a redshift table which is irrelevant to the content of the table, which will always succeed no mater what weird strings are stored in the table.įinally I figured out the right approach, to add escape in both unload and copy command: unload ('select * from tbl_example') to 's3://s3bucket/tbl_example' CREDENTIALS 'aws_access_key_id=xxx aws_secret_access_key=xxx' delimiter '|' addquotes escape allowoverwrite Ĭopy tbl_example2 from 's3://s3bucket/tbl_example' CREDENTIALS 'aws_access_key_id=xxx aws_secret_access_key=xxx' delimiter '|' removequotes escape Then I have to change the delimiter '|' to another one like ',' and try again, if I'm unlucky, maybe it takes multiple tries to get a success. I will get load error if the table happens to have a field with its content as "||". The right delimiter is relevant to the content of the table! I had to change the delimiter each time I met load errors.įor example, when I use the following command to unload/copy a table: unload ('select * from tbl_example') to 's3://s3bucket/tbl_example' CREDENTIALS 'aws_access_key_id=xxx aws_secret_access_key=xxx' delimiter '|' addquotes allowoverwrite Ĭopy tbl_example2 from 's3://s3bucket/tbl_example' CREDENTIALS 'aws_access_key_id=xxx aws_secret_access_key=xxx' delimiter '|' removequotes
In RedShift, it is convenient to use unload/copy to move data to S3 and load back to redshift, but I feel it is hard to choose the delimiter each time.
0 kommentar(er)
