How to export Google spanner query results to .csv

问题:

I am new to google spanner and I have run a query and found about 50k rows of data. I want to export that resultset to my local machine like .csv or into a google sheet. Previously I have used TOAD where I have an export button, but here I do not see any of those options. Any suggestions please.

回答1:

The gcloud spanner databases execute-sql command allows you to run SQL statements on the command line and redirect output to a file. The --format=csv global argument should output in CSV.

https://cloud.google.com/spanner/docs/gcloud-spanner https://cloud.google.com/sdk/gcloud/reference/

回答2:

You could use a number of standard database tools with Google Cloud Spanner using a JDBC driver.

Have a look at this article: https://www.googlecloudspanner.com/2017/10/using-standard-database-tools-with.html

Toad is not included as an example, and I don't know if Toad supports dynamic loading of JDBC drivers and connecting to any generic JDBC database. If not, you could try one of the other tools listed in the article. Most of them would also include an export function.

回答3:

Unfortunately, gcloud spanner databases execute-sql is not quite compatible with --format=csv because of the way the data is laid out under the hood (an array instead of a map). It's much less pretty, but this works:

SQL_STRING='select * from your_table'
gcloud spanner databases execute-sql [YOURDB] --instance [YOURINSTANCE] \
--sql=SQL_STRING --format json > data.json

jq '.metadata.rowType.fields[].name' data.json | tr '\n' ', ' > data.csv
echo "" >> data.csv
jq '.rows[] | @csv' data.json >> data.csv

This dumps the query in json form to data.json, then writes the column names to the CSV, followed by a line feed, and finally the row contents. As a bonus, jq is installed by default on cloudshell, so this shouldn't carry any extra dependencies there.