This quickstart shows you how to perform basic operations in Spannerby using the Google Cloud console. In the quickstart, you will:

• Create a Spanner instance.
• Create a database.
• Create a schema.
• Insert and modify data.
• Run a query.

For information on the cost of using Spanner, seePricing.

Note: To explore Spanner using a 90-day free trial instance,seeCreate a Spanner free trial instance.

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.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

1. Optional: The Spanner API should be auto-enabled. If not, enable it manually:
   Enable Spanner API

2. To get the permissions that you need to create instances and databases, ask your administrator to grant you the Cloud Spanner Admin (roles/spanner.admin) IAM role on your project.

Create an instance

When you first use Spanner, you must create an instance, which is anallocation of resources that are used by Spanner databases in thatinstance.

1. In the Google Cloud console, go to theSpanner page.

   Go to Spanner

2. Select or create a Google Cloud project if you haven't done so already.

3. On theSpanner page, clickCreate a provisioned instance.

   If you've used Spanner before, you'll see theSpannerInstances page instead of the product page. ClickCreate instance.

4. In theName your instance page, enter an instance name,such asTest Instance.

5. The instance ID is automatically entered based on the instance name,for example, astest-instance. Change it, if required. ClickContinue.

6. In theConfigure your instance page, retain the default optionRegional and select a configuration from the drop-down menu.

   Your instance configuration determines the geographic location where yourinstances are stored and replicated.

7. ClickContinue.

8. In theAllocate compute capacity page, selectProcessing units (PUs)and retain the default value of 1000 processing units.

9. ClickCreate.

   The Google Cloud console displays theOverview page for the instanceyou created.

Create a database

1. In the Google Cloud console, go to theSpanner Instances page.

   Go to Spanner instances

2. Click the instance you created, for exampleTest Instance.

3. In the instance Overview page that opens, clickCreate database.

4. For the database name, enter a name, such asexample-db.

5. Select a database dialect.

   For information about support for PostgreSQL and for guidance forchoosing a dialect, seePostgreSQL interface.If you selected GoogleSQL, you'll define the schema in theDefine your schema text field in the next section of this quickstart.

   Your database creation page now looks like this:

   

6. ClickCreate.

   The Google Cloud console displays theOverview page for the databaseyou created.

Create a schema for your database

Note: Spanner Studio (formerly labeledQuery in the Google Cloud console)supports SQL, DML, and DDL operations in a single editor. For more information,seeManage your data using the Google Cloud console.

1. In the navigation menu, clickSpanner Studio.

2. In theSpanner Studio page, clickaddNew tab or use the empty editor tab.

3. Enter:

   GoogleSQL

   CREATETABLESingers(SingerIdINT64NOTNULL,FirstNameSTRING(1024),LastNameSTRING(1024),SingerInfoBYTES(MAX),BirthDateDATE)PRIMARYKEY(SingerId);

   PostgreSQL

   CREATETABLESingers(BirthDateTIMESTAMPTZ,SingerIdBIGINTPRIMARYKEY,FirstNameVARCHAR(1024),LastNameVARCHAR(1024),SingerInfoBYTEA);

4. Clickplay_circleRun.

   The Google Cloud console returns to the databaseOverview page andshows thatSchema updates are underway. When the update is complete, thepage looks like this:

   GoogleSQL

   

   PostgreSQL

   

   Notice that PostgreSQL converts the table name to lowercase.

Insert and modify data

The Google Cloud console provides an interface for inserting, editing, anddeleting data.

Insert data

1. In the list of tables on the databaseOverview page, click the Singers table.

   The Google Cloud console displays the Singers table'sSchema page.

2. In the navigation menu, clickData to display the Singers table'sData page.

