spanner-mycli

My personal fork of spanner-cli, interactive command line tool for Cloud Spanner.

Description

spanner-mycli is an interactive command line tool for Google Cloud Spanner.
You can control your Spanner databases with idiomatic SQL commands.

Differences from original spanner-cli

Respects my minor use cases
- SHOW LOCAL PROTO and SHOW REMOTE PROTO statement
- Can use embedded emulator (--embedded-emulator)
- Support query parameters
- Test root-partitionable with TRY PARTITIONED QUERY <sql> command
- GenAI support(GEMINI statement).
Respects training and verification use-cases.
- gRPC logging(--log-grpc)
- Support mutations
Respects batch use cases as well as interactive use cases
More gcloud spanner databases execute-sql compatibilities
- Support compatible flags (--sql, --query-mode, --strong, --read-timestamp)
More gcloud spanner databases ddl update compatibilities
- Support --proto-descriptor-file flag
Generalized concepts to extend without a lot of original syntax
- Generalized system variables concept inspired by Spanner JDBC properties
  - SET <name> = <value> statement
  - SHOW VARIABLES statement
  - SHOW VARIABLE <name> statement
  - --set <name>=<value> flag
Improved interactive experience
- Use hymkor/go-multiline-ny instead of chzyer/readline"
  - Native multi-line editing
- Improved prompt
  - Use % for prompt expansion, instead of \ to avoid escaping
  - Allow newlines in prompt using %n
  - System variables expansion
  - Prompt2 with margin and waiting status
- Autowrap and auto adjust column width to fit within terminal width.
- Progress bar of DDL execution.
Utilize other libraries
- Dogfooding cloudspannerecosystem/memefish
  - Spin out memefish logic as apstndb/gsqlutils.
- Utilize apstndb/spantype and apstndb/spanvalue

Disclaimer

Do not use this tool for production databases as the tool is experimental/alpha quality forever.

Version policy

This software will not have a stable release. In other words, v1.0.0 will never be released. It will be operated as a kind of ZeroVer.

v0.X.Y will be operated as follows:

The initial release version is v0.1.0, forked from spanner-cli v0.10.6.
The patch version Y will be incremented for changes that include only bug fixes.
The minor version X will always be incremented when there are new features or changes related to compatibility.
As a general rule, unreleased updates to the main branch will be released within one week.

Install

Install Go and run the following command.

# For Go 1.23+
go install github.com/apstndb/spanner-mycli@latest

Or you can use a container image.

https://github.com/apstndb/spanner-mycli/pkgs/container/spanner-mycli

Usage

Usage:
  spanner-mycli [OPTIONS]

spanner:
  -p, --project=                         (required) GCP Project ID. [$SPANNER_PROJECT_ID]
  -i, --instance=                        (required) Cloud Spanner Instance ID [$SPANNER_INSTANCE_ID]
  -d, --database=                        (required) Cloud Spanner Database ID. [$SPANNER_DATABASE_ID]
  -e, --execute=                         Execute SQL statement and quit. --sql is an alias.
  -f, --file=                            Execute SQL statement from file and quit.
  -t, --table                            Display output in table format for batch mode.
  -v, --verbose                          Display verbose output.
      --credential=                      Use the specific credential file
      --prompt=                          Set the prompt to the specified format (default: spanner%t> )
      --prompt2=                         Set the prompt2 to the specified format (default: %P%R> )
      --log-memefish                     Emit SQL parse log using memefish
      --history=                         Set the history file to the specified path (default: /tmp/spanner_mycli_readline.tmp)
      --priority=                        Set default request priority (HIGH|MEDIUM|LOW)
      --role=                            Use the specific database role
      --endpoint=                        Set the Spanner API endpoint (host:port)
      --directed-read=                   Directed read option (replica_location:replica_type). The replicat_type is optional and either READ_ONLY or READ_WRITE
      --set=                             Set system variables e.g. --set=name1=value1 --set=name2=value2
      --param=                           Set query parameters, it can be literal or type(EXPLAIN/DESCRIBE only) e.g. --param="p1='string_value'" --param=p2=FLOAT64
      --proto-descriptor-file=           Path of a file that contains a protobuf-serialized google.protobuf.FileDescriptorSet message.
      --insecure                         Skip TLS verification and permit plaintext gRPC. --skip-tls-verify is an alias.
      --embedded-emulator                Use embedded Cloud Spanner Emulator. --project, --instance, --database, --endpoint, --insecure will be automatically configured.
      --emulator-image=                  container image for --embedded-emulator (default: gcr.io/cloud-spanner-emulator/emulator:1.5.25)
      --log-grpc                         Show gRPC logs
      --query-mode=[NORMAL|PLAN|PROFILE] Mode in which the query must be processed.
      --strong                           Perform a strong query.
      --read-timestamp=                  Perform a query at the given timestamp.

Authentication

Unless you specify a credential file with --credential, this tool uses Application Default Credentials as credential source to connect to Spanner databases.

Please make sure to prepare your credential by gcloud auth application-default login.

If you're running spanner-mycli in docker container on your local machine, you have to pass local credentials to the container with the following command.

docker run -it \
  -e GOOGLE_APPLICATION_CREDENTIALS=/tmp/credentials.json \
  -v $HOME/.config/gcloud/application_default_credentials.json:/tmp/credentials.json:ro \
  spanner-mycli --help
$ docker run -it \
    -v $HOME/.config/gcloud/application_default_credentials.json:/home/nonroot/.config/gcloud/application_default_credentials.json:ro \
    ghcr.io/apstndb/spanner-mycli --help

Example

Interactive mode

