notebooks/03-query/20-tiering-historicals.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0cb3b009-ebde-4d56-9d59-a028d66d8309",
   "metadata": {},
   "source": [
    "# Load table data to different historical tiers using retention rules\n",
    "<!--\n",
    "  ~ Licensed to the Apache Software Foundation (ASF) under one\n",
    "  ~ or more contributor license agreements.  See the NOTICE file\n",
    "  ~ distributed with this work for additional information\n",
    "  ~ regarding copyright ownership.  The ASF licenses this file\n",
    "  ~ to you under the Apache License, Version 2.0 (the\n",
    "  ~ \"License\"); you may not use this file except in compliance\n",
    "  ~ with the License.  You may obtain a copy of the License at\n",
    "  ~\n",
    "  ~   http://www.apache.org/licenses/LICENSE-2.0\n",
    "  ~\n",
    "  ~ Unless required by applicable law or agreed to in writing,\n",
    "  ~ software distributed under the License is distributed on an\n",
    "  ~ \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY\n",
    "  ~ KIND, either express or implied.  See the License for the\n",
    "  ~ specific language governing permissions and limitations\n",
    "  ~ under the License.\n",
    "  -->\n",
    "\n",
    "As an Apache Druid cluster administrator, you can use [Service tiering](https://druid.apache.org/docs/latest/operations/mixed-workloads#service-tiering) to provision cluster resources according to various performance and storage requirements. For example, you can isolate resource-intensive queries with complex subqueries and large result sets from high priority, interactive queries.\\n\"\n",
    "\n",
    "This tutorial demonstrates how to create [historical tiers](https://druid.apache.org/docs/latest/operations/mixed-workloads#historical-tiering) to host different data based upon data age. Then, when you run a query, Druid uses the service that hosts the data corresponding to the time period within the query."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbdbf6ad-ca7b-40f5-8ca3-1070f4a3ee42",
   "metadata": {},
   "source": [
    "## Prerequisites\n",
    "\n",
    "This tutorial works with Druid 30.0.0 or later.\n",
    "\n",
    "This tutorial requires a deployment of Druid with multiple historical services, with one of the historical tiers named \"slow\".\n",
    "\n",
    "Launch this tutorial and all prerequisites using the `tiered-druid-jupyter` profile of the Docker Compose file for Jupyter-based Druid tutorials to create a cluster with an additional historical.\n",
    "\n",
    "For more information, see the Learn Druid repository [readme](https://github.com/implydata/learn-druid)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5007a243-b81a-4601-8f57-5b14940abbff",
   "metadata": {},
   "source": [
    "## Initialization\n",
    "\n",
    "The following cells set up the notebook and learning environment ready for use."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b769122-c5a4-404e-9ef8-9c0ebd97695a",
   "metadata": {},
   "source": [
    "### Set up a connection to Apache Druid\n",
    "\n",
    "Run the next cell to set up the Druid Python client's connection to Apache Druid.\n",
    "\n",
    "If successful, the Druid version number will be shown in the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1ec783b-df3f-4168-9be2-cdc6ad3e33c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import druidapi\n",
    "import os\n",
    "\n",
    "druid_headers = {'Content-Type': 'application/json'}\n",
    "\n",
    "if 'DRUID_HOST' not in os.environ.keys():\n",
    "    druid_host=f\"http://localhost:8888\"\n",
    "else:\n",
    "    druid_host=f\"http://{os.environ['DRUID_HOST']}:8888\"\n",
    "\n",
    "print(f\"Opening a connection to {druid_host}.\")\n",
    "druid = druidapi.jupyter_client(druid_host)\n",
    "display = druid.display\n",
    "sql_client = druid.sql\n",
    "status_client = druid.status\n",
    "\n",
    "status_client.version"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c3d6b39-6551-4b2a-bdfb-9606aa92c853",
   "metadata": {},
   "source": [
    "### Import additional modules\n",
    "\n",
    "Run the following cell to import additional Python modules that you will use to make direct calls to some APIs in Druid."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc4c2524-0eba-4bc6-84ed-da3a25aa5fbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f61d2dc-02e8-4fcc-8975-2f923bd8d1a0",
   "metadata": {},
   "source": [
    "### Create some helper functions\n",
    "\n",
    "Run the next cell to set up a standard piece of SQL that you will use in this notebook. It uses the `server_segments`, `servers`, and `segments` tables to produce a list of segments and where they’re cached."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6d94590-681b-4163-b07d-ce8e26737cd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "table_name = 'example-wikipedia-tiering'\n",
    "\n",
    "layout_query = f'''\n",
    "SELECT\n",
    "  a.\"start\",\n",
    "  a.\"end\",\n",
    "  c.\"server\",\n",
    "  c.\"tier\",\n",
    "  \"num_rows\",\n",
    "  \"size\"\n",
    "FROM \"sys\".\"segments\" a\n",
    "LEFT JOIN \"sys\".\"server_segments\" b ON a.\"segment_id\" = b.\"segment_id\"\n",
    "LEFT JOIN \"sys\".\"servers\" c ON b.\"server\" = c.\"server\"\n",
    "WHERE \"datasource\" = '{table_name}'\n",
    "ORDER BY \"start\", \"tier\"\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "472589e4-1026-4b3b-bb79-eedabb2b44c4",
   "metadata": {},
   "source": [
    "## Create a table using batch ingestion\n",
    "\n",
    "In this section, you use batch ingestion to create a table with data spanning a few years, then identify where the  data is cached for querying."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2137c41-cdf9-437c-8072-850cd113d046",
   "metadata": {},
   "source": [
    "### Ingest example data\n",
    "\n",
    "Run the next cell to ingest the initial data. The ingestion query only loads a subset of all columns available in the example dataset.\n",
    "\n",
    "When completed, the cell prints a description of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f52a94fb-d2e4-403f-ab10-84d3af7bf2c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "REPLACE INTO \"''' + table_name + '''\" OVERWRITE ALL\n",
    "WITH \"ext\" AS (\n",
    "  SELECT *\n",
    "  FROM TABLE(\n",
    "    EXTERN(\n",
    "      '{\"type\":\"http\",\"uris\":[\"https://druid.apache.org/data/wikipedia.json.gz\"]}',\n",
    "      '{\"type\":\"json\"}'\n",
    "    )\n",
    "  ) EXTEND (\"isRobot\" VARCHAR, \"channel\" VARCHAR, \"timestamp\" VARCHAR, \"flags\" VARCHAR, \"isUnpatrolled\" VARCHAR, \"page\" VARCHAR, \"diffUrl\" VARCHAR, \"added\" BIGINT, \"comment\" VARCHAR, \"commentLength\" BIGINT, \"isNew\" VARCHAR, \"isMinor\" VARCHAR, \"delta\" BIGINT, \"isAnonymous\" VARCHAR, \"user\" VARCHAR, \"deltaBucket\" BIGINT, \"deleted\" BIGINT, \"namespace\" VARCHAR, \"cityName\" VARCHAR, \"countryName\" VARCHAR, \"regionIsoCode\" VARCHAR, \"metroCode\" BIGINT, \"countryIsoCode\" VARCHAR, \"regionName\" VARCHAR)\n",
    ")\n",
    "SELECT\n",
    "  TIME_PARSE(\"timestamp\") AS \"__time\",\n",
    "  \"isRobot\",\n",
    "  \"channel\",\n",
    "  \"isUnpatrolled\",\n",
    "  \"page\",\n",
    "  \"comment\",\n",
    "  \"commentLength\",\n",
    "  \"user\"\n",
    "FROM \"ext\"\n",
    "PARTITIONED BY DAY\n",
    "'''\n",
    "\n",
    "display.run_task(sql)\n",
    "sql_client.wait_until_ready(table_name)\n",
    "display.table(table_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb3e3d61-46bc-4046-94f5-dc20a0c9a7b8",
   "metadata": {},
   "source": [
    "### Inspect the servers and current configuration\n",
    "\n",
    "Query the server’s system table to see available Historicals their assigned tiers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67de46b9-391d-41ed-9704-d3935d14fdf2",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "SELECT server, tier, curr_size\n",
    "FROM \"sys\".\"servers\"\n",
    "WHERE \"server_type\" = 'historical'\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e3ac6bc-69ea-4d15-ace6-0063951caea0",
   "metadata": {},
   "source": [
    "You will see that there are multiple historical servers.\n",
    "\n",
    "- One belongs to the default tier `_default_tier`.\n",
    "- One belongs to the `slow` tier.\n",
    "\n",
    "> If you don't see multiple servers on multiple tiers, stop now.\n",
    "> See [pre-requisites](#prerequisites) for more information.\n",
    "\n",
    "Run the next cell to inspect the current distribution of data using the sys tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "357cdb7f-8be4-4c4f-b26d-fdb3c041988c",
   "metadata": {},
   "outputs": [],
   "source": [
    "display.sql(layout_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9320078-86ab-40b7-82eb-7785e36dbfe6",
   "metadata": {},
   "source": [
    "Druid loads all segments for the table, totalling around 20,000 rows, onto Historicals in the `default_tier` tier.\n",
    "\n",
    "To understand the default configuration, run the following cell to inspect the current retention load rules with the Coordinator API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69ac786f-f27c-46ca-9f85-25b77798efd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(json.dumps(json.loads(requests.get(f'{druid_host}/druid/coordinator/v1/rules').text), indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e597661-e6a8-4a3b-98cc-c1fc7e3bafa4",
   "metadata": {},
   "source": [
    "After creation, tables don't have any load rules of their own. Instead, they adhere to Druid's default set of rules, `_default`.\n",
    "\n",
    "By default, the `_default` rules set contains only one rule - a [load forever](https://druid.apache.org/docs/latest/operations/rule-configuration#forever-load-rule) rule (`loadForever`) with a replication factor (`tieredReplicants`) of 2 across servers in the `_default_tier`.\n",
    "\n",
    "Druid caches the entire timeline of data for your table on Historicals in the `_default_tier` and executes queries there."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b6c9b88-837d-4c80-a28d-36184ba63355",
   "metadata": {},
   "source": [
    "## Cache data on different tiers\n",
    "\n",
    "In this section, you create an additional load rule to load data onto the `slow` tier using a combination of different types of [load rules](https://druid.apache.org/docs/latest/operations/rule-configuration#load-rules): forever, period, and interval ."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da440b36-c51b-485d-acff-c61f3b4852c3",
   "metadata": {},
   "source": [
    "### Load all data onto multiple tiers\n",
    "\n",
    "Run the next cell to create a JSON object for us to store a retention rule, send it to the Coordinator API, and then print out the current full rule set on the database.\n",
    "\n",
    "- Add Historicals in the `slow` tier to the replication rules (`tieredReplicants`).\n",
    "- Load one replica of the data onto the `slow` tier.\n",
    "- Load one replica of the data onto the `_default_tier` tier.\n",
    "- Call the the `rules` endpoint (Retention rules API) and pass the `table_name` to apply the load rule in Druid."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e22d9c43-838c-43e5-b885-fb980d9465fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "retention_rules = [\n",
    "  {\n",
    "    \"type\": \"loadForever\",\n",
    "    \"tieredReplicants\": {\n",
    "      \"_default_tier\": 1,\n",
    "      \"slow\": 1\n",
    "    }\n",
    "  }\n",
    "]\n",
    "\n",
    "requests.post(f\"{druid_host}/druid/coordinator/v1/rules/{table_name}\", json.dumps(retention_rules), headers=druid_headers)\n",
    "print(json.dumps(json.loads(requests.get(f'{druid_host}/druid/coordinator/v1/rules').text), indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19819547-f069-4bfc-be2a-4f28509a0bf1",
   "metadata": {},
   "source": [
    "In addition to the `_default` rule set, there is a new rule set specific to the `example-wikipedia-tiering` table.\n",
    "\n",
    "Run the next cell to see the location of the cached data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6c9f83e-eb2d-40c0-b35a-c765bf781634",
   "metadata": {},
   "outputs": [],
   "source": [
    "display.sql(layout_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de9165b5-034f-4189-9d7b-7b6349b085ab",
   "metadata": {},
   "source": [
    "By setting up a table-specific rule set, where `tieredReplicants` includes both tiers, both the `slow` and `_default_tier` tiers have been loaded with all the segments of your table.\n",
    "\n",
    "(Run the cell above again if you don't see this immediately.)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a0ad47b-c6f9-45af-9278-4c81a1aaf6bc",
   "metadata": {},
   "source": [
    "### Load tiers according to data age\n",
    "\n",
    "Run the next cell to create some \"fake\" data in the table that is a year older.\n",
    "\n",
    "* INSERT appends data instead to the table of REPLACE INTO, which would overwrite.\n",
    "* The [TIME_PARSE](https://druid.apache.org/docs/latest/querying/sql-scalar#date-and-time-functions) function shifts the timestamp back by a year. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbd90368-7da5-4d2c-94b2-1bac4001291a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "INSERT INTO \"''' + table_name + '''\"\n",
    "WITH \"ext\" AS (\n",
    "  SELECT *\n",
    "  FROM TABLE(\n",
    "    EXTERN(\n",
    "      '{\"type\":\"http\",\"uris\":[\"https://druid.apache.org/data/wikipedia.json.gz\"]}',\n",
    "      '{\"type\":\"json\"}'\n",
    "    )\n",
    "  ) EXTEND (\"isRobot\" VARCHAR, \"channel\" VARCHAR, \"timestamp\" VARCHAR, \"flags\" VARCHAR, \"isUnpatrolled\" VARCHAR, \"page\" VARCHAR, \"diffUrl\" VARCHAR, \"added\" BIGINT, \"comment\" VARCHAR, \"commentLength\" BIGINT, \"isNew\" VARCHAR, \"isMinor\" VARCHAR, \"delta\" BIGINT, \"isAnonymous\" VARCHAR, \"user\" VARCHAR, \"deltaBucket\" BIGINT, \"deleted\" BIGINT, \"namespace\" VARCHAR, \"cityName\" VARCHAR, \"countryName\" VARCHAR, \"regionIsoCode\" VARCHAR, \"metroCode\" BIGINT, \"countryIsoCode\" VARCHAR, \"regionName\" VARCHAR)\n",
    ")\n",
    "SELECT\n",
    "  TIME_SHIFT(TIME_PARSE(\"timestamp\"), 'P1Y', -1) AS \"__time\",\n",
    "  \"isRobot\",\n",
    "  \"channel\",\n",
    "  \"isUnpatrolled\",\n",
    "  \"page\",\n",
    "  \"comment\",\n",
    "  \"commentLength\",\n",
    "  \"user\"\n",
    "FROM \"ext\"\n",
    "PARTITIONED BY DAY\n",
    "'''\n",
    "\n",
    "display.run_task(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dbdcc30-f0b1-494e-85bc-ea899a920f74",
   "metadata": {},
   "source": [
    "Review the following retention load rules.\n",
    "\n",
    "- An [interval load rule](https://druid.apache.org/docs/latest/operations/rule-configuration#interval-load-rule) (`loadByInterval`) covers 10 years of data before 1st January 2016. It requests one replica of data be cached in the `slow` tier.\n",
    "- `loadForever` requests that all data be cached on the `_default_tier` tier.\n",
    "\n",
    "Run the cell to submit it to the cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba75b9f9-cfe5-4073-933d-b1672995531e",
   "metadata": {},
   "outputs": [],
   "source": [
    "retention_rules = [\n",
    "  {\n",
    "    \"type\": \"loadByInterval\",\n",
    "    \"interval\": \"P10Y/2016\",\n",
    "    \"tieredReplicants\": {\n",
    "      \"slow\": 1\n",
    "    }\n",
    "  },\n",
    "  {\n",
    "    \"type\": \"loadForever\",\n",
    "    \"tieredReplicants\": {\n",
    "        \"_default_tier\": 1\n",
    "    }\n",
    "  }\n",
    "]\n",
    "\n",
    "requests.post(f\"{druid_host}/druid/coordinator/v1/rules/{table_name}\", json.dumps(retention_rules), headers=druid_headers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6c686c9-93c7-4477-a9ee-902b2ac8c209",
   "metadata": {},
   "source": [
    "Run the next cell to see where the location of the cached data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ed7d699-5993-4f68-bb3b-976548ae92ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "display.sql(layout_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56c57575-95cf-48d9-aae6-5bf8ddda0c3b",
   "metadata": {},
   "source": [
    "Druid checks each segment against the rules in order to decide where each must be cached. Therefore:\n",
    "\n",
    "* Data for 2015 (`loadByInterval`) is only cached on the `slow` tier.\n",
    "* All other data (`loadForever`) is only available on the `_default_tier` tier.\n",
    "\n",
    "Re-run the cell above if you don't see this immediately.\n",
    "\n",
    "Run the following query on the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e77af65e-3596-454e-8dfb-5056546813dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = f'''\n",
    "SELECT\n",
    "  TIME_FLOOR(__time, 'P1D') AS \"date\",\n",
    "  COUNT(*) AS \"rows\",\n",
    "  COUNT(DISTINCT \"user\") AS \"users\",\n",
    "  CAST(AVG(\"commentLength\") AS INTEGER) AS \"average_comment\"\n",
    "FROM \"{table_name}\"\n",
    "GROUP BY 1\n",
    "'''\n",
    "\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea52acbb-826c-4022-ab8a-5d79fc2eda0a",
   "metadata": {},
   "source": [
    "Consider that, because of [time partitioning](https://druid.apache.org/docs/latest/multi-stage-query/concepts#partitioning-by-time), some parts of this query were calculated on the `slow` historical tier, and some were calculated on Historicals in the `_default_tier` tier."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37e887eb-f2b9-4014-b2bd-e2e0186a56f3",
   "metadata": {},
   "source": [
    "### Load data according to age\n",
    "\n",
    "Run the cell below to ingest some data for the current year.\n",
    "\n",
    "The TIME_EXTRACT function calculates the shift between the source timestamp and today's date to fake data for this year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3ccb4e5-0845-4985-a427-1dc1381afa7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "INSERT INTO \"''' + table_name + '''\"\n",
    "WITH \"ext\" AS (\n",
    "  SELECT *\n",
    "  FROM TABLE(\n",
    "    EXTERN(\n",
    "      '{\"type\":\"http\",\"uris\":[\"https://druid.apache.org/data/wikipedia.json.gz\"]}',\n",
    "      '{\"type\":\"json\"}'\n",
    "    )\n",
    "  ) EXTEND (\"isRobot\" VARCHAR, \"channel\" VARCHAR, \"timestamp\" VARCHAR, \"flags\" VARCHAR, \"isUnpatrolled\" VARCHAR, \"page\" VARCHAR, \"diffUrl\" VARCHAR, \"added\" BIGINT, \"comment\" VARCHAR, \"commentLength\" BIGINT, \"isNew\" VARCHAR, \"isMinor\" VARCHAR, \"delta\" BIGINT, \"isAnonymous\" VARCHAR, \"user\" VARCHAR, \"deltaBucket\" BIGINT, \"deleted\" BIGINT, \"namespace\" VARCHAR, \"cityName\" VARCHAR, \"countryName\" VARCHAR, \"regionIsoCode\" VARCHAR, \"metroCode\" BIGINT, \"countryIsoCode\" VARCHAR, \"regionName\" VARCHAR)\n",
    ")\n",
    "SELECT\n",
    "  TIME_SHIFT(TIME_PARSE(\"timestamp\"), 'P1Y', (TIME_EXTRACT(CURRENT_TIMESTAMP,'YEAR') - TIME_EXTRACT(TIME_PARSE(\"timestamp\"),'YEAR'))) AS \"__time\",\n",
    "  \"isRobot\",\n",
    "  \"channel\",\n",
    "  \"isUnpatrolled\",\n",
    "  \"page\",\n",
    "  \"comment\",\n",
    "  \"commentLength\",\n",
    "  \"user\"\n",
    "FROM \"ext\"\n",
    "PARTITIONED BY DAY\n",
    "'''\n",
    "\n",
    "display.run_task(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f976d910-d4e9-43d6-ad8c-4f03bc4dd762",
   "metadata": {},
   "source": [
    "The following retention load rules configuration creates load rules as follows:\n",
    "\n",
    "- `loadByPeriod`, creates a [period load rule](https://druid.apache.org/docs/latest/operations/rule-configuration/#period-load-rule) for one cached replica of data newer than one year on both the `_default_tier` and `slow` tiers.\n",
    "- `loadForever`, creates a rule to cache all remaining data on the `slow` tier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d5b78c0-22a0-4738-907c-10ac53996d4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "retention_rules = [\n",
    "  {\n",
    "    \"type\": \"loadByPeriod\",\n",
    "    \"period\": \"P1Y\",\n",
    "    \"tieredReplicants\": {\n",
    "      \"_default_tier\": 1,\n",
    "      \"slow\": 1\n",
    "    }\n",
    "  },\n",
    "  {\n",
    "    \"type\": \"loadForever\",\n",
    "    \"tieredReplicants\": {\n",
    "      \"slow\": 1\n",
    "    }\n",
    "  }\n",
    "]\n",
    "\n",
    "requests.post(f\"{druid_host}/druid/coordinator/v1/rules/{table_name}\", json.dumps(retention_rules), headers=druid_headers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a0923ca-d869-4f11-a523-cbdaa3e049ef",
   "metadata": {},
   "source": [
    "Run the following cell to see the resulting distribution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39e021c4-4619-4ef3-b4b9-41d3ab56dfcf",
   "metadata": {},
   "outputs": [],
   "source": [
    "display.sql(layout_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cd75180-c2f2-4c07-8d7c-9a0925c8b5b7",
   "metadata": {},
   "source": [
    "When it has been applied:\n",
    "\n",
    "* All data _younger_ than a year is cached on both tiers (caught by the opening `loadByPeriod` rule).\n",
    "* All other data is cached exclusively on the `slow` tier (caught by the closing `loadForever` rule)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1be115d5-6939-449a-92a0-04f563e02faf",
   "metadata": {},
   "source": [
    "### Leave some data uncached\n",
    "\n",
    "To keep some of your table data accessible _only_ from [deep storage](https://druid.apache.org/docs/latest/querying/query-deep-storage), set `tieredReplicants` to an empty set and set `useDefaultTierForNull` to false.\n",
    "\n",
    "Review the rules below, and then run the cell to apply it to the table. This uses a mixture of all the retention load rules you have seen so far.\n",
    "\n",
    "* A period load rule, which fires first, catches all data newer than a year, and loads it on `_default_tier` Historicals.\n",
    "* An interval load rule, catcheing data for 2015 and loading this to Historicals in the `slow` tier only.\n",
    "* A final forever load rule, that, since it has no `tieredReplicants` and `useDefaultTierForNull` is `false`, ensures none of the remaining data is cached on Historicals.\n",
    "\n",
    "What do you predict will happen to data for 2016?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a35417e-7632-43ed-927d-47a464eacd6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "retention_rules = [\n",
    "  {\n",
    "    \"type\": \"loadByPeriod\",\n",
    "    \"period\": \"P1Y\",\n",
    "    \"tieredReplicants\": {\n",
    "      \"_default_tier\": 1\n",
    "    }\n",
    "  },\n",
    "  {\n",
    "    \"type\": \"loadByInterval\",\n",
    "    \"interval\": \"2015/P1Y\",\n",
    "    \"tieredReplicants\": {\n",
    "      \"slow\": 1\n",
    "    }\n",
    "  },\n",
    "  {\n",
    "    \"type\": \"loadForever\",\n",
    "    \"tieredReplicants\": {},\n",
    "    \"useDefaultTierForNull\": \"false\"\n",
    "  }\n",
    "]\n",
    "\n",
    "requests.post(f\"{druid_host}/druid/coordinator/v1/rules/{table_name}\", json.dumps(retention_rules), headers=druid_headers)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a437e86-6c88-4d49-a1a7-762fa438c059",
   "metadata": {},
   "source": [
    "Run the following cell to see the resulting distribution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0a4e7a3-2ccf-4c8e-8dfd-f127fcd9e206",
   "metadata": {},
   "outputs": [],
   "source": [
    "display.sql(layout_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f8ac22c-9fd5-4209-86c5-41fd7b4431cb",
   "metadata": {},
   "source": [
    "Notice that, depending on the period of time they cover, Druid loads some table segments onto Historicals but not others.\n",
    "\n",
    "The order of the rules means:\n",
    "\n",
    "1. Druid loads data newer than a year onto `_default_tier` Historicals.\n",
    "2. Druid loads data covering 2015 onto `slow`-tier Historicals.\n",
    "3. Druid loads no other data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44738d6d-cec2-40ad-aaba-998c758c63f4",
   "metadata": {},
   "source": [
    "## Clean up\n",
    "\n",
    "Run the following cell to remove the table used in this notebook from the database and delete your additional ruleset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8082b545-ba7f-4ede-bb6e-2a6dd62ba0d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "retention_rules = []\n",
    "requests.post(f\"{druid_host}/druid/coordinator/v1/rules/{table_name}\", json.dumps(retention_rules), headers=druid_headers)\n",
    "print(f\"Drop table: [{druid.datasources.drop(table_name)}]\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54b8d5fe-ba85-4b5b-9669-0dd47dfbccd1",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "* All historical servers belong to a tier.\n",
    "* The default tier for all Historicals is `_default`.\n",
    "* Default retention rules apply to all tables.\n",
    "* Out of the box, the default retention rule set has only one rule, loading all data on to the `_default` tier.\n",
    "* There is an API endpoint for amending load rules.\n",
    "* Rule sets can be made up of a mixture of age, interval, and \"forever\" rules.\n",
    "* Druid applies rules in the order they are listed in the retention rules configuration.\n",
    "\n",
    "## Learn more\n",
    "\n",
    "* Amend `retention_rules` to try different periods, and to see what happens when the rule order is reversed.\n",
    "* Read more about [retention rules](https://druid.apache.org/docs/latest/operations/rule-configuration), particularly [load rules](https://druid.apache.org/docs/latest/operations/rule-configuration#load-rules), in the documentation."
   ]
  }
 ],
 "metadata": {
  "execution": {
   "allow_errors": true,
   "timeout": 300
  },
  "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}