Create and query a database by using the Google Cloud console

Create and query a database by using the Google Cloud console

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

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

Note: For information on the cost of using Cloud Spanner, see Pricing.

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.

   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

3. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

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

   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

5. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a 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. In the Google Cloud console, go to the Cloud Spanner Instances page.

   Go to Cloud Spanner instances

2. Click Create instance.

3. For the instance name, enter a name, such as Test Instance.

4. The instance ID is automatically entered based on the instance name, for example, as test-instance. Change it, if required.

5. In Choose your configuration, retain the default option Regional and select a configuration from the drop-down menu.

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

6. In Allocate compute capacity, retain the default value of 1000 processing units.

   The instance creation page now looks like this:

   

7. Click Create.

   The instance appears in the instances list.

Create a database

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

   Go to Cloud Spanner instances

2. Click the instance you created, for example Test Instance.

3. In the instance Overview page that opens, click Create database.

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

5. 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, you'll define the schema in the Define your schema text field in the next section of this quickstart.

   Your database creation page now looks like this:

   

6. Click Create.

   The Google Cloud console displays the Overview page for the database you created.

Create a schema for your database

1. In the Tables section of the database Overview page, click Create table.

2. In the Write DDL statements page, 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 Google Cloud console returns to the database 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 lowercase.

Insert and modify data

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

Insert data

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

   The Google Cloud console displays the Singers table's Schema page.

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

3. Click Insert.

   The Google Cloud console displays the Singers table's Query page with a new query tab that contains INSERT and SELECT statements that 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

   

   PostgreSQL

   

7. Click Insert to add another row.

   The Google Cloud console again displays the Singers table's Query page with a new query tab that contains the same 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

    

    PostgreSQL

    

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

1. Click Insert to add a row.

   Cloud Spanner again displays the Singers table's Query page with a new query tab that contains the same 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 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 an empty string in the LastName column:

   Google Standard SQL

   

   PostgreSQL

   

Edit data

1. On the Singers table's Data page, select the checkbox on the row with the primary key value of 3, and then click Edit.

   The Cloud Spanner displays the Query page with a new tab containing template UPDATE and SELECT statements that you can 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=NULL,
     firstname='Kena',
     lastname='',
     singerinfo=NULL
   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

   

   PostgreSQL

   

Delete data

1. On the Singers table's Data page, select the checkbox on 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

   

   PostgreSQL

   

Run a query

1. On the database Overview page, click Query in the navigation menu.

2. Click New tab to create a new query tab. Then, enter the following query in the query editor:

   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

   

   PostgreSQL

   

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

Clean up

To avoid additional charges to your Cloud Billing account, delete the database and the instance that you created. Deleting an instance automatically deletes all databases created in the instance.

Delete the database

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

   Go to Cloud 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 the Database details page, click Delete database.

5. Confirm that you want to delete the database by entering the database name and clicking Delete.

Delete the instance

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

   Go to Cloud Spanner instances

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

3. Click Delete instance.

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

What's next

• Learn about Instances.
• Understand the Cloud Spanner Schema and Data Model.
• Learn more about Google Standard SQL 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.