$ spanner-mycli -p myproject -i myinstance -d mydb
Connected.
spanner> CREATE TABLE users (
      ->   id INT64 NOT NULL,
      ->   name STRING(16) NOT NULL,
      ->   active BOOL NOT NULL
      -> ) PRIMARY KEY (id);
Query OK, 0 rows affected (30.60 sec)

spanner> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| users          |
+----------------+
1 rows in set (18.66 msecs)

spanner> INSERT INTO users (id, name, active) VALUES (1, "foo", true), (2, "bar", false);
Query OK, 2 rows affected (5.08 sec)

spanner> SELECT * FROM users ORDER BY id ASC;
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1  | foo  | true   |
| 2  | bar  | false  |
+----+------+--------+
2 rows in set (3.09 msecs)

spanner> BEGIN;
Query OK, 0 rows affected (0.02 sec)

spanner(rw txn)> DELETE FROM users WHERE active = false;
Query OK, 1 rows affected (0.61 sec)

spanner(rw txn)> COMMIT;
Query OK, 0 rows affected (0.20 sec)

spanner> SELECT * FROM users ORDER BY id ASC;
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1  | foo  | true   |
+----+------+--------+
1 rows in set (2.58 msecs)

spanner> DROP TABLE users;
Query OK, 0 rows affected (25.20 sec)

spanner> SHOW TABLES;
Empty set (2.02 msecs)

spanner> EXIT;
Bye

Batch mode

By passing SQL from standard input, spanner-mycli runs in batch mode.

$ echo 'SELECT * FROM users;' | spanner-mycli -p myproject -i myinstance -d mydb
id      name    active
1       foo     true
2       bar     false

You can also pass SQL with command line option -e.

$ spanner-mycli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;'
id      name    active
1       foo     true
2       bar     false

With -t option, results are displayed in table format.

$ spanner-mycli -p myproject -i myinstance -d mydb -e 'SELECT * FROM users;' -t
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1  | foo  | true   |
| 2  | bar  | false  |
+----+------+--------+

Directed reads mode

spanner-mycli now supports directed reads, a feature that allows you to read data from a specific replica of a Spanner database. To use directed reads with spanner-mycli, you need to specify the --directed-read flag. The --directed-read flag takes a single argument, which is the name of the replica that you want to read from. The replica name can be specified in one of the following formats:

<replica_location>
<replica_location>:<replica_type>

The <replica_location> specifies the region where the replica is located such as us-central1, asia-northeast2.
The <replica_type> specifies the type of the replica either READ_WRITE or READ_ONLY.

$ spanner-mycli -p myproject -i myinstance -d mydb --directed-read us-central1

$ spanner-mycli -p myproject -i myinstance -d mydb --directed-read us-central1:READ_ONLY

$ spanner-mycli -p myproject -i myinstance -d mydb --directed-read asia-northeast2:READ_WRITE

Directed reads are only effective for single queries or queries within a read-only transaction. Please note that directed read options do not apply to queries within a read-write transaction.

Note

If you specify an incorrect region or type for directed reads, directed reads will not be enabled and your requsts won't be routed as expected. For example, in a multi-region configuration nam3, if you mistype us-east1 as us-east-1, the connection will succeed, but directed reads will not be enabled.

To perform directed reads to asia-northeast2 in a multi-region configuration asia1, you need to specify asia-northeast2 or asia-northeast2:READ_WRITE. Since the replicas placed in asia-northeast2 are READ_WRITE replicas, directed reads will not be enabled if you specify asia-northeast2:READ_ONLY.

Please refer to the Spanner documentation to verify the valid configurations.

Syntax

In the following syntax, we use <> for a placeholder, [] for an optional keyword, and {} for a mutually exclusive keyword.

The syntax is case-insensitive.
\G delimiter is also supported for displaying results vertically.

