CareerByteCode
ByteBlogby CareerByteCode

Spanner Free Trial: Global SQL Database Hands-On

What you build: a free trial Cloud Spanner instance with interleaved tables, real duplicate and orphan errors, a unique index, a Python app saving an order and points in one transaction, and a stale r

Spanner Free Trial: Global SQL Database Hands-On

✍️ Translate this blog into Hindi, Tamil or Telugu

Cloud Spanner is Google's globally distributed SQL database: it speaks SQL with real transactions like PostgreSQL or MySQL, but it scales across machines and regions without you sharding anything. It is the database behind very large Google services, and it used to be out of reach for learners because of its price. The Spanner free trial instance changes that: 90 days, 10 GB, no charge. In this lab you create one, design two tables the Spanner way (interleaved), load and query data, hit the two errors every Spanner beginner meets, add an index, write a small Python app that saves an order and its loyalty points in one transaction, and read data "as it was a minute ago".

Who it is for: developers and data beginners who know basic SQL. Time: about 1 hour. Cost: free. The free trial instance costs nothing for 90 days, and we delete it at the end.

Every command below was run for real by the CareerByteCode Team on a fresh Google Cloud project, and every output is the real result we got.

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →

Part A: Spanner in plain English

Spanner in one picture
An instance holds databases; tables are split into key ranges across servers; interleaving keeps a customer's orders next to the customer
  • Instance: the compute and storage that run your databases, in one region (regional-us-east1) or several (nam3, nam-eur-asia1). Normal instances are sized in processing units; the free trial instance is a special small one with 10 GB for 90 days, one per project.
  • Database: tables, indexes and their data, inside an instance.
  • Primary key: every table must have one, and Spanner stores rows in key order, split into ranges across servers. Choosing the key well is the main design decision.
  • Interleaved table: a child table stored inside its parent. Orders is interleaved in Customers, so customer 1's orders sit right next to customer 1, and reading a customer with their orders is one fast read. Real-life example: a filing cabinet where each customer's folder holds their own order slips, instead of one giant drawer of all slips.
  • Transaction: several reads and writes that succeed or fail together. Spanner gives strong (externally consistent) transactions, even across regions.
  • Commit timestamp: Spanner can stamp a row with the exact moment it was committed (PENDING_COMMIT_TIMESTAMP()).
  • Stale read: reading the data as it was at an earlier moment. It never blocks writers, which is great for reports.

Before you start

  1. A Google Cloud account with billing (the free trial works) and a project. One free Spanner instance per project, ever (see Troubleshooting), so use a project that has not had one.
  2. Open Cloud Shell (the >_ icon at the top right of the console) and set your project:
export PROJECT_ID="your-project-id"
gcloud config set project $PROJECT_ID
gcloud services enable spanner.googleapis.com
  1. Make a folder ~/lab194 with two files: schema.sql (Step 3) and shop_app.py (Step 8). All commands run in Cloud Shell.

Part B: the lab

Step 1: where can Spanner run?

Why: an instance configuration decides where your data lives. A regional config keeps it in one region (with copies across zones); multi-region configs like nam3 or nam-eur-asia1 keep copies across regions so the database survives a whole region going down.

# Where can Spanner run? An instance config = the region (or regions) that hold your data
gcloud spanner instance-configs list --format="table(name.basename(), displayName)" | grep -E "NAME|us-east1|nam3|nam-eur" | head -5
NAME                              DISPLAY_NAME
nam-eur-asia1                     United States, Europe, and Asia (Iowa/Oklahoma/Belgium/Taiwan)
nam-eur-asia3                     (Iowa/South Carolina/Belgium/Netherlands/Taiwan/Oklahoma)
nam3                              United States (Northern Virginia/South Carolina)
regional-us-east1                 us-east1

Step 2: create the free trial instance

Why: --instance-type=free-instance asks for the free trial. expireBehavior: REMOVE_AFTER_GRACE_PERIOD means that after 90 days (plus a grace period) it is removed unless you upgrade it to a paid instance.

