Custom delimiter while exporting Google Cloud SQL to CSV

Question

I've been successfully exporting GCloud SQL to CSV with its default delimiter ",". I want to import this CSV to Google Big Query and I've succeed to do this.

However, I'm experiencing a little problem. There's "," in some of my cell/field. It causes Big Query import process not working properly. For Example:

"Budi", "19", "Want to be hero, and knight"

My questions are:

• Is it possible to export Google Cloud SQL with custom delimiter e.g. "|"?
• If not, how to make above sample data to be imported in Google Big Query and become 3 field/cell?

Cheers.

Answer 1

Is it possible to export Google Cloud SQL with custom delimiter e.g. "|"?

Yes it's, See the documentation page of BigQuery how to set load options provided in this link

You will need to add --field_delimiter = '|' to your command

From the documentation:

(Optional) The separator for fields in a CSV file. The separator can be any ISO-8859-1 single-byte character. To use a character in the range 128-255, you must encode the character as UTF8. BigQuery converts the string to ISO-8859-1 encoding, and uses the first byte of the encoded string to split the data in its raw, binary state. BigQuery also supports the escape sequence "\t" to specify a tab separator. The default value is a comma (,).

Answer 2

As far as I know there's no way of setting a custom delimiter when exporting from CloudSQL to CSV. I attempted to introduce my own delimiter by formulating my select query like so:

select column_1||'|'||column_2 from foo

But this only results in CloudSQL escaping the whole result in the resulting CSV with double quotes. This also aligns with the documentation which states:

Exporting in CSV format is equivalent to running the following SQL statement:

  SELECT <query> INTO OUTFILE ... CHARACTER SET 'utf8mb4'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
    ESCAPED BY '\\' LINES TERMINATED BY '\n'

https://cloud.google.com/sql/docs/mysql/import-export/exporting