Usage	Syntax	Note
List databases	`SHOW DATABASES;`
Switch database	`USE <database> [ROLE <role>];`	The role you set is used for accessing with fine-grained access control.
Create database	`CREATE DATABSE <database>;`
Drop database	`DROP DATABASE <database>;`
List tables	`SHOW TABLES [<schema>];`	If schema is not provided, default schema is used
Show table schema	`SHOW CREATE TABLE <table>;`	The table can be a FQN.
Show columns	`SHOW COLUMNS FROM <table>;`	The table can be a FQN.
Show indexes	`SHOW INDEX FROM <table>;`	The table can be a FQN.
Show sampled query plans	`SHOW QUERY PROFILES;`	EARLY EXPERIMENTAL
Show single sampled query plan	`SHOW QUERY PROFILE <fingerprint>;`	EARLY EXPERIMENTAL
Create table	`CREATE TABLE ...;`
Change table schema	`ALTER TABLE ...;`
Delete table	`DROP TABLE ...;`
Truncate table	`TRUNCATE TABLE <table>;`	Only rows are deleted. Note: Non-atomically because executed as a partitioned DML statement.
Create index	`CREATE INDEX ...;`
Delete index	`DROP INDEX ...;`
Create role	`CREATE ROLE ...;`
Drop role	`DROP ROLE ...;`
Grant	`GRANT ...;`
Revoke	`REVOKE ...;`
Query	`SELECT ...;`
DML	`{INSERT\|UPDATE\|DELETE} ...;`
Partitioned DML	`PARTITIONED {UPDATE\|DELETE} ...;`
Show local proto descriptors	`SHOW LOCAL PROTO;`
Show remote proto bundle	`SHOW REMOTE PROTO;`
Show Query Execution Plan	`EXPLAIN SELECT ...;`
Show DML Execution Plan	`EXPLAIN {INSERT\|UPDATE\|DELETE} ...;`
Show Query Execution Plan with Stats	`EXPLAIN ANALYZE SELECT ...;`
Show DML Execution Plan with Stats	`EXPLAIN ANALYZE {INSERT\|UPDATE\|DELETE} ...;`
Show Query Result Shape	`DESCRIBE SELECT ...;`
Show DML Result Shape	`DESCRIBE {INSERT\|UPDATE\|DELETE} ... THEN RETURN ...;`
Start a new query optimizer statistics package construction	`ANALYZE;`
Start Transaction	`BEGIN [TRANSACTION] [PRIORITY {HIGH\|MEDIUM\|LOW}];`	(Spanner JDBC driver style); It respects `READONLY` system variable. See Request Priority for details on the priority.
Set transaction mode	`SET TRANSACTION {READ ONLY\|READ WRITE};`	(Spanner JDBC driver style); Set transaction mode for the current transaction.
Start Read-Write Transaction	`BEGIN RW [TRANSACTION] [PRIORITY {HIGH\|MEDIUM\|LOW}];`	(spanner-cli style); See Request Priority for details on the priority.
Commit Read-Write Transaction or end Read-OnlyTransaction	`COMMIT [TRANSACTION];`
Rollback Read-Write Transaction or end Read-Only Transaction	`ROLLBACK [TRANSACTION];`	`CLOSE` is a synonym of `ROLLBACK`.
Start Read-Only Transaction	`BEGIN RO [TRANSACTION] [{<seconds>\|<RFC3339-formatted time>}] [PRIORITY {HIGH\|MEDIUM\|LOW}];`	`<seconds>` and `<RFC3339-formatted time>` is used for stale read. See Request Priority for details on the priority.
Test root-partitionable	`TRY PARTITIONED QUERY <sql>`
Show partition tokens of partition query	`PARTITION <sql>`
Perform write mutations	`MUTATE <table_fqn> {INSERT\|UPDATE\|REPLACE\|INSERT_OR_UPDATE} ...`
Perform delete mutations	`MUTATE <table_fqn> DELETE ...`
Exit CLI	`EXIT;`
Show variable	`SHOW VARIABLE <name>;`
Set variable	`SET <name> = <value>;`
Show variables	`SHOW VARIABLES;`
Set type query parameter	`SET PARAM <name> <type>;`
Set value query parameter	`SET PARAM <name> = <value>;`
Show variables	`SHOW PARAMS;`
Show DDLs	`SHOW DDLS;`

Customize prompt

You can customize the prompt by --prompt option or CLI_PROMPT system variable.
There are some escape sequences for being used in prompt.

Escape sequences:

%p : GCP Project ID
%i : Cloud Spanner Instance ID
%d : Cloud Spanner Database ID
%t : In transaction mode (ro txn) or (rw txn)
%n : Newline
%% : Character %
%{VAR_NAME} : System variable expansion

Example:

$ spanner-mycli -p myproject -i myinstance -d mydb --prompt='[%p:%i:%d]%n\t%% '
Connected.
[myproject:myinstance:mydb]
%
[myproject:myinstance:mydb]
% SELECT * FROM users ORDER BY id ASC;
+----+------+--------+
| id | name | active |
+----+------+--------+
| 1  | foo  | true   |
| 2  | bar  | false  |
+----+------+--------+
2 rows in set (3.09 msecs)

[myproject:myinstance:mydb]
% begin;
Query OK, 0 rows affected (0.08 sec)

[myproject:myinstance:mydb]
(rw txn)% ...

The default prompt is spanner%t> .

Prompt2

%P is substituted with padding to align the primary prompt.
%R is substituted with the current waiting status.

Prompt	Meaning
`-`	Waiting for terminating multi-line query by `;`
`'''`	Waiting for terminating multi-line single-quoted string literal by `'''`
`"""`	Waiting for terminating multi-line double-quoted string literal by `"""`
`/*`	Waiting for terminating multi-line comment by `*/`

spanner> SELECT """
    """> test
    """> """ AS s,
      -> '''
    '''> test
    '''> ''' AS s2,
      -> /*
     /*> 
     /*> */
      -> ;

The default prompt2 is %P%R> .

Config file

This tool supports a configuration file called spanner_mycli.cnf, similar to my.cnf.
The config file path must be ~/.spanner_mycli.cnf. In the config file, you can set default option values for command line options.

Example:

[spanner]
project = myproject
instance = myinstance
prompt = "[%p:%i:%d]%t> "

Configuration Precedence

Command line flags(highest)
Environment variables
.spanner_mycli.cnf in current directory
.spanner_mycli.cnf in home directory(lowest)

Request Priority

You can set request priority for command level or transaction level. By default MEDIUM priority is used for every request.

To set a priority for command line level, you can use --priority={HIGH|MEDIUM|LOW} command line option or CLI_PRIORITY system variable.

To set a priority for transaction level, you can use PRIORITY {HIGH|MEDIUM|LOW} keyword.

Here are some examples for transaction-level priority.

# Read-write transaction with low priority
BEGIN PRIORITY LOW;

# Read-only transaction with low priority
BEGIN RO PRIORITY LOW;

# Read-only transaction with 60s stale read and medium priority
BEGIN RO 60 PRIORITY MEDIUM;

# Read-only transaction with exact timestamp and medium priority
BEGIN RO 2021-04-01T23:47:44+00:00 PRIORITY MEDIUM;

Note that transaction-level priority takes precedence over command-level priority.

Transaction Tags and Request Tags

You can set transaction tag using SET TRANSACTION_TAG = "<tag>", and request tag using SET STATEMENT_TAG = "<tag>". Note: TRANSACTION_TAG is effective only in the current transaction and STATEMENT_TAG is effective only in the next query.

