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

The Spanner CLI is a command-line interface (CLI) that lets you connect andinteract with your Spanner database. It's incorporated into theGoogle Cloud CLI (gcloud CLI) tointerface with Spanner. You can use the Spanner CLI todirectly executeGoogleSQLstatements in your Spanner database. Your statements canconsist of data definition language (DDL), data manipulation language(DML), or data query language (DQL) statements. You can use the Spanner CLIto run SQL command scripts and automate tasks.

It is based on the open sourcespanner-cliproject.

For more information, see a list of all supportedgcloud spanner cli commands.

Key benefits

You can use the Spanner to perform the following actions:

• Run DDL, DML, and DQL SQL commands.
• Write and execute SQL statements across multiple lines.
• Usemeta-commands to help with system tasks suchas executing a system shell command and executing SQL from a file.
• Automate SQL executions bywriting a series of SQL statements into a script file, andthen instructing Spanner CLI to execute the script. In addition, you canredirect the output to an output file.
• Start an interactive Spanner CLI session,which lets you directly type SQL statements and meta-commands and see resultsin the CLI.

Before you begin

Before using the Spanner CLI, ensure that you have the required roleand have installed the CLI.

Required roles

To get the permissions that you need to install the Spanner, ask your administrator to grant you theCloud Spanner Admin (roles/spanner.admin) IAM role on Spanner. For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Install the Spanner CLI

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

To install the Spanner CLI manually, run the following command:

gcloud components install spanner-cli

apt-getinstallgoogle-cloud-cli-spanner-cli

1. Set up a Google Cloud project.

2. Set up authentication using the gcloud CLI.

3. Create an instance.

4. Create a database.

5. Run the following command to start the Spanner CLI and interact with yourSpanner database:

   gcloudspannercliDATABASE_ID--instance=INSTANCE_ID

   Replace the following:

   • DATABASE_ID: the ID of the Spannerdatabase. This is the name you used in the previous Create a databasestep. You can use thegcloud spanner databases listcommand to list the Spanner databases contained withinthe given instance.
   • INSTANCE_ID: the ID of the Spannerinstance. This is the name you used in the previous Create an instancestep. You can use thegcloud spanner instances listcommand to list the Spanner instances contained withinthe given project.

Execute SQL

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

Use theexecute flag

To use theexecute flag to execute SQL, run the followinggcloud spanner cli command:

gcloudspannercliDATABASE_ID--instanceINSTANCE_ID\--execute"SQL"

Replace the following:

• DATABASE_ID: the ID of the Spannerdatabase that you want to connect to.
• INSTANCE_ID: the ID of the Spannerinstance that you want to connect to.
• SQL: the SQL that you want to execute.

For example, to execute a DDL statement:

gcloudspannerclitest-database--instancetest-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:

gcloudspannerclitest-database--instancetest-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 readsits input from a file and writes its output to another file. To use thefile-based input and output method to execute SQL, run the following command:

gcloudspannercliDATABASE_ID--instanceINSTANCE_ID\--sourceINPUT_FILE_PATH--teeOUTPUT_FILE_PATH

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

gcloudspannercliDATABASE_ID--instanceINSTANCE_ID\    <INPUT_FILE_PATH >OUTPUT_FILE_PATH

Replace the following:

• DATABASE_ID: the ID of the Spannerdatabase that you want to connect to.
• INSTANCE_ID: the ID of the Spannerinstance that you want to connect to.
• SOURCE_FILE_PATH: the file that contains the SQL thatyou want to execute.
• OUTPUT_FILE_PATH: the named file to append a copy of theSQL output.

Start an interactive session

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

gcloudspannercliDATABASE_ID--instance=INSTANCE_ID

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

• Directly type GoogleSQL statements:
  • GoogleSQL DDL reference
  • GoogleSQL DML reference
  • GoogleSQL query syntax
• Execute transactions
• Use supportedmeta-commands

After pressing theENTER key, the statement or command is sent to theappropriate Spanner database. Spanner thenexecutes the statement or command.

In the following example, you start an interactive session intest-databaseand then executeSELECT 1;:

gcloud spanner cli test-database --instance test-instanceWelcome 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)

Execute partitioned DML statement

In the Spanner CLI, you can use thePARTITIONED keyword with theUPDATEandDELETE commands to run efficient, large-scalepartitioned DMLstatements. When the Spanner CLI encountersPARTITIONED UPDATE orPARTITIONED DELETE, it recognizes them as partitioned DML operations. Thesekeywords are useful for operations that affect a significant portion of a tablewithout locking the entire table for an extended period. Spannerdoesn't apply the partitioned DML statements atomically across the entire table.It applies partitioned DML statements atomically across each partition.

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

-- Update all rows in the 'Products' table by multiplying the price by 2spanner-cli> PARTITIONED UPDATE Products SET Price = Price * 2 WHERE Price > 100;-- Delete all rows in the 'Products' table with price less than 500spanner-cli> PARTITIONED DELETE FROM Products WHERE Price < 500;

Supported meta-commands

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

Additional supported commands

The Spanner CLI supports additional commands. For more information, seeSpanner CLI commands.

Get support

To report an issue with the Spanner CLI, create a newissue.

What's next

• See a list of all supportedSpanner CLI commands.
• See a list of all supportedgcloud spanner cli commands.