courses/understanding_spanner/colab-notebooks/Spanner_PostgeSQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QKBCLNZVAv_j"
   },
   "source": [
    "## Install the Spanner Python API\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7uhDuh2B3-CX"
   },
   "outputs": [],
   "source": [
    "! pip install --upgrade google-cloud-spanner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "f-wsCeqBAyWl"
   },
   "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": "lJIrqAZz4CzU"
   },
   "outputs": [],
   "source": [
    "project_id = 'your-project-id-here'\n",
    "instance_id = 'spanner-postgres'\n",
    "\n",
    "processing_units = 100\n",
    "database_id = 'pets-postgres'\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": "iziApd7nA5F1"
   },
   "source": [
    "## Create a Spanner instance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "fUJGt4or4DgG"
   },
   "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",
    "        processing_units=processing_units,\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",
    "# Call the function\n",
    "create_instance_with_processing_units(instance_id, processing_units)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ghw0o_qxBFIe"
   },
   "source": [
    "## Create the Pets database\n",
    "\n",
    "Note: The parameter database_dialect-2 makes this a PostgreSQL database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "hpgkaDz24Osx"
   },
   "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, database_dialect=2\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",
    "# Call the function\n",
    "create_database(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "VRPYRaVaBHwp"
   },
   "source": [
    "## The following function runs DDL statements to build the database schema\n",
    "\n",
    "The parameter to the update_ddl() function is an array. You can run one or more DDL commands. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6JY0xghs6SaR"
   },
   "outputs": [],
   "source": [
    "def run_ddl_statement(instance_id, database_id, ddl):\n",
    "    \n",
    "    spanner_client = spanner.Client(project=project_id)\n",
    "    instance = spanner_client.instance(instance_id)\n",
    "    database = instance.database(database_id)\n",
    "\n",
    "    operation = database.update_ddl(\n",
    "        [ddl]\n",
    "    )\n",
    "\n",
    "    print(\"Waiting for operation to complete...\")\n",
    "    operation.result(OPERATION_TIMEOUT_SECONDS)\n",
    "\n",
    "    print(\"Ran statement: {}\".format(ddl))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IDA3A4VYBSYp"
   },
   "source": [
    "## Create the Owners table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "FIf7iqy16_2r"
   },
   "outputs": [],
   "source": [
    "ddl = \"\"\"CREATE TABLE Owners (\n",
    "                  OwnerID VARCHAR(36) PRIMARY KEY,\n",
    "                  OwnerName VARCHAR(1024) NOT NULL\n",
    "               )\"\"\"\n",
    "\n",
    "run_ddl_statement(instance_id, database_id, ddl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5jWaHTkBBW2z"
   },
   "source": [
    "## Create the Pets table\n",
    "\n",
    "Note: the Pets table is interleaved with the Owners table. This means each pet is stored with its owner. The primary key of the Pets table uses both OwnerID and PetID. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "afnOhO3v8LMQ"
   },
   "outputs": [],
   "source": [
    "ddl = \"\"\"CREATE TABLE Pets (\n",
    "                  OwnerID VARCHAR(36) NOT NULL, \n",
    "                  PetID VARCHAR(36) NOT NULL,     \n",
    "                  PetType VARCHAR(1024) NOT NULL,\n",
    "                  PetName VARCHAR(1024) NOT NULL,\n",
    "                  Breed VARCHAR(1024) NOT NULL,\n",
    "                  PRIMARY KEY (OwnerID, PetID)\n",
    "              ) \n",
    "              INTERLEAVE IN PARENT Owners ON DELETE CASCADE\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "run_ddl_statement(instance_id, database_id, ddl)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hWEAc578B1Ij"
   },
   "source": [
    "## Display the database schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "1J_oDtTK9KVZ"
   },
   "outputs": [],
   "source": [
    "def get_database_ddl(instance_id, database_id):\n",
    "    \"\"\"Gets the database DDL statements.\"\"\"\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": "GZRGAvhjB8ET"
   },
   "source": [
    "## Add some test records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "LO-z1x629VKF"
   },
   "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": "kXeryKjV_aqU"
   },
   "source": [
    "## Go to the Google Cloud Console and verify that the Spanner instance, database, tables, and records were all created. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "68K_pvCuCUUq"
   },
   "source": [
    "## Runs the query passed as an argument\n",
    "\n",
    "This function runs any SELECT query passed as an argument. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cbNHZ51G-GAq"
   },
   "outputs": [],
   "source": [
    "def run_query(sql):\n",
    "  # Instantiate a client.\n",
    "  spanner_client = spanner.Client(project=project_id)\n",
    "\n",
    "  # Get a Cloud Spanner instance by ID.\n",
    "  instance = spanner_client.instance(instance_id)\n",
    "\n",
    "  # Get a Cloud Spanner database by 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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "k3QOelQ6CeKG"
   },
   "source": [
    "## Query with a join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Ofdj6ok2-OEj"
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT Owners.OwnerID, OwnerName, PetName, PetType, Breed \n",
    "         FROM Owners \n",
    "         JOIN Pets ON Owners.OwnerID = Pets.OwnerID;\"\"\"\n",
    "\n",
    "run_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "-mB52ZWOC3rL"
   },
   "source": [
    "## Delete all of the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cwpex85KC6N7"
   },
   "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_owners(transaction):\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_owners)\n",
    "\n",
    "# Call the function\n",
    "delete_data_with_dml(instance_id, database_id)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HfjCtt0CDmgm"
   },
   "source": [
    "## Delete the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "LZjVAHLoDszL"
   },
   "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": "QawIviJTD1lb"
   },
   "source": [
    "## Delete the instance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-98p13_kD4-X"
   },
   "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": "GS9LUsmhAAL5"
   },
   "source": [
    "## Go to the Console and verify that the Spanner instance was deleted. "
   ]
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyP4j/aAs/M//3GybY2BdAT1",
   "collapsed_sections": [],
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3.8.2 64-bit",
   "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
}