{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "bfd236f8-9128-41a0-9389-4a63aaf9df9c",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "# Catalog, schema, table, volume\n",
        "\n",
        "`workspace.sales.orders` is not a filename with dots in it. It is three levels of Unity Catalog:\n",
        "a catalog holds schemas, a schema holds tables and volumes, and the last part is the thing itself.\n",
        "Every grant, every share and every lineage edge hangs off that structure, which is why getting it\n",
        "wrong on day one is expensive later.\n",
        "\n",
        "This lab builds one of each. A schema of your own, two tables in it, and a volume beside them for\n",
        "the files that are not tables.\n",
        "\n",
        "Full instructions: https://brickster.io/labs/catalog-schema-table-volume\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "dfb94916-b312-47d4-992f-e0ef4f10dbdd",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 1. Your salt\n",
        "\n",
        "Copy the eight characters under **Your salt** on the lab page and paste them below. One of the four\n",
        "answers depends on it.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "53b237a0-0a4b-41ba-8ec6-aac161227c3a",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "SALT = \"00000000\"  # <- paste yours here\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "2421d7e7-5907-4beb-a03c-c242aa5627b7",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 2. Where you are standing\n",
        "\n",
        "Every query you have ever run in a workspace ran somewhere. These two functions say where. An\n",
        "unqualified table name is resolved against them, which is the entire reason a query that worked in\n",
        "one notebook fails in another.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "e245c569-b875-4772-a64a-49e158028f0b",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "here = spark.sql(\"select current_catalog() as c, current_schema() as s\").collect()[0]\n",
        "print(\"catalog:\", here[\"c\"], \"  schema:\", here[\"s\"])\n",
        "\n",
        "display(spark.sql(\"show catalogs\"))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "96fccfef-f9e4-412b-9236-57950137a604",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 3. The fourth kind of thing\n",
        "\n",
        "A schema holds tables, and it also holds **volumes**. A volume is the place for everything that is\n",
        "not a table: CSVs somebody sent you, images, model files, a PDF. It is governed by the same grants\n",
        "as the tables beside it, and it appears on the filesystem under `/Volumes/<catalog>/<schema>/<name>`.\n",
        "\n",
        "Databricks ships one you can read. The sample datasets that used to live under `/databricks-datasets`\n",
        "are a volume in the `samples` catalog now, so the three-level name reaches files as well as rows.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "8f7ccef3-24ec-4cbb-9d20-cc31528edda6",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "display(dbutils.fs.ls(\"/Volumes/samples/databricks/datasets\"))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "fc924e4f-a72f-4cd9-866a-6db9241a3907",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 4. A schema of your own\n",
        "\n",
        "Creating a catalog usually needs an account admin, so this lab stays inside the one you already\n",
        "have and makes a schema in it. That is the level most people actually create.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "ad64e645-e136-47d1-a0c3-54adffa5b570",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "CATALOG = \"workspace\"  # change this if workspace is not where you can create a schema\n",
        "SCHEMA = \"brickster_namespace\"\n",
        "T = f\"{CATALOG}.{SCHEMA}\"\n",
        "\n",
        "spark.sql(f\"create schema if not exists {T}\")\n",
        "spark.sql(f\"drop table if exists {T}.orders\")\n",
        "spark.sql(f\"drop table if exists {T}.region_totals\")\n",
        "\n",
        "print(\"schema:\", T)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "ccd4fdb6-dacd-41c7-af35-b311c6780042",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "Two tables, both built from `spark.range`, so there is nothing to download and the numbers are the\n",
        "same in every workspace. 4,096 orders over 64 regions, and a summary derived from them.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "2768857f-5e64-4057-9f8e-0ec6f0659064",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "spark.sql(f\"\"\"\n",
        "  create table {T}.orders as\n",
        "  select id,\n",
        "         cast(id % 64 as int) as region,\n",
        "         cast((id * 53) % 97 - 40 as int) as amount\n",
        "  from range(0, 4096)\n",
        "\"\"\")\n",
        "\n",
        "spark.sql(f\"\"\"\n",
        "  create table {T}.region_totals as\n",
        "  select region, sum(amount) as total from {T}.orders group by region\n",
        "\"\"\")\n",
        "\n",
        "display(spark.sql(f\"show tables in {T}\"))\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "c819f760-9679-42cf-8ee7-b26731ebc306",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "# TODO: how many tables does your schema hold now.\n",
        "# `show tables in <catalog>.<schema>` lists them, and a DataFrame can .count() itself.\n",
        "schema_tables = None\n",
        "\n",
        "print(f\"BRICKSTER:schema_tables={schema_tables}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "9eaf8b54-5ff6-4767-bd88-a85d967275ce",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 5. What the table calls itself\n",
        "\n",
        "You created `orders` inside a schema inside a catalog. Ask the table what its own name is and it\n",
        "gives back all three levels, which is the name every grant and every other workspace has to use.\n",
        "\n",
        "`describe detail` is the metadata view of a Delta table: its name, where its files are, how many\n",
        "there are, when it was last written.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "62f7cbbc-29ba-48a0-9059-b9bdc47a0152",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "display(spark.sql(f\"describe detail {T}.orders\"))\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "ae85adc5-e4aa-4ec5-9314-3ebd4d93af96",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "# TODO: read the table's own fully qualified name out of `describe detail`, which is the\n",
        "# `name` column, then print the middle level of it. That middle level is the schema.\n",
        "full = None\n",
        "table_schema = None\n",
        "\n",
        "print(f\"BRICKSTER:table_schema={table_schema}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "374c8588-15ef-48b2-a8db-4cffc54754be",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 6. A volume of your own\n",
        "\n",
        "Same schema, different kind of thing. Create the volume, then write two files into it the way you\n",
        "would write any file: the path is a path, and `dbutils.fs` reaches it.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "2865e3d4-acf1-4519-8b11-c02d4d90dc9e",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "VOLUME = f\"/Volumes/{CATALOG}/{SCHEMA}/drop\"\n",
        "\n",
        "spark.sql(f\"create volume if not exists {T}.drop\")\n",
        "\n",
        "dbutils.fs.put(f\"{VOLUME}/README.txt\", \"files that are not tables live here\\n\", True)\n",
        "dbutils.fs.put(f\"{VOLUME}/regions.txt\", \"64 regions, 4096 orders\\n\", True)\n",
        "\n",
        "display(dbutils.fs.ls(VOLUME))\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "7a28356d-d58d-4d19-86a2-272bf96146a6",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "# TODO: how many files are in your volume now.\n",
        "# dbutils.fs.ls returns a list.\n",
        "volume_files = None\n",
        "\n",
        "print(f\"BRICKSTER:volume_files={volume_files}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "0709fd78-e382-491c-8501-ef2bef6b3045",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 7. Your slice of the table\n",
        "\n",
        "The last question is yours alone. Your salt picks one region out of 64. Read that region's total\n",
        "out of the tables you just built.\n",
        "\n",
        "A notebook borrowed from somebody else answers this with their region and fails here, which is the\n",
        "only thing standing between this lab and a copied run.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "e2508200-0ca1-4088-a5df-3d3bd5dbfd94",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "bucket = int(SALT, 16) % 64\n",
        "print(\"your region:\", bucket)\n",
        "\n",
        "# TODO: the total for your region, read from the tables in your schema.\n",
        "bucket_total = None\n",
        "\n",
        "print(f\"BRICKSTER:bucket_total={bucket_total}\")\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {},
          "inputWidgets": {},
          "nuid": "ae0e523a-6cc0-4cd8-9094-9ebb6bfd4fcb",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "source": [
        "## 8. Check your run\n",
        "\n",
        "Two ways, same grader.\n",
        "\n",
        "**Export and upload.** *File > Export > IPYNB*, then drop it on the lab page.\n",
        "\n",
        "**Or straight from here.** Make a token on https://brickster.io/settings, put it in the `brickster`\n",
        "secret scope once per workspace, and run the last cell. Your Databricks credentials never leave\n",
        "this workspace.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "fe6305a1-87a2-4b58-b525-812d83b80a0b",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "%pip install brickster\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "application/vnd.databricks.v1+cell": {
          "cellMetadata": {
            "byteLimit": 2048000,
            "rowLimit": 10000
          },
          "inputWidgets": {},
          "nuid": "0f8159ea-a17e-4e3c-ab59-343d609c0129",
          "showTitle": false,
          "tableResultSettingsMap": {},
          "title": ""
        }
      },
      "outputs": [],
      "source": [
        "import brickster\n",
        "\n",
        "brickster.submit(\"catalog-schema-table-volume\")\n"
      ]
    }
  ],
  "metadata": {
    "application/vnd.databricks.v1+notebook": {
      "computePreferences": null,
      "dashboards": [],
      "environmentMetadata": {
        "base_environment": "",
        "environment_version": "5"
      },
      "inputWidgetPreferences": null,
      "language": "python",
      "notebookMetadata": {
        "pythonIndentUnit": 4
      },
      "notebookName": "starter",
      "widgets": {}
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}