CODEWITHSUNDEEP
gcloudgoogle-cloud-spanner
RPayne
RPayne

Reputation: 75

formating gcloud spanner queries

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

Upvotes: 1

Views: 1210

Answers (2)

Drew
Drew

Reputation: 6639

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.

Upvotes: 0

c24t
c24t

Reputation: 91

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=" "))'

Upvotes: 2

Related Questions