# Cloud Spanner - Database Fundamentals - GSP1048

## Overview

Cloud Spanner is Google’s fully managed, horizontally scalable relational database service. Customers in financial services, gaming, retail and many other industries trust it to run their most demanding workloads, where consistency and availability at scale are critical.

In this lab, you perform basic administrative tasks within a Cloud Spanner instance.

### What you'll do

In this lab, you learn how to perform the following tasks:

* Create an instance.
    
* Create a database.
    
* Create tables.
    
* Insert and modify data.
    
* Use the Google Cloud CLI with Cloud Spanner.
    
* Use Automation Tools with Cloud Spanner.
    
* Delete an instance.
    

## Setup and requirements

### Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click **Start Lab**, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

* Access to a standard internet browser (Chrome browser recommended).
    

**Note:** Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.

* Time to complete the lab—remember, once you start, you cannot pause a lab.
    

**Note:** Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.

### How to start your lab and sign in to the Google Cloud console

1. Click the **Start Lab** button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:
    
    * The Open Google Cloud console button
        
    * Time remaining
        
    * The temporary credentials that you must use for this lab
        
    * Other information, if needed, to step through this lab
        
2. Click **Open Google Cloud console** (or right-click and select **Open Link in Incognito Window** if you are running the Chrome browser).
    
    The lab spins up resources, and then opens another tab that shows the Sign in page.
    
    ***Tip:*** Arrange the tabs in separate windows, side-by-side.
    
    **Note:** If you see the **Choose an account** dialog, click **Use Another Account**.
    
3. If necessary, copy the **Username** below and paste it into the **Sign in** dialog.
    
    ```apache
    student-04-182b08b200a0@qwiklabs.net
    ```
    
    You can also find the Username in the Lab Details pane.
    
4. Click **Next**.
    
5. Copy the **Password** below and paste it into the **Welcome** dialog.
    
    ```apache
    ivpPLSRYHmka
    ```
    
    You can also find the Password in the Lab Details pane.
    
6. Click **Next**.
    
    **Important:** You must use the credentials the lab provides you. Do not use your Google Cloud account credentials.
    
    **Note:** Using your own Google Cloud account for this lab may incur extra charges.
    
7. Click through the subsequent pages:
    
    * Accept the terms and conditions.
        
    * Do not add recovery options or two-factor authentication (because this is a temporary account).
        
    * Do not sign up for free trials.
        

After a few moments, the Google Cloud console opens in this tab.

**Note:** To access Google Cloud products and services, click the **Navigation menu** or type the service or product name in the **Search** field.

