Export Cloud SQL data into a CSV, including headers and adding a custom delimiter

[Luca Vlad]

Hello, I am using the REST API for exporting the data from a SQL Cloud db into a csv on a bucket. Documentation for the API is : Cloud SQL to CSV. A further explanation of the request body parameters is found here: REST API request. Because my data contains ',' I want to change the default deliminator into '|' . Also I want my csv file to include a header line (table columns names). Do you guys know anything that can help?

Thank you very much!

[George (Cloud Platform Support)]

Bună Luca, 

You have an example on the page you linked to, of the query used to select data for export: 

  SELECT <query> INTO OUTFILE ... CHARACTER SET 'utf8mb4'

        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'

        ESCAPED BY '\\' LINES TERMINATED BY '\n'

To change the default delimiter you can use FIELDS TERMINATED BY, and, generally, adapt this query to your needs. The ExportContext object contains:

  "csvExportOptions": {

    "selectQuery": string

  }

You can use this string value of the selectQuery parameter to tailor the query sent as part of the InstancesExportRequest, when calling the Method: instances.export. 

There is no ready-made way to include table column names, one needs to provide for that separately, but you can find examples on the Web, for instance the reply to question "Include headers when using SELECT INTO OUTFILE?" in stackoverflow. 

You can use this string value of the selectQuery parameter to tailor the query sent as part of the InstancesExportRequest, when calling the Method: instances.export. 

[Luca Vlad]

Hello,

Thanks for your answer! In regards with the query, I can not just do the query:

SELECT <query> INTO OUTFILE ... CHARACTER SET 'utf8mb4'

        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'

        ESCAPED BY '\\' LINES TERMINATED BY '\n'

Because I am accessing the resources via the REST API and if I exchange the  string parameter from:

  "csvExportOptions": {

    "selectQuery": string

  }

with the specified query the  FIELDS TERMINATED BY ','  is actually begin discarded.

In regards with the include headers I saw that solution, but that solution always means that you are aware of the column names and always change them when they change in the database. I was searching for a more automated method one that can get the column names without specifically mentioning them. Basically I wanted to tun something like: "SELECT * FROM table_name" and get data and column names.

Thank you! 

[Mary (Cloud Platform Support)]

Hello Luca,

Currently with the API you would need to specify the column names when exporting, when as you mentioned you are not aware of the name(s) or number of columns it can be difficult. 

There is an existing public feature request to have the table headers included in the export which can be viewed here[1]. There is no ETA nor a guarantee this feature request will be implemented in the future. 

I suggest you to up vote the feature request (click on the plus 1 button), this will help the engineering team to know that there are more users interested in this feature request. You can also star the feature request in order to receive any updates should there be any posts. 

[1] https://issuetracker.google.com/111342008

[Luca Vlad]

I will do so, thank you both very much!