+------------------------------+
| BEGIN;                       |
| SET TRANSACTION_TAG = "tx1"; |
|                              |
| SET STATEMENT_TAG = "req1";  |
| SELECT val                   |
| FROM tab1      +--------------transaction_tag = tx1, request_tag = req1
| WHERE id = 1;                |
|                              |
| SELECT *                     |
| FROM tab1;     +--------------transaction_tag = tx1
|                              |
| SET STATEMENT_TAG = "req2";  |
|                              |
| UPDATE tab1                  |
| SET val = 10   +--------------transaction_tag = tx1, request_tag = req2
| WHERE id = 1;                |
|                              |
| COMMIT;        +--------------transaction_tag = tx1
|                              |
| BEGIN;                       |
| SELECT 1;                    |
| COMMIT;        +--------------no transaction_tag
+------------------------------+

Using with the Cloud Spanner Emulator

This tool supports the Cloud Spanner Emulator via the SPANNER_EMULATOR_HOST environment variable.

$ export SPANNER_EMULATOR_HOST=localhost:9010
# Or with gcloud env-init:
$ $(gcloud emulators spanner env-init)

$ spanner-mycli -p myproject -i myinstance -d mydb

# Or use --endpoint with --insecure
$ unset SPANNER_EMULATOR_HOST
$ spanner-mycli -p myproject -i myinstance -d mydb --endpoint=localhost:9010 --insecure

Notable features of spanner-mycli

This section describes some notable features of spanner-mycli, they are not appeared in original spanner-cli.

System Variables

Spanner JDBC inspired variables

They have almost same semantics with Spanner JDBC properties

Name	Type	Example
READONLY	READ_WRITE	`TRUE`
READ_ONLY_STALENESS	READ_WRITE	`"analyze_20241017_15_59_17UTC"`
OPTIMIZER_VERSION	READ_WRITE	`"7"`
OPTIMIZER_STATISTICS_PACKAGE	READ_WRITE	`"7"`
RPC_PRIORITY	READ_WRITE	`"MEDIUM"`
READ_TIMESTAMP	READ_ONLY	`"2024-11-01T05:28:58.943332+09:00"`
COMMIT_RESPONSE	READ_ONLY	`"2024-11-01T05:31:11.311894+09:00"`
TRANSACTION_TAG	READ_WRITE	`"app=concert,env=dev,action=update"`
STATEMENT_TAG	READ_WRITE	`"app=concert,env=dev,action=update,request=fetch"`

spanner-mycli original variables

Name	READ/WRITE	Example
CLI_PROJECT	READ_ONLY	`"myproject"`
CLI_INSTANCE	READ_ONLY	`"myinstance"`
CLI_DATABASE	READ_ONLY	`"mydb"`
CLI_DIRECT_READ	READ_ONLY	`"asia-northeast:READ_ONLY"`
CLI_ENDPOINT	READ_ONLY	`"spanner.me-central2.rep.googleapis.com:443"`
CLI_FORMAT	READ_WRITE	`"TABLE"`
CLI_HISTORY_FILE	READ_ONLY	`"/tmp/spanner_mycli_readline.tmp"`
CLI_PROMPT	READ_WRITE	`"spanner%t> "`
CLI_PROMPT2	READ_WRITE	`"%P%R> "`
CLI_ROLE	READ_ONLY	`"spanner_info_reader"`
CLI_VERBOSE	READ_WRITE	`TRUE`
CLI_PROTO_DESCRIPTOR_FILE	READ_WRITE	`"order_descriptors.pb"`
CLI_PARSE_MODE	READ_WRITE	`"FALLBACK"`
CLI_INSECURE	READ_WRITE	`"FALSE"`
CLI_QUERY_MODE	READ_WRITE	`"PROFILE"`
CLI_LINT_PLAN	READ_WRITE	`"TRUE"`

Embedded Cloud Spanner Emulator

spanner-mycli can launch Cloud Spanner Emulator with empty database, powered by testcontainers.

$ spanner-mycli --embedded-emulator [--emulator-image= gcr.io/cloud-spanner-emulator/emulator:${VERSION}]
> SET CLI_PROMPT="%p:%i:%d%n> ";
Empty set (0.00 sec)

emulator-project:emulator-instance:emulator-database
> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("INFORMATION_SCHEMA", "SPANNER_SYS");
+---------------+--------------+------------+-------------------+------------------+------------+---------------+-----------------+--------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARENT_TABLE_NAME | ON_DELETE_ACTION | TABLE_TYPE | SPANNER_STATE | INTERLEAVE_TYPE | ROW_DELETION_POLICY_EXPRESSION |
| STRING        | STRING       | STRING     | STRING            | STRING           | STRING     | STRING        | STRING          | STRING                         |
+---------------+--------------+------------+-------------------+------------------+------------+---------------+-----------------+--------------------------------+
+---------------+--------------+------------+-------------------+------------------+------------+---------------+-----------------+--------------------------------+
Empty set (8.763167ms)

Protocol Buffers support

You can use --proto-descriptor-file option to specify proto descriptor file.

$ spanner-mycli --proto-descriptor-file=testdata/protos/order_descriptors.pb 
Connected.
spanner> SHOW LOCAL PROTO;
+---------------------------------+-------------------+--------------------+
| full_name                       | package           | file               |
+---------------------------------+-------------------+--------------------+
| examples.shipping.Order         | examples.shipping | order_protos.proto |
| examples.shipping.Order.Address | examples.shipping | order_protos.proto |
| examples.shipping.Order.Item    | examples.shipping | order_protos.proto |
| examples.shipping.OrderHistory  | examples.shipping | order_protos.proto |
+---------------------------------+-------------------+--------------------+
4 rows in set (0.00 sec)

spanner> SET CLI_PROTO_DESCRIPTOR_FILE += "testdata/protos/query_plan_descriptors.pb";
Empty set (0.00 sec)