3. ClickInsert.

   The Google Cloud console displays the Singers table's Spanner Studiopage with a new query tab that contains theINSERT statement thatyou edit to insert a row in the Singers table and view the result of thatinsertion:

   GoogleSQL

   -- Add new values in the VALUES clause in order of the column list.-- Each value must be type compatible with its associated column.INSERTINTOSingers(SingerId,FirstName,LastName,SingerInfo,BirthDate)VALUES(<SingerId>,-- type: INT64<FirstName>,-- type: STRING(1024)<LastName>,-- type: STRING(1024)<SingerInfo>,-- type: BYTES(MAX)<BirthDate>-- type: DATE)THENRETURNSingerId,FirstName,LastName,SingerInfo,BirthDate;

   PostgreSQL

   -- Add new values in the VALUES clause in order of the column list.-- Each value must be type compatible with its associated column.INSERTINTOsingers(singerid,firstname,lastname,singerinfo,birthdate)VALUES(<singerid>,-- type: bigint<firstname>,-- type: character varying<lastname>,-- type: character varying<singerinfo>,-- type: bytea<birthdate>-- type: timestamp with time zone);THENRETURNsingerid,firstname,lastname,singerinfo,birthdate;

   Notice that PostgreSQL converts the column names to all lowercase.

4. Edit theINSERT statement'sVALUES clause.

   GoogleSQL

   -- Add new values in the VALUES clause in order of the column list.-- Each value must be type compatible with its associated column.INSERTINTOSingers(SingerId,BirthDate,FirstName,LastName,SingerInfo)VALUES(1,-- type: INT64NULL,-- type: DATE'Marc',-- type: STRING(1024)'Richards',-- type: STRING(1024)NULL-- type: BYTES(MAX))THENRETURNSingerId,FirstName,LastName,SingerInfo,BirthDate;

   PostgreSQL

   -- Add new values in the VALUES clause in order of the column list.-- Each value must be type compatible with its associated column.INSERTINTOsingers(singerid,birthdate,firstname,lastname,singerinfo)VALUES(1,-- type: bigintNULL,-- type: timestamp with time zone'Marc',-- type: character varying'Richards',-- type: character varyingNULL-- type: bytea);THENRETURNsingerid,firstname,lastname,singerinfo,birthdate;

5. Clickplay_circleRun.

   Spanner runs the statements. When finished, theResultstab shows that the statement inserted one row:

   GoogleSQL

   

   PostgreSQL

   

6. In the Explorer, clickmore_vertView actions next to theSingers table, and then clickInsert data.

