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

  1. 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.
  2. In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

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

    Go to project selector

  5. Make sure that billing is enabled for your Cloud project. Learn how to confirm that billing is enabled for your project.

  6. Enable the Cloud Spanner API for the project.

    Enable the Cloud Spanner API

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.

  1. Go to the Cloud Spanner Instances page in the Cloud Console.

    Go to the instances page

  2. Click Create instance.

  3. For the instance name, enter Test Instance.

  4. For the instance ID, enter test-instance.

  5. Use a Regional configuration.

  6. Choose any regional configuration from the drop-down menu.

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

  7. Use default compute capacity of 1000 processing units.

    Your instance creation page now looks like this:

    Screenshot of the instance creation window

  8. Click Create.

    Your instance appears in the instance list.

Create a database

  1. Go to the Cloud Spanner Instances page in the Cloud Console.

    Go to the instances page

  2. Click the Test Instance instance.

  3. Click Create database.

  4. For the database name, enter example-db.

    Your database creation page now looks like this:

    Screenshot of the database creation window

  5. Select a database dialect.

    For information about support for PostgreSQL and for guidance for choosing a dialect, see PostgreSQL interface.

  6. If you selected Google SQL, leave the Define your schema text field blank for now. You'll define your schema in the next section of this quickstart.

  7. Click Create.

    The Cloud Console displays the Overview page for the database you created.

Create a schema for your database

  1. On example-db's Overview page, click Create table at the start of the list of tables.

    The Cloud Console displays the Write DDL statements page.

  2. Enter:

    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
    );
    
  3. Click Submit.

    The Cloud Console returns to example-db's Overview page and shows that Schema updates are underway. When the update is complete, the page looks like this:

    Google Standard SQL

    Screenshot of table

    PostgreSQL

    Screenshot of table

    Notice that PostgreSQL converts the table name to all lower case.

Insert and modify data

The Cloud Console provides an interface for inserting, editing, and deleting data.

Insert data

  1. On example-db's Overview page, click the Singers table in the list of tables.

    The Cloud Console displays the Singers table's Schema page.

  2. In the side menu, click Data to display the Singers table's Data page.

  3. Click Insert.

    The Cloud Console displays the Singers table's Query page with a new query tab containing template INSERT and SELECT statements you edit to insert a row in the Singers 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.

  4. Edit the INSERT statement's VALUES clause and the SELECT statement's WHERE 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;
    
  5. 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.

  6. In the Results tab, click the table link. The Singers table now has one row:

    Google Standard SQL

    Screenshot of Singers table data with one row

    PostgreSQL

    Screenshot of Singers table data with one row

  7. Click Insert to add an additional row.

    The Cloud Console again displays the Singers table's Query page with a new query tab containing the same template INSERT and SELECT statements.

  8. Edit the INSERT statement's VALUES clause and the SELECT statement's WHERE 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;
    
  9. Click Run.

    After Cloud Spanner runs the statements, the Results tab again indicates that the first statement inserted one row.

  10. Click the table link. The Singers table now has two rows:

    Google Standard SQL

    Screenshot of Singers table data with two rows

    PostgreSQL

    Screenshot of Singers table data with two rows

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

  1. Click Insert to add row.

    The Cloud Console again displays the Singers table's Query page with a new query tab containing the same template INSERT and SELECT statements.

  2. Edit the template INSERT statement's VALUES clause and SELECT statement's WHERE 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 a NULL value.

  3. Click Run.

    After Cloud Spanner runs the statements, the Results tab again indicates that the first statement inserted one row.

  4. Click the table link. The Singers table now has three rows, and the row with the primary key value of 3 has a last name column that is an empty string:

    Google Standard SQL

    Screenshot of Singers table data with three rows

    PostgreSQL

    Screenshot of Singers table data with three rows

Edit data

  1. On the Singers table's Data page, check the box next to the row with the primary key value of 3, and then click Edit.

    The Cloud Console displays the Query page with a new tab containing template UPDATE and SELECT statements that you edit. Note that the WHERE clauses of both statements indicate that the row to update is the one with the primary key value of 3.

    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';
    
  2. Edit the UPDATE statement's SET 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'; 
    
  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.

  4. In the Results tab, click the table link.

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

    Google Standard SQL

    Screenshot of Singers table data with an updated row

    PostgreSQL

    Screenshot of Singers table data with an updated row

Delete data

  1. On the Singers table's Data page, check the box next to the row with 2 in the first column, and then click Delete.
  2. In the dialog that appears, click Confirm.

    The Singers table now has two rows:

    Google Standard SQL

    Screenshot of Singers table data with two rows; the row for SingerId 2 is gone

    PostgreSQL

    Screenshot of Singers table data with two rows; the row for SingerId 2 is gone

Run a query

  1. On example-db's Overview page, click Query in the side menu to display example-db's Query page.

  2. Click New tab to create a new query tab. Then, enter this query:

    Google Standard SQL

    SELECT * FROM Singers;
    

    PostgreSQL

    SELECT * FROM singers;
    
  3. Click Run.

    Cloud Spanner runs the query. When finished, the Results tab displays the result of your query:

    Google Standard SQL

    Screenshot of query results

    PostgreSQL

    Screenshot of query results

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

  1. Go to the Cloud Spanner Instances page in the Google Cloud Console.

    Go to the instances page

  2. Click Test Instance.

  3. Click example-db.

  4. In the Database details page, click DELETE DATABASE.

  5. 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.

  1. Go to the Cloud Spanner Instances page in the Google Cloud Console.

    Go to the instances page

  2. Click Test Instance.

  3. Click DELETE INSTANCE.

  4. Confirm that you want to delete the instance and click Delete.

What's next