{ "cells": [ { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "7f7d2d8f-af01-4f0a-a636-2f45d71d5da9", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "# Time travel and table history\n", "\n", "You ran the update with the wrong filter. Then you ran a delete to tidy up, and made it worse. On\n", "a file-based table that is the end of the story and the start of a restore-from-backup ticket.\n", "\n", "A Delta table keeps every version it has ever had. This lab breaks a table twice, reads it as it\n", "was before either mistake, works out what the damage was, and puts it back.\n", "\n", "Full instructions: https://brickster.io/labs/time-travel-and-table-history\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "a5ab290b-85a9-43da-9e4e-7f99c90a7b1b", "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": "c544a2e2-93dc-4593-a187-ccbfdf06a6c6", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "SALT = \"00000000\" # <- paste yours here\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "2b28af9d-f82e-47c0-a251-bf02473869f7", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "## 2. A table, before anybody touches it\n", "\n", "Built from `spark.range`, so there is nothing to download and the numbers are the same in every\n", "workspace. 4,096 orders over 64 regions.\n", "\n", "`create table ... as select` is one write, so the table starts life at **version 0**. Every write\n", "after this one adds a version, and the old ones do not go anywhere.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "ff4b78b8-71c9-416f-bd36-bdef103a1141", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "CATALOG = \"workspace\" # change this if workspace is not where you can create a schema\n", "SCHEMA = \"brickster_history\"\n", "T = f\"{CATALOG}.{SCHEMA}.orders\"\n", "\n", "spark.sql(f\"create schema if not exists {CATALOG}.{SCHEMA}\")\n", "spark.sql(f\"drop table if exists {T}\")\n", "\n", "spark.sql(f\"\"\"\n", " create table {T} as\n", " select id,\n", " cast(id % 64 as int) as region,\n", " cast((id * 31) % 89 - 35 as int) as amount\n", " from range(0, 4096)\n", "\"\"\")\n", "\n", "print(\"rows now:\", spark.sql(f\"select count(*) as n from {T}\").collect()[0][\"n\"])\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "19448a80-3218-496d-830d-36a0ae84917f", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "## 3. Tuesday\n", "\n", "Two writes, in the order they happened.\n", "\n", "The update was meant for one region and went to eight of them. The delete was somebody tidying up\n", "the negative amounts afterwards, not knowing the update had made some of them.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "f25113ee-3d6b-4b74-bd0c-daa00afb143f", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "spark.sql(f\"update {T} set amount = 0 where region < 8\")\n", "\n", "spark.sql(f\"delete from {T} where amount < 0\")\n", "\n", "print(\"rows now:\", spark.sql(f\"select count(*) as n from {T}\").collect()[0][\"n\"])\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "98fbebb5-98fe-4732-9238-e8f8375c4642", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "## 4. What the table remembers\n", "\n", "`describe history` lists every version of the table: what the operation was, who ran it, when, and\n", "a map of metrics about what it touched.\n", "\n", "You have run three statements against this table. Look at the `operation` column before you count\n", "anything, because the number of rows here is not three. Serverless optimises tables on its own, and\n", "every time it does it commits a version of its own next to yours.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "1d2b1072-bfa8-47b2-b6cb-5951bfca749d", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "display(spark.sql(f\"describe history {T}\"))\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "c6595ca5-cfaf-491a-9d42-fe32549d651e", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "# TODO: how many of the versions in this history came from the three statements in this lab.\n", "# Not all of them did. Read the `operation` column, then count only the operations you ran:\n", "# CREATE TABLE AS SELECT, UPDATE and DELETE.\n", "my_writes = None\n", "\n", "print(\"versions in total:\", spark.sql(f\"describe history {T}\").count())\n", "print(f\"BRICKSTER:my_writes={my_writes}\")\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "1189408a-5279-4b0a-b737-fdc561737050", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "The `operationMetrics` column is worth opening before you move on. It is where a write records what\n", "it did, and the keys are different for each kind of operation, which is why the next answer is not\n", "read out of it. The optimiser's own versions show up here too, with metrics about files rather than\n", "about rows.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "f8a4d01c-2008-4001-bd01-4fcd832eaffc", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "display(spark.sql(f\"\"\"\n", " select version, operation, operationMetrics\n", " from (describe history {T})\n", " order by version\n", "\"\"\"))\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "bdfaa3c0-ee6f-4eba-a314-c9475e713809", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "## 5. Reading the table as it was\n", "\n", "`version as of 0` queries the table as it stood at the version you name. It is an ordinary query,\n", "so anything you would normally write works against it, and nothing about the table changes.\n", "\n", "This is the whole point. You do not have to know which rows the delete took, because the version\n", "before it is still readable.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "7f98a0f7-adee-44a8-aad5-a18642c52331", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "# TODO: two answers, both about the damage.\n", "# v0_rows is the row count as the table stood at version 0.\n", "# deleted_rows is how many rows the delete took, which is v0_rows against the count now.\n", "# `select count(*) from version as of 0` reads the old version without changing anything.\n", "v0_rows = None\n", "deleted_rows = None\n", "\n", "print(f\"BRICKSTER:v0_rows={v0_rows}\")\n", "print(f\"BRICKSTER:deleted_rows={deleted_rows}\")\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "f93ebed3-bcf8-4274-a4be-11617397281e", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "## 6. Putting it back\n", "\n", "`restore table ... to version as of 0` makes the current table equal to that version again. It does\n", "this by writing a *new* version, not by throwing the bad ones away, so Tuesday stays on the record\n", "and nothing you restore from is ever lost.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "f1756a50-930c-4b85-a948-94b32826355a", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "# TODO: put the table back to the way it was at version 0.\n", "# `restore table
to version as of `. Look at the history afterwards: the restore is\n", "# itself a version, and the two bad writes are still listed.\n", "\n", "print(\"rows now:\", spark.sql(f\"select count(*) as n from {T}\").collect()[0][\"n\"])\n" ] }, { "cell_type": "markdown", "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": {}, "inputWidgets": {}, "nuid": "1f807133-6497-430a-9ae9-fc35a47e24a2", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "source": [ "## 7. Your slice of the restored table\n", "\n", "The last question is yours alone. Your salt picks one region out of 64. Total its amounts in the\n", "table you just restored.\n", "\n", "If the restore worked, this is the total the table had before Tuesday. If it did not, the regions\n", "under 8 answer zero and you will know which step to go back to.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "application/vnd.databricks.v1+cell": { "cellMetadata": { "byteLimit": 2048000, "rowLimit": 10000 }, "inputWidgets": {}, "nuid": "205add78-195e-42fd-8f98-fcdca4680100", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "bucket = int(SALT, 16) % 64\n", "print(\"your region:\", bucket)\n", "\n", "# TODO: the total of the amounts in your region, in the restored table.\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": "848c0347-74e6-44bf-b3fa-63b67d3f9a2f", "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": "c4e7ff59-20e5-4844-bbe8-44a8896b3f19", "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": "b73c2d8e-c85e-494e-a1a9-357e1054c9ee", "showTitle": false, "tableResultSettingsMap": {}, "title": "" } }, "outputs": [], "source": [ "import brickster\n", "\n", "brickster.submit(\"time-travel-and-table-history\")\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 }