7. Edit theINSERT statement'sVALUES clause and theSELECT statement'sWHERE clause:

   GoogleSQL

   -- Add new values in the VALUES clause in order of the column list.-- Each value must be type compatible with its associated column.INSERTINTOSingers(SingerId,BirthDate,FirstName,LastName,SingerInfo)VALUES(2,-- type: INT64NULL,-- 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*FROMSingersWHERESingerId=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.INSERTINTOsingers(singerid,birthdate,firstname,lastname,singerinfo)VALUES(2,-- type: bigintNULL,-- type: timestamp with time zone'Catalina',-- type: character varying'Smith',-- type: character varyingNULL-- type: bytea);-- Change values in the WHERE condition to match the inserted row.SELECT*FROMsingersWHEREsingerid=2;

8. Clickplay_circleRun.

   After Spanner runs the statements, theResults tabshows that the statement inserted one row.

9. In the Explorer, clickmore_vertView actions next to theSingers table, and then clickPreview Data.

10. Clickplay_circleRun. The Singers table now has two rows:

    GoogleSQL

    

    PostgreSQL

    

You can also insert empty string values when you enter data.

1. ClickInsert to add a row.

   Spanner again displays the Singers table'sSpanner Studiopage with a new query tab that contains the sameINSERT andSELECTstatements.

2. Edit the templateINSERT statement'sVALUES clause andSELECT statement'sWHERE clause:

   GoogleSQL

   -- Add new values in the VALUES clause in order of the column list.-- Each value must be type compatible with its associated column.INSERTINTOSingers(SingerId,BirthDate,FirstName,LastName,SingerInfo)VALUES(3,-- type: INT64NULL,-- 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*FROMSingersWHERESingerId=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.INSERTINTOsingers(singerid,birthdate,firstname,lastname,singerinfo)VALUES(3,-- type: bigintNULL,-- type: timestamp with time zone'Kena',-- type: character varying'',-- type: character varyingNULL-- type: bytea);-- Change values in the WHERE condition to match the inserted row.SELECT*FROMsingersWHEREsingerid=3;

   Notice that the value provided for the last name column is an empty string,'', not aNULL value.

3. Clickplay_circleRun.

   After Spanner runs the statements, theResults tabshows that the statement inserted one row.

4. In the Explorer, clickmore_vertView actions next to theSingers table, and then clickPreview Data.

5. Clickplay_circleRun. TheSingers table now has three rows, and therow with the primary key value of3 has an empty string in theLastNamecolumn:

   GoogleSQL

   

   PostgreSQL

   

Edit data

1. On the Singers table'sData page, select the checkbox on the row with the primary key value of3, and then clickEdit.

   The Spanner displays theSpanner Studio page with anew tab containing templateUPDATE andSET statements that you can edit.Note that theWHERE clauses of both statements indicate that the row toupdate is the one with the primary key value of3.

   GoogleSQL

   -- Change values in the SET clause to update the row where the WHERE condition is true.UPDATESingersSETBirthDate='',FirstName='Kena',LastName='',SingerInfo=''WHERESingerId=3;SELECT*FROMSingersWHERESingerId=3;

   PostgreSQL

   -- Change values in the SET clause to update the row where the WHERE condition is true.UPDATEsingersSETbirthdate=NULL,firstname='Kena',lastname='',singerinfo=NULLWHEREsingerid='3';SELECT*FROMsingersWHEREsingerid='3';

2. Edit theUPDATE statement'sSET clause to update only the birth date:

   GoogleSQL

   -- Change values in the SET clause to update the row where the WHERE condition is true.UPDATESingersSETBirthDate='1961-04-01'WHERESingerId=3;SELECT*FROMSingersWHERESingerId=3;

   PostgreSQL

   -- Change values in the SET clause to update the row where the WHERE condition is true.UPDATEsingersSETbirthdate='1961-04-01 00:00:00 -8:00'WHEREsingerid='3';SELECT*FROMsingersWHEREsingerid='3';

3. Clickplay_circleRun.

   Spanner runs the statements. When finished, theResultstab shows that the first statement updated one row.

4. In the Explorer, clickmore_vertView actions next to theSingers table, and then clickPreview Data.

5. Clickplay_circleRun. The updated row now has a value for the birth date.

   GoogleSQL

   

   PostgreSQL

   

Delete data

1. On the Singers table'sData page, select the checkbox on the row with2 in the first column, and then clickDelete.

2. In the dialog that appears, clickConfirm.

   The Singers table now has two rows:

   GoogleSQL

   

   PostgreSQL

   

Run a query

1. On the databaseOverview page, clickSpanner Studio in thenavigation menu.

2. ClickNew tab to create a new query tab. Then, enter the following queryin the query editor:

   GoogleSQL

   SELECT*FROMSingers;

   PostgreSQL

   SELECT*FROMsingers;

3. Clickplay_circleRun.

   Spanner runs the query. When finished, theResultstab displays the result of your query:

   GoogleSQL

   

   PostgreSQL

   

Congratulations! You've successfully created a Spanner databaseand executed a SQL statement by using the query editor!

Clean up

To avoid incurring additional charges to your Google Cloud account, deletethe database and instance. Disabling the Cloud Billing API doesn't stopcharges. When you delete an instance, all databases in the instance are deleted.

Delete the database

1. In the Google Cloud console, go to theSpanner Instances page.

   Go to Spanner instances

2. Click the name of the instance that has the database that you want to delete,for example,Test Instance.

3. Click the name of the database that you want to delete, for example,example-db.

4. In theDatabase details page, clickdeleteDelete database.

5. Confirm that you want to delete the database by entering the database nameand clickingDelete.

Delete the instance

1. In the Google Cloud console, go to theSpanner Instances page.

   Go to Spanner instances

2. Click the name of the instance that you want to delete, for example,Test Instance.

3. ClickdeleteDelete instance.

4. Confirm that you want to delete the instance by entering the instance name and clickingDelete.

What's next

• Learn aboutInstances.
• Understand the SpannerSchema and Data Model.
• Learn more aboutGoogleSQL Data Definition Language (DDL).
• Learn more aboutQuery Execution Plans.
• Learn how to use Spanner withC++,C#,Go,Java,Node.js,PHP,Python,Ruby,REST, orgcloud.