Spanner CLI quickstart

This page introduces Spanner CLI and explains how to use it.

The Spanner CLI is a command-line interface (CLI) that lets you connect and interact with your Spanner database. It's incorporated into the Google Cloud CLI (gcloud CLI) to interface with Spanner. You can use the Spanner CLI to directly execute GoogleSQL statements in your Spanner database. Your statements can consist of data definition language (DDL), data manipulation language (DML), or data query language (DQL) statements. You can use the Spanner CLI to run SQL command scripts and automate tasks.

For more information, see a list of all supported gcloud alpha spanner cli commands .

Key benefits

The key benefits of the Spanner CLI include:

Runs DDL, DML, and DQL SQL commands.
You can write and execute SQL statements across multiple lines.
Supports meta-commands to help you with system tasks such as executing a system shell command and executing SQL from a file.
You can automate SQL executions by writing a series of SQL statements into a script file , and then instructing Spanner CLI to execute the script. In addition, you can redirect the output to an output file.
You can start an interactive Spanner CLI session , which lets you directly type SQL statements and meta-commands and see results in the CLI.

Before you begin

The Spanner CLI is available in the gcloud CLI. When you run the gcloud alpha spanner cli command for the first time, gcloud CLI automatically installs the Spanner CLI component.

Use the Spanner CLI

Set up a Google Cloud project .
Set up authentication using the gcloud CLI .
Create an instance .
Create a database .
Run the following command to start the Spanner CLI and interact with your Spanner database:
```
 gcloud  
alpha  
spanner  
cli  
 DATABASE_ID 
  
--instance = 
 INSTANCE_ID 
 
```
Replace the following:
- DATABASE_ID : the ID of the Spanner database. This is the name you used in the previous Create a database step. You can use the gcloud spanner databases list command to list the Spanner databases contained within the given instance.
- INSTANCE_ID : the ID of the Spanner instance. This is the name you used in the previous Create an instance step. You can use the gcloud spanner instances list command to list the Spanner instances contained within the given project.

Execute SQL

You can execute SQL statements in the Spanner CLI by using the execute option or using a file-based input and output method . Your SQL statements can consist of DDL, DML, or DQL.

Use the `execute` flag

To use the execute flag to execute SQL, run the following gcloud alpha spanner cli command:

 gcloud  
alpha  
spanner  
cli  
 DATABASE_ID 
  
--instance  
 INSTANCE_ID 
  
 \ 
  
--execute  
 " SQL 
"

Replace the following:

DATABASE_ID : the ID of the Spanner database that you want to connect to.
INSTANCE_ID : the ID of the Spanner instance that you want to connect to.
SQL : the SQL that you want to execute.

For example, to execute a DDL statement:

 gcloud  
alpha  
spanner  
cli  
test-database  
--instance  
test-instance  
 \ 
  
--execute  
 "CREATE TABLE Singers ( \ 
 SingerId   INT64 NOT NULL, \ 
 FirstName  STRING(1024), \ 
 LastName   STRING(1024), \ 
 SingerInfo STRING(1024), \ 
 BirthDate  DATE \ 
 ) PRIMARY KEY(SingerId);"

To execute a DML statement:

 gcloud  
alpha  
spanner  
cli  
test-database  
--instance  
test-instance  
 \ 
  
--execute  
 "INSERT INTO Singers (SingerId, FirstName, LastName, SingerInfo) \ 
 VALUES(1, 'Marc', 'Richards', 'nationality: USA'), \ 
 (2, 'Catalina', 'Smith', 'nationality: Brazil'), \ 
 (3, 'Andrew', 'Duneskipper', NULL);"

Use a file-based input and output

If you use the file-based input and output method, Spanner reads its input from a file and writes its output to another file. To use the file-based input and output method to execute SQL, run the following command:

 gcloud  
alpha  
spanner  
cli  
 DATABASE_ID 
  
--instance  
 INSTANCE_ID 
  
 \ 
  
--source  
 INPUT_FILE_PATH 
  
--tee  
 OUTPUT_FILE_PATH

You can also use the file-based redirection input and output method:

 gcloud  
alpha  
spanner  
cli  
 DATABASE_ID 
  
--instance  
 INSTANCE_ID 
  
 \ 
 < 
 INPUT_FILE_PATH 
 > 
 OUTPUT_FILE_PATH

Replace the following:

DATABASE_ID : the ID of the Spanner database that you want to connect to.
INSTANCE_ID : the ID of the Spanner instance that you want to connect to.
SOURCE_FILE_PATH : the file that contains the SQL that you want to execute.
OUTPUT_FILE_PATH : the named file to append a copy of the SQL output.

Start an interactive session

You can start an interactive Spanner CLI session, which lets you directly type SQL statements and meta-commands and see results in the CLI. To do so, run the following command:

 gcloud  
alpha  
spanner  
cli  
 DATABASE_ID 
  
--instance = 
 INSTANCE_ID

Upon successful connection between the CLI and your database, you will see a prompt (for example, spanner-cli> ) where you can do the following:

Directly type GoogleSQL statements:
Execute transactions
Use supported meta-commands

After pressing the ENTER key, the statement or command is sent to the appropriate Spanner database. Spanner then executes the statement or command.

In the following example, you start an interactive session in test-database and then execute SELECT 1; :

 gcloud  
alpha  
spanner  
cli  
test-database  
--instance  
test-instance

Welcome  
to  
Spanner-Cli  
Client.
Type  
 'help;' 
  
or  
 '\h' 
  
 for 
  
help.
Type  
 'exit;' 
  
or  
 'quit;' 
  
or  
 '\q' 
  
to  
exit.

spanner-cli>  
SELECT  
 1 
 ; 
+---+ | 
  
 | 
+---+ | 
  
 1 
  
 | 
+---+ 1 
  
rows  
 in 
  
 set 
  
 ( 
 1 
.11  
msecs )

Execute DDL statement

To execute a DDL statement, you can run the following:

 spanner-cli>  
CREATE  
TABLE  
Singers  
 ( 
  
->  
SingerId  
INT64  
NOT  
NULL,  
->  
FirstName  
STRING ( 
 1024 
 ) 
,  
->  
LastName  
STRING ( 
 1024 
 ) 
,  
->  
SingerInfo  
STRING ( 
 1024 
 ) 
,  
->  
BirthDate  
DATE  
->  
 ) 
  
PRIMARY  
KEY ( 
SingerId ) 
 ; 
Query  
OK,  
 0 
  
rows  
affected  
 ( 
 17 
.08  
sec )

Execute DML statement

To execute a DML statement, you can run the following:

 spanner-cli>  
INSERT  
INTO  
Singers  
 ( 
SingerId,  
FirstName,  
LastName,  
SingerInfo ) 
  
->  
VALUES ( 
 1 
,  
 'Marc' 
,  
 'Richards' 
,  
 'nationality: USA' 
 ) 
,  
->  
 ( 
 2 
,  
 'Catalina' 
,  
 'Smith' 
,  
 'nationality: Brazil' 
 ) 
,  
->  
 ( 
 3 
,  
 'Andrew' 
,  
 'Duneskipper' 
,  
NULL ) 
 ; 
Query  
OK,  
 3 
  
rows  
affected  
 ( 
 0 
.32  
sec )

Supported meta-commands

The Spanner CLI supports utility meta-commands, which are commands that operate on the client, in this case the Spanner CLI. The following meta-commands are supported in the Spanner CLI:

Use	`\u`	Connects to another database. Takes the new database name as an argument.

What's next

See a list of all supported gcloud alpha spanner cli commands .