spanner> SHOW LOCAL PROTO;
+----------------------------------------------------------------+-------------------+-----------------------------------------------+
| full_name                                                      | package           | file                                          |
+----------------------------------------------------------------+-------------------+-----------------------------------------------+
| examples.shipping.Order                                        | examples.shipping | order_protos.proto                            |
| examples.shipping.Order.Address                                | examples.shipping | order_protos.proto                            |
| examples.shipping.Order.Item                                   | examples.shipping | order_protos.proto                            |
| examples.shipping.OrderHistory                                 | examples.shipping | order_protos.proto                            |
| google.protobuf.Struct                                         | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.Struct.FieldsEntry                             | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.Value                                          | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.ListValue                                      | google.protobuf   | google/protobuf/struct.proto                  |
| google.protobuf.NullValue                                      | google.protobuf   | google/protobuf/struct.proto                  |
| google.spanner.v1.PlanNode                                     | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.Kind                                | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.ChildLink                           | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.ShortRepresentation                 | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.PlanNode.ShortRepresentation.SubqueriesEntry | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
| google.spanner.v1.QueryPlan                                    | google.spanner.v1 | googleapis/google/spanner/v1/query_plan.proto |
+----------------------------------------------------------------+-------------------+-----------------------------------------------+
15 rows in set (0.00 sec)

spanner> CREATE PROTO BUNDLE (`examples.shipping.Order`);
Query OK, 0 rows affected (6.34 sec)

spanner> SHOW REMOTE PROTO;
+-------------------------+-------------------+
| full_name               | package           |
+-------------------------+-------------------+
| examples.shipping.Order | examples.shipping |
+-------------------------+-------------------+
1 rows in set (0.94 sec)

spanner> ALTER PROTO BUNDLE INSERT (`examples.shipping.Order.Item`);
Query OK, 0 rows affected (9.25 sec)

spanner> SHOW REMOTE PROTO;
+------------------------------+-------------------+
| full_name                    | package           |
+------------------------------+-------------------+
| examples.shipping.Order      | examples.shipping |
| examples.shipping.Order.Item | examples.shipping |
+------------------------------+-------------------+
2 rows in set (0.82 sec)

spanner> ALTER PROTO BUNDLE UPDATE (`examples.shipping.Order`);
Query OK, 0 rows affected (8.68 sec)

spanner> ALTER PROTO BUNDLE DELETE (`examples.shipping.Order.Item`);
Query OK, 0 rows affected (9.55 sec)

spanner> SHOW REMOTE PROTO;
+-------------------------+-------------------+
| full_name               | package           |
+-------------------------+-------------------+
| examples.shipping.Order | examples.shipping |
+-------------------------+-------------------+
1 rows in set (0.79 sec)

You can also use CLI_PROTO_DESCRIPTOR_FILE system variable to update or read the current proto descriptor file setting.

spanner> SET CLI_PROTO_DESCRIPTOR_FILE = "./other_descriptors.pb";
Empty set (0.00 sec)

spanner> SHOW VARIABLE CLI_PROTO_DESCRIPTOR_FILE;
+---------------------------+
| CLI_PROTO_DESCRIPTOR_FILE |
+---------------------------+
| ./other_descriptors.pb    |
+---------------------------+
Empty set (0.00 sec)

memefish integration

spanner-mycli utilizes memefish as:

statement type detector
comment stripper
statement separator

Statement type detector behavior can be controlled by CLI_PARSE_MODE system variable.

CLI_PARSE_MODE	Description
FALLBACK	Use memefish but fallback if error
NO_MEMEFISH	Don't use memefish
MEMEFISH_ONLY	Use memefish and don't fallback

spanner> SET CLI_PARSE_MODE = "MEMEFISH_ONLY";
Empty set (0.00 sec)

spanner> SELECT * FRM 1;
ERROR: invalid statement: syntax error: :1:10: expected token: <eof>, but: <ident>

  1:  SELECT * FRM 1
               ^~~

spanner> SET CLI_PARSE_MODE = "FALLBACK";
Empty set (0.00 sec)

spanner> SELECT * FRM 1;
2024/11/02 00:22:57 ignore memefish parse error, err: syntax error: :1:10: expected token: <eof>, but: <ident>

  1:  SELECT * FRM 1
               ^~~

ERROR: spanner: code = "InvalidArgument", desc = "Syntax error: Expected end of input but got identifier \\\"FRM\\\" [at 1:10]\\nSELECT * FRM 1\\n         ^"
spanner> SET CLI_PARSE_MODE = "NO_MEMEFISH";
Empty set (0.00 sec)

spanner> SELECT * FRM 1;
ERROR: spanner: code = "InvalidArgument", desc = "Syntax error: Expected end of input but got identifier \\\"FRM\\\" [at 1:10]\\nSELECT * FRM 1\\n         ^"

Mutations support

spanner-mycli supports mutations.

Mutations are buffered in read-write transaction, or immediately commit outside explicit transaction.

Write mutations

MUTATE <table_fqn> {INSERT|UPDATE|REPLACE|INSERT_OR_UPDATE} {<struct_literal> | <array_of_struct_literal>};

Delete mutations

MUTATE <table_fqn> DELETE ALL;
MUTATE <table_fqn> DELETE {<tuple_struct_literal> | <array_of_tuple_struct_literal>};
MUTATE <table_fqn> DELETE KEY_RANGE({start_closed | start_open} => <tuple_struct_literal>,
                                    {end_closed | end_open} => <tuple_struct_literal>);

Note: In this context, parenthesized expression and some simple literals are treated as a single field struct literal.

Examples of mutations

Example schema

