notebooks/03-query/02-approx-ranking.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "557e06e8-9b35-4b34-8322-8a8ede6de709",
   "metadata": {},
   "source": [
    "# Using TopN approximation in Druid queries\n",
    "\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",
    "Imagine you’re building a dynamic filter in your app: you want to populate it with, say, the top most popular (COUNT) dimension values in descending order (ORDER BY). Druid speeds up this type of query using TopN approximation by default. In this tutorial, work through some examples and see the effect of turning approximation off."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c94ff5c9-ada9-4f1d-8541-649e70cfc9a3",
   "metadata": {},
   "source": [
    "## Prerequisites\n",
    "\n",
    "This tutorial works with Druid 26.0.0 or later.\n",
    "\n",
    "#### Run using Docker\n",
    "\n",
    "Launch this tutorial and all prerequisites using the `druid-jupyter` profile of the Docker Compose file for Jupyter-based Druid tutorials. For more information, see [Docker for Jupyter Notebook tutorials](https://github.com/implydata/learn-druid/#readme).\n",
    "   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6b56cfc-9951-4a4e-a3f4-828e2dd5b3b5",
   "metadata": {},
   "source": [
    "## Initialization\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": "685b872e-0d59-4100-a636-39ec93c627fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import druidapi\n",
    "import os\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",
    "\n",
    "display = druid.display\n",
    "sql_client = druid.sql\n",
    "status_client = druid.status\n",
    "\n",
    "status_client.version"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6fe9c99-ee0d-4205-9ca5-a8810c977335",
   "metadata": {},
   "source": [
    "## Load example data\n",
    "\n",
    "Once your Druid environment is up and running, ingest the sample data for this tutorial.\n",
    "\n",
    "Run the following cell to create a table called `example-flights-topn`.  When completed, you'll see a description of the final table.\n",
    "\n",
    "Monitor the ingestion task process in the Druid console."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e89a3000-a65e-4c4a-a917-3c37cbe975b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql='''\n",
    "REPLACE INTO \"example-flights-topn\" OVERWRITE ALL\n",
    "WITH \"ext\" AS (SELECT *\n",
    "FROM TABLE(\n",
    "  EXTERN(\n",
    "    '{\"type\":\"http\",\"uris\":[\"https://static.imply.io/example-data/flight_on_time/flights/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2005_11.csv.zip\"]}',\n",
    "    '{\"type\":\"csv\",\"findColumnsFromHeader\":true}'\n",
    "  )\n",
    ") EXTEND (\"depaturetime\" VARCHAR, \"arrivalime\" VARCHAR, \"Year\" BIGINT, \"Quarter\" BIGINT, \"Month\" BIGINT, \"DayofMonth\" BIGINT, \"DayOfWeek\" BIGINT, \"FlightDate\" VARCHAR, \"Reporting_Airline\" VARCHAR, \"DOT_ID_Reporting_Airline\" BIGINT, \"IATA_CODE_Reporting_Airline\" VARCHAR, \"Tail_Number\" VARCHAR, \"Flight_Number_Reporting_Airline\" BIGINT, \"OriginAirportID\" BIGINT, \"OriginAirportSeqID\" BIGINT, \"OriginCityMarketID\" BIGINT, \"Origin\" VARCHAR, \"OriginCityName\" VARCHAR, \"OriginState\" VARCHAR, \"OriginStateFips\" BIGINT, \"OriginStateName\" VARCHAR, \"OriginWac\" BIGINT, \"DestAirportID\" BIGINT, \"DestAirportSeqID\" BIGINT, \"DestCityMarketID\" BIGINT, \"Dest\" VARCHAR, \"DestCityName\" VARCHAR, \"DestState\" VARCHAR, \"DestStateFips\" BIGINT, \"DestStateName\" VARCHAR, \"DestWac\" BIGINT, \"CRSDepTime\" BIGINT, \"DepTime\" BIGINT, \"DepDelay\" BIGINT, \"DepDelayMinutes\" BIGINT, \"DepDel15\" BIGINT, \"DepartureDelayGroups\" BIGINT, \"DepTimeBlk\" VARCHAR, \"TaxiOut\" BIGINT, \"WheelsOff\" BIGINT, \"WheelsOn\" BIGINT, \"TaxiIn\" BIGINT, \"CRSArrTime\" BIGINT, \"ArrTime\" BIGINT, \"ArrDelay\" BIGINT, \"ArrDelayMinutes\" BIGINT, \"ArrDel15\" BIGINT, \"ArrivalDelayGroups\" BIGINT, \"ArrTimeBlk\" VARCHAR, \"Cancelled\" BIGINT, \"CancellationCode\" VARCHAR, \"Diverted\" BIGINT, \"CRSElapsedTime\" BIGINT, \"ActualElapsedTime\" BIGINT, \"AirTime\" BIGINT, \"Flights\" BIGINT, \"Distance\" BIGINT, \"DistanceGroup\" BIGINT, \"CarrierDelay\" BIGINT, \"WeatherDelay\" BIGINT, \"NASDelay\" BIGINT, \"SecurityDelay\" BIGINT, \"LateAircraftDelay\" BIGINT, \"FirstDepTime\" VARCHAR, \"TotalAddGTime\" VARCHAR, \"LongestAddGTime\" VARCHAR, \"DivAirportLandings\" VARCHAR, \"DivReachedDest\" VARCHAR, \"DivActualElapsedTime\" VARCHAR, \"DivArrDelay\" VARCHAR, \"DivDistance\" VARCHAR, \"Div1Airport\" VARCHAR, \"Div1AirportID\" VARCHAR, \"Div1AirportSeqID\" VARCHAR, \"Div1WheelsOn\" VARCHAR, \"Div1TotalGTime\" VARCHAR, \"Div1LongestGTime\" VARCHAR, \"Div1WheelsOff\" VARCHAR, \"Div1TailNum\" VARCHAR, \"Div2Airport\" VARCHAR, \"Div2AirportID\" VARCHAR, \"Div2AirportSeqID\" VARCHAR, \"Div2WheelsOn\" VARCHAR, \"Div2TotalGTime\" VARCHAR, \"Div2LongestGTime\" VARCHAR, \"Div2WheelsOff\" VARCHAR, \"Div2TailNum\" VARCHAR, \"Div3Airport\" VARCHAR, \"Div3AirportID\" VARCHAR, \"Div3AirportSeqID\" VARCHAR, \"Div3WheelsOn\" VARCHAR, \"Div3TotalGTime\" VARCHAR, \"Div3LongestGTime\" VARCHAR, \"Div3WheelsOff\" VARCHAR, \"Div3TailNum\" VARCHAR, \"Div4Airport\" VARCHAR, \"Div4AirportID\" VARCHAR, \"Div4AirportSeqID\" VARCHAR, \"Div4WheelsOn\" VARCHAR, \"Div4TotalGTime\" VARCHAR, \"Div4LongestGTime\" VARCHAR, \"Div4WheelsOff\" VARCHAR, \"Div4TailNum\" VARCHAR, \"Div5Airport\" VARCHAR, \"Div5AirportID\" VARCHAR, \"Div5AirportSeqID\" VARCHAR, \"Div5WheelsOn\" VARCHAR, \"Div5TotalGTime\" VARCHAR, \"Div5LongestGTime\" VARCHAR, \"Div5WheelsOff\" VARCHAR, \"Div5TailNum\" VARCHAR, \"Unnamed: 109\" VARCHAR))\n",
    "SELECT\n",
    "  TIME_PARSE(\"depaturetime\") AS \"__time\",\n",
    "  \"Reporting_Airline\",\n",
    "  \"Tail_Number\",\n",
    "  \"Distance\",\n",
    "  \"Origin\",\n",
    "  \"Flight_Number_Reporting_Airline\"\n",
    "FROM \"ext\"\n",
    "PARTITIONED BY DAY\n",
    "'''\n",
    "\n",
    "display.run_task(sql)\n",
    "sql_client.wait_until_ready('example-flights-topn')\n",
    "display.table('example-flights-topn')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00141575-29b4-440e-b23f-f7c6b237ef28",
   "metadata": {},
   "source": [
    "When this is completed, run the following cell to load some Python libraries we need to explore what TopN does."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2a19226-6abc-436d-ac3c-9c04d6026707",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f388633f-195b-4381-98cc-7a2f80f48690",
   "metadata": {},
   "source": [
    "## Example TopN style queries\n",
    "\n",
    "Druid looks for patterns in `SELECT` statements to determine if they would benefit from using approximation. A ranking query, like the following, matches the rules for TopN approximation, so Druid enables it by default.\n",
    "\n",
    "For Druid to automatically optimize for TopN, you need a SQL statement that has:\n",
    "* A GROUP BY on one dimension, and\n",
    "* an ORDER BY on one aggregate.\n",
    "\n",
    "Run this query to see what the results are like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b76e5184-9fe4-4f21-a471-4e15d16515c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT\n",
    "    \"Reporting_Airline\",\n",
    "    COUNT(*) AS Flights,\n",
    "    SUM(\"Distance\") AS SumDistance\n",
    "FROM\n",
    "    \"example-flights-topn\"\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 10\n",
    "'''\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5600fc48-c999-406f-800b-3f0f6a973aa0",
   "metadata": {},
   "source": [
    "Run the following cell, which uses the `explain_sql` method to show the [`EXPLAIN PLAN`](https://druid.apache.org/docs/latest/querying/sql-translation#interpreting-explain-plan-output) for this query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7595eec0-a709-4cd6-985e-eec8a6e37b61",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(json.dumps(json.loads(sql_client.explain_sql(sql)['PLAN']), indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8658b26e-2f09-4a97-96e8-589168130559",
   "metadata": {},
   "source": [
    "The plan `queryType` is `topN`, showing that TopN approximation was used.\n",
    "\n",
    "Druid applied a `LIMIT` operation on the results calculated by each data service involved in the query, improving processing efficiency by minimizing the amount of data transferred to the Broker.\n",
    "\n",
    "This [pushed-down](https://druid.apache.org/docs/latest/querying/groupbyquery#limit-pushdown-optimization) `LIMIT` is the `max` of the `threshold` in the plan (which came from the `LIMIT` in the SQL) and the [`minTopNThreshold`](https://druid.apache.org/docs/latest/querying/topnquery.html#aliasing) setting in your cluster - the default being 1,000.\n",
    "\n",
    "To see the implication of this `LIMIT` in action, the cardinality of the `GROUP BY` dimension therefore needs to exceed this cap.\n",
    "\n",
    "Run the following query to discover the cardinality of the `GROUP BY` on `Reporting_Airline`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65a968e5-d51e-47e9-af04-88181f3b865b",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT COUNT (DISTINCT \"Reporting_Airline\") AS UniqueReportingAirlines\n",
    "FROM \"example-flights-topn\"\n",
    "'''\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13c8a101-dcba-49be-8d05-0a5dbd9731ca",
   "metadata": {},
   "source": [
    "The number of unique values is below the `LIMIT` cap, meaning, there is no trimming and the results are not approximate; all the data servers will return all their results, without trimming, to be merged and passed back to us.\n",
    "\n",
    "What is the cardinality for the `Tail_Number` dimension?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71c1816b-f090-4a3d-b476-8d40eb9c2dec",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT\n",
    "    COUNT (DISTINCT \"Tail_Number\") AS UniqueTailNumbers\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Tail_Number\" <> ''\n",
    "'''\n",
    "display.sql(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72a1a858-bda8-464e-988b-c4ed80b63f43",
   "metadata": {},
   "source": [
    "With this many distinct values to `GROUP BY`, the services involved in the query will trim their results when using the\n",
    "`topN` engine.\n",
    "\n",
    "Run the next query to visualize the distribution of unique `Tail_Number`s in the example dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "208f1463-34dd-4b0e-aa78-e582e2133a8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT\n",
    "    \"Tail_Number\",\n",
    "    COUNT(*) AS RecordCount\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Tail_Number\" <> ''\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 500\n",
    "'''\n",
    "\n",
    "df4 = pd.DataFrame(sql_client.sql(sql))\n",
    "\n",
    "df4.plot(x='Tail_Number', y='RecordCount', marker='o')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.gca().get_legend().remove()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b16d9a4d-c9e7-447b-8cdb-7ad1c0f88d73",
   "metadata": {},
   "source": [
    "The plot shows that we have a long tail distribution, meaning there is a high likelihood the same `Tail_Number` will be in rank position one across the data set, and therefore across all segments. The flatter the distribution, the less reliable this assertion is.\n",
    "\n",
    "Take a look at the following cell to see a query that counts the number of records and sums total distance for each `Tail_Number`.\n",
    "\n",
    "Run the cell to execute this query in both TopN and non-TopN modes.\n",
    "\n",
    "The query gives a simple breakdown of flights, with distance, into `Tail_Number`, sorted by descending distance.\n",
    "\n",
    "It is first run and put into a Dataframe `df1`. It is then run again using the `useApproximateTopN` query context parameter to turn off approximation, storing the results in `df2`.\n",
    "\n",
    "It then runs a `compare` of `df2` against `df1` using `df3` and prints the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71db4746-4e8a-447e-aa58-f4c4ce3d7ffc",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT\n",
    "    \"Tail_Number\",\n",
    "    COUNT(*) AS \"count\",\n",
    "    SUM(Distance) AS \"distance\"\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Tail_Number\" IS NOT NULL\n",
    "GROUP BY 1\n",
    "ORDER BY 3 DESC\n",
    "LIMIT 500\n",
    "'''\n",
    "\n",
    "# Load the results into a pandas DataFrame\n",
    "\n",
    "df1 = pd.DataFrame(sql_client.sql(sql))\n",
    "\n",
    "# Set up a sql_request to turn off TopN approximation\n",
    "\n",
    "req = sql_client.sql_request(sql)\n",
    "req.add_context(\"useApproximateTopN\", \"false\")\n",
    "resp = sql_client.sql_query(req)\n",
    "\n",
    "# Load the non-TopN results into a second pandas DataFrame using that request\n",
    "\n",
    "df2 = pd.DataFrame(sql_client.sql_query(req).rows)\n",
    "\n",
    "# Load the compare of df1 to df2 into a new dataframe and print\n",
    "\n",
    "df3 = df1.compare(df2, keep_equal=True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78836242-acc8-4403-9e96-2177b96110ed",
   "metadata": {},
   "source": [
    "You can see:\n",
    "\n",
    "* The `self` (df1) and `other` (df2) rank position of each `Tail_Number` in each position\n",
    "* The self / other values for the calculated `count` and `distance`\n",
    "\n",
    "You may notice some `Tail_Number`s are in different positions depending on what the calculated `distance` is: certain data servers returned different sets of results, depending entirely on local data distribution. And some `Tail_Number`s may not appear in the list at all as they drop below the fold the cut-off applied to that specific process.\n",
    "\n",
    "Let's try this with a different dimension, `Flight_Number_Reporting_Airline`. The example dataset has more unique values, but the distribution is much flatter than `Tail_Number`. Run the following cell to see the count and a distribution plot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a96f924c-9fc1-4000-9a54-7a951db5d2bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT COUNT(DISTINCT \"Flight_Number_Reporting_Airline\") AS UniqueReportingAirlines\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Flight_Number_Reporting_Airline\" <> ''\n",
    "'''\n",
    "\n",
    "display.sql(sql)\n",
    "\n",
    "sql = '''\n",
    "SELECT \"Flight_Number_Reporting_Airline\", COUNT(*) AS Flights\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Flight_Number_Reporting_Airline\" > 0\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 500\n",
    "'''\n",
    "\n",
    "# Load the results into a pandas DataFrame\n",
    "\n",
    "df5 = pd.DataFrame(sql_client.sql(sql))\n",
    "\n",
    "# Display a plot\n",
    "\n",
    "df5.plot(x='Flight_Number_Reporting_Airline', y='Flights', kind=\"bar\", xticks=[])\n",
    "plt.gca().get_legend().remove()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4866091d-e689-4209-8f6e-4edd526646e9",
   "metadata": {},
   "source": [
    "This dimension, unlike `Tail_Number`, has a flatter distribution. Each data process is likely to have a flatter distribution of data, too, meaning the top ranking results are less prominent. The \"voting\" across the servers as to what is in the top is less clear.\n",
    "\n",
    "Run the following cell to repeat the same test we did before, creating two sets of results, and comparing them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "405f7a9b-ac13-4c13-8e30-42058df4cbce",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT\n",
    "    \"Flight_Number_Reporting_Airline\",\n",
    "    AVG(\"Distance\") AS AverageDistance\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Flight_Number_Reporting_Airline\" IS NOT NULL\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 10\n",
    "'''\n",
    "\n",
    "# Set up a sql_request to turn off TopN approximation\n",
    "\n",
    "req = sql_client.sql_request(sql)\n",
    "req.add_context(\"useApproximateTopN\", \"false\")\n",
    "resp = sql_client.sql_query(req)\n",
    "\n",
    "# Load two pandas DataFrames - one with the TopN and one with the non-TopN results\n",
    "\n",
    "df1 = pd.DataFrame(sql_client.sql(sql))\n",
    "df2 = pd.DataFrame(sql_client.sql_query(req).rows)\n",
    "\n",
    "# Load the compare of df1 to df2 into a new dataframe and print\n",
    "\n",
    "df3 = df1.compare(df2, keep_equal=True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "989a3e1c-cc8a-47c9-ad15-0b95fa00c7a6",
   "metadata": {},
   "source": [
    "Here, the flatter distribution exaggerates ranking and calculation error. Further issues are caused by the calculation being non-additive.\n",
    "\n",
    "The following cell contains a query that is a good example of TopN being applied: it creates a list of `Tail_Number`s within a particular period of time. Imagine that you might use this list to provide an interactive filter on `Tail_Number` to the end user when they're looking at this specific time period.\n",
    "\n",
    "Run the following cell to show the cardinality of `Tail_Number`s in that period, and then to plot the distribution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d039c393-96f4-4847-ac60-4414477ebc3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT COUNT (DISTINCT \"Tail_Number\") AS UniqueTailNumbers\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Tail_Number\" <> ''\n",
    "AND (TIMESTAMP '2005-11-01' <= \"__time\" AND \"__time\" <= TIMESTAMP '2005-11-14')\n",
    "'''\n",
    "display.sql(sql)\n",
    "\n",
    "sql = '''\n",
    "SELECT\n",
    "    \"Tail_Number\",\n",
    "    COUNT(*) AS \"Flights\"\n",
    "FROM \"example-flights-topn\"\n",
    "WHERE \"Tail_Number\" <> ''\n",
    "AND (TIMESTAMP '2005-11-01' <= \"__time\" AND \"__time\" <= TIMESTAMP '2005-11-14')\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 500\n",
    "'''\n",
    "\n",
    "df4 = pd.DataFrame(sql_client.sql(sql))\n",
    "\n",
    "df4.plot(x='Tail_Number', y='Flights', marker='o')\n",
    "plt.xticks(rotation=45, ha='right')\n",
    "plt.gca().get_legend().remove()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eeed8fa8-d1ce-41b2-955b-88fb0834ab36",
   "metadata": {},
   "source": [
    "This distribution pattern is good for TopN - the highest ranking values are very prominent.\n",
    "\n",
    "Run the following cell to compare the two styles of execution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d47d2017-1d89-4622-a42c-d86f29a774e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "SELECT \"Tail_Number\", COUNT(*) AS \"count\", SUM(Distance) AS \"distance\"\n",
    "    FROM \"example-flights-topn\"\n",
    "    WHERE \"Tail_Number\" IS NOT NULL\n",
    "    AND (TIMESTAMP '2005-11-01' <= \"__time\" AND \"__time\" <= TIMESTAMP '2005-11-14')\n",
    "    GROUP BY 1\n",
    "    ORDER BY 3 DESC\n",
    "    LIMIT 500\n",
    "'''\n",
    "\n",
    "req = sql_client.sql_request(sql)\n",
    "req.add_context(\"useApproximateTopN\", \"false\")\n",
    "resp = sql_client.sql_query(req)\n",
    "\n",
    "df1 = pd.DataFrame(sql_client.sql(sql))\n",
    "df2 = pd.DataFrame(sql_client.sql_query(req).rows)\n",
    "\n",
    "df3 = df1.compare(df2, keep_equal=True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f58a1846-5072-4495-b840-a620de3c0442",
   "metadata": {},
   "source": [
    "The distribution, together with our filters, means that these results are useful for this kind of interactive UI element."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36c4298a-bf7f-40e2-a3d9-b2b02d6a8f6e",
   "metadata": {},
   "source": [
    "## Cleanup\n",
    "\n",
    "Run the following cell to remove the table from the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b4f0f8e6-aca1-4ad1-98a8-b786b4e1ee97",
   "metadata": {},
   "outputs": [],
   "source": [
    "druid.datasources.drop(\"example-flights-topn\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b43cd060-429e-4e84-b559-ad63624e7c14",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "The speed boost we receive through TopN, at the expense of some accuracy, makes it useful for interactive elements like filters or initial lists of results that people will then deep dive into.\n",
    "\n",
    "* TopN is the default execution model for `GROUP BY` queries with one dimension, an `ORDER BY` and a `LIMIT` clause\n",
    "* You can turn TopN off with a query context parameter\n",
    "* Accuracy is highly dependent on distribution of the data, after filters etc., across the database"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d08b8804-1051-4d38-88e7-2be1776934eb",
   "metadata": {},
   "source": [
    "## Learn more\n",
    "\n",
    "Read the following documentation for more information:\n",
    "\n",
    "* [TopN queries](https://druid.apache.org/docs/latest/querying/topnquery)"
   ]
  }
 ],
 "metadata": {
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}