# Create the FREE TRIAL instance (90 days, 10 GB, one per project)
gcloud spanner instances create shop-trial --config=regional-us-east1 --instance-type=free-instance --description="Free trial for the lab" 2>&1 | tail -1
gcloud spanner instances describe shop-trial --format="yaml(displayName, config.basename(), instanceType, state, freeInstanceMetadata.expireBehavior)"
......done.
config: regional-us-east1
displayName: Free trial for the lab
freeInstanceMetadata:
  expireBehavior: REMOVE_AFTER_GRACE_PERIOD
instanceType: FREE_INSTANCE
state: READY

Step 3: a database with interleaved tables

Why: Customers has the key CustomerId. Orders has the key (CustomerId, OrderId), starting with the parent's key, and INTERLEAVE IN PARENT Customers ON DELETE CASCADE stores orders inside their customer and deletes them with it. allow_commit_timestamp lets Spanner fill CreatedAt with the commit time.

# A database with two tables: Orders is INTERLEAVED in Customers (each customer's orders are stored next to it)
cat schema.sql
gcloud spanner databases create shopdb --instance=shop-trial --ddl-file=schema.sql 2>&1 | tail -1
gcloud spanner databases ddl describe shopdb --instance=shop-trial | grep -E "CREATE|INTERLEAVE"
CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  Name       STRING(100) NOT NULL,
  Email      STRING(200) NOT NULL,
  City       STRING(50),
  Points     INT64 NOT NULL DEFAULT (0),
) PRIMARY KEY (CustomerId);

CREATE TABLE Orders (
  CustomerId INT64 NOT NULL,
  OrderId    INT64 NOT NULL,
  Item       STRING(100) NOT NULL,
  Amount     NUMERIC NOT NULL,
  CreatedAt  TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp = true),
) PRIMARY KEY (CustomerId, OrderId),
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
.....done.
CREATE TABLE Customers (
CREATE TABLE Orders (
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
Free instance and schema
The free instance (FREE_INSTANCE, READY) and the database with Orders interleaved in Customers

Step 4: insert data with SQL

Why: Spanner supports normal INSERT, UPDATE and DELETE (DML). PENDING_COMMIT_TIMESTAMP() is replaced by the real commit time.

# Insert data with SQL (DML)
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Customers (CustomerId, Name, Email, City) VALUES (1, 'Asha', 'asha@example.com', 'Chennai'), (2, 'Ravi', 'ravi@example.com', 'Pune'), (3, 'Meera', 'meera@example.com', 'Kochi')"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Orders (CustomerId, OrderId, Item, Amount, CreatedAt) VALUES (1, 101, 'Laptop stand', 1499, PENDING_COMMIT_TIMESTAMP()), (1, 102, 'USB-C cable', 399, PENDING_COMMIT_TIMESTAMP()), (2, 201, 'Keyboard', 2499, PENDING_COMMIT_TIMESTAMP())"
Statement modified 3 rows
Statement modified 3 rows

Step 5: query it

Why: a join and a group-by, exactly as in other SQL databases. Kochi has a customer with no orders, so LEFT JOIN with COALESCE shows 0.

# Query it: a join, and the totals per city
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT c.Name, o.OrderId, o.Item, o.Amount FROM Customers c JOIN Orders o ON o.CustomerId = c.CustomerId ORDER BY o.OrderId"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT c.City, COUNT(o.OrderId) AS orders, COALESCE(SUM(o.Amount), 0) AS total FROM Customers c LEFT JOIN Orders o ON o.CustomerId = c.CustomerId GROUP BY c.City ORDER BY total DESC"
Name  OrderId  Item          Amount
Asha  101      Laptop stand  1499
Asha  102      USB-C cable   399
Ravi  201      Keyboard      2499
City     orders  total
Pune     1       2499
Chennai  2       1898
Kochi    0       0
Insert and query
3 customers and 3 orders inserted; a join, and orders and totals per city

Break it: a duplicate key and a missing parent

Two errors every Spanner beginner meets: inserting a row whose key already exists, and inserting a child row (an order) whose parent (customer 99) does not exist.

# Break it: a duplicate key, and an order for a customer that does not exist
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Customers (CustomerId, Name, Email) VALUES (1, 'Copy', 'copy@example.com')" 2>&1 | grep -iE "error|exists" | head -2
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Orders (CustomerId, OrderId, Item, Amount, CreatedAt) VALUES (99, 901, 'Ghost order', 100, PENDING_COMMIT_TIMESTAMP())" 2>&1 | grep -iE "error|parent|not found" | head -2
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT COUNT(*) AS customers FROM Customers"
ERROR: (gcloud.spanner.databases.execute-sql) ALREADY_EXISTS: Row [1] in table Customers already exists
ERROR: (gcloud.spanner.databases.execute-sql) NOT_FOUND: Parent row for row [99,901] in table Orders is missing. Row cannot be written. This command is authenticated as lab-shell@your-project-id.iam.gserviceaccount.com which is the active account specified by the [core/account] property.
customers
3
  • ALREADY_EXISTS: the primary key must be unique. Use INSERT OR UPDATE (or UPDATE) when you mean "change it".
  • NOT_FOUND: Parent row ... is missing: an interleaved child cannot exist without its parent. This is a feature: it protects your data from orphan orders.
  • The table still has 3 customers: neither bad insert changed anything.

Step 6: a secondary index

Why: rows are stored in key order (CustomerId), so finding a customer by email would read every row. A secondary index on Email makes it a direct lookup, and UNIQUE also stops two customers from sharing an email. @{FORCE_INDEX=...} tells Spanner to use it. Building an index on an existing table takes a little while (the long line of dots), even on a tiny table.

# A secondary index: find a customer by email without reading the whole table, and keep emails unique
gcloud spanner databases ddl update shopdb --instance=shop-trial --ddl="CREATE UNIQUE INDEX CustomersByEmail ON Customers(Email)" 2>&1 | tail -1
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT CustomerId, Name FROM Customers@{FORCE_INDEX=CustomersByEmail} WHERE Email = 'ravi@example.com'"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="INSERT INTO Customers (CustomerId, Name, Email) VALUES (4, 'Ravi K', 'ravi@example.com')" 2>&1 | grep -iE "error|unique|exists" | head -2
..........done.
CustomerId  Name
2           Ravi
ERROR: (gcloud.spanner.databases.execute-sql) ALREADY_EXISTS: Unique index violation on index CustomersByEmail at index key [ravi@example.com,2]. It conflicts with row [2] in table Customers.

The last insert was refused by the unique index: ravi@example.com is already taken by customer 2. (That refused insert is why this step ends with exit code 1.)

Step 7: a Python app with one transaction

Why: this is what Spanner is for. shop_app.py places an order and adds loyalty points (1 point per 100 of the amount) inside run_in_transaction: both writes happen, or neither does. It signs in with your Cloud Shell login (on a server: a service account), so there is no password or key file.

# shop_app.py: a tiny app on Cloud Spanner. It places an order and adds loyalty points in ONE read-write transaction,
# then reads the customer back. Sign-in: the VM's service account (no key file, no password).
import sys
from google.cloud import spanner

INSTANCE, DATABASE = "shop-trial", "shopdb"
db = spanner.Client().instance(INSTANCE).database(DATABASE)


def place_order(customer_id, order_id, item, amount):
    def work(tx):
        rows = list(tx.execute_sql("SELECT Points FROM Customers WHERE CustomerId = @c",
                                   params={"c": customer_id}, param_types={"c": spanner.param_types.INT64}))
        if not rows:
            raise ValueError(f"customer {customer_id} does not exist")
        tx.insert("Orders", columns=("CustomerId", "OrderId", "Item", "Amount", "CreatedAt"),
                  values=[(customer_id, order_id, item, amount, spanner.COMMIT_TIMESTAMP)])
        new_points = rows[0][0] + int(amount) // 100
        tx.update("Customers", columns=("CustomerId", "Points"), values=[(customer_id, new_points)])
        return new_points
    return db.run_in_transaction(work)


if __name__ == "__main__":
    cid, oid, item, amount = int(sys.argv[1]), int(sys.argv[2]), sys.argv[3], sys.argv[4]
    try:
        points = place_order(cid, oid, item, amount)
        print(f"order {oid} saved for customer {cid}; points now {points}")
    except Exception as e:
        print(f"order {oid} NOT saved: {type(e).__name__}: {str(e).splitlines()[0][:120]}")
    with db.snapshot() as snap:
        sql = ("SELECT c.Name, c.Points, COUNT(o.OrderId) FROM Customers c LEFT JOIN Orders o ON o.CustomerId = c.CustomerId "
               "WHERE c.CustomerId = @c GROUP BY c.Name, c.Points")
        for r in snap.execute_sql(sql, params={"c": cid}, param_types={"c": spanner.param_types.INT64}):
            print(f"{r[0]}: {r[1]} points, {r[2]} orders")

We place one good order, one for a customer that does not exist, and the same order twice:

🧪⭐ Recommended by CareerByteCode · ByteLabs project bundleCloud and DevOps Realtime Starterโ‚น499Build the projects →
# A small Python app (on the Cloud Shell VM): an order and its loyalty points saved in ONE transaction
export GOOGLE_CLOUD_PROJECT=$PROJECT_ID   # the project the app uses (Cloud Shell sets this for you)
python3 -m venv .venv && . .venv/bin/activate && pip install -q google-cloud-spanner 2>&1 | grep -iv "notice" | tail -1
grep -nE "^def|run_in_transaction|tx\.(insert|update)" shop_app.py
python3 shop_app.py 1 103 "Monitor arm" 3200
python3 shop_app.py 99 902 "Ghost order" 500
python3 shop_app.py 1 103 "Monitor arm again" 3200
10:def place_order(customer_id, order_id, item, amount):
16:        tx.insert("Orders", columns=("CustomerId", "OrderId", "Item", "Amount", "CreatedAt"),
19:        tx.update("Customers", columns=("CustomerId", "Points"), values=[(customer_id, new_points)])
21:    return db.run_in_transaction(work)
order 103 saved for customer 1; points now 32
Asha: 32 points, 3 orders
order 902 NOT saved: ValueError: customer 99 does not exist
order 103 NOT saved: AlreadyExists: 409 Row [1,103] in table Orders already exists, request_id = 1.11740438615885963494.EXTERNAL_IP
Asha: 32 points, 3 orders
Transaction app
One order saved with its points in one transaction; an unknown customer and a duplicate order are refused and nothing changes
  • Order 103 (3,200) was saved and Asha got 32 points, in one transaction.
  • The order for customer 99 was refused by our own check inside the transaction, so nothing was written.
  • Sending order 103 again failed with AlreadyExists, and the transaction rolled back: Asha still has 32 points and 3 orders, not 64 points. Without a transaction you could easily end up with points for an order that was never saved.

Step 8: stale reads

Why: a stale read asks for the data as it was at a moment in the past. Spanner keeps old versions for a while (1 hour by default), so a report can read a consistent picture without blocking anyone who is writing.

# Stale reads: read the data as it was 60 seconds ago (reports that must not slow down writes)
gcloud spanner databases execute-sql shopdb --instance=shop-trial --read-timestamp=$(date -u -d "-60 seconds" +%Y-%m-%dT%H:%M:%SZ) --sql="SELECT CustomerId, Points FROM Customers WHERE CustomerId = 1"
gcloud spanner databases execute-sql shopdb --instance=shop-trial --sql="SELECT CustomerId, Points FROM Customers WHERE CustomerId = 1"
CustomerId  Points
1           0
CustomerId  Points
1           32

60 seconds ago Asha had 0 points; now she has 32. Same table, same query, two moments in time.

POC: prove it works

  • A free trial Spanner instance (FREE_INSTANCE, READY) in us-east1.
  • Two tables with Orders interleaved in Customers, loaded with SQL and queried with a join and a group-by.
  • Spanner refused a duplicate key, an orphan order and a duplicate email (unique index).
  • A Python app saved an order and its points in one transaction, and rolled back cleanly on a duplicate.
  • A stale read returned the data as it was 60 seconds earlier.

Quiz task: check what you learned

  1. Why does the Orders key start with CustomerId? (Answer: an interleaved child's key must begin with its parent's key, so it is stored inside the parent.)
  2. You need to find orders by Item quickly. What do you add? *(Answer: CREATE INDEX OrdersByItem ON Orders(Item).)*
  3. An insert says Parent row ... is missing. What does it mean? (Answer: the parent customer does not exist; insert the customer first.)
  4. Why use a transaction for "save order + add points"? (Answer: both must happen or neither; a failure in the middle must not leave points without an order.)
  5. How many free trial instances can one project have? (Answer: one per project, ever.)

Clean up (delete everything)

Why: the free instance is free, but it still counts as your one free instance and holds data. Delete the database, then the instance.

# Clean up: delete the database and the free instance
gcloud spanner databases delete shopdb --instance=shop-trial --quiet 2>&1 | tail -1
gcloud spanner instances delete shop-trial --quiet 2>&1 | tail -1
echo "Spanner instances left: $(gcloud spanner instances list --format='value(name)' | wc -l)"
Spanner instances left: 0
Clean up
The database and the free instance are deleted: 0 Spanner instances left

FAQ

When should I choose Spanner instead of Cloud SQL? Choose Cloud SQL (MySQL or PostgreSQL) for most apps: it is cheaper and familiar. Choose Spanner when you need more scale than one machine, very high availability (99.999 percent in multi-region configs) or one consistent database across regions.

Does Spanner speak PostgreSQL? Yes: a database can use the PostgreSQL dialect, so many PostgreSQL tools and drivers work. This lab used GoogleSQL, Spanner's default dialect.

Why not use an auto-increment ID as the key? Ever-growing keys send every new row to the same server (a "hotspot"). Spanner recommends keys that spread out, such as a UUID or a bit-reversed sequence. Our tiny lab used small numbers for readability.

What happens after the 90 days? You can upgrade the free instance to a paid one and keep the data. Otherwise it is removed after a grace period.

How do I see a query plan? Add --query-mode=PROFILE to execute-sql, or open the query in Spanner Studio in the console and look at the plan and statistics.

Can I back up a free trial instance? Free trial instances have limits (for example on backups and size). Upgrade to a paid instance for production features.

Troubleshooting

You seeWhyFix
FAILED_PRECONDITION: Free instances are limited to 1 per project lifecycle.The project already had a free instance, even a deleted oneUse another project (we hit this on our second run)
ALREADY_EXISTS: Row [...] already existsThe primary key is takenUse another key, or INSERT OR UPDATE / UPDATE
NOT_FOUND: Parent row for row [...] is missingInterleaved child without a parentInsert the parent row first
Unique index violation on index ...A UNIQUE index already has that valueUse another value, or update the existing row
Python: 403 ... spanner permissionYour account or the service account has no Spanner roleGrant roles/spanner.databaseUser (data) or roles/spanner.admin
Index creation takes longSpanner backfills the index onlineWait; check with gcloud spanner operations list --instance=...

What next

Prepared by the CareerByteCode Team from our tested lab, with AI help for the write-up.

Read next →BigQuery ML: Train a Model with Just SQL⏱ 12 min

⭐ Keep learning with CareerByteCode

Hand-picked to go deeper on this topic
News, guides and updates from the CareerByteCode team
🏅 Expert writer

The CareerByteCode team writes about what is new on careerbytecode.in: courses, labs, exams, events and the community, which is free to join for influencers and content creators in tech.

Get CareerByteCode Team's next blog by email

One email a week when they publish. Add the weekly Best of ByteBlog too. No spam, unsubscribe with one click.

We email you once to confirm. By subscribing you agree to the privacy policy.

💬 Comments (0)

Top · Newest

Sign in to comment

Related blogs

⭐ Recommended by CareerByteCode · BytePlay courseNVIDIA GPU AI Factory: Architecture, Engineering & Operationsโ‚น40,000Start learning →