Quickstart using the console
This quickstart shows you how to perform basic operations in Cloud Spanner using the Google Cloud console. In the quickstart, you will:
- Create a Cloud Spanner instance, database, and table.
- Add a schema.
- Write and modify data.
- Run a query.
Before you begin
- Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.
-
Enable the Cloud Spanner API for the project.
Create an instance
When you first use Cloud Spanner, you must create an instance, which is an allocation of resources that are used by Cloud Spanner databases in that instance.
Go to the Cloud Spanner Instances page in the Google Cloud console.
Click Create instance.
For the instance name, enter
Test Instance
.For the instance ID, enter
test-instance
.Use a Regional configuration.
Choose any regional configuration from the drop-down menu.
Your instance configuration determines the geographic location where your instances are stored and replicated.
Use the default compute capacity of 1000 processing units.
Your instance creation page now looks like this:
Click Create.
Your instance appears in the instance list.
Create a database
Go to the Cloud Spanner Instances page in the Google Cloud console.
Click the Test Instance instance.
Click Create database.
For the database name, enter
example-db
.Your database creation page now looks like this:
Select a database dialect.
For information about support for PostgreSQL and for guidance for choosing a dialect, see PostgreSQL interface.
If you selected Google Standard SQL, leave the Define your schema text field blank for now. You'll define your schema in the next section of this quickstart.
Click Create.
The Google Cloud console displays the Overview page for the database you created.
Create a schema for your database
On the Overview page for
example-db
, click Create table at the start of the list of tables.The Google Cloud console displays the Write DDL statements page.
-
Google Standard SQL
CREATE TABLE Singers ( SingerId INT64 NOT NULL, FirstName STRING(1024), LastName STRING(1024), SingerInfo BYTES(MAX), BirthDate DATE, ) PRIMARY KEY(SingerId);
PostgreSQL
CREATE TABLE Singers ( SingerId BIGINT PRIMARY KEY, FirstName VARCHAR(1024), LastName VARCHAR(1024), SingerInfo BYTEA, BirthDate TIMESTAMPTZ );
Click Submit.
The Google Cloud console returns to the
example-db
Overview page and shows that schema updates are underway. When the update is complete, the page looks like this:Google Standard SQL
PostgreSQL
Notice that PostgreSQL converts the table name to all lower case.
Insert and modify data
The Google Cloud console provides an interface for inserting, editing, and deleting data.
Insert data
On the
example-db
Overview page, click theSingers
table in the list of tables.The Google Cloud console displays the
Singers
table's Schema page.In the navigation menu, click Data to display the
Singers
table's Data page.Click Insert.
The Google Cloud console displays the
Singers
table's Query page with a new query tab containing templateINSERT
andSELECT
statements you edit to insert a row in theSingers
table and view the result of that insertion:Google Standard SQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO Singers (SingerId, BirthDate, FirstName, LastName, SingerInfo) VALUES (<SingerId>, -- type: INT64 <BirthDate>, -- type: DATE <FirstName>, -- type: STRING(1024) <LastName>, -- type: STRING(1024) <SingerInfo> -- type: BYTES(MAX) ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM Singers WHERE SingerId=<SingerId>;
PostgreSQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO singers (singerid, birthdate, firstname, lastname, singerinfo) VALUES (<singerid>, -- type: bigint <birthdate>, -- type: timestamp with time zone <firstname>, -- type: character varying <lastname>, -- type: character varying <singerinfo> -- type: bytea ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM singers WHERE singerid=<singerid>;
Notice that PostgreSQL converts the column names to all lower case.
Edit the
INSERT
statement'sVALUES
clause and theSELECT
statement'sWHERE
clause:Google Standard SQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO Singers (SingerId, BirthDate, FirstName, LastName, SingerInfo) VALUES (1, -- type: INT64 NULL, -- type: DATE 'Marc', -- type: STRING(1024) 'Richards', -- type: STRING(1024) NULL -- type: BYTES(MAX) ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM Singers WHERE SingerId=1;
PostgreSQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO singers (singerid, birthdate, firstname, lastname, singerinfo) VALUES (1, -- type: bigint NULL, -- type: timestamp with time zone 'Marc', -- type: character varying 'Richards', -- type: character varying NULL -- type: bytea ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM singers WHERE singerid=1;
Click Run.
Cloud Spanner runs the statements. When finished, the Results tab indicates that the first statement inserted one row, and provides a link to view the table's data.
In the Results tab, click the table link. The
Singers
table now has one row:Google Standard SQL
PostgreSQL
Click Insert to add an additional row.
The Google Cloud console again displays the
Singers
table's Query page with a new query tab containing the same templateINSERT
andSELECT
statements.Edit the
INSERT
statement'sVALUES
clause and theSELECT
statement'sWHERE
clause:Google Standard SQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO Singers (SingerId, BirthDate, FirstName, LastName, SingerInfo) VALUES (2, -- type: INT64 NULL, -- type: DATE 'Catalina', -- type: STRING(1024) 'Smith', -- type: STRING(1024) NULL -- type: BYTES(MAX) ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM Singers WHERE SingerId=2;
PostgreSQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO singers (singerid, birthdate, firstname, lastname, singerinfo) VALUES (2, -- type: bigint NULL, -- type: timestamp with time zone 'Catalina', -- type: character varying 'Smith', -- type: character varying NULL -- type: bytea ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM singers WHERE singerid=2;
Click Run.
After Cloud Spanner runs the statements, the Results tab again indicates that the first statement inserted one row.
Click the table link. The
Singers
table now has two rows:Google Standard SQL
PostgreSQL
You can also insert empty string values when you enter data.
Click Insert to add row.
The Google Cloud console again displays the
Singers
table's Query page with a new query tab containing the same templateINSERT
andSELECT
statements.Edit the template
INSERT
statement'sVALUES
clause andSELECT
statement'sWHERE
clause:Google Standard SQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO Singers (SingerId, BirthDate, FirstName, LastName, SingerInfo) VALUES (3, -- type: INT64 NULL, -- type: DATE 'Kena', -- type: STRING(1024) '', -- type: STRING(1024) NULL -- type: BYTES(MAX) ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM Singers WHERE SingerId=3;
PostgreSQL
-- Add new values in the VALUES clause in order of the column list. -- Each value must be type compatible with its associated column. INSERT INTO singers (singerid, birthdate, firstname, lastname, singerinfo) VALUES (3, -- type: bigint NULL, -- type: timestamp with time zone 'Kena', -- type: character varying '', -- type: character varying NULL -- type: bytea ); -- Change values in the WHERE condition to match the inserted row. SELECT * FROM singers WHERE singerid=3;
Notice that the value provided for the last name column is an empty string,
''
, not aNULL
value.Click Run.
After Cloud Spanner runs the statements, the Results tab again indicates that the first statement inserted one row.
Click the table link. The
Singers
table now has three rows, and the row with the primary key value of3
has a last name column that is an empty string:Google Standard SQL
PostgreSQL
Edit data
On the
Singers
table's Data page, check the box next to the row with the primary key value of3
, and then click Edit.The Google Cloud console displays the Query page with a new tab containing template
UPDATE
andSELECT
statements that you edit. Note that theWHERE
clauses of both statements indicate that the row to update is the one with the primary key value of3
.Google Standard SQL
-- Change values in the SET clause to update the row where the WHERE condition is true. UPDATE Singers SET BirthDate='', FirstName='Kena', LastName='', SingerInfo='' WHERE SingerId=3; SELECT * FROM Singers WHERE SingerId=3;
PostgreSQL
-- Change values in the SET clause to update the row where the WHERE condition is true. UPDATE singers SET birthdate='', firstname='Kena', lastname='', singerinfo='' WHERE singerid='3'; SELECT * FROM singers WHERE singerid='3';
Edit the
UPDATE
statement'sSET
clause to update only the birth date:Google Standard SQL
-- Change values in the SET clause to update the row where the WHERE condition is true. UPDATE Singers SET BirthDate='1961-04-01' WHERE SingerId=3; SELECT * FROM Singers WHERE SingerId=3;
PostgreSQL
-- Change values in the SET clause to update the row where the WHERE condition is true. UPDATE singers SET birthdate='1961-04-01 00:00:00 -8:00' WHERE singerid='3'; SELECT * FROM singers WHERE singerid='3';
Click Run.
Cloud Spanner runs the statements. When finished, the Results tab indicates that the first statement updated one row, and provides a link to view the table's data.
In the Results tab, click the table link.
The updated row now has a value for the birth date.
Google Standard SQL
PostgreSQL
Delete data
- On the
Singers
table's Data page, check the box next to the row with2
in the first column, and then click Delete. In the dialog that appears, click Confirm.
The
Singers
table now has two rows:Google Standard SQL
PostgreSQL
Run a query
On the
example-db
Overview page, click Query in the navigation menu to display the Query page forexample-db
.Click New tab to create a new query tab. Then, enter this query:
Google Standard SQL
SELECT * FROM Singers;
PostgreSQL
SELECT * FROM singers;
Click Run.
Cloud Spanner runs the query. When finished, the Results tab displays the result of your query:
Google Standard SQL
PostgreSQL
Congratulations! You've successfully created a Cloud Spanner database and executed a SQL statement using the query editor!
Cleanup
To avoid additional charges to your Cloud Billing account, delete the database and the instance that you created.
Delete the database
Go to the Cloud Spanner Instances page in the Google Cloud console.
Click Test Instance.
Click
example-db
.In the Database details page, click delete DELETE DATABASE.
Confirm that you want to delete the database and click Delete.
Delete the instance
Deleting an instance automatically deletes all databases created in that instance.
Go to the Cloud Spanner Instances page in the Google Cloud console.
Click Test Instance.
Click delete DELETE INSTANCE.
Confirm that you want to delete the instance and click Delete.
What's next
- Learn about Instances.
- Understand the Cloud Spanner Schema and Data Model.
- Learn more about Data Definition Language (DDL).
- Learn more about Query Execution Plans.
- Learn how to use Cloud Spanner with C++, C#, Go, Java, Node.js, PHP, Python, Ruby, REST, or gcloud.