README
¶
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 PROTOandSHOW REMOTE PROTOstatement- Can use embedded emulator (
--embedded-emulator) - Support query parameters
- Test root-partitionable with
TRY PARTITIONED QUERY <sql>command
- Respects training and verification use-cases.
- gRPC logging(
--log-grpc) - Support mutations
- gRPC logging(
- Respects batch use cases as well as interactive use cases
- More
gcloud spanner databases execute-sqlcompatibilities- Support compatible flags (
--sql,--query-mode,--strong,--read-timestamp)
- Support compatible flags (
- More
gcloud spanner databases ddl updatecompatibilities- Support
--proto-descriptor-fileflag
- Support
- Generalized concepts to extend without a lot of original syntax
- Generalized system variables concept inspired by Spanner JDBC properties
SET <name> = <value>statementSHOW VARIABLESstatementSHOW VARIABLE <name>statement--set <name>=<value>flag
- Generalized system variables concept inspired by Spanner JDBC properties
- Improved interactive experience
- Use
hymkor/go-multiline-nyinstead ofchzyer/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
- Use
- Autowrap and auto adjust column width to fit within terminal width.
- Progress bar of DDL execution.
- Use
- Utilize other libraries
- Dogfooding
cloudspannerecosystem/memefish- Spin out memefish logic as
apstndb/gsqlutils.
- Spin out memefish logic as
- Utilize
apstndb/spantypeandapstndb/spanvalue
- Dogfooding
Install
Install Go and run the following command.
# For Go 1.16+
go install github.com/apstndb/spanner-mycli@latest
Or you can build a docker image.
git clone https://github.com/apstndb/spanner-mycli.git
cd spanner-mycli
docker build -t 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
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 mistypeus-east1asus-east-1, the connection will succeed, but directed reads will not be enabled.To perform directed reads to
asia-northeast2in a multi-region configurationasia1, you need to specifyasia-northeast2orasia-northeast2:READ_WRITE. Since the replicas placed inasia-northeast2are READ_WRITE replicas, directed reads will not be enabled if you specifyasia-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.
\Gdelimiter 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 Read-Write Transaction | BEGIN [RW] [PRIORITY {HIGH|MEDIUM|LOW}] [TAG <tag>]; |
See Request Priority for details on the priority. The tag you set is used as both transaction tag and request tag. See also Transaction Tags and Request Tags. |
| Commit Read-Write Transaction | COMMIT; |
|
| Rollback Read-Write Transaction | ROLLBACK; |
|
| Start Read-Only Transaction | BEGIN RO [{<seconds>|<RFC3339-formatted time>}] [PRIORITY {HIGH|MEDIUM|LOW}] [TAG <tag>]; |
<seconds> and <RFC3339-formatted time> is used for stale read. See Request Priority for details on the priority. The tag you set is used as request tag. See also Transaction Tags and Request Tags. |
| End Read-Only Transaction | CLOSE; |
|
| 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; |
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
%Pis substituted with padding to align the primary prompt.%Ris 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.cnfin current directory.spanner_mycli.cnfin 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
In a read-write transaction, you can add a tag following BEGIN RW TAG <tag>.
spanner-mycli adds the tag set in BEGIN RW TAG as a transaction tag.
The tag will also be used as request tags within the transaction.
# Read-write transaction
# transaction_tag = tx1
+--------------------+
| BEGIN RW TAG tx1; |
| |
| SELECT val |
| FROM tab1 +-----request_tag = tx1
| WHERE id = 1; |
| |
| UPDATE tab1 |
| SET val = 10 +-----request_tag = tx1
| WHERE id = 1; |
| |
| COMMIT; |
+--------------------+
In a read-only transaction, you can add a tag following BEGIN RO TAG <tag>.
Since read-only transaction doesn't support transaction tag, spanner-mycli adds the tag set in BEGIN RO TAG as request tags.
# Read-only transaction
# transaction_tag = N/A
+--------------------+
| BEGIN RO TAG tx2; |
| |
| SELECT SUM(val) |
| FROM tab1 +-----request_tag = tx2
| WHERE id = 1; |
| |
| CLOSE; |
+--------------------+
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 |
|---|---|---|
| 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" |
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?
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
TODO
- Show secondary index by "SHOW CREATE TABLE"
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.
Disclaimer
Do not use this tool for production databases as the tool is experimental/alpha quality forever.
Documentation
¶
Overview ¶
Package main is a command line tool for Cloud Spanner
InterceptorLogger adapts zap logger to interceptor logger.
Source Files
¶
Directories
Click to show internal directories.
Click to hide internal directories.