formating gcloud spanner queries

Question

Up until recently my gcloud spanner queries where nicely presented as columns across the screen, with each output line representing a single row from the query. Recently however, for some unknown reason, the output is now displayed as row data presented in single column:value pair down the screen, e.g.

PKey: 9moVr4HmSy6GGIYJyVGu3A==

Ty: Pf

IsNode: Y

P: None

IX: X

I have tried various --format command line options but alas have had no success in generating the original row-per-line-output format i.e. with columns presented across the screen as follows

PKey                     Ty   IsNode   P   IX    <-- columns names

9moVr4HmSy6GGIYJyVGu3A== Pf    Y      None  X.  <--- row data

What format option should I use ?.

Example of gcloud query:

gcloud spanner databases execute-sql test-sdk-db --instance=test-instance --sql="Select * from Block "

Thanks

Answer 1

gcloud formats the results as a table if they're being written to a file, the usual formatting rules apply otherwise.

So one easy way to see the table in the shell is to tee it somewhere:

gcloud spanner databases execute-sql test-sdk-db --instance=test-instance --sql="Select * from Block " \
| tee /dev/null

If you can't do that for some reason you can always get the same result with some --format surgery. To print the column names:

gcloud spanner databases execute-sql test-sdk-db --instance=test-instance --sql="Select * from Block " \
--format 'csv[no-heading, delimiter=" "](metadata.rowType.fields.name)'

And to print the rows:

gcloud spanner databases execute-sql test-sdk-db --instance=test-instance --sql="Select * from Block " \
--format 'csv[no-heading, delimiter="\n"](rows.map().flatten(separator=" "))'

Answer 2

The format of gcloud spanner databases execute-sql is a result of broader changes in formatting to better support accessibility standards for screen readers.

There are two methods to receive results in the tabular format:

1. Set the accessibility/screen_reader configuration property to false.

   gcloud config set accessibility/screen_reader false
   

2. Similar to the other suggestion about using formatting, you can use a --format=multi(...) option in your gcloud command:

   gcloud spanner databases execute-sql test-sdk-db \
       --instance=test-instance --sql="Select * from Block " \
       --format="multi(metadata:format='value[delimiter='\t'](rowType.fields.name)', \
                           rows:format='value[delimiter='\t']([])')"
   

   The caveat of this second method is that column names and values may not align due to differences in length.