CREATE TABLE MutationTest (PK INT64, Col INT64) PRIMARY KEY(PK);
CREATE TABLE MutationTest2 (PK1 INT64, PK2 STRING(MAX), Col INT64) PRIMARY KEY(PK1, PK2);

Examples of Write mutations

Insert a single row with key(1).

MUTATE MutationTest INSERT STRUCT(1 AS PK);

Insert or update four rows with keys(1, "foo", 1, "n", 1, "m", 50, "foobar").

MUTATE MutationTest2 INSERT_OR_UPDATE [STRUCT(1 AS PK1, "foo" AS PK2, 0 AS Col), (1, "n", 1), (1, "m", 3), (50, "foobar", 4)];

Examples of Delete mutations

Delete all rows in MutationTest table.

MUTATE MutationTest DELETE ALL;

Delete rows with PK (1) in MutationTest table.

MUTATE MutationTest DELETE (1);

Delete a single row with PK (1, "foo") in MutationTest2 table.

MUTATE MutationTest2 DELETE (1, "foo");

Delete two rows with PK (1, "foo", 2, "bar") in MutationTest2 table.

MUTATE MutationTest2 DELETE [(1, "foo"), (2, "bar")];

Delete rows between (1, "a") <= PK < (1, "n") in MutationTest2 table

MUTATE MutationTest2 DELETE KEY_RANGE(start_closed => (1, "a"), end_open => (1, "n"));

Query parameter support

Many Cloud Spanner clients don't support query parameters.

If you do not modify the query, you will not be able to execute queries that contain query parameters, and you will not be able to view the query plan for queries with parameter types STRUCT or ARRAY.

spanner-mycli solves this problem by supporting query parameters.

You can define query parameters using command line option --param or SET commands. It supports type notation or literal value notation in GoogleSQL.

Note: They are supported on the best effort basis, and type conversions are not supported.

Query parameters definition using `--param` option

$ spanner-mycli \
                --param='array_type=ARRAY<STRUCT<FirstName STRING, LastName STRING>>' \
                --param='array_value=[STRUCT("Marc" AS FirstName, "Richards" AS LastName), ("Catalina", "Smith")]'

You can see defined query parameters using SHOW PARAMS; command.

> SHOW PARAMS;
+-------------+------------+-------------------------------------------------------------------+
| Param_Name  | Param_Kind | Param_Value                                                       |
+-------------+------------+-------------------------------------------------------------------+
| array_value | VALUE      | [STRUCT("Marc" AS FirstName, "Richards" AS LastName), ("Catalina", "Smith")] |
| array_type  | TYPE       | ARRAY<STRUCT<FirstName STRING, LastName STRING>>                  |
+-------------+------------+-------------------------------------------------------------------+
Empty set (0.00 sec)

You can use value query parameters in any statement.

> SELECT * FROM Singers WHERE STRUCT(FirstName, LastName) IN UNNEST(@array_value);
+----------+-----------+----------+------------+------------+
| SingerId | FirstName | LastName | SingerInfo | BirthDate  |
+----------+-----------+----------+------------+------------+
| 2        | Catalina  | Smith    | NULL       | 1990-08-17 |
| 1        | Marc      | Richards | NULL       | 1970-09-03 |
+----------+-----------+----------+------------+------------+
2 rows in set (7.8 msecs)

You can use type query parameters only in EXPLAIN or DESCRIBE without value.

> EXPLAIN SELECT * FROM Singers WHERE STRUCT(FirstName, LastName) IN UNNEST(@array_type);
+-----+----------------------------------------------------------------------------------+
| ID  | Query_Execution_Plan                                                             |
+-----+----------------------------------------------------------------------------------+
|   0 | Distributed Cross Apply                                                          |
|   1 | +- [Input] Create Batch                                                          |
|   2 | |  +- Compute Struct                                                             |
|   3 | |     +- Hash Aggregate                                                          |
|   4 | |        +- Compute                                                              |
|   5 | |           +- Array Unnest                                                      |
|  10 | |              +- [Scalar] Array Subquery                                        |
|  11 | |                 +- Array Unnest                                                |
|  35 | +- [Map] Serialize Result                                                        |
|  36 |    +- Cross Apply                                                                |
|  37 |       +- [Input] Batch Scan (Batch: $v14, scan_method: Scalar)                   |
|  40 |       +- [Map] Local Distributed Union                                           |
| *41 |          +- Filter Scan (seekable_key_size: 0)                                   |
|  42 |             +- Table Scan (Full scan: true, Table: Singers, scan_method: Scalar) |
+-----+----------------------------------------------------------------------------------+
Predicates(identified by ID):
 41: Residual Condition: (($FirstName = $batched_v8) AND ($LastName = $batched_v9))

14 rows in set (0.18 sec)

> DESCRIBE SELECT * FROM Singers WHERE STRUCT(FirstName, LastName) IN UNNEST(@array_type);
+-------------+-------------+
| Column_Name | Column_Type |
+-------------+-------------+
| SingerId    | INT64       |
| FirstName   | STRING      |
| LastName    | STRING      |
| SingerInfo  | BYTES       |
| BirthDate   | DATE        |
+-------------+-------------+
5 rows in set (0.17 sec)

Interactive definition of query parameters using `SET` commands

You can define type query parameters using SET PARAM param_name type; command.

> SET PARAM string_type STRING;
Empty set (0.00 sec)

> SHOW PARAMS;
+-------------+------------+-------------+
| Param_Name  | Param_Kind | Param_Value |
+-------------+------------+-------------+
| string_type | TYPE       | STRING      |
+-------------+------------+-------------+
Empty set (0.00 sec)

You can define type query parameters using SET PARAM param_name = value; command.

> SET PARAM bytes_value = b"foo";
Empty set (0.00 sec)

