How to export Google spanner query results to .csv or google sheets?

Question

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.

Answer 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/

Answer 2

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.

Answer 3

As @redpandacurios stated you can use the gcloud spanner databases execute-sql CLI command to achieve this, though without the --format csv option as it causes a Format [csv] requires a non-empty projection. error on gcloud v286.0.0.

This does not produce the projection error:

gcloud spanner databases execute-sql \
  [DATABASE] \
  --instance [INSTANCE] \
  --sql "<YOUR SQL>" \
  >output.csv

But you get an output formatted as:

<column1> <column2>
<data1>   <data1>
<data2>   <data2>
...
<dataN>   <dataN>

So not quite csv, but whitespace separated. If you want JSON, use --format json >output.json in place of the last line.

To get CSV it seems you may need to convert from JSON to CSV as stated in one of the other answers.

Answer 4

Others have mentioned using --format "csv" but getting the error Format [csv] requires a non-empty projection.

I believe I discovered how to specify projections that will get --format csv to work as expected. An example:

gcloud spanner databases execute-sql [DATABASE] --instance [INSTANCE] \
  --sql "select c1, c2 from t1" \
  --flatten "rows" \
  --format "csv(rows[0]:label=c1, rows[1]:label=c2)"

rows is the actual field name returned by execute-sql and that we need to properly transform in the projection.

Answer 5

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.

Answer 6

I made it with awk only, my gcloud is producing "text" output by default, where values have no whitespaces and are separated with tabs:

    gcloud spanner databases execute-sql \
      [DATABASE] \
      --instance [INSTANCE] \
      --sql "<YOUR SQL>" \
        | awk '{print gensub(/[[:space:]]{1,}/,",","g",$0)}' \
      > output.csv

For key=value format (useful where there are many columns) I use this awk filter instead, to catch the column names in 1st row, then to combine them with values:

awk 'NR==1 {split($0,columns); next} {split ($0,values); for (i=1; i<=NF; i++) printf ("row %d: %s=%s\n", NR-1, columns[i], values[i]); }'