![Navigation menu icon and Search field](https://cdn.qwiklabs.com/9Fk8NYFp3quE9mF%2FilWF6%2FlXY9OUBi3UWtb2Ne4uXNU%3D align="left")

### Activate Cloud Shell

Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.

1. Click **Activate Cloud Shell** at the top of the Google Cloud console.
    
2. Click through the following windows:
    
    * Continue through the Cloud Shell information window.
        
    * Authorize Cloud Shell to use your credentials to make Google Cloud API calls.
        

When you are connected, you are already authenticated, and the project is set to your **Project\_ID**, `qwiklabs-gcp-02-069e6513c1d6`. The output contains a line that declares the **Project\_ID** for this session:

```apache
Your Cloud Platform project in this session is set to qwiklabs-gcp-02-069e6513c1d6
```

`gcloud` is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

3. (Optional) You can list the active account name with this command:
    

```apache
gcloud auth list
```

4. Click **Authorize**.
    

**Output:**

```apache
ACTIVE: *
ACCOUNT: student-04-182b08b200a0@qwiklabs.net

To set the active account, run:
    $ gcloud config set account `ACCOUNT`
```

5. (Optional) You can list the project ID with this command:
    

```apache
gcloud config list project
```

**Output:**

```apache
[core]
project = qwiklabs-gcp-02-069e6513c1d6
```

**Note:** For full documentation of `gcloud`, in Google Cloud, refer to [the gcloud CLI overview guide](https://cloud.google.com/sdk/gcloud).

## Task 1. Create an instance

1. The first step in using Cloud Spanner is to create an instance. An instance is an allocation of Google Cloud compute and storage resources. From the Console, open the navigation menu () > **View All Products**. Under **Databases** section, click **Spanner**.
    
2. Accept any acknowledgement or information window that may appear.
    
3. Then click **Create a Provisioned Instance**.
    
4. Fill in the following fields, leave the remainder with the default values:
    

| **Item** | **Value** |
| --- | --- |
| **Select an edition** | Enterprise |
| **Instance Name** | banking-instance |
| **Select a configuration** | `us-east1` |
| **Configure compute capacity** | Unit - Nodes // Quantity - 1 |

5. Click **Create**. Now you can see your instance on the Instance Details page. Here you have an overview of how the instance is performing, utilization, etc.. The next step is to create a database.
    

## Task 2. Create a database

1. From the instance details page, click **Create database**.
    
2. For the database name, enter **banking-db**.
    
3. Skip the **Define your schema (optional)** step for now. You'll define your schema in the next section.
    
4. Click **Create**.
    
5. You're now on the **Overview** page for the new database you created. You can see that the page is similar to the Instance one, but the statistics refer to the specific database. Also note the new options on the left menu.
    
6. Click **Check my progress** to verify the objective.
    

Create an instance and database

**Check my progress**

## Task 3. Create a table in your database

1. On the Database Details page for your **banking-db** database, scroll down the page and click **Create table**.
    
2. Click the blue **+** icon to open the **Query** page, enter:
    

```sql
CREATE TABLE Customer (
  CustomerId STRING(36) NOT NULL,
  Name STRING(MAX) NOT NULL,
  Location STRING(MAX) NOT NULL,
) PRIMARY KEY (CustomerId);
```

3. Click **Run**.
    
4. When the operation is complete, click **Overview** under **Database** in the left menu. Then scroll down to **Tables** and click **Customer** to see the schema details:
    
5. Click **Check my progress** to verify the objective.
    

Create a schema for your database

**Check my progress**

## Task 4. Insert and modify data

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

### Insert data

1. While on the **Schema** page, click **Data** in the left menu. Then click **Insert**.
    
2. This takes you to the **Query** tab of the **Spanner Studio** automatically. Click **Clear Query**, paste the query below, and click **Run**:
    

```sql
INSERT INTO
  Customer (CustomerId,
    Name,
    Location)
VALUES
  ('bdaaaa97-1b4b-4e58-b4ad-84030de92235',
    'Richard Nelson',
    'Ada Ohio'
    );
```

3. The lower page of the screen shows the result. The **Customer** table now has one row.
    
4. Add a second row. Replace the previous statement with the following, and click **Run**:
    

```sql
INSERT INTO
  Customer (CustomerId,
    Name,
    Location)
VALUES
  ('b2b4002d-7813-4551-b83b-366ef95f9273',
    'Shana Underwood',
    'Ely Iowa'
    );
```

### Run a query

1. You can execute a SQL statement on the query page of your database.
    
2. In the left pane of the Cloud Platform Console, click **Spanner Studio** to navigate to the Query UI window.
    
3. Click the blue **+** icon to open the **Query** page. Click **Clear Query**, paste the query below, and click Run:
    

```sql
SELECT * FROM Customer;
```

4. Click **Run**.
    
5. The Cloud Console displays the result of your query.
    

## Task 5. Use the Google Cloud CLI with Cloud Spanner

The Cloud Console is very useful, but in some use cases you want to manage Spanner instances using other methods. Google Cloud services can also be managed through the command line tool named **gcloud**. The easiest way to use the **gcloud** CLI is via the Cloud Shell but it can also be installed on a wide variety of operating systems.

### Create an instance with CLI

1. Creating a Spanner instance via **gcloud** is very simple. The core command is as follows:
    

```powershell
    gcloud spanner instances create [INSTANCE-ID] \
    --config=[INSTANCE-CONFIG] \
    --description="[INSTANCE-NAME]" \
    --nodes=[NODE-COUNT]
```

2. In the Cloud Shell, create a new Cloud Spanner using the command below.
    

```apache
gcloud spanner instances create banking-instance-2 \
--config=regional-us-east1  \
--description="Banking Instance 2" \
--nodes=2
```

### Listing instances

1. You can run the following command to list the Spanner instances available in your project.
    

```apache
gcloud spanner instances list
```

### Creating a database

1. You can also create databases in a Spanner instance using **gcloud**.
    
2. In the Cloud Shell, create a new database using the command below.
    

```apache
gcloud spanner databases create banking-db-2 --instance=banking-instance-2
```

3. Click **Check my progress** to verify the objective.
    

Create an instance and database with CLI

**Check my progress**

### Modifying number of nodes

Remember that it is important to provision enough nodes to keep CPU utilization and storage utilization below the recommended maximum values. However, sometimes it is necessary to reduce the number of nodes.

1. You are now going to reduce the number of nodes of the instance **banking-instance-2** from two to one.
    
2. Use the following gcloud command to adjust the instance:
    

```apache
gcloud spanner instances update banking-instance-2 --nodes=1
```

3. After completion, check that the number of nodes has been reduced:
    

```apache
gcloud spanner instances list
```

## Task 6. Use Automation Tools with Cloud Spanner

As your Spanner architectures grow and get more complex, you want to automate the deployment and management of Spanner instances. One of the tools available to deploy and manage Spanner is Terraform.

The goal of this lab is not to explain how Terraform works. If you are not familiar with Terraform, you can [check the documentation](https://cloud.google.com/docs/terraform).

In short, you provide Terraform with a set of files that describe the infrastructure or services that you want it to deploy in Google Cloud, which is called a **configuration**. Then you instruct Terraform to **initialize** and **plan** the deployment, before applying the changes.

### Verify Terraform installation

1. Terraform comes pre-installed in the Cloud Shell. Using the previous Cloud Shell (or open it again if you closed it), verify that terraform is available:
    

```apache
terraform -version
```

2. You should see an output similar to this:
    

```apache
Terraform v1.5.6
on linux_amd64
```

**Note:** You may safely ignore any warning about the version of Terraform being out of date.

### Create Terraform Configuration

1. In the cloud shell enter the following command to invoke the **Nano** text editor and create a new empty configuration file named **spanner.tf**.
    

```apache
nano spanner.tf
```

2. In the **Nano** editor, paste the code block listed below.
    

```apache
resource "google_spanner_instance" "banking-instance-3" {
  name         = "banking-instance-3"
  config       = "regional-us-east1"
  display_name = "Banking Instance 3"
  num_nodes    = 2
  labels = {
  }
}
```

3. Press **Ctrl+X** to exit Nano, **Y** to confirm the update, and press **Enter** to save your changes.
    

### Deploy

1. The next step is to make sure all the Terraform service providers are available (in this case, the Spanner service provider). For that, run the following command in the Cloud Shell:
    

```apache
terraform init
```

2. Next instruct Terraform to create an execution plan that is based on the configuration file that you created a few steps ago. Run the following command:
    

```apache
terraform plan
```

3. The output show details about the new instance that will be created. Run the following command to apply the plan to your project:
    

```apache
terraform apply
```

4. The plan will be displayed again and Terraform will pause for approval to continue. Type **yes** and Terraform will create the new instance.
    
5. The apply will process and return the following output:
    

```apache
Apply complete! Resources: 1 added, 0 changed, 0 destroyed.
```

6. Confirm that the new instance, **banking-instance-3**, was created:
    

```apache
gcloud spanner instances list
```

## Task 7. Deleting instances

1. A very quick way to delete an instance is using the CLI. Run the following command:
    

```apache
gcloud spanner instances delete banking-instance-2
```

2. To confirm that **banking-instance-2** was deleted run the following command:
    

```apache
gcloud spanner instances list
```

---

## Solution of Lab

%[https://youtu.be/dGPFWrMy5gE] 

```apache
curl -LO raw.githubusercontent.com/ePlus-DEV/storage/refs/heads/main/labs/GSP1048/lab.sh
source lab.sh
```

**Script Alternative**

```apache
export REGION=
```
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1744173878138/8468cdd4-eb24-42f0-8405-01260f437d42.png align="center")

```apache
curl -LO raw.githubusercontent.com/QUICK-GCP-LAB/2-Minutes-Labs-Solutions/main/Cloud%20Spanner%20-%20Database%20Fundamentals/gsp1048.sh
sudo chmod +x gsp1048.sh
./gsp1048.sh
```