> SHOW PARAMS;
+-------------+------------+-------------+
| Param_Name  | Param_Kind | Param_Value |
+-------------+------------+-------------+
| bytes_value | VALUE      | B"foo"      |
+-------------+------------+-------------+
Empty set (0.00 sec)

Partition Queries

spanner-mycli have some partition queries functionality.

Test root-partitionable

You can test whether the query is root-partitionable using TRY PARTITIONED QUERY command.

spanner> TRY PARTITIONED QUERY SELECT * FROM Singers;
+--------------------+
| Root_Partitionable |
+--------------------+
| TRUE               |
+--------------------+
1 rows in set (0.78 sec)

spanner> TRY PARTITIONED QUERY SELECT * FROM Singers ORDER BY SingerId;
ERROR: query can't be a partition query: rpc error: code = InvalidArgument desc = Query is not root partitionable since it does not have a DistributedUnion at the root. Please check the conditions for a query to be root-partitionable.
error details: name = Help desc = Conditions for a query to be root-partitionable. url = https://cloud.google.com/spanner/docs/reads#read_data_in_parallel

Show partition tokens.

You can show partition tokens using PARTITION command.

Note: spanner-mycli does not clean up batch read-only transactions, which may prevent resources from being freed until they time out.

spanner> PARTITION SELECT * FROM Singers;
+-------------------------------------------------------------------------------------------------+
| Partition_Token                                                                                 |
+-------------------------------------------------------------------------------------------------+
| QUw0MGxyRjJDZENocXc0TkZnR3NxVHN1QnFCMy1yWkxWZmlIdFhhc2U4T2lWOGJRVFhJRkgydU1URmZUb2dBLXVvZE1O... |
| QUw0MGxyRzhPdmZUR04xclFQVTJKQXlVMEJjZFBUWTV3NUFsT2x0VGxTNHZWSExsejQweXNUWUFtUXd5ZjBzWEhmQ0Fa... |
| QUw0MGxyR3paSk96WUNqdjFsQW9tc2UwOFFoNlA4SzhHUzNWQVltNzVlRHZxdjZpUmFVSFN2UmtBanozc0hEaE9Iem9x... |
+-------------------------------------------------------------------------------------------------+
3 rows in set (0.65 sec)

Query plan linter (EARLY EXPERIMANTAL)

CLI_LINT_PLAN system variable enables heuristic query plan linter in EXPLAIN and EXPLAIN ANALYZE.

spanner> SET CLI_LINT_PLAN = TRUE;
Empty set (0.00 sec)

spanner> EXPLAIN SELECT * FROM Singers WHERE FirstName LIKE "%Hoge%";
+----+----------------------------------------------------------------------------------+
| ID | Query_Execution_Plan                                                             |
+----+----------------------------------------------------------------------------------+
|  0 | Distributed Union (distribution_table: Singers, split_ranges_aligned: false)     |
|  1 | +- Local Distributed Union                                                       |
|  2 |    +- Serialize Result                                                           |
| *3 |       +- Filter Scan (seekable_key_size: 0)                                      |
|  4 |          +- Table Scan (Full scan: true, Table: Singers, scan_method: Automatic) |
+----+----------------------------------------------------------------------------------+
Predicates(identified by ID):
 3: Residual Condition: ($FirstName LIKE '%Hoge%')

Experimental Lint Result:
 3: Filter Scan (seekable_key_size: 0)
     Residual Condition: Potentially expensive Residual Condition: Maybe better to modify it to Scan Condition
 4: Table Scan (Full scan: true, Table: Singers, scan_method: Automatic)
     Full scan=true: Potentially expensive execution full scan: Do you really want full scan?

GenAI support

You can use GEMINI statement by setting vertexai_project in config file.

[spanner]
vertexai_project = example-project

The generated query is automatically filled in the prompt.

spanner> GEMINI  "Show all table with table_schema concat by dot if table_schema is not empty string";
+--------------------------------------------+
| Answer                                     |
+--------------------------------------------+
| SELECT CASE                                |
|     WHEN table_schema = '' THEN table_name |
|     ELSE table_schema || '.' || table_name |
|   END                                      |
| FROM INFORMATION_SCHEMA.TABLES             |
| WHERE table_type = 'BASE TABLE';           |
+--------------------------------------------+
Empty set (2.73 sec)

spanner> SELECT CASE
             WHEN table_schema = '' THEN table_name
             ELSE table_schema || '.' || table_name
           END
         FROM INFORMATION_SCHEMA.TABLES
         WHERE table_type = 'BASE TABLE';

Show query profiles (EARLY EXPERIMENTAL)

spanner-mycli can render undocumented underlying table of sampled query plans. These features are early experimental state so it will be changed.

Show query profiles(It will be very long outputs).

