courses/understanding_spanner/colab-notebooks/Spanner_Samples_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yir3_hhI6El2"
   },
   "source": [
    "## Install the Spanner Python API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zIAMqF3FV2qQ"
   },
   "outputs": [],
   "source": [
    "! pip install --upgrade google-cloud-spanner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FLsnZvxW6YN3"
   },
   "source": [
    "## Set the following variables \n",
    "\n",
    "You at least need to change the Project ID variable to your project's ID. You can change the other variables if you like. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Fvl1JzxrWkl3"
   },
   "outputs": [],
   "source": [
    "project_id = 'your-project-id-here'\n",
    "instance_id = 'spanner-instance-python'\n",
    "\n",
    "processing_units = 100\n",
    "database_id = 'pets-db'\n",
    "\n",
    "OPERATION_TIMEOUT_SECONDS = 240\n",
    "\n",
    "!gcloud services enable spanner.googleapis.com \n",
    "print(\"Spanner Enabled\")\n",
    "\n",
    "print(\"Done\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-VjmkR1Y6VMX"
   },
   "source": [
    "## Create a Spanner instance\n",
    "\n",
    "The code below uses the Python library to create a Spanner instance. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yqVnvI12XKAq"
   },
   "outputs": [],
   "source": [
    "import time\n",
    "from google.cloud import spanner\n",
    "\n",
    "def create_instance_with_processing_units(instance_id, processing_units):\n",
    "    \"\"\"Creates an instance.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "\n",
    "    config_name = \"{}/instanceConfigs/regional-us-central1\".format(\n",
    "        spanner_client.project_name\n",
    "    )\n",
    "\n",
    "    instance = spanner_client.instance(\n",
    "        instance_id,\n",
    "        configuration_name=config_name,\n",
    "        display_name=instance_id,\n",
    "        # Use node_count attribute to create instance using nodes\n",
    "        # instead of Processing Units\n",
    "        # node_count = 1,\n",
    "        processing_units=processing_units,\n",
    "        labels={\n",
    "            \"created\": str(int(time.time())),\n",
    "        },\n",
    "    )\n",
    "\n",
    "    # create() returns a long-running operation\n",
    "    operation = instance.create()\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\n",
    "        \"Created instance {} with {} processing units\".format(\n",
    "            instance_id, instance.processing_units\n",
    "        )\n",
    "    )\n",
    "\n",
    "\n",
    "# Call the function\n",
    "create_instance_with_processing_units(instance_id, processing_units)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "59dzbwnH8xZi"
   },
   "source": [
    "## Go to the Google Cloud Console and verify that the Spanner instance was created. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0rl6983F67Cw"
   },
   "source": [
    "## Create the Pets database\n",
    "\n",
    "This code create the Pets database on the instance created above. Note the DDL code that creates the Owners and Pets tables. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qx2igXsmaUF5"
   },
   "outputs": [],
   "source": [
    "def create_database(instance_id, database_id):\n",
    "    \"\"\"Creates a database and tables for sample data.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "\n",
    "    database = instance.database(\n",
    "        database_id,\n",
    "        ddl_statements=[\n",
    "            \"\"\"CREATE TABLE Owners (\n",
    "                  OwnerID STRING(36) NOT NULL,\n",
    "                  OwnerName STRING(MAX) NOT NULL\n",
    "               ) PRIMARY KEY (OwnerID)\"\"\",\n",
    "            \"\"\"CREATE TABLE Pets (\n",
    "                  PetID STRING(36) NOT NULL, \n",
    "                  OwnerID STRING(36) NOT NULL, \n",
    "                  PetType STRING(MAX) NOT NULL,\n",
    "                  PetName STRING(MAX) NOT NULL,\n",
    "                  Breed STRING(MAX) NOT NULL,\n",
    "              ) PRIMARY KEY (PetID)\"\"\"\n",
    "        ],\n",
    "    )\n",
    "\n",
    "    # create() returns a long-running operation\n",
    "    operation = database.create()\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\"Created database {} on instance {}\".format(database_id, instance_id))\n",
    "\n",
    "create_database(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "02th_8rh7H6c"
   },
   "source": [
    "## Show the data definition code for the Pets database \n",
    "\n",
    "Verify that the DDL code from the last code block was set correctly. This uses the Admin API to retrieve the DDL. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "gqFB2R8lcADM"
   },
   "outputs": [],
   "source": [
    "def get_database_ddl(instance_id, database_id):\n",
    "  \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "    ddl = spanner_client.database_admin_api.get_database_ddl(database=database.name)\n",
    "\n",
    "    print(\"Retrieved database DDL for {}\".format(database_id))\n",
    "\n",
    "    for statement in ddl.statements:\n",
    "        print(statement)\n",
    "\n",
    "\n",
    "get_database_ddl(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "O1HFjqG37SJY"
   },
   "source": [
    "## Add a few test records\n",
    "\n",
    "Note the batch insert in this example to add multiple records. We could run an inter-query as well, but this is more efficient when adding multiple records at the same time. \n",
    "\n",
    "Also, note the code to create the UUIDs that are used for primary key values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8z84NIQBcTzj"
   },
   "outputs": [],
   "source": [
    "import uuid\n",
    "\n",
    "def insert_data(instance_id, database_id):\n",
    "    doug_id = str(uuid.uuid4())\n",
    "    john_id = str(uuid.uuid4())\n",
    "    sue_id = str(uuid.uuid4())\n",
    "\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    with database.batch() as batch:\n",
    "        batch.insert(\n",
    "            table=\"Owners\",\n",
    "            columns=(\"OwnerID\", \"OwnerName\"),\n",
    "            values=[\n",
    "                (doug_id, u\"Doug\"),\n",
    "                (john_id, u\"John\"),\n",
    "                (sue_id, u\"Sue\"),\n",
    "            ],\n",
    "        )\n",
    "\n",
    "        batch.insert(\n",
    "            table=\"Pets\",\n",
    "            columns=(\"PetID\", \"OwnerID\", \"PetType\", \"PetName\", \"Breed\"),\n",
    "            values=[\n",
    "                (str(uuid.uuid4()), doug_id, u\"Dog\", u\"Noir\", u\"Schnoodle\"),\n",
    "                (str(uuid.uuid4()), doug_id, u\"Dog\", u\"Bree\", u\"Mutt\"),\n",
    "                (str(uuid.uuid4()), doug_id, u\"Cat\", u\"Tom\", u\"Alley\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Dog\", u\"Duke\", u\"GoldenDoodle\"),\\\n",
    "                (str(uuid.uuid4()), john_id, u\"Dog\", u\"Sparky\", u\"Poodle\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Turtle\", u\"Cuff\", u\"Box\"),\n",
    "                (str(uuid.uuid4()), john_id, u\"Turtle\", u\"Link\", u\"Box\"),\n",
    "                (str(uuid.uuid4()), sue_id, u\"Cat\", u\"Cleo\", u\"Domestic\"),\n",
    "            ],\n",
    "        )\n",
    "\n",
    "    print(\"Inserted data.\")\n",
    "\n",
    "\n",
    "insert_data(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GDM4oSmb7WiY"
   },
   "source": [
    "## Run a query using SQL\n",
    "\n",
    "Run a SELECT query to test whether the test records were added. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "jM79QuodVlF2"
   },
   "outputs": [],
   "source": [
    "def run_simple_query(sql):\n",
    "  spanner_client = spanner.Client(project=project_id)\n",
    "  instance = spanner_client.instance(instance_id)\n",
    "  database = instance.database(database_id)\n",
    "\n",
    "  # Execute a simple SQL statement.\n",
    "  with database.snapshot() as snapshot:\n",
    "      results = snapshot.execute_sql(sql)\n",
    "      for row in results:\n",
    "        print(row)\n",
    "\n",
    "\n",
    "sql = \"\"\"SELECT OwnerName, PetName, PetType, Breed \n",
    "         FROM Owners \n",
    "         JOIN Pets ON Owners.OwnerID = Pets.OwnerID;\"\"\"\n",
    "\n",
    "\n",
    "run_simple_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tIaIEp0k-luw"
   },
   "source": [
    "## Read data from a table\n",
    "\n",
    "The code above runs a SELECT query. In this block, columns are just read from tables. Notice the function here is snapshot.read(), in the prior block it was snapshot.execute_sql()."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5rqQrEcL980A"
   },
   "outputs": [],
   "source": [
    "def read_data(instance_id, database_id):\n",
    "    \"\"\"Reads sample data from the database.\"\"\"\n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    with database.snapshot() as snapshot:\n",
    "        keyset = spanner.KeySet(all_=True)\n",
    "        results = snapshot.read(\n",
    "            table=\"Pets\", columns=(\"PetID\", \"PetName\", \"PetType\", \"Breed\"), keyset=keyset\n",
    "        )\n",
    "\n",
    "        for row in results:\n",
    "            print(u\"PetID: {}, PetName: {}, PetType: {}, Breed: {}\".format(*row))\n",
    "\n",
    "\n",
    "# Call the function\n",
    "read_data(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Z_7nLM_r8_ei"
   },
   "source": [
    "## Go to the Console and verify the Spanner instance, database, tables, and data were all created "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_AVdvElrZ-aG"
   },
   "source": [
    "## Delete all of the data\n",
    "\n",
    "Here we just delete all of the data by running a couple of DELETE queries. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "SImpKl-3aCD9"
   },
   "outputs": [],
   "source": [
    "def delete_data_with_dml(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    def delete_pets_owners(transaction):\n",
    "        row_ct = transaction.execute_update(\n",
    "            \"DELETE FROM Pets WHERE true = true\"\n",
    "        )\n",
    "        row_ct += transaction.execute_update(\n",
    "            \"DELETE FROM Owners WHERE true = true\"\n",
    "        )\n",
    "\n",
    "        print(\"{} record(s) deleted.\".format(row_ct))\n",
    "\n",
    "    database.run_in_transaction(delete_pets_owners)\n",
    "\n",
    "# Call the function\n",
    "delete_data_with_dml(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "KkFNBsunbZ8t"
   },
   "source": [
    "## Delete the database\n",
    "\n",
    "In this block, the database itself is deleted. We didn't actually have to delete the data prior to deleting the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "UOYfn7bxbZVZ"
   },
   "outputs": [],
   "source": [
    "def delete_database(instance_id, database_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "    \n",
    "    database.drop()\n",
    "\n",
    "    print(\"{} database dropped\".format(database_id))\n",
    " \n",
    "\n",
    "# Call the function\n",
    "delete_database(instance_id, database_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lWXl0d_UcnU3"
   },
   "source": [
    "## Delete the instance\n",
    "\n",
    "We're done. Let's delete the Spanner instance since it costs money. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "hK3BYqbxcrp3"
   },
   "outputs": [],
   "source": [
    "def delete_spanner_instance(instance_id):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    instance.delete()\n",
    "\n",
    "    print(\"{} instance deleted\".format(instance_id))\n",
    "\n",
    "# Call the function\n",
    "delete_spanner_instance(instance_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_KPqKpEw8j0f"
   },
   "source": [
    "## Go to the Console and see if the Spanner instance was deleted. "
   ]
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyNdoJ6l/PNTmvI0QCHdxzbX",
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}