spanner> SHOW QUERY PROFILES;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Plan                                                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT INTERVAL_END, QUERY_PROFILE FROM SPANNER_SYS.QUERY_PROFILES_TOP_HOUR                                                                               |
| ID | Plan                                                                                                                                                 |
| *0 | Distributed Union (distribution_table: _TopNQueryProfiles, split_ranges_aligned: false)                                                              |
|  1 | +- Local Distributed Union                                                                                                                           |
|  2 |    +- Serialize Result                                                                                                                               |
| *3 |       +- Filter Scan (seekable_key_size: 0)                                                                                                          |
| *4 |          +- Table Scan (Table: _TopNQueryProfiles, scan_method: Scalar)                                                                              |
| Predicates:                                                                                                                                               |
| 0: Split Range: (($interval_seconds = <scrubbed>) AND ($source = <scrubbed>) AND ($call_type = <scrubbed>))                                               |
| 3: Residual Condition: (($source = <scrubbed>) AND ($call_type = <scrubbed>))                                                                             |
| 4: Seek Condition: ($interval_seconds = <scrubbed>)                                                                                                       |
|                                                                                                                                                           |
| interval_end:                 2024-11-29 16:00:00 +0000 UTC                                                                                               |
| text_fingerprint:             1603015871075919821                                                                                                         |
| elapsed_time:                 8.52 msecs                                                                                                                  |
| cpu_time:                     7.48 msecs                                                                                                                  |
| rows_returned:                24                                                                                                                          |
| deleted_rows_scanned:         3                                                                                                                           |
| optimizer_version:            7                                                                                                                           |
| optimizer_statistics_package: auto_20241128_05_46_13UTC                                                                                                   |
|                                                                                                                                                           |
| SELECT @_p0_INT64                                                                                                                                         |
| ID | Plan                                                                                                                                                 |
|  0 | Serialize Result                                                                                                                                     |
|  1 | +- Unit Relation                                                                                                                                     |
|                                                                                                                                                           |
| interval_end:                 2024-11-23 17:00:00 +0000 UTC                                                                                               |
| text_fingerprint:             -773118905674708524                                                                                                         |
| elapsed_time:                 2.25 msecs                                                                                                                  |
| cpu_time:                     1.26 msecs                                                                                                                  |
| rows_returned:                1                                                                                                                           |
| deleted_rows_scanned:         0                                                                                                                           |
| optimizer_version:            7                                                                                                                           |
| optimizer_statistics_package: auto_20241122_05_36_46UTC                                                                                                   |
|

Render a latest profile for a TEXT_FINGERPRINT. It is compatible with plan linter(CLI_LINT_PLAN).

spanner> SHOW QUERY PROFILE 1603015871075919821;
+----+-----------------------------------------------------------------------------------------+---------------+------------+---------------+
| ID | Query_Execution_Plan                                                                    | Rows_Returned | Executions | Total_Latency |
+----+-----------------------------------------------------------------------------------------+---------------+------------+---------------+
| *0 | Distributed Union (distribution_table: _TopNQueryProfiles, split_ranges_aligned: false) | 24            | 1          | 3.31 msecs    |
|  1 | +- Local Distributed Union                                                              | 24            | 1          | 3.29 msecs    |
|  2 |    +- Serialize Result                                                                  | 24            | 1          | 3.28 msecs    |
| *3 |       +- Filter Scan (seekable_key_size: 0)                                             |               |            |               |
| *4 |          +- Table Scan (Table: _TopNQueryProfiles, scan_method: Scalar)                 | 24            | 1          | 0.27 msecs    |
+----+-----------------------------------------------------------------------------------------+---------------+------------+---------------+
Predicates(identified by ID):
 0: Split Range: (($interval_seconds = <scrubbed>) AND ($source = <scrubbed>) AND ($call_type = <scrubbed>))
 3: Residual Condition: (($source = <scrubbed>) AND ($call_type = <scrubbed>))
 4: Seek Condition: ($interval_seconds = <scrubbed>)

Experimental Lint Result:
 3: Filter Scan (seekable_key_size: 0)
     Residual Condition: Potentially expensive Residual Condition: Maybe better to modify it to Scan Condition

5 rows in set (8.52 msecs)
cpu time:             7.48 msecs
rows scanned:         24 rows
deleted rows scanned: 3 rows
optimizer version:    7
optimizer statistics: auto_20241128_05_46_13UTC

How to develop

Run unit tests.

$ make test

Note: It requires Docker because integration tests using testcontainers.

Or run test except integration tests.

$ make fasttest

Incompatibilities from spanner-cli

In principle, spanner-mycli accepts the same input as spanner-cli, but some compatibility is intentionally not maintained.

BEGIN RW TAG <tag> and BEGIN RO TAG <tag> are no longer supported.
- Use SET TRANSACTION TAG = "<tag>" and SET STATEMENT_TAG = "<tag>".
- Rationale: spanner-cli are broken. cloudspannerecosystem/spanner-cli#132
\G is no longer supported.
- Use SET CLI_FORMAT = "VERTICAL".
- Rationale: \G is not compatible with GoogleSQL lexical structure and memefish.
\ is no longer used for prompt expansions.
- Use % instead.
- Rationale: \ is needed to be escaped in ini files of jassevdk/go-flags.

TODO

Show secondary index by "SHOW CREATE TABLE"

Name		Name	Last commit message	Last commit date
Latest commit History 288 Commits
.github/workflows		.github/workflows
docs		docs
internal/proto/zetasql		internal/proto/zetasql
testdata		testdata
.gitignore		.gitignore
.goreleaser.yaml		.goreleaser.yaml
.goxc.json		.goxc.json
Changes.md		Changes.md
Dockerfile		Dockerfile
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
cli.go		cli.go
cli_test.go		cli_test.go
constant.go		constant.go
decoder.go		decoder.go
decoder_test.go		decoder_test.go
emulator.go		emulator.go
execute_sql.go		execute_sql.go
genai.go		genai.go
go.mod		go.mod
go.sum		go.sum
integration_test.go		integration_test.go
lint_plan.go		lint_plan.go
main.go		main.go
mutations.go		mutations.go
mutations_test.go		mutations_test.go
params.go		params.go
proto.go		proto.go
query_plan.go		query_plan.go
query_plan_test.go		query_plan_test.go
separator.go		separator.go
separator_test.go		separator_test.go
session.go		session.go
session_test.go		session_test.go
statement.go		statement.go
statement_test.go		statement_test.go
system_variables.go		system_variables.go
system_variables_test.go		system_variables_test.go
util.go		util.go
zap_logger.go		zap_logger.go

License

apstndb/spanner-mycli

Folders and files

Latest commit